Spring中的JdbcTemplate操作

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

准备工作

（1）导入相关的jar包

（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: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.lalala"></context:component-scan>
    
    <!--数据库连接池-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="url" value="jdbc:mysql:///springtest" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    </bean>

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

（3）创建 dao 和service 类，在 dao 中注入JdbcTemplate对象

service中注入dao

JdbcTemplate操作数据库（添加）

新建数据库：springtest，并创建表 t_book

新建实体类 Book

package com.lalala.spring5.entity;

public class Book {
    private String bookId;
    private String bookname;
    private String bookstatus;

    public String getBookId() {
        return bookId;
    }

    public String getBookname() {
        return bookname;
    }

    public String getBookstatus() {
        return bookstatus;
    }

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

    public void setBookname(String bookname) {
        this.bookname = bookname;
    }

    public void setBookstatus(String bookstatus) {
        this.bookstatus = bookstatus;
    }
}

BookService中新增addBook方法

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

    //添加的方法
    public void addBook(Book book){
        bookDao.add(book);
    }
}

BookDao接口如下

package com.lalala.spring5.dao;

import com.lalala.spring5.entity.Book;

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

BookDaoImpl如下

package com.lalala.spring5.dao;

import com.lalala.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 void add(Book book) {
        //创建sql语句
        String sql = "insert into t_book values(?,?,?)";
        //调用方法实现
        int update = jdbcTemplate.update(sql, book.getBookId(), book.getBookname(), book.getBookstatus());
        System.out.println(update);
    }
}

通过查看数据表 t_book，可以看到新增加了一条数据

JdbcTemplate操作数据库（修改和删除）

bookService中如下

package com.lalala.spring5.service;

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

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

    //添加的方法
    public void addBook(Book book){
        bookDao.add(book);
    }

    //修改的方法
    public void updateBook(Book book){
        bookDao.updateBook(book);
    }
    //修改的方法
    public void deleteBook(String id){
        bookDao.delete(id);
    }
}

BookDao如下

package com.lalala.spring5.dao;

import com.lalala.spring5.entity.Book;

public interface BookDao {
    void add(Book book);

    void updateBook(Book book);

    void delete(String id);
}

BookDaoImpl如下

package com.lalala.spring5.dao;

import com.lalala.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 void add(Book book) {
        //创建sql语句
        String sql = "insert into t_book values(?,?,?)";
        //调用方法实现
        int update = jdbcTemplate.update(sql, book.getBookId(), book.getBookname(), book.getBookstatus());
        System.out.println(update);
    }

    //修改
    @Override
    public void updateBook(Book book) {
        String sql = "update t_book set bookname=?,bookstatus=? where book_id=?";
        Object[] args = {book.getBookname(), book.getBookstatus(),book.getBookId()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }
    //删除
    @Override
    public void delete(String id) {
        String sql = "delete from t_book where book_id=?";
        int update = jdbcTemplate.update(sql, id);
        System.out.println(update);
    }
}

测试代码如下

package com.lalala.spring5.test;

import com.lalala.spring5.entity.Book;
import com.lalala.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("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);

        //添加
        //Book book = new Book();
        //book.setBookId("1");
        //book.setBookname("java");
        //book.setBookstatus("a");
        //bookService.addBook(book);

        //修改
        //Book book = new Book();
        //book.setBookId("1");
        //book.setBookname("javajava");
        //book.setBookstatus("b");
        //bookService.updateBook(book);

        bookService.deleteBook("1");
    }
}

JdbcTemplate操作数据库（查询返回某个值）

BookService

package com.lalala.spring5.service;

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

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

    //查询表中记录数
    public int findCount(){
        return bookDao.selectCount();
    }
}

BookDao

package com.lalala.spring5.dao;

import com.lalala.spring5.entity.Book;

public interface BookDao {
    int selectCount();
}

BookDaoImpl

package com.lalala.spring5.dao;

import com.lalala.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 selectCount() {
        String sql = "select count(*) from t_book";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }
}

测试代码

package com.lalala.spring5.test;

import com.lalala.spring5.entity.Book;
import com.lalala.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("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        int count = bookService.findCount();
        System.out.println(count);
    }
}

JdbcTemplate操作数据库（查询返回对象）

BookService如下

package com.lalala.spring5.service;

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

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

    //查询返回对象
    public Book findOne(String id){
        return bookDao.findBookInfo(id);
    }
}

BookDao如下

package com.lalala.spring5.dao;

import com.lalala.spring5.entity.Book;

public interface BookDao {

    Book findBookInfo(String id);
}

BookDaoImpl如下

package com.lalala.spring5.dao;

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

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

    @Override
    public Book findBookInfo(String id) {
        String sql = "select * from t_book where book_id=?";
        /**
         * 三个参数
         *   第一个参数：sql语句
         *   第二个参数：RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装
         *   第三个参数：sql语句值
         */
        //调用方法
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }
}

测试代码如下

package com.lalala.spring5.test;

import com.lalala.spring5.entity.Book;
import com.lalala.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("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);

        Book book = bookService.findOne("1");
        System.out.println(book);
    }
}

JdbcTemplate操作数据库（查询返回集合）

BookService如下

package com.lalala.spring5.service;

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

import java.util.List;

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

    //查询返回集合
    public List<Book> findAll(){
        return bookDao.findAllBook();
    }
}

BookDao如下

package com.lalala.spring5.dao;

import com.lalala.spring5.entity.Book;

import java.util.List;

public interface BookDao {

    List<Book> findAllBook();
}

BookDaoImpl如下

package com.lalala.spring5.dao;

import com.lalala.spring5.entity.Book;
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 BookDaoImpl implements BookDao{
    //注入 JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Book> findAllBook() {
        String sql = "select * from t_book";
        //调用方法
        List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));

        return bookList;
    }
}

测试代码如下

package com.lalala.spring5.test;

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

import java.util.List;

public class TestBook {
    @Test
    public void testJdbcTemplate(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);

        List<Book> all = bookService.findAll();
        System.out.println(all);
    }
}

JdbcTemplate操作数据库（批量添加操作）

批量操作：操作表里面多条记录

BookService中如下

package com.lalala.spring5.service;

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

import java.util.List;

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

    //批量添加
    public void batchAdd(List<Object[]> batchArgs ){
        bookDao.batchAddBook(batchArgs);
    }
}

BookDao如下

package com.lalala.spring5.dao;

import java.util.List;

public interface BookDao {
    void batchAddBook(List<Object[]> batchArgs);
}

BookDaoImpl如下

package com.lalala.spring5.dao;

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

import java.util.Arrays;
import java.util.List;

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

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

测试方法如下

package com.lalala.spring5.test;

import com.lalala.spring5.entity.Book;
import com.lalala.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("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);

        //批量添加测试
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"3","java","a"};
        Object[] o2 = {"4","c++","b"};
        Object[] o3 = {"5","MySQL","c"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);

        //调用批量添加方法
        bookService.batchAdd(batchArgs);
    }
}

JdbcTemplate操作数据库（批量修改操作）

BookService

package com.lalala.spring5.service;

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

import java.util.List;

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

    //批量修改
    public void batchUpdate(List<Object[]> batchArgs ){
        bookDao.batchUpdateBook(batchArgs);
    }
}

BookDao如下

package com.lalala.spring5.dao;

import java.util.List;

public interface BookDao {
    //批量修改
    void batchUpdateBook(List<Object[]> batchArgs);
}

BookDaoImpl如下

package com.lalala.spring5.dao;

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

import java.util.Arrays;
import java.util.List;

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

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

测试代码如下

package com.lalala.spring5.test;

import com.lalala.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("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);

        //批量修改
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"java123","a1","3"};
        Object[] o2 = {"c++456","b1","4"};
        Object[] o3 = {"MySQL789","c1","5"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);

        //调用批量修改的方法
        bookService.batchUpdate(batchArgs);
    }
}

JdbcTemplate操作数据库（批量删除操作）

BookService如下

package com.lalala.spring5.service;

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

import java.util.List;

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

    //批量删除
    public void batchDelete(List<Object[]> batchArgs ){
        bookDao.batchDeleteBook(batchArgs);
    }
}

BookDao如下

package com.lalala.spring5.dao;

import java.util.List;

public interface BookDao {
    //批量删除
    void batchDeleteBook(List<Object[]> batchArgs);
}

BookDaoImpl如下

package com.lalala.spring5.dao;

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

import java.util.Arrays;
import java.util.List;

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

    @Override
    public void batchDeleteBook(List<Object[]> batchArgs) {
        String sql = "delete from t_book where book_id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }
}

测试代码如下

package com.lalala.spring5.test;

import com.lalala.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("bean.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.batchDelete(batchArgs);
    }
}