Beginning Spring学习笔记——第4章（二）使用Spring执行数据访问操作

使用JdbcTemplate运行查询

---

常用query(), queryForObject(), queryForList(), queryForMap(), queryForRowSet()以及它们的重载版本进行查询
下queryForObject()实现AccountDaoJdbcImpl中的find(long accountId)方法

public Account find(long accountId) {        return jdbcTemplate                .queryForObject(                        "select id,owner_name,balance,access_time,locked from account where id = ?",                        new RowMapper<Account>() {                            public 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;                            }                        }, accountId);    }

然后使用如下SQL语句插入一个账户

INSERT INTO ACCOUNT (ID, OWNER_NAME, BALANCE, ACCESS_TIME, LOCKED) VALUES (100, 'Van Gogh', 10.0 , '2017-08-29', false)

再在Main函数中使用accountDao Bean获取账户记录

public class Main {    public static void main(String[] args) throws SQLException {        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Ch4Configuration.class);        AccountDao accountDao = applicationContext.getBean(AccountDao.class);        Account account = accountDao.find(100L);        System.out.println(account.getId());        System.out.println(account.getOwnerName());        System.out.println(account.getBalance());        System.out.println(account.getAccessTime());        System.out.println(account.isLocked());    }}

得到结果

相比直接使用JDBC，这种方法在模板类的回调方法中封装了所有需要的数据访问逻辑。
在查询中我们还可以使用命名参数，首先在数据访问实现类中定义命名参数,再用它实现find方法返回有相同用户名的所有账户。

public class AccountDaoJdbcImpl implements AccountDao {    private JdbcTemplate jdbcTemplate;    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {        this.jdbcTemplate = jdbcTemplate;        namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);    }    public List<Account> find(String ownerName) {        return namedParameterJdbcTemplate.query(                "select id,owner_name,balance,access_time,locked from account where owner_name = :ownerName"                , Collections.singletonMap("ownerName", ownerName),                 new RowMapper<Account>() {                    public 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;                    }                });    }

还可以使用PreparedStatement，使得多次执行相同查询的预处理步骤只执行一次从而节省时间。

public List<Account> find(final boolean locked) {        PreparedStatementCreatorFactory psCreatorFactory = new PreparedStatementCreatorFactory(                "select * from account where locked = ?",                new int[] { Types.BOOLEAN });        return jdbcTemplate.query(psCreatorFactory                .newPreparedStatementCreator(new Object[] { locked }),                new RowMapper<Account>() {                    public 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;                    }                });    }

以上代码为获取某种锁定状态的所有账户的查找。

使用JdbcTemplate插入、更新和删除记录

---

通过JdbcTemplate的update()及其重载方法实现。
首先创建三个异常类表示插入、更新和删除失败，在三个函数失败时创建。

public class InsertFailedException extends DataAccessException {    public InsertFailedException(String msg) {        super(msg);    }}public class UpdateFailedException extends DataAccessException {    public UpdateFailedException(String msg) {        super(msg);    }}public class DeleteFailedException extends DataAccessException {    public DeleteFailedException(String msg) {        super(msg);    }}

然后创建插入函数

public void insert(Account account) {        PreparedStatementCreatorFactory psCreatorFactory = new PreparedStatementCreatorFactory(                "insert into account(owner_name,balance,access_time,locked) values(?,?,?,?)",                new int[] { Types.VARCHAR, Types.DOUBLE, Types.TIMESTAMP,                        Types.BOOLEAN });        KeyHolder keyHolder = new GeneratedKeyHolder();        int count = jdbcTemplate.update(                psCreatorFactory.newPreparedStatementCreator(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());    }

值得注意的是为了防止主键ID的重复，此处的ID为KeyHolder自动生成。插入操作由PreparedStatement完成。
然后创建更新函数：

public void update(Account account) {        int count = jdbcTemplate                .update("update account  set (owner_name,balance,access_time,locked) = (?,?,?,?) where id = ?",                        account.getOwnerName(), account.getBalance(),                        account.getAccessTime(), account.isLocked(),                        account.getId());        if (count != 1)            throw new UpdateFailedException("Cannot update account");    }

此处直接用JdbcTemplate完成。
最后创建删除函数：

public void delete(long accountId) {        int count = jdbcTemplate.update("delete account where id = ?",                accountId);        if (count != 1)            throw new DeleteFailedException("Cannot delete account");    }

使用SimpleJdbcCall调用和储存过程

---

在Main函数中创建SimpleJdbcCall实例，指定要执行的储存过程名并声明输入输出参数，若返回ResultSet则分配名称并用RowMapper处理。然后用compile()编译过程，用execute执行。

public class Main {    public static void main(String[] args) throws SQLException {        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Ch4Configuration.class);        JdbcTemplate jdbcTemplate = applicationContext.getBean(JdbcTemplate.class);        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate);        simpleJdbcCall        .withProcedureName("concat")        .withoutProcedureColumnMetaDataAccess()        .declareParameters(                new SqlParameter("param1", Types.VARCHAR),                new SqlParameter("param2", Types.VARCHAR)).                returningResultSet("result", new SingleColumnRowMapper<String>(String.class));        simpleJdbcCall.compile();        Map<String, Object> paramMap = new HashMap<String, Object>();        paramMap.put("param1", "hello ");        paramMap.put("param2", "world!");        Map<String,Object> resultMap = simpleJdbcCall.execute(paramMap);        List<String> resultList = (List<String>) resultMap.get("result");        for(String value:resultList) {            System.out.println(value);        }    }}

运行得到结果，拼接了两个String实例。

用batchUpdate执行批处理操作

在一个PreparedStatement中执行一系列更新操作以减少往返数据库次数，提高性能

public void update(final List<Account> accounts) {        int[] counts = jdbcTemplate.batchUpdate(                "update account set (owner_name,balance,access_time,locked) = (?,?,?,?) where id = ?",                new BatchPreparedStatementSetter() {                                public void setValues(PreparedStatement ps, int i) throws SQLException {                            Account account = accounts.get(i);                            ps.setString(1, account.getOwnerName());                            ps.setDouble(2, account.getBalance());                            ps.setTimestamp(3, new Timestamp(account.getAccessTime().getTime()));                            ps.setBoolean(4, account.isLocked());                            ps.setLong(5, account.getId());                        }                        public int getBatchSize() {                            return accounts.size();                        }                    });                    int i = 0;                    for(int count:counts) {                        if(count == 0) throw new UpdateFailedException("Row not updated :" + i);                        i++;                    }    }

以上代码对列表accounts内的所有用户进行了更新。

处理LOB对象

---

分CLOB(Character Large Object)和BLOB(Binary Large Object)两种，用于处理二进制大型数据和文本大型数据。都使用LobHandler和LobCreator接口处理，分别用来访问和设置LOB值。

final LobHandler lobHandler = new DefaultLobHandler();        final String textualContent = "test";        final byte[] binaryContent = textualContent.getBytes();        final long accountId = 100L;        jdbcTemplate                .update("update account set (owner_photo,account_desc) = (?,?) where id = ? ",                        new PreparedStatementSetter() {                            public void setValues(PreparedStatement ps)                                    throws SQLException {                                LobCreator lobCreator = lobHandler                                        .getLobCreator();                                lobCreator.setBlobAsBytes(ps, 1, binaryContent);                                lobCreator.setClobAsString(ps, 2,                                        textualContent);                                ps.setLong(3, accountId);                            }                        });