Autogenerate tables in PostgreSQL database from defined Entities

Question

I have been using Apache Derby as a database for a school project, but I want to make the switch to PostgreSQL for my new personal project since, as I have understood it, it's a lot better in a production environment. I'm creating trying to create a RESTAPI with Jersey, running on a Paraya server.

In my last project, using the Apache Derby DB, all the tables got created automatically from the entities on deployment. However, I am not able to get this to work with the PostgreSQL database. Is this not possible or am I doing something wrong? If I'm doing something incorrectly, I would guess the fault is in the persistance.xml file (maybe in the glassfish-resources.xml file).

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0" xmlns="http://java.sun.com/xml/ns/persistence">
    <persistence-unit name="WikiDB" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <properties>
            <property name="javax.persistence.target-database" value="PostgreSQL" />
            <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/wiki"/>
            <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
            <property name="javax.persistence.jdbc.user" value="john.blaberg"/>
            <property name="javax.persistence.jdbc.password" value=""/>
            <property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>
            <property name="eclipselink.ddl-generation.output-mode" value="database"/>
        </properties>
    </persistence-unit>
</persistence>

glassfish-resources.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//Payara.fish//DTD Payara Server 5 Resource Definitions//EN"
        "https://raw.githubusercontent.com/payara/Payara-Server-Documentation/master/schemas/payara-resources_1_6.dtd">
<resources>
    <jdbc-connection-pool datasource-classname="org.postgresql.ds.PGConnectionPoolDataSource"
                          res-type="javax.sql.ConnectionPoolDataSource">
        <property name="serverName" value="localhost"/>
        <property name="portNumber" value="5432"/>
        <property name="databaseName" value="wiki"/>
        <property name="Account Name" value="test_username"/>
        <property name="Password" value="test_password"/>
        <property name="driverClass" value="org.postgresql.Driver"/>
    </jdbc-connection-pool>
    <jdbc-resource enabled="true" jndi-name="jdbc/wiki"
                   object-type="user" pool-name="PostPool"/>
</resources>

And my entity:

@Data
@Entity
@NoArgsConstructor
public class Brand {
    @Id @GeneratedValue
    private Long id;
    private String brandName;

    public Brand(String brandName){
        this.brandName = brandName;
    }
}

I get no error messages when I'm deploying the application.

You did not narrow down your question to a specific topic. Instead, many questions were made. I guess you've figured the correct jpa descriptor file is persistence.xml (not persistAnce). About the transaction, you should annotate your relevant methods with @Transactional. Consider edit the question to your main problem first. — Paulo Araújo, Apr 03 '20 at 02:55
My main problem is that no tables are created in the schema, but I'm not really sure why. That's why I included so much information in the question. Do you think it has something to do with the persistence.xml file, I will edit down the question to just that. — BaraKanske, Apr 03 '20 at 06:55
so, you want the app itself will create db/schema/entities if they are not created before the app is deployed? have you looked at this @BaraKanske? https://stackoverflow.com/questions/31319038/database-creation-not-working-in-eclipselink-with-glassfish-4-and-postgres — , Apr 03 '20 at 10:36
Yes exactly! I want the app to generate tables base on my entities. I have been able to do this with Apache Derby, but not after the switch to PostgreSQL. I looked at the link, but unfortunately, that did not work. — BaraKanske, Apr 03 '20 at 13:42

score 0 · Answer 1 · answered Jul 09 '21 at 12:48

if you want the app itself will create db/schema/entities

add <property name="hibernate.hbm2ddl.auto" value="create-drop" /> to

basically it will generate db/schema/entities by its own and if already exists it will drop them first & then create

By the way, you mentioned persistance.xml over persistence.xml (one of the common mistakes)

Postgresql persistence.xml template :

<?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="myApp" transaction-type="RESOURCE_LOCAL">

    <properties>

        <!-- DB Driver -->
        <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/> 

        <!-- DB Name -->
        <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/test"/> 
        <!-- DB User -->
        <property name="javax.persistence.jdbc.user" value="user" /> 
        <!-- DB Password -->
        <property name="javax.persistence.jdbc.password" value="1234" /> 


        <!-- Show SQL in console -->
        <property name="hibernate.show_sql" value="true" /> 
         <!-- Show SQL formatted -->
        <property name="hibernate.format_sql" value="true" />
        <!-- DB Dialect -->
        <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" /> 
        <!-- create / create-drop / update -->
        <property name="hibernate.hbm2ddl.auto" value="create-drop" /> 
    </properties>

</persistence-unit>

Autogenerate tables in PostgreSQL database from defined Entities

1 Answers1