Spring5入门到实战------12、使用JdbcTemplate操作数据库（增删改查）。具体代码+讲解 【上篇】
这一篇主要介绍批量操作数据库
1、jdbcTemplate 实现批量添加操作
有两个参数
第一个参数：sql 语句
第二个参数：List 集合，添加多条记录数据
核心
@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));
}
测试
@Test
public void testJdbcTemplate4(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
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);
}
测试结果
2、JdbcTemplate 实现批量修改操作
核心
@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));
}
测试
@Test
public void testJdbcTemplate5(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
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);
}
测试结果
3、JdbcTemplate 实现批量删除操作
核心部分
@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));
}
测试
@Test
public void testJdbcTemplate6(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
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);
}
测试结果
4、完整的项目案例
4.1 目录结构
4.2 BookDao.java
/**
* @author Lenovo
* @version 1.0
* @data 2022/10/23 15:53
*/
public interface BookDao {
/**
* 批量添加
* @param batchArgs
* @return
*/
void batchAdd(List<Object[]> batchArgs);
/**
* 批量修改
* @param batchArgs
* @return
*/
void batchUpdate(List<Object[]> batchArgs);
/**
* 批量删除
* @param batchArgs
* @return
*/
void batchDelete(List<Object[]> batchArgs);
}
4.3 BookDaoImpl.java
/**
* @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 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));
}
}
4.4 BookService.java
/**
* @author Lenovo
* @version 1.0
* @data 2022/10/23 15:53
*/
@Service
public class BookService {
/**
* 注入dao
*/
@Autowired
private BookDao bookDao;
/**
* 批量添加
* @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);
}
}
4.5 bean.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"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
<!--开启组件扫描-->
<context:component-scan base-package="com.zyz"></context:component-scan>
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/user_db" />
<property name="username" value="root" />
<property name="password" value="root" />
<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>
</beans>
4.6 TestDemo.java
/**
* @author Lenovo
* @version 1.0
* @data 2022/10/23 16:26
*/
public class TestDemo {
@Test
public void testJdbcTemplate4(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
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 ClassPathXmlApplicationContext("bean.xml");
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 ClassPathXmlApplicationContext("bean.xml");
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);
}
}
4.7 Book.java
可以使用lombok插件、简化get/set
/**
* @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 + '\'' +
'}';
}
}
5、后语
主要用的时候、要知道使用哪个方法就行。学无止境。。。。。。
