Choosing between jdbcTemplate.update(String sql,Object[] args,int[] argTypes) and jdbcTemplate.update(String sql,Object[] args)

user_5208416 0
Viewed 5528

I'm learning the combination of Spring Boot and jdbcTemplate for some basic crud operations, and trying to better understand which update method I should choose.

I understand that the following two class approaches (adapted from this post) will write the same record to the database.

Example 1:

public class InsertDemo {
private static final String sql =
        "INSERT INTO records (title, " +
        "    release_date, " +
        "    artist_id, " +
        "    label_id, " +
        "    created) " +
        "VALUES (?, ?, ?, ?, ?)";

private DataSource dataSource;

public InsertDemo(DataSource dataSource) {
    this.dataSource = dataSource;
}

public void saveRecord(String title, Date releaseDate,
                       Integer artistId, Integer labelId) {
    JdbcTemplate template = new JdbcTemplate(this.dataSource);

    Object[] params = new Object[] {
            title, releaseDate, artistId, labelId, new Date()
    };
    int[] types = new int[] {
            Types.VARCHAR,
            Types.DATE,
            Types.INTEGER,
            Types.INTEGER,
            Types.DATE
    };

    int row = template.update(sql, params, types);
    System.out.println(row + " row inserted.");
}

Example 2:

public class InsertDemo {
private static final String sql =
        "INSERT INTO records (title, " +
        "    release_date, " +
        "    artist_id, " +
        "    label_id, " +
        "    created) " +
        "VALUES (?, ?, ?, ?, ?)";

private DataSource dataSource;

public InsertDemo(DataSource dataSource) {
    this.dataSource = dataSource;
}

public void saveRecord(String title, Date releaseDate,
                       Integer artistId, Integer labelId) {
    JdbcTemplate template = new JdbcTemplate(this.dataSource);

    Object[] params = new Object[] {
            title, releaseDate, artistId, labelId, new Date()
    };

    int row = template.update(sql, params);
    System.out.println(row + " row inserted.");
}

but I'm unclear on why I would/should use the first one that specifies argument types. I've read the javadoc but I'm still not sure why I would need to specify the types. What am I missing?

spring-boot jdbctemplate java
2 Answers

Setting argument type provides correctness and optimisation (slight) for the underlying SQL statement. (The JdbcTemplate internally builds a PreparedStatement and sets values to it using provided/derived types).

In your example, if you don't specify Types array, they will be set as SqlTypeValue.TYPE_UNKOWN which eventually be guessed or resolved as;

if (sqlType == SqlTypeValue.TYPE_UNKNOWN || sqlType == Types.OTHER) {
            if (isStringValue(inValue.getClass())) {
                ps.setString(paramIndex, inValue.toString());
            }
            else if (isDateValue(inValue.getClass())) {
                ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
            }
            else if (inValue instanceof Calendar) {
                Calendar cal = (Calendar) inValue;
                ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
            }
            else {
                // Fall back to generic setObject call without SQL type specified.
                ps.setObject(paramIndex, inValue);
            }
        }

Have a look at org.springframework.jdbc.core.StatementCreatorUtils#setValue

So it's a good practice to set the arg types.

user_12930870

Without Type parameter:

sql SQL containing bind parameters args arguments to bind to the query (leaving it to the PreparedStatement to guess the corresponding SQL type); may also contain SqlParameterValue objects which indicate not only the argument value but also the SQL type and optionally the scale

With Type parameter:

sql SQL containing bind parameters args arguments to bind to the query argTypes SQL types of the arguments (constants from java.sql.Types)

user_44362570
Related