JdbcTemplate
Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作
准备工作
1、引入相关jar包
druid-1.2.9.jar mysql-connector-java-8.0.28.jar spring-jdbc-5.3.18.jar spring-orm-5.3.18.jar spring-tx-5.3.18.jar
2、在spring配置文件配置数据库连接池
<!--引入外部属性文件，需要配置context名称空间--> <context:property-placeholder location="classpath:druid.properties"/> <!--配置连接池--> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="driverClassName" value="${jdbc.driverClass}"></property> <property name="url" value="${jdbc.url}"></property> <property name="username" value="${jdbc.username}"></property> <property name="password" value="${jdbc.password}"></property> </bean>
druid.properties
jdbc.driverClass=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/jwt jdbc.username=root jdbc.password=root
3、配置JdbcTemplate对象，注入DataSource
<!-- JdbcTemplate对象 --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!--注入dataSource--> <property name="dataSource" ref="dataSource"/> </bean>
4、创建service类，创建dao类
- 在dao中注入jdbcTemplate对象
- 在service中注入dao对象
<!-- 组件扫描 --> <context:component-scan base-package="com.jwt"></context:component-scan>
@Service public class BookService { //注入dao @Autowired private BookDao bookDao; }
public interface BookDao { }
@Repository public class BookDaoImpl implements BookDao { //注入JdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; }
实现添加操作
1、先创建一个表
CREATE TABLE `book`( id INT, name VARCHAR (255), status VARCHAR (255) )CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB ;
2、创建表对应实体类
public class Book { private String bookId; private String bookName; private String bookStatus; public String getBookId() { return bookId; } public void setBookId(String bookId) { this.bookId = bookId; } public String getBookName() { return bookName; } public void setBookName(String bookName) { this.bookName = bookName; } public String getBookStatus() { return bookStatus; } public void setBookStatus(String bookStatus) { this.bookStatus = bookStatus; } }
3、编写service和dao
- 1.在dao中增加数据库添加方法
- 2.在dao的实现类中实现该方法
- 调用JdbcTemplate对象里面update(String sql, Object… args)方法实现添加操作
- 第一个参数：sql语句
- 第二个参数：可变参数，设置sql语句值
- 3.在service中调用dao的添加方法
public interface BookDao { void add(Book book);//添加的方法 }
@Repository public class BookDaoImpl implements BookDao { //注入JdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; @Override public void add(Book book) { // 创建sql语句 String sql = "INSERT INTO `book` VALUES (?, ?, ?)"; // 调用方法实现 int update = jdbcTemplate.update(sql, book.getBookId(), book.getBookName(), book.getBookStatus()); System.out.println(update); } }
@Service public class BookService { //注入dao @Autowired private BookDao bookDao; public void add(Book book) { bookDao.add(book); } }
4、测试
public class testJDBC { @Test public void testadd() { ApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); Book book = new Book(); book.setBookId("1"); book.setBookName("java"); book.setBookStatus("a"); bookService.add(book); } }
运行后表中就会成功增加一条数据
实现修改操作
详细请参考添加操作
@Override public void update(Book book) { String sql = "update book set name=?,status=? where id=?"; Object[] args = {book.getBookName(), book.getBookStatus(),book.getBookId()}; int update = jdbcTemplate.update(sql, args); System.out.println(update); }
测试
@Test public void testupdate() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); Book book = new Book(); book.setBookName("C++"); book.setBookStatus("b"); book.setBookId("1"); bookService.update(book); }
实现添加操作
@Override public void delete(String id) { String sql = "delete from book where id=?"; int update = jdbcTemplate.update(sql,id); System.out.println(update); }
测试
@Test public void testdelete() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); bookService.delete("1"); }
实现查询操作
查询返回某个值
- 查询表里面有多少条记录，返回是某个值
- queryForObject(String sql, Class<T> requiredType)
- 第一个参数：sql语句
- 第二个参数：返回类型Class
@Override public int select() { String sql = "select count(*) from book"; Integer count = jdbcTemplate.queryForObject(sql, Integer.class); return count; }
测试
@Test public void testselect() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); int select = bookService.select(); System.out.println("select = " + select); }
查询返回对象
- queryForObject(String sql, RowMapper<T> rowMapper, 0bject… args)
- 第一个参数：sql语句
- 第二个参数：RowMapper 是接口，针对返回不同类型数据，使用这个接口里面 实现类完成数据封装
- 第三个参数：sql 语句值
//查询返回对象 @Override public Book selectObj(String id) { String sql = "select * from book where `id`=?"; Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id); return book; }
测试
@Test public void testselectObj() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); Book book = bookService.selectObj("1"); System.out.println("book = " + book); }
数据库中表的字段名要和实体类Book中的字段名字一样，不然会返回null
查询返回集合
- query(String sql, RowMapper<T> rowMapper, 0bject… args)
- 第一个参数: sql语句
- 第二个参数: RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装
- 第三个参数: sql 语句值
@Override public List<Book> selectCollection() { String sql = "select * from book"; //调用方法 List<Book> bookList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class)); return bookList; }
测试
@Test public void testselectCollection() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Book> books = bookService.selectCollection(); System.out.println("books = " + books); }
实现批量操作
批量添加
- batchUpdate方法
- 第一个参数：sql语句
- 第二个参数：List集合，添加多条记录数据
@Override public void batchAdd(List<Object[]> batchArgs) { String sql = "insert into book values(?,?,?)"; int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs); System.out.println(Arrays.toString(ints)); }
测试
@Test public void testbatchAdd() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Object[]> batchArgs = new ArrayList<>(); Object[] o1 = {"3","java","a"}; Object[] o2 = {"4","c++","b"}; Object[] o3 = {"5","MySQL","c"}; batchArgs.add(o1); batchArgs.add(o2); batchArgs.add(o3); //调用批量添加 bookService.batchAdd(batchArgs); }
批量修改
@Override public void batchUpdate(List<Object[]> batchArgs) { String sql = "update book set name=?,status=? where id=?"; int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs); System.out.println(Arrays.toString(ints)); }
测试
@Test public void testbatchUpdate() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Object[]> batchArgs = new ArrayList<>(); Object[] o1 = {"java2","a3","3"}; Object[] o2 = {"c++2","b4","4"}; Object[] o3 = {"MySQL2","c5","5"}; batchArgs.add(o1); batchArgs.add(o2); batchArgs.add(o3); bookService.batchUpdate(batchArgs); }
批量删除
@Override public void batchDelete(List<Object[]> batchArgs) { String sql = "delete from book where id=?"; int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs); System.out.println(Arrays.toString(ints)); }
测试
@Test public void testbatchDelete() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Object[]> batchArgs = new ArrayList<>(); Object[] o1 = {"3"}; Object[] o2 = {"4"}; batchArgs.add(o1); batchArgs.add(o2); bookService.batchDelete(batchArgs); }
