Spring基础（十二）：JDBCTemplate的使用
【摘要】
文章目录
JDBCTemplate的使用
一、JdbcTemplate概述
二、导入依赖
&nbs...
文章目录
JDBCTemplate的使用
一、JdbcTemplate概述
JdbcTemplate是spring框架中提供的一个对象，是对原始繁琐的Jdbc API对象的简单封装。spring框架为我们提供了很多的操作模板类。例如：操作关系型数据的JdbcTemplate和，操作nosql数据库的RedisTemplate，操作消息队列的JmsTemplate等等。
按如下项目结构准备 maven jar项目即可
二、导入依赖
-
<dependencies>
-
<!--spring核心容器包-->
-
<dependency>
-
<groupId>org.springframework</groupId>
-
<artifactId>spring-context</artifactId>
-
<version>5.3.5</version>
-
</dependency>
-
<!--spring切面包-->
-
<dependency>
-
<groupId>org.springframework</groupId>
-
<artifactId>spring-aspects</artifactId>
-
<version>5.3.5</version>
-
</dependency>
-
<!--aop联盟包-->
-
<dependency>
-
<groupId>aopalliance</groupId>
-
<artifactId>aopalliance</artifactId>
-
<version>1.0</version>
-
</dependency>
-
<!--德鲁伊连接池-->
-
<dependency>
-
<groupId>com.alibaba</groupId>
-
<artifactId>druid</artifactId>
-
<version>1.1.10</version>
-
</dependency>
-
<!--mysql驱动-->
-
<dependency>
-
<groupId>mysql</groupId>
-
<artifactId>mysql-connector-java</artifactId>
-
<version>8.0.22</version>
-
</dependency>
-
<!--springJDBC包-->
-
<dependency>
-
<groupId>org.springframework</groupId>
-
<artifactId>spring-jdbc</artifactId>
-
<version>5.3.5</version>
-
</dependency>
-
<!--spring事务控制包-->
-
<dependency>
-
<groupId>org.springframework</groupId>
-
<artifactId>spring-tx</artifactId>
-
<version>5.3.5</version>
-
</dependency>
-
<!--spring orm 映射依赖-->
-
<dependency>
-
<groupId>org.springframework</groupId>
-
<artifactId>spring-orm</artifactId>
-
<version>5.3.5</version>
-
</dependency>
-
<!--Apache Commons日志包-->
-
<dependency>
-
<groupId>commons-logging</groupId>
-
<artifactId>commons-logging</artifactId>
-
<version>1.2</version>
-
</dependency>
-
<!--Junit单元测试-->
-
<dependency>
-
<groupId>junit</groupId>
-
<artifactId>junit</artifactId>
-
<version>4.13.1</version>
-
<scope>test</scope>
-
</dependency>
-
<!--lombok -->
-
<dependency>
-
<groupId>org.projectlombok</groupId>
-
<artifactId>lombok</artifactId>
-
<version>1.18.12</version>
-
<scope>provided</scope>
-
</dependency>
-
</dependencies>
三、准备JDBC.properties
-
jdbc_username=root
-
jdbc_password=root
-
jdbc_driver=com.mysql.cj.jdbc.Driver
-
jdbc_url=jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
四、准备applicationContext.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:p="http://www.springframework.org/schema/p"
-
xmlns:c="http://www.springframework.org/schema/c"
-
xmlns:util="http://www.springframework.org/schema/util"
-
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/util
-
http://www.springframework.org/schema/util/spring-util.xsd
-
http://www.springframework.org/schema/context
-
http://www.springframework.org/schema/context/spring-context.xsd
-
http://www.springframework.org/schema/aop
-
http://www.springframework.org/schema/aop/spring-aop.xsd
-
">
-
<!--spring 注解扫描-->
-
<context:component-scan base-package="com.lanson"/>
-
<!--读取jdbc配置文件-->
-
<context:property-placeholder location="classpath:jdbc.properties"/>
-
<!--配置德鲁伊连接池-->
-
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
-
<property name="username" value="${jdbc_username}"></property>
-
<property name="password" value="${jdbc_password}"></property>
-
<property name="url" value="${jdbc_url}"></property>
-
<property name="driverClassName" value="${jdbc_driver}"></property>
-
</bean>
-
<!--配置JDBCTemplate对象,并向里面注入DataSource-->
-
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
-
<!--通过set方法注入连接池-->
-
<property name="dataSource" ref="dataSource"></property>
-
</bean>
-
</beans>
五、准备实体类
-
package com.lanson.pojo;
-
import lombok.AllArgsConstructor;
-
import lombok.Data;
-
import lombok.NoArgsConstructor;
-
import java.util.Date;
-
/**
-
* @Author: Lansonli
-
* @Description: MircoMessage:Mark_7001
-
*/
-
@AllArgsConstructor
-
@NoArgsConstructor
-
@Data
-
public class Emp implements Serializable{
-
private Integer empno;
-
private String ename;
-
private String job;
-
private Integer mgr;
-
private Date hiredate;
-
private Double sal;
-
private Double comm;
-
private Integer deptno;
-
}
六、准备service层接口和实现类
-
package com.lanson.service;
-
import com.lanson.pojo.Emp;
-
import java.util.List;
-
/**
-
* @Author: Lansonli
-
* @Description: MircoMessage:Mark_7001
-
*/
-
public interface EmpService {
-
int findEmpCount();
-
Emp findByEmpno(int empno);
-
List<Emp> findByDeptno(int deptno);
-
int addEmp(Emp emp);
-
int updateEmp(Emp emp);
-
int deleteEmp( int empno);
-
}
-
-
package com.lanson.service.impl;
-
import com.lanson.dao.EmpDao;
-
import com.lanson.pojo.Emp;
-
import com.lanson.service.EmpService;
-
import org.springframework.beans.factory.annotation.Autowired;
-
import org.springframework.stereotype.Service;
-
import java.util.List;
-
/**
-
* @Author: Lansonli
-
* @Description: MircoMessage:Mark_7001
-
*/
-
@Service
-
public class EmpServiceImpl implements EmpService {
-
@Autowired
-
private EmpDao empDao;
-
@Override
-
public int findEmpCount() {
-
return empDao.findEmpCount();
-
}
-
@Override
-
public Emp findByEmpno(int empno) {
-
return empDao.findByEmpno( empno);
-
}
-
@Override
-
public List<Emp> findByDeptno(int deptno) {
-
return empDao.findByDeptno( deptno);
-
}
-
@Override
-
public int addEmp(Emp emp) {
-
return empDao.addEmp(emp);
-
}
-
@Override
-
public int updateEmp(Emp emp) {
-
return empDao.updateEmp(emp);
-
}
-
@Override
-
public int deleteEmp(int empno) {
-
return empDao.deleteEmp(empno);
-
}
-
}
七、准备dao层接口和实现类
-
package com.lanson.dao;
-
import com.lanson.pojo.Emp;
-
import java.util.List;
-
/**
-
* @Author: Lansonli
-
* @Description: MircoMessage:Mark_7001
-
*/
-
public interface EmpDao {
-
int findEmpCount();
-
Emp findByEmpno(int empno);
-
List<Emp> findByDeptno(int deptno);
-
int addEmp(Emp emp);
-
int updateEmp(Emp emp);
-
int deleteEmp(int empno);
-
}
-
package com.lanson.dao.impl;
-
import com.lanson.dao.EmpDao;
-
import com.lanson.pojo.Emp;
-
import org.springframework.beans.factory.annotation.Autowired;
-
import org.springframework.jdbc.core.BeanPropertyRowMapper;
-
import org.springframework.jdbc.core.JdbcTemplate;
-
import org.springframework.jdbc.core.RowMapper;
-
import org.springframework.stereotype.Repository;
-
import java.sql.ResultSet;
-
import java.sql.SQLException;
-
import java.util.List;
-
/**
-
* @Author: Lansonli
-
* @Description: MircoMessage:Mark_7001
-
*/
-
@Repository
-
public class EmpDaoImpl implements EmpDao {
-
@Autowired
-
private JdbcTemplate jdbcTemplate;
-
@Override
-
public int findEmpCount() {
-
/*查询员工个数
-
* queryForObject 两个参数
-
* 1 SQL语句
-
* 2 返回值类型
-
*
-
* */
-
Integer empCount = jdbcTemplate.queryForObject("select count(1) from emp", Integer.class);
-
return empCount;
-
}
-
@Override
-
public Emp findByEmpno(int empno) {
-
/*
-
* 查询单个员工对象
-
* queryForObject三个参数
-
* 1 SQL语句
-
* 2 RowMapper接口的实现类对象,用于执行返回的结果用哪个类来进行封装 ,实现类为BeanPropertyRowMapper
-
* 3 SQL语句中需要的参数 (可变参数)
-
* */
-
BeanPropertyRowMapper<Emp> rowMapper =new BeanPropertyRowMapper<>(Emp.class);
-
Emp emp = jdbcTemplate.queryForObject("select * from emp where empno =?", rowMapper, empno);
-
return emp;
-
}
-
@Override
-
public List<Emp> findByDeptno(int deptno) {
-
/*
-
* 查询单个员工对象
-
* query三个参数
-
* 1 SQL语句
-
* 2 RowMapper接口的实现类对象,用于执行返回的结果用哪个类来进行封装 ,实现类为BeanPropertyRowMapper
-
* 3 SQL语句中需要的参数 (可变参数)
-
* */
-
BeanPropertyRowMapper<Emp> rowMapper =new BeanPropertyRowMapper<>(Emp.class);
-
List<Emp> emps = jdbcTemplate.query("select * from emp where deptno =?", rowMapper, deptno);
-
return emps;
-
}
-
@Override
-
public int addEmp(Emp emp) {
-
/*增删改
-
* 统统用update方法 两个参数
-
* 1 SQL语句
-
* 2 SQL语句需要的参数 (可变参数)
-
*
-
* */
-
String sql ="insert into emp values(DEFAULT ,?,?,?,?,?,?,?)";
-
Object[] args ={emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno()};
-
return jdbcTemplate.update(sql,args);
-
}
-
@Override
-
public int updateEmp(Emp emp) {
-
String sql ="update emp set ename =? , job =?, mgr=? , hiredate =?, sal=?, comm=?, deptno =? where empno =?";
-
Object[] args ={emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno(),emp.getEmpno()};
-
return jdbcTemplate.update(sql,args);
-
}
-
@Override
-
public int deleteEmp(int empno) {
-
String sql ="delete from emp where empno =?";
-
return jdbcTemplate.update(sql, empno);
-
}
-
}
八、测试代码
-
package com.lanson.test;
-
import com.lanson.pojo.Emp;
-
import com.lanson.service.EmpService;
-
import org.junit.Test;
-
import org.springframework.context.ApplicationContext;
-
import org.springframework.context.support.ClassPathXmlApplicationContext;
-
import java.util.Date;
-
import java.util.List;
-
/**
-
* @Author: Lansonli
-
* @Description: MircoMessage:Mark_7001
-
*/
-
public class Test1 {
-
@Test
-
public void testEmpService(){
-
ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
-
EmpService empService = context.getBean(EmpService.class);
-
// 查询员工个数
-
/*int empCount = empService.findEmpCount();
-
System.out.println(empCount);*/
-
// 根据员工编号查询员工对象
-
/* Emp byEmpno = empService.findByEmpno(7521);
-
System.out.println(byEmpno);*/
-
/*根据部门编号查询多个员工对象集合*/
-
/*List<Emp> emps = empService.findByDeptno(20);
-
emps.forEach(System.out::println);*/
-
/*增加员工信息*/
-
/*int rows = empService.addEmp(new Emp(null, "TOM", "SALESMAN", 7521, new Date(), 2000.0, 100.0, 10));
-
System.out.println(rows);*/
-
/*根据员工编号修改员工信息*/
-
/*int rows = empService.updateEmp(new Emp(7939, "JERRY", "MANAGER", 7839, new Date(), 3000.0, 0.0, 20));
-
System.out.println(rows);*/
-
/*根据员工编号删除员工信息*/
-
/*int rows = empService.deleteEmp(7939);
-
System.out.println(rows);*/
-
}
-
}
- 📢博客主页：https://lansonli.blog.csdn.net
- 📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正！
- 📢本文由 Lansonli 原创，首发于 CSDN博客🙉
- 📢停下休息的时候不要忘了别人还在奔跑，希望大家抓紧时间学习，全力奔赴更美好的生活✨
文章来源: lansonli.blog.csdn.net，作者：Lansonli，版权归原作者所有，如需转载，请联系作者。
原文链接：lansonli.blog.csdn.net/article/details/126881764
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）