Hikari pool for H2/Hibernate JPA on Jetty - SQL errors TABLE does not exist

Question

I'm using Hikari for pooling H2 memory connections in a Jetty 9 server for a webapp.

Works great, until I go away for a few hours and leave it idle. When I come back all of my database queries tell me the the table does not exist when I reload the page. I'm not sure where this is going wrong, I'm assuming the connection is still good since it's trying to run the query, but strange error to get.

Here's my setup:

jetty-env.xml:

<Configure id="wac" class="org.eclipse.jetty.webapp.WebAppContext">
    <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
        <Arg>jdbc/myds</Arg>
        <Arg>
            <New class="com.zaxxer.hikari.HikariDataSource">
                <Arg>
                    <New class="com.zaxxer.hikari.HikariConfig">
                        <Set type="int" name="maximumPoolSize">1</Set>
                        <Set name="dataSourceClassName">
                            org.h2.jdbcx.JdbcDataSource-
                        </Set>
                        <Call name="addDataSourceProperty">
                            <Arg>url</Arg>
                            <Arg>jdbc:h2:mem:test_mem</Arg>
                        </Call>
                    </New>
                </Arg>
            </New>
        </Arg>
    </New>
</Configure>

persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
 http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="MyJPAJAXRS" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <non-jta-data-source>jdbc/myds</non-jta-data-source>
    <class>net.b.Configuration</class>
    <class>net.b.Form</class>
    <class>net.b.FormValue</class>
    <class>net.b.Translation</class>
    <properties>
      <property name="javax.persistence.schema-generation.database.action"
                value="drop-and-create"/>
      <property name="javax.persistence.sql-load-script-source" value="META-INF/seed.sql"/>
    </properties>
  </persistence-unit>
</persistence>


<!--<property name="javax.persistence.jdbc.driver" value="org.h2.Driver"/>-->
<!--<property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:test_mem"/>-->
<!--<property name="javax.persistence.schema-generation.create-source"-->
<!--value="metadata-then-script"/>-->

Sample Query:

TypedQuery<String> query = em.createQuery(
                    "SELECT t.value FROM Translation t WHERE t.key = :key", String.class);
 query.setParameter("key", key);
 result = query.getSingleResult();

This REST endpoint will work 100 times, but after some idle time I get exception that table "TRANSLATION" does not exist. What gives?

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

Your answer can be found here here. You need to use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1, otherwise the database and tables will disappear as soon as the last connection is closed.

edited May 23 '17 at 10:30

Community

1
1

answered Mar 12 '17 at 14:23

brettw

10,664
2
42
59

Hikari pool for H2/Hibernate JPA on Jetty - SQL errors TABLE does not exist

1 Answers1