jeudi 14 mai 2015

Create JDBC connection pool in GlassFish for remote database

Do I need to create a connection pool for a remote database in my GlassFish server console?

Details:

I'm using a MySQL Amazon RDS instance for my database. I have the database setup and running from the AWS console. Using the Eclipse data source tools, pings succeed. I'm using EclipseLink as my JPA provider. My peristence.xml file looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://ift.tt/1cKbVbQ" xmlns:xsi="http://ift.tt/ra1lAU" xsi:schemaLocation="http://ift.tt/1cKbVbQ http://ift.tt/1kMb4sd">
<persistence-unit name="WebApplication" transaction-type="JTA">
    <properties>
        <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"></property>
        <property name="javax.persistence.jdbc.url" value="jdbc:mysql://link-to-my-database.amazonaws.com:3306/TestDB"></property>
        <property name="javax.persistence.jdbc.user" value="admin"/>
        <property name="javax.persistence.jdbc.password" value="my-password"/>
        <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
        <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
    </properties>
</persistence-unit>

The MySQL connector (connector/j) is placed in my application build path. Within my application, I can (rudimentary) test the connection (which also succeeds):

try{
            System.out.println("Loading driver...");
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Driver loaded!");
        }catch(ClassNotFoundException e){
            System.out.println("JAR file containing JDBC driver (com.mysql.jdbc.Driver) has not been placed in the class path.");
            e.printStackTrace();
        }
        Connection connection = null;
        try{
            System.out.println("Connecting to database...");
            connection = DriverManager.getConnection("jdbc:mysql://link-to-my-database.us-east-1.rds.amazonaws.com:3306/TestDB", "admin", "my-password");
            System.out.println("Connected to database!");
            System.out.println("Connection read only: " + connection.isReadOnly());
            System.out.println("Connection warnings: " + connection.getWarnings()); 
            System.out.println("Connection schema: " + connection.getSchema());
            System.out.println("MetaData: " + connection.getMetaData().toString());
        }catch(SQLException e){
            System.out.println("Error connecting to the database. SQLException:");
            e.printStackTrace();
            System.out.println("Error connecting to the database. RuntimeException:");
            throw new RuntimeException("Cannot connect the database!", e);
        }finally{
            System.out.println("Closing the connection.");
            if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
        }

Since, I'm running in a container managed environment, I can even test that the injected EntityManager is connected properly:

@PersistenceContext(unitName = "WebApplication")
private EntityManager em;

System.out.println("CONNECTION PROPERTIES:");
Map<String, Object> props = em.getProperties();
for(Map.Entry<String, Object> prop : props.entrySet()){
    System.out.println("Property: " + prop.getKey() + " Value: " + prop.getValue());
}

Which outputs the following:

2015-05-14T16:23:44.320-0400|Info: CONNECTION PROPERTIES:
2015-05-14T16:23:44.320-0400|Info: Property: eclipselink.ddl-generation Value: drop-and-create-tables
2015-05-14T16:23:44.320-0400|Info: Property: javax.persistence.jdbc.url Value: jdbc:mysql://link-to-my-database.us-east-1.rds.amazonaws.com:3306/TestDB
2015-05-14T16:23:44.320-0400|Info: Property: javax.persistence.jdbc.user Value: admin
2015-05-14T16:23:44.320-0400|Info: Property: javax.persistence.jdbc.driver Value: com.mysql.jdbc.Driver
2015-05-14T16:23:44.320-0400|Info: Property: javax.persistence.jdbc.password Value: password
2015-05-14T16:23:44.320-0400|Info: Property: javax.persistence.schema-generation.database.action Value: drop-and-create
2015-05-14T16:23:44.320-0400|Info: Property: hibernate.transaction.manager_lookup_class Value: org.hibernate.transaction.SunONETransactionManagerLookup
2015-05-14T16:23:44.320-0400|Info: Property: eclipselink.target-server Value: SunAS9
2015-05-14T16:23:44.320-0400|Info: Property: toplink.target-server Value: SunAS9

So, it looks to me that the connection should be good and work. However, when attempting to view the tables using both Eclipse Data Source Explorer View (Data Tools Platform) and the MySQL Workbench, there are no tables to view (yes I've refreshed). This led me to believe that the data was being stored elsewhere, as if to a default location. I open GlassFish developer console (localhost:4848) > JDBC > Connection Pools. There is a "default" connection pool which I deleted. Now, when running the app I get the following errors at the beginning of the stack trace:

2015-05-14T17:06:27.493-0400|Severe: Exception while invoking class org.glassfish.persistence.jpa.JPADeployer prepare method
2015-05-14T17:06:27.516-0400|Severe: Exception while preparing the app
2015-05-14T17:06:27.517-0400|Severe: Exception during lifecycle processing
java.lang.RuntimeException: Invalid resource : jdbc/__default__pm 

Yet, I never specified for the server to connect to jdbc/__default__pm. This leads me to believe that it looks for a "default" connection pool as a fallback if it can't connect to the specified one in the persistence.xml file. Though, the connection works it seems it is not being made by the app, is this because I have to specify the remote connection in a connection pool in the GlassFish server console? Or am I missing something else?

Aucun commentaire:

Enregistrer un commentaire