How to set up glassfish-resource.xml and web.xml?

Question

I have some troubleshoots and I don't really understand why it happened. I'm making simple web-service which try to goes to DB and take 1 record.

I add new server-resource through NetBeans Wizard. NB created new resourse and connection pool for it. Look like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//GlassFish.org//DTD GlassFish Application Server 3.1 Resource Definitions//EN" "http://glassfish.org/dtds/glassfish-resources_1_5.dtd">
<resources>
  <jdbc-resource enabled="true" jndi-name="jdbc/testdb" object-type="user" pool-name="testdbPool">
    <description/>
  </jdbc-resource>
  <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="oracle.jdbc.pool.OracleDataSource" 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="testdbPool" 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="URL" value="jdbc:oracle:thin:@193.107.2.38:5555:ora10e"/>
    <property name="User" value=""/>
    <property name="Password" value=""/>
  </jdbc-connection-pool>
</resources>

The I make changes in Web.xml. Like this:

    <?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
     version="3.1">
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
    <resource-ref>
      <description>DB Connection Pool</description>
      <res-ref-name>jdbc/testdb</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
      <res-sharing-scope>Shareable</res-sharing-scope>
   </resource-ref>

</web-app>

But on test glassfish doesn't connect me to this base. It use defaultPool. This is log:

WARNING:   RAR5038:Unexpected exception while creating resource for pool DerbyPool. Exception : javax.resource.spi.ResourceAllocationException: Connection could not be allocated because: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused: connect.
WARNING:   RAR5117 : Failed to obtain/create connection from connection pool [ DerbyPool ]. Reason : com.sun.appserv.connectors.internal.api.PoolingException: Connection could not be allocated because: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused: connect.
WARNING:   RAR5114 : Error allocating connection : [Error in allocating a connection. Cause: Connection could not be allocated because: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused: connect.]
SEVERE:   java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused: connect.

What am I doing wrong?

Answer 1

The problem is that the resource type is incorrect. You are listing your resource as a javax.sql.DataSource. However, you are actually using a javax.sql.ConnectionPoolDataSource

<resource-ref>
   <description>DB Connection Pool</description>
   <res-ref-name>jdbc/testdb</res-ref-name>
   <res-type>javax.sql.ConnectionPoolDataSource</res-type>
   <res-auth>Container</res-auth>
   <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

I ran into a similar issue while trying to establish a database connection between a Glassfish server and an SQLite DB. I thought that the problem was that the library just didn't support it, but changing the <res-type> solved the issue.

The big thing that attributed to this problem is that I was trying to add this resource through the tools provided by NetBeans. That's all well and good, but whenever you try to add a Resource Reference in your web.xml file, you are only presented with a limited pool of choices in the dropdown which made me believe that those were the only valid options.

However, you are able to manually type the class into the textbox of the dropdown OR you can go to the Source view of the web.xml and enter the class there by hand.

Additionally, if you are using a sun-resources.xml file to add the resource to the server automatically, my file looked like this when it finally worked (ellipses added for redacted attributes):

<resources>
   <jdbc-resource 
      enabled="true" 
      jndi-name="jdbc/rpg" 
      object-type="user" 
      pool-name="RpgPool">
      <description>Description</description>
   </jdbc-resource>
   <jdbc-connection-pool 
      ...
      datasource-classname="org.sqlite.SQLiteConnectionPoolDataSource" 
      ...
      res-type="javax.sql.ConnectionPoolDataSource" 
      wrap-jdbc-objects="false">
      <description>Description</description>
      <property 
         name="URL" 
         value="jdbc:sqlite:C:/some/where/out/there"/>
   </jdbc-connection-pool>
</resources>