1.背景

1、什么是JdbcTemplate
（1）Spring框架对JDBC进行封装，使用JdbcTemplate方便实现对数据库操作

2.使用的环境准备

（1）引入相关jar包
spring之jdbctemplate - 图1
2.编写spring配置文件bean01.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3.        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.        xmlns:context="http://www.springframework.org/schema/context"
  5.        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">
  6.     <!--1.组件扫描-->
  7.     <context:component-scan base-package="com.ldp.jdbctemplate"></context:component-scan>
  8.     <!--2.数据库连接池-->
  9.     <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
  10.         <property name="url" value="jdbc:mysql://127.0.0.1:3306/ldp-data?characterEncoding=utf8"/>
  11.         <property name="username" value="root"/>
  12.         <property name="password" value="admin"/>
  13.         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  14.     </bean>
  15.     <!--3.JdbcTemplate对象-->
  16.     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  17.         <!--注入dataSource-->
  18.         <property name="dataSource" ref="dataSource"></property>
  19.     </bean>
  20. </beans>

3.model、dao接口、dao实现、dao测试结构搭建
spring之jdbctemplate - 图2
model
spring之jdbctemplate - 图3
dao接口

  1. package com.ldp.jdbctemplate.dao;
  3. import com.ldp.jdbctemplate.model.UserOrder;
  5. import java.util.List;
  7. /**
  8.  * @Copyright (C) XXXXXXXXXXX科技股份技有限公司
  9.  * @Author: lidongping
  10.  * @Date: 2021-01-15 9:43
  11.  * @Description:
  12.  */
  13. public interface IUserOrderDao {
  14.     /**
  15.      * @param userOrder
  16.      * @return
  17.      */
  18.     int save(UserOrder userOrder);
  20.     /**
  21.      * @param id
  22.      * @return
  23.      */
  24.     int delete(Integer id);
  26.     /**
  27.      * @param userOrder
  28.      * @return
  29.      */
  30.     int update(UserOrder userOrder);
  32.     /**
  33.      *
  34.      * @param id
  35.      * @return
  36.      */
  37.     UserOrder queryById(Integer id);
  39.     /**
  40.      *
  41.      * @return
  42.      */
  43.     List<UserOrder> queryList();
  45.     /**
  46.      * @return
  47.      */
  48.     int count();
  49. }

dao实现(结构而已,还没具体写内部实现,准备好,好使用spring的jdbcTemplate实现)

  1. package com.ldp.jdbctemplate.dao.impl;
  3. import com.ldp.jdbctemplate.dao.IUserOrderDao;
  4. import com.ldp.jdbctemplate.model.UserOrder;
  5. import org.springframework.stereotype.Repository;
  7. import java.util.List;
  9. /**
  10.  * @Copyright (C) XXXXXXXXXXX科技股份技有限公司
  11.  * @Author: lidongping
  12.  * @Date: 2021-01-15 9:46
  13.  * @Description:
  14.  */
  15. @Repository
  16. public class UserOrderDaoImpl implements IUserOrderDao {
  17.     @Override
  18.     public int save(UserOrder userOrder) {
  19.         return 0;
  20.     }
  22.     @Override
  23.     public int delete(Integer id) {
  24.         return 0;
  25.     }
  27.     @Override
  28.     public int update(UserOrder userOrder) {
  29.         return 0;
  30.     }
  32.     @Override
  33.     public UserOrder queryById(Integer id) {
  34.         return null;
  35.     }
  37.     @Override
  38.     public List<UserOrder> queryList() {
  39.         return null;
  40.     }
  42.     @Override
  43.     public int count() {
  44.         return 0;
  45.     }
  46. }

dao测试

  1. package com.ldp.jdbctemplate.dao.impl;
  4. import com.ldp.jdbctemplate.dao.IUserOrderDao;
  5. import com.ldp.jdbctemplate.model.UserOrder;
  6. import org.junit.jupiter.api.Test;
  7. import org.springframework.beans.factory.annotation.Autowired;
  8. import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;
  10. import java.util.List;
  12. /**
  13.  * @Copyright (C) XXXXXXXXXXX科技股份技有限公司
  14.  * @Author: lidongping
  15.  * @Date: 2021-01-15 9:47
  16.  * @Description:
  17.  */
  18. @SpringJUnitConfig(locations = "classpath:bean01.xml")
  19. public class UserOrderDaoImplTest {
  20.     @Autowired
  21.     private IUserOrderDao userOrderDao;
  23.     @Test
  24.     public void save() {
  25.         UserOrder userOrder = new UserOrder();
  26.         userOrder.setOrderNo("NO1001");
  27.         userOrder.setProductName("苹果");
  28.         int result = userOrderDao.save(userOrder);
  29.         System.out.println("受影响行数:" + result);
  30.     }
  32.     @Test
  33.     public void delete() {
  34.         int result = userOrderDao.delete(2);
  35.         System.out.println("受影响行数:" + result);
  36.     }
  38.     @Test
  39.     public void update() {
  40.         UserOrder userOrder = new UserOrder();
  41.         userOrder.setId(2);
  42.         userOrder.setProductName("苹果-修改");
  43.         int result = userOrderDao.save(userOrder);
  44.         System.out.println("受影响行数:" + result);
  45.     }
  47.     @Test
  48.     public void queryById() {
  49.         UserOrder userOrder = userOrderDao.queryById(2);
  50.         System.out.println("查询结果:" + userOrder);
  51.     }
  53.     @Test
  54.     public void queryList() {
  55.         List<UserOrder> userOrders = userOrderDao.queryList();
  56.         System.out.println("查询结果:" + userOrders);
  57.     }
  59.     @Test
  60.     public void count() {
  61.         int count = userOrderDao.count();
  62.         System.out.println("总共条数:" + count);
  63.     }
  64. }

3.jdbcTemplate实现增删改查

首先注入jdbcTemplate
spring之jdbctemplate - 图4
增删改查具体实现

  1. package com.ldp.jdbctemplate.dao.impl;
  3. import com.ldp.jdbctemplate.dao.IUserOrderDao;
  4. import com.ldp.jdbctemplate.model.UserOrder;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.jdbc.core.BeanPropertyRowMapper;
  7. import org.springframework.jdbc.core.JdbcTemplate;
  8. import org.springframework.stereotype.Repository;
  10. import java.util.List;
  12. /**
  13.  * @Copyright (C) XXXXXXXXXXX科技股份技有限公司
  14.  * @Author: lidongping
  15.  * @Date: 2021-01-15 9:46
  16.  * @Description:
  17.  */
  18. @Repository
  19. public class UserOrderDaoImpl implements IUserOrderDao {
  20.     @Autowired
  21.     private JdbcTemplate jdbcTemplate;
  23.     @Override
  24.     public int save(UserOrder userOrder) {
  25.         // 建议在mysql的可视化客户端(Navicat)写好sql后复制到这里
  26.         String sql = "INSERT INTO user_order (order_no,product_name) VALUES (?,?)";
  28.         return jdbcTemplate.update(sql, userOrder.getOrderNo(), userOrder.getProductName());
  29.     }
  31.     @Override
  32.     public int delete(Integer id) {
  33.         String sql = "DELETE FROM user_order WHERE id=?";
  35.         return jdbcTemplate.update(sql, id);
  36.     }
  38.     @Override
  39.     public int update(UserOrder userOrder) {
  40.         String sql = "UPDATE user_order SET product_name=? WHERE id=?";
  42.         return jdbcTemplate.update(sql, userOrder.getProductName(), userOrder.getId());
  43.     }
  45.     @Override
  46.     public UserOrder queryById(Integer id) {
  47.         String sql = "SELECT id,order_no orderNo,product_name productName FROM user_order WHERE id=?";
  48.         return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<UserOrder>(UserOrder.class), id);
  49.     }
  51.     @Override
  52.     public List<UserOrder> queryList() {
  53.         String sql = "SELECT id,order_no,product_name FROM user_order";
  54.         return jdbcTemplate.query(sql, new BeanPropertyRowMapper<UserOrder>(UserOrder.class));
  55.     }
  57.     @Override
  58.     public int count() {
  59.         String sql = "SELECT count(1) num FROM user_order";
  60.         return jdbcTemplate.queryForObject(sql, Integer.class);
  61.     }
  62. }

测试:执行上面的dao测试方法即可

4.jdbcTemplate批量操作

1.批量增加、批量删除、批量修改代码实现

  1. @Override
  2.     public int batchSave(List<UserOrder> userOrders) {
  3.         // 建议在mysql的可视化客户端(Navicat)写好sql后复制到这里
  4.         String sql = "INSERT INTO user_order (order_no,product_name) VALUES (?,?)";
  5.         // 批量添加测试
  6.         List<Object[]> batchArgs = new ArrayList<>();
  7.         for (UserOrder userOrder : userOrders) {
  8.             Object[] o = {userOrder.getOrderNo(), userOrder.getProductName()};
  9.             batchArgs.add(o);
  10.         }
  11.         int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
  13.         return ints.length;
  14.     }
  16.     @Override
  17.     public int batchDelete(List<Integer> ids) {
  18.         String sql = "DELETE FROM user_order WHERE id=?";
  19.         List<Object[]> batchArgs = new ArrayList<>();
  20.         for (Integer id : ids) {
  21.             Object[] object = {id};
  22.             batchArgs.add(object);
  23.         }
  24.         int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
  25.         return ints.length;
  26.     }
  28.     @Override
  29.     public int batchUpdate(List<UserOrder> userOrders) {
  30.         // 建议在mysql的可视化客户端(Navicat)写好sql后复制到这里
  31.         String sql = "UPDATE user_order SET product_name=? WHERE id=?";
  33.         List<Object[]> batchArgs = new ArrayList<>();
  34.         for (UserOrder userOrder : userOrders) {
  35.             Object[] o = {userOrder.getProductName(), userOrder.getId()};
  36.             batchArgs.add(o);
  37.         }
  38.         int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
  40.         return ints.length;
  41.     }

2.批量增加、批量删除、批量修改代码测试

  1. @Test
  2.     public void batchSave() {
  3.         List<UserOrder> list = new ArrayList<>();
  4.         for (int i = 0; i < 5; i++) {
  5.             UserOrder userOrder = new UserOrder();
  6.             userOrder.setOrderNo("NO1001-" + i);
  7.             userOrder.setProductName("产品名称-" + i);
  8.             list.add(userOrder);
  9.         }
  10.         int count = userOrderDao.batchSave(list);
  11.         System.out.println("受影响行数:" + count);
  12.     }
  14.     @Test
  15.     public void batchDelete() {
  16.         List<Integer> list = new ArrayList<>();
  17.         list.add(4);
  18.         list.add(5);
  19.         list.add(6);
  20.         int count = userOrderDao.batchDelete(list);
  21.         System.out.println("受影响行数:" + count);
  22.     }
  24.     @Test
  25.     public void batchUpdate() {
  26.         List<UserOrder> list = new ArrayList<>();
  27.         for (int i = 8; i <= 12; i++) {
  28.             UserOrder userOrder = new UserOrder();
  29.             userOrder.setId(i);
  30.             userOrder.setProductName("修改-产品名称-" + i);
  31.             list.add(userOrder);
  32.         }
  33.         int count = userOrderDao.batchUpdate(list);
  34.         System.out.println("受影响行数:" + count);
  35.     }

完美！