Spring

Spring 整合 JdbcTemplate 连接操作 MySQL

Posted by 暮夏有五 on 2021-01-13
Estimated Reading Time 5 Minutes
Words 1k In Total
Viewed Times

JdbcTemplate 是 Spring 利用 Aop 思想封装的 JDBC 操作工具。

准备工作

引入依赖

创建一个新项目，添加如下依赖：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.1.9.RELEASE</version>
</dependency>

<!-- DataBase Begin -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.1.9.RELEASE</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>
<!-- DataBase Over -->

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `test01`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

创建实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public class User {
    private Integer id;
    private String username;
    private String address;

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

	// 省略 get/set 方法
}

Java 配置

提供一个配置类，在配置类中配置 JdbcTemplate

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@Configuration
public class JdbcConfig {
    
    @Bean
    DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setUrl("jdbc:mysql:///test01");
        return dataSource;
    }
    
    @Bean
    JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource());
    }
}

这里，提供两个 Bean，一个是 DataSource 的 Bean，另一个是 JdbcTemplate 的 Bean，JdbcTemplate 的配置非常容易，只需要 new 一个 Bean 出来，然后配置一下 DataSource 就可以。

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
public class Main {

    private JdbcTemplate jdbcTemplate;

    @Before
    public void before() {
        AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(JdbcConfig.class);
        jdbcTemplate = ctx.getBean(JdbcTemplate.class);
    }

    @Test
    public void insert() {
        jdbcTemplate.update("insert into user (username,address) values (?,?);", "antonio", "www.antoniopeng.com");
    }
    @Test
    public void update() {
        jdbcTemplate.update("update user set username=? where id=?", "antonio", 1);

    }
    @Test
    public void delete() {
        jdbcTemplate.update("delete from user where id=?", 2);
    }

    @Test
    public void select() {
        User user = jdbcTemplate.queryForObject("select * from user where id=?", new BeanPropertyRowMapper<User>(User.class), 1);
        System.out.println(user);
    }
}

在查询时，如果使用了 BeanPropertyRowMapper，要求查出来的字段必须和 Bean 的属性名一一对应。如果不一样，则不要使用 BeanPropertyRowMapper，此时需要自定义 RowMapper 或者给查询的字段取别名。

  1. 给查询出来的列取别名：

    1
    2
    3
    4
    5
    @Test
    public void select2() {
        User user = jdbcTemplate.queryForObject("select id,username as name,address from user where id=?", new BeanPropertyRowMapper<User>(User.class), 1);
        System.out.println(user);
    }

  2. 自定义 RowMapper

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    @Test
    public void select3() {
        User user = jdbcTemplate.queryForObject("select * from user where id=?", new RowMapper<User>() {
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String address = resultSet.getString("address");
                User u = new User();
                u.setId(id);
                u.setName(username);
                u.setAddress(address);
                return u;
            }
        }, 1);
        System.out.println(user);
    }

查询多条记录，方式如下：

1
2
3
4
5
@Test
public void select4() {
    List<User> list = jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
    System.out.println(list);
}

XML 配置

以上配置，也可以通过 XML 文件来实现。通过 XML 文件实现只是提供 JdbcTemplate 实例，剩下的代码还是 Java 代码，就是 JdbcConfig 被 XML 文件代替而已。

1
2
3
4
5
6
7
8
9
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
    <property name="username" value="root"/>
    <property name="password" value="123"/>
    <property name="url" value="jdbc:mysql:///test01?serverTimezone=Asia/Shanghai"/>
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

配置完成后，加载该配置文件，并启动：

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
public class Main {

    private JdbcTemplate jdbcTemplate;

    @Before
    public void before() {
        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        jdbcTemplate = ctx.getBean(JdbcTemplate.class);
    }

    @Test
    public void insert() {
        jdbcTemplate.update("insert into user (username,address) values (?,?);", "javaboy", "www.javaboy.org");
    }
    @Test
    public void update() {
        jdbcTemplate.update("update user set username=? where id=?", "javaboy123", 1);

    }
    @Test
    public void delete() {
        jdbcTemplate.update("delete from user where id=?", 2);
    }

    @Test
    public void select() {
        User user = jdbcTemplate.queryForObject("select * from user where id=?", new BeanPropertyRowMapper<User>(User.class), 1);
        System.out.println(user);
    }
    @Test
    public void select4() {
        List<User> list = jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
        System.out.println(list);
    }

    @Test
    public void select2() {
        User user = jdbcTemplate.queryForObject("select id,username as name,address from user where id=?", new BeanPropertyRowMapper<User>(User.class), 1);
        System.out.println(user);
    }

    @Test
    public void select3() {
        User user = jdbcTemplate.queryForObject("select * from user where id=?", new RowMapper<User>() {
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String address = resultSet.getString("address");
                User u = new User();
                u.setId(id);
                u.setName(username);
                u.setAddress(address);
                return u;
            }
        }, 1);
        System.out.println(user);
    }

}

更多干货请移步：https://antoniopeng.com

If you like this blog or find it useful for you, you are welcome to comment on it. You are also welcome to share this blog, so that more people can participate in it. If the images used in the blog infringe your copyright, please contact the author to delete them. Thank you !

FEATURED TAGS
Spring
FRIENDS
ESC