Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Use Mimer SQL with JRun
Category: Tools and Interfaces
Introduction

This article describes how you can use Mimer SQL for J2EE development with JRun.

It explains how to make the Mimer JDBC driver available to JRun, how to define a data source and includes an example that shows how to open a connection from your Java code.

JRun is a full-featured J2EE container that has been certified by Sun to conform to the J2EE standard.

Note! This article discusses the Mimer JDBC Driver, but you can use any JDBC driver.

About the Mimer JDBC Driver

There are two versions of the Mimer JDBC Driver:
  • Mimer JDBC 1.3 Driver, for Mimer SQL 8.2 or later. The Java environment on the client must support at least JDK 1.1.
  • Mimer JDBC 2.2 Driver, for Mimer SQL 8.2.2 or later. The Java environment on the client must support at least JDK 1.2.
You can download a driver here.
Read more about the Mimer JDBC Driver here.

Description

To use Mimer SQL for J2EE development with JRun you must:
  • make the JDBC driver available to JRun
  • define a data source.
  • connect using the data source in your Java code.

Making the JDBC Driver Available to JRun

Before we can define a data source we must make the JDBC driver available to JRun.

There are two ways of making the Mimer JDBC driver available to JRun:

Copy the driver directly
The first and easiest, method is to simply copy the driver JAR file to a location that JRun uses, for example <jrun-root-dir>/servers/lib.

Tell JRun where to find the driver
The second way is to tell JRun where to find the driver.
There are also two ways of doing this:
  • In the JRun Management Console (JMC), under Java Settings, enter the path to the driver, for example:



  • Click update.
  • Edit the configuration files directly. The configuration files you can use are global.properties or local.properties. Entries in global.properties are system wide and entries in local.properties only apply to one server.


Defining a Data Source

The next step is to define a data source. You can define a data source in JMC or by editing local.properties.

Defining a Data Source in JMC

  1. In JMC, select the server you want to edit and then select JDBC Data Sources and click Add. This will start a wizard to configure the data source, for example:



    On the first screen you must select RDBMS Server Name in this case Not Listed, a Data Source Name for example MimerPool and a description if you want to. Click next.


  2. On the next screen:



    Enter the details for Server User, Server Password, Driver Class Name, in this case com.mimer.jdbc.Driver, and URL, for example jdbc:mimer://localhost/multi1 where multi1 is the name of the multi user system on your machine. Click next.


  3. The next screen displays options for the pooling mechanism, for example:



    Accept the default values. Click next.


  4. On the final screen:



    Confirm the Driver and URL by clicking done.


Defining a Data Source by Editing local.properties

To define the same data source without using JMC, edit local.properties and add the following:

jdbc.MimerPool.display-name=MimerPool
jdbc.MimerPool.driver=com.mimer.jdbc.Driver
jdbc.MimerPool.url=jdbc:mimer://localhost/multi1
jdbc.MimerPool.description=A Mimer SQL connection pool
jdbc.MimerPool.pooling=true
jdbc.MimerPool.timeout=5
jdbc.MimerPool.interval=60
jdbc.MimerPool.param.user=HOTELADM
jdbc.MimerPool.param.password=HOTELADM


Testing Your Data Source

If you want, you can test the data source directly from JMC. Just click Test.

Connecting Using the Data Source in Your Java Code

When you want to use a connection in your Java code, JNDI is used to fetch the data source and then you use the data source to get a connection. This process consists of three steps:
  1. Get an initial context, for example: Context = new InitialContext();
  2. Get a data source, for example:
    DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/MimerPool");
  3. Get a connection, for example:
    Connection con = ds.getConnection();


When you are done with the connection and you close it with Connection.close(), the connection isn’t really closed, it’s returned to the connection pool and ready for the next user.

Environment

  • Mimer SQL 8.2
  • JRun 3.1
  • Java Runtime Environment 1.3


Example

The following example is a simple servlet that queries the database we used in the previous examples and prints all tables that the current user has access to.
package se.mimer.informationtechnology.examples;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import javax.sql.*;
import java.sql.*;
import javax.naming.*;

/**
* TestServlet retrieves and prints all tables in the database owned by the
* current user. The connection is fetched from the connection pool in
* JRun in this case, but exactly the same code should work with
* any J2EE conformant server.
*/


public class TestServlet extends HttpServlet {
private static final String CONTENT_TYPE = "text/html";
private static DataSource dataSource = null;

/**Initialize global variables*/
public void init() throws ServletException {
try {
/**
* Get an initial context to be used when querying JNDI. Since querying JNDI
* is a quite expensive operation it's only done once. The dataSource will
* be shared between all requests for the servlet,
* and that's what we want.
*/
javax.naming.Context context = new javax.naming.InitialContext();
//Get the Datasource from JNDI. This is the standard way to do it.
dataSource = (DataSource) context.lookup("java:comp/env/jdbc/DBPool");
}
catch(NamingException ne) {
throw new ServletException("Error looking up dataSource: " +
ne.getMessage());
}
}

/**Process the HTTP Get request*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType(CONTENT_TYPE);
Connection con = null;
ResultSet rs = null;
String sql = "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES";
sql += " WHERE TABLE_SCHEMA = ?";
PreparedStatement pstmt = null;
PrintWriter out = response.getWriter();
out.println("<html><head><title>Tables</title></head><body>");
out.println("<table>");
out.println("<tr><td><b>Name</b></td><td><b>Type</b></td></tr>");
try {
//Get a connection from the Datasource, i.e from the connection pool.
con = dataSource.getConnection();
//Prepare the statement for execution.
pstmt = con.prepareStatement(sql);
pstmt.setString(1, con.getMetaData().getUserName().toUpperCase());
rs = pstmt.executeQuery();
//Loop over the entire result set.
while(rs.next()){
out.println("<tr>");
out.println("<td>" + rs.getString("TABLE_NAME") + "</td>");
out.println("<td>" + rs.getString("TABLE_TYPE") + "</td>");
out.println("</tr>");
}
}
catch(SQLException sqle){
throw new ServletException("Error getting connection: " +
sqle.getMessage());
}
finally{
/**
* To close the result set, statements and connection in a finally clause
* is prefered. This way resources is always closed, even if an exception
* is thrown earlier.
* To close result set, statements and connections in separate try clauses
* garanetes that we at least tries to close everything. Even if, for
* example, the rs.close() fails, con.close() will execute.
*/
try{
//Close the result set
rs.close();
}
catch(Exception e){
//Do nothing
}
try{
//Close the statement
pstmt.close();
}
catch(Exception e){
//Do nothing
}
try{
//Close the conection. The connection isn't really closed, it's only
//given back to the connection pool and marked as available.
con.close();
}
catch(Exception e){
//Do Nothing
}
}
out.println("</table>");
out.println("</body></html>");

}
/**Clean up resources*/
public void destroy() {
}
}




Links

Here are some links you might find useful:
Benefits

Using a standard J2EE server makes your code more portable. Because JRun is J2EE certified, the same code should work on any J2EE server. This way you don’t depend on a specific application server provider.

Last updated: 2009-10-13

 

Powered by Mimer SQL

Powered by Mimer SQL