【开源中国 APP 全新上线】“动弹” 回归、集成大模型对话、畅读技术报告”
在用JDBC进行数据库的操作的时候会在每个DAO中写有很多重复和类似的代码(建立连接,关闭连接等等),Spring的JDBC框架对这些重复的操作做了提取,形成了模板,使用Spring的JDBC框架的开发者通过提供SQL语句和在必要的时候提供callback类(用于提供更新操作的值和从返回结果集中提取返回结果),开发者就不用再去写那些重复的建立连接,关闭连接的代码了,这样不但减少了代码量,同时也避免了如忘记关闭数据库连接这类的错误.
Spring提供了如下的类来实现JDBC的基本操作和错误处理:
1,JdbcTemplate
类:完成了资源的创建以及释放工作,从而简化了我们对JDBC的使用.JdbcTemplate将完成JDBC核心处理流程,比如建立连接,Statement的创建、SQL执行,关闭连接等.而把SQL语句的编写以及查询结果的提取工作留给我们的应用代码。它可以完成SQL查询、更新以及调用存储过程,可以对ResultSet
进行遍历并加以提取。
2,NamedParameterJdbcTemplate
类:在传统的SQL语句中,参数都是用'?'
占位符来表示的.NamedParameterJdbcTemplate可以通过冒号(:)加参数名的方式来代表
占位符(类似于PL/SQL),从而是SQL代码更可读.
3,SimpleJdbcTemplate
类:它利用了Java 5的一些语言特性,比如Varargs和Autoboxing.(在Spring3.0中,JdbcTemplate也支持
Varargs和Autoboxing.)
上面3个类的关系是:在最底层的调用基本上都是通过JdbcTemplate来完成的,
NamedParameterJdbcTemplate有一个
JdbcTemplate成员变量,
SimpleJdbcTemplate有一个
NamedParameterJdbcTemplate成员变量.
4,SimpleJdbcCall类:主要用来调用承储过程和函数.
开发的常用模式(Best Practice):在访问数据库的DAO类中注入datasource,构建jdbctemplate,使用jdbctemplate完成JDBC操作.
[另外.最好是继承 JdbcDaoSupport类,这样就不用在自己的DAO类中定义JdbcTemplate成员变量和写setDataSource方法了.]
public class JDBCTestDAO { private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } ...... }对应的配置:
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" /> <property name="url" value="jdbc:oracle:thin:@localhost:1521:ORCL" /> <property name="username" value="user" /> <property name="password" value="pwd" /> </bean> <bean id="JDBCTestDAO" class="com.test.spring.dao.JDBCTestDAO"> <property name="dataSource" ref="dataSource" /> </bean>对应的数据库表为:
Create table a(id number,val varchar2(50)); 1,Insert: public void testInsert() { this.jdbcTemplate.update( "insert into A (ID, VAL) values (?, ?)", "1", "val1"); } 2,update: public void testUpdate() throws Exception { this.jdbcTemplate.update( "update A set val = ? where id = ?","val_bf1", "1"); } 3,delete: public void testDelete() { this.jdbcTemplate.update( "delete from A"); }4,查找单行:RowMapper的mapRow方法会被回调,用来从结果集中取值.
public void testSelectSingle(int id) { String sql = "select id, val from A" + " where id = ?"; RowMapper<TableA> mapper = new RowMapper<TableA>() { public TableA mapRow(ResultSet rs, int rowNum) throws SQLException { TableA rcd = new TableA(); rcd.setId(rs.getInt("id")); rcd.setVal(rs.getString("val")); return rcd; } }; //3.0之后JdbcTemplate也支持可变长度参数 // TableA record = (TableA) this.getJdbcTemplate().queryForObject(sql,new Object[] { id }, mapper); TableA record = (TableA) this.getJdbcTemplate().queryForObject(sql, mapper, id); System.out.println(record.getVal()); }5,查找多行:RowMapper的mapRow方法会在结果集每次循环的时候被回调,用来从结果集中取值.
public void testSelectMutiple() { String sql = "select id, val from A"; RowMapper<TableA> mapper = new RowMapper<TableA>() { public TableA mapRow(ResultSet rs, int rowNum) throws SQLException { TableA rcd = new TableA(); rcd.setId(rs.getInt("id")); rcd.setVal(rs.getString("val")); return rcd; } }; List<TableA> records = this.getJdbcTemplate().query(sql, mapper); /* * for (Iterator<TableA> itr = records.iterator(); itr.hasNext();) { * TableA recd = itr.next(); System.out.println(recd.getVal()); } */ for (TableA recd : records) { System.out.println(recd.getVal()); } }6,批量插入和更新:BatchPreparedStatementSetter的setValues方法会被回调用于给statement赋值.
public void testbatchInsert(final List<TableA> records) { String sql = "insert into A values(?,?)"; int[] updateCounts = this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setInt(1, records.get(i).getId()); ps.setString(2, records.get(i).getVal()); } public int getBatchSize() { return records.size(); } }); }7,利用SimpleJdbcCall调用stored procedure:对应的数据库SP为:
CREATE OR REPLACE procedure testproc( in_id in integer, out_id out integer, out_val out varchar2) as begin select id,val into out_id,out_val from a where id= in_id; end; / public class JDBCCallDAO extends JdbcDaoSupport { private SimpleJdbcCall procCaller; protected void initTemplateConfig() { this.procCaller = new SimpleJdbcCall(this.getDataSource()).withProcedureName("TESTPROC"); } //通过字段的metadata进行类型匹配 public void testdefaultCallSP(int id) { SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id); Map out = procCaller.execute(in); System.out.println(out.get("OUT_ID")); System.out.println((String) out.get("OUT_VAL")); } // 通过显示指定字段的类型 public void testExplicitCallSP(int id) { SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id); procCaller.withoutProcedureColumnMetaDataAccess(); procCaller.useInParameterNames("IN_ID"); procCaller.declareParameters(new SqlParameter("in_id", Types.NUMERIC), new SqlOutParameter("OUT_ID", Types.NUMERIC), new SqlOutParameter("OUT_VAL", Types.VARCHAR)); Map out = procCaller.execute(in); System.out.println(out.get("OUT_ID")); System.out.println((String) out.get("OUT_VAL")); } ...... }8,操作LOB类型的字段:对应的数据库表为:
create table lobtable(id number,b_lob blob,c_lob clob); public class LobDAO { private JdbcTemplate jdbcTemplate; private LobHandler lobHandler = new DefaultLobHandler(); public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } public void testInsert(int id) throws Exception { final int v_id = id; final byte[] blobIn = "BLOB TEST".getBytes(); final InputStream blobIs = new ByteArrayInputStream(blobIn); final byte[] clobIn = "CLOB TEST".getBytes(); final InputStream clobIs = new ByteArrayInputStream(clobIn); final InputStreamReader clobReader = new InputStreamReader(clobIs); jdbcTemplate.execute("INSERT INTO lobtable (id, c_lob, b_lob) VALUES (?, ?, ?)", new AbstractLobCreatingPreparedStatementCallback(lobHandler) { protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException { ps.setInt(1, v_id); lobCreator.setClobAsCharacterStream(ps, 2, clobReader, (int) clobIn.length); lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, (int) blobIn.length); } }); blobIs.close(); clobReader.close(); } public void testUpdate(int id) throws Exception { final int v_id = id; final byte[] clobIn = "CLOB TEST UPDATE".getBytes(); final InputStream clobIs = new ByteArrayInputStream(clobIn); final InputStreamReader clobReader = new InputStreamReader(clobIs); jdbcTemplate.execute("UPDATE lobtable set c_lob=? where id=? ", new AbstractLobCreatingPreparedStatementCallback(lobHandler) { protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException { lobCreator.setClobAsCharacterStream(ps, 1, clobReader, (int) clobIn.length); ps.setInt(2, v_id); } }); clobReader.close(); } public void testReadLob() { List<Map<String, Object>> l = jdbcTemplate.query("select id, c_lob, b_lob from lobtable", new RowMapper<Map<String, Object>>() { public Map<String, Object> mapRow(ResultSet rs, int i) throws SQLException { Map<String, Object> results = new HashMap<String, Object>(); String clobText = lobHandler.getClobAsString(rs, "c_lob"); results.put("C_LOB", clobText); byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "b_lob"); results.put("B_LOB", blobBytes); return results; } }); for(Map<String, Object> m:l){ System.out.println(m.get("C_LOB")); byte[] blob = (byte[])m.get("B_LOB"); System.out.println(new String(blob)); } } }默认情况下,jdbctemplate的每一个jdbc操作完成后对会提交到数据库,可以通过Spring的事务声明把这些操作组合到一个事务中而完成真正的商业逻辑.
原文链接: http://blog.csdn.net/kkdelta/article/details/5567520