Spring’s JdbcDaoSupport Quick Start

This post serves as a very concise introduction to Spring’s JdbcDaoSupport usage, executing both queries and database functions. I’m assuming you already know how to configure the basic spring configuration for accesing the database.

One of the advantages of using this class is that it has a very easy to read code, and once you code the first method, the rest of them are practically a copy/paste job, while maintaining a lot of flexibilit, since you can call functions, stored procedures, or plain text queries very easily.

Using JdbcDaoSupport is very simple. The first step is making your DAO extend from the JdbcDaoSupport class

public class StudentDao extends JdbcDaoSupport

Simple, plain text query example

Let’s look at an example of executing a simple, plain text query:

Collection studentsCollection= null;

RowMapper rowMapper = new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Student student = new Student();
student.setId(rs.getString(“id”));
student.setName(rs.getString(“name”));
return student;
}
};

studentsCollection= getJdbcTemplate().query(“select * from students”, rowMapper);

  1. First we instantiate an anonymous inner class of RowMapper and override the mapRow method, which handles each row returned by a predetermined query.
  2. Call the “query” method from the JdbcTemplate (the “getJdbcTemplate” method is inherited from JdbcDaoSupport)
  3. Pass the query and the RowMapper as parameters

By now, the “studentsCollection” variable is now filled with the results from the query. That’s all there is to it.

Stored procedure or database function example

StoredProcedure sp = new StoredProcedure(getJdbcTemplate(), “SCHOOL_PKG.GET_STUDENT_GLOBAL_GRADE”){};
sp.declareParameter(new SqlOutParameter(“grade”, OracleTypes.NUMBER));
sp.declareParameter(new SqlInOutParameter(“student_id”, OracleTypes.NUMBER));

Map params = new HashMap();
params.put(“ret”, null);
params.put(“student_id”, “” + 1);

//uncomment if you are calling a function
//sp.setFunction(true);
//sp.compile();

Map result = sp.execute(params);
System.out.println(String.valueOf(result.get(“grade”)));

  1. Create an instance of the StoreProcedure class (it’s included in the Spring’s core package) and pass the JdbcTemplate (it’s inherited from JdbcDaoSupport) and the procedure or function name as parameters (Note that the StoreProcedure class is an abstract class, and therefore we have to put the brackets and the end to tell the virtual machine that we are implementing it as an anonymous class)
  2. Declare the input and output parameters (SqlOutParameter, SqlInOutParameter, respectively) in the same order as declared in the database
  3. Create a map to allocate the parameters (Specify null for the output parameters, since those will be filled by spring when executing the procedure)
  4. If you are calling a function, uncomment the setFunction method (by default it’s set to false, assuming that you are calling a store procedure)
  5. Call the “execute” method, which returns a map with the results

Final notes

This quick start guide should cover most scenarios. I’m sure it’ll save you lot’s of investigation time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s