Spring的JdbcTemplate的基本操作入门
JdbcTemplate概述 :
JdbcTemplate是spring框架中提供的一个对象，是对原始繁琐的Jdbc API对象的简单封装。spring框架为我们提供了很多的操作模板类。例如：操作关系型数据的JdbcTemplate和，操作nosql数据库的RedisTemplate，操作消息队列的JmsTemplate等等。
1 创建项目，导入jar包，准备相关配置文件
pom.xml:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>spring_jdbcTemplate_01</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!--spring核心-->
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.21</version>
</dependency>
<!--切面-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.3.16</version>
</dependency>
<!--spring整合事务-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.3.21</version>
</dependency>
<!--orm对象关系映射-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.3.16</version>
</dependency>
<!--方便开发pojo类-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
<!--spring与jdbc整合-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.21</version>
</dependency>
<!--Spring日志-->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<!--数据库驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<!--连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.6</version>
</dependency>
<!--测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
jdbc.properties:
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql:
jdbc.username=root
jdbc.password=123456
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:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>
<context:component-scan base-package="com.zi"></context:component-scan>
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<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 id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
2 创建实体类及相关dao、service
Emp.class
package com.zi.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
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;
}
EmpDao:
package com.zi.dao;
import com.zi.pojo.Emp;
public interface EmpDao {
int findEmpCount();
Emp findEmpByEmpno(int empno);
int addEmp(Emp emp);
int updateEmp(Emp emp);
int deleteEmpByEmpno(int empno);
}
EmpDaoImpl:
package com.zi.dao.impl;
import com.zi.dao.EmpDao;
import com.zi.pojo.Emp;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class EmpDaoImpl implements EmpDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int findEmpCount() {
String sql = "select count(1) from emp";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
@Override
public Emp findEmpByEmpno(int empno) {
String sql = "select * from emp where empno = ?";
BeanPropertyRowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);
Emp emp = jdbcTemplate.queryForObject(sql, rowMapper, empno);
return emp;
}
@Override
public int addEmp(Emp emp) {
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()};
int row = jdbcTemplate.update(sql, args);
return row;
}
@Override
public int deleteEmpByEmpno(int empno) {
String sql = "delete from emp where empno = ?";
int i = jdbcTemplate.update(sql, empno);
return i;
}
}
EmpService:
package com.zi.service;
import com.zi.pojo.Emp;
public interface EmpService {
int findEmpCount();
Emp findEmpByEmpno(int empno);
int addEmp(Emp emp);
int updateEmp(Emp emp);
int deleteEmpByEmpno(int empno);
}
EmpServiceImpl:
package com.zi.service.impl;
import com.zi.dao.EmpDao;
import com.zi.pojo.Emp;
import com.zi.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class EmpServiceImpl implements EmpService {
@Autowired
private EmpDao empDao;
@Override
public int findEmpCount() {
return empDao.findEmpCount();
}
@Override
public Emp findEmpByEmpno(int empno) {
return empDao.findEmpByEmpno(empno);
}
@Override
public int addEmp(Emp emp) {
return empDao.addEmp(emp);
}
@Override
public int updateEmp(Emp emp) {
return empDao.updateEmp(emp);
}
@Override
public int deleteEmpByEmpno(int empno) {
return empDao.deleteEmpByEmpno(empno);
}
}
3 测试
package com.zi.test;
import com.zi.pojo.Emp;
import com.zi.service.EmpService;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.Date;
public class Test01 {
private ApplicationContext applicationContext = null;
private EmpService empService = null;
@Before
public void init(){
applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
empService = applicationContext.getBean(EmpService.class);
}
@Test
public void testFindEmpCount(){
System.out.println(empService.findEmpCount());
}
@Test
public void testFindEmpByEmpno(){
Emp emp = empService.findEmpByEmpno(7936);
System.out.println(emp);
}
@Test
public void testAddEmp(){
Emp emp = new Emp(null, "Rose", "SmallBoss", 7902, new Date(), 4000.0, 500.0, 20);
int row = empService.addEmp(emp);
System.out.println(row);
}
@Test
public void updateEmp(){
Emp emp = new Emp(7938, "大漂亮郭艾伦", "PG", 7902, new Date(), 6000.0, 700.0, 10);
int i = empService.updateEmp(emp);
System.out.println(i);
}
@Test
public void deleteEmpByEmpno(){
int i = empService.deleteEmpByEmpno(7938);
System.out.println(i);
}
}
4 拓展【JdbcTemplate的批处理操作】
EmpServiceImpl:
@Override
public int[] empBathAdd(List<Emp> emps) {
String sql = "insert into emp values(DEFAULT,?,?,?,?,?,?,?)";
List<Object[]> args = new ArrayList<>();
for (Emp emp : emps) {
Object[] arg = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno()};
args.add(arg);
}
return jdbcTemplate.batchUpdate(sql, args);
}
测试类：
@Test
public void empBatchAdd(){
List<Emp> emps = new ArrayList<>();
for (int i = 0; i < 15; i++) {
Emp emp = new Emp(null, "" + i, "" + i, 7902, new Date(), 6000.0, 700.0, 20);
emps.add(emp);
}
int[] row = empService.empBathAdd(emps);
System.out.println(Arrays.toString(row));
}
|