PL-J JDBC Driver

A Java stored procedure uses simple JDBC calls to perform database operations. PL-J has two JDBC Drivers:

• PostgreSQL Driver, this is derived from the original PostgreSQL JDBC Project.
• Scratch driver. This is a research driver with many configuration options, prepared statement pool and other features.

In general, the properties of the PL-J JDBC drivers:

• Does not open connection to the RDBMS, the RDBMS did already open it (inverse behavior)
• More JDBC connection will operate on the same connection
• No frontend-backend protocol, it uses the Channel architecture
• Highly configurable
• Provides plan pool to reuse already prepared statements
• Metainformations use the configuration object.
• Does not use any RDBMS specific code
• Type mapping using the typemapper architecture
• Transactions: not supported, will support embeded transactions.
• And the best: you dont have to know all this, only JDBC programming (and keep an aya on the unimplemented features list)

O/R mapping:

• In the very far future O/R mapping tools can be integrated to PL-J runtime. Basically when running PL-J triggers, you handle objects.

You dont need to load the classes for the drivers implivitly, the Drivers are pre-registered. An example of stored procedure:

package example;

import java.sql.*;

public class MyUDF {

  /**
   * @jsproc.udf name="jconcat"
   */
  public static final String doSomeSQL(String arg1) {
    Connection conn = null;
    PreparedStatement sta = null;
    ResultSet res = null;
    try{
      conn = DriverManager.getConnection("jdbc:default:connection");
      sta = conn.prepareStatement("select a from b where c = ? ");
      sta.setString(1, arg1);
      res = sta.executeQuery();
      if(res.next())
        return res.getString(1);
      return "--not found--";
    } catch (SQLException e) {
      //log the exception, rethrow something you like
      ...
    } finally {
      //close resources
      ...
    }
  }
}

Because of the statement plan pool embeded in the JDBC driver, prepared statements will have better performance than dynamic sql.

~_{grammar, typos}~