spring的jdbc模板

在之前的学习spring的过程中，我们知道了spring的核心功能是：

　　1.对象管理

　　2.依赖注入

那么，平时的项目中会经常用到数据库连接，在之前我们都是使用自己封装的BaseDao方法来处理数据库的sql语句，然后需要频繁的开关流，浪费资源。后来学习了数据库连接池，关于连接池的详细叙述，之前有写过，这里就不再多说了。现在我们学习了spring框架，那么spring框架是如何处理jdbc的呢？

下面创建一个项目，导入spring核心jar包，数据库连接包以及spring-jdbcjar包，需要添加的jar包如下：

<?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>

    <groupId>com.zs</groupId>
    <artifactId>demo3</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <spring.version>4.3.18.RELEASE</spring.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.45-bin</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
    </dependencies>

</project>

新建spring配置文件

<?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"
       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">

    <!--1.加载数据库连接池的配置信息-->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!--2.配置连接池,这里使用dbcp连接池，其他的连接池配置都差不多，需要将class内容修改-->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <!--${}读取之前加载的.properties文件，需要注意的是，.properties文件内的属性名必须加上jdbc.
        如，jdbc.url、jdbc.username如果不加jdbc. 会报异常
        还可以继续往下配置最大连接数、初始化连接数、最大等待时间等，配置方式与上面的一样，要先在配置文件中添加，
        也可以在value后直接写值，就不需要.properties文件了-->
    </bean>

</beans>

jdbc.properties如下：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///test
jdbc.username=root
jdbc.password=123456

然后我们测试是否连接数据库成功，在以往的测试类中，都需要读取applicationContext.xml文件，创建对象，就像下面这样：

import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class mysqlTest {
    @Test
    public void fun1() throws SQLException {
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        DataSource bean = context.getBean(DataSource.class);
        Connection connection = bean.getConnection();
        System.out.println(connection);

    }
}

现在使用spring的单元测试testjar包使用注解来配置，进行单元测试

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

//使用注解配置spring主配置文件
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
@RunWith(value = SpringJUnit4ClassRunner.class)//表明使用springJunit4的类来进行测试
public class mysqlTest {
    @Autowired
    private DataSource ds;//使用注解自动装配
    @Test
    public void fun1() throws SQLException {
       /* ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        DataSource bean = context.getBean(DataSource.class);*/
        Connection connection = ds.getConnection();
        System.out.println(connection);

    }
}

 下面使用spring的jdbc模板来操作数据库完成增删改查等操作：

创建实体类：

package com.zs.entity;

import org.springframework.stereotype.Component;

@Component
public class User {
    private int uid;
    private String username;
    private String password;

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

dao层，dao层的接口这里就不写了，直接写实现类：

package com.zs.dao.impl;

import com.zs.dao.IUserDao;
import com.zs.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UserDaoImpl implements IUserDao {

    @Autowired
    private JdbcTemplate jt;

    @Override
    public int save(User user) {
        String sql = "insert into user(username,password) value(?,?)";
        return jt.update(sql, user.getUsername(), user.getPassword());
    }

    @Override
    public int updUser(User user) {
        String sql = "update user set username=?,password=? where uid=?";
        return jt.update(sql, user.getUsername(), user.getPassword(), user.getUid());
    }

    @Override
    public int delUser(int uid) {
        String sql = "delete from user where uid=?";
        return jt.update(sql, uid);
    }

    @Override
    public List<User> listUser() {
        String sql = "select * from user";
        return jt.query(sql, new BeanPropertyRowMapper<User>(User.class));
    }
}

业务逻辑层，同样不写接口，通过实现类可以看出接口的内容

package com.zs.service.impl;

import com.zs.dao.IUserDao;
import com.zs.entity.User;
import com.zs.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService implements IUserService {

    @Autowired
    private IUserDao userDao;
    @Override
    public boolean save(User user) {
        return userDao.save(user) > 0;
    }

    @Override
    public boolean updUser(User user) {
        return userDao.updUser(user) > 0;
    }

    @Override
    public boolean delUserById(int uid) {
        return userDao.delUser(uid) > 0;
    }

    @Override
    public List<User> users() {
        return userDao.listUser();
    }
}

还需要修改spring配置文件，在之前的配置文件后添加如下：

进行单元测试：

import com.zs.entity.User;
import com.zs.service.IUserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
@RunWith(value = SpringJUnit4ClassRunner.class)
public class UserTest {

    @Autowired
    private IUserService userService;
    @Autowired
    private User user;
    @Test
    public void fun1() {
        user.setUsername("hello");
        user.setPassword("123456");
        boolean b = userService.save(user);
        System.out.println(b);
    }

    @Test
    public void fun2() {
        user.setUid(2);
        user.setUsername("王五");
        user.setPassword("123456");
        boolean b = userService.updUser(user);
        System.out.println(b);
    }
    @Test
    public void fun3() {
        boolean b = userService.delUserById(1);
        System.out.println(b);
    }
    @Test
    public void fun4() {
        List<User> users = userService.users();
        for (User user1 : users) {
            System.out.println(user1);
        }
    }
}