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 {/***     创建数据库连接池*/@Beanpublic 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 对象*/@Beanpublic JdbcTemplate getJdbcTemplate(DataSource dataSource) {//到 ioc 容器中根据类型找到 dataSourceJdbcTemplate jdbcTemplate = new JdbcTemplate();//注入 dataSourcejdbcTemplate.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 {/***     创建数据库连接池*/@Beanpublic 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 对象*/@Beanpublic JdbcTemplate getJdbcTemplate(DataSource dataSource) {//到 ioc 容器中根据类型找到 dataSourceJdbcTemplate jdbcTemplate = new JdbcTemplate();//注入 dataSourcejdbcTemplate.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 selectAllBook();/*** 批量添加* @param batchArgs* @return*/void batchAdd(List batchArgs);/*** 批量修改* @param batchArgs* @return*/void batchUpdate(List batchArgs);/*** 批量删除* @param batchArgs* @return*/void batchDelete(List 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*/@Autowiredprivate JdbcTemplate jdbcTemplate;@Overridepublic 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);}@Overridepublic 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);}@Overridepublic 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);}@Overridepublic int selectCount() {String sql ="select count(*) from t_book";Integer count = jdbcTemplate.queryForObject(sql,Integer.class);return count;}@Overridepublic Book selectOneBook(int id) {String sql = "select * from t_book where book_id = ?";Book book = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper(Book.class),id);return book;}@Overridepublic List selectAllBook() {String sql = "select * from t_book";List bookList = jdbcTemplate.query(sql,new BeanPropertyRowMapper(Book.class));return bookList;}@Overridepublic void batchAdd(List batchArgs) {String sql = "insert into t_book values(?,?,?)";int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);System.out.println(Arrays.toString(ints));}@Overridepublic void batchUpdate(List 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));}@Overridepublic void batchDelete(List 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;}@Overridepublic 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*/@Autowiredprivate 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 selectBookAll(){return bookDao.selectAllBook();}/*** 批量添加* @return*/public void batchAdd(List batchArgs){bookDao.batchAdd(batchArgs);}/*** 批量修改* @return*/public void batchUpdate(List batchArgs){bookDao.batchUpdate(batchArgs);}/*** 批量删除* @return*/public void batchDelete(List 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 {@Testpublic 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);}@Testpublic 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);}@Testpublic 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());}@Testpublic 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());}@Testpublic void testJdbcTemplate4(){ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class);BookService bookService = context.getBean("bookService", BookService.class);List 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);}@Testpublic void testJdbcTemplate5(){ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class);BookService bookService = context.getBean("bookService", BookService.class);List 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);}@Testpublic void testJdbcTemplate6(){ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class);BookService bookService = context.getBean("bookService", BookService.class);List batchArgs = new ArrayList<>();Object[] o1 ={3};Object[] o2 ={5};batchArgs.add(o1);batchArgs.add(o2);bookService.batchDelete(batchArgs);}
}

3、后语

学无止境。。。。。。