Spring 与 JdbcTemplate

1. 前言

Spring 除了 Mybatis 外同样支持 JPA 和 JdbcTemplate 等的数据映射框架。这里简单给一个关于 JdbcTemplate 的示例。

JdbcTemplate 的功能当然不如 MyBatis 来的强大,但是如果偏爱 Java 代码手动做对象映射的可以试一试,下面不多废话,直接上代码。

2. 实例配置

首先给出需要引入的依赖:

  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
  </dependency>
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>

然后自定义数据源 dataSourcejdbcTemplate

  package com.avaloninc.springjdbctemplateexample.conf;
  
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.context.annotation.Bean;
  import org.springframework.context.annotation.Configuration;
  import org.springframework.context.annotation.PropertySource;
  import org.springframework.core.env.Environment;
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.jdbc.datasource.DataSourceTransactionManager;
  import org.springframework.jdbc.datasource.DriverManagerDataSource;
  import org.springframework.transaction.PlatformTransactionManager;
  import org.springframework.transaction.annotation.EnableTransactionManagement;
  
  import javax.sql.DataSource;
  
  @Configuration
  @EnableTransactionManagement
  @PropertySource(value = "classpath:application.properties")
  public class AppConfig {
  
    @Autowired
    private Environment env;
  
    @Bean(name = "dataSource")
    public DataSource dataSource() {
      DriverManagerDataSource dataSource = new DriverManagerDataSource();
      String                  url        = env.getProperty("jdbc.url");
      String                  userName   = env.getProperty("jdbc.username");
      String                  password   = env.getProperty("jdbc.password");
      dataSource.setUrl(url);
      dataSource.setUsername(userName);
      dataSource.setPassword(password);
      return dataSource;
    }
  
    @Bean
    public PlatformTransactionManager dataSourceTransactionManager() {
      return new DataSourceTransactionManager(dataSource());
    }
  
    @Bean
    public JdbcTemplate jdbcTemplate() {
      JdbcTemplate jdbcTemplate = new JdbcTemplate();
      jdbcTemplate.setDataSource(dataSource());
      return jdbcTemplate;
    }
  }

这段代码中,我们通过注入 Environment 对象来实现从配置文件中获取数据库的连接信息。

3. 实例代码

先给出模型类:

  package com.avaloninc.springjdbctemplateexample.domain;
  
  import lombok.Data;
  
  @Data
  public class Person {
    private int    id;
    private String name;
    private int    age;
    private Gender gender;
  
    public enum Gender {
      MALE,
      FEMALE;
    }
  }
  

然后给定两个简单的读写接口的实现:

  package com.avaloninc.springjdbctemplateexample.service;
  
  import com.avaloninc.springjdbctemplateexample.domain.Person;
  
  public interface PersonService {
    /**
     * Insert int.
     *
     * @param person the person
     * @return the int
     */
    int insert(Person person);
  
    /**
     * Gets person by id.
     *
     * @param id the id
     * @return the person by id
     */
    Person getPersonById(int id);
  }
  
  package com.avaloninc.springjdbctemplateexample.service;
  
  import com.avaloninc.springjdbctemplateexample.domain.Person;
  import com.avaloninc.springjdbctemplateexample.domain.Person.Gender;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.jdbc.core.RowMapper;
  import org.springframework.jdbc.support.GeneratedKeyHolder;
  import org.springframework.stereotype.Service;
  
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  @Service
  public class PersonServiceImpl implements PersonService {
  
    @Autowired
    private JdbcTemplate jdbcTemplate;
  
    @Override
    public int insert(Person person) {
      GeneratedKeyHolder holder = new GeneratedKeyHolder();
      int rowCount = this.jdbcTemplate.update(
          connection -> {
            PreparedStatement statement = connection.prepareStatement(
                "insert into person (name, age, gender) values (?, ?,?)",
                Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, person.getName());
            statement.setInt(2, person.getAge());
            statement.setInt(3, person.getGender().ordinal());
            return statement;
          },
          holder);
      person.setId(holder.getKey().intValue());
      return rowCount;
    }
  
    @Override
    public Person getPersonById(int id) {
      return this.jdbcTemplate.queryForObject("select id, name, age, gender from person where id = ?",
                                              new Object[] {id}, new RowMapper<Person>() {
            @Override
            public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
              Person person = new Person();
              person.setId(rs.getInt("id"));
              person.setName(rs.getString("name"));
              person.setAge(rs.getInt("age"));
              person.setGender(Gender.values()[rs.getInt("gender")]);
              return person;
            }
          });
    }
  }
  

完全手动实现了对象的映射,对于复杂类型的数据反序列化,不必借助 MyBatis 的 TypeHandler,直接代码实现即可。

4. 单元测试

单元测试走一发,确认有效:

  package com.avaloninc.springjdbctemplateexample.service;
  
  import com.avaloninc.springjdbctemplateexample.domain.Person;
  import com.avaloninc.springjdbctemplateexample.domain.Person.Gender;
  import junit.framework.TestCase;
  import org.junit.Test;
  import org.junit.runner.RunWith;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.boot.test.context.SpringBootTest;
  import org.springframework.test.context.junit4.SpringRunner;
  
  @SpringBootTest
  @RunWith(SpringRunner.class)
  public class PersonServiceImplTest extends TestCase {
  
    @Autowired
    private PersonService personService;
  
    @Test
    public void test() {
      Person person = new Person();
      person.setName("John");
      person.setAge(28);
      person.setGender(Gender.MALE);
  
      int count = personService.insert(person);
      assertTrue(count > 0);
      assertTrue(person.getId() > 0);
      Person personById = personService.getPersonById(person.getId());
      assertNotNull(personById);
      assertEquals(person, personById);
    }
  }

Insert Or Update 后续

1. 问题描述

使用 insert … on duplicate key update 语法实现 insertOrUpdate 之后出现了几个新问题,首先给出测试代码:

DROP database if EXISTS test;
CREATE database test;

DROP TABLE if EXISTS test.person;
CREATE table test.person (
    id int not NULL PRIMARY KEY auto_increment,
    name VARCHAR(100) not NULL DEFAULT '' UNIQUE COMMENT '名字',
    age int not NULL DEFAULT 0 COMMENT '年龄',
    gender VARCHAR(20) NOT NULL DEFAULT '' COMMENT '性别',
    addresses text NOT NULL COMMENT '地址'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='person';

MyBatis 语句:

<insert id="insertOrUpdate" useGeneratedKeys="true" keyProperty="id">
    insert into test.person
    (id, name, age, gender, addresses)
    VALUES
    <foreach collection="list" item="person" separator=",">
        (id, #{person.name}, #{person.age}, #{person.gender},
        #{person.addresses, typeHandler=com.note4code.test.persistence.typeHandler.GenericMapHandler})
    </foreach>
    on duplicate key update
    age = VALUES(age),
    gender = VALUES(gender),
    addresses = VALUES(addresses)
</insert>

Java 代码:

package com.note4code.test.service;

import com.note4code.test.domain.Address;
import com.note4code.test.domain.Gender;
import com.note4code.test.domain.Person;
import com.note4code.test.domain.Province;
import junit.framework.TestCase;
import org.assertj.core.util.Lists;
import org.assertj.core.util.Maps;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.IOException;
import java.util.List;
import java.util.Map;

@RunWith(SpringRunner.class)
@SpringBootTest
public class PersonServiceTest extends TestCase{

  @Autowired
  private PersonService personService;

  private Person me;
  private Person you;
  private Person him;

  @Before
  public void initData() {
    Address address = new Address(Province.BEIJING, "北京", "学院路");
    Map<Province, Address> map = Maps.newHashMap(Province.BEIJING, address);

    this.me = new Person();
    this.me.setName("me");
    this.me.setAge(27);
    this.me.setGender(Gender.MALE);
    this.me.setAddresses(map);

    this.you = new Person();
    this.you.setName("you");
    this.you.setAge(25);
    this.you.setGender(Gender.FEMALE);
    this.you.setAddresses(map);

    this.him = new Person();
    this.him.setName("him");
    this.him.setAge(25);
    this.him.setGender(Gender.MALE);
    this.him.setAddresses(map);
  }

  @Test
  public void testForOnDuplicateKey() {
    personService.addPerson(me);
    int id = me.getId();

    me.setAge(28);
    List<Person> people = Lists.newArrayList(me, you, him);
    personService.addOrUpdate(people);
    assertTrue(id != me.getId());
  }
}

运行测试用例,得到的输出结果是:

people = [Person{id=2, name=’me’, age=28, gender=MALE, addresses={BEIJING=Address{province=BEIJING, city=’北京’, street=’学院路’}}}
, Person{id=0, name=’you’, age=25, gender=FEMALE, addresses={BEIJING=Address{province=BEIJING, city=’北京’, street=’学院路’}}}
, Person{id=0, name=’him’, age=25, gender=MALE, addresses={BEIJING=Address{province=BEIJING, city=’北京’, street=’学院路’}}}
]

另外,查询数据库可以得到:

mysql root@localhost:test> SELECT * from person;
+------+--------+-------+----------+--------------------------------------------------------------------+
|   id | name   |   age | gender   | addresses                                                          |
|------+--------+-------+----------+--------------------------------------------------------------------|
|    1 | me     |    28 | MALE     | {"BEIJING":{"province":"BEIJING","city":"北京","street":"学院路"}} |
|    2 | you    |    25 | FEMALE   | {"BEIJING":{"province":"BEIJING","city":"北京","street":"学院路"}} |
|    3 | him    |    25 | MALE     | {"BEIJING":{"province":"BEIJING","city":"北京","street":"学院路"}} |
+------+--------+-------+----------+--------------------------------------------------------------------+
3 rows in set
Time: 0.002s
mysql root@localhost:test> SELECT LAST_INSERT_ID();
+--------------------+
|   LAST_INSERT_ID() |
|--------------------|
|                 17 |
+--------------------+
1 row in set
Time: 0.001s

从上面的示例可以看出 3 个问题:

  1. 即使使用了 userGeneratedKeys = true 并指定了 keyProperty,只回写了第一行的主键。
  2. 回写的主键与数据库不一致。
  3. LAST_INSERT_ID() 的值发生了跳跃,按理来说应该是 3,但是变成了 17。

2. 疑问

看到这里其实很让人费解:

  1. 为什么只返回了一个主键?
  2. useGeneratedKeys 返回的主键不对那么到底是什么?
  3. 为什么 LAST_INSERT_ID() 发生了跳变?

首先从 userGeneratedKeys 说起:

useGeneratedKeys(仅对 insert 和 update 有用)这会令 MyBatis 使用 JDBC 的 getGeneratedKeys 方法来取出由数据库内部生成的主键(比如:像 MySQL 和 SQL Server 这样的关系数据库管理系统的自动递增字段),默认值:false。

引自 insert, update 和 delete

With older JDBC drivers for MySQL, you could always use a MySQL-specific method on theStatement interface, or issue the query SELECT LAST_INSERT_ID() after issuing an INSERT to a table that had an AUTO_INCREMENT key.

First, we demonstrate the use of the new JDBC 3.0 method getGeneratedKeys() which is now the preferred method to use if you need to retrieve AUTO_INCREMENT keys and have access to JDBC 3.0. The second example shows how you can retrieve the same value using a standard SELECT LAST_INSERT_ID() query. 

引自 Retrieving AUTO_INCREMENT Column Values through JDBC

也就是说 Mybatis 通过 useGeneratedKeys 返回的是 LAST_INSERT_ID()

接着说,那么为什么只回写了一个主键,并且还是错的呢?

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only.

引自 LAST_INSERT_ID()LAST_INSERT_ID(expr)

按照上文的说法,批量插入只会返回插入的第一条数据的主键。第一次插入 me 这个对象之后 LAST_INSERT_ID() 返回 1。接着在插入 people 时首先是更新了 me 这行记录,而 LAST_INSERT_ID() 没有变。直到插入第二行 you 这个对象,此时 LAST_INSERT_ID() 返回 2,也就是批量插入后回写的主键值。这同时解释了为什么只回写了一个主键并且回写的主键与数据库没有对应上。

最后,关于 LAST_INSERT_ID() 的跳变,我也找到了一些参考资料:

  1. 官方文档:InnoDB AUTO_INCREMENT Lock Modes
  2. 其实这个说的更加简洁清楚:AUTO_INCREMENT 字段的GAP

Insert Or Update

1. MySQL 批量插入

有的时候会有这样的需求:

  1. 批量写入数据,但是数据可能存在主键或者唯一性索引的冲突写到一半就失败了。
  2. 每天批量更新表,新数据插入,旧数据更新。

于是我们不禁想到是否能实现一个 insertOrUpdate 方法呢?事实上,MySQL 给出了标准 SQL 的两种扩展来帮我们实现这种需求。

2. INSERT ... ON DUPLICATE KEY UPDATE

标准 SQL 的扩展,如果插入时遇到主键冲突或者唯一性约束不满足则会执行 update 操作,例如:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

支持多行插入:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

ON DUPLICATE KEY UPDATE 子句中的 VALUES(a) 表示的是如果没有冲突将会插入 a 列的值,即 1 或者 4。

ON DUPLICATE KEY UPDATE 子句不适宜在有多个唯一性索引的表上使用。

参考:14.2.5.3 INSERT … ON DUPLICATE KEY UPDATE Syntax

3. Replace

Replace 是标准 SQL 的扩展,等价于 inserts/deletes and inserts

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    SELECT ...

所有列的值都从 Replace 语句中获取,无法从现有的行中获取值,如果有的列没有指定值那么会给定默认值。如果在指定值的时候使用了 SET col_name = col_name + 1 语句,等价于 SET col_name = DEFAULT(col_name) + 1

Replace 语句的返回值表示影响的行数,影响行数 = 删除的行数 + 插入的行数。对于单行的 Replace 操作,如果返回 1 表示仅仅插入了新的一行,如果大于 1 则表示删除了至少一行并插入了一行。

参考:14.2.8 REPLACE Syntax

3. 异同分析

从文档的说明来看,至少有两点差异:

  1. INSERT … ON DUPLICATE KEY UPDATE 在插入时可以使用现有行的值, Replace 不支持。
    mysql root@localhost:test> create table tb_table (a int not NULL PRIMARY key auto_increment, b int NOT NULL DEFAULT 0, c int NOT NULL DEFAULT 0)
    Query OK, 0 rows affected
    Time: 0.042s
    mysql root@localhost:test> insert into tb_table (a, b, c) VALUES (1,2,3);
    Query OK, 1 row affected
    Time: 0.024s
    mysql root@localhost:test> INSERT INTO tb_table (a, b, c) values (1,5,6) on  DUPLICATE KEY UPDATE b = values(b) + b;
    Query OK, 2 rows affected
    Time: 0.025s
    mysql root@localhost:test> SELECT * from tb_table;
    +-----+-----+-----+
    |   a |   b |   c |
    |-----+-----+-----|
    |   1 |   7 |   3 |
    +-----+-----+-----+
    1 row in set
    Time: 0.001s
    
  2. INSERT … ON DUPLICATE KEY UPDATE 在不满足主键约束或者唯一性约束的时候是更新操作,所以主键不会变。而 Replacedeletes inserts 也就是说老的记录会被删除,如果不指定主键值那么可能会破坏已有的外键关联(比如主键是自增产生,插入的时候不指定主键值)。
    mysql root@localhost:test> create table tb_table (a int not NULL PRIMARY key auto_increment, b int NOT NULL DEFAULT 0 unique, c int NOT NULL DEFAULT 0)
    Query OK, 0 rows affected
    Time: 0.027s
    mysql root@localhost:test> show create table tb_table;
    +----------+----------------+
    | Table    | Create Table   |
    |----------+----------------|
    | tb_table | CREATE TABLE `tb_table` (
     `a` int(11) NOT NULL AUTO_INCREMENT,
     `b` int(11) NOT NULL DEFAULT '0',
     `c` int(11) NOT NULL DEFAULT '0',
     PRIMARY KEY (`a`),
     UNIQUE KEY `b` (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8                |
    +----------+----------------+
    1 row in set
    Time: 0.020s
    mysql root@localhost:test> insert into tb_table (b,c) VALUES (2,3);
    Query OK, 1 row affected
    Time: 0.013s
    mysql root@localhost:test> SELECT * from tb_table;
    +-----+-----+-----+
    |   a |   b |   c |
    |-----+-----+-----|
    |   1 |   2 |   3 |
    +-----+-----+-----+
    1 row in set
    Time: 0.001s
    mysql root@localhost:test> replace into tb_table (b,c) values (2,4);
    Query OK, 2 rows affected
    Time: 0.023s
    mysql root@localhost:test> SELECT * from tb_table;
    +-----+-----+-----+
    |   a |   b |   c |
    |-----+-----+-----|
    |   2 |   2 |   4 |
    +-----+-----+-----+
    1 row in set
    Time: 0.001s
    mysql root@localhost:test> replace into tb_table (b,c) values (2,4),(3,5);
    Query OK, 3 rows affected
    Time: 0.007s
    mysql root@localhost:test> SELECT * from tb_table;
    +-----+-----+-----+
    |   a |   b |   c |
    |-----+-----+-----|
    |   3 |   2 |   4 |
    |   4 |   3 |   5 |
    +-----+-----+-----+
    2 rows in set
    Time: 0.001s
    mysql root@localhost:test> insert into tb_table (b,c) values (2,8) on  DUPLICATE KEY UPDATE c = VALUES(c);
    Query OK, 2 rows affected
    Time: 0.013s
    mysql root@localhost:test> SELECT * from tb_table;
    +-----+-----+-----+
    |   a |   b |   c |
    |-----+-----+-----|
    |   3 |   2 |   8 |
    |   4 |   3 |   5 |
    +-----+-----+-----+
    2 rows in set
    Time: 0.001s
    

为 MySQL 表添加 ctime 和 mtime

1. 序

以前在使用 Ruby on Rails 的时候,框架自动会为每张表增加 mtimectime 两个字段,最近在手动建 MySQL 表的时候对这两个字段做了一点研究。

2. 字段的初始化和更新方式

  1. TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP: 在创建新记录和修改现有记录的时候都对这个数据列刷新。
  2. TIMESTAMP DEFAULT CURRENT_TIMESTAMP 在创建新记录的时候把这个字段设置为当前时间,但以后修改时,不再刷新它。
  3. TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 在创建新记录的时候把这个字段设置为 0,修改字段时更新。

当你给一个 timestamp 设置为 on update current_timestamp 的时候,其他的 timestamp 字段需要显式设定 default 值

3. 实例

CREATE TABLE if not exists mybatis.person(
  id BIGINT not null auto_increment PRIMARY KEY,
  name VARCHAR(20) NOT NULL DEFAULT '',
  phone VARCHAR(15) NOT NULL DEFAULT '',
  email VARCHAR(50) NOT NULL DEFAULT '',
  ctime TIMESTAMP DEFAULT '0000-00-00 00:00:00',
  mtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_bin;