7. Spring-JdbcTemplate

一、准备工作

1. 引入相关依赖

<?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>org.example</groupId>
    <artifactId>spring5-05-JdbcTemplate</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>15</maven.compiler.source>
        <maven.compiler.target>15</maven.compiler.target>
    </properties>

    <dependencies>


        <!-- https://mvnrepository.com/artifact/org.springframework/spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring</artifactId>
            <version>5.2.6.RELEASE</version>
            <type>pom</type>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-expression -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.1.1</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.2</version>
            <scope>test</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-aspects -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/net.sourceforge.cglib/com.springsource.net.sf.cglib -->
        <dependency>
            <groupId>net.sourceforge.cglib</groupId>
            <artifactId>com.springsource.net.sf.cglib</artifactId>
            <version>2.2.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.aopalliance/com.springsource.org.aopalliance -->
        <dependency>
            <groupId>org.aopalliance</groupId>
            <artifactId>com.springsource.org.aopalliance</artifactId>
            <version>1.0.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.aspectj/com.springsource.org.aspectj.weaver -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>com.springsource.org.aspectj.weaver</artifactId>
            <version>1.6.8.RELEASE</version>
        </dependency>



    </dependencies>

</project>

2. 编写配置文件

<?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:p="http://www.springframework.org/schema/p"
       xmlns:util="http://www.springframework.org/schema/util"
       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/util http://www.springframework.org/schema/util/spring-util.xsd
                           http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

   <!--开启扫描-->
   <context:component-scan base-package="com.znb.spring5"></context:component-scan>

   <!--引入外部属性文件-->
   <!-- <context:property-placeholder location="jdbc.properties"></context:property-placeholder>-->
   <!-- &lt;!&ndash;直接配置连接池&ndash;&gt;
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        &lt;!&ndash; 获取properties文件内容，根据key获取，使用spring表达式获取 &ndash;&gt;
        <property name="driverClassName" value="${driverClassName}">

        </property>
        <property name="url" value="${url}">

        </property>
        <property name="username" value="${username}">

        </property>
        <property name="password" value="${password}">

        </property>
    </bean>-->

   <!--直接配置连接池-->
   <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
      <!-- 获取properties文件内容，根据key获取，使用spring表达式获取 -->
      <property name="driverClassName" value="com.mysql.cj.jdbc.Driver">

      </property>
      <property name="url" value="jdbc:mysql://localhost:3306/usedb?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;serverTimezone=GMT">

      </property>
      <property name="username" value="root">

      </property>
      <property name="password" value="2550">

      </property>
   </bean>

   <!--JdbcTemplate对象-->
   <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
      <!--注入dataSourse-->
      <property name="dataSource" ref="dataSource"></property>
   </bean>
</beans>

3. 创建数据库中的表

create database usedb;
use usedb;

create table t_book(
	bookId varchar(20),
	bookName varchar(20),
	bookTatus(20)
);

二、JdbcTemplate操作数据库

1. 添加数据

1. BookService类

@Service
public class BookService {

    //注入Dao
    @Autowired
    private BookDao bookDao;


    public void addBook(Book book) {
        bookDao.add(book);
    }
}

2. BookDao接口

public interface BookDao {
    void add(Book book);
}

3. BookDaoImpl类

@Repository
public class BookDaoImpl implements BookDao {

    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void add(Book book) {
        String sql = "insert into t_book values(?,?,?)";
        int update = jdbcTemplate.update(sql, book.getBookId(), book.getBookName(), book.getBookTatus());
        System.out.println(update);


    }
}

4. Book类（与数据库中表对应的实体类）

public class Book {

    private String bookId;
    private String bookName;
    private String bookTatus;

    public String getBookId() {
        return bookId;
    }

    public void setBookId(String bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookTatus() {
        return bookTatus;
    }

    public void setBookTatus(String bookTatus) {
        this.bookTatus = bookTatus;
    }
}

5. 测试类

public class BookServiceTest extends TestCase {

    public void testAddBook() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
        book.setBookId("1");
        book.setBookName("红楼梦");
        book.setBookTatus("有货");
        bookService.addBook(book);
    }
}

2. 修改和删除操作

	@Override
    public void updateBook(Book book) {
        String sql = "update t_book set bookname = ? ,booktatus = ? where bookid = ? ";
        int update = jdbcTemplate.update(sql, book.getBookName(), book.getBookTatus(), book.getBookId());
        System.out.println(update);
    }

    @Override
    public void deleteBook(String id) {
        String sql = "delete from t_book where bookid = ? ";
        int update = jdbcTemplate.update(sql,id);
        System.out.println(update);
    }

3. 查询操作

① 查询返回某一个值

	@Override
    public int selectCount() {
        String sql = "select count(*) from t_book";
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
        return integer;
    }

② 查询返回对象

	@Override
    public Book selectOne(String id) {
        String sql = "select * from t_book where bookid = ?";
        Book book = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }

③ 查询返回集合

	@Override
    public List<Book> findAll() {
        String sql = "select * from t_book ";
        List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return query;
    }

4. 批量操作数据

① 批量添加

添加方法

	//批量添加
    @Override
    public void batchAddBook(List<Object[]> args) {
        String sql = "insert into t_book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql,args);
        System.out.println(Arrays.toString(ints));
    }

测试代码

package com.znb.spring5.service;

import com.znb.spring5.entity.Book;
import junit.framework.TestCase;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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

public class BookServiceTest extends TestCase {

    public void testAddBook() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
      
        //批量添加
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"3","java","c"};
        Object[] o2 = {"4","python","c"};
        Object[] o3 = {"5","PHP","c"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);

        bookService.batchAdd(batchArgs);


    }
}

② 批量修改

修改方法

	//批量修改
    @Override
    public void bUpdate(List<Object[]> args) {
        String sql = "update t_book set bookname = ?, booktatus = ? where bookid = ?";
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println(Arrays.toString(ints));
    }

测试类

package com.znb.spring5.service;

import com.znb.spring5.entity.Book;
import junit.framework.TestCase;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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

public class BookServiceTest extends TestCase {

    public void testAddBook() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);


        //批量添加
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"java","c","3"};
        Object[] o2 = {"python","d","4"};
        Object[] o3 = {"PHP","e","5"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);

        bookService.bUpdate(batchArgs);


    }
}

③ 批量删除

删除方法

	//批量删除
    @Override
    public void bDelete(List<Object[]> args) {
        String sql = "delete from t_book where bookid = ? ";
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println(Arrays.toString(ints));
    }

测试类

package com.znb.spring5.service;

import com.znb.spring5.entity.Book;
import junit.framework.TestCase;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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

public class BookServiceTest extends TestCase {

    public void testAddBook() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);


        //批量删除
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"3"};
        Object[] o2 = {"4"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        bookService.bDelete(batchArgs);
    }
}