Spring5入门到实战------12、使用JdbcTemplate操作数据库（增删改查）。具体代码+讲解 【上篇】
Spring5入门到实战------13、使用JdbcTemplate操作数据库（批量增删改）。具体代码+讲解 【下篇】
以上两篇采用的是注解开发形式+xml配置文件形式的混合。这一篇讲解完全采用注解开发的形式来完成以上操作（实际开发中也是使用注解的多）。只需要做一个微小的改动便可完成以上操作。
1、新增配置类代替xml文件
这里需要考虑两个问题：
- 如何创建数据库连接池在配置类中？
- jdbcTemplate的对象如何在配置类创建？
新增一个配置类:MyConfig.java
/**
* @author Lenovo
* @version 1.0
* @data 2022/10/26 8:42
*/
@Configuration //配置类
@ComponentScan(basePackages = "com.zyz")//组件扫描
public class MyConfig {
/**
* 创建数据库连接池
*/
@Bean
public DruidDataSource getDruidDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/user_db");
dataSource.setUsername("root");
dataSource.setPassword("root");
return dataSource;
}
/**
* 创建 JdbcTemplate 对象
*/
@Bean
public JdbcTemplate getJdbcTemplate(DataSource dataSource) {
//到 ioc 容器中根据类型找到 dataSource
JdbcTemplate jdbcTemplate = new JdbcTemplate();
//注入 dataSource
jdbcTemplate.setDataSource(dataSource);
return jdbcTemplate;
}
}
测试的时候、由读取xml文件改为读取配置类
ApplicationContext context =
new AnnotationConfigApplicationContext(MyConfig.class);
只要在原先代码基础上做出以上改动就可以了、以下的代码完全可以不用在看了、基本上都是一样的。贴代码的主要原因、方便记录以下
2、上下篇完整项目结构及代码（采用完全注解形式）
2.1 项目结构
2.2 MyConfig.java
package com.zyz.spring5.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
/**
* @author Lenovo
* @version 1.0
* @data 2022/10/26 8:42
*/
@Configuration //配置类
@ComponentScan(basePackages = "com.zyz")//组件扫描
public class MyConfig {
/**
* 创建数据库连接池
*/
@Bean
public DruidDataSource getDruidDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/user_db");
dataSource.setUsername("root");
dataSource.setPassword("root");
return dataSource;
}
/**
* 创建 JdbcTemplate 对象
*/
@Bean
public JdbcTemplate getJdbcTemplate(DataSource dataSource) {
//到 ioc 容器中根据类型找到 dataSource
JdbcTemplate jdbcTemplate = new JdbcTemplate();
//注入 dataSource
jdbcTemplate.setDataSource(dataSource);
return jdbcTemplate;
}
}
2.3 BookDao.java
package com.zyz.spring5.dao;
import com.zyz.spring5.entity.Book;
import java.util.List;
/**
* @author Lenovo
* @version 1.0
* @data 2022/10/23 15:53
*/
public interface BookDao {
/**
* 添加
* @param book
*/
void addBook(Book book);
/**
* 修改
* @param book
*/
void updateBook(Book book);
/**
* 删除
* @param id
*/
void deleteBook(int id);
/**
* 查询表记录数
*/
int selectCount();
/**
* 查询对象
*/
Book selectOneBook(int id);
/**
* 查询全部对象
*/
List<Book> selectAllBook();
/**
* 批量添加
* @param batchArgs
* @return
*/
void batchAdd(List<Object[]> batchArgs);
/**
* 批量修改
* @param batchArgs
* @return
*/
void batchUpdate(List<Object[]> batchArgs);
/**
* 批量删除
* @param batchArgs
* @return
*/
void batchDelete(List<Object[]> batchArgs);
}
2.4 BookDaoImpl.java
package com.zyz.spring5.dao;
import com.zyz.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;
/**
* @author Lenovo
* @version 1.0
* @data 2022/10/23 15:53
*/
@Repository
public class BookDaoImpl implements BookDao{
/**
* 注入jdbcTemplate
*/
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void addBook(Book book) {
//1、创建sql语句
String sql = "insert into t_book values(?,?,?)";
//2、调用方法
Object [] args ={book.getBook_id(),book.getBook_name(),book.getIsSale()};
int update = jdbcTemplate.update(sql,args);
System.out.println(update);
}
@Override
public void updateBook(Book book) {
//1、创建sql语句
String sql = "update t_book set book_name=?,isSale=? where book_id=?";
//2、调用方法
Object [] args ={book.getBook_name(),book.getIsSale(),book.getBook_id()};
int update = jdbcTemplate.update(sql,args);
System.out.println(update);
}
@Override
public void deleteBook(int id) {
//1、创建sql语句
String sql = "delete from t_book where book_id=?";
//2、调用方法
int update = jdbcTemplate.update(sql,id);
System.out.println(update);
}
@Override
public int selectCount() {
String sql ="select count(*) from t_book";
Integer count = jdbcTemplate.queryForObject(sql,Integer.class);
return count;
}
@Override
public Book selectOneBook(int id) {
String sql = "select * from t_book where book_id = ?";
Book book = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class),id);
return book;
}
@Override
public List<Book> selectAllBook() {
String sql = "select * from t_book";
List<Book> bookList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));
return bookList;
}
@Override
public void batchAdd(List<Object[]> batchArgs) {
String sql = "insert into t_book values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
System.out.println(Arrays.toString(ints));
}
@Override
public void batchUpdate(List<Object[]> batchArgs) {
String sql = "update t_book set book_name=?,isSale=? where book_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
System.out.println(Arrays.toString(ints));
}
@Override
public void batchDelete(List<Object[]> batchArgs) {
String sql = "delete from t_book where book_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
System.out.println(Arrays.toString(ints));
}
}
2.5 Book.java
package com.zyz.spring5.entity;
/**
* @author Lenovo
* @version 1.0
* @data 2022/10/23 16:10
*/
public class Book {
private int book_id;
private String book_name;
private String isSale;
public int getBook_id() {
return book_id;
}
public void setBook_id(int book_id) {
this.book_id = book_id;
}
public String getBook_name() {
return book_name;
}
public void setBook_name(String book_name) {
this.book_name = book_name;
}
public String getIsSale() {
return isSale;
}
public void setIsSale(String isSale) {
this.isSale = isSale;
}
@Override
public String toString() {
return "Book{" +
"book_id=" + book_id +
", book_name='" + book_name + '\'' +
", isSale='" + isSale + '\'' +
'}';
}
}
2.6 BookService.java
package com.zyz.spring5.service;
import com.zyz.spring5.dao.BookDao;
import com.zyz.spring5.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author Lenovo
* @version 1.0
* @data 2022/10/23 15:53
*/
@Service
public class BookService {
/**
* 注入dao
*/
@Autowired
private BookDao bookDao;
/**
* 添加书
* @param book
*/
public void addBook(Book book){
bookDao.addBook(book);
}
/**
* 修改书籍
* @param book
*/
public void updateBook(Book book){
bookDao.updateBook(book);
}
/**
* 删除书籍
* @param id
*/
public void deleteBook(int id){
bookDao.deleteBook(id);
}
/**
* 查询
* @return
*/
public int selectCount(){
return bookDao.selectCount();
}
/**
* 查询对象
* @return
*/
public Book selectBookOne(int id){
return bookDao.selectOneBook(id);
}
/**
* 查询全部对象
* @return
*/
public List<Book> selectBookAll(){
return bookDao.selectAllBook();
}
/**
* 批量添加
* @return
*/
public void batchAdd(List<Object[]> batchArgs){
bookDao.batchAdd(batchArgs);
}
/**
* 批量修改
* @return
*/
public void batchUpdate(List<Object[]> batchArgs){
bookDao.batchUpdate(batchArgs);
}
/**
* 批量删除
* @return
*/
public void batchDelete(List<Object[]> batchArgs){
bookDao.batchDelete(batchArgs);
}
}
2.7 TestDemo.java
package com.zyz.spring5.test;
import com.zyz.spring5.config.MyConfig;
import com.zyz.spring5.entity.Book;
import com.zyz.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
/**
* @author Lenovo
* @version 1.0
* @data 2022/10/23 16:26
*/
public class TestDemo {
@Test
public void testJdbcTemplate(){
ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class);
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setBook_id(8);
book.setBook_name("C语言");
book.setIsSale("出售");
bookService.addBook(book);
}
@Test
public void testJdbcTemplate1(){
ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class);
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setBook_id(1);
book.setBook_name("数据结构");
book.setIsSale("不出售");
bookService.updateBook(book);
}
@Test
public void testJdbcTemplate2(){
ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class);
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setBook_id(1);
bookService.deleteBook(book.getBook_id());
}
@Test
public void testJdbcTemplate3(){
ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class);
BookService bookService = context.getBean("bookService", BookService.class);
// System.out.println(bookService.selectCount());
// System.out.println(bookService.selectBookOne(1));
System.out.println(bookService.selectBookAll());
}
@Test
public void testJdbcTemplate4(){
ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class);
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 ={3,"C语言","出售"};
Object[] o2 ={4,"Python语言","出售"};
Object[] o3 ={5,"Php语言","出售"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchAdd(batchArgs);
}
@Test
public void testJdbcTemplate5(){
ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class);
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 ={"C语言001","出售",3};
Object[] o2 ={"Python语言001","出售",4};
Object[] o3 ={"Php语言001","出售",5};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchUpdate(batchArgs);
}
@Test
public void testJdbcTemplate6(){
ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class);
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 ={3};
Object[] o2 ={5};
batchArgs.add(o1);
batchArgs.add(o2);
bookService.batchDelete(batchArgs);
}
}
3、后语
学无止境。。。。。。
