Simplified Jdbc operations

As I stated in the last post, Spring DaoSupport and Template API is not recommended in new projects.
But for Jdbc API, how to simplify the data operations?
Spring jdbc support(spring-jdbc maven dependency) provides some simplified APIs for Jdbc operations.

Overview

Unlike the approach of JdbcDaoSupport, it is no need to subclass the JdbcDaoSupport class. A SQL query(and modifying query) execution will be wrapped in a single object. All you need to do is creating a new class(by subclassing the related operation object) for every SQL query execution.
In these operation classes, MappingSqlQuery, SqlUpdate, SimpleJdbcInsert are used frequently.
Follow the following steps to use them.

1. Compose sql query statement, and declare parameters used in the sql statement.
2. Call compile() method from super class to prepare the sql statement.
3. Call execute methods to execute the query.

MappingSqlQuery

MappingSqlQuery is designated for executing a select sql query.

1. Create a custom class to subclass MappingSqlQuery.
   

   private static final String SELECT_BY_SLUG_SQL = "select * from conference where slug=?";
   
   private class FindBySlug extends MappingSqlQuery {
   
       private String slug;
   
       public FindBySlug(DataSource ds, String sql, String slug) {
           super(ds, sql);
           this.slug = slug;
           declareParameter(new SqlParameter(Types.VARCHAR));
           compile();
       }
   
       public Conference go() {
           List confs= super.execute(slug);
           if(!confs.isEmpty()){
               return confs.get(0);
           }
           return null;
       }
   
       @Override
       protected Conference mapRow(ResultSet rs, int rowNum)
               throws SQLException {
           return SimpleJdbcConferenceDaoImpl.this.mapRow(rs);
       }
   
   }
   

   In the constructor declaration FindBySlug, a DataSource object, a sql string, and a slug parameter will be required to pass in when construct a new FindBySlug object.
   Then call declareParameter to declare the object type that will be passed in later.
   At last call compile to prepare the sql statement.
   Beside these, you have to implement your own mapRow method to wrap the return result object from the Jdbc ResultSet.
   

   private Conference mapRow(ResultSet rs) throws SQLException {
       Conference conference = new Conference();
       conference.setName(rs.getString("name"));
       conference.setDescription(rs.getString("description"));
       conference.setSlug(rs.getString("slug"));
       conference.setStartedDate(rs.getDate("started_date"));
       conference.setEndedDate(rs.getDate("ended_date"));
       conference.setId(rs.getLong("id"));
       return conference;
   }
   

   I provides a go method to call the execute method to execute the query and return the query results.
   The execute method also has some variants, please explore the MappingSqlQuery class source for more details.
   
2. In your business method, new a FindBySlug object and call go to get the result.
   

   @Override
   public Conference findBySlug(String slug) {
       try {
           return new FindBySlug(dataSource, SELECT_BY_SLUG_SQL, slug).go();
       } catch (Exception e) {
           e.printStackTrace();
       }
       return null;
   }
   

MappingSqlQuery is a generalized version of SqlQuery, there are some other SqlQuery API may be useful.
Using UpdatableSqlQuery you can execute a updatable sql query(you have to implement your own updateRow method).
SqlFunction is use for fetch a single object from select clause.

SqlUpdate

As the name indicates, it is use for executing a modifying query, including insert, update and delete operations.
The steps are similar with using MappingSqlQuery.

1. Create your own SqlUpdate subclass.
   

   private static final String DELETE_BY_ID_SQL = "delete from conference where id=?";
   
   private class DeleteById extends SqlUpdate {
   
       private Long id;
   
       public DeleteById(DataSource ds, String sql, Long id) {
           super(ds, sql);
           this.id = id;
           declareParameter(new SqlParameter(Types.NUMERIC));
           compile();
       }
   
       public int go() {
           return super.update(id);
       }
   
   }
   

   The SqlUpdate provides several update variants to execute a sql update.
   
2. New a DeleteById object.
   

   @Override
   public void delete(final Long id) {
       new DeleteById(dataSource, DELETE_BY_ID_SQL, id).go();
   }
   

   The above codes demonstrated a delete operation, others are similar, such as update operation.
   

   private static final String UPDATE_SQL = "update conference set slug=?, name=?, description=?, started_date=?, ended_date=? where id =?";
   
   private class ConferenceUpdate extends SqlUpdate {
   
       private Conference conference;
   
       public ConferenceUpdate(DataSource ds, String sql, Conference conference) {
           super(ds, sql);
           this.conference = conference;
           declareParameter(new SqlParameter(Types.VARCHAR));
           declareParameter(new SqlParameter(Types.VARCHAR));
           declareParameter(new SqlParameter(Types.VARCHAR));
           declareParameter(new SqlParameter(Types.TIMESTAMP));
           declareParameter(new SqlParameter(Types.TIMESTAMP));
           declareParameter(new SqlParameter(Types.NUMERIC));
           compile();
       }
   
       public int go() {
           return super
                   .update(conference.getSlug(), conference.getName(),
                           conference.getDescription(),
                           new java.sql.Timestamp(conference.getStartedDate()
                                   .getTime()), new java.sql.Timestamp(
                                   conference.getEndedDate().getTime()),
                           conference.getId());
       }
   }
   

   You can also use SqlUpdate to execute an insert operation, and return the generated primary key, a variant of update method can accept a KeyHolder type parameter which can hold the generated key after the update is called. The usage of KeyHolder is demonstrated in before post, I do not want to repeat the steps here.
   

SimpleJdbcInsert

SimpleJdbcInsert does not belong to the inheritance of SqlQuery API. It is a standalone API provided more simple steps for insert sql operation.
The usage of SimpleJdbcInsert is every similar with SqlUpdate and MappingSqlQuery.

1. Create your own SimpleJdbcInsert subclass.
   

   private class ConferencenJdbcInsert extends SimpleJdbcInsert {
   
       private Conference conference;
   
       public ConferencenJdbcInsert(DataSource dataSource,
               Conference conference) {
           super(dataSource);
           this.conference = conference;
           this.withTableName("conference")
                   .usingColumns("name", "slug", "description",
                           "started_date", "ended_date")
                   .usingGeneratedKeyColumns("id");
           compile();
       }
   
       public Long go() {
           Map params = new HashMap();
           params.put("name", conference.getName());
           params.put("slug", conference.getSlug());
           params.put("description", conference.getDescription());
           params.put("started_date", new java.sql.Timestamp(conference
                   .getStartedDate().getTime()));
           params.put("ended_date", new java.sql.Timestamp(conference
                   .getEndedDate().getTime()));
   
           return super.executeAndReturnKey(params).longValue();
       }
   
   }
   

   A little difference is you do not need to compose a sql clause and call declareParameters method, instead, a fluid API is ready for specifying the table and columns will be affected.
   And the method to get the returned generated key also can be simplified.
   
2. Use it.
   

   @Override
   public Long save(final Conference conference) {
       return new ConferencenJdbcInsert(dataSource, conference).go();
   }
   

Summary

As you see, wrapping a single sql query into a single object make the application more maintainable than before.
And you are also free from subclassing a JdbcDaoSupport and writing many anonymous inner classes when using JdbcTemplate.

Check out the codes from my github.com,  https://github.com/hantsy/spring-sandbox.