java - Wix 嵌入式 mysql 与 spring boot DDL 查询错误对于具有嵌入式 id 的实体

Question

已解决配置错误我想在mysql中测试我的spring boot应用程序的测试方法。

因此，我添加了 wix-embedded-mysql 并配置了嵌入式数据库，但是当我运行测试时，它无法从我的实体对象之一创建表，并出现以下错误:

create table user_tickets (date_time datetime(6) not null, ticket_id BIGINT UNSIGNED not null, user_id INT not null, created_date date, created_time time, modified_date date, modified_time time, version bigint, body varchar(1000), type varchar(255), created_by_id INT, modified_by_id INT, primary key (date_time, ticket_id, user_id)) engine=InnoDB
Hibernate: create table user_tickets (date_time datetime(6) not null, ticket_id BIGINT UNSIGNED not null, user_id INT not null, created_date date, created_time time, modified_date date, modified_time time, version bigint, body varchar(1000), type varchar(255), created_by_id INT, modified_by_id INT, primary key (date_time, ticket_id, user_id)) engine=InnoDB
2019-04-20 03:22:37.080  WARN 2186 --- [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:424) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:315) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:155) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:312) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1758) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1695) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:573) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:495) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1089) ~[spring-context-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:859) ~[spring-context-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550) ~[spring-context-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:780) ~[spring-boot-2.0.5.RELEASE.jar:2.0.5.RELEASE]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:412) ~[spring-boot-2.0.5.RELEASE.jar:2.0.5.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:333) ~[spring-boot-2.0.5.RELEASE.jar:2.0.5.RELEASE]
    at org.springframework.boot.test.context.SpringBootContextLoader.loadContext(SpringBootContextLoader.java:139) ~[spring-boot-test-2.0.5.RELEASE.jar:2.0.5.RELEASE]
    at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:99) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:117) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.test.context.support.DefaultTestContext.getApplicationContext(DefaultTestContext.java:108) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.test.context.web.ServletTestExecutionListener.setUpRequestContextIfNecessary(ServletTestExecutionListener.java:190) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.test.context.web.ServletTestExecutionListener.prepareTestInstance(ServletTestExecutionListener.java:132) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.test.context.TestContextManager.prepareTestInstance(TestContextManager.java:246) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.createTest(SpringJUnit4ClassRunner.java:227) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner$1.runReflectiveCall(SpringJUnit4ClassRunner.java:289) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) ~[junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.methodBlock(SpringJUnit4ClassRunner.java:291) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:246) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) ~[junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) ~[junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) ~[junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) ~[junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) ~[junit-4.12.jar:4.12]
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) ~[junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363) ~[junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137) ~[junit-4.12.jar:4.12]
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68) ~[junit-rt.jar:na]
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47) ~[junit-rt.jar:na]
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242) ~[junit-rt.jar:na]
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) ~[junit-rt.jar:na]
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE USER_TICKETS (DATE_TIME DATETIME([*]6) NOT NULL, TICKET_ID BIGINT UNSIGNED NOT NULL, USER_ID INT NOT NULL, CREATED_DATE DATE, CREATED_TIME TIME, MODIFIED_DATE DATE, MODIFIED_TIME TIME, VERSION BIGINT, BODY VARCHAR(1000), TYPE VARCHAR(255), CREATED_BY_ID INT, MODIFIED_BY_ID INT, PRIMARY KEY (DATE_TIME, TICKET_ID, USER_ID)) ENGINE=INNODB "; expected "FOR, UNSIGNED, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:
create table user_tickets (date_time datetime(6) not null, ticket_id BIGINT UNSIGNED not null, user_id INT not null, created_date date, created_time time, modified_date date, modified_time time, version bigint, body varchar(1000), type varchar(255), created_by_id INT, modified_by_id INT, primary key (date_time, ticket_id, user_id)) engine=InnoDB [42001-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.message.DbException.getSyntaxError(DbException.java:217) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.command.Parser.getSyntaxError(Parser.java:555) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.command.Parser.read(Parser.java:3518) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.command.Parser.readIfMore(Parser.java:950) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.command.Parser.parseCreateTable(Parser.java:6571) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.command.Parser.parseCreate(Parser.java:4615) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.command.Parser.parsePrepared(Parser.java:380) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.command.Parser.parse(Parser.java:335) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.command.Parser.parse(Parser.java:311) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.command.Parser.prepareCommand(Parser.java:278) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.engine.Session.prepareLocal(Session.java:611) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.engine.Session.prepareCommand(Session.java:549) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205) ~[h2-1.4.197.jar:1.4.197]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-2.7.9.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-2.7.9.jar:na]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    ... 58 common frames omitted

这就是我在 POM 中添加依赖项的方法:

<dependency>
            <groupId>com.wix</groupId>
            <artifactId>wix-embedded-mysql</artifactId>
            <version>4.2.0</version>
            <scope>test</scope>
</dependency>

并配置嵌入式数据库如下:

MysqldConfig config = MysqldConfig.aMysqldConfig(v5_7_19)
                    .withPort(3397)
                .withTimeZone(TimeZone.getTimeZone(ZoneId.of("UTC")))
                .withUser("test", "test")
                .build();



SchemaConfig schemaConfig = SchemaConfig.aSchemaConfig("test_database")
                .build();

embeddedMysql = EmbeddedMysql.anEmbeddedMysql(config)
                .addSchema(schemaConfig)
                .start();

我的实体定义如下:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "user_tickets")
public class UserTicket extends BaseModel implements BaseModelId<UserTicketId> {
    @EmbeddedId
    private UserTicketId id;

    @NotEmpty(groups = Group.class, message = "{validation.notnull}")
    @Column(length = 1000)
    private String body;

    @Enumerated(EnumType.STRING)
    private UserTicketType type;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @MapsId("userId")
    @JoinColumn(name = "user_id", columnDefinition = "INT", foreignKey =
    @ForeignKey(foreignKeyDefinition = "FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE"))
    private User user;

    @Valid
    @NotNull(groups = Group.class, message = "{validation.notnull}")
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @MapsId("ticketId")
    @JoinColumn(name = "ticket_id", columnDefinition = "BIGINT UNSIGNED", foreignKey =
    @ForeignKey(foreignKeyDefinition = "FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE"))
    private Ticket ticket;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "userTicket", orphanRemoval = true)
    private Set<UserTicketAttachment> userTicketAttachments;
}

最后是嵌入的 id:

@Getter
@Setter
@ToString(of = {"userId", "ticketId", "dateTime"})
@EqualsAndHashCode(of = {"userId", "ticketId", "dateTime"})
@NoArgsConstructor
@AllArgsConstructor
@Embeddable
public class UserTicketId implements Serializable {
    @Column(name = "user_id")
    private Long userId;
    @Column(name = "ticket_id")
    private Long ticketId;
    @Column(name = "date_time")
    private LocalDateTime dateTime;
}

所有其他实体，甚至具有嵌入式 ID 的实体，都已在嵌入式数据库中成功创建。

我什至在嵌入式数据库中手动复制并粘贴了生成的表查询，它起作用了!

我还需要提及的是，该设置对于真实数据库来说工作正常。

这是我的 application.properties 数据源定义:

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3397/test_database?autoReconnect=true&verifyServerCertificate=false&useSSL=false&requireSSL=false
spring.datasource.username=test
spring.datasource.password=test
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop 

Answer 1

这是一个配置错误。

我想使用嵌入式 MySQL 来测试一些特定的测试。但由于 mysql 测试速度缓慢，我希望其他测试在内存中的 H2 下运行。

所以我创建了一个配置 bean，如下所示:

@Configuration
@EnableJpaRepositories(basePackages = "com.package.repository",
        repositoryFactoryBeanClass = EntityGraphJpaRepositoryFactoryBean.class)
@PropertySource("classpath:application-embeddedmysqldb.properties")
@EnableTransactionManagement
public class EmbeddedMysqlConfig {

}

但是我忘记添加引用实际wix嵌入式数据库的数据源。

结果数据库仍然是H2，但是mysql配置困惑。

我添加了如下数据源:

 @Bean
 @ConfigurationProperties(prefix = "spring.datasource")
 public DataSource dataSource() {
     return DataSourceBuilder.create().build();
 }

它解决了这个问题。