Spring JdbcTemplate

1、简介

为了在特定领域帮助我们简化代码，Spring 封装了很多 『Template』形式的模板类。例如：RedisTemplate、RestTemplate 等等，包括我们今天要学习的 JDBCTemplate。

2、准备工作

①加入依赖

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.11.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.3</version>
        </dependency>

        <!-- Spring 持久化层支持jar包 -->
        <!-- Spring 在执行持久化层操作、与持久化层技术进行整合过程中，需要使用orm、jdbc、tx三个jar包 -->
        <!-- 导入 orm 包就可以通过 Maven 的依赖传递性把其他两个也导入 -->
        
        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.3.1</version>
        </dependency>
        <!-- 数据源 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.31</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.2.11.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

    </dependencies>
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

②jdbc.properties

jdbc.user=root
jdbc.password=root
jdbc.url=jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8
jdbc.driver=com.mysql.jdbc.Driver
1
2
3
4

③Spring 配置文件

[1]配置数据源

  <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <property name="username" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="url" value="${jdbc.url}"></property>
    </bean>
1
2
3
4
5
6
7

[2]配置 JDBCTemplate

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
1
2
3

[3]在测试类装配 JdbcTemplate

@RunWith(value = SpringJUnit4ClassRunner.class)
@ContextConfiguration(value = "classpath:ApplicationContext.xml")
public class SpringJunitTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;

  

}
1
2
3
4
5
6
7
8
9

3、基本用法

@RunWith(value = SpringJUnit4ClassRunner.class)
@ContextConfiguration(value = "classpath:ApplicationContext.xml")
public class SpringJunitTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 增
     */
    @Test
    public void testAdd(){
        String sql="insert  into  user values('5','牛魔王','1995-02-12','男','火焰山')";
        jdbcTemplate.update(sql);
    }
    /**
     * 删
     */

    @Test
    public void testDelete(){
        String sql="delete from user where id =?";
        jdbcTemplate.update(sql,5);
    }
    /**
     * 改
     */
    @Test
    public void testUpdate(){
        String sql="update user set username=? where id=?";
        jdbcTemplate.update(sql,"红孩儿",5);
    }

    /**
     * 查--查一个字段
     */
    @Test
    public void testQuery(){
        String sql="select username from user where id=? ";
        String username = jdbcTemplate.queryForObject(sql, String.class, 5);
        System.out.println(username);
    }

    /**
     * 查一个实体类
     */
    @Test
    public void testObject(){
        String sql="select * from user where id=?";
        User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class),5);
        System.out.println(user.toString());

    }

}

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
47
48
49
50
51
52
53
54
55

其中查询实体类是借助RowMapper 完成查询
需要创建一个实体类与要查询的表对应

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

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
47
48
49
50
51
52
53
54
55
56
57
58
59