How to use the pre-configured MySQLDS and PostgreSQLDS data sources in the Java cartridges

Code: 
KB-E1086
Applies to: 
OpenShift
Issue: 

The Java cartridges on OpenShift (jbossas-7, jbosseap-6, jbossews-1.0, jbossews-2.0) come with pre-configured data sources for both MySQL and PostgreSQL. This Knowledge Base article will show you how to use them to connect your Java application to your MySQL database. These instructions will reference the changes that need to be made to the following code to use PostgreSQL instead of MySQL where needed.

Resolution: 

Introduction

All four of the available Java cartridges on OpenShift come with two pre-configured data sources. One for MySQL and one for PostgreSQL. We have gotten quite a few questions on the forums and via the email list (openshift@redhat.com) asking how to use those data sources to connect to your database, and we are going to show some sample code here that will help you get started.

There are two types of Java cartridges and they expose the data source in different ways

There are two types of Java cartridge that we are going to look at, and each has to use a little bit different code to access the database. The JBOSS Application Server 7 & JBOSS EAP cartridges are using jndi (Java Naming and Directory Interface) to expose the connection details to your database via the .openshift/config/standalone.xml file, and the JBOSS EWS (Tomcat 6 & Tomcat 7) cartridges provide a Resouce via the .openshift/config/context.xml file. So please note which cartridge you are using for your Java application on OpenShift so that you can refer to the correct example below.

The database table for the examples below

If you would like to test this code out on your application, either add the phpmyadmin cartridge to your gear, or use the mysql command line and create the example table with the below structure. This is SQL so you can copy & paste this and it should work. Then you should create a JSP file called 'mysql.jsp' in your application (in the src/main/webapp directory) and copy/paste the below example code into it. Save it. Then do a git commit & git push. Then you can visit your appname-domain.rhcloud.com/mysql.jsp and see the results.

--
-- Table structure for table `names`
--
CREATE TABLE IF NOT EXISTS `names` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first` varchar(50) NOT NULL,
  `last` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
--
-- Data for table `names`
--
INSERT INTO `names` (`id`, `first`, `last`) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Doe'),
(3, 'John', 'Smith'),
(4, 'Jane', 'Smith');

JBoss Application Server 7 / JBoss Enterprise Application Platform 6.1.0 Example

This example applies to the jbosseap-6 and jbossas-7 cartridges. If you want to use PostgreSQL instead of MySQL, replace the 'MySQLDS' below with 'PostgreSQLDS'.

<%@ page language="java" contentType="text/html; charset=US-ASCII" pageEncoding="US-ASCII"%>
<%@ page import='java.sql.*' %>
<%@ page import='javax.sql.*' %>
<%@ page import='javax.naming.*' %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<title>JBOSS Application Server 7 &amp; JBOSS EAP</title>
</head>
<body>
<%
Connection result = null;
try {
    Context initialContext = new InitialContext();
    DataSource datasource = (DataSource)initialContext.lookup("java:jboss/datasources/MySQLDS");
    result = datasource.getConnection();
    Statement stmt = result.createStatement() ;
    String query = "select * from names;" ;
    ResultSet rs = stmt.executeQuery(query) ;
    while (rs.next()) {
        out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + "<br />");
    }
} catch (Exception ex) {
    out.println("Exception: " + ex + ex.getMessage());
}
%>
</body>
</html>

Tomcat 6 (JBoss EWS 1.0) / Tomcat 7 (JBoss EWS 2.0) Example

This example applies to the jbossews-1.0 and jbossews-2.0 cartridges. If you want to use PostgreSQL instead of MySQL, replace the 'MySQLDS' below with 'PostgreSQLDS'.

<%@ page language="java" contentType="text/html; charset=US-ASCII" pageEncoding="US-ASCII"%>
<%@ page import='java.sql.*' %>
<%@ page import='javax.sql.*' %>
<%@ page import='javax.naming.*' %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<title>Tomcat 6 &amp; Tomcat 7 (JBOSS EWS) Example</title>
</head>
<body>
<%
Connection result = null;
try {
    InitialContext ic = new InitialContext();
    Context initialContext = (Context) ic.lookup("java:comp/env");
    DataSource datasource = (DataSource) initialContext.lookup("jdbc/MySQLDS");
    result = datasource.getConnection();
    Statement stmt = result.createStatement() ;
    String query = "select * from names;" ;
    ResultSet rs = stmt.executeQuery(query) ;
    while (rs.next()) {
        out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + "<br />");
    }
} catch (Exception ex) {
    out.println("Exception: " + ex + ex.getMessage());
}
%>
</body>
</html>

Wrap Up

These examples are just to help you get going using the pre-configured data sources, there are tons of possibilities for how to use this logic in your own application, and of course there are other options such as Hibernate that are not discussed here. But since you get two data sources already set up within the cartridge when you install it, why not learn to use them? Of course if you were writing this in your own application you would put in more try/catch logic and handle individual exceptions, but remember that this is just an example to get you going making the connection to your database.