实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作

精选原创

MrReady 2024-10-10 09:09:34 博主文章分类：java ©著作权

文章标签 sql JdbcTemplate Spring JDBC API MySQL 文章分类 软件测试

©著作权归作者所有：来自51CTO博客作者MrReady的原创作品，请联系作者获取转载授权，否则将追究法律责任

在这里实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作

1 新建工程

这里通过https://start.spring.io/来创建

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_Spring JDBC API

2 建测试表

CREATE TABLE `customer` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR ( 100 ) NULL,
    `birthday` datetime ( 0 ) NULL,
    `email` VARCHAR ( 100 ) NULL,
    `money` DECIMAL ( 10, 0 ) NULL 
)

3 编写单元测试代码

3.1 修改配置

spring.application.name=jdbcapi

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springtest?useSSL=false&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

#不打印数据库链接池的信息  减少日志输出
logging.level.com.zaxxer.hikari =warn

3.2 创建实体类Customer

Customer.java

package com.test.jdbcapi;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import lombok.Data;
import java.math.BigDecimal;
import java.util.Date;
import com.fasterxml.jackson.annotation.JsonFormat;

@Data
public final class Customer {
    private int id;
    private String name;
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date birthday;
    private String email;
    @JsonSerialize(using = ToStringSerializer.class)
    private BigDecimal money;
}

3.3 JdbcapiApplicationTests.java中添加注入JdbcTemplate

package com.test.jdbcapi;
import org.junit.jupiter.api.Test;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.sql.Date;
import java.sql.ResultSet;
@SpringBootTest
class JdbcapiApplicationTests {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
}

3.4 单元测试

3.4.1 insert插入

    @Test
    void testInsert() throws JsonProcessingException, ParseException {
        System.out.println("testInsert");
        Customer customer = new Customer();
        customer.setId(1);
        customer.setName("VIP客户");
        String dateString = "2000-01-01";
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        Date date = Date.valueOf(dateString);
        customer.setBirthday(date);
        customer.setEmail("326880839@");
        customer.setMoney(new BigDecimal("9999999"));
        String sql = "insert into customer(name,birthday,email,money) values(?,?,?,?)";
        int i = jdbcTemplate.update(sql, customer.getName(), customer.getBirthday(), customer.getEmail(), customer.getMoney());
        System.out.println("insert:" + i);
        System.out.println("结束");

        ObjectMapper objectMapper = new ObjectMapper();
        String str = objectMapper.writeValueAsString(customer);
        System.out.println(str);
    }

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_sql_02

3.4.2 update修改

    @Test
    void testUpdate() throws JsonProcessingException {
        System.out.println("testUpdate");
        Customer customer = new Customer();
        customer.setId(1);
        customer.setName("VIP客户");
        customer.setMoney(new BigDecimal("19999999"));

        String sql = "update customer set money=? where id=?";
        int i = jdbcTemplate.update(sql, customer.getMoney(), customer.getId());
        System.out.println("update:" + i);
        System.out.println("结束");

        ObjectMapper objectMapper = new ObjectMapper();
        String str = objectMapper.writeValueAsString(customer);
        System.out.println(str);
    }

3.4.3 删除

    @Test
    void testDelete() {
        System.out.println("testDelete");
        int id = 1;
        String sql = "delete from customer where id=?";
        int i = jdbcTemplate.update(sql, id);
        System.out.println("delete:" + i);
        System.out.println("结束");
    }

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_Spring JDBC API_03

3.4.4 删除多条数据

这时先往数据库写入多条数据后再进行以验证返回修改数据是否正确

    @Test
    void testDeleteByName() {
        System.out.println("testDeleteByName");
        String name = "VIP客户";
        String sql = "delete from customer where name=?";
        int i = jdbcTemplate.update(sql, name);
        System.out.println("delete:" + i);
        System.out.println("结束");
    }

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_sql_04

3.4.5截断表

    @Test
    void testTruncate() {
        System.out.println("testTruncate");
        String sql = "truncate  customer";
        //int i = jdbcTemplate.update(sql);
        //System.out.println("Truncate:" + i);
        jdbcTemplate.execute(sql);
        System.out.println("结束");
    }

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_MySQL_05

truncate截断表操作使用jdbcTemplate.update返回的修改行数是不正确的

3.4.6 查询返回SqlRowSet

    @Test
    void testQueryForRowSet() {
        System.out.println("testQueryForRowSet");
        String sql = "select * from customer";
        SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
        while (rowSet.next()) {
            int id = rowSet.getInt("id");
            String name = rowSet.getString("name");
            java.sql.Timestamp birthday = rowSet.getTimestamp("birthday");
            //String birthday= rowSet.getString("birthday");
            BigDecimal money = rowSet.getBigDecimal("money");
            System.out.println("id:" + id + ",name:" + name + ",birthday:" + birthday + ",money:" + money);
        }
    }

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_sql_06

3.4.7查询返回对象列表List<T>

    @Test
    void testGetAllCustomer() throws JsonProcessingException, ParseException {
        System.out.println("testGetAllCustomer");
        String sql = "select * from  customer";
        List<Customer> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Customer.class));
        list.forEach(customer -> System.out.println("customer: " + customer.getName()));
        ObjectMapper objectMapper = new ObjectMapper();
        String str = objectMapper.writeValueAsString(list);
        System.out.println(str);
    }

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_JdbcTemplate_07

3.4.8 查询返回 Map

    @Test
    void testQueryForMap() throws JsonProcessingException, ParseException {
        System.out.println("testQueryForMap");
        int id = 1;
        String sql = "select * from  customer where id=?";
        Map<String, Object> map = jdbcTemplate.queryForMap(sql, id);

        ObjectMapper objectMapper = new ObjectMapper();
        String str = objectMapper.writeValueAsString(map);
        System.out.println(str);
    }

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_sql_08

queryForMap在执行sql查询,结果如果返回多条记录时会报错的

org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [select * from customer]; Parameter index out of range (1 > number of parameters, which is 0).

3.4.9 查询返回单个对象Object

    @Test
    void testQueryForObject() throws JsonProcessingException, ParseException {
        System.out.println("testQueryForObject");
        int id = 1;
        String sql = "select * from  customer where id=?";
        Customer customer = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Customer.class), id);

        ObjectMapper objectMapper = new ObjectMapper();
        String str = objectMapper.writeValueAsString(customer);
        System.out.println(str);
    }

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_MySQL_09

3.4.10 自定义映射数据库字段与实体类属性

自定义实现，定制取数，用于将结果集映射到Customer对象

    //RowMapper自定义实现，定制取数，用于将结果集映射到Customer对象
    private static class CustomerRowMapper implements RowMapper<Customer> {
        @Override
        public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
            Customer customer = new Customer();
            customer.setId(rs.getInt("id"));
            customer.setName(rs.getString("name"));
            customer.setEmail(rs.getString("email"));
            return customer;
        }
    }
 
    @Test
    public void testQueryForObjectByCustomerRowMapper() throws JsonProcessingException {
        int id = 1;
        String sql = "select * from  customer WHERE id = ?";
        Customer customer = jdbcTemplate.queryForObject(sql, new CustomerRowMapper(), new Object[]{id});

        ObjectMapper objectMapper = new ObjectMapper();
        String str = objectMapper.writeValueAsString(customer);
        System.out.println(str);
    }

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_MySQL_10

3.4.11 批量处理

    @Test
    public void testBatchUpdate() throws JsonProcessingException {
        List<Customer> customers = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Customer customer = new Customer();
            customer.setName("客户" + i);
            customers.add(customer);
        }

        String sql = "insert into customer(name) values(?)";
        int[][] resultInt = jdbcTemplate.batchUpdate(sql, customers, customers.size(), (ps, customer) -> {
            ps.setString(1, customer.getName());
        });

        ObjectMapper objectMapper = new ObjectMapper();
        String str = objectMapper.writeValueAsString(resultInt);
        System.out.println(str);
    }

实践使用Spring JDBC API JdbcTemplate来对MySQL数据库进行增删改查操作_JdbcTemplate_11