Specifying schema in glassfish jdbc connection pool?

Question

I have read some of the answers like by appending ?searchpath=myschema or ?currentSchema=myschema but it still doesn't work in my case. I use NetBeans and I can execute commands to the connection with the intended schema and it works well but in run time, Glassfish only connects to public schema ignoring the ?currentSchema=myschema. My postgresql version is 9.6 and the JDBC driver version is the latest 42.0.0

This is my glassfish-resource.xml:

<resources>
    <jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false" connection-creation-retry-attempts="0" connection-creation-retry-interval-in-seconds="10" connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0" connection-validation-method="auto-commit" datasource-classname="org.postgresql.ds.PGSimpleDataSource" fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="true" lazy-connection-association="false" lazy-connection-enlistment="false" match-connections="false" max-connection-usage-count="0" max-pool-size="32" max-wait-time-in-millis="60000" name="post-gre-sql_aegwyncreds_dbexerphi_dbaPool" non-transactional-connections="false" pool-resize-quantity="2" res-type="javax.sql.DataSource" statement-timeout-in-seconds="-1" steady-pool-size="8" validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
        <property name="serverName" value="localhost"/>
        <property name="portNumber" value="5432"/>
        <property name="databaseName" value="mydb"/>
        <property name="User" value="user"/>
        <property name="Password" value="pass"/>
        <property name="URL" value="jdbc:postgresql://localhost:5432/mydb?currentSchema=myschema"/>
        <property name="driverClass" value="org.postgresql.Driver"/>
    </jdbc-connection-pool>
    <jdbc-resource enabled="true" jndi-name="java:app/myjndisource" object-type="user" pool-name="post-gre-sql_mydb_user_dbaPool"/>
</resources>

This is my persistence unit:

<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="myPU" transaction-type="JTA">
    <jta-data-source>java:app/myjndisource</jta-data-source>
    <class>myclass</class>
    . . . . . . .  . . .
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
    </properties>
  </persistence-unit>
</persistence>

Answer 1

I finally figured it out by myself after combining some Google results, I hope that this useful for people who are like me. Here are some conclusions that I made.

1. Adding the currentSchema=myschema in jdbc-conncetion-pool element doesn't change anything in JPA if that connection is used in JPA and specified in the persistence unit, like my previous example:

   java:app/myjndisource

   EntityManager still uses public schema and ignore the specified schema.

2. We need to use orm.xml file to specify the schema that we want, like in this example:

   JPA - EclipseLink - How to change default schema

   The orm.xml file can be set with any name but it must be on the classpath. In NetBeans you can use this folder structure as example since you can't generate this file with Netbeans built in help.

   Web Pages -> WEB-INF -> classes -> META-INF -> ( the mapping files )

Netbeans web application structure

Then in the persistence unit we can specify the mapping-file element, but remember these 2 points

A. In Netbeans ( or perhaps other IDES ), you must place the mapping-file element after the jta-data-source element but before class element otherwise there will be some deployment error like in this link:

https://netbeans.org/bugzilla/show_bug.cgi?id=170348

B. If you name the mapping file with the name orm.xml and include it in the same directory with your persistence unit, automatically the persistence unit includes that mapping file although you don't specify it with the mapping-file element.

So if you have 2 mapping files and one of them named orm.xml and both of them include schema element and your persistence unit uses mapping-file element for the other mapping file there will be an error because of the conflicting schema.