Beginning Spring学习笔记——第4章（三）将JDBC操作模块化为Java对象

封装SQL查询执行

---

创建一个扩展自MappingSqlQuery的类来封装用ID查询的SQL操作。

public class AccountByIdQuery extends MappingSqlQuery<Account> {    public AccountByIdQuery(DataSource dataSource) {        super(dataSource,                 "select id,owner_name,balance,access_time,locked from account where id = ?");        declareParameter(new SqlParameter(Types.BIGINT));        compile();    }    @Override    protected Account mapRow(ResultSet rs, int rowNum) throws SQLException {        Account account = new Account();        account.setId(rs.getLong("id"));        account.setOwnerName(rs.getString("owner_name"));        account.setBalance(rs.getDouble("balance"));        account.setAccessTime(rs.getTimestamp("access_time"));        account.setLocked(rs.getBoolean("locked"));        return account;    }}

使用时要先在数据访问实现类中添加MappingSqlQuery类型属性，并传入封装类的值

private MappingSqlQuery<Account> accountByIdQuery;public void setAccountByIdQuery(MappingSqlQuery<Account> accountByIdQuery) {    this.accountByIdQuery = accountByIdQuery;}

并在配置文件中注入相关依赖：

@Configurationpublic class Ch4Configuration {    @Bean    public DataSource dataSource() {        DriverManagerDataSource dataSource = new DriverManagerDataSource();        dataSource.setDriverClassName("org.h2.Driver");        dataSource.setUrl("jdbc:h2:tcp://localhost/~/test");        dataSource.setUsername("sa");        dataSource.setPassword("");        return dataSource;    }    @Bean    public JdbcTemplate jdbcTemplate() {        JdbcTemplate jdbcTemplate = new JdbcTemplate();        jdbcTemplate.setDataSource(dataSource());        return jdbcTemplate;    }    @Bean    public AccountDao accountDao() {        AccountDaoJdbcImpl accountDao = new AccountDaoJdbcImpl();        accountDao.setJdbcTemplate(jdbcTemplate());        accountDao.setAccountByIdQuery(accountByIdQuery());        return accountDao;    }    @Bean    public MappingSqlQuery<Account> accountByIdQuery() {        AccountByIdQuery query = new AccountByIdQuery(dataSource());        return query;    }}

这时实现类中的查找方法就只需要调用封装类实例的方法即可：

public Account find(long accountId) {        return accountByIdQuery.findObject(accountId);}

封装SQL DML操作

---

即插入更新和删除操作，三者的封装类都继承自SqlUpdate，如下：

public class AccountInsert extends SqlUpdate {    public AccountInsert(DataSource dataSource) {        super(dataSource,        "insert into account(owner_name,balance,access_time,locked) values(?,?,?,?)");        setParameters(new SqlParameter[] {                 new SqlParameter(Types.VARCHAR),                new SqlParameter(Types.DOUBLE),                new SqlParameter(Types.TIMESTAMP),                new SqlParameter(Types.BOOLEAN) });        setReturnGeneratedKeys(true);        setGeneratedKeysColumnNames(new String[]{"id"});        compile();    }}public class AccountDelete extends SqlUpdate {    public AccountDelete(DataSource dataSource) {        super(dataSource, "delete account where id = ?");        setParameters(new SqlParameter[]{new SqlParameter(Types.BIGINT)});        compile();    }}public class AccountUpdate extends SqlUpdate {    public AccountUpdate(DataSource dataSource) {        super(dataSource, "update account set (owner_name,balance,access_time,locked) = (?,?,?,?) where id=?");        setParameters(new SqlParameter[] {                 new SqlParameter(Types.VARCHAR),                new SqlParameter(Types.DOUBLE),                new SqlParameter(Types.TIMESTAMP),                new SqlParameter(Types.BOOLEAN),                new SqlParameter(Types.BIGINT)});        compile();    }}

同样需要在配置时注入相应Bean以及实现类中创建相应对象才能使用。

private SqlUpdate accountInsert;    private SqlUpdate accountUpdate;    private SqlUpdate accountDelete;    public void setAccountInsert(SqlUpdate accountInsert) {        this.accountInsert = accountInsert;    }    public void setAccountUpdate(SqlUpdate accountUpdate) {        this.accountUpdate = accountUpdate;    }    public void setAccountDelete(SqlUpdate accountDelete) {        this.accountDelete = accountDelete;    }//...    public void insert(Account account) {        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();        int count = accountInsert.update(new Object[]{account.getOwnerName(),account.getBalance(),account.getAccessTime(),account.isLocked()},keyHolder);        if (count != 1)            throw new InsertFailedException("Cannot insert account");        account.setId(keyHolder.getKey().longValue());    }    public void update(Account account) {        int count = accountUpdate.update(account.getOwnerName(),account.getBalance(),account.getAccessTime(),account.isLocked(),account.getId());        if (count != 1)            throw new UpdateFailedException("Cannot update account");    }    public void delete(long accountId) {        int count = accountDelete.update(accountId);        if (count != 1)            throw new DeleteFailedException("Cannot delete account");    }

封装储存过程的执行

---

这样的封装类扩展自StoredProcedure抽象类

public class ConcatStoredProcedure extends StoredProcedure {    public ConcatStoredProcedure(DataSource dataSource) {        setDataSource(dataSource);        setSql("concat");        declareParameter(new SqlParameter("param1",Types.VARCHAR));        declareParameter(new SqlParameter("param2",Types.VARCHAR));        compile();    }    public String execute(String param1, String param2) {        Map<String,Object> inParams = new HashMap<String,Object>();        inParams.put("param1", param1);        inParams.put("param2", param2);        Map<String, Object> map = execute(inParams);        List<Map> list = (List<Map>) map.get("#result-set-1");        return list.get(0).values().iterator().next().toString();    }}

在调用compile前使用输入输出参数设置其SQL属性，在公共方法execute中完成操作。

异常处理和错误代码转换

---

Spring JDBC抛出的所有异常均为DataAccessException的子类，因此只需要一个try-catch代码块就能捕捉任何层的Spring数据访问异常。
可用SQLException Translator将SQL Exception自动转化为DataAccessException。