Having problems setting up Jakarta EE on Netbeans using GlassFish and Derby

I know it's pretty outdated technology but I found myself in the need of doing a project using Jakarta EE on GlassFish. I already know the answer, I just want to document my workaround for making this combination of technologies work together, since the information to fix all the problems that may arise is scattered through many different posts and websites.

First I will list the issues and then I will reply with the solutions:

1. First problem I encountered is that IntelliJ asks you to pay a subscription in order to use Jakarta EE functionality and Eclipse IDE has archived their plugins for GlassFish, so I ended up using Netbeans because it's the only IDE that still supports GlassFish apparently.

2. Then, after downloading Apache Derby (10.16.1.1) for setting up the database server I was receiving this exception:

An error occurred while creating the database:
java.lang.ClassNotFoundException: org.apache.derby.jdbc.Clientdriver

3. After solving the missing class issue, every commit I tried to do to the database was being set to rollback, being unable to persist anything into the database. This is the exception I was getting:

javax.persistence.RollbackException: Transaction "rolled back" because transaction was set to RollbackOnly.

4. Finally, when the transaction was finally not getting rolled back, I was getting an error because apparently the table I was trying to write into didn't exist. Even though I could clearly see it in the Derby server and the persistence.xml file pointed to it correctly:

java.sql.SQLSyntaxErrorException: Table/View 'CUSTOMER' does not exist.

Here's the workaround that I used to solve all these issues:

> 2. Then, after downloading Apache Derby (10.16.1.1) for setting up the database server I was receiving this exception:
>
> An error occurred while creating the database:
> java.lang.ClassNotFoundException: org.apache.derby.jdbc.Clientdriver
>

Basically the library had a class in the wrong package. I just copied the jdbc folder located in db-derby-10.16.1.1-bin\lib\derbytools.jar\org\apache\derby and pasted it in db-derby-10.16.1.1-bin\lib\derbyclient.jar\org\apache\derby.

> 3. After solving the missing class issue, every commit I tried to do to the database was being set to rollback, being unable to persist anything into the database. This is the exception I was getting:
>
> javax.persistence.RollbackException: Transaction "rolled back" because transaction was set to RollbackOnly.
>

Here the problem was that when the persistence.xml file was created, instead of using the jakarta library for the persistence-unit properties, it was using the old javax library, creating an incompatibility. Change all the javax for jakarta. Also, you may need to set the transaction-type to JTA.

<persistence-unit name="customersDatabase" transaction-type="JTA">
  <exclude-unlisted-classes>false</exclude-unlisted-classes>
  <properties>
    <property name="javax.persistence.jdbc.url" value="jdbc:derby://localhost:1527/customers"/>
    <property name="javax.persistence.jdbc.user" value="root"/>
    <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.ClientDriver"/>
    <property name="javax.persistence.jdbc.password" value="root"/>
    <property name="javax.persistence.schema-generation.database.action" value="create"/>
  </properties>
</persistence-unit>

> 4. Finally, when the transaction was finally not getting rolled back, I was getting an error because apparently the table I was trying to write into didn't exist. Even though I could clearly see it in the Derby server and the persistence.xml file pointed to it correctly:
>
> java.sql.SQLSyntaxErrorException: Table/View 'CUSTOMER' does not exist.
>

This is the one that took me more time, I was just going crazy. In the persistence.xml file we stated which database we want to connect to. However, there's a file called domain.xml inside the GlassFish server domain's domain-name\config folder which also sets up a database connection and dominates over the persistence file. It stablishes a connection to three different databases, we have to change the one named DerbyPool:

<jdbc-connection-pool is-isolation-level-guaranteed="false" datasource-classname="org.apache.derby.jdbc.ClientDataSource" name="DerbyPool" res-type="javax.sql.DataSource">
  <property name="PortNumber" value="1527"></property>
  <property name="Password" value="root"></property>
  <property name="User" value="root"></property>
  <property name="serverName" value="localhost"></property>
  <property name="DatabaseName" value="customers"></property>
  <property name="connectionAttributes" value=";create=true"></property>
</jdbc-connection-pool>

And that's it! If you installed everything correctly and fixed these issues your GlassFish + Derby server should be running and synchronized with Netbeans and all queries to the database should be working properly.