Spring JdbcTemplate执行数据访问

准备工作

在IDEA中新建maven项目

添加依赖

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>4.0.5.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>4.0.5.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>1.3.175</version>
    </dependency>
</dependencies>

其中spring-jdbc 和spring-context是spring的包

H2是一个轻量级的关系数据库。

打开h2数据库

在库文件中找到org.h2.tools.Console类，作为java应用程序运行。即可在网页中运行h2数据库。

在h2中建立account表

      

 create table account(
ID bigint identity primary key,
OWNER_NAME varchar(255),
BALANCE double,
ACCESS_TIME timestamp,
LOCKED boolean)

创建Account类

public class Account {
    private long id;
    private String ownerName;
    private double balance;
    private Date accessTime;
    private boolean locked;

    public Date getAccessTime() {
        return accessTime;
    }

    public double getBalance() {
        return balance;
    }

    public long getId() {
        return id;
    }

    public String getOwnerName() {
        return ownerName;
    }

    public boolean isLocked() {
        return locked;
    }

    public void setAccessTime(Date accessTime) {
        this.accessTime = accessTime;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    public void setId(long id) {
        this.id = id;
    }

    public void setLocked(boolean locked) {
        this.locked = locked;
    }

    public void setOwnerName(String ownerName) {
        this.ownerName = ownerName;
    }
}

创建AccountDao接口

public interface AccountDao {
    public void insert(Account account);
    public void update(Account account);
    public void delete(long id);
    public Account find(long accountId);
    public List<Account> find(List<Long> accountIds);
}

创建AccountDaoJdbcImpl类用来实现AccountDao

先在其中添加JdbcTemplate类属性以及set方法

public class AccountDaoJdbcImpl implements AccountDao {
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
        this.jdbcTemplate = jdbcTemplate;
    }
    public void insert(Account account){

    }
    public void update(Account account){

    }
    public void delete(long id){

    }
    public Account find(long id){
        return null;
    }
    public List<Account> find(List<Long> ids){
        return null;
    }
}

 

创建springbean配置类

@org.springframework.context.annotation.Configuration
public class Configuration {
    @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());
        return accountDao;
    }
}

分别有DataSource,JdbcTemplate,AccountDao三个Bean,依赖关系如代码所示

 

使用JdbcTemplate运行查询

在AccountDaoJdbcTemplate类中实现find（）方法。

public Account find(long id){
    String sql = "select * from account where id = ?";
    return jdbcTemplate.queryForObject(sql, new RowMapper<Account>() {
        public Account mapRow(ResultSet resultSet, int i) throws SQLException {
            Account account = new Account();
            account.setId(resultSet.getLong("id"));
            account.setOwnerName(resultSet.getString("owner_name"));
            account.setBalance(resultSet.getDouble("balance"));
            account.setAccessTime(resultSet.getTimestamp("access_time"));
            account.setLocked(resultSet.getBoolean("locked"));
            return account;
        }
    },id);
}

其中jdbcTemplate的queryForObject方法的三个参数。

第一个：被执行的sql查询语句，最后用可变参数对象作为查询输入参数。

第二个：从RowMapper接口创建的匿名类中返回获取到的Account对象。

第三个：查询语句中的输入参数。

SpringJdbc 和 普通 JDBC 的区别有：

一部分是查询和其参数。

另一部分是处理结果的逻辑。

JdbcTemplate 使用了模板方法，即把不同的算法作为参数传入方法，在需要时调用以知的算法。

JdbcTemplate 还使用了回调对象以及匿名类。

可以使用RowMapper 将ResultSet返回的每一行映射到一个结果对象。

针对ResultSet中的每一行调用mapRow()方法，从而使每一行映射到对于的结果对象。

RowMapper是无状态和可以重复使用的。

在h2数据库插入一条记录待查询

insert into account values(100,'john doe',10.0,'2014-01-01',false);

在main方法中使用accountDao bean获取记录

public class Main {
    public static void main(String[] args) throws SQLException {
        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Configuration.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());

    }

输出

使用命名参数进行查询

AccountDaoJdbcImpl中添加NamedParameterJdbcTemplate类型的属性

private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
    this.jdbcTemplate = jdbcTemplate;
    namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
}

使用NamedParameterJdbcTemplate实现dao中find方法。

public Account find(String ownerName){
    String sql = "select * from account where owner_name = :ownerName";
    return namedParameterJdbcTemplate.queryForObject(sql, Collections.singletonMap("ownerName", ownerName), new RowMapper<Account>() {
        public Account mapRow(ResultSet resultSet, int i) throws SQLException {
            Account account = new Account();
            account.setId(resultSet.getLong("id"));
            account.setOwnerName(resultSet.getString("owner_name"));
            account.setBalance(resultSet.getDouble("balance"));
            account.setAccessTime(resultSet.getTimestamp("access_time"));
            account.setLocked(resultSet.getBoolean("locked"));
            return account;
        }
    });
}

main方法查询记录

public class Main {
    public static void main(String[] args) throws SQLException {
        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Configuration.class);
        AccountDao accountDao = applicationContext.getBean(AccountDao.class);

        Account account =accountDao.find("john doe");

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

    }
}

使用IN子句编写查询

public List<Account> find(List<Long> ids){
    SqlParameterSource sqlParameterSource = new MapSqlParameterSource("ids",ids);
    String sql = "select * from account where id in (:ids)";
    return namedParameterJdbcTemplate.query(sql, sqlParameterSource, new RowMapper<Account>() {
        public Account mapRow(ResultSet resultSet, int i) throws SQLException {
            Account account = new Account();
            account.setId(resultSet.getLong("id"));
            account.setOwnerName(resultSet.getString("owner_name"));
            account.setBalance(resultSet.getDouble("balance"));
            account.setAccessTime(resultSet.getTimestamp("access_time"));
            account.setLocked(resultSet.getBoolean("locked"));
            return account;
        }
    });

}

这里首先需要一个SqlParameterSource类对象，用来输入参数。

而使用namedParameterJdbcTemplate对象的query来查询。其中的三个参数：

第一个，sql查询语句，其中的in后的输入参数用（：）来命名。

第二个，是开始定义的sqlParameterSource对象。

第三个，继续是回调方法，其中的mapRow方法返回每一行对于的查询对象。

JdbcTemplate中使用PreparedStatements

public List<Account> find(boolean islocked){
    String sql = "select * from account where locked = ?";
    PreparedStatementCreatorFactory preparedStatementCreatorFactory = new PreparedStatementCreatorFactory(sql,new int[]{Types.BOOLEAN});
    return jdbcTemplate.query(preparedStatementCreatorFactory.newPreparedStatementCreator(new Object[]{islocked}), new RowMapper<Account>() {
        public Account mapRow(ResultSet resultSet, int i) throws SQLException {
            Account account = new Account();
            account.setId(resultSet.getLong("id"));
            account.setOwnerName(resultSet.getString("owner_name"));
            account.setBalance(resultSet.getDouble("balance"));
            account.setAccessTime(resultSet.getTimestamp("access_time"));
            account.setLocked(resultSet.getBoolean("locked"));
            return account;
        }
    });
}

使用preparedStatements 与Statements的区别在于：

1，执行sql操作时分为解析，编译，执行等步骤。Statements每次都很进行预处理（解析，编译），而preparedStatements只预处理一次，提高了性能。

2. preparedStatements将查询条件以输入参数的方式执行，防止了sql注入攻击。

Spring中，先创建PreparedStatementCreatorFactory对象，然后用JdbcTemplate执行query.

Query方法中，factory创建creator对象。

插入，更新和删除记录

先新建插入，更新删除异常类

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

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

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

在daoImpl中实现这个个方法

public void insert(Account account){
    String sql = "insert into account(owner_name,balance,access_time,locked) values(?,?,?,?)";
    PreparedStatementCreatorFactory psCreatorFactory = new PreparedStatementCreatorFactory(sql,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("insert failed");
    account.setId(keyHolder.getKey().longValue());
}
public void update(Account account){
    String sql = "update account set(owner_name,balance,access_time,locked) = (?,?,?,?) where id = ?";
    int count = jdbcTemplate.update(sql,account.getOwnerName(),account.getBalance(),account.getAccessTime(),account.isLocked(),account.getId());
    if(count != 1)throw new UpdateFailedException("update failed");
}
public void delete(long id){
    String sql = "delete account where id = ?";
    int count =jdbcTemplate.update(sql,id);
    if(count != 1)throw new DeleteFailedException("delete failed");
}

这三个方法中都要用到JdbcTemplate的update方法。

其中insert方法由于创建的表有自动生成id的功能，所以新建的对象可以不用添加id.

最后在main方法中进行测试。

调用存储过程和存储函数

使用SimpleJdbcCall 和StoredProcudure可以调用存储过程和存储函数