Spring JdbcTemplate 基本使用

JDBTemplate 是 Spring 框架中提供的 — 个模板对象,是对原始繁琐 JDBC API 对象的简单封装。

核心对象

导入依赖坐标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.3.10</version>
</dependency>
<!-- jdbcTemplate 注意要和spring版本一致 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.10</version>
</dependency>
<!-- 事务相关 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
<!-- 连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.6</version>
</dependency>
<!-- aspect织入(切点表达式需要用到该jar包) -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.7</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>

创建 Dao 和 Service 层

实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package com.orginly.domain;

public class User {
private Integer id;
private String name;
private Integer age;

public Integer getId() {
return id;
}

public void setId(Integer id) {
this.id = id;
}


public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public Integer getAge() {
return age;
}

public void setAge(Integer age) {
this.age = age;
}

@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}

Dao

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
// 接口
public interface UserDao {
public List<User> findAll();

public User find(int id);

public void updateUser(User user);
}


// 实现类
@Repository
public class UserDaoImpl implements UserDao {

@Autowired
private JdbcTemplate jdbcTemplate;

/**
* 用户列表
*/
@Override
public List<User> findAll() {
String sql = "select * from user";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
}

/**
* 查找一条用户信息
* @param id
*/
@Override
public User find(int id) {
String sql = "select * from user where id = ?";
return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
}

/**
* 更新用户信息
*/
@Override
public void updateUser(User user) {
String sql = "update user set name = ?,age = ? where id = ?";
jdbcTemplate.update(sql, user.getName(), user.getAge(), user.getId());
}
}

Service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// 接口
public interface UserService {
public void findALl();
public void updateUser();
}

@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;

@Override
public void findALl() {
List<User> users = userDao.findAll();
System.out.println(users.toString());
}

@Override
public void updateUser() {
User user = userDao.find(2);
user.setName("li1");
userDao.updateUser(user);
System.out.println(user);

}

}

整合 JdbcTemplate 至 Spring

  1. 新建 jdbc.properties 配置文件

    1
    2
    3
    4
    jdbc.driverClassName = com.mysql.cj.jdbc.Driver
    jdbc.url = jdbc:mysql://localhost/demo?characterEncoding=UTF-8
    jdbc.username = root
    jdbc.password = 000
  2. 配置 JdbcTemplate 数据源 并将对象创建权交给 IOC 容器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    <?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
    https://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context
    https://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 开启 IOC 注解扫描 -->
    <context:component-scan base-package="com.orginly"/>

    <!-- 引入 properties 文件 -->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!-- 配置数据源 -->
    <bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    </bean>

    <!-- jdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <!-- 绑定数据源 -->
    <property name="dataSource" ref="datasource"/>
    </bean>
    </beans>

整合 Junit4 单元测试

1
2
3
4
5
6
7
8
9
10
11
12
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringTest {

@Autowired
private UserService userService;

@Test
public void test() {
userService.findALl();
}
}