Spring之JdbcTemplate

1.JdbcTemplate是Spring对jdbc进行封装后的一个模板。

2.如何通过JdbcTemplate对数据库进行访问？

（1）pom.xml配置文件的相关依赖如下：

<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/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.myspring</groupId>
  <artifactId>spring2</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>spring2 Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.2.12.RELEASE</version>
    </dependency>
    <!--单元测试依赖-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <!--dom4j用来解析XML文件的Java组件-->
    <dependency>
      <groupId>org.dom4j</groupId>
      <artifactId>dom4j</artifactId>
      <version>2.1.3</version>
    </dependency>
    <dependency>
      <groupId>jaxen</groupId>
      <artifactId>jaxen</artifactId>
      <version>1.2.0</version>
    </dependency>
    <!--mysql驱动-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.30</version>
    </dependency>
    <!--jdbc,这个依赖中有JdbcTemplate-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.2.12.RELEASE</version>
    </dependency>
  </dependencies>
  <build>
    <finalName>spring2</finalName>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <configuration>
          <source>7</source>
          <target>7</target>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

（1）创建类对DataSource进行实现

MyDataSource.java

package com.zrx.beans1;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;

public class MyDataSource implements DataSource {
    private String driver;
    private String url;
    private String name;
    private String password;

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public void setUrl(String url) {
        this.url = url;
    }

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

    public void setPassword(String password) {
        this.password = password;
    }
    //重点需要重写getConnection()方法
    public Connection getConnection() throws SQLException {
        try {
            //获取类
            Class.forName(driver);
            //获取驱动连接
            Connection connection = DriverManager.getConnection(url,name,password);
            return connection;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }

    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }

    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    public void setLoginTimeout(int seconds) throws SQLException {

    }

    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }
}

（3）创建实体类

User1.java

package com.zrx.beans1;

public class User1 {
    private Integer userid;
    private String username;
    private Integer age;
    private String sex;

    public User1(Integer userid, String username, Integer age, String sex) {
        this.userid = userid;
        this.username = username;
        this.age = age;
        this.sex = sex;
    }

    public User1() {
    }

    @Override
    public String toString() {
        return "User1{" +
                "userid=" + userid +
                ", username='" + username + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                '}';
    }

    public Integer getUserid() {
        return userid;
    }

    public String getUsername() {
        return username;
    }

    public Integer getAge() {
        return age;
    }

    public String getSex() {
        return sex;
    }

    public void setUserid(Integer userid) {
        this.userid = userid;
    }

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

    public void setAge(Integer age) {
        this.age = age;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }
}

（4）spring配置文件

spring-template.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="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="myDataSource"></property>
    </bean>
    <bean id="myDataSource" class="com.zrx.beans1.MyDataSource">
        <property name="driver" value="com.mysql.cj.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3307/my_user"></property>
        <property name="name" value="root"></property>
        <property name="password" value="zrx123"></property>
    </bean>
</beans>

3.通过JdbcTemplate实现对数据库的增删改查

import com.zrx.beans1.User1;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class TemplateTest {
    //1.增加数据
    @Test
    public void testInsert(){
        //获取JdbcTemplate对象
        ApplicationContext context = new ClassPathXmlApplicationContext("spring-template.xml");
        JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate",JdbcTemplate.class);
        //执行插入语句
        String sql = "insert into user(userid,username,age,sex) values (?,?,?,?)";
        jdbcTemplate.update(sql,null,"赵六",22,"男");
    }

    //2.删除数据
    @Test
    public void testDelete(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring-template.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate",JdbcTemplate.class);
        String sql = "delete from user where userid = ?";
        jdbcTemplate.update(sql,1);
    }

    //3.修改数据
    @Test
    public void testAlter(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring-template.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate",JdbcTemplate.class);
        String sql = "update user set username = ?,age = ?,sex = ? where userid = ?";
        jdbcTemplate.update(sql,"钱七",21,"女",5);
    }

    //4.查询一个对象
    @Test
    public void testSelect_one(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring-template.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate",JdbcTemplate.class);
        String sql = "select * from user where userid = ?";
        User1 user1 = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(User1.class),2);
        System.out.println(user1);
    }

    //5.查询多个对象
    @Test
    public void testSelect_many(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring-template.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate",JdbcTemplate.class);
        String sql = "select * from user";
        List<User1> user1s = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User1.class));
        System.out.println(user1s);
    }

    //6.查询记录条数
    @Test
    public void testSelect_num(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring-template.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate",JdbcTemplate.class);
        String sql = "select count(1) from user";
        int count = jdbcTemplate.queryForObject(sql,int.class);
        System.out.println(count);
    }

    //7.批量添加数据
    @Test
    public void testInsert_many(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring-template.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate",JdbcTemplate.class);
        String sql = "insert into user(userid,username,age,sex) values(?,?,?,?)";
        Object[] obj1 = {null,"u1",21,"女"};
        Object[] obj2 = {null,"u1",22,"女"};
        Object[] obj3 = {null,"u1",23,"女"};
        List<Object[]> list = new ArrayList<>();
        list.add(obj1);
        list.add(obj2);
        list.add(obj2);
        jdbcTemplate.batchUpdate(sql,list);
    }

    //8.批量删除数据
    @Test
    public void testDelete_many(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring-template.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate",JdbcTemplate.class);
        String sql = "delete from user where userid = ?";
        Object[] obj1 = {2};
        Object[] obj3 = {3};
        Object[] obj2 = {4};
        List<Object[]> list = new ArrayList<>();
        list.add(obj1);
        list.add(obj2);
        list.add(obj3);
        jdbcTemplate.batchUpdate(sql,list);
    }

    //9.批量修改数据
    @Test
    public void testUpdate(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring-template.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate",JdbcTemplate.class);
        String sql = "update user set username = ?,age = ?,sex = ? where userid = ?";
        Object[] obj1 = {"user1",20,"男",6};
        Object[] obj2 = {"user2",20,"男",7};
        Object[] obj3 = {"user3",20,"男",8};
        List<Object[]> list = new ArrayList<>();
        list.add(obj1);
        list.add(obj2);
        list.add(obj3);
        jdbcTemplate.batchUpdate(sql,list);
    }
}