java.lang.Object

de.elo.ix.jscript.DBConnection

public class DBConnection
extends java.lang.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 < 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 Summary

Constructors
Constructor	Description
`DBConnection()`	Constructor.
`DBConnection(IDBConnectionHandler handler)`
`DBConnection(java.lang.String dataSourceName)`	Constructor.
`DBConnection(java.lang.String dataSourceName, java.lang.String schemaName)`	Constructor.

Method Summary

Modifier and Type	Method	Description
`java.lang.String`	`escapeString(java.lang.String value)`
`java.lang.String`	`getDatabaseProductName()`
`boolean`	`isDB2()`
`boolean`	`isMssql()`
`boolean`	`isOracle()`
`boolean`	`isPgsql()`
`java.sql.Connection`	`open()`
`java.lang.Object[][]`	`query(java.lang.String sqlcmd)`
`java.lang.Object[][]`	`query(java.lang.String sqlcmd, java.lang.Object params)`
`java.lang.Object[][]`	`query(java.lang.String sqlcmd, java.lang.Object params, int max, java.lang.Object binding)`
`de.elo.ix.db.schema.DBSchemaModifier`	`schema()`	Returns a `DBSchemaModifier` that is capable of creating and altering tables in a relational database system.
`java.lang.Integer`	`update(java.lang.String sqlcmd)`
`java.lang.Integer`	`update(java.lang.String sqlcmd, java.lang.Object rows)`
`java.lang.Integer`	`update(java.lang.String sqlcmd, java.lang.Object rows, java.lang.Object binding)`

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

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.
  
  Throws:
  
  java.lang.Exception
- DBConnection
  
  public DBConnection(java.lang.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(java.lang.String dataSourceName, java.lang.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
- getDatabaseProductName
  
  public java.lang.String getDatabaseProductName() throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- isOracle
  
  public boolean isOracle() throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- isDB2
  
  public boolean isDB2() throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- isMssql
  
  public boolean isMssql() throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- isPgsql
  
  public boolean isPgsql() throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- open
  
  public java.sql.Connection open() throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- query
  
  public java.lang.Object[][] query(java.lang.String sqlcmd, java.lang.Object params, int max, java.lang.Object binding) throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- query
  
  public java.lang.Object[][] query(java.lang.String sqlcmd, java.lang.Object params) throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- query
  
  public java.lang.Object[][] query(java.lang.String sqlcmd) throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- update
  
  public java.lang.Integer update(java.lang.String sqlcmd, java.lang.Object rows, java.lang.Object binding) throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- update
  
  public java.lang.Integer update(java.lang.String sqlcmd) throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- update
  
  public java.lang.Integer update(java.lang.String sqlcmd, java.lang.Object rows) throws java.lang.Exception
  
  Throws:
  
  java.lang.Exception
- escapeString
  
  public java.lang.String escapeString(java.lang.String value)
- schema
  
  public de.elo.ix.db.schema.DBSchemaModifier schema()
  
  Returns a DBSchemaModifier that is capable of creating and altering tables in a relational database system. Currently, this method is supported only when using a database connection to the repository's database.
  
  Returns:
  
  DBSchemaModifier

Class DBConnection