How do I add JDBC drivers and configure JDBC Resources in Payara Micro?

Question

What are my options to set up JDBC drivers and resources when using Java EE Payara Micro?

Answer 1

This method combines answers from Mike and Adam Bien via tainos. It involves making a new domain.xml, which is a Payara config file. No application modification is needed if it worked with full Payara. The example below is for PostgreSQL JDBC.

1. Open payara-micro.jar with an archive manager and extract the file /microdomain.xml.
2. Open microdomain.xml in a text editor.
3. If your application was already deployed to a full Payara, you can copy-paste the changes below from the full Payara's domain.xml.

4. Add right above the line containing </resources>, using your dbname, dbuser, dbpassword, hostname:port and poolname:

   <jdbc-connection-pool connection-validation-method="auto-commit" driver-classname="org.postgresql.Driver" res-type="java.sql.Driver" name="poolname" is-connection-validation-required="true" connection-creation-retry-attempts="3" validate-atmost-once-period-in-seconds="60">
       <property name="URL" value="jdbc:postgresql://localhost:5432/dbname"></property>
       <property name="user" value="dbuser"></property>
       <property name="password" value="dbpassword"></property>
   </jdbc-connection-pool>
   <jdbc-resource pool-name="poolname" jndi-name="jdbc/poolname"></jdbc-resource>
   

5. Add right above the line containing </server>:

   <resource-ref ref="jdbc/poolname"></resource-ref>
   

6. Save and close the text editor.

7. Start Payara micro from command line, using your paths and filenames. Linux syntax:

   java -cp "/opt/jdbc/postgresql.jar:/opt/payara/micro.jar" fish.payara.micro.PayaraMicro --deploy webapp.war --domainConfig microdomain.xml
   

Answer 2

Add the datasource definition to your web.xml and then add the jar file for the JDBC jar into your WEB-INF/lib. Then deploy the war file as usual to Payara Micro.

<data-source>
  <name>java:global/ExampleDataSource</name>
  <class-name>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</class-name>
  <server-name>localhost</server-name>
  <port-number>3306</port-number>
  <database-name>mysql</database-name>
  <user>root</user>
  <password>root</password>
  <!-- Example of how to use a Payara specific custom connection pool    setting -->
  <property>
     <name>fish.payara.sql-trace-listeners</name>
     <value>com.sun.gjc.util.SQLTraceLogger</value>
  </property>
</data-source>

There is a complete example of how to do this on the Payara Examples GitHub repository. See Datasource example on Payara GitHub