Spring5_JdbcTemplate操作

嘎嘎_膈

已于 2022-10-25 09:11:46 修改

阅读量305

点赞数

文章标签： java jar spring

于 2022-08-28 16:16:31 首次发布

本文链接：https://blog.csdn.net/wanananhh/article/details/126567134

版权

1.JdbcTemplate概念

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

2.准备工作

（1）引入jar包

（2）在Spring配置文件中配置连接池

prop.jdbcDriverClassName=com.mysql.jdbc.Driver
prop.url=jdbc:mysql://localhost:3306/bookdb?useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC
prop.username=root
prop.password=123456

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

        <context:property-placeholder location="jdbc.properties"></context:property-placeholder>
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
            <property name="driverClassName" value="${prop.jdbcDriverClassName}"></property>
            <property name="url" value="${prop.url}"></property>
            <property name="username" value="${prop.username}"></property>
            <property name="password" value="${prop.password}"></property>
        </bean>
</beans>

(3)创建JdbcTemplate对象，给JdbcTemplate中dataSource注入Datasource属值

注：用JdbcTemplate操作数据库连接池，所以用到配置文件属性注入，就是Spring操作Jdbc，jdbc将对象属性赋值给JdbcTemplate

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="datasource"></property>
    </bean>

（4）建立Service类和创建dao类并通过注解创建对象，Service中注入dao，daoz中注入JdbcTemplate属性，创建实体类，开启组件扫描

XMl文件

<context:component-scan base-package="Spring06"></context:component-scan>

实体类


public class Book {
    private int id;       //编号id
    private String bname; //书籍名称
    private String autor; //作者

    public Book() {
    }

    public Book(int id, String bname, String autor) {
        this.id = id;
        this.bname = bname;
        this.autor = autor;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBname() {
        return bname;
    }

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

    public String getAutor() {
        return autor;
    }

    public void setAutor(String autor) {
        this.autor = autor;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", bname='" + bname + '\'' +
                ", autor='" + autor + '\'' +
                '}';
    }
}

创建以及注入代码

import Spring06.Dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class BookService {
    @Autowired
    private BookDao bookDao;


}



import org.springframework.stereotype.Repository;

@Repository
public interface BookDao {

}



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

@Repository
public class BookDaoImpl implements BookDao{
    @Autowired
    private JdbcTemplate jdbctemplate;


}

3.JdbcTemplate操作数据库（添加操作）

public void insert(Book book){
        bookDao.insert(book);
    }


void insert(Book book);


@Override
    public void insert(Book book) {
        String sql="INSERT INTO book VALUES(?,?,?)";
        Object[] objects={book.getId(),book.getBname(),book.getAutor()};
        int update = jdbctemplate.update(sql, objects);
        System.out.println(update);
    }

@Test
    public void test1(){
        ApplicationContext context=new ClassPathXmlApplicationContext("bean6.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book=new Book();
        book.setId(1);
        book.setBname("西游记");
        book.setAutor("吴承恩");
        bookService.insert(book);
    }

4.JdbcTemplate操作数据库（修改操作）

public void update(Book book){
        bookDao.update(book);
    }


void update(Book book);


@Override
    public void update(Book book) {
        String sql="UPDATE book SET autor=? WHERE id=?";
        Object[] objects={book.getAutor(),book.getId()};
        int update = jdbctemplate.update(sql, objects);
        System.out.println(update);
    }

@Test
    public void test2(){
        BookService bookService = Application();
        Book book=new Book();
        book.setId(1);
        book.setAutor("吴承恩.");
        bookService.update(book);
    }

4.JdbcTemplate操作数据库（删除操作）

 public void delete(int id){
        bookDao.dalete(id);
    }


void dalete(int id);


@Override
    public void dalete(int id) {
        String sql="DELETE FROM book WHERE id=?";
        int update = jdbctemplate.update(sql, id);
        System.out.println(update);
    }

@Test
    public void test3(){
        BookService bookService = Application();
        bookService.delete(1);
    }

4.JdbcTemplate操作数据库（查询操作--单个值）

queryForObject方法中：第一个参数代表--sql语句；第二个参数代表--返回类型class

public int selectcount(){
        return bookDao.selectcount();
    }


int selectcount();


@Override
    public int selectcount() {
        String sql="SELECT COUNT(*) FROM book";
        Integer integer = jdbctemplate.queryForObject(sql, Integer.class);
        return integer;
    }

@Test
    public void test4(){
        BookService bookService = Application();
        int selectcount = bookService.selectcount();
        System.out.println(selectcount);
    }

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

queryForObject方法中：第一个参数：sql语句第二个参数：RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装第三个参数：sql 语句值

public Book selectObject(int id){
        return bookDao.selectObject(id);
    }


Book selectObject(int id);


@Override
    public Book selectObject(int id) {
        String sql="SELECT * FROM book WHERE id=?";
        Book book = jdbctemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }

@Test
    public void test5(){
        BookService bookService = Application();
        Book book = bookService.selectObject(4);
        System.out.println(book.toString());
    }

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

public List<Book> selectList(){
        return bookDao.selectList();
    }


List<Book> selectList();


@Override
    public List<Book> selectList() {
        String sql="SELECT * FROM book";
        List<Book> query = jdbctemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return query;
    }

@Test
    public void test6(){
        BookService bookService = Application();
        List<Book> books = bookService.selectList();
        System.out.println(books);
    }

7.JdbcTemplate操作数据库（批量增加）

//batchUpdate方法第一个参数：sql语句第二个参数：List集合，添加多条记录数据

注：批量插入的形式是以3数组的形式插入

public void batchinsert(List<Object[]> list){
        bookDao.batchinsert(list);
    }


void batchinsert(List<Object[]> list);


@Override
    public void batchinsert(List<Object[]> list) {
        String sql="INSERT INTO book VALUES(?,?,?)";
        int[] ints = jdbctemplate.batchUpdate(sql, list);
        System.out.println(ints.length);
    }

@Test
    public void test7(){
        BookService bookService = Application();
        List<Object[]> list=new ArrayList<>();
        Object[] objects1={"5","论语","孔子"};
        Object[] objects2={"6","孟子","孟子"};
        Object[] objects3={"7","大学","中庸"};
        list.add(objects1);
        list.add(objects2);
        list.add(objects3);
        bookService.batchinsert(list);
    }

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

public void batchupdate(List<Object[]> list){
        bookDao.batchupdate(list);
    }


void batchupdate(List<Object[]> list);


@Override
    public void batchupdate(List<Object[]> list) {
        String sql="UPDATE book SET autor=? WHERE id=?";
        int[] ints = jdbctemplate.batchUpdate(sql, list);
        System.out.println(ints.length);
    }

@Test
    public void test8(){
        BookService bookService = Application();
        List<Object[]> list=new ArrayList<>();
        Object[] objects1={"司马光",6};
        Object[] objects2={"司马亮",7};
        list.add(objects1);
        list.add(objects2);
        bookService.batchupdate(list);
    }

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

public void batchdelete(List<Object[]> list){
        bookDao.batchdelete(list);
    }


void batchdelete(List<Object[]> list);


@Override
    public void batchdelete(List<Object[]> list) {
        String sql="DELETE FROM book WHERE id=?";
        int[] ints = jdbctemplate.batchUpdate(sql, list);
        System.out.println(ints.length);
    }

 @Test
    public void test9(){
        BookService bookService = Application();
        List<Object[]> list=new ArrayList<>();
        Object[] objects1={6};
        Object[] objects2={7};
        list.add(objects1);
        list.add(objects2);
        bookService.batchdelete(list);
    }

10.总结

1.JdbcTemplate进行增删改操作没有返回值，有形参（涉及到批量操作用集合List<Object[]>，没涉及到批量操作用id值或者传类book），主要通过以数组的形式存储到集合中，进行调用方法操作

a.进行一条的增、删、改操作

b.进行多条的增、删、改操作

2.JdbcTemplate进行查询操作有返回值，涉及到批量操作用集合List<Book>作为返回值类型，没涉及到批量操作用用类型Book或者int(Integer)）

a.进行查询返回值是对象形式：

b.进行返回值类型是数值型式：

c.进行返回值类型式List集合形式：

11.总体代码

prop.jdbcDriverClassName=com.mysql.jdbc.Driver
prop.url=jdbc:mysql://localhost:3306/bookdb?useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC
prop.username=root
prop.password=123456

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

        <context:property-placeholder location="jdbc.properties"></context:property-placeholder>
        <bean id="datasource" class="com.alibaba.druid.pool.DruidDataSource">
            <property name="driverClassName" value="${prop.jdbcDriverClassName}"></property>
            <property name="url" value="${prop.url}"></property>
            <property name="username" value="${prop.username}"></property>
            <property name="password" value="${prop.password}"></property>
        </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="datasource"></property>
    </bean>

    <context:component-scan base-package="Spring06"></context:component-scan>
</beans>

package Spring06.Service;

import Spring06.Dao.BookDao;
import Spring06.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BookService {
    @Autowired
    private BookDao bookDao;

    public void insert(Book book){
        bookDao.insert(book);
    }

    public void update(Book book){
        bookDao.update(book);
    }

    public void delete(int id){
        bookDao.dalete(id);
    }

    public int selectcount(){
        return bookDao.selectcount();
    }

    public Book selectObject(int id){
        return bookDao.selectObject(id);
    }

    public List<Book> selectList(){
        return bookDao.selectList();
    }

    public void batchinsert(List<Object[]> list){
        bookDao.batchinsert(list);
    }

    public void batchupdate(List<Object[]> list){
        bookDao.batchupdate(list);
    }

    public void batchdelete(List<Object[]> list){
        bookDao.batchdelete(list);
    }

}

package Spring06.Dao;

import Spring06.entity.Book;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface BookDao {

    void insert(Book book);

    void update(Book book);

    void dalete(int id);

    int selectcount();

    Book selectObject(int id);

    List<Book> selectList();

    void batchinsert(List<Object[]> list);

    void batchupdate(List<Object[]> list);

    void batchdelete(List<Object[]> list);

}

package Spring06.Dao;

import Spring06.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{
    @Autowired
    private JdbcTemplate jdbctemplate;


    @Override
    public void insert(Book book) {
        String sql="INSERT INTO book VALUES(?,?,?)";
        Object[] objects={book.getId(),book.getBname(),book.getAutor()};
        int update = jdbctemplate.update(sql, objects);
        System.out.println(update);
    }

    @Override
    public void update(Book book) {
        String sql="UPDATE book SET autor=? WHERE id=?";
        Object[] objects={book.getAutor(),book.getId()};
        int update = jdbctemplate.update(sql, objects);
        System.out.println(update);
    }

    @Override
    public void dalete(int id) {
        String sql="DELETE FROM book WHERE id=?";
        int update = jdbctemplate.update(sql, id);
        System.out.println(update);
    }

    @Override
    public int selectcount() {
        String sql="SELECT COUNT(*) FROM book";
        Integer integer = jdbctemplate.queryForObject(sql, Integer.class);
        return integer;
    }

    @Override
    public Book selectObject(int id) {
        String sql="SELECT * FROM book WHERE id=?";
        Book book = jdbctemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }

    @Override
    public List<Book> selectList() {
        String sql="SELECT * FROM book";
        List<Book> query = jdbctemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return query;
    }

    @Override
    public void batchinsert(List<Object[]> list) {
        String sql="INSERT INTO book VALUES(?,?,?)";
        int[] ints = jdbctemplate.batchUpdate(sql, list);
        System.out.println(ints.length);
    }

    @Override
    public void batchupdate(List<Object[]> list) {
        String sql="UPDATE book SET autor=? WHERE id=?";
        int[] ints = jdbctemplate.batchUpdate(sql, list);
        System.out.println(ints.length);
    }

    @Override
    public void batchdelete(List<Object[]> list) {
        String sql="DELETE FROM book WHERE id=?";
        int[] ints = jdbctemplate.batchUpdate(sql, list);
        System.out.println(ints.length);
    }

}

package TestPackage;

import Spring06.Service.BookService;
import Spring06.entity.Book;
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 test06 {

    public BookService Application(){
        ApplicationContext context=new ClassPathXmlApplicationContext("bean6.xml");
        return context.getBean("bookService", BookService.class);
    }

    @Test
    public void test1(){
        BookService bookService = Application();
        Book book=new Book();
        book.setId(2);
        book.setBname("西游记");
        book.setAutor("吴承恩");
        bookService.insert(book);
    }

    @Test
    public void test2(){
        BookService bookService = Application();
        Book book=new Book();
        book.setId(1);
        book.setAutor("吴承恩.");
        bookService.update(book);
    }

    @Test
    public void test3(){
        BookService bookService = Application();
        bookService.delete(1);
    }

    @Test
    public void test4(){
        BookService bookService = Application();
        int selectcount = bookService.selectcount();
        System.out.println(selectcount);
    }

    @Test
    public void test5(){
        BookService bookService = Application();
        Book book = bookService.selectObject(4);
        System.out.println(book.toString());
    }

    @Test
    public void test6(){
        BookService bookService = Application();
        List<Book> books = bookService.selectList();
        System.out.println(books);
    }

    @Test
    public void test7(){
        BookService bookService = Application();
        List<Object[]> list=new ArrayList<>();
        Object[] objects1={"5","论语","孔子"};
        Object[] objects2={"6","孟子","孟子"};
        Object[] objects3={"7","大学","中庸"};
        list.add(objects1);
        list.add(objects2);
        list.add(objects3);
        bookService.batchinsert(list);
    }

    @Test
    public void test8(){
        BookService bookService = Application();
        List<Object[]> list=new ArrayList<>();
        Object[] objects1={"司马光",6};
        Object[] objects2={"司马亮",7};
        list.add(objects1);
        list.add(objects2);
        bookService.batchupdate(list);
    }

    @Test
    public void test9(){
        BookService bookService = Application();
        List<Object[]> list=new ArrayList<>();
        Object[] objects1={6};
        Object[] objects2={7};
        list.add(objects1);
        list.add(objects2);
        bookService.batchdelete(list);
    }

}