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