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

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

@Configuration
public 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。