什么是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>
<!-- 配置jdbc模板 -->
<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();
//根据id查询信息
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.add(dept);
//修改操作
//deptService.update(dept);
//删除操作
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);
//根据id查询信息
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);
}