Writing DAOs using Spring JDBC

Lets see how to write Daos using spring jdbc.

I create a schema TESTDBSCHEMA and in that I create a table called PERSON.

CREATE TABLE TESTDBSCHEMA.PERSON(
FIRST_NAME     CHAR(20)      NOT  NULL, 

LAST_NAME      CHAR(20)      NOT  NULL, 

AGE                     INTEGER       NOT  NULL, 

EMP_NO               INTEGER       NOT  NULL
 );

CREATE SEQUENCE TESTDBSCHEMA.PERSON_CNTR_SEQ AS INTEGER START WITH 1 INCREMENT BY 1 NO CACHE;

CREATE UNIQUE INDEX TESTDBSCHEMA.PERSON_IX ON TESTDBSCHEMA.PERSON(EMP_NO);

ALTER TABLE TESTDBSCHEMA.PERSON  ADD CONSTRAINT PERSON_IX PRIMARY KEY (EMP_NO);
We usually assign a sequence to a primary key. Here PERSON CNTR_SEQ is for entering values into EMP_NO column.

For this table we can write a Transfer Object (TO) , also called as domain class as shown below.

public class Person {
          private String firstName;
          private String lastName;
          private Integer age;
          private Integer empNo;
//setters and getters
}

JdbcTemplate Class:This is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in the org.springframework.dao package.
(Ref: http://static.springsource.org/spring/docs/2.5.x/api/org/springframework/jdbc/core/JdbcTempla te.html)

Most of the exceptions thrown by Spring Jdbc API are runtime, so we don't have to handle those exceptions. In conventional Jdbc Daos we would have to handle all the subclasses of java.sql.SQLException which is a pain.


SimpleJdbcTemplate  Class:
This class basically evolved from JdbcTemplate class. It can take advantage of java 1.5 features like autoboxing, generics and variable-length arguments to simplify its usage.We can either instantiate the SimpleJdbcTemplate  directky or retrieve its instance by extending SimpleJdbcDaoSupport class.
(Ref: Spring Recipes 2.5 Apress Edition)

Here is the code for the dao with simple CRUD operations

public class JdbcPersonDao extends SimpleJdbcDaoSupport  implements PersonDao{

public JdbcPersonDao{)
 

public List<Person> readAll{){
       String sql = "SELECT* FROM TESTDBSCHEMA.PERSON";
       List<Person> persons= getSimpleJdbcTemplate{).query{sql,new PersonRowMapper{));
      return persons;
}

public int insert{Person person) {
         String sql = "INSERT IN TO TESTDBSCHEMA.PERSON{FIRST_NAME, LAST_NAME, AGE) " +  "VALUES{:firstName,:lastName,:age, NEXT VALUE FOR TESTDBSCHEMA.PERSON_CNTR_SEQ)";
   Map<String, 0bject> parameters = new HashMap<String,0bject>{);
   parameters.put{"firstName", person.getFirstName{));
   parameters.put{"lastName", person.getLastName{));
   parameters.put{"age", person.getAge{));
  SqlParameterSource parameterSource = new MapSqlParameterSource{parameters);
  return getSimpleJdbcTemplate{).update{sql,parameterSource);
}

public int update{Person person){
   String sql = "UPDATE TESTDBSCHEMA.PERSON  SET FIRST_NAME = :firstName,     LAST_NAME:lastName, AGE = :age" + "WHERE EMP_NO = :empNo";

   Map<String,0bject> parameters = new HashMap<String,0bject>{);
   parameters.put{"firstName", person.getFirstName{));
   parameters.put{"lastName", person.getLastName{));
   parameters.put{"age", person.getAge{));
   parameters.put{"empNo", person.getEmpNo{));

   SqlParameterSource parameterSource = new MapSqlParameterSource{parameters);
   return getSimpleJdbcTemplate{).update{sql,parameterSource);
}

public int delete{Integer empNo) {
   String sql = "DELETE FROM TESTDBSCHEMA.PERSON  WHERE EMP_NP = :empNo" ;
   Map<String,Object> parameters = new HashMap<String, 0bject>{);
   parameters.put{"empNo",empNo);
   return getSimpleJdbcTemplate{).update{sql,parameters);
}

public Person getPerson(Integer  empNo){
 String sql = "SELECT* FROM TESTDBSCHEMA.PERSON  WHERE EMP NO= :EMPnO ";         Map<String,Object> parameters = new HashMap<String,Object>(); parameters.put("empNo", empNo);
 return getSimpleJdbcTemplate().queryForObject(sql, new PersonRowMapper(),parameters);
}

}

public class PersonRowMapper ParameterizedRowMapper<Person>{
    public Person mapRow(ResultSet rs, int argl) throws SQLException{
        Person person = new Person();
        person.setAge(rs.getint("AGE"));
        person.setEmpNo(rs.getint("EMP_NO"));
        person.setFirstName(rs.getString("FIRST_NAME"));
        person.setLastName(rs.getString("LAST_NAME"));
        return person;
    }
}   

In your spring configuration you will now have these following entries.

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName" value="java:comp/env/jdbc/TestDBDatasource" />
</bean>

<bean id="personDao" class="JdbcPersonDao">
<property name="dataSource"  ref="dataSource" />
</bean>


We can just inject dataSource to PersonDao and the inherited methods from SimpleJdbcDaoSupport would generate JdbcTemplate for us.  Or we could also create our own JdbcTemplate and inject it in to PersonDao as shown below.

<bean id="jdbcTemplate"  class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource"  ref="dataSource"/>
</bean>

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName" value="java:comp/env/jdbc/TestDBDatasource"  />
</bean>

<bean id="personDao" class="JdbcPersonDao">
<property name="jdbcTemplate"  ref="jdbcTemplate"/>
</bean>

Now you can inject the personDao to any of your objects using spring DI.


How to write Spring Jdbc Dao  without row mapper class?

Here is the code.

public List<Person> createList(List<Map<String, Object>> rows){
     List<Person> persons= new ArrayList<Person>();
     for(Map profileMap : rows){
           persons.add(this.createOneObject(profileMap));

      }

  return persons;

public Person createOneObject(Map  personMap){

   Person person = new Person();
   person.setAge((Integer)personMap.get( "AGE"  )  )  ;                 person.setLastName(((String)personMap.get( "FISRT_NAME"  )) .trim()); 
person.setLastName(((String)personMap.get( "LAST_NAME" )) .trim());
person.setEmpNo((Integer)personMap.get("EMP_NO"));
return person;

public List<Person> readAll(){
  String sql = "SELECT* FROM TESTDBSCHEMA.PERSON";
  return this.createList(this.getSimpleJdbcTemplate().queryForList(sql));
}


public Person getPerson(Integer empNo){
  String sql = "SELECT* FROM TESTDBSCHEMA.PERSON  WHERE EMP NO= :empNo ";   Map<String,Object> parameters= new HashMap<String,Object>(); parameters.put("empNo", empNo);
return this.createOneObject(this.getSimpleJdbcTemplate().queryForMap(sql, parameters));
}How to write Spring Jdbc Dao that has SQL IN clause?

String sql = "SELECT* FROM PERSON WHERE AGE IN (:agesList)"i
List<Integer> agesList = new ArrayList<Integer>();
agesList.add(30);agesList.add(40);
MapSqlParameterSource parameters= new MapSqlParameterSource();
parameters.addValue("agesList", agesList);
return this.createList(this.getSimpleJdbcTemplate().queryForList(sql,parameters)


How to remove schema name before table name in sql query?
 
The examples above shows that we hard coded the schema name before the table name. In real time environments we would usually find that the schema name would be different for different environments. We wouldn't want to hard code the schema name in the code.


There are 2 ways this can be done

1. Externalize the schema name in a separate bean as shown below. The only  drawback with this solution is that you need to create different ears with different schema names for different environments with different schema names. However it does externalize the schema name from code to configuration.

<bean id="schema" class="java.lang.String">
        <constructor-arg value="TESTDBSCHEMA"/>
</bean>

<bean id="personDao" class="JdbcPersonDao">
<constructor-arg> <ref  bean="dataSource"/> </constructor-arg>
<constructor-arg> <ref  bean="schema"/> </constructor-arg>
</bean>


public JdbcPersonDao(DataSource ds, String schema){
       setDataSource(ds);
       getJdbcTemplate().execute("SET CURRENT SCHEMA   "  +  schema);
}
Schema is set in different databases in different ways. The Set schema statement above works only for db2.

2. While creating the datasource (JNDI) you can specify "currentSchema" custom property for a data source in the server (RSA or Websphere).
The description of this property in RSA is given as following "It identifies the default schema name used to qualify unqualified database object references where applicable in dynamically prepared SQL statements. Unless currentSchema is used, the default schema name is the
authorization id of the current session user." This is pretty much self explanatory.

The instructions to set this field (currentSchema) is described in this post "Creating a DataSource in RSA".

The second method is usually followed in real time environments. The first method however can be used when the development is done in tomcat or at times when a datasource jndi is not created during development phase of project.

Now we don't need to specify the schema name in the query as shown below


public Person getPerson(Integer  empNo){
String sql = "SELECT* FROM PERSON WHERE EMP_NO = :empNo ";
Map<String,Object>  parameters= new  HashMap<String,Object>();
parameters.put("empNo", empNo);
return this.createOneObject(this.getSimpleJdbcTemplate(}.queryForMap(sql, parameters)) ;