跳至主要内容

Simplified Jdbc operations

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.

评论

此博客中的热门博文

AngularJS CakePHP Sample codes

Introduction This sample is a Blog application which has the same features with the official CakePHP Blog tutorial, the difference is AngularJS was used as frontend solution, and CakePHP was only use for building backend RESR API. Technologies AngularJS   is a popular JS framework in these days, brought by Google. In this example application, AngularJS and Bootstrap are used to implement the frontend pages. CakePHP   is one of the most popular PHP frameworks in the world. CakePHP is used as the backend REST API producer. MySQL   is used as the database in this sample application. A PHP runtime environment is also required, I was using   WAMP   under Windows system. Post links I assume you have some experience of PHP and CakePHP before, and know well about Apache server. Else you could read the official PHP introduction( php.net ) and browse the official CakePHP Blog tutorial to have basic knowledge about CakePHP. In these posts, I tried to follow the steps describ

JPA 2.1: Attribute Converter

JPA 2.1: Attribute Converter If you are using Hibernate, and want a customized type is supported in your Entity class, you could have to write a custom Hibernate Type. JPA 2.1 brings a new feature named attribute converter, which can help you convert your custom class type to JPA supported type. Create an Entity Reuse the   Post   entity class as example. @Entity @Table(name="POSTS") public class Post implements Serializable { private static final long serialVersionUID = 1L; @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name="ID") private Long id; @Column(name="TITLE") private String title; @Column(name="BODY") private String body; @Temporal(javax.persistence.TemporalType.DATE) @Column(name="CREATED") private Date created; @Column(name="TAGS") private List<String> tags=new ArrayList<>(); } Create an attribute convert

Auditing with Hibernate Envers

Auditing with Hibernate Envers The approaches provided in JPA lifecyle hook and Spring Data auditing only track the creation and last modification info of an Entity, but all the modification history are not tracked. Hibernate Envers fills the blank table. Since Hibernate 3.5, Envers is part of Hibernate core project. Configuration Configure Hibernate Envers in your project is very simple, just need to add   hibernate-envers   as project dependency. <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-envers</artifactId> </dependency> Done. No need extra Event listeners configuration as the early version. Basic Usage Hibernate Envers provides a simple   @Audited   annotation, you can place it on an Entity class or property of an Entity. @Audited private String description; If   @Audited   annotation is placed on a property, this property can be tracked. @Entity @Audited public class Signup implements Serializa