spring的JdbcTemplate基本使用

1.JdbcTemplate的概念

Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作。

2.JdbcTemplate的准备工作

2.1导入依赖包

 		<dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.1.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.1.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>

1. spring-jdbc是spring集成的jdbc（JdbcTemplate）
2. spring-tx坐标是spring的事务控制相关的

2.2 在配置文件中配置数据库连接池

 <!--配置jdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

        <property name="dataSource" ref="dataSource"></property>
    </bean>
<!--配置datasource  -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/springioc"></property>
        <property name="username" value="root"></property>
        <property name="password" value="850319"></property>
    </bean>

2.3 创建实体类

package com.itheima.domain;

public class Account {
    private Integer id;
    private String name;
    private Float money;

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Float getMoney() {
        return money;
    }
    public void setMoney(Float money) {
        this.money = money;
    }
    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

2.4 查询语句

 ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
//        添加一个
        jt.execute("insert into account(name,money)value('fff',1000)");
//        更新
        jt.update("update account set name=?,money=? where id=?","test",4567,7);
//        删除一行
        jt.update("delete from account where id=?",7);

这只是很简单的增删改。
但是在进行查询时需要在参数中添加一个AccountRowMapper的对象。如下所示

//查询一个
        List<Account> accounts2 = jt.query("select * from account where id=?",new AccountRowMapper(),2);
//查询所有
 List<Account> accounts = jt.query("select * from account where money>?",new AccountRowMapper(),500f);
 
 class AccountRowMapper implements RowMapper<Account>{
    @Override
    public Account mapRow(ResultSet rs,int rowNum) throws SQLException{
        Account account = new Account();
        account.setId(rs.getInt("id"));
        account.setName(rs.getString("name"));
        account.setMoney(rs.getFloat("money"));
        return account;
    }

2.5 对语句进行封装

Java spring中使用BeanPropertyRowMapper将数据库查询结果转换为Java类对象，省略了上一步自己RowMapper。

public class AccountDaoImpl implements IAccountDao {
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public Account findAccountById(Integer accountid) {
        List<Account> accounts = jdbcTemplate.query("select * from account where id=?",new BeanPropertyRowMapper<Account>(Account.class),accountid);
        return accounts.isEmpty()? null:accounts.get(0);
    }

    @Override
    public Account findAccountByName(String name) {
        List<Account> accounts = jdbcTemplate.query("select * from account where name=?",new BeanPropertyRowMapper<Account>(Account.class),name);
        if (accounts.isEmpty())return null;
        if(accounts.size()>1){
            throw new RuntimeException("结果不唯一");
        }
        return accounts.get(0);
    }

    @Override
    public void updateAccount(Account account) {
        jdbcTemplate.update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
    }

}