Package de.elo.ix.jscript

Class DBConnection

java.lang.Object
de.elo.ix.jscript.DBConnection
public class DBConnection extends Object
This class provides access to an external database.

In order to use this class to access external databases, a connection pool has to be defined as a JNDI Resource e.g. in the Tomcat configuration file Tomcat/conf/context.xml.

It is also possible to access the ELO archive database the Indexserver is connected to. The default constructor of DBConnection obtains a connection for this purpose.

Configure Database Pool

The following XML code shows examples for resource definitions for ORALCE, DB2 and Microsoft SQL Server. 

 
 
   <Resource name="jdbc/MyORACLE" auth="Container"
               type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
               url="jdbc:oracle:thin:@srvporacle01vm:1521:ora11uni"
               username="elo80_ora" password="elodb" maxActive="20" maxIdle="10"
               maxWait="-1" /> 
               
   <Resource name="jdbc/MyDB2" auth="Container"
               type="javax.sql.DataSource" driverClassName="com.ibm.db2.jcc.DB2Driver"
               url="jdbc:db2://srvt02:50000/elotestu"
               username="elodb" password="elodb" maxActive="20" maxIdle="10"
               maxWait="-1"/>
               
   <Resource name="jdbc/MyMSSQL" auth="Container"
               type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
               url="jdbc:sqlserver://localhost:1433;databaseName=elo80"
               username="elodb" password="elodb" maxActive="20" maxIdle="10"
               maxWait="-1"/>

Hints:

  • DB2 does not provide an argument for the schema name in the jdbc-url string. The schema has to be supplied in the constructor of the DBConnection class.
  • The database name for a Microsoft SQL Server connection can either be defined in the JNDI Resource by the argument "databaseName" in the jdbc-url. Or it can be supplied in the constructor of the DBConnection class.

For more information on how to configure database connection pools, refer to the original Tomcat documentation resp. the documentation of the application server that is used.

Execute SELECT Statement

The following JavaScript function shows how to execute a SELECT command. It creates a new DBConnection object that uses database connections from the database pool defined in the JNDI Resource "jdbc/MyDB2". The constructor itself does not request a connection. This is done inside the query function. When the query function exits, it releases the connection. Thus, there is no need to explicitly close a database connection here. Refer to the documentation of the function query(String, Object, int, Object) for more information on executing SELECT commands. 

 
  function dbread() {
    var db = new Packages.de.elo.ix.jscript.DBConnection( "jdbc/MyDB2", "TESTSCHEMA" );
    var args = ["Stuttgart", 59];
    var ret = db.query("select name, salary from employees where branch like ? and age > ? order by salary", args);
    for (var i = 0; i %lt; ret.length; i++) {
      log.info("name=" + ret[i][0] + ", salary=" + ret[i][1]);
    }
    return ret;
  }

Execute INSERT Statement

The next example inserts two rows into a database table. It uses the update function which can also execute UPDATE and DELETE commands. The row data is supplied in an array of rows whereby each row is an array of columns. The function returns the number of inserted (updated or deleted) database rows. 

 
  function dbinsert() {
    var db = new Packages.de.elo.ix.jscript.DBConnection( "jdbc/MyDB2", "TESTSCHEMA" );
    var args = [["Fritz", 54321.22, 38],["Maria", 65432.11, 43]];
    var ret = db.update("insert into employees (name, salary, age) values (?, ?, ?)", args);
    log.info("number of inserted rows=" + ret); // == 2
    return ret;
 }

Write JDBC Code

If the query and update functions of this class does not fit the requirements, a JDBC connection object can be obtained from a call to the open function. The programmer is responsible for releasing this connection by a call to its close method. 

 
  function dbjdbc() {
    var ret = [];
    var jdbc;
    var stmt;
    var rs;
    try {
       var db = new Packages.de.elo.ix.jscript.DBConnection( );
       jdbc = db.open();
       stmt = jdbc.createStatement();
       rs = stmt.executeQuery("select ....");
       var cols = rs.getMetaData().getColumnCount();
       while (rs.next()) {
         var row = [];
         for (var i = 1; i <= cols; i++) {
           row.push( rs.getString(i) );
         }
         ret.push(row);
       }
     }
    }
    catch (ex) {
      log.error(ex);
    }   
    finally {
      if (rs) try {rs.close();} catch (ignored) {}
      if (stmt) try {stmt.close();} catch (ignored) {}
      if (jdbc) try {jdbc.close();} catch (ignored) {}
    }
    return ret;
 }

  • Constructor Details

    • DBConnection

      public DBConnection(IDBConnectionHandler handler)

    • DBConnection

      public DBConnection()
      Constructor. Use this constructor to obtain connections from the internal Indexserver connection pool. This connections access the ELO archive database.

    • DBConnection

      public DBConnection(String dataSourceName)
      Constructor. Calls open with the given parameters
      Parameters:
      dataSourceName - Data source name specified in the Tomcat configuration. E.g. "jdbc/MyDB2".

    • DBConnection

      public DBConnection(String dataSourceName, String schemaName)
      Constructor. Calls open with the given parameters
      Parameters:
      dataSourceName - Data source name specified in the Tomcat configuration. E.g. "jdbc/MyDB2".
      schemaName - Optional. DB2 schema name or MSSQL database name.

  • Method Details