一、JdbdTemplate基本使用

1.1JdbcTemplate基本使用概述

JdbcTemplate是Spring框架提供的一个对象，是对原始繁琐的jdbc api的封装。Spring框架为我们提供了很多操作的模板类。例如操作关系型数据库的JdbcTemplate和HibernateTemplate，操作nosql数据库的RedisTemplate等等。JdbcTemplate对象创建的源码如下：

public JdbcTemplate() {
    }

    public JdbcTemplate(DataSource dataSource) {
        this.setDataSource(dataSource);
        this.afterPropertiesSet();
    }

    public JdbcTemplate(DataSource dataSource, boolean lazyInit) {
        this.setDataSource(dataSource);
        this.setLazyInit(lazyInit);
        this.afterPropertiesSet();
    }
public void setDataSource(@Nullable DataSource dataSource) {
        this.dataSource = dataSource;
    }

上述除了默认构造参数之外，都需要提供一个数据源。既然JdbcTemplate中有set方法，那我们可以先在配置文件中配置这些对象，然后通过依赖注入的方式来配置这些对象。

1.2JdbcTemplate基本使用的开发步骤

1.2.1导入spring-jdbc坐标和spring-tx坐标

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <packaging>jar</packaging>

    <groupId>com.itheima</groupId>
    <artifactId>itheima_spring_jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.9.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.2.9.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.2.9.RELEASE</version>
        </dependency>

    </dependencies>

</project>

1.2.2 创建Account实体类对象

public class Account implements Serializable{
    private Integer id;
    private String name;
    private Double money;

    public Integer getId() {
        return id;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }
}

1.2.3 在spring配置文件中配置数据源以及JdbcTemplate

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
    <!--配置数据源对象-->
    <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mybatis"></property>
        <property name="user" value="root"></property>
        <property name="password" value="Shezeq1,"></property>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="datasource"></property>
    </bean>
</beans>

1.2.4JdbcTemplate的增删改查操作

• 最基本的使用

public class JdbcTemplateDemo1 {
    public static void main(String[] args) {
        //1.获取Spring容器
        ApplicationContext ac =  new ClassPathXmlApplicationContext("bean.xml");
        //2.根据id获取bean对象
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
        //3.执行操作
        jdbcTemplate.execute("insert  into account(name,money) values('吉姆巴特勒',200.34)");
    }
}

• 保存操作

public class JdbcTemplateDemo1 {
    public static void main(String[] args) {
        //1.获取Spring容器
        ApplicationContext ac =  new ClassPathXmlApplicationContext("bean.xml");
        //2.根据id获取bean对象
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
        //3.执行操作
//        jdbcTemplate.execute("insert  into account(name,money) values('吉姆巴特勒',200.34)");
        //保存
        jdbcTemplate.update("insert  into account(name,money) values(?,?)","格林",234);
    }
}

• 更新操作

public class JdbcTemplateDemo1 {
    public static void main(String[] args) {
        //1.获取Spring容器
        ApplicationContext ac =  new ClassPathXmlApplicationContext("bean.xml");
        //2.根据id获取bean对象
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
        //3.执行操作
//        jdbcTemplate.execute("insert  into account(name,money) values('吉姆巴特勒',200.34)");
        //保存
        //jdbcTemplate.update("insert  into account(name,money) values(?,?)","格林",234);
        //更新操作
        jdbcTemplate.update("update account set name=?,money=? where id=?","小岛川龙井",666.777,5);
    }
}

• 删除操作

public class JdbcTemplateDemo1 {
    public static void main(String[] args) {
        //1.获取Spring容器
        ApplicationContext ac =  new ClassPathXmlApplicationContext("bean.xml");
        //2.根据id获取bean对象
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
        //3.执行操作
//        jdbcTemplate.execute("insert  into account(name,money) values('吉姆巴特勒',200.34)");
        //保存
        //jdbcTemplate.update("insert  into account(name,money) values(?,?)","格林",234);
        //更新操作
//        jdbcTemplate.update("update account set name=?,money=? where id=?","小泽玛丽亚",666.777,5);
        //删除操作
        jdbcTemplate.update("delete from account where id=?",5);
    }
}

• 查询所有

package com.itheima.jdbcTemplate;

import com.itheima.domain.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class JdbcTemplateDemo1 {
    public static void main(String[] args) {
        //1.获取Spring容器
        ApplicationContext ac =  new ClassPathXmlApplicationContext("bean.xml");
        //2.根据id获取bean对象
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
        //3.执行操作
//        jdbcTemplate.execute("insert  into account(name,money) values('吉姆巴特勒',200.34)");
        //保存
        //jdbcTemplate.update("insert  into account(name,money) values(?,?)","格林",234);
        //更新操作
//        jdbcTemplate.update("update account set name=?,money=? where id=?","小泽玛丽亚",666.777,5);
        //删除操作
//        jdbcTemplate.update("delete from account where id=?",5);
        List<Account> accounts = jdbcTemplate.query("select * from account where money>?", new AccountRowMapper(), 500);
        for (Account account:accounts){
            System.out.println(account);
        }

    }
}

class AccountRowMapper implements RowMapper<Account> {
    @Override
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
        Account account = new Account();
        account.setId(resultSet.getInt("id"));
        account.setName(resultSet.getString("name"));
        account.setMoney(resultSet.getDouble("money"));
        return account;
    }
}

• 查询一个(根据id查询)

package com.itheima.jdbcTemplate;

import com.itheima.domain.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class JdbcTemplateDemo1 {
    public static void main(String[] args) {
        //1.获取Spring容器
        ApplicationContext ac =  new ClassPathXmlApplicationContext("bean.xml");
        //2.根据id获取bean对象
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
        //3.执行操作
//        jdbcTemplate.execute("insert  into account(name,money) values('吉姆巴特勒',200.34)");
        //保存
        //jdbcTemplate.update("insert  into account(name,money) values(?,?)","格林",234);
        //更新操作
//        jdbcTemplate.update("update account set name=?,money=? where id=?","小泽玛丽亚",666.777,5);
        //删除操作
//        jdbcTemplate.update("delete from account where id=?",5);
//        List<Account> accounts = jdbcTemplate.query("select * from account where money>?", new AccountRowMapper(), 500);
//        for (Account account:accounts){
//            System.out.println(account);
//        }
        //查询一个
        List<Account> accounts = jdbcTemplate.query("select * from account where id=?", new AccountRowMapper(), 6);
        System.out.println(accounts.isEmpty()?"没有结果":accounts.get(0));
    }
}

class AccountRowMapper implements RowMapper<Account> {
    @Override
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
        Account account = new Account();
        account.setId(resultSet.getInt("id"));
        account.setName(resultSet.getString("name"));
        account.setMoney(resultSet.getDouble("money"));
        return account;
    }
}

• 查询返回一行一列

public class JdbcTemplateDemo1 {
    public static void main(String[] args) {
        //1.获取Spring容器
        ApplicationContext ac =  new ClassPathXmlApplicationContext("bean.xml");
        //2.根据id获取bean对象
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
        //3.执行操作
//        jdbcTemplate.execute("insert  into account(name,money) values('吉姆巴特勒',200.34)");
        //保存
        //jdbcTemplate.update("insert  into account(name,money) values(?,?)","格林",234);
        //更新操作
//        jdbcTemplate.update("update account set name=?,money=? where id=?","小泽玛丽亚",666.777,5);
        //删除操作
//        jdbcTemplate.update("delete from account where id=?",5);
//        List<Account> accounts = jdbcTemplate.query("select * from account where money>?", new AccountRowMapper(), 500);
//        for (Account account:accounts){
//            System.out.println(account);
//        }
        //查询一个
//        List<Account> accounts = jdbcTemplate.query("select * from account where id=?", new AccountRowMapper(), 6);
//        System.out.println(accounts.isEmpty()?"没有结果":accounts.get(0));
        //查询返回一行一列的操作
        Integer total = jdbcTemplate.queryForObject("select count(*) from account where money>?", Integer.class, 500);
        System.out.println(total);
    }

}

class AccountRowMapper implements RowMapper<Account> {
    @Override
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
        Account account = new Account();
        account.setId(resultSet.getInt("id"));
        account.setName(resultSet.getString("name"));
        account.setMoney(resultSet.getDouble("money"));
        return account;
    }
}

1.3在dao中使用JdbcTemplate

1.3.1准备实体类

package com.itheima.domain;

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

    public Integer getId() {
        return id;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }
}

1.3.2在dao中定义JdbcTemplate

package com.itheima.dao.impl;

import com.itheima.dao.IAccountDao;
import com.itheima.domain.Account;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class IAccountDaoImpl implements IAccountDao {
    public void setJt(JdbcTemplate jt) {
        this.jt = jt;
    }

    //
    private JdbcTemplate jt;

    @Override
    public List<Account> findAll() {

        List<Account> accounts =  jt.query("select * from account",new AccountRwoMapper());
        return accounts;
    }

    @Override
    public Account findAccountById(Integer id) {
        List<Account> accounts = jt.query("select * from account where id=?",new AccountRwoMapper(),id);
        return accounts.get(0);
    }

    @Override
    public Account findAccountByName(String name) {
        List<Account> accounts = jt.query("select * from account where name =?",new AccountRwoMapper(),name);
        return accounts.get(0);
    }

    @Override
    public void saveAccount(Account account) {
        jt.update("insert into account(name,money) values(?,?)",account.getName(),account.getMoney());
    }

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

    @Override
    public void deleteAccount(Integer id) {
        jt.update("delete from account where id =?",id);
    }
}

1.3.3在spring主配置文件bean.xml中加入相关配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
    <bean id="iAccountDao" class="com.itheima.dao.impl.IAccountDaoImpl">
        <property name="jt" ref="jt"></property>
    </bean>
    <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="datasource"></property>
    </bean>
    <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mybatis"></property>
        <property name="user" value="root"></property>
        <property name="password" value="Shezeq1,"></property>
    </bean>
</beans>

二、声明式事务控制