Close

Spring - JDBC Operations

[Last Updated: Apr 7, 2018]

Spring JDBC modules simplify the use of underlying JDBC API. org.springframework.jdbc.core.JdbcOperations defines Spring way of handling database access using JDBC. This interface provides abstraction from the low level repetitive JDBC operations like opening the database connection, preparing the Jdbc statements, handling and processing exception, handling transactions, closing the connection etc.

This interface is not meant to be implemented by the application code. We already have a well known implementation class, JdbcTemplate


Here we are going to give a quick overview of the methods defined in JdbcOperations interface.

Methods Explanation
query(String sql, ResultSetExtractor<T> rse) query(String sql, RowCallbackHandler rch) query(String sql, RowMapper<T> rowMapper) These methods handle sql 'select' query. The second parameter of all methods is a call back interface.
  1. ResultSetExtractor<T>{T extractData(ResultSet rs};} client has to iterate and extract result itself. Result object T typically is a map/collection etc. Not recommended. Used by Spring internally itself.
  2. RowCallbackHandler{void processRow(ResultSet rs);} Client is called back on per row basis. Doesn't return anything so client has to capture result into some external object or use the extracted result at the spot e.g. send email or send JMS message etc. A new instance should be created every time we want to use it.
  3. RowMapper<T>{T mapRow(ResultSet rs, int rowNum)} for mapping rows to T on a per-row basis. If we need to map exactly one result object per row then this is a good choice.
query(String sql, Object[] args, XYZ) Where args are value binding for '?' placeholder and XYZ is one of the callback classes described above (That means there are three overloaded methods in this group)
query(String sql, Object[] args, int[] argTypes, XYZ) Where argTypes are sql type (java.sql.Types). The methods without this parameter, figure out the corresponding SQL type themselves. XYZ is same as above.
query(String sql, XYZ, Object... args) Where args are same as above i.e. values for '?' placeholders. The difference is, these overloaded methods have Java Varargs as parameter.
query(String sql, PreparedStatementSetter pss, XYZ) Where the call back interface 
PreparedStatementSetter{
            void setValues(PreparedStatement ps);
                }
passes java.sql.PreparedStatement to the client code for setting necessary parameters for the placeholders.
query(PreparedStatementCreator psc, XYZ) Where the call back interface 
PreparedStatementCreator{
PreparedStatement createPreparedStatement(Connection con);
                }
passes java.sql.Connection to the client code for creating and returning the java.sql.PreparedStatement themselves. Mostly it is used internally by the Spring framework. We should look for a better alternative before using it.
queryForABC(....) Where ABC can be one of the followings:
  1. List : We expect the query result will be of multiple rows. It has many overloaded versions. If there are multiple column fields with each fetched rows, then we should use the ones return List of Maps. If there's only one field with fetched rows, then we should use the ones return List<T>, where T is the corresponding java type for the column type.
  2. Map : If query is setup to return only one row with multiple columns, then we should use this one
  3. Object : If query is going to return only one row and one field, then we can use this. The object type should be compatible with database column type. The overloaded methods with parameter RowObject is used if there are multiple columns but we want to map them to our custom java object T.
  4. RowSet : return the disconnected Spring's SqlRowSet. It's a mirror interface for javax.sql.RowSet
update(....) All overloaded update methods are used to perform 'insert' or 'update' or 'delete' statements. The use of different combination of parameters are same as above methods, expect for the one with KeyHolder. It is used to capture auto-generated keys, typically resulted in an 'insert' statement.
batchUpdate(....) Issue multiple SQL updates on a single JDBC Statement using batching. Followings are two new parameters which belong to different overloaded batchUpdate methods: 
  1. BatchPreparedStatementSetter{
     void setValues(PreparedStatement ps, int i);
     int getBatchSize();
            }
    It's similar to PreparedStatementSetter described up, except it is called multiple times for each number of updates in the batch. It has extra int i parameter too, which is the 0 based index of the update iteration. Useful for setting params from user collection to the statement. 
  2. ParameterizedPreparedStatementSetter<T>{
     void setValues(PreparedStatement ps, T argument);
                    }
    Better than the last one as it passes the real user object to set the parameter to the statement. The corresponding batchUpdate accepts the collection of user object.
execute(....) It can be used to execute any arbitrary SQL but often used for DDL statements like creating/alerting/dropping tables etc.
call(....) Calls database stored procedures or functions.

In the next tutorials we are going to give examples on the above methods.

See Also