使用spring进行JDBC数据访问（下）

4.4将jdbc操作模块化为java对象

4.4.1 封装sql执行查询

使用该技术的主要好处是帮助我们将sql查询封装为java对象，并在多个不同的地方使用。

（1）创建一个AccountByIdQuery继承MappingSqlQuery类的查询类

public class AccountByIdQuery extends MappingSqlQuery<Account> {
   
	public AccountByIdQuery(DataSource dataSource){
		//向该超类传入一个DataSource，和sql查询语句
		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;
	}

}

（2）向AccountDaoJdbcImpl 类中添加MappingSqlQuery类型的属性

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

(3)为AccountByIdQuery定义一个Bean，并注入DataSource作为构造函数参数。然后再向accountDao Bean中注入MappingSqlQuery Bean：

//封装sql语句
  @Bean
  public MappingSqlQuery<Account> accountByIdQuery(){
	     AccountByIdQuery query =new AccountByIdQuery(dataSource());
	     return query;
  }
  
  @Bean
  public AccountDao accountDao() throws SQLException{
	  AccountDaoJdbcImpl accountDao = new AccountDaoJdbcImpl();
	  accountDao.setJdbcTemplate(jdbcTemplate());
	  //sql数据封装
	  accountDao.setAccountByIdQuery(accountByIdQuery());
	  return accountDao;
  }

4.2.2 封装sql dml 操作

（1）更新

//更新的数据库操作封装成对象
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();
   }
}

(2)删除

public class AccountDelete extends SqlUpdate {

	public AccountDelete(DataSource dataSource) {
		 super(dataSource,"delete from account where id=?");
		 setParameters(new SqlParameter[]{
				 new SqlParameter(Types.BIGINT)
		 });
		 //这个方法使对象插入
		 compile();
	}
}

4.3.2 封装存储过程执行p129