问用于测试的Spring H2 db抛出“表未找到”和"SQL语法错误“。

EN

    alter table store_order_items 
       drop 
       foreign key FKikqpbj6xmmyoblsolyhk250tq" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlString(SchemaDropperImpl.java 
[...]
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "STORE_ORDER_ITEMS" not found (this database is empty); SQL statement:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "
    create table store_order_items (
       Id bigint not null auto_increment,
        quantity bigint,
        order_Id bigint,
        product_Id bigint,
        primary key (Id)
    ) engine=InnoDB" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:458) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
[...]
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "\000a    create table store_order_items (\000a       Id bigint not null auto_increment,\000a        quantity bigint,\000a        order_Id bigint,\000a        product_Id bigint,\000a        primary key (Id)\000a    ) engine[*]=InnoDB"; expected "identifier"; SQL statement:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;

spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.h2.console.enabled=true


logging.level.org.hibernate.SQL=DEBUG
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

spring.jpa.hibernate.ddl-auto=create
spring.jpa.generate-ddl=true
spring.jpa.defer-datasource-initialization=true

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

@ExtendWith(SpringExtension.class)
@SpringBootTest
@Transactional
public class UserRepositoryTests {

    private static final Logger LOGGER = Logger.getLogger(UserRepositoryTests.class.getName());

    @Autowired
    private UserRepositoryImpl userRepository;

    @Autowired
    private PasswordEncoder encoder;


    @Test
    void contextLoads() {
        System.out.println(encoder.encode("123"));
// dummy test just to test if tables are built properly
    }

@Entity
@Table(name = "store_order_items")
@NamedQueries({
        @NamedQuery(name=OrderItem.ORDER_ITEM_FIND_ALL, query=OrderItem.ORDER_ITEM_FIND_ALL_JPQL)
})
public class OrderItem {

    // get list of all orders
    public static final String ORDER_ITEM_FIND_ALL = "orderItemAll";
    public static final String ORDER_ITEM_FIND_ALL_JPQL = "SELECT oi FROM OrderItem oi";


    // id will be replaced with two foreign keys
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long Id;

    private Long quantity;

    @JsonBackReference
    @ManyToOne
    private Order order;

    @JsonBackReference
    @ManyToOne
    private Product product;

// getters and setters omitted
}

@Entity
@Table(name = "store_orders")
@NamedQueries({
        @NamedQuery(name=Order.ORDER_FIND_ALL, query=Order.ORDER_FIND_ALL_JPQL),
        @NamedQuery(name=Order.ORDER_SUMMARIES, query=Order.ORDER_SUMMARIES_JPQL)
})
public class Order {

// named queries omitted

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long Id;

    private String orderDate;

    @JsonBackReference
    @ManyToOne
    private User user;

    @JsonManagedReference
    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<OrderItem> orderItems = new ArrayList<>();

// getters and setters omitted
}