Setting SQL statement parameter value: column index 1, parameter value [name], value class [java.lang.String], SQL type unknown

Question

I am passing parameters to Query using NamedParameterJdbcTemplate . But am getting trace details as a warning and parameter binding is not showing.Main problem for is it is showing the executing sql but I want parameter also part of it but only ?(question mark) comes.After the warning binding happens and execution is good but binding is not visible in log.Please help me to check this.

column index 1, parameter value [mike], value class [java.lang.String], SQL type unknown column index 2, parameter value [1], value class [java.lang.Integer], SQL type unknown

Repository:

@Repository
public class DataRepository {
    private static Logger log = LoggerFactory.getLogger(DataRepository.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    public Data findDataObjet() throws Exception {

        Map<String, Object> parameters = new HashMap<>();
        parameters.put("id1", "mike");
        parameters.put("id2", new Long(1));

        String sqlString = "select * from table1 where id1 = ":id1" and id2 = :id2";

        Data extObj = jdbcTemplate.query(sqlString, parameters, (rs) -> {
            if (rs != null && rs.next()) {
                Data innerObj = new Data();
                innerObj.setName(rs.getString("name"));             
                return innerObj;
            } else {
                log.info("No records found:"+rs);
                return null;
            }
        });

        return extObj;

    }
}

application.properties:

spring.datasource.url=url
spring.datasource.username=user
spring.datasource.password=password
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

spring.datasource.type = com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.maximum-pool-size=2

logback-spring.xml:

<springProfile name="dev">
        <logger name="org.springframework.jdbc.core.JdbcTemplate">
            <level value="trace" />
        </logger>
        <logger name="org.springframework.jdbc.core.StatementCreatorUtils">
            <level value="trace" />
        </logger>
        <appender name="dailyRollingFileAppender"
            class="ch.qos.logback.core.rolling.RollingFileAppender">
            <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
                <FileNamePattern>${logsPath}TSYS2DM%d{MMddyyyy}.log
                </FileNamePattern>
                <maxHistory>4</maxHistory>
            </rollingPolicy>

            <encoder>
                <Pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level
                    %logger{35}-%msg %n</Pattern>
            </encoder>

        </appender>
        <root level="INFO">
            <appender-ref ref="dailyRollingFileAppender" />
        </root>
    </springProfile>

Log Trace:

2018-03-07 12:19:42.888 DEBUG 30224 --- [resources/input] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
2018-03-07 12:19:42.889 DEBUG 30224 --- [resources/input] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [ select * from table1 where id1 = "?" and id2 = ?;]
2018-03-07 12:19:43.132 TRACE 30224 --- [resources/input] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [mike], value class [java.lang.String], SQL type unknown
2018-03-07 12:19:43.132 TRACE 30224 --- [resources/input] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [1], value class [java.lang.Integer], SQL type unknown
2018-03-07 12:19:43.439  INFO 30224 --- [resources/input] c.t.f.c.t.t.db.TRMAccountDataRepository  : found record with cnsmr_id:1234

Viewy

The parameter binding is showing: Setting SQL statement parameter value: column index 1, parameter value [mike], value class [java.lang.String], SQL type unknown. Executing a prepared statement doesn't consist in replacing the named parameters in the SQL by values. It consists in passing the driver a query with parameter placeholders, and setting values for all the parameters. All you're seeing is perfectly normal. – Pretzel

its normal, refer this #1932708 – Tureen

@Chids this is normal based on the link and unknown can be ignored as it is coming more than 5 years and api is working like this. – Viewy

@JBNizet thanks for your response. – Viewy

@Viewy , yes behaviour has not changed . there hasn't been any enhacements on this – Tureen

Answer 1

Use Wrapper class SqlParameterValue and the notification will disappear:

    Map<String, Object> parameters = new HashMap<>();
    parameters.put("id1", new SqlParameterValue(Types.VARCHAR,"mike"));
    parameters.put("id2", new SqlParameterValue(Types.BIGINT,new Long(1)));

In the Logfile you will see SQL Type 91... and not SQL type unknown

Emplane

Where does SqlParameterValue originate from? – Wollis