I am trying to test a JDBC client with an in-memory database, so I can test a variety of cases with unit tests.

Here is what I have so far:

Model class (also contains constructors)

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

I have a client class which I want to use to interact with postgres, using java.sql DriverManager and PreparedStatements.

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);
        }
    }
    
}

I have my unit test which I want to run. I expect it to connect to the in-memory database via the client, on test set up, create the database, and then test my insert method.

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);
    }
}

What I expect to happen

The set up method creates the table. My test inserts into the table

What actually happens

Logging shows the set up is ran. The test is ran, but the insert fails because the table does not exist apparently.

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

My application.properties in the test directory looks like:

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

CodePudding user response：

You are missing a \n in your comment for the SQL. Now the whole SQL statement is interpreted as a comment and thus nothing will be executed.

String sql = "-- template\n" 
            "CREATE TABLE my_model_template (" 
            "    uuid UUID NOT NULL," 
            ...  
            ");";

something along those lines, or if you are on a newer version of Java you might even be able to use text blocks.

String sql = """-- template
          CREATE TABLE my_model_template (
              uuid UUID NOT NULL," 
          ... 
          );""";

This would work from Java 15 (with experimental features enabled) or Java 17 or up as well.

CodePudding user response：

This happens when you use either a wrong dialect inside your application.properties file, or you validate against the wrong database. For example, when you run your application against a local H2 database, the best choice would be to remove the dialect, since Hibernate can recognize the database without this property (if the Version of Hibernate is new enough to recognize newer databases). Another solution would be to remove the validate attribute, but I would not recommend that, since you have no database checks at startup then:

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