34-39、Spring5之JdbcTemplate操作数据库-增删改查

诚实可靠小郎俊
143 阅读6分钟

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

package com.atguigu.spring5.entity;
public class User {
    private Integer userId;
    private String username;
    private String password;
    private String email;
    public Integer getUserId() {
        return userId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
}

2、编写service和dao

  • （1）在dao进行数据库（添加、编辑、删除）操作
  • （2）调用jdbcTemplate对象里面update方法实现添加操作 updata(Sting sql, Object...args); 第一个参数：sql语句 第二个参数：可变参数，设置sql语句值

添加

package com.atguigu.spring5.dao;

import com.atguigu.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) {
        // 1 创建sql语句
        String sql = "insert into t_user value(?,?,?)";
        // 2 调用方法实现
        Object[] args = {book.getUsername(), book.getPassword(), book.getEmail()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }
}

添加测试代码

package com.atguigu.spring5.test;

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

import javax.sql.DataSource;
import java.sql.SQLException;

public class testBook {
    @Test
    public void testJDBC() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        DataSource dataSource = context.getBean("dataSource", DataSource.class);
        System.out.println("连接成功" + dataSource);
    }

    @Test
    public void testJdbcTemplateAdd() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
        book.setUsername("zhupeiya2");
        book.setPassword("123456");
        book.setEmail("zhupeiya@qq.com");
        bookService.addBook(book);
    }
}

修改

BookService文件

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

BookDao文件

// 修改的方法
void updateBook(Book book);

BookDaoImpl文件

// 修改的方法
@Override
public void updateBook(Book book) {
    String sql = "update t_user set username=?, password=?, email=? where id=?";
    Object[] args = {book.getUsername(), book.getPassword(), book.getEmail(), book.getUserId()};
    int update = jdbcTemplate.update(sql, args);
    System.out.println(update);
}

编辑测试代码

@Test
public void testJdbcTemplateUpdate() {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    Book book = new Book();
    book.setUsername("manyue");
    book.setPassword("123456");
    book.setEmail("manyue@qq.com");
    book.setUserId(17);
    bookService.updateBook(book);
}

删除

BookService文件

// 删除的方法
public void deleteBook(Integer id) {
    bookDao.deleteBook(id);
}

BookDao文件

// 删除的方法
void deleteBook(Integer id);

BookDaoImpl文件

//  删除的方法
@Override
public void deleteBook(Integer id) {
    String sql = "delete from t_user where id=?";
    int update = jdbcTemplate.update(sql, id);
    System.out.println(update);
}

编辑测试代码

@Test
public void testJdbcTemplateDelete() {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    Book book = new Book();
    bookService.deleteBook(20);
}

查询

查询返回某个值
  • 1、查询表里有多少条记录，返回时某个值
  • 2、使用jdbcTemplate实现查询返回某个值代码jdbcTemplate.queryForObject(String sql, Class<T> requiredType);第一个参数：sql语句；第二个参数：返回类型Class

BookService文件

public int findCount() {
     return bookDao.selectCount();
}

BookDao文件

// 查询表记录数
int selectCount();

BookDaoImpl文件

// 查询表记录数
@Override
public int selectCount() {
    String sql = "select count(*) from t_user";
    Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
    return integer;
}

编辑测试代码

@Test
public void testJdbcTemplateSelectCount() {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    int count = bookService.findCount();
    System.out.println(count);
}
查询返回对象
  • 1、场景：查询图书详情
  • 2、使用jdbcTemplate实现查询返回对象jdbcTemplate.queryForObject(String sql, RowMapper<T> rowMapper, Object...args);第一个参数：sql语句；第二个参数：RowMapper是接口返回不同类型数据，使用这个接口里面实现类完成数据封装；第三个参数：sql语句?值

BookService文件

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

BookDao文件

// 查询返回对象
Book findBookInfo(Integer id);

BookDaoImpl文件

// 查询返回对象
@Override
public Book findBookInfo(Integer id) {
    String sql = "select * from t_user where id=?";
    Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
    return book;
}

编辑测试代码

@Test
public void testJdbcTemplateFindBookInfo() {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    Book book = bookService.findOne(18);
    System.out.println(book);
}
查询返回集合
  • 1、场景：查询图书列表分页
  • 2、调用jdbcTemplate方法实现查询返回集合jdbcTemplate.query(String sql, RowMapper<T> rowMapper, Object...args);第一个参数：sql语句；第二个参数：RowMapper是接口返回不同类型数据，使用这个接口里面实现类完成数据封装；第三个参数：sql语句?值

BookService文件

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

BookDao文件

// 查询返回集合
List<Book> findAllBook();

BookDaoImpl文件

// 查询返回集合
@Override
public List<Book> findAllBook() {
    String sql = "select * from t_user";
    List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
    return query;
}

编辑测试代码

@Test
public void testJdbcTemplateFindBookAll() {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    List<Book> all = bookService.findAll();
    System.out.println(all);
}
批量添加操作
  • 1、批量操作：操作表里面多条记录
  • 2、调用jdbcTemplate实现批量添加操作jdbcTemplate.batchUpdate(String sql, List<Object[] batchArgs>);第一个参数：sql语句；第二个参数：List集合，添加多条记录数据

BookService文件

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

BookDao文件

// 批量添加
void batchAddBook(List<Object[]> batchArgs);

BookDaoImpl文件

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

编辑测试代码

@Test
public void testJdbcTemplateBatchAddBook() {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    List<Object[]> objects = new ArrayList<>();
    Object[] o1 = {"wang4","123456","wang4@qq.com"};
    Object[] o2 = {"wang5","123456","wang5@qq.com"};
    Object[] o3 = {"wang6","123456","wang6@qq.com"};
    objects.add(o1);
    objects.add(o2);
    objects.add(o3);
    bookService.batchAdd(objects);
}
批量修改操作
  • 1、批量操作：操作表里面多条记录
  • 2、调用jdbcTemplate实现批量添加操作jdbcTemplate.batchUpdate(String sql, List<Object[] batchArgs>);第一个参数：sql语句；第二个参数：List集合，添加多条记录数据

BookService文件

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

BookDao文件

// 批量修改
void batchUpdateBook(List<Object[]> batchArgs);

BookDaoImpl文件

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

编辑测试代码

@Test
public void testJdbcTemplateBatchUpdateBook() {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    List<Object[]> objects = new ArrayList<>();
    Object[] o1 = {"wang22","123456","wang4@qq.com",22};
    Object[] o2 = {"wang23","123456","wang5@qq.com",23};
    Object[] o3 = {"wang24","123456","wang6@qq.com",24};
    objects.add(o1);
    objects.add(o2);
    objects.add(o3);
    bookService.batchUpdate(objects);
}
批量删除操作
  • 1、批量操作：操作表里面多条记录
  • 2、调用jdbcTemplate实现批量添加操作jdbcTemplate.batchUpdate(String sql, List<Object[] batchArgs>);第一个参数：sql语句；第二个参数：List集合，添加多条记录数据

BookService文件

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

BookDao文件

// 批量删除
void batchDeleteBook(List<Object[]> batchArgs);

BookDaoImpl文件

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

编辑测试代码

@Test
public void testJdbcTemplateBatchDeleteBook() {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    List<Object[]> objects = new ArrayList<>();
    Object[] o1 = {22};
    Object[] o2 = {23};
    Object[] o3 = {24};
    objects.add(o1);
    objects.add(o2);
    objects.add(o3);
    bookService.batchDelete(objects);
}

完整代码

BookService文件
package com.atguigu.spring5.service;

import com.atguigu.spring5.dao.BookDao;
import com.atguigu.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
    private BookDao bookDao;

    // 添加的方法
    public void addBook(Book book) {
        bookDao.add(book);
    }
    // 修改的方法
    public void updateBook(Book book) {
        bookDao.updateBook(book);
    }
    // 删除的方法
    public void deleteBook(Integer id) {
        bookDao.deleteBook(id);
    }
    // 查询表记录数
    public int findCount() {
         return bookDao.selectCount();
    }
    // 查询返回对象
    public Book findOne(Integer id) {
        return bookDao.findBookInfo(id);
    }
    // 查询返回集合
    public List<Book> findAll() {
        return bookDao.findAllBook();
    }
    // 批量添加
    public void batchAdd(List<Object[]> batchArgs) {
        bookDao.batchAddBook(batchArgs);
    }
    // 批量修改
    public void batchUpdate(List<Object[]> batchArgs) {
        bookDao.batchUpdateBook(batchArgs);
    }

    // 批量删除
    public void batchDelete(List<Object[]> batchArgs) {
        bookDao.batchDeleteBook(batchArgs);
    }
}
BookDao文件
package com.atguigu.spring5.dao;

import com.atguigu.spring5.entity.Book;

import java.util.List;

public interface BookDao {
    // 添加的方法
    public void add(Book book);
    // 修改的方法
    void updateBook(Book book);
    // 删除的方法
    void deleteBook(Integer id);
    // 查询表记录数
    int selectCount();
    // 查询返回对象
    Book findBookInfo(Integer id);
    // 查询返回集合
    List<Book> findAllBook();
    // 批量添加
    void batchAddBook(List<Object[]> batchArgs);
    // 批量修改
    void batchUpdateBook(List<Object[]> batchArgs);
    // 批量删除
    void batchDeleteBook(List<Object[]> batchArgs);
}
BookDaoImpl文件
package com.atguigu.spring5.dao;

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

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

    // 添加的方法
    @Override
    public void add(Book book) {
        // 1 创建sql语句
        String sql = "insert into t_user(`username`, `password`,`email`) value(?,?,?)";
        // 2 调用方法实现
        Object[] args = {book.getUsername(), book.getPassword(), book.getEmail()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }
    // 修改的方法
    @Override
    public void updateBook(Book book) {
        String sql = "update t_user set username=?, password=?, email=? where id=?";
        Object[] args = {book.getUsername(), book.getPassword(), book.getEmail(), book.getUserId()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }
    //  删除的方法
    @Override
    public void deleteBook(Integer id) {
        String sql = "delete from t_user where id=?";
        int update = jdbcTemplate.update(sql, id);
        System.out.println(update);
    }
    // 查询表记录数
    @Override
    public int selectCount() {
        String sql = "select count(*) from t_user";
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
        return integer;
    }
    // 查询返回对象
    @Override
    public Book findBookInfo(Integer id) {
        String sql = "select * from t_user where id=?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }
    // 查询返回集合
    @Override
    public List<Book> findAllBook() {
        String sql = "select * from t_user";
        List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return query;
    }
    // 批量添加
    @Override
    public void batchAddBook(List<Object[]> batchArgs) {
        String sql = "insert into t_user(`username`, `password`,`email`) value(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }
    // 批量修改
    @Override
    public void batchUpdateBook(List<Object[]> batchArgs) {
        String sql = "update t_user set username=?, password=?, email=? where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }
    // 批量删除
    @Override
    public void batchDeleteBook(List<Object[]> batchArgs) {
        String sql = "delete from t_user where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }


}
编辑测试代码
package com.atguigu.spring5.test;

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

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class testBook {
    @Test
    public void testJdbcTemplateAdd() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
        book.setUsername("zhupeiya2");
        book.setPassword("123456");
        book.setEmail("zhupeiya@qq.com");
        bookService.addBook(book);
    }
    @Test
    public void testJdbcTemplateUpdate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
        book.setUsername("manyue");
        book.setPassword("123456");
        book.setEmail("manyue@qq.com");
        book.setUserId(17);
        bookService.updateBook(book);
    }
    @Test
    public void testJdbcTemplateDelete() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
        bookService.deleteBook(20);
    }
    @Test
    public void testJdbcTemplateSelectCount() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        int count = bookService.findCount();
        System.out.println(count);
    }
    @Test
    public void testJdbcTemplateFindBookInfo() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = bookService.findOne(18);
        System.out.println(book);
    }
    @Test
    public void testJdbcTemplateFindBookAll() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Book> all = bookService.findAll();
        System.out.println(all);
    }

    @Test
    public void testJdbcTemplateBatchAddBook() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> objects = new ArrayList<>();
        Object[] o1 = {"wang4","123456","wang4@qq.com"};
        Object[] o2 = {"wang5","123456","wang5@qq.com"};
        Object[] o3 = {"wang6","123456","wang6@qq.com"};
        objects.add(o1);
        objects.add(o2);
        objects.add(o3);
        bookService.batchAdd(objects);
    }
    @Test
    public void testJdbcTemplateBatchUpdateBook() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> objects = new ArrayList<>();
        Object[] o1 = {"wang22","123456","wang4@qq.com",22};
        Object[] o2 = {"wang23","123456","wang5@qq.com",23};
        Object[] o3 = {"wang24","123456","wang6@qq.com",24};
        objects.add(o1);
        objects.add(o2);
        objects.add(o3);
        bookService.batchUpdate(objects);
    }
    @Test
    public void testJdbcTemplateBatchDeleteBook() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> objects = new ArrayList<>();
        Object[] o1 = {22};
        Object[] o2 = {23};
        Object[] o3 = {24};
        objects.add(o1);
        objects.add(o2);
        objects.add(o3);
        bookService.batchDelete(objects);
    }

}

遇到的报错情况

如果测试有报错# org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection的错误，请看一下 mysql-connector-java-8.0.29.jar这个jar包和你数据库版本一样不，不一样换成一样就ok了

avatar
文章
阅读
粉丝