内存数据库jdbcsqlsyntaxerrorexception中的Java h2：未找到表“table_name”

Question

我试图用内存中的数据库来测试JDBC客户机，这样我就可以用单元测试来测试各种情况。

以下是我目前掌握的信息：

@Entity
@Table(name = "table_name")
public class MyModel {

public class MyModelClient {

    private final Connection con;

    public MyModelClient(String url, String user, String password) {
        try {
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            throw new RuntimeException("uh oh", e);
        }
    }

    public Connection getCon() {
        return con;
    }

    private static String ROWS = "\"uuid\", ...";

    private String insertMyModelQuery() {
        return "INSERT INTO table_name (" + MyModelClient.ROWS + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" +
        "ON CONFLICT(\"uuid\") DO UPDATE SET url=EXCLUDED.url RETURNING uuid;";
    }

    public void insertMyModel(MyModel article) {
        try (PreparedStatement preparedStatement = con.prepareStatement(this.insertMyModelQuery())) {
            preparedStatement.setObject(1, article.uuid);
            ...
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new InsertMyModelException("failed to insert MyModel: " + e.toString(), e);
        }
    }
    
}

public class MyModelClientTest {

    static MyModelClient client;
    
    @BeforeAll
    public static void setUp() {
        client = new ArticleClient("jdbc:h2:mem:testdb;DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE", "postgres", "mysecretpassword");
         String sql = "-- template"+
            "CREATE TABLE my_model_template ("+
            "    uuid UUID NOT NULL,"+
            ... +
            ");";
        String sql2 = "-- Create table"+
            "CREATE TABLE table_name"+
            "    (LIKE my_model_template)"+
            "    PARTITION BY RANGE (created_datetime);";
            
        try {
            Statement s=client.getCon().createStatement();
            s.execute(sql);
            s.execute(sql2);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void shouldInsertMyModel() {
        System.out.println("TEST---------------------");
        MyModel m = TestMyModelBuilder.build();
        client.insertMyModel(m);
    }
}

failed to insert MyModel: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "table_name" not found;

测试目录中的Application.Properties如下所示：

spring.datasource.url=jdbc:h2:mem:testdb;DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=postgres
spring.datasource.password=mysecretpassword
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.defer-datasource-initialization=true

Answer 1

当您在application.properties文件中使用错误的方言，或者针对错误的数据库进行验证时，就会发生这种情况。例如，当您针对本地H2数据库运行应用程序时，最好的选择是删除方言，因为Hibernate可以在没有此属性的情况下识别数据库（如果Hibernate的版本足够新，可以识别较新的数据库）。另一个解决方案是删除validate属性，但我不建议这样做，因为在启动时没有数据库检查：

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=update