Payara Micro ignoring connection url in persistence.xml

user_4601149 0
Viewed 705

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
payara-micro java-8 h2 java
2 Answers

The problem is that you're using transaction-type="JTA", which means that the datasource is retrieved from Payara Micro and not created by Hibernate based on the properties you specified.

You should change transaction-type="JTA" to transaction-type="RESOURCE_LOCAL". That should fix it and behave as you expect.

What's happening now is that Hibernate asks for a datasource from Payara Micro and ignores the hibernate.connection properties. You didn't specify any datasource JNDI name (e.g. with the hibernate.connection.datasource), therefore it will retrieve the default datasource. The default datasource in Payara Micro is for an H2 database stored in a temporary directory.

user_7845940

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.

user_46011490
Related