什么是JdbcTemplate?
Spring 框架对 JDBC
进行封装,使用 JdbcTemplate
方便实现对数据库操作。
准备工作
引入jdbcTemplate的相关依赖:

案例实操
- 创建
jdbc.properties
文件,配置数据库信息
| jdbc.driver=com.mysql.cj.jdbc.Driver |
| jdbc.url=jdbc:mysql://localhost:3306/dbtest1?serverTimezone=UTC |
| jdbc.username=root |
| jdbc.password=123456 |
- 创建
application.xml
配置文件,配置相关信息
| |
| <context:component-scan base-package="com.evan.spring5"/> |
| |
| |
| <context:property-placeholder location="classpath:jdbc.properties"/> |
| |
| |
| <bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource"> |
| <property name="driverClassName" value="${jdbc.driver}"/> |
| <property name="url" value="${jdbc.url}"/> |
| <property name="username" value="${jdbc.username}"/> |
| <property name="password" value="${jdbc.password}"/> |
| </bean> |
| |
| |
| <bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate"> |
| |
| <property name="dataSource" ref="dataSource"/> |
| </bean> |
- 创建数据表
| CREATE TABLE `dept` ( |
| `id` int NOT NULL, |
| `dept_name` varchar(15) DEFAULT NULL, |
| PRIMARY KEY (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
- 创建实体类
| public class Dept { |
| |
| private long id; |
| private String deptName; |
| |
| public long getId() { |
| return id; |
| } |
| |
| public void setId(long id) { |
| this.id = id; |
| } |
| |
| public String getDeptName() { |
| return deptName; |
| } |
| |
| public void setDeptName(String deptName) { |
| this.deptName = deptName; |
| } |
| |
| @Override |
| public String toString() { |
| return "Dept{" + |
| "id=" + id + |
| ", deptName='" + deptName + '\'' + |
| '}'; |
| } |
| } |
- 创建dao接口、dao实现类和service类,并将其加入到spring容器中
① DeptDao接口
| public interface DeptDao { |
| |
| int add(Dept dept); |
| |
| int update(Dept dept); |
| |
| int delete(Dept dept); |
| |
| int queryCount(); |
| |
| Dept queryById(Integer id); |
| |
| List<Dept> queryDeptList(); |
| |
| int batchAdd(List<Object[]> list); |
| |
| void batchUpdate(List<Object[]> list); |
| |
| void batchDelete(List<Object[]> list); |
| } |
② 创建DeptDaoImpl实现类
| @Repository |
| public class DeptDaoImpl implements DeptDao { |
| |
| @Autowired |
| private JdbcTemplate jdbcTemplate; |
| |
| @Override |
| public int add(Dept dept) { |
| String sql = "insert into dept(id,dept_name) values(?,?)"; |
| Object[] args = {dept.getId(),dept.getDeptName()}; |
| return jdbcTemplate.update(sql,args); |
| } |
| |
| @Override |
| public int update(Dept dept) { |
| String sql = "update dept set dept_name=? where id=?"; |
| Object[] args = {dept.getDeptName(),dept.getId()}; |
| return jdbcTemplate.update(sql,args); |
| } |
| |
| @Override |
| public int delete(Dept dept) { |
| String sql = "delete from dept where id=?"; |
| return jdbcTemplate.update(sql,dept.getId()); |
| } |
| |
| @Override |
| public int queryCount() { |
| String sql = "select count(1) from dept"; |
| return jdbcTemplate.queryForObject(sql,Integer.class); |
| } |
| |
| @Override |
| public Dept queryById(Integer id) { |
| String sql = "select * from dept where id=?"; |
| BeanPropertyRowMapper<Dept> dpr = new BeanPropertyRowMapper<>(Dept.class); |
| return jdbcTemplate.queryForObject(sql,dpr,id); |
| } |
| |
| @Override |
| public List<Dept> queryDeptList() { |
| String sql = "select * from dept"; |
| BeanPropertyRowMapper<Dept> dbp = new BeanPropertyRowMapper<>(Dept.class); |
| return jdbcTemplate.query(sql,dbp); |
| } |
| |
| @Override |
| public int batchAdd(List<Object[]> list) { |
| String sql = "insert into dept value(?,?)"; |
| int[] ints = jdbcTemplate.batchUpdate(sql, list); |
| return ints.length; |
| } |
| |
| @Override |
| public void batchUpdate(List<Object[]> list) { |
| String sql = "update dept set dept_name=? where id=?"; |
| int[] ints = jdbcTemplate.batchUpdate(sql, list); |
| System.out.println(Arrays.toString(ints)); |
| } |
| |
| @Override |
| public void batchDelete(List<Object[]> list) { |
| String sql = "delete from t_book where id=?"; |
| int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs); |
| System.out.println(Arrays.toString(ints)); |
| } |
| } |
③ 创建DeptService类
| @Service |
| public class DeptService { |
| |
| @Autowired |
| private DeptDao deptDao; |
| |
| public int add(Dept dept) { |
| if (dept != null) { |
| return deptDao.add(dept); |
| } |
| throw new RuntimeException("添加数据失败"); |
| } |
| |
| |
| public int update(Dept dept) { |
| return deptDao.update(dept); |
| } |
| |
| |
| public int delete(Dept dept) { |
| return deptDao.delete(dept); |
| } |
| |
| public int queryCount() { |
| return deptDao.queryCount(); |
| } |
| |
| public Dept queryById(Integer id) { |
| return deptDao.queryById(id); |
| } |
| |
| public List<Dept> queryDeptList() { |
| return deptDao.queryDeptList(); |
| } |
| |
| public int batchAdd(List<Object[]> list) { |
| return deptDao.batchAdd(list); |
| } |
| |
| public void batchUpdate(List<Object[]> list) { |
| deptDao.batchUpdate(list); |
| } |
| |
| public void batchDelete(List<Object[]> list) { |
| deptDao.batchDelete(list); |
| } |
| } |
测试
| @Test |
| public void test1() { |
| ApplicationContext context = |
| new ClassPathXmlApplicationContext("application.xml"); |
| DeptService deptService = context.getBean("deptService", DeptService.class); |
| Dept dept = new Dept(); |
| dept.setId(1); |
| dept.setDeptName("开发部"); |
| |
| |
| |
| |
| |
| deptService.delete(dept); |
| } |
| |
| @Test |
| public void test2() { |
| ApplicationContext context = new ClassPathXmlApplicationContext("application.xml"); |
| DeptService deptService = context.getBean("deptService", DeptService.class); |
| |
| int count = deptService.queryCount(); |
| System.out.println(count); |
| |
| |
| Dept dept = deptService.queryById(1001); |
| System.out.println(dept); |
| |
| |
| System.out.println(Arrays.toString(deptService.queryDeptList().toArray())); |
| ) |
| |
| |
| @Test |
| public void test3() { |
| ApplicationContext context = |
| new ClassPathXmlApplicationContext("application.xml"); |
| DeptService deptService = context.getBean("deptService", DeptService.class); |
| List<Object[]> lists = new ArrayList<>(); |
| Object o1[] = {12,"B1"}; |
| Object o2[] = {13,"B2"}; |
| Object o3[] = {14,"B3"}; |
| lists.add(o1); |
| lists.add(o2); |
| lists.add(o3); |
| int result = deptService.batchAdd(lists); |
| System.out.println(result); |
| } |
| |
| |
| @Test |
| public void test3() { |
| ApplicationContext context = |
| new ClassPathXmlApplicationContext("application.xml"); |
| DeptService deptService = context.getBean("deptService", DeptService.class); |
| List<Object[]> batchArgs = new ArrayList<>(); |
| Object[] o1 = {"java0909",1001}; |
| Object[] o2 = {"c++1010",1002}; |
| Object[] o3 = {"MySQL1111",1003}; |
| batchArgs.add(o1); |
| batchArgs.add(o2); |
| batchArgs.add(o3); |
| |
| deptService.batchUpdate(batchArgs); |
| } |
| |
| |
| @Test |
| public void test3() { |
| ApplicationContext context = |
| new ClassPathXmlApplicationContext("application.xml"); |
| DeptService deptService = context.getBean("deptService", DeptService.class); |
| List<Object[]> batchArgs = new ArrayList<>() |
| Object[] o1 = {1001}; |
| Object[] o2 = {1002}; |
| batchArgs.add(o1); |
| batchArgs.add(o2); |
| |
| deptService.batchDelete(batchArgs); |
| } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)