I am writing a Payara Micro web application that should connect to a database as defined in persistence.xml. The database it should connect to is a file on my local machine and at the time of project startup, it does not exist. To my understanding, H2 should create a file for the database on first connect.
The persistence.xml looks like:
<?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="com.danjbower_h2test_war_0.0.1PU"
transaction-type="JTA">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<class>com.danjbower.h2test.Test</class>
<properties>
<property name="hibernate.connection.driver_class" value="org.h2.Driver" />
<property name="hibernate.connection.url" value="jdbc:h2:file:C:/databases/testdb" />
<property name="hibernate.connection.username" value="sa" />
<property name="hibernate.connection.password" value="" />
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.format_sql" value="true" />
<property name="hibernate.hbm2ddl.auto" value="update" />
<property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />
<property name="hibernate.temp.use_jdbc_metadata_defaults" value="false" />
<property name="hibernate.transaction.jta.platform" value="SunOne" />
</properties>
</persistence-unit>
</persistence>
When the project is running it connects to a database and runs sql commands to create tables and add a specific entity I want adding defined in a startup bean. However, when I check my system it has not created a C:\databases\testdb.mv.db file as I would have expected. What is going wrong and how can I make it so it actually generates the database file rather than seemingly ignoring the configuration?
Additional files for MCVE
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.danjbower</groupId>
<artifactId>H2Test</artifactId>
<version>0.0.1</version>
<packaging>war</packaging>
<name>H2Test</name>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<failOnMissingWebXml>false</failOnMissingWebXml>
<version.payara>5.201</version.payara>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>fish.payara.api</groupId>
<artifactId>payara-bom</artifactId>
<version>${version.payara}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>jakarta.platform</groupId>
<artifactId>jakarta.jakartaee-web-api</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>jakarta.enterprise.concurrent</groupId>
<artifactId>jakarta.enterprise.concurrent-api</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>jakarta.resource</groupId>
<artifactId>jakarta.resource-api</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>jakarta.batch</groupId>
<artifactId>jakarta.batch-api</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.eclipse.microprofile</groupId>
<artifactId>microprofile</artifactId>
<scope>provided</scope>
<type>pom</type>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.4.25.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>5.4.25.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate.javax.persistence</groupId>
<artifactId>hibernate-jpa-2.1-api</artifactId>
<version>1.0.2.Final</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>fish.payara.maven.plugins</groupId>
<artifactId>payara-micro-maven-plugin</artifactId>
<version>1.0.6</version>
<configuration>
<payaraVersion>${version.payara}</payaraVersion>
<deployWar>false</deployWar>
<commandLineOptions>
<option>
<key>--autoBindHttp</key>
</option>
<option>
<key>--deploy</key>
<value>${project.build.directory}/${project.build.finalName}</value>
</option>
</commandLineOptions>
<contextRoot>/H2Test</contextRoot>
</configuration>
</plugin>
</plugins>
</build>
<repositories>
<repository>
<id>payara-patched-externals</id>
<name>Payara Patched Externals</name>
<url>https://raw.github.com/payara/Payara_PatchedProjects/master</url>
<releases>
<enabled>true</enabled>
</releases>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
</repositories>
</project>
Test.java
package com.danjbower.h2test;
import java.util.Objects;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.xml.bind.annotation.XmlRootElement;
@Entity
@Table(name = "Tests")
@XmlRootElement
public class Test
{
@Id
@Column(name = "Id", unique = true)
@Basic(optional = false)
@NotNull
private Integer id = 0;
public Integer getId()
{
return id;
}
public void setId(Integer id)
{
this.id = id;
}
@Override
public int hashCode()
{
return id;
}
@Override
public boolean equals(Object obj)
{
if (this == obj)
{
return true;
}
if (obj == null)
{
return false;
}
if (getClass() != obj.getClass())
{
return false;
}
final Test other = (Test) obj;
return Objects.equals(id, other.id);
}
@Override
public String toString()
{
return "Test (Id: " + id + ")";
}
}
StartUp.java
package com.danjbower.h2test;
import javax.annotation.PostConstruct;
import javax.ejb.Singleton;
import javax.ejb.Startup;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
@Startup
@Singleton
public class StartUp
{
@PersistenceContext(unitName = "com.danjbower_h2test_war_0.0.1PU")
private EntityManager entityManager;
@PostConstruct
@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void init()
{
Test test = new Test();
test.setId(4);
entityManager.persist(test);
}
}
Log showing it running commands
Hibernate:
create table Tests (
Id integer not null,
primary key (Id)
)
Hibernate:
insert
into
Tests
(Id)
values
(?)
[2021-03-07T03:37:03.511+0000] [] [INFO] [] [PayaraMicro] [tid: _ThreadID=1 _ThreadName=main] [timeMillis: 1615088223511] [levelValue: 800]
{
"Instance Configuration": {
"Host": "192.168.1.12",
"Http Port(s)": "8080",
"Https Port(s)": "",
"Instance Name": "Hilarious-Boxfish",
"Instance Group": "MicroShoal",
"Hazelcast Member UUID": "ae8dbc3a-b361-4dd6-bbba-762e620f96a8",
"Deployed": [
{
"Name": "H2Test-0.0.1",
"Type": "war",
"Context Root": "/H2Test"
}
]
}
}
[2021-03-07T03:37:03.513+0000] [] [INFO] [] [PayaraMicro] [tid: _ThreadID=1 _ThreadName=main] [timeMillis: 1615088223513] [levelValue: 800]
Payara Micro URLs:
http://192.168.1.12:8080/H2Test
A working example of the project can be found here github.com/DanJBower/H2Test.
One solution to this problem is to set up a jta-data-source instead of using hibernate.connection.* properties.
This meant changing persistence.xml to
<?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="com.danjbower_h2test_war_0.0.1PU"
transaction-type="JTA">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<jta-data-source>java:app/TestDb</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.format_sql" value="true" />
<property name="hibernate.hbm2ddl.auto" value="update" />
<property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />
<property name="hibernate.transaction.jta.platform" value="SunOne" />
</properties>
</persistence-unit>
</persistence>
and adding a file called payara-resources.xml. This is where the database connection is set up.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//Payara.fish//DTD Payara Server 4 Resource Definitions//EN" "https://raw.githubusercontent.com/payara/Payara-Community-Documentation/master/docs/modules/ROOT/pages/schemas/payara-resources_1_6.dtd">
<resources>
<jdbc-resource pool-name="TestDb"
jndi-name="java:app/TestDb"
enabled="true" />
<jdbc-connection-pool datasource-classname="org.h2.jdbcx.JdbcDataSource"
name="TestDb"
res-type="javax.sql.DataSource">
<property name="URL" value="jdbc:h2:file:C:/databases/TestDb" />
<property name="User" value="sa" />
<property name="Password" value="" />
</jdbc-connection-pool>
</resources>
Note
Originally I put this in web.xml as
<data-source>
<name>java:app/TestDb</name>
<class-name>org.h2.Driver</class-name>
<url>jdbc:h2:file:C:/databases/TestDb</url>
<user>sa</user>
<password></password>
</data-source>
However, when doing this I came across an error where it said there was no password set. Very similar to this issue.