1 JdbcTemplate简介
- 为了使JDBC更加易于使用，Spring在JDBC API上定义了一个抽象层，以此建立一个JDBC存取的框架。
- 作为Spring JDBC框架的核心，JDBC模板的设计目的是为不同类型的JDBC操作提供模板方法，每个模板方法都能控制整个过程，并允许覆盖过程中的特定任务。通过这种方式，可以在尽可能保留灵活性的情况下，将数据库存取的工作量降到最低。
2 使用
- 使用步骤：
- 导入相关jar包。
- 配置数据库信息。
- 创建对应实体类。
- 使用JdbcTemplate。
1、导入相关jar包
2、配置数据库信息
db.properties：
jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/spring_test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC jdbc.username=root jdbc.password=root
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 https://www.springframework.org/schema/context/spring-context.xsd"> <!-- 注解扫描 --> <context:component-scan base-package="com.xianhuii"></context:component-scan> <!-- 配置数据源 --> <!-- 引入外部化的配置文件 --> <context:property-placeholder location="classpath:db.properties"/> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="${jdbc.driver}"></property> <property name="jdbcUrl" value="${jdbc.url}"></property> <property name="user" value="${jdbc.username}"></property> <property name="password" value="${jdbc.password}"></property> </bean> <!-- 配置jdbcTemplate --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!-- 注入数据源 --> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
3、实体类
package com.xianhuii.beans;
public class Employee {
private Integer id;
private String lastName;
private String email;
private Integer deptId;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", lastName='" + lastName + '\'' +
", email='" + email + '\'' +
", deptId=" + deptId +
'}';
}
}4、测试及使用方法
package com.xianhuii.test;
import com.xianhuii.beans.Employee;
import com.xianhuii.dao.EmployeeJdbcTemplateDao;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.util.ArrayList;
import java.util.List;
public class JdbcTemplateTest {
private ApplicationContext context;
private JdbcTemplate jdbcTemplate;
private EmployeeJdbcTemplateDao dao;
{
context = new ClassPathXmlApplicationContext("applicationContext.xml");
jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
dao = (EmployeeJdbcTemplateDao) context.getBean("employeeJdbcTemplateDao");
}
/**
* update：insert、update、delete
*/
@Test
public void testUpdate() {
String sql = "update employee set last_name = ? where id = ?";
jdbcTemplate.update(sql, "Black", 1001);
}
/**
* batchUpdate：批量增、删、改
*/
@Test
public void testBatchUpdate() {
String sql = "insert into employee (last_name, email, dept_id) values(?,?,?)";
List<Object[]> batchArgs = new ArrayList<>();
batchArgs.add(new Object[]{"AA", "aa@qq.com", 101});
batchArgs.add(new Object[]{"BB", "bb@qq.com", 101});
batchArgs.add(new Object[]{"CC", "cc@qq.com", 101});
batchArgs.add(new Object[]{"DD", "dd@qq.com", 101});
jdbcTemplate.batchUpdate(sql, batchArgs);
}
/**
* queryForObject()：查询单行返回单个对象
*/
@Test
public void testQueryForObject() {
String sql = "select id, last_name lastName, email, dept_id deptId from employee where id = ?";
// rowMapper：将数据库表中一行数据中每个列的值，映射到指定的JavaBean中对应的属性上。
// rowMapper解决了下划线到驼峰命名：last_name --> lastName
RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1001);
System.out.println(employee);
}
/**
* queryForObject()：查询返回单个值。（统计查询等）
*/
@Test
public void testQueryForObject1() {
String sql = "select count(id) from employee";
Integer result = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(result);
}
/**
* query：查询多行，返回多个对象。
*/
@Test
public void testQuery() {
String sql = "select id, last_name lastName, email, dept_id deptId from employee";
RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
List<Employee> employees = jdbcTemplate.query(sql, rowMapper);
for (Employee employee : employees) {
System.out.println(employee);
}
}
/**
* 测试dao
*/
@Test
public void testDaoGetEmployeeById() {
Employee employee = dao.getEmployeeById(1001);
System.out.println(employee);
}
}5、DAO层中使用示例
package com.xianhuii.dao;
import com.xianhuii.beans.Employee;
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;
@Repository
public class EmployeeJdbcTemplateDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public Employee getEmployeeById(Integer id) {
String sql = "select id, last_name lastName, email, dept_id deptId from employee where id = ?";
RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, id);
return employee;
}
}
京公网安备 11010502036488号