《Spring 2.0技术手册》 读书笔记七-Spring的DAO框架（2）-JDBC支持

在上篇笔记中，说道使用JDBC存取数据库时，流程大体相同，反复使用较为繁琐。因此Spring采用了Template-Callback模式来简化JDBC使用时的流程。

Spring提供了org.springframework.jdbc.core.JdbcTemplate类，它被设计为线程安全，它提供的一些操作方法封装了JDBC流程。使用JdbcTemplate，首先需要一个DataSource对象作为构造对象:JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource)。然后就可以使用JdbcTemplate提供的update/execute/query等系列方法执行具体存取操作了。省去了自己建立连接、创建statement等。

DAO实现类中需有注入DataSource的方法，此方法使用dataSource初始化JdbcTemplate。

private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource){ jdbcTemplate=new JdbcTemplate(dataSource); } /*直接使用JdbcTemplate进行数据存取*/ public void create(){ jdbcTemplate.execute("create table user(id int primary key, name varchar(20))"); }

Bean定义文件中进行配置：

<bean id="userDAO" class="org.spring.dao.UserDAO"> <property name="dataSource" ref="dataSource"></property> </bean> 

接着对JdbcTemplate的常用方法进行分类介绍如下：

1. 执行静态陈述

public void execute(final String sql)：执行DDL statement，包括create table/index，alter table/index，drop table/index。

2. 更新表格：DML statement-insert,update,delete。使用update系列函数

public int update(final String sql); public int update(String sql, Object... args); jdbcTemplate.update( "insert into user(id,name) values(?,?)", new Object[]{id,name}); public int update(PreparedStatementCreator psc); //org.springframework.jdbc.core.PreparedStatementCreator接口定义 public interface PreparedStatementCreator { /** * Create a statement in this connection. Allows implementations to use * PreparedStatements. The JdbcTemplate will close the created statement. */ PreparedStatement createPreparedStatement(Connection con) throws SQLException; } //使用例子 jdbcTemplate.update(new PreparedStatementCreator(){ public PreparedStatement createPreparedStatement(Connection con) throws SQLException { String sql="insert into user(name) values(?)"; PreparedStatement preStmt=con.prepareStatement(sql); preStmt.setString(1, name); return preStmt; } }); //与update(PreparedStatementCreator psc)互补 public int update(String sql, PreparedStatementSetter pss); //org.springframework.jdbc.core.PreparedStatementSetter定义 public interface PreparedStatementSetter { /** * Set parameter values on the given PreparedStatement. * @param ps the PreparedStatement to invoke setter methods on */ void setValues(PreparedStatement ps) throws SQLException; } //使用例子 jdbcTemplate.update( "insert into user(name) values(?)", new PreparedStatementSetter(){ public void setValues(PreparedStatement ps)throws SQLException { ps.setString(1, name); }} ); /* 批处理更新语句:首先，实现org.springframework.jdbc.core.BatchPreparedStatementSetter接口。 然后，使用JdbcTemplate的方法public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) */ public int[] insertUsers(List<User> us){ final List<User> users=us; String sql1="insert into user (name) values (?)"; BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter(){ /** * Return the size of the batch. * @return the number of statements in the batch */ public int getBatchSize() { return users.size(); } /** * Set parameter values on the given PreparedStatement. * @param ps the PreparedStatement to invoke setter methods on * @param i index of the statement we're issuing in the batch, starting from 0 */ public void setValues(PreparedStatement ps, int i) throws SQLException { User user=users.get(i); ps.setString(1, user.getName()); } }; return jdbcTemplate.batchUpdate(sql1, setter); } 

3. 查询.使用queryForXXX系列

public int queryForInt(String sql); public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType); //每一个Map代表一行数据，key为列名，object为值 public List<Map<String, Object>> queryForList(String sql); /*对查询数据进行处理再传回。 使用org.springframework.jdbc.core.RowCallbackHandler接口 */ public interface RowCallbackHandler { /** * Implementations must implement this method to process each row of data * in the ResultSet. This method should not call <code>next()</code> on * the ResultSet; it is only supposed to extract values of the current row. * <p>Exactly what the implementation chooses to do is up to it: * A trivial implementation might simply count rows, while another * implementation might build an XML document. * @param rs the ResultSet to process (pre-initialized for the current row) * @throws SQLException if a SQLException is encountered getting * column values (that is, there's no need to catch SQLException) */ void processRow(ResultSet rs) throws SQLException; } /*使用JdbcTemplate的查询函数*/ public void query(String sql, Object[] args, RowCallbackHandler rch); //例子 public User find1(Integer id){ final User user=new User(); jdbcTemplate.query( "select * from user where id=?", new Object[]{id}, new RowCallbackHandler(){ public void processRow(ResultSet rs) throws SQLException { user.setName(rs.getString("name")); } }); return user; }

对查询结果进行封装-ORM(对象关系映射)。除了上面提到的使用RowCallbackHandler进行关系-对象映射，还可以使用org.springframework.jdbc.core.RowMapper接口，实现单行、多行封装。使用例子如下：

public interface RowMapper<T> { /** * Implementations must implement this method to map each row of data * in the ResultSet. This method should not call <code>next()</code> on * the ResultSet; it is only supposed to map values of the current row. * @param rs the ResultSet to map (pre-initialized for the current row) * @param rowNum the number of the current row * @return the result object for the current row */ T mapRow(ResultSet rs, int rowNum) throws SQLException; } //例子 /*单独定义RowMapper，达到重用。也可以在查询方法中定义RowMapper*/ class UserRowMapper implements RowMapper<User>{ public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user=new User(); user.setId(rs.getInt("id")); user.setName(rs.getString("name")); return null; } } //单行封装 public User find2(Integer id){ User user =(User)jdbcTemplate.queryForObject( "select * from user where id=?", new Object[]{id}, new UserRowMapper()); return user; } //多行封装 public List<User> find3(){ List<User> users=jdbcTemplate.query("select * from user", new UserRowMapper()); return users; } 

4. 存取Lob

在JDBC中可以使用Clob(character large object-文字文本文件)与Blob(Binary large object-图像等二进制文件)，JdbcTemplate也可以进行大对象的便捷存取。储存大对象使用 public <T> T execute(String sql, PreparedStatementCallback<T> action)方法，读取大对象使用 public <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse)。具体过程如下：

public void storeLob() throws IOException{ final File binaryFile=new File("D://wish.jpg"); final File txtFile=new File("D://test.txt"); //转换为流，读到内存 final InputStream is=new FileInputStream(binaryFile); final Reader reader=new FileReader(txtFile); final LobHandler lobHandler=new DefaultLobHandler(); jdbcTemplate.execute("insert into testLob (image,txt) values(?,?)", new AbstractLobCreatingPreparedStatementCallback(lobHandler){ protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException { lobCreator.setBlobAsBinaryStream( ps, 1, is, (int)binaryFile.length()); lobCreator.setClobAsCharacterStream( ps, 2, reader, (int)txtFile.length()); } }); reader.close(); is.close(); } //读取BLob/Clob public void readLob() throws IOException{ final OutputStream os=new FileOutputStream(new File("D://wish1.jpg")); final Writer writer=new FileWriter("D://test1.txt"); final LobHandler lobHandler=new DefaultLobHandler(); jdbcTemplate.query("select image,txt from testLob where id=?", new Object[]{1}, new AbstractLobStreamingResultSetExtractor(){ protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException { FileCopyUtils.copy( lobHandler.getBlobAsBinaryStream(rs, 1), os); FileCopyUtils.copy( lobHandler.getClobAsCharacterStream(rs,2), writer); } }); os.close(); writer.close(); } 

在建立org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback（抽象类）对象时，需传递一个接口org.springframework.jdbc.support.lob.LobHandler实例，对于MySQL、SQL Server、Oracle 10g，LobHander实例使用org.springframework.jdbc.support.lob.DefaultLobHandler。对于oracle 9i可以使用OracleLobHandler。在setValues()方法实现中，使用org.springframework.jdbc.support.lob.LobCreator分别设置Blob与Clob的来源串流，索引1/2表示第一与第二个占位字符'?'的位置，并指定读取长度。

从数据库读取大对象文件时，使用了org.springframework.util.FileCopyUtils的copy方法，将LobHandler取的串流直接转接给文件输出FileWriter/FileOutputStream对象。

下面贴出，抽象类AbstractLobCreatingPreparedStatementCallback的部分定义、接口LobCreator的方法、抽象类AbstractLobStreamingResultSetExtractor部分定义、抽象类FileCopyUtils的copy方法、接口LobHandler的部分方法。

public abstract class AbstractLobCreatingPreparedStatementCallback implements PreparedStatementCallback<Integer>{ /** * Set values on the given PreparedStatement, using the given * LobCreator for BLOB/CLOB arguments. * @param ps the PreparedStatement to use * @param lobCreator the LobCreator to use * @throws SQLException if thrown by JDBC methods * @throws DataAccessException in case of custom exceptions */ protected abstract void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException; } public interface LobCreator { void setBlobAsBytes(PreparedStatement ps, int paramIndex, byte[] content) throws SQLException; void setBlobAsBinaryStream( PreparedStatement ps, int paramIndex, InputStream contentStream, int contentLength) throws SQLException; void setClobAsString(PreparedStatement ps, int paramIndex, String content) throws SQLException; void setClobAsAsciiStream( PreparedStatement ps, int paramIndex, InputStream asciiStream, int contentLength) throws SQLException; void setClobAsCharacterStream( PreparedStatement ps, int paramIndex, Reader characterStream, int contentLength) throws SQLException; void close(); } public abstract class AbstractLobStreamingResultSetExtractor implements ResultSetExtractor { /** * Stream LOB content from the given ResultSet to some OutputStream. * <p>Typically used as inner class, with access to surrounding method arguments * and to a LobHandler instance variable of the surrounding class. * @param rs the ResultSet to take the LOB content from * @throws DataAccessException in case of custom exceptions * @see org.springframework.jdbc.support.lob.LobHandler#getBlobAsBinaryStream * @see org.springframework.util.FileCopyUtils */ protected abstract void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException; } public abstract class FileCopyUtils { /** * Copy the contents of the given InputStream to the given OutputStream. * Closes both streams when done. */ public static int copy(InputStream in, OutputStream out){}; /** * Copy the contents of the given Reader to the given Writer. * Closes both when done. */ public static int copy(Reader in, Writer out){}; } public interface LobHandler { /** * Retrieve the given column as binary stream from the given ResultSet. * Might simply invoke <code>ResultSet.getBinaryStream</code> or work with * <code>ResultSet.getBlob</code>, depending on the database and driver. */ InputStream getBlobAsBinaryStream(ResultSet rs, int columnIndex) throws SQLException; /** * Retrieve the given column as character stream from the given ResultSet. * Might simply invoke <code>ResultSet.getCharacterStream</code> or work with * <code>ResultSet.getClob</code>, depending on the database and driver. */ Reader getClobAsCharacterStream(ResultSet rs, int columnIndex) throws SQLException; }