Simplified Jdbc Query

最新推荐文章于 2025-08-14 09:11:10 发布

转载最新推荐文章于 2025-08-14 09:11:10 发布 · 98 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/hantsy/blog/134418

文章标签：

#java

为什么80%的码农都做不了架构师？>>>

#Simplified Jdbc Query

As I stated in the last post, Spring DaoSupport and Template 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, you do not have to subclass this class. A SQL query(and modifying query) 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.

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

##MappingSqlQuery

MappingSqlQuery is designated for executing a select sql query.

Create a custom class to subclass MappingSqlQuery.

<pre> private static final String SELECT_BY_SLUG_SQL = "select * from conference where slug=?"; private class FindBySlug extends MappingSqlQuery<Conference> { 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<Conference> 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); } } </pre>

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.

Then 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.

<pre> 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; } </pre>

I provides a go method to call the execute or its variants to execute the query.

New a FindBySlug object and call go to get the result.

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

MappingSqlQuery is a more generic version of SqlQuery API, 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.

Create your own SqlUpdate subclass.

<pre> 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); } } </pre>

The SqlUpdate provides several update variants to execute a sql update.

New a DeleteById object.

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

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

<pre> 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()); } } </pre>

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 duplicate the codes 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.

Create your own SimpleJdbcInsert subclass.

<pre> 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<String, Object> params = new HashMap<String, Object>(); 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(); } } </pre>

A little difference is you do not need to 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.