1.操作语法
初始数据库表t_book
①查询多条记录, 返回集合
@Override
public List<Book> findAll(){
String sql = "select * from t_book";
//查找一条记录用queryForObject(), 但是查找多条记录用的是query
List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class));//<>里的参数可以省略
return bookList;
}
执行结果
System.out.println(bookDao.findAll());
②批量添加多条记录
注意:批量添加、批量修改和批量删除都是用batchUppdate方法
@Override
public int[] batchAdd(List<Object[]> bookList) {
String sql = "insert into t_book values(?, ?, ?)";
int[] res = jdbcTemplate.batchUpdate(sql, bookList);
return res;
}
执行结果
Object[] book1 = {4, "html", 2.55};
Object[] book2 = {5, "css", 6.25};
Object[] book3 = {6, "javascript", 8.11};
List<Object[]> bookList = new LinkedList<>();
bookList.add(book1);
bookList.add(book2);
bookList.add(book3);
System.out.println(bookDao.batchAdd(bookList));
③批量修改多条记录
@Override
public int[] batchUpdateById(List<Object[]> bookList) {
String sql = "update t_book set name = ?, price = ? where id = ?";
int[] res = jdbcTemplate.batchUpdate(sql, bookList);
return res;
}
执行结果
Object[] book1 = {"html_batch_update", 6.66, 4}; //注意参数顺序
Object[] book2 = {"css_batch_update", 6.66, 5};
Object[] book3 = {"javascript_batch_update", 6.66, 6};
List<Object[]> bookList = new LinkedList<>();
bookList.add(book1);
bookList.add(book2);
bookList.add(book3);
System.out.println(bookDao.batchUpdateById(bookList));
④批量删除多条记录
@Override
public int[] batchDeleteById(List<Object[]> bookList) {
String sql = "delete from t_book where id = ?";
//就算只有一个id参数, 也不能传入{4, 5, 6}这样的数字数组, 只能传入List<Object[]>类型
int[] res = jdbcTemplate.batchUpdate(sql, bookList);
return res;
}
执行结果
Object[] book1 = {4};
Object[] book2 = {5};
Object[] book3 = {6};
List<Object[]> bookList = new LinkedList<>();
bookList.add(book1);
bookList.add(book2);
bookList.add(book3);
System.out.println(bookDao.batchDeleteById(bookList));
2.完整代码
配置文件bean1.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 https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd">
<!--开启组件扫描-->
<context:component-scan base-package="com.limi"></context:component-scan>
<!-- 1.在 spring 配置文件配置数据库连接池-->
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/db_springtest" />
<property name="username" value="root" />
<property name="password" value="123456" />
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
</bean>
<!--2.配置 JdbcTemplate 对象,注入 DataSource-->
<!-- JdbcTemplate 对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入 dataSource-->
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
Book
package com.limi.entity;
public class Book {
private Integer id;
String name;
Double price;
public Book(){}
public Book(Integer id, String name, Double price) {
this.id = id;
this.name = name;
this.price = price;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
@Override
public String toString() {
return "Book{" +
"id=" + id +
", name='" + name + '\'' +
", price=" + price +
'}';
}
}
BookDao
package com.limi.dao;
import com.limi.entity.Book;
import java.util.List;
public interface BookDao {
//查找所有书籍
List<Book> findAll();
//批量添加书籍
int[] batchAdd(List<Object[]> bookList);
//批量修改书籍
int[] batchUpdateById(List<Object[]> bookList);
//批量删除书籍
int[] batchDeleteById(List<Object[]> bookList);
}
BookDaoImpl
package com.limi.dao;
import com.limi.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.List;
@Repository
public class BookDaoImpl implements BookDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public List<Book> findAll(){
String sql = "select * from t_book";
//查找一条记录用queryForObject(), 但是查找多条记录用的是query
List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class));//<>里的参数可以省略
return bookList;
}
@Override
public int[] batchAdd(List<Object[]> bookList) {
String sql = "insert into t_book values(?, ?, ?)";
int[] res = jdbcTemplate.batchUpdate(sql, bookList);
return res;
}
@Override
public int[] batchUpdateById(List<Object[]> bookList) {
String sql = "update t_book set name = ?, price = ? where id = ?";
int[] res = jdbcTemplate.batchUpdate(sql, bookList);
return res;
}
@Override
public int[] batchDeleteById(List<Object[]> bookList) {
String sql = "delete from t_book where id = ?";
//就算只有一个id参数, 也不能传入{4, 5, 6}这样的数字数组, 只能传入List<Object[]>类型
int[] res = jdbcTemplate.batchUpdate(sql, bookList);
return res;
}
}
测试类MyTest
package com.limi.test;
import com.limi.dao.BookDao;
import com.limi.dao.BookDaoImpl;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class MyTest {
@Test
public void test1(){
//1.加载bean的xml文件, 以src为根目录
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
//2.获取配置的对象, 参数1:bean的id值, 参数2: 类名.class
BookDao bookDao = context.getBean("bookDaoImpl", BookDaoImpl.class);
//3.使用对象
System.out.println(bookDao.findAll());
}
}
数据库表t_book的结构