4.1 JdbcTemplate
JdbcTemplate 是 Spring JDBC 核心包(core)中的核心类,它可以通过配置文件、注解、Java 配置类等形式获取数据库的相关信息,实现了对 JDBC 开发过程中的驱动加载、连接的开启和关闭、SQL 语句的创建与执行、异常处理、事务处理、数据类型转换等操作的封装。我们只要对其传入SQL 语句和必要的参数即可轻松进行 JDBC 编程。
JdbcTemplate 的全限定命名为 org.springframework.jdbc.core.JdbcTemplate,它提供了大量的查询和更新数据库的方法,如下表所示。
方法 | 说明 |
---|---|
public int update(String sql, Object ... args) | 用于执行新增、更新、删除等语句;sql:需要执行的 SQL 语句;args 表示需要传入到 SQL 语句中的参数。 |
public <T> T queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object... args) | 用于执行查询语句;sql:需要执行的 SQL 语句;rowMapper:用于确定返回的集合(List)的类型;args:表示需要传入到 SQL 语句的参数。 |
public <T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object... args) | 用于执行查询语句;sql:需要执行的 SQL 语句;rowMapper:用于确定返回的集合(List)的类型;args:表示需要传入到 SQL 语句的参数。 |
public int[] batchUpdate(String sql, List<Object[]> batchArgs, final int[] argTypes) | 用于批量执行新增、更新、删除等语句; sql:需要执行的 SQL 语句;argTypes:需要注入的 SQL 参数的 JDBC 类型;batchArgs:表示需要传入到 SQL 语句的参数。 |
4.1.1 实例
-
创建配置文件
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 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 "> <!-- 数据库连接池 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="url" value="jdbc:mysql://localhost:3306/user_db?useUnicode=true&characterEncoding=UTF-8" /> <property name="username" value="root" /> <property name="password" value="123456" /> <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" /> </bean> <!--开启组件扫描--> <context:component-scan base-package="top.shiyiri.spring5" /> </beans>
-
创建 JavaBean
package top.shiyiri.spring5.entity; public class Book { private String userId; private String username; private String ustatus; public Book(String userId, String username, String ustatus) { this.userId = userId; this.username = username; this.ustatus = ustatus; } public Book() { } public String getUserId() { return userId; } public void setUserId(String userId) { this.userId = userId; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getUstatus() { return ustatus; } public void setUstatus(String ustatus) { this.ustatus = ustatus; } @Override public String toString() { return "User{" + "userId='" + userId + '\'' + ", username='" + username + '\'' + ", ustatus='" + ustatus + '\'' + '}'; } }
-
创建持久层类
package top.shiyiri.spring5.dao; import top.shiyiri.spring5.entity.Book; import java.util.List; public interface BookDao { void add(Book book); void updateBook(Book book); void deleteBook(String id); int selectCount(); Book findBookInfo(String id); List<Book> findAllBook(); void batchAddBook(List<Object[]> batchArgs); void batchUpdateBook(List<Object[]> batchArgs); void batchDeleteBook(List<Object[]> batchArgs); }
package top.shiyiri.spring5.dao; 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 top.shiyiri.spring5.entity.Book; import java.util.Arrays; import java.util.List; /** * @author Aunean * @date 2022/2/18 14:26 */ @Repository public class BookDaoImpl implements BookDao { //注入JdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; @Override public void add(Book book) { //1.创建SQL语句 String sql = "insert into t_book values(?,?,?)"; int update = jdbcTemplate.update(sql, book.getUserId(), book.getUsername(), book.getUstatus()); System.out.println(update); } @Override public void updateBook(Book book) { //1.创建SQL语句 String sql = "update t_book set username=?, ustatus=? where user_id = ?"; int update = jdbcTemplate.update(sql, book.getUsername(), book.getUstatus(), book.getUserId()); System.out.println(update); } @Override public void deleteBook(String id) { //1.创建SQL语句 String sql = "delete from t_book where user_id = ?"; int update = jdbcTemplate.update(sql, id); System.out.println(update); } //查询表记录数 @Override public int selectCount() { String sql = "select count(*) from t_book"; return jdbcTemplate.queryForObject(sql, Integer.class); } @Override public Book findBookInfo(String id) { String sql = "select * from t_book where user_id = ?"; return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Book.class), id); } //返回对象集合 @Override public List<Book> findAllBook() { String sql = "select * from t_book"; return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class)); } @Override public void batchAddBook(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 batchUpdateBook(List<Object[]> batchArgs) { String sql = "update t_book set username=?, ustatus=? where user_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_book where user_id = ?"; int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs); System.out.println(Arrays.toString(ints)); } }
-
service层
package top.shiyiri.spring5.service; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import top.shiyiri.spring5.dao.BookDao; import top.shiyiri.spring5.entity.Book; import java.util.List; /** * @author Aunean * @date 2022/2/18 14:25 */ @Service public class BookService { @Autowired private BookDao bookDao; //添加的方法 public void add(Book book) { bookDao.add(book); } //修改的方法 public void update(Book book) { bookDao.updateBook(book); } //删除的方法 public void delete(String id) { bookDao.deleteBook(id); } //查询表记录数 public int findCount() { return bookDao.selectCount(); } //查询对象 public Book findOne(String id) { return bookDao.findBookInfo(id); } //查询对象集合 public List<Book> findList() { return bookDao.findAllBook(); } //批量添加 public void batchAdd(List<Object[]> batchArgs) { bookDao.batchAddBook(batchArgs); } //批量修改 public void batchUpdateBook(List<Object[]> batchArgs) { bookDao.batchUpdateBook(batchArgs); } //批量删除 public void batchDelete(List<Object[]> batchArgs) { bookDao.batchDeleteBook(batchArgs); } }
-
测试代码
package top.shiyiri.spring5.test; import org.junit.Test; import org.springframework.context.support.ClassPathXmlApplicationContext; import top.shiyiri.spring5.entity.Book; import top.shiyiri.spring5.service.BookService; import java.util.ArrayList; import java.util.List; public class TestBook { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); @Test public void testAdd() { bookService.add(new Book("2", "python", "a")); } @Test public void testUpdate() { bookService.update(new Book("1", "Hadoop", "a")); } @Test public void testDelete() { // bookService.delete("1"); int count = bookService.findCount(); System.out.println(count); } @Test public void testFindOne() { Book count = bookService.findOne("1"); System.out.println(count); } @Test public void testFindAll() { List<Book> list = bookService.findList(); // System.out.println(list); list.forEach(System.out::println); } @Test public void testBatch() { ArrayList<Object[]> batch = new ArrayList<>(); Object[] o1 = {"3", "java", "b"}; Object[] o2 = {"4", "scala", "c"}; Object[] o3 = {"5", "spring", "a"}; batch.add(o1); batch.add(o2); batch.add(o3); bookService.batchAdd(batch); } @Test public void testBatchUpdateBook() { ArrayList<Object[]> batch = new ArrayList<>(); Object[] o1 = {"java++", "b", "3"}; Object[] o2 = {"scala++", "c", "4",}; Object[] o3 = {"spring++", "a", "5",}; batch.add(o1); batch.add(o2); batch.add(o3); bookService.batchUpdateBook(batch); } @Test public void testBatchDeleteBook() { ArrayList<Object[]> batch = new ArrayList<>(); Object[] o1 = {"3"}; Object[] o2 = {"4"}; batch.add(o1); batch.add(o2); bookService.batchDelete(batch); } }