Spring 对JDBC操作的支持

在用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的事务声明把这些操作组合到一个事务中而完成真正的商业逻辑.