[Java知识库]Sping5框架 JdbcTemplate 对数据库表的增 删，改，查代码

引入jar包

?配置jdbctmplatepei配置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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">

        <property name="url" value="jdbc:mysql://10.0.0.8:3306/test" />
        <property name="username" value="ecc_event_management" />
        <property name="password" value="94020212" />
        <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>

    <!-- 组件扫描 -->
    <context:component-scan base-package="sun.jdbctemp"></context:component-scan>


</beans>

在数据库端创建表：

进入test库
use test;
创建表
create table s_book(userid varchar(300),username varchar(300),ustatus varchar(20));
查询
select * from s_book;

1.实体层 pojo或者emtry ：主要为数据库表的对象

package sun.jdbctemp.pojo;

import org.springframework.stereotype.Repository;

/**
 * @author sunyc
 * @create 2022-04-13 14:13
 */
@Repository
public class Book {
    @Override
    public String toString() {
        return
                "userid='" + userid + '\'' +
                ", username='" + username + '\'' +
                ", ustatus='" + ustatus ;
    }

    private String userid;
    private String username;
    private String ustatus;
    public Book() {
    }
    public Book(String userid, String username, String ustatus) {
        this.userid = userid;
        this.username = username;
        this.ustatus = ustatus;
    }
    public String getUserid() {
        return userid;
    }
    public void setUserid(String userid) {
        this.userid = userid;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getUstatus() {
        return ustatus;
    }
    public void setUstatus(String ustatus) {
        this.ustatus = ustatus;
    }
}

2.Dao层：主要是实现对数据库表的操作

?dao层接口：

package sun.jdbctemp.dao;
import org.springframework.stereotype.Repository;
import sun.jdbctemp.pojo.Book;
import java.util.List;
/**
 * @author sunyc
 * @create 2022-04-13 14:04
 */
@Repository
public interface BookDao {
    //添加数据
    public void add(Book book);
    //删除数据
    public void delete(String id);
    //修改数据
    public void update(Book book);
    //查询表中数据总数
    int findCount();
    //查询返回对象
    Book findList(String id);
    //返回list集合 多条数据
    List<Book> findAll(String min, String max);
    //批量插入 入参为list集合类型位object数组的方式
    void batchAdd(List<Object[]> args);
    //批量更新 入参为list集合类型位object数组的方式
    void batchUpdate(List<Object[]> args);
    //批量删除 入参为list集合类型位object数组的方式
    void batchDelete(List<Object[]> args);
}

dao层接口实现类：

package sun.jdbctemp.dao;
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 sun.jdbctemp.pojo.Book;
import java.util.Arrays;
import java.util.List;
/**
 * @author sunyc
 * @create 2022-04-13 14:04
 */
@Repository
public class BookDaoImpl implements BookDao{
    @Override//批量删除  入参为list集合类型位object数组的方式
    public void batchDelete(List<Object[]> args) {
        String sql="delete from s_book where userid=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println("BookDaoImpl.batchDelete 方法删除了:"+ints.length);
    }
    @Override//批量更新  入参为list集合类型位object数组的方式
    public void batchUpdate(List<Object[]> args) {
        String sql="update s_book set username=?,ustatus=? where userid=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println("BookDaoImpl.batchUpdate 方法更新了:"+ ints.length);
    }
    @Override //批量插入  入参为list集合类型位object数组的方式
    public void batchAdd(List<Object[]> args) {
        String sql="insert into s_book(userid,username,ustatus) values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println("BookDaoImpl.batchAdd方法增加了:"+ints.length);
    }
    //注入jdbctemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override//添加数据
    public void add(Book book) {
        String sql="insert into s_book(userid,username,ustatus) values(?,?,?)";
        int update = jdbcTemplate.update(sql, book.getUserid(), book.getUsername(), book.getUstatus());
        System.out.println("BookDaoImpl.add方法增加了:"+update);
    }
    @Override //删除数据
    public void delete(String id) {
        String sql="delete from s_book where userid=?";
        int update = jdbcTemplate.update(sql, id);
        System.out.println("BookDaoImpl.delete方法删除了:"+update);
    }
    @Override//修改数据
    public void update(Book book) {
        String sql="update s_book set username=?,ustatus=? where userid=?";
        int update = jdbcTemplate.update(sql, book.getUsername(),book.getUstatus(),book.getUserid());
        System.out.println("BookDaoImpl.update方法更新了:"+update);
    }
    @Override//查询表中数据总数
    public int findCount() {
        String sql="select  count(*) from s_book";
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println("BookDaoImpl.findCount方法更新了:"+integer);
        return integer;
    }
    @Override //查询返回对象
    public Book findList(String id) {
        String sql="select  * from s_book where userid=?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Book.class), id);
        return book;
    }
    @Override//返回list集合 多条数据
    public List<Book> findAll(String min, String max) {
        String sql="select  * from s_book where userid>=? and userid<=?";
        List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class), min, max);
        return query;
    }

}

Service层：

package sun.jdbctemp.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import sun.jdbctemp.dao.BookDao;
import sun.jdbctemp.pojo.Book;

import java.util.List;

/**
 * @author sunyc
 * @create 2022-04-13 14:03
 */
@Service
public class BookService {
    //注入bookdao对象 相当于 BookDao book=new BookDaoImpl（）；
    @Autowired
    private BookDao bookDao;
    //添加数据
    public void add(Book book){
        bookDao.add(book);
    }
    //删除数据
    public void delete(String id){
        bookDao.delete(id);
    }
    //修改数据
    public void update(Book book){
        bookDao.update(book);
    }
    //查询表中数据总数
    public int  findCount(){
        return bookDao.findCount();
    }
    //查询返回一个对象
    public Book findList(String id){
       return bookDao.findList(id);
    }
    //返回list集合 多条数据
    public List<Book> findAll(String min,String max){
        return bookDao.findAll(min,max);
    }
    //批量插入  入参为list集合类型位object数组的方式
    public void batchAdd(List<Object[]> args){
        bookDao.batchAdd(args);
    }
    //批量更新  入参为list集合类型位object数组的方式
    public void batchUpdate(List<Object[]> args){
        bookDao.batchUpdate(args);
    }
    //批量删除  入参为list集合类型位object数组的方式
    public void batchDelete(List<Object[]> args){
        bookDao.batchDelete(args);
    }

}

测试：

package sun.jdbctemp.test;

import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import sun.jdbctemp.pojo.Book;
import sun.jdbctemp.service.BookService;
import sun.jdbctemp.service.SunBookService;

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

/**
 * @author sunyc
 * @create 2022-04-13 14:23
 */
class BookDaoImplTest {
    @Test批量删除
    public void batchDelete() {
        ApplicationContext context=new ClassPathXmlApplicationContext("mysqlbean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        //批量添加测试  入参为list集合类型位object数组的方式
        List<Object[]> list=new ArrayList<>();
        list.add(new Object[]{"10"});
        list.add(new Object[]{"11"});
        list.add(new Object[]{"12"});
        //添加
        bookService.batchDelete(list);
    }
    @Test批量更新
    public void batchUpdate() {
        ApplicationContext context=new ClassPathXmlApplicationContext("mysqlbean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        //批量添加测试  入参为list集合类型位object数组的方式
        List<Object[]> list=new ArrayList<>();
        list.add(new Object[]{"sunc","N","10"});
        list.add(new Object[]{"sunc","N","11"});
        list.add(new Object[]{"sunc","N","12"});
        //添加
        bookService.batchUpdate(list);
    }
    @Test批量插入
    public void batchAdd() {
        ApplicationContext context=new ClassPathXmlApplicationContext("mysqlbean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        //批量添加测试  入参为list集合类型位object数组的方式
        List<Object[]> list=new ArrayList<>();
        list.add(new Object[]{"10","10","N"});
        list.add(new Object[]{"11","10","N"});
        list.add(new Object[]{"12","10","N"});
        //添加
        bookService.batchAdd(list);
    }
    @Test//返回list集合 多条数据
    public void findAll() {
        ApplicationContext context=new ClassPathXmlApplicationContext("mysqlbean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        System.out.println(bookService.findAll("1","3"));
    }
    @Test//返回一个对象
    public void findList() {
        ApplicationContext context=new ClassPathXmlApplicationContext("mysqlbean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        System.out.println(bookService.findList("1"));
    }
    @Test//返回数据表的条数
    public void findCount() {
        ApplicationContext context=new ClassPathXmlApplicationContext("mysqlbean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        bookService.findCount();
    }
    @Test//更新单条数据
    public void update() {
        ApplicationContext context=new ClassPathXmlApplicationContext("mysqlbean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        bookService.update(new Book("1","sunchao","Y"));
    }
    @Test//删除单条数据
    public void delete() {
        ApplicationContext context=new ClassPathXmlApplicationContext("mysqlbean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        bookService.delete("1");
    }
    @Test//添加单条数据
    public void add() {
        ApplicationContext context=new ClassPathXmlApplicationContext("mysqlbean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        bookService.add(new Book("3","sunchao","N"));
    }
}

超强干货来袭

云风专访：近40年码龄，通宵达旦的技术人生