1、Spring_JDBCTemplate的使用
JdbcTemplate概述
JdbcTemplate是spring框架中提供的一个对象,是对原始繁琐的Jdbc API对象的简单封装。spring框架为我们提供了很多的操作模板类。例如:操作关系型数据的JdbcTemplate和,操作nosql数据库的RedisTemplate,操作消息队列的JmsTemplate等等。
项目结构准备:
导入依赖
<packaging>jar</packaging>
<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
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
<!--spring 注解扫描-->
<context:component-scan base-package="com.wml"/>
<!--读取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>
准备实体类
@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层接口和实现类
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);
}
@Service
public class EmpServiceImpl implements EmpService {
/*需要调用dao层来查询员工个数*/
@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层接口和实现类
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);
}
@Repository
public class EmpDaoImpl implements EmpDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int findEmpCount() {
/*
* 查询员工个数
* jdbcTemplate.queryForObject三个参数
* 第一个参数 SQL语句
* 第二个参数 RowMapper接口的实现类对象,用于执行返回的结果用哪个类来进行封装 ,实现类为BeanPropertyRowMapper
*
* */
String sql = "select count(1) from emp";
/*实现功能*/
Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
/*queryForObject用Integer.class类来封装SQL语句*/
return integer;
}
@Override
public Emp findByEmpno(int empno) {
/*
* 查询单个员工对象
* 第一个参数 SQL语句
* 第二个参数 RowMapper接口的实现类对象,用于执行返回的结果用哪个类来进行封装 ,实现类为BeanPropertyRowMapper
* 第三个参数 SQL语句中需要的参数 (可变参数)
* */
String sql = "select * from emp where empno=?";
BeanPropertyRowMapper<Emp> rowMapper = new BeanPropertyRowMapper<Emp>(Emp.class);
Emp o = jdbcTemplate.queryForObject(sql,rowMapper,empno);
return o;
}
@Override
public List<Emp> findByDeptno(int deptno) {
/*
*查询多个员工对象集合
*query三个参数
*第一个参数 SQL语句
*第二个参数 RowMapper接口的实现类对象,用于执行返回的结果用哪个类来进行封装 ,实现类为BeanPropertyRowMapper
*第三个参数 SQL语句中需要的参数 (可变参数)
* */
String sql = "select * from emp where deptno = ?";
BeanPropertyRowMapper<Emp> rowMapper = new BeanPropertyRowMapper<Emp>(Emp.class);
List<Emp> emps = jdbcTemplate.query(sql, rowMapper, deptno);
return emps;
}
@Override
public int addEmp(Emp emp) {
/*
* 增删改员工信息
* 统统用update方法 两个参数
* jdbcTemplate.update
* 第一个参数 SQL语句
* 第三个参数 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);
}
}
测试代码
public class Test01 {
@Test
public void testEmpService(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationcontext.xml");
EmpService empService = context.getBean(EmpService.class);
//context.getBean("EmpServiceImpl",EmpService.class);id和类型
// 查询员工个数
/*int empCount = empService.findEmpCount();
System.out.println(empCount);*/
// 根据员工编号查询员工对象
/*Emp emp = empService.findByEmpno(7521);
System.out.println(emp);*/
/*根据部门编号查询多个员工对象集合*/
/*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(7935, "JERRY", "MANAGER", 7839, new Date(), 3000.0, 0.0, 20));
System.out.println(rows);*/
/*根据员工编号删除员工信息*/
int rows = empService.deleteEmp(7935);
System.out.println(rows);
}
}
2、Spring_JDBCTemplate批操作
一次连接,操作表格里的多条数据,就是批量操作
1 批量增加
2 批量修改
3 批量删除
项目结构、导入依赖、准备JDBC.properties、准备applicationcontext.xml
在1、Spring_JDBCTemplate的使用中
实体类
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Dept implements Serializable {
private Integer DEPTNO;
private String DNAME;
private String LOC;
}
准备Service层接口和实现类
public interface DeptService {
/*批量增删改*/
int[] deptBatchAdd(List<Dept> depts);
int[] deptBatchUpdate(List<Dept> depts);
int[] deptBatchDelete(List<Integer> deptnos);
}
@Service
public class DeptServiceImpl implements DeptService {
/*需要调用dao层来增删改*/
@Autowired
private DeptDao deptDao;
@Override
public int[] deptBatchAdd(List<Dept> depts) {
int[] ints = deptDao.deptBatchAdd(depts);
return ints;
}
@Override
public int[] deptBatchUpdate(List<Dept> depts) {
return deptDao.deptBatchUpdate(depts);
}
@Override
public int[] deptBatchDelete(List<Integer> deptnos) {
return deptDao.deptBatchDelete(deptnos);
}
}
准备Dao层接口和实现类
public interface DeptDao {
int[] deptBatchAdd(List<Dept> depts);
int[] deptBatchUpdate(List<Dept> depts);
int[] deptBatchDelete(List<Integer> deptnos);
}
@Repository
public class DeptDaoImpl implements DeptDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int[] deptBatchAdd(List<Dept> depts) {
String sql = "insert into dept values(DEFAULT,?,?)";
List<Object[]> args = new LinkedList<Object[]>();
for (Dept dept : depts) {
Object[] arg = {dept.getDNAME(),dept.getLOC()};
args.add(arg);
}
int[] ints = jdbcTemplate.batchUpdate(sql, args);
return ints;
}
@Override
public int[] deptBatchUpdate(List<Dept> depts) {
String sql = "update dept set DNAME = ? , LOC = ? where deptno = ?";
List<Object[]> args = new LinkedList<Object[]>();
for (Dept dept : depts) {
Object[] arg = {dept.getDNAME(),dept.getLOC(),dept.getDEPTNO()};
args.add(arg);
}
return jdbcTemplate.batchUpdate(sql,args);
}
@Override
public int[] deptBatchDelete(List<Integer> deptnos) {
String sql = "delete from dept where deptno = ?";
List<Object[]> args = new LinkedList<Object[]>();
for (Integer deptno : deptnos) {
Object[] arg = {deptno};
args.add(arg);
}
return jdbcTemplate.batchUpdate(sql,args);
}
}
测试代码
public class Test02 {
@Test
public void testdeptBatchAdd(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationcontext.xml");
DeptService deptService = context.getBean(DeptService.class);
List<Dept> depts = new ArrayList<>();
for (int i = 0; i <10 ; i++) {
depts.add(new Dept(null,"name"+i,"loc"+i));
}
int[] ints = deptService.deptBatchAdd(depts);
System.out.println(Arrays.toString(ints));
}
@Test
public void testdeptBatchUpdate(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationcontext.xml");
DeptService deptService = context.getBean(DeptService.class);
List<Dept> depts = new ArrayList<>();
for (int i = 86; i <96 ; i++) {
depts.add(new Dept(i,"newNAME","newLOC"));
}
int[] ints = deptService.deptBatchUpdate(depts);
System.out.println(Arrays.toString(ints));
}
@Test
public void testdeptBatchDelete(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationcontext.xml");
DeptService deptService = context.getBean(DeptService.class);
List<Integer> deptnos = new ArrayList<>();
for (int i = 86; i <96 ; i++) {
deptnos.add(i);
}
int[] ints = deptService.deptBatchDelete(deptnos);
System.out.println(Arrays.toString(ints));
}
}