h2-browser doesn't show my tables

[JonPichel]

Hi,

I'm having trouble using H2 in-memory database with Hibernate and Play Java version 2.8.17. I can persist my objects and perform queries without problems, but when I try to run H2-browser it connects to my database but doesn't show my tables or created rows.

I have also tried with IntelliJ Idea's datasources and it also connects but it doesn't display the information from my application.

I don't know what I'm doing wrong, can somebody help me?

Thanks in advance

[mkurz]

Are you connected to the correct db?

[JonPichel]

This is my persistence.xml:

`

<persistence-unit name="defaultPersistenceUnit" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

    <class>models.User</class>
    <class>models.Sighting</class>
    <class>models.Confirmation</class>
    <properties>
        <property name="javax.persistence.jdbc.driver" value="org.h2.Driver" />
        <property name="javax.persistence.jdbc.url"    value="jdbc:h2:mem:play;MODE=MYSQL" />
        <property name="javax.persistence.jdbc.user" value="sa" />
        <property name="javax.persistence.jdbc.password" value="" />

        <property name="hibernate.dialect" value="org.hibernate.dialect.MariaDBDialect"/>
        <property name="hibernate.hbm2ddl.auto" value="create-drop" />
        <property name="show_sql" value="true"/>
        <property name="hibernate.temp.use_jdbc_metadata_defaults" value="false"/>
    </properties>
</persistence-unit>

`

And when I connect I've tried both with "jdbc:h2:mem:play;MODE=MYSQL" and "jdbc:h2:mem:play".

This is my application.conf:

`db {
default.driver=org.h2.Driver
default.url="jdbc:h2:mem:play;MODE=MYSQL"
default.username="sa"
default.password=""
default.jndiName=DefaultDS
}

jpa.default=defaultPersistenceUnit

fixedConnectionPool = 9

database.dispatcher {
executor = "thread-pool-executor"
throughput = 1
thread-pool-executor {
fixed-pool-size = ${fixedConnectionPool}
}
}`

I think I am, but this is my first time using H2 so I'm probably not getting something.

[Squeng]

You're probably connecting to/using two different memory-based DBs (each process with its own). It may be easier to start with a file-based one.

[JonPichel]

How could I access the database from the Play Process? I'm running the application using the default configuration from IntelliJ. I've tried to use sbt shell and first run the application using "run" and then run h2-browser but it says it is busy so I can't.

EDIT: Ok I've run "sbt h2-browser shell" and then inside the shell "run" and I was finally able to connect to the application database. How could I integrate this into the Play2Run configuration?

[Squeng]

It's been a while since I last used h2 (or any relational DB for that matter). Here's the conf from an old project:

https://www.playframework.com/documentation/latest/JavaDatabase

https://www.playframework.com/documentation/latest/JavaJPA

see also https://github.com/playframework/play-java-jpa-example

db.default.driver=org.h2.Driver
db.default.driver=${?DB_DRIVER}
db.default.url="jdbc:h2:~/FiLe-App/FiLeDB;FILE_LOCK=NO;USER=sa;PASSWORD=123;MODE=MSSQLServer"
db.default.url=${?DB_URL}
db.default.username=${?DB_USERNAME}
db.default.password=${?DB_PASSWORD}
db.default.jndiName=DefaultDS

Maybe FILE_LOCK=NO did the trick.

I hope this helps!

[jducoeur]

Hmm. FILE_LOCK definitely wasn't necessary the last time I worked with this stuff -- I think that's only if you're in file mode, not memory mode.

My H2 test setup code is here. That's configuring in code instead; the relevant code is just

    val context = ApplicationLoader.createContext(
      Environment.simple(),
      Map(
        // Flag to allow system code to check whether we are in this mode:
        "querki.test.inmemory" -> "true",
        // For these tests, use the in-memory H2 SQL DB:
        "db.system.driver" -> "org.h2.Driver",
        "db.system.url" -> "jdbc:h2:mem:system;MODE=MYSQL",
        "db.user.driver" -> "org.h2.Driver",
        "db.user.url" -> "jdbc:h2:mem:user;MODE=MYSQL"
      )
    )

Different question: @JonPichel, are you sure that you're creating your tables? A common mistake when using H2 is neglecting this. Since H2 knows nothing about your persistent on-disk database schema, you have to build up the entire schema in memory every time you run the tests. So for example, each of my tests runs this setupDatabase() function before starting, so that the database exists to write into.