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>

schema is not part of connection string. try appending it to a relation name instead?.. — Vao Tsun, Apr 12 '17 at 10:22
@VaoTsun The [PostgreSQL JDBC documentation](https://jdbc.postgresql.org/documentation/94/connect.html#connection-parameters) begs to differ: _"`currentSchema = String` Specify the schema to be set in the search-path. This schema will be used to resolve unqualified object names used in statements over this connection."_ — Mark Rotteveel, Apr 12 '17 at 11:09
@Vao Tsun , appending it to relation name ? do you mean to Entity or Table annotation attribute in the entity classes ? but I want one class to be used in multiple schema. — Mideel, Apr 12 '17 at 11:23
Could it be because of the JPA, maybe normal JDBC commands without JPA would work but JPA still uses public schema even if we use that ? — Mideel, Apr 12 '17 at 11:25
Maybe JPA generates sql commands like " SELECT * FROM public.mytable" or something like that ? — Mideel, Apr 12 '17 at 11:30

score 1 · Answer 1 · edited May 23 '17 at 12:18

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.

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.
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.

Specifying schema in glassfish jdbc connection pool?

1 Answers1