[Spring]Druid连接池+JdbcTemplate增删改查+批量增删改查

JdbcTemplate

1 什么是JdbcTemplate
1.1 Spring框架对JDBD进行封装,使用JdbcTemplate方便实现对数据库操作
1.2 引入maven坐标
1.3 application.xml 数据库连接池Druid配置
1.4 application.xml 配置JdbcTemplate对象,注入DataSource
1.5 pojo类Book–BookDao接口–BookDaoImpl(注入jdbctemplate对象)–BookService(注入BookDaoImpl对象)

实现功能

增删改查
批量增删改查

编码步骤
1 创建项目目录;配置application.xml或者配置类
1.1 application.xml
复制约束–开启包扫描–开启注释–druild数据连接池注册–jdbc注册

注意
druid的dataSource路径: com.alibaba.druid.pool.DruidDataSource
driver的路径: com.mysql.jdbc.Driver
jdbc的路径: org.springframework.jdbc.core.JdbcTemplate

2 entity类(此处我命名为pojo差不多)–dao类–service类–test类使用注解开发(ioc)部分
编写pojo类多写了bind()和parse()；注解编程了多例模式 @Scope(“prototype”)
编写Dao接口和DaoImpl – 编写Service – test类调用

pom.xml

   
      
      
        junit
        junit
        4.11
        test
      


      
      
        com.alibaba
        druid
        1.2.9
      
      
        mysql
        mysql-connector-java
        5.1.47
      




      
        org.springframework
        spring-context
        5.3.16
      

      
        org.testng
        testng
        RELEASE
        compile
      

      
        org.slf4j
        slf4j-nop
        1.7.2
        jar
      

      
        javax.annotation
        javax.annotation-api
        1.3.2
      

      
        org.aspectj
        aspectjweaver
        1.8.13
      

      
        org.springframework
        spring-jdbc
        5.3.15
      
      
        org.springframework
        spring-tx
        5.3.15
      

      
        org.springframework
        spring-orm
        5.1.7.RELEASE

application.xml

Book.java

package com.middlename.pojo;

import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import javax.annotation.Nullable;

@Component()
@Scope("prototype")
public class Book {
    private Integer bookid;
    private String bookname;
    private String author;

    public void setBookid(Integer bookid) {
        this.bookid = bookid;
    }

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

    public void setAuthor(String author) {
        this.author = author;
    }

    public void bind(Integer id,String name,String author){
        this.setBookid(id);
        this.setBookname(name);
        this.setAuthor(author);
    }

    public Object[] parse(@Nullable Object... rest){
        return new Object[]{this.bookid,this.bookname,this.author};
    }

    public Object[] parsewithid(Integer id){
        return new Object[]{this.bookid,this.bookname,this.author,id};
    }

    @Override
    public String toString() {
        return "Book{" +
                "bookid=" + bookid +
                ", bookname='" + bookname + ''' +
                ", author='" + author + ''' +
                '}';
    }
}

BookDao.java

package com.middlename.dao;

import com.middlename.pojo.Book;

import java.util.List;

public interface BookDao {
    public void add(Object[] bookargs);
    public void modify(Book info,Integer id);
    public void delete(Integer id);
    public void findCount();
    public Book findByid(Integer id);
    public List findAll();
    public void batchadd(List books);
    public void batchdelete(List indes);
    public void batchmodify();
}

BookDaoImpl.java

package com.middlename.dao;

import com.middlename.pojo.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.ArrayList;
import java.util.Arrays;
import java.util.List;

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

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void add(Object[] bookargs) {
        String sql = "insert into book values(?,?,?)";
        int i = jdbcTemplate.update(sql, bookargs);
        System.out.println(i);
    }

    @Override
    public void modify(Book info,Integer id) {
        Object[] args = info.parsewithid(id);
        String sql = "update book set bookid=?,bookname=?,author=? where bookid=?";
        int i = jdbcTemplate.update(sql, args);
        System.out.println(i);
    }

    @Override
    public void delete(Integer id) {
        String sql = "delete from book where bookid = ?";
        Object[] args = new Object[]{id};
        int i = jdbcTemplate.update(sql, args);
        System.out.println(i);
    }

    @Override
    public void findCount() {
        String sql = "select count(*) from book";
        Integer i = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(i);
    }

    @Override
    public Book findByid(Integer id) {
        String sql = "select * from book where bookid = ?";
        Object[] args = new Object[]{id};
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(Book.class),args);

        System.out.println(book);
        return book;
    }

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

        for(Book ite : list){
            System.out.println(ite);
        }

        return list;
    }

    @Override
    public void batchadd(List books) {
        List argslist = new ArrayList();
        for(Book ite:books){
            argslist.add(ite.parse());
            System.out.println(ite);
        }

        String sql = "insert into book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, argslist);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void batchdelete(List indes) {
        String sql = "delete from book where bookid = ?";

        int[] ints = jdbcTemplate.batchUpdate(sql, indes);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void batchmodify() {
        List argslist = new ArrayList();
        String sql = "update book set bookid=?,bookname=?,author=? where bookid=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, argslist);
        System.out.println(Arrays.toString(ints));
    }
}

BookService.java

package com.middlename.service;

import com.middlename.dao.BookDao;
import com.middlename.pojo.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

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

    public void setBookdao(BookDao bookdao) {
        this.bookdao = bookdao;
    }

    public void add(Book info){
        Object[] bookinfo = info.parse();
        bookdao.add(bookinfo);
    }

    public void modify(Book info,Integer id){
        bookdao.modify(info,id);
    }

    public void delete(Integer id){
        bookdao.delete(id);
    }

    public void findCount(){
        bookdao.findCount();
    }

    public Book findByid(Integer id){
        return bookdao.findByid(id);
    }

    public List findall(){
        return bookdao.findAll();
    }

    public void batchadd(List books){
        bookdao.batchadd(books);
    }

    public void batchdelete(){
        Object[] ob1 = new Object[]{1};
        Object[] ob2 = new Object[]{2};
        Object[] ob3 = new Object[]{3};
        Object[] ob4 = new Object[]{666};
        List l = new ArrayList();
        l.add(ob1);l.add(ob2);l.add(ob3);l.add(ob4);
        bookdao.batchdelete(l);
    }
}

Test.java

    public void passedTest() throws SQLException {
        ApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        //1 测试连接池配置
        DruidDataSource dataSource = context.getBean("dataSource",DruidDataSource.class);
        Connection conn = dataSource.getConnection();
        conn.close();

        //2 测试jdbcTemplate
        JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate",JdbcTemplate.class);
        String sql = "select count(*) from book";
        Integer in = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(in);

        //3 测试增删改查
        Book info = context.getBean("book", Book.class);info.bind(222,"海贼王","尾田");
        BookService bookService = context.getBean("bookService", BookService.class);
        //3.1 测试增
        bookService.add(info);
        //3.2 测试改
        Book info2 = context.getBean("book", Book.class);info.bind(444,"海贼王","尾田");
        bookService.modify(info2,111);
        //3.3 测试删
        bookService.delete(444);
        //3.4 测试查
        bookService.findCount();
        System.out.println(bookService.findByid(333));
        bookService.findall();

        //4 测试批量操作
        //批量添加
        Book info1 = context.getBean("book", Book.class);info1.bind(1,"海贼王","尾田");
        Book info4 = context.getBean("book", Book.class);info4.bind(2,"海贼王","尾田");
        Book info3 = context.getBean("book", Book.class);info3.bind(3,"海贼王","尾田");
        List list1 = new ArrayList();
        list1.add(info1);list1.add(info4);list1.add(info3);
        bookService.batchadd(list1);
        //批量删
        bookService.batchdelete();
        //批量修改不测试了,封装参数有点麻烦
    }

项目结构和book表结构

总结
jdbc简化了数据库的操作
1 调用方法
jdbcTemplate.update(sql,arg);
public int update(String sql, @Nullable Object… args) throws DataAccessException

jdbcTemplate.queryForObject(sql,Integer.class);
public T queryForObject(String sql, Class requiredType) throws DataAccessException

jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(Book.class), id);
public T queryForObject(String sql, RowMapper rowMapper, @Nullable Object… args) throws DataAccessException

jdbcTemplate.query(sql, new BeanPropertyRowMapper(Book.class));
public List query(String sql, RowMapper rowMapper) throws DataAccessException

jdbcTemplate.batchUpdate(sql, batchArgs);
public int[] batchUpdate(String sql, List batchArgs) throws DataAccessException

2 占位符绑定参数用Object[]数组按顺序匹配
3 结果集映射实现RowMapper接口 java反射机制

demo地址 https://github.com/lujie3/lujieSpringSeries

[Spring]Druid连接池+JdbcTemplate增删改查+批量增删改查

Java相关栏目本月热门文章