Modeling JDBC Operations as Java Objects

The org.springframework.jdbc.object package contains classes that allow you to access the database in a more object-oriented way. For example, you can run queries and get the results as a list containing business objects with relational column data mapped to the properties of the business object. You can also run stored procedures and execute update, delete, and insert statements.

Understanding SqlQuery

SqlQuery is a reusable, thread-safe class , which encapsulates the SQL query. Subclasses must implement the newRowMapper(..) method to provide an instance of a RowMapper that can create one object for each row resulting from traversing the ResultSet. which is created during query execution. The SqlQuery class is rarely used directly because the MappingSqlQuery subclass provides a much more convenient implementation for mapping strings to Java classes. Other implementations that extend SqlQuery are MappingSqlQueryWithParameters and UpdatableSqlQuery.

Using MappingSqlQuery

MappingSqlQuery is a reusable query in which concrete subclasses must implement the abstract mapRow(..) method to map each row of the provided ResultSet into an object of the specified type. The following example shows a custom query that maps data from the t_actor relation to an instance of the Actor class:

public class ActorMappingQuery extends MappingSqlQuery<Actor> {
    public ActorMappingQuery(DataSource ds) {
        super(ds, "select id, first_name, last_name from t_actor where id = ?");
        declareParameter(new SqlParameter("id", Types.INTEGER));
        compile();
    }
    @Override
    protected Actor mapRow(ResultSet rs, int rowNumber) throws SQLException {
        Actor actor = new Actor();
        actor.setId(rs.getLong("id"));
        actor.setFirstName(rs.getString("first_name"));
        actor.setLastName(rs.getString("last_name"));
        return actor;
    }
}

class ActorMappingQuery(ds: DataSource) : MappingSqlQuery<Actor>(ds, "select id, first_name, last_name from t_actor where id = ?") {
    init {
        declareParameter(SqlParameter("id", Types.INTEGER))
        compile()
    }
    override fun mapRow(rs: ResultSet, rowNumber: Int) = Actor(
            rs.getLong("id"),
            rs.getString("first_name"),
            rs.getString("last_name")
    )
}

The class extends MappingSqlQuery, parameterized by the Actor type. The constructor for this custom request takes DataSource as its only parameter. In this constructor, you can call the superclass constructor with DataSource and the SQL that must be executed to retrieve the rows for this query. This SQL is used to create a PreparedStatement, so it can contain placeholders for any parameters that will be passed at runtime. You must declare each parameter using the declareParameter method, passing it a SqlParameter. SqlParameter takes the name and JDBC type defined in java.sql.Types. Once all the parameters have been defined, you can call the compile() method to prepare and subsequently execute the statement. Once compiled, this class is thread safe, so as long as these instances are created when the DAO is initialized, they can be stored as instance variables and reused. The following example shows how to define such a class:

private ActorMappingQuery actorMappingQuery;
@Autowired
public void setDataSource(DataSource dataSource) {
    this.actorMappingQuery = new ActorMappingQuery(dataSource);
}
public Customer getCustomer(Long id) {
    return actorMappingQuery.findObject(id);
}

private val actorMappingQuery = ActorMappingQuery(dataSource)
fun getCustomer(id: Long) = actorMappingQuery.findObject(id)

The method in the previous example gets the client with id passed as the only parameter. Since we only need to return one object, we call the findObject helper method with id as a parameter. If instead there was a query that returned a list of objects and took additional parameters, we would use one of the execute methods, which takes an array of parameter values passed as variable-length arguments. The following example shows this method:

public List<Actor> searchForActors(int age, String namePattern) {
    List<Actor> actors = actorSearchMappingQuery.execute(age, namePattern);
    return actors;
}

fun searchForActors(age: Int, namePattern: String) =
            actorSearchMappingQuery.execute(age, namePattern)

Using SqlUpdate

The SqlUpdate class encapsulates a SQL update. Like a query, the update object is reusable, and like all RdbmsOperation classes, the update can have parameters and is defined in SQL. This class provides a number of update(..) methods similar to the execute(..) methods of request objects. The SqlUpdate class is concrete. It can be subclassed - for example, to add a custom update method. However, you don't necessarily need to subclass the SqlUpdate class because it can be easily parameterized by specifying SQL and declaring parameters. The following example creates a custom update method named execute:

import java.sql.Types;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;
public class UpdateCreditRating extends SqlUpdate {
    public UpdateCreditRating(DataSource ds) {
        setDataSource(ds);
        setSql("update customer set credit_rating = ? where id = ?");
        declareParameter(new SqlParameter("creditRating", Types.NUMERIC));
        declareParameter(new SqlParameter("id", Types.NUMERIC));
        compile();
    }
    /**
     * @param id для обновляемого Customer
     * @param rating новое значение кредитного рейтинга
     * @return количество обновленных строк
     */
    public int execute(int id, int rating) {
        return update(rating, id);
    }
}

import java.sql.Types
import javax.sql.DataSource
import org.springframework.jdbc.core.SqlParameter
import org.springframework.jdbc.object.SqlUpdate
class UpdateCreditRating(ds: DataSource) : SqlUpdate() {
    init {
        setDataSource(ds)
        sql = "update customer set credit_rating = ? where id = ?"
        declareParameter(SqlParameter("creditRating", Types.NUMERIC))
        declareParameter(SqlParameter("id", Types.NUMERIC))
        compile()
    }
    /**
     * @param id для обновляемого Customer
     * @param rating новое значение кредитного рейтинга
     * @return количество обновленных строк
     */
    fun execute(id: Int, rating: Int): Int {
        return update(rating, id)
    }
}

Using StoredProcedure

The StoredProcedure class is an abstract superclass for RDBMS stored procedure object abstractions.

The inherited property sql is the name of the stored procedure in the RDBMS.

To define a parameter for the StoredProcedure class, you can use SqlParameter or one of its subclasses. You must set the parameter name and SQL type in the constructor, as shown in the following code snippet:

new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_first_name", Types.VARCHAR),

SqlParameter("in_id", Types.NUMERIC),
SqlOutParameter("out_first_name", Types.VARCHAR),

The SQL type is specified using the java.sql.Types constants .

The first line (with SqlParameter) declares the IN parameter. You can use IN parameters both for calls to stored procedures and for queries using SqlQuery and its subclasses.

The second line (with SqlOutParameter) declares out parameter that will be used in the stored procedure call. There is also a SqlInOutParameter for InOut parameters (parameters that provide a in value to a procedure and also return a value).

For in parameters, in addition to the name and SQL type, you can set the scale for numeric data or the type name for custom created database types. For out parameters, you can specify a RowMapper to handle the display of rows returned from the REF cursor. Another option is to set SqlReturnType, which allows you to define custom handling of return values.

The following simple DAO example uses StoredProcedure to call the (sysdate)() function), which comes with any Oracle database. To use stored procedure functionality, you must create a class that extends StoredProcedure. In this example, the StoredProcedure class is an inner class. However, if you need to reuse StoredProcedure, you can declare it as a top-level class. This example has no input parameters, but the output parameter is declared as a date type using the SqlOutParameter class. The execute() method runs the procedure and extracts the returned date from the resulting Map. The resulting Map has an entry for each declared output parameter (in this case only one), using the parameter name as the key. The following listing shows our custom StoredProcedure class:

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;
public class StoredProcedureDao {
    private GetSysdateProcedure getSysdate;
    @Autowired
    public void init(DataSource dataSource) {
        this.getSysdate = new GetSysdateProcedure(dataSource);
    }
    public Date getSysdate() {
        return getSysdate.execute();
    }
    private class GetSysdateProcedure extends StoredProcedure {
        private static final String SQL = "sysdate";
        public GetSysdateProcedure(DataSource dataSource) {
            setDataSource(dataSource);
            setFunction(true);
            setSql(SQL);
            declareParameter(new SqlOutParameter("date", Types.DATE));
            compile();
        }
        public Date execute() {
            // The string "sysdate" has no input parameters, so it returns an empty Map...
            Map<String, Object> results = execute(new HashMap<String, Object>());
            Date sysdate = (Date) results.get("date");
            return sysdate;
        }
    }
}

import java.sql.Types
import java.util.Date
import java.util.Map
import javax.sql.DataSource
import org.springframework.jdbc.core.SqlOutParameter
import org.springframework.jdbc.object.StoredProcedure
class StoredProcedureDao(dataSource: DataSource) {
    private val SQL = "sysdate"
    private val getSysdate = GetSysdateProcedure(dataSource)
    val sysdate: Date
        get() = getSysdate.execute()
    private inner class GetSysdateProcedure(dataSource: DataSource) : StoredProcedure() {
        init {
            setDataSource(dataSource)
            isFunction = true
            sql = SQL
            declareParameter(SqlOutParameter("date", Types.DATE))
            compile()
        }
        fun execute(): Date {
            // The string "sysdate" has no input parameters, so an empty Map is returned...
            val results = execute(mutableMapOf<String, Any>())
            return results["date"] as Date
        }
    }
}

The following StoredProcedure example has two output parameters (in this case REF cursors from Oracle ):

import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;
public class TitlesAndGenresStoredProcedure extends StoredProcedure {
    private static final String SPROC_NAME = "AllTitlesAndGenres";
    public TitlesAndGenresStoredProcedure(DataSource dataSource) {
        super(dataSource, SPROC_NAME);
        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));
        declareParameter(new SqlOutParameter("genres", OracleTypes.CURSOR, new GenreMapper()));
        compile();
    }
    public Map<String, Object> execute() {
        // again, this stored procedure has no input parameters, so it returns an empty Map
        return super.execute(new HashMap<String, Object>());
    }
}

import java.util.HashMap
import javax.sql.DataSource
import oracle.jdbc.OracleTypes
import org.springframework.jdbc.core.SqlOutParameter
import org.springframework.jdbc.object.StoredProcedure
class TitlesAndGenresStoredProcedure(dataSource: DataSource) : StoredProcedure(dataSource, SPROC_NAME) {
    companion object {
        private const val SPROC_NAME = "AllTitlesAndGenres"
    }
    init {
        declareParameter(SqlOutParameter("titles", OracleTypes.CURSOR, TitleMapper()))
        declareParameter(SqlOutParameter("genres", OracleTypes.CURSOR, GenreMapper()))
        compile()
    }
    fun execute(): Map<String, Any> {
        // again, this stored procedure has no input parameters, so it returns an empty Map
        return super.execute(HashMap<String, Any>())
    }
}

Note how the overloads of the declareParameter(..) method that were used in the TitlesAndGenresStoredProcedure constructor are passed to instances of the RowMapper implementation. This is a very convenient and efficient way to reuse existing functionality. The following two examples contain code for two RowMapper implementations.

The TitleMapper class maps a ResultSet with a domain object Title for each row in the provided ResultSet as follows:

import java.sql.ResultSet;
import java.sql.SQLException;
import com.foo.domain.Title;
import org.springframework.jdbc.core.RowMapper;
public final class TitleMapper implements RowMapper<Title> {
    public Title mapRow(ResultSet rs, int rowNum) throws SQLException {
        Title title = new Title();
        title.setId(rs.getLong("id"));
        title.setName(rs.getString("name"));
        return title;
    }
}

import java.sql.ResultSet
import com.foo.domain.Title
import org.springframework.jdbc.core.RowMapper
class TitleMapper : RowMapper<Title> {
    override fun mapRow(rs: ResultSet, rowNum: Int) =
            Title(rs.getLong("id"), rs.getString("name"))
}

The GenreMapper class maps a ResultSet to a Genre domain object for each row in the provided ResultSet as follows:

import java.sql.ResultSet;
import java.sql.SQLException;
import com.foo.domain.Genre;
import org.springframework.jdbc.core.RowMapper;
public final class GenreMapper implements RowMapper<Genre> {
    public Genre mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Genre(rs.getString("name"));
    }
}

import java.sql.ResultSet
import com.foo.domain.Genre
import org.springframework.jdbc.core.RowMapper
class GenreMapper : RowMapper<Genre> {
    override fun mapRow(rs: ResultSet, rowNum: Int): Genre {
        return Genre(rs.getString("name"))
    }
}

To To pass parameters to a stored procedure that contains one or more input parameters in its RDBMS definition, you can write a strongly typed execute(..) method that will be delegated to the untyped execute(Map) method in the superclass, as shown in the following example:

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;
public class TitlesAfterDateStoredProcedure extends StoredProcedure {
    private static final String SPROC_NAME = "TitlesAfterDate";
    private static final String CUTOFF_DATE_PARAM = "cutoffDate";
    public TitlesAfterDateStoredProcedure(DataSource dataSource) {
        super(dataSource, SPROC_NAME);
        declareParameter(new SqlParameter(CUTOFF_DATE_PARAM, Types.DATE);
        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));
        compile();
    }
    public Map<String, Object> execute(Date cutoffDate) {
        Map<String, Object> inputs = new HashMap<String, Object>();
        inputs.put(CUTOFF_DATE_PARAM, cutoffDate);
        return super.execute(inputs);
    }
}

import java.sql.Types
import java.util.Date
import javax.sql.DataSource
import oracle.jdbc.OracleTypes
import org.springframework.jdbc.core.SqlOutParameter
import org.springframework.jdbc.core.SqlParameter
import org.springframework.jdbc.object.StoredProcedure
class TitlesAfterDateStoredProcedure(dataSource: DataSource) : StoredProcedure(dataSource, SPROC_NAME) {
    companion object {
        private const val SPROC_NAME = "TitlesAfterDate"
        private const val CUTOFF_DATE_PARAM = "cutoffDate"
    }
    init {
        declareParameter(SqlParameter(CUTOFF_DATE_PARAM, Types.DATE))
        declareParameter(SqlOutParameter("titles", OracleTypes.CURSOR, TitleMapper()))
        compile()
    }
    fun execute(cutoffDate: Date) = super.execute(
            mapOf<String, Any>(CUTOFF_DATE_PARAM to cutoffDate))
}