在内存数据库中使用H2进行的春季测试将截断所有表 [英] Spring test with H2 in memory database truncate all tables

问题描述

我是一名使用Spring从事MVC项目的初中CS专业学生，对Spring来说我还很陌生.

I'm a junior CS major student working on an MVC project using spring and I am quite new to spring.

我在内存db中设置了H2并编写了2个sql脚本；一个用于填充数据库，另一个用于将其清空.

I set up an H2 in memory db and wrote 2 sql scripts; one to populate the db and another one to flush it empty.

据我所知，truncate table <table name>和delete from <table name>之间的实际区别是截断表为ddl，因此其速度更快，并且还重置了自动递增的列.问题是H2的截断表不会重置自动递增的列.你能指出我在搞砸吗?

As far as I know, the practical difference between truncate table <table name> and delete from <table name> is truncate table is ddl thus its faster and it also resets the auto incremented columns. The problem is H2's truncate table does not reset auto incremented columns. Could you point out where I'm messing up?

我正在使用spring的SqlGroup批注来运行脚本.

I'm using spring's SqlGroup annotation to run the scripts.

@SqlGroup({
    @Sql(executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD, scripts = 
                                                "classpath:populate.sql"),
    @Sql(executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD, scripts = 
                                                  "classpath:cleanup.sql")
})

这是应该用来截断每个表的cleanup.sql.

Here's the cleanup.sql that is supposed to truncate every table.

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE `criteria`;
TRUNCATE TABLE `job_title`;
TRUNCATE TABLE `organization`;
TRUNCATE TABLE `organization_teams`;
TRUNCATE TABLE `organization_users`;
TRUNCATE TABLE `organization_job_titles`;
TRUNCATE TABLE `review`;
TRUNCATE TABLE `review_evaluation`;
TRUNCATE TABLE `team`;
TRUNCATE TABLE `team_members`;
TRUNCATE TABLE `user`;
TRUNCATE TABLE `user_criteria_list`;

SET FOREIGN_KEY_CHECKS=1;

populate.sql只需在这些表中插入一堆行，而不会违反完整性约束.

And populate.sql simply inserts a bunch of rows to those tables without violating integrity constraints.

因此，当我运行测试类时，只有第一个方法通过.剩下的我会得到这样的东西:

So when i run my test class, only the first method passes. For the rest i get stuff like this:

违反参照完整性约束:"FK3MAB5XYC980PSHDJ3JJ6XNMMT:PUBLIC.ORGANIZATION_JOB_TITLES外键(JOB_TITLES_ID)参考PUBLIC.JOB_TITLE(ID)(1)"； SQL语句: 插入organization_job_titles(organization_id，job_titles_id)VALUES(1，1)

Referential integrity constraint violation: "FK3MAB5XYC980PSHDJ3JJ6XNMMT: PUBLIC.ORGANIZATION_JOB_TITLES FOREIGN KEY(JOB_TITLES_ID) REFERENCES PUBLIC.JOB_TITLE(ID) (1)"; SQL statement: INSERT INTO organization_job_titles(organization_id, job_titles_id) VALUES(1, 1)

我认为问题出在自动增量列未重置.所以我写了另外一个测试课:

I thought the problem arose from auto incremented columns not being reset. So i written a different test class:

monAutoIncTest.java

monAutoIncTest.java

 @Sql(executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD, scripts =
    "classpath:truncateTable.sql")
    public class monAutoIncTest {
        @Autowired
        OrganizationService organizationService;

        @Test
            public void h2truncate_pls() throws BaseException {
            organizationService.add(new Organization("Doogle"));
            Organization fetched = organizationService.getByName("Doogle");
            Assert.assertEquals(1, fetched.getId());
        }

        @Test
        public void h2truncate_plz() throws BaseException {
            organizationService.add(new Organization("Zoogle"));
            Organization fetched = organizationService.getByName("Zoogle");
            Assert.assertEquals(1, fetched.getId());
        }
    } 

使用truncateTable.sql

With truncateTable.sql

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE `organization`;

SET FOREIGN_KEY_CHECKS=1;

运行测试时，首先运行的那个方法通过，另一个运行给我.

When the test runs, whichever method that is run first passes, and the other gives me this.

java.lang.AssertionError: 预期:1 实际:2

java.lang.AssertionError: Expected :1 Actual :2

推荐答案

目前H2不支持该功能，但是我们可以在

For now H2 does not support that feature, but we can see such plans in their roadmap

TRUNCATE应该像在MySQL和MS SQL中一样重置标识列 服务器(可能还有其他数据库).

TRUNCATE should reset the identity columns as in MySQL and MS SQL Server (and possibly other databases).

尝试将此语句与TRUNCATE一起使用:

Try to use this statement in composition with TRUNCATE:

ALTER TABLE [table] ALTER COLUMN [column] RESTART WITH [initial value]

这篇关于在内存数据库中使用H2进行的春季测试将截断所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！