Spring5--JdbcTemplate

概述和准备工作

1、什么是 JdbcTemplate

​ Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作

2、准备工作

（1）引入相关 jar 包

（2）在 spring 配置文件配置数据库连接池

1、在src目录下创建jdbc.properties

prop.driverClass=com.mysql.jdbc.Driver
prop.url=jdbc:mysql://localhost:3306/user_db
prop.username=root
prop.password=123456

2、bean1.xml中

    <!--引入外部属性文件-->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!--配置连接池-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="driverClassName" value="${prop.driverClass}"/>
        <property name="url" value="${prop.url}"/>
        <property name="username" value="${prop.username}"/>
        <property name="password" value="${prop.password}"/>
    </bean>

（3）配置 JdbcTemplate 对象，注入 DataSource

bean1.xml

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

（4）创建 service 类，创建 dao 类，在 dao 注入 jdbcTemplate 对象

1、开启组件扫描，bean1.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:component-scan base-package="com.spring5"/>

    <!--引入外部属性文件-->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!--配置连接池-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="driverClassName" value="${prop.driverClass}"/>
        <property name="url" value="${prop.url}"/>
        <property name="username" value="${prop.username}"/>
        <property name="password" value="${prop.password}"/>
    </bean>

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

2、编写service层和dao层

package com.spring5.service;

import com.spring5.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class BookService {
    // 注入dao
    @Autowired
    private BookDao bookDao;
}

package com.spring5.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookDaoImpl implements BookDao {
    // 注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

JdbcTemplate 操作数据库

添加

1、对应数据库创建实体类

package com.spring5.entity;

public class Book {
    private int bid;
    private String bname;
    private String bstatus;

    public Book() {
    }

    public Book(int bid, String bname, String bstatus) {
        this.bid = bid;
        this.bname = bname;
        this.bstatus = bstatus;
    }

    public int getBid() {
        return bid;
    }

    public String getBname() {
        return bname;
    }

    public String getBstatus() {
        return bstatus;
    }

    public void setBid(int bid) {
        this.bid = bid;
    }

    public void setBname(String bname) {
        this.bname = bname;
    }

    public void setBstatus(String bstatus) {
        this.bstatus = bstatus;
    }
}

2、编写 service 和 dao

（1）在 dao 进行数据库添加操作

（2）调用 JdbcTemplate 对象里面 update 方法实现添加操作

package com.spring5.dao;

import com.spring5.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookDaoImpl implements BookDao {
    // 注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int add(Book book) {
        // 创建sql语句
        String sql = "insert into book values(?,?,?)";
        int row = jdbcTemplate.update(sql, book.getBid(), book.getBname(), book.getBstatus());
        return row;
    }
}

package com.spring5.service;

import com.spring5.dao.BookDao;
import com.spring5.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class BookService {
    // 注入dao
    @Autowired
    private BookDao bookDao;

    //添加的方法
    public void addBook(Book book) {
        int row = bookDao.add(book);
        System.out.println(row);
    }
}

3、测试类

package com.spring5.test;

import com.spring5.entity.Book;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        bookService.addBook(new Book(101, "大主宰", "完结"));
    }
}

4、运行结果

5、数据库查看

修改和删除

BookDaoImpl类

package com.spring5.dao;

import com.spring5.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookDaoImpl implements BookDao {
    // 注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int add(Book book) {
        // 创建sql语句
        String sql = "insert into book values(?,?,?)";
        return jdbcTemplate.update(sql, book.getBid(), book.getBname(), book.getBstatus());
    }

    @Override
    public int update(Book book) {
        String sql = "update book set bname=?,bstatus=? where bid=?";
        return jdbcTemplate.update(sql, book.getBname(), book.getBstatus(), book.getBid());
    }

    @Override
    public int delete(int bid) {
        String sql = "delete from book where bid=?";
        return jdbcTemplate.update(sql, bid);
    }

}

BookService类

package com.spring5.service;

import com.spring5.dao.BookDao;
import com.spring5.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class BookService {
    // 注入dao
    @Autowired
    private BookDao bookDao;

    //添加的方法
    public void addBook(Book book) {
        int row = bookDao.add(book);
        System.out.println(row);
    }

    //修改的方法
    public void updateBook(Book book) {
        int row = bookDao.update(book);
        System.out.println(row);
    }

    //删除的方法
    public void deleteBook(int bid) {
        int row = bookDao.delete(bid);
        System.out.println(row);
    }
}

测试

修改

package com.spring5.test;

import com.spring5.entity.Book;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        //bookService.addBook(new Book(101, "大主宰", "完结"));
        bookService.updateBook(new Book(101, "圣墟", "连载"));
    }
}

删除

package com.spring5.test;

import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        //bookService.addBook(new Book(101, "大主宰", "完结"));
        //bookService.updateBook(new Book(101, "圣墟", "连载"));
        bookService.deleteBook(101);
    }
}

查询

查询返回某个值

第一个参数：sql 语句；第二个参数：返回类型 Class

第一个参数：sql 语句；第二个参数：sql 语句?参数值；第三个参数：返回类型 Class

往BookDao接口新增一个int count();方法，在BookDaoImpl中重写该方法：

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

若带参数，修改如下;

String[] args = {...};
return jdbcTemplate.queryForObject(sql, args, Integer.class);

在BookService新增方法：

    //查询表中记录数
    public void findCount() {
        int count = bookDao.count();
        System.out.println(count);
    }

手动在数据库添加3条记录

修改测试类：

package com.spring5.test;

import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        //bookService.addBook(new Book(101, "大主宰", "完结"));
        //bookService.updateBook(new Book(101, "圣墟", "连载"));
        //bookService.deleteBook(101);
        bookService.findCount();
    }
}

运行结果：

查询返回对象

第一个参数：sql 语句

第二个参数：RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成 数据封装

第三个参数：sql 语句?参数值

往BookDao接口新增一个Book findBookById(int bid);方法，在BookDaoImpl中重写该方法：

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

在BookService新增方法：

    //查询返回对象
    public void findBookbyId(int bid) {
        Book book = bookDao.findBookById(bid);
        System.out.println(book);
    }

修改测试类：

package com.spring5.test;

import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        //bookService.addBook(new Book(101, "大主宰", "完结"));
        //bookService.updateBook(new Book(101, "圣墟", "连载"));
        //bookService.deleteBook(101);
        //bookService.findCount();
        bookService.findBookbyId(101);
    }
}

运行结果：

查询返回集合

往BookDao接口新增一个List<Book> findAll();方法，在BookDaoImpl中重写该方法：

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

在BookService新增方法：

    //查询返回集合
    public void findBookAll() {
        List<Book> bookList = bookDao.findAll();
        System.out.println(bookList);
    }

修改测试类：

package com.spring5.test;

import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        //bookService.addBook(new Book(101, "大主宰", "完结"));
        //bookService.updateBook(new Book(101, "圣墟", "连载"));
        //bookService.deleteBook(101);
        //bookService.findCount();
        //bookService.findBookbyId(101);
        bookService.findBookAll();
    }
}

运行结果：

批量操作

​ 操作表里面多条记录

添加

第一个参数：sql 语句

第二个参数：List 集合，添加多条记录数据

往BookDao接口新增一个int[] batchAdd(List<Object[]> list);方法，在BookDaoImpl中重写该方法：

    @Override
    public int[] batchAdd(List<Object[]> list) {
        String sql = "insert into book values(?,?,?)";
        return jdbcTemplate.batchUpdate(sql, list); // 返回影响行数
    }

在BookService新增方法：

    //批量添加
    public void batchAddBook(List<Object[]> list) {
        int[] rows = bookDao.batchAdd(list);
        System.out.println(Arrays.toString(rows));
    }

修改测试类：

package com.spring5.test;

import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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

public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        // 批量添加
        List<Object[]> list = new ArrayList<>();
        Object[] o1 = {104, "完美世界", "完结"};
        Object[] o2 = {105, "冰火魔厨", "完结"};
        Object[] o3 = {106, "斗罗大陆", "完结"};
        list.add(o1);
        list.add(o2);
        list.add(o3);
        bookService.batchAddBook(list);
    }
}

运行结果：

查看数据库：

修改

往BookDao接口新增一个int[] batchUpdate(List<Object[]> list);方法，在BookDaoImpl中重写该方法：

    @Override
    public int[] batchUpdate(List<Object[]> list) {
        String sql = "update book set bname=?,bstatus=? where bid=?";
        return jdbcTemplate.batchUpdate(sql, list); // 返回影响行数
    }

在BookService新增方法：

    //批量修改
    public void batchUpdateBook(List<Object[]> list) {
        int[] rows = bookDao.batchUpdate(list);
        System.out.println(Arrays.toString(rows));
    }

修改测试类：

package com.spring5.test;

import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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

public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        // 批量修改
        List<Object[]> list = new ArrayList<>();
        Object[] o1 = {"星门", "完结", 104};    // 根据sql语句参数位置来
        Object[] o2 = {"超凡世界", "完结", 105};
        Object[] o3 = {"遮天", "完结", 106};
        list.add(o1);
        list.add(o2);
        list.add(o3);
        bookService.batchUpdateBook(list);
    }
}

运行结果：

查看数据库：

删除

往BookDao接口新增一个int[] batchDelete(List<Object[]> list);方法，在BookDaoImpl中重写该方法：

    @Override
    public int[] batchDelete(List<Object[]> list) {
        String sql = "delete from book where bid=?";
        return jdbcTemplate.batchUpdate(sql, list); // 返回影响行数
    }

在BookService新增方法：

    //批量删除
    public void batchDeleteBook(List<Object[]> list) {
        int[] rows = bookDao.batchDelete(list);
        System.out.println(Arrays.toString(rows));
    }

修改测试类：

package com.spring5.test;

import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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

public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        // 批量删除
        List<Object[]> list = new ArrayList<>();
        Object[] o1 = {104};   
        Object[] o2 = {105};
        list.add(o1);
        list.add(o2);
        bookService.batchDeleteBook(list);
    }
}

运行结果：

查看数据库：