Spring 整合JDBC

Spring整合JDBC

1. JDBCTemplate初探

Spring提供了很多持久层技术的模版类来简化编程。其中，它整合了JDBC、Hibernate、MyBatis等等持久层技术的模版类供我们选择使用。

首先，选择JDBCTemplate来体验一下

package com.liweijian.test1;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;

import java.beans.PropertyVetoException;

/**
 * @Author:Liweijian
 * @Description: 测试Spring中的JDBCTemplate
 * @Date:Create in 20:44 2018/1/22 0022
 */
public class Test1 {

    @Test
    public void fun() throws PropertyVetoException {

        //1.准备连接池
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql:///t1");
        dataSource.setUser("root");
        dataSource.setPassword("qq352642663");

        //2.创建JDBC模版
        JdbcTemplate template = new JdbcTemplate(dataSource);

        //3.书写sql
        String sql = "insert into t_user values(null,'rose')";
        template.update(sql);
    }
}

2. Spring 整合JDBCTemplate

首先，我们需要将对应的包导入

4+2基础包
c3p0连接池+JDBC驱动
如果需要使用Spring集成的JUnit测试，还需要导入spring-aop + spring-test + junit4类库

准备数据库（数据库为了测试，只准备了id和name字段）

编写Bean

package com.liweijian.bean;

/**
 * @Author:Liweijian
 * @Description:
 * @Date:Create in 21:01 2018/1/22 0022
 */
public class User {

    private Integer id;
    private String name;

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

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

书写Dao

package com.liweijian.test2;

import com.liweijian.bean.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

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

/**
 * @Author:Liweijian
 * @Description:
 * @Date:Create in 21:06 2018/1/22 0022
 */
public class UserDaoImpl implements UserDao {
    private JdbcTemplate template;

    @Override
    public void save(User user) {

        String sql = "insert into t_user values (null,?)";
        template.update(sql,user.getName());
    }

    @Override
    public void delete(Integer id) {
        String sql = "delete from  t_user where id = ?";
        template.update(sql,id);
    }

    @Override
    public void update(User user) {
        String sql = "update t_user set name = ? where id = ?";
        template.update(sql,user.getName(),user.getId());
    }

    @Override
    public User getById(Integer id) {
        String sql = "select * from t_user where id = ?";
        return template.queryForObject(sql, new RowMapper<User>() {

            @Override
            //实现接口封装对象并返回。
            //类似与DBUtils，只不过DBUtils帮我们实现了这些分装方法
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                return user;
            }
        },id);
    }

    @Override
    public int getByTotalCount() {
        String sql = "select count(*) from t_user";
        Integer count = template.queryForObject(sql, Integer.class);
        return count;
    }

    @Override
    public List<User> getAll() {
        String sql = "select * from t_user";

        List<User> query = template.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                return user;
            }
        });

        return query;
    }


    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }
}

配置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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <!--
        依赖关系：DataSource ==> JdbcTemplate ==> UserDaoImpl
        所以，在配置的时候，只要配置好依赖关系，然后测试的时候获得UserDaoImpl就可以
    -->

    <!-- 1.配置连接池 -->
    <!--
     dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql:///t1");
        dataSource.setUser("root");
        dataSource.setPassword("qq352642663");
    -->
    <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="DriverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="JdbcUrl" value="jdbc:mysql:///t1"></property>
        <property name="User" value="root"></property>
        <property name="Password" value="qq352642663"></property>
    </bean>

    <!-- 2.配置JdbcTemplate -->
    <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!-- 3.配置UserDao -->
    <bean name="userDao" class="com.liweijian.test2.UserDaoImpl">
        <property name="template" ref="jdbcTemplate"></property>
    </bean>


</beans>

书写测试类

package com.liweijian.test2;

import com.liweijian.bean.User;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

/**
 * @Author:Liweijian
 * @Description:
 * @Date:Create in 21:35 2018/1/22 0022
 */
public class Test2 {

    @Test
    public void fun1(){

        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) context.getBean("userDao");

        User user = new User();
        user.setName("java");
        userDao.save(user);
    }

    @Test
    public void fun2(){

        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) context.getBean("userDao");

        User user = new User();
        user.setId(2);
        user.setName("c++");
        userDao.update(user);
    }

    @Test
    public void fun3(){

        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) context.getBean("userDao");

        userDao.delete(2);
    }

    @Test
    public void fun4(){

        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) context.getBean("userDao");

        User user = userDao.getById(3);
        System.out.println(user.toString());
    }

    @Test
    public void fun5(){

        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) context.getBean("userDao");

        int byTotalCount = userDao.getByTotalCount();

        System.out.println(byTotalCount);
    }

    @Test
    public void fun6(){

        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) context.getBean("userDao");

        List<User> all = userDao.getAll();

        System.out.println(all);

    }
}

读取外部properties文件

在applicationContext.xml中书写标签

1 2	<!--读取properties文件--> <context:property-placeholder location="classpath:db.properties"></context:property-placeholder>

在src下创建db.properties文件

修改bean标签

<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
      <property name="DriverClass" value="${driverClass}"></property>
      <property name="JdbcUrl" value="${jdbcUrl}"></property>
      <property name="User" value="${user}"></property>
      <property name="Password" value="${password}"></property>
  </bean>

####感谢阅读本博客。

####欢迎关注我的博客：https://li-weijian.github.io/

####欢迎关注我的CSDN：https://blog.csdn.net/qq352642663

####需要联系请加QQ：352642663

####欢迎联系我共同交流