Retrieve JDBC connection to Maximo database

In some cases you need a JDBC connection to directly access Maximo database in order to perform some job. This is not common since all Maximo data can be accessed through standard MboSets. However, you may need to access non-Maximo tables, perform specific SQL statements or bypass Mbos for performance reasons. The two main approaches to use issue standard SQL code through Java JDBC are described here.

JDBC Connection
Here is a sample that shows how to get a JDBC connection from Maximo connection pool (and correctly release it).

public int countAssets() throws RemoteException, MXException
{
  MXServer mxServer = MXServer.getMXServer();
  ConnectionKey conKey = mxServer.getSystemUserInfo().getConnectionKey();
  Connection con = mxServer.getDBManager().getConnection(conKey);

  int c = 0;
  try
  {
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("select count(*) from asset");
    while (rs.next())
      c = rs.getInt(1);
    rs.close();
    stmt.close();
    con.commit();
  }
  catch (SQLException e )
  {
    throw new MXApplicationException("SQLException caught", e.getMessage());
  }
  finally
  {
    mxServer.getDBManager().freeConnection(conKey);
  }
  return c;
}

DBShortcut class
A quick easy way to get a java.sql.ResultSet object is to use DBShortcut class. This object comes in the psdi.mbo package and is designed to quickly retrieve result-sets or run update statements against the database.

public int countAssets() throws RemoteException, MXException
{
  MXServer mxServer = MXServer.getMXServer();
  ConnectionKey conKey = mxServer.getSystemUserInfo().getConnectionKey();
  DBShortcut dbs = new DBShortcut();
  dbs.connect(conKey);

  int c = 0;
  try
  {
    ResultSet rs = dbs.executeQuery("select count(*) from asset");
    while (rs.next())
      c = rs.getInt(1);
    rs.close();
    dbs.commit();
  }
  catch (SQLException e )
  {
    throw new MXApplicationException("SQLException caught", e.getMessage());
  }
  finally
  {
    dbs.close();
  }
  return c;
}

Alternative methods
In both the described methods the connection is retrieved through a static reference to MXServer object and a system psdi.security.ConnectionKey object.
A better approach could be to use some methods defined in the psdi.mbo.Mbo class as follows.

ConnectionKey conKey = getUserInfo().getConnectionKey();
Connection con = getMboServer().getDBConnection(conKey);