sql - DbUnit 不会在插入时更新 postgresql 序列

Question

我正在使用 DbUnit 在 postgreSql 数据库上运行一些测试。为了能够运行我的测试，我通过在每次测试之前重新填充数据库表，运行干净的插入，使数据库进入一个众所周知的状态。因此，我使用下面的 FlatXmlDataSet 定义(与附加的 SQL 模式进行比较)。

但是，如果我运行 testCreateAvatar() 测试用例，我会因为状态代码不匹配而出现异常，这是由失败的 sql 插入引起的，因为已经存在主键 ( id 字段)。看一下我的数据库，测试数据集的插入不会更新相应的 *avatars_id_seq* 和 *users_id_seq* 序列表，这些表用于生成 id 字段(postgresql 生成自动增量值的机制)。

这意味着，如果我在 FlatXmlDataSet 定义中定义静态 ID，则自动增量值不会更新。所以我的问题是如何更改此行为或自行设置自动增量值(使用 DbUnit)。

头像创建测试用例

@Test
public void testCreateAvatar() throws Exception {
    // Set up the request url.
    final HttpPost request = new HttpPost(
            "http://localhost:9095/rest/avatars");

    // Setup the JSON blob, ...
    JSONObject jsonAvatar = new JSONObject();
    jsonAvatar.put("imageUrl", "images/dussel.jpg");

    // ... add it to the post request ...
    StringEntity input = new StringEntity(jsonAvatar.toString());
    input.setContentType("application/json");
    request.setEntity(input);

    // ... and execute the request.
    final HttpResponse response = HttpClientBuilder.create().build()
            .execute(request);

    // Verify the result.
    assertThat(response.getStatusLine().getStatusCode(),
            equalTo(HttpStatus.SC_CREATED));

    // Fetch dussel duck from the database ...
    Avatar dussel = getServiceObjDao().queryForFirst(
                getServiceObjDao().queryBuilder().where()
         .eq("image_url", "images/dussel.jpg")
         .prepare());

    // ... and verify that the object was created correctly.
    assertThat(dussel, notNullValue());
    assertThat("images/dussel.jpg", equalTo(dussel.getImageUrl()));
}

DbUnit 数据集

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
   <!-- Avatars -->
   <avatars 
      id="1" 
      image_url="images/donald.jpg" />
   <avatars 
      id="2" 
      image_url="images/daisy.jpg" />

   <!-- Users -->
   <users 
      id = "1"
      name = "Donald Duck"
      email = "donald.duck@entenhausen.de"
      password = "quack" />
   <users 
      id = "2"
      name = "Daisy Duck"
      email = "daisy.duck@entenhausen.de"
      password = "flower" />
</dataset>

用户和头像表架构

CREATE TABLE avatars (
   id BIGSERIAL PRIMARY KEY,
   cdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   mdate TIMESTAMP,
   image_url VARCHAR(200),
   UNIQUE (image_url)
);

CREATE TABLE users (
   id BIGSERIAL PRIMARY KEY,
   cdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   mdate TIMESTAMP,
   name VARCHAR(160) NOT NULL,
   email VARCHAR (355) UNIQUE NOT NULL,
   password VARCHAR(30) NOT NULL,
   avatar_id BIGINT,
   UNIQUE (name),
   CONSTRAINT user_avatar_id FOREIGN KEY (avatar_id)
      REFERENCES avatars (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Answer 1

下面的函数查找数据库中的所有序列，从序列名中提取出对应表的名称，最后根据对应表中的最大id值更新序列的当前值。由于还没有更好的解决方案，这似乎是要走的路。希望这对某人有所帮助。

基于 harmic 的建议的简单解决方案

@Before
public void resetSequence() {
    Connection conn = null;
    try {
        // Establish a database connection.
        conn = DriverManager.getConnection(
                this.props.getProperty("database.jdbc.connectionURL"),
                this.props.getProperty("database.jdbc.username"), 
                this.props.getProperty("database.jdbc.password"));

        // Select all sequence names ...
        Statement seqStmt = conn.createStatement();
        ResultSet rs = seqStmt.executeQuery("SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';");

        // ... and update the sequence to match max(id)+1.
        while (rs.next()) {
            String sequence = rs.getString("relname");
            String table = sequence.substring(0, sequence.length()-7);
            Statement updStmt = conn.createStatement();
            updStmt.executeQuery("SELECT SETVAL('" + sequence + "', (SELECT MAX(id)+1 FROM '" + table + "'));");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            conn.close();
        } catch (SQLException e) {
        }
    }
}