spring JDBC模板

Spring的JDBC的模板

• Spring是EE开发的一站式的框架，有EE开发的每层的解决方案。
• Spring对持久层也提供了解决方案：ORM模块和JDBC的模板。
• Spring提供了很多的模板用于简化开发
  • JDBC：org.springframework.jdbc.core.jdbc.jdbcTemplate
  • Hibernate：orm.springframework.orm.hibernamte.HibernateTemplate

JDBC模板使用的入门

引入jar包

• spring开发基本jar包
• 数据库驱动
• Spring的JDBC模板的jar包

创建数据库和表

create table account(
	id int primary key auto_increment,
	name varchar(20),
	money double
);

使用JDBC的模板

@Test
public void test() {
	// 1. 创建连接池（数据库相关信息）
	DriverManagerDataSource dataSource = new DriverManagerDataSource();
	dataSource.setDriverClassName("com.mysql.jdbc.Driver");
	// "jdbc:mysql:///spring" 相当于 "dbc:mysql://localhost:3306/spring"
	dataSource.setUrl("jdbc:mysql://localhost:3307/spring");
	dataSource.setUsername("root");
	dataSource.setPassword("123456");
	// 2. 创建JDBC模板
	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	jdbcTemplate.update("insert into account values (null,?,?)", "IT666", 1000d);
}

将连接池和模板交给Spring管理

配置文件配置Bean

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop
		http://www.springframework.org/schema/aop/spring-aop.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3307/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

使用jdbcTemplate注解插入数据

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest2 {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void test() {
        jdbcTemplate.update("insert into account values (null,?,?)", "IT888", 1000d);
    }
}

使用开源连接池

DBCP

引入jar包

配置DBCP连接池

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop
		http://www.springframework.org/schema/aop/spring-aop.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3307/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <!--dbcp-->
    <bean id="dbcp" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3307/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dbcp"/>
    </bean>

</beans>

测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest2 {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void test() {
        jdbcTemplate.update("insert into account values (null,?,?)", "IT777", 1000d);
    }
}

C3P0

引入jar包

配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop
		http://www.springframework.org/schema/aop/spring-aop.xsd">

    <!--dbcp-->
    <bean id="dbcp" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3307/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <!--C3P0-->
    <bean id="c3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3307/spring"/>
        <property name="user" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="c3p0"/>
    </bean>

</beans>

测试同上

DRUID

引入jar包

配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop
		http://www.springframework.org/schema/aop/spring-aop.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3307/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <!--dbcp-->
    <bean id="dbcp" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3307/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <!--C3P0-->
    <bean id="c3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3307/spring"/>
        <property name="user" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <!--druid-->
    <bean id="druid" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3307/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="druid"/>
    </bean>

</beans>

测试同上

运行时发生错误：

解决方法：

将 druid-1.0.15.jar 换成 druid-1.1.11.jar 即可

使用属性文件配置数据库连接信息

1.创建属性文件

jdbc.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3307/spring
username=root
password=123456

2.配置文件中引入属性文件

（1）<bean>方式

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop
		http://www.springframework.org/schema/aop/spring-aop.xsd">

    <!--加载属性文件-->
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location" value="jdbc.properties"/>
    </bean>

    <!--C3P0-->
    <bean id="c3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${driverClass}"/>
        <property name="jdbcUrl" value="${url}"/>
        <property name="user" value="${username}"/>
        <property name="password" value="${password}"/>
    </bean>

    <!--druid-->
    <bean id="druid" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${driverClass}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="druid"/>
    </bean>

</beans>

测试：

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest2 {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void test() {
        jdbcTemplate.update("insert into account values (null,?,?)", "IT789", 1000d);
    }
}

（2）<context/>方式

配置文件要加上

jdbc.properties
key值不能和name一样，所以一般加上jdbc前缀

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3307/spring
jdbc.username=root
jdbc.password=123456

applicationContext.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"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context
		http://www.springframework.org/schema/context/spring-context.xsd
		http://www.springframework.org/schema/aop
		http://www.springframework.org/schema/aop/spring-aop.xsd">

    <!--加载属性文件-->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!--druid-->
    <bean id="druid" class="com.alibaba.druid.pool.DruidDataSource">
        <!--key值不能和name一样，所以一般加上jdbc前缀-->
        <property name="driverClassName" value="${jdbc.driverClass}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="druid"/>
    </bean>

</beans>

测试同上

JDBC模板CRUD操作

插入操作、删除操作、更新操作

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest2 {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    // 插入操作
    @Test
    public void insert() {
        jdbcTemplate.update("insert into account values (null,?,?)", "IT147", 1000d);
    }

    // 删除操作
    @Test
    public void delete(){
        jdbcTemplate.update("delete from account where id=?",1);
    }

    // 更新操作
    @Test
    public void update(){
        jdbcTemplate.update("update account set name=?,money=? where id=?","IT666",2000d,9);
    }
}

查询操作

（1）查询某一个字段

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest2 {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void query(){
        String name = jdbcTemplate.queryForObject("select name from account where id=?",String.class,9);
        System.out.println(name);
    }

    @Test
    public void queryCount(){
        long count = jdbcTemplate.queryForObject("select count(*)from account",long.class);
        System.out.println(count);
    }
}

（2）查询返回对象的集合

使用 lombok，开启注解

@Getter@Setter
public class Account {
    private Integer id;
    private String name;
    private double money;

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

（2.1）返回一个对象

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest2 {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void queryAccount(){
        Account account = jdbcTemplate.queryForObject("select * from account where id=?", new MyRowMap(), 9);
        System.out.println(account);
    }
}

class MyRowMap 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;
    }
}

（2.2）返回所有对象集合

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest2 {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void queryAllAccount(){
        List<Account> query = jdbcTemplate.query("select * from account", new MyRowMap());
        for (Account account : query) {
            System.out.println(account);
        }
    }
}

class MyRowMap 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;
    }
}

通过内省重构 mapRow() 方法

public class RowMap<T> implements RowMapper<T> {

    private Class<T> classType;

    public RowMap(Class<T> classType) {
        this.classType = classType;
    }

    @Override
    public T mapRow(ResultSet resultSet, int i) throws SQLException {
        T obj = null;
        try {
            // 创建一个对象
            obj = this.classType.newInstance();
            // 通过内省来拿属性 , Object.class
            BeanInfo bf = Introspector.getBeanInfo(this.classType, Object.class);
            // 获取所有属性描述器
            PropertyDescriptor[] pds = bf.getPropertyDescriptors();
            // 遍历每一个属性的描述器
            for (PropertyDescriptor pd : pds) {
                if (pd.getName().equals("anonymousName"))
                    continue;
                Object val = resultSet.getObject(pd.getName());
                // 给对象设置属性值
                pd.getWriteMethod().invoke(obj, val);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return obj;
    }
}

测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest2 {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void queryAllAccount(){
        List<User> query = jdbcTemplate.query("select * from user", new MyRowMap(User.class));
        for (User user : query) {
            System.out.println(user);
        }
    }
}

class MyRowMap extends RowMap<User>{
    public MyRowMap(Class<User> classType) {
        super(classType);
    }
}

通过反射重构 mapRow() 方法

public class RowMapNew<T> implements RowMapper<T> {

    private Class<T> classType;

    public RowMapNew(Class<T> classType) {
        this.classType = classType;
    }

    @Override
    public T mapRow(ResultSet resultSet, int i) throws SQLException {
        T obj = null;
        try {
            // 创建一个对象
            obj = this.classType.newInstance();
            // 通过反射来拿属性和方法
            Field[] fields = this.classType.getDeclaredFields();
            Method[] methods = this.classType.getDeclaredMethods();
            // 遍历每一个属性
            for (Field field : fields) {
                Object val = resultSet.getObject(field.getName());
                // 将属性名的首字母变为大写
                String s = field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
                // 给对象设置属性值
                for (Method method : methods) {
                    if(("set"+s).equals(method.getName())){
                        method.invoke(obj,val);
                        break;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return obj;
    }
}

测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest2 {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void queryAllAccount(){
        List<Account> query = jdbcTemplate.query("select * from account", new MyRowMap(Account.class));
        for (Account account : query) {
            System.out.println(account);
        }
    }
}

class MyRowMap extends RowMapNew<Account> {
    public MyRowMap(Class<Account> classType) {
        super(classType);
    }
}