【从零开始学Spring Boot】-11.Spring Boot JdbcTemplate操作数据库

1.简介

1.1 概述

This is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in the dao package.

这是JDBC核心软件包中的中心类。它简化了JDBC的使用并有助于避免常见错误。它执行核心的JDBC工作流程，留下应用程序代码以提供SQL并提取结果。此类执行SQL查询或更新，在ResultSets上启动迭代并捕获JDBC异常，并将其转换为dao包中定义的通用，信息量更大的异常层次结构。

1.2 特点

2.演示环境

1. JDK 1.8.0_201
2. Spring Boot 2.2.0.RELEASE
3. 构建工具（apache maven 3.6.3）
4. 开发工具（IntelliJ IDEA ）

3.演示代码

3.1 代码说明

使用 JdbcTemplate 连接 mysql 数据库，进行常见的数据库操作，并在 web 项目中展示。

同时配置了两个数据源，分别是：master、slave，可以方便的在二者之间切换。

3.2 代码结构

3.3 maven 依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

3.4 配置文件

application.properties

spring.datasource.master.url=jdbc:mysql://172.16.11.125:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.master.username=root
spring.datasource.master.password=123456
spring.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.slave.url=jdbc:mysql://172.16.11.125:3306/test_jpa?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.slave.username=root
spring.datasource.slave.password=123456
spring.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver

3.5 java代码

UserModel.java

public class UserModel {
    private Long id;
    private String name;
    private Integer age;
    private String birthday;
    private String address;
    private String phone;

    public UserModel() {}

    public UserModel(String name, Integer age, String birthday, String address, String phone) {
        this.name = name;
        this.age = age;
        this.birthday = birthday;
        this.address = address;
        this.phone = phone;
    }

    // get&set&toString
}

DataSourceConfig.java

@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSourceProperties masterDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSourceProperties slaveDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    public DataSource masterDataSource() {
        return masterDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean
    public DataSource slaveDataSource() {
        return slaveDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean(name = "masterJdbcTemplate")
    public JdbcTemplate masterJdbcTemplate() {
        return new JdbcTemplate(masterDataSource());
    }

    @Bean(name = "slaveJdbcTemplate")
    public JdbcTemplate slaveJdbcTemplate() {
        return new JdbcTemplate(slaveDataSource());
    }
}

UserRowMapper.java

public class UserRowMapper implements RowMapper<UserModel> {
    @Override
    public UserModel mapRow(ResultSet resultSet, int i) throws SQLException {
        UserModel userModel = new UserModel();
        userModel.setId(resultSet.getLong("id"));
        userModel.setName(resultSet.getString("name"));
        userModel.setAge(resultSet.getInt("age"));
        userModel.setBirthday(resultSet.getString("birthday"));
        userModel.setAddress(resultSet.getString("address"));
        userModel.setPhone(resultSet.getString("phone"));
        return userModel;
    }
}

UserService.java

public interface UserService {

    /**
     * 根据id查找
     * @param id id
     * @return userModel
     */
    UserModel findUserById(Long id);

    /**
     * 根据名称查找
     * @param name name
     * @return userModel
     */
    UserModel findUserByName(String name);

    /**
     * 查询所有
     * @return userModel
     */
    List<UserModel> findAll();

    /**
     * 新增
     * @param userModel userModel
     * @return userModel
     */
    UserModel addUser(UserModel userModel);

    /**
     * 新增2
     * @param userModel userModel
     * @return userModel
     */
    UserModel insertUser(UserModel userModel);

    /**
     * 新增并获取主键
     * @param userModel userModel
     * @return userModel
     */
    UserModel insertAndGetPK(UserModel userModel);

    /**
     * 根据id更新地址
     * @param address 地址
     * @param id id
     * @return userModel
     */
    UserModel updateAddressById(String address, Long id);

    /**
     * 根据id删除
     * @param id id
     * @return userModel
     */
    UserModel deleteById(Long id);
}

UserServiceImpl.java

@Service
public class UserServiceImpl implements UserService {

    private static final Logger LOGGER = LoggerFactory.getLogger(UserServiceImpl.class);

    @Autowired
    @Qualifier("masterJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Override
    public UserModel findUserById(Long id) {
        String sql = "select id, `name`, age, birthday, address, phone from t_user where id = ?";
        return jdbcTemplate.queryForObject(sql, new Object[] {id}, new UserRowMapper());
    }

    @Override
    public UserModel findUserByName(String name) {
        String sql = "select id, `name`, age, birthday, address, phone from t_user where name = ?";
        return jdbcTemplate.queryForObject(sql, new Object[] {name}, new UserRowMapper());
    }

    @Override
    public List<UserModel> findAll() {
        String sql = "select id, `name`, age, birthday, address, phone from t_user";
        // List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(UserModel.class));
    }

    @Override
    public UserModel addUser(UserModel user) {
        String sql = "INSERT INTO t_user(`name`, age, birthday, address, phone) VALUES (?, ?, ?, ?, ?);";
        // jdbcTemplate.update(sql, new Object[] {user.getName(), user.getAge(), user.getBirthday(), user.getAddress(),
        // user.getPhone()});
        // 批量操作使用 batchUpdate
        jdbcTemplate.update(sql, user.getName(), user.getAge(), user.getBirthday(), user.getAddress(), user.getPhone());
        return findUserByName(user.getName());
    }

    @Override
    public UserModel insertUser(UserModel user) {
        String sql = "INSERT INTO t_user(`name`, age, birthday, address, phone) VALUES (?, ?, ?, ?, ?);";
        jdbcTemplate.update(sql, ps -> {
            ps.setString(1, user.getName());
            ps.setInt(2, user.getAge());
            ps.setString(3, user.getBirthday());
            ps.setString(4, user.getAddress());
            ps.setString(5, user.getPhone());
        });
        return findUserByName(user.getName());
    }

    @Override
    public UserModel insertAndGetPK(UserModel user) {
        String sql = "INSERT INTO t_user(`name`, age, birthday, address, phone) VALUES (?, ?, ?, ?, ?);";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, new String[] {"id"});
            ps.setString(1, user.getName());
            ps.setInt(2, user.getAge());
            ps.setString(3, user.getBirthday());
            ps.setString(4, user.getAddress());
            ps.setString(5, user.getPhone());
            return ps;
        }, keyHolder);
        long pk = keyHolder.getKey().longValue();
        LOGGER.info("insertAndGetPK pk is {}", pk);
        return findUserById(pk);
    }

    @Override
    public UserModel updateAddressById(String address, Long id) {
        String sql = "UPDATE t_user set address = ? where id = ?";
        jdbcTemplate.update(sql, address, id);
        return findUserById(id);
    }

    @Override
    public UserModel deleteById(Long id) {
        String sql = "DELETE FROM t_user where id = ?";
        UserModel user = findUserById(id);
        jdbcTemplate.update(sql, id);
        return user;
    }
}

UserController.java

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping(value = "/list")
    public List<UserModel> list() {
        return userService.findAll();
    }

    @GetMapping(value = "/findByName/{name}")
    public UserModel findByName(@PathVariable String name) {
        return userService.findUserByName(name);
    }

    @GetMapping(value = "/findById/{id}")
    public UserModel findById(@PathVariable Long id) {
        return userService.findUserById(id);
    }

    @PostMapping(value = "/add")
    public UserModel add(@RequestBody UserModel userModel) {
        return userService.addUser(userModel);
    }

    @PostMapping(value = "/insert")
    public UserModel insert(@RequestBody UserModel userModel) {
        return userService.insertUser(userModel);
    }

    @PostMapping(value = "/insertAndGet")
    public UserModel insertAndGet(@RequestBody UserModel userModel) {
        return userService.insertAndGetPK(userModel);
    }

    @PutMapping(value = "/updateAddressById")
    public UserModel updateAddressById(@RequestBody UserModel userModel) {
        return userService.updateAddressById(userModel.getAddress(), userModel.getId());
    }

    @DeleteMapping(value = "/deleteById/{id}")
    public UserModel deleteById(@PathVariable Long id) {
        return userService.deleteById(id);
    }
}

3.6 git 地址

spring-boot/spring-boot-06-jdbc/spring-boot-jdbc-template

4.效果展示

启动 SpringBootJdbcTemplateApplication.main 方法，在 spring-boot-jdbc-template.http 访问下列地址，观察输出信息是否符合预期。

查询列表

### GET /user/list
GET http://localhost:8080/user/list
Accept: application/json

根据姓名查询

### GET /user/findByName/{name}
GET http://localhost:8080/user/findByName/zhangsan
Accept: application/json

根据id查询

### GET /user/findById/{id}
GET http://localhost:8080/user/findById/2
Accept: application/json

添加用户

### POST /user/add
POST http://localhost:8080/user/add
Accept: application/json
Content-Type: application/json

{
  "name": "aa123",
  "age": 31,
  "birthday": "1989-05-21",
  "address": "hangzhou",
  "phone": "15666666666"
}

添加用户2

### POST /user/insert
POST http://localhost:8080/user/insert
Accept: application/json
Content-Type: application/json

{
  "name": "bb123",
  "age": 32,
  "birthday": "1988-06-07",
  "address": "xian",
  "phone": "13455555555"
}

新增用户并返回主键

POST http://localhost:8080/user/insertAndGet
Accept: application/json
Content-Type: application/json

{
  "name": "cc123",
  "age": 42,
  "birthday": "1978-03-15",
  "address": "nanjing",
  "phone": "13999999999"
}

更新用户地址

### PUT /user/updateAddressById
PUT http://localhost:8080/user/updateAddressById
Accept: application/json
Content-Type: application/json

{
  "id": 8,
  "address": "suzhou"
}

根据id删除

### DELETE /user/deleteById
DELETE http://localhost:8080/user/deleteById/7
Accept: application/json