Connecting to database and creating tables with JChem Base for Java applications

JChem Class Library is a collection of Java classes, which provides the functionality of handling JChem structure tables in relational database management systems (RDBMS-s) for Java applications.

Contents

Software Requirements

In order to develop applications using JChem Class Library the following software need to be installed:

  • A relational database engine (RDBMS) that stores the structures in tables. The database server doesn't have to be installed on the same machine as the application if the computers are connected in a local network (Internet connection is also sufficient, but the speed of such system might be low).

  • A JDBC or an ODBC driver for the database engine, which is compatible with the applied version of Java Virtual Machine. The driver has to be installed on the same computer as the application. If you don't have a driver, search for it in the Internet site of the RDBMS vendor. See FAQ for more details on JDBC and ODBC drivers.

  • A web server (in the case of web applications).
    If your application will contain servlets or JSP scripts , a combination of a web server and a servlet server are needed. An example of such systems:

    • Tomcat web server (or Tomcat as servlet server integrated into Apache, IIS, or Netscape) This web server is recommended as having the best support for JChem base functionality.

Most servlet servers can also handle Java Server Pages (JSP) scripts that are HTML pages with embedded Java code running on the server. Moreover, separate JSP engines that can be connected to servlet servers are also available.
If you are going to develop an ASP application, then Microsoft's Internet Information Server is needed to be set up. ASP scripts can access JChem through a .NET bridge.
The JChem package contains examples for JSP.

  • JChemManager for creating structure tables, importing and exporting structure files, and for describing the properties of the RDBMS.

To let Java applications use the JChem Class Library, set classpath to include <JChem home>/lib/jchem.jar. See the jcman batch file or shell script in the jchem/bin directory as examples for setting classpath. (See the warning to avoid problems.)Alternatively, you can reference the ChemAxon Public Repository in your Maven project's pom.xml:

<repositories>
<repository>
<id>ChemAxon Public Repository</id>
<url>https://repository.chemaxon.com/artifactory/libs-release</url>
</repository>
</repositories>
 
<dependencies>
<dependency>
<groupId>com.chemaxon</groupId>
<artifactId>jchem</artifactId>
<version>15.5.4.0</version>
</dependency>
</dependencies>

Then add your account info in settings.xml:

<servers>
<server>
<id>ChemAxon Public Repository</id>
<username>Your username</username>
<password>Your password</password>
</server>
</servers>

For access rights to repository.chemaxon.com, please send a request to [email protected]. We also suggest to use Encrypted Password in settings.xml. For details see the Artifactory documentation.

If you are developing a web application (running servlets and/or JSP), see the documentation of your servlet engine for details on setting classpath for servlets. (Click here for Tomcat instructions.)

JChem Base features and architecture

The following table summarizes the technical details of JChem Base:

Technical details

Resulting features

The system is built in Java

Portable and high performance

It uses JDBC technology to store and retrieve structures in relational databases.

  • Most database engines (like Oracle, MySQL, MSSQL Server, MS Access, DB2, PostgreSQL etc...) can be used to store structures.

  • Structural and other chemical or non-chemical data can be handled together.

  • Tables can be joined.

  • SQL statements can access structural data.

Java modules are supplied for chemical structure searching.

  • Fast and reliable search that can be built into custom systems.

  • Web access to structure querying. Structure search and data manipulation can be handled by Java servlets and scripts that access Java classes or JavaBeans, like JSP (Java Server Pages).

  • Structure search can be built into legacy databases.

Structure input/update/display by the Marvin Java Applets and JavaBeans.

Convenient and interactive user interfaces for

  • Java capable web browsers (recently all are)

  • standalone Java applications

Java application for table creation and SDF/Molfile/SMILES import/export.

Communication with other chemical applications.

Architecture: A typical interaction between a client and the database

  1. Using a web browser, the user enters a structure into MarvinSketch.

  2. A custom script (or servlet) for substructure/similarity searching is activated, which

    1. Connects to a database through JDBC.

    2. Searches in a table containing structures.

    3. Creates a list containing the ID numbers of found structures

  3. The script retrieves mixed structural and non-structural data by SQL SELECT statements, using the list of hits and tables or views in the database.

  4. The script creates the page that displays the retrieved data in the client's browser using MarvinView .

  5. The user manipulates the data ...

    images/download/attachments/51022483/architecture.png

Please note that there are other possibilities for invoking substructure searching, which might better suit your demands.

Initializing the Database for Structure Handling

Before starting the development of a system that uses JChem to access structure tables in RDBMS-s, run JChemManager to initialize the database for structure handling:

See the documentation of JChemManager for more details.

What is Useful to Be Familiar With Before Starting the Development

Development of Graphical User Interfaces for JChem Applications

To support drawing and displaying chemical structures in standalone, rich-client or Java Web Start applications, JChem is bundled with MarvinBeans.
Marvin applets are recommended for applying in the interface of chemical web applications. Development issues and examples of using these tools are available in the Marvin Developer's Guide . However, you can also apply other chemical GUIs that import and export structure file formats recognized by JChem.

Connecting to Databases

Java classes in JChem may use ConnectionHandler or the java.sql.Connection class passed as a parameter to connect to a database.
Examples for opening a connection using ConnectionHandler

  • Java:

        ConnectionHandler connHandler = new ConnectionHandler();
        connHandler.setUrl(url);
        connHandler.setDriver(driver);
        connHandler.setPropertyTable(propTableName);
        connHandler.setLoginName(userName);
        connHandler.setPassword(password);
        try {
            connHandler.connect();
        } catch ...

    The driver can be defined as a built-in constant for the specified database server. See the DatabaseConstants class Example, if the database server is Oracle:

        connHandler.setDriver(DatabaseConstants.ORACLE_DRIVER);
  • JSP:

        <jsp:useBean id="connHandler" scope="session"
                class="chemaxon.util.ConnectionHandler" />
        <%
        if(!connHandler.isConnected()) {
            %>
            <jsp:setProperty name="connHandler" property="url"
                    value="<%= url %>" />
            <jsp:setProperty name="connHandler" property="driver"
                    value="<%= driver %>" />
            <jsp:setProperty name="connHandler" property="propertyTable"
                    value="<%= propTableName %>" />
            <jsp:setProperty name="connHandler" property="loginName"
                    value="<%= username %>" />
            <jsp:setProperty name="connHandler" property="password"
                    value="<%= password %>" />
            <jsp:setProperty name="connHandler" property="connected"
                    value="<%= true %>" />
            <%
        }
        %>

    If parameters of the connection are saved in JChemManager then they can be reused from code.

  • Java:

        ConnectionHandler ch = new ConnectionHandler();
        Properties props = new SettingsHandler().getSettings();
        if (!ch.loadValuesFromProperties(props)) {
            throw new IOException("Insufficient connection data.");
        }
        try {
            ch.connect();
        } catch ...

    Java examples are available in Java and HTML format.

Structure cache identification and registration

Cache identifiers are introduced in JChem 5.3.2 in order to better scale search and database performance and improve safe and efficient structure cache load. Caches - or more precisely, cache pools - are identified with a cache ID. All the caches get the same cache ID if they belong to the same cache pool. This way the cache ID identifies the cache pool or - what is equivalent to this - the JVM. By default all cache pools get a random cache ID (this ID can be modified in case of permanent caches - see #3 ).
In order to run a well performing search application, the cache ID must be registered before the first search. The registration process inserts a record into the cache registration database table (its default name is [JChemProperties table name]+"_CR", eg. JChemProperties_CR). If an application hasn't been registered before the first search is performed, it tries to register itself automatically but it may fail if the database connection is used in a transaction.
Before closing your application (quitting the JVM) please always unregister your cache!
There is only one exception to this rule: if your cache is permanently registered you may restart your application and simply set the previously registered cache ID (see #3 ). No re-registration is needed in this case.

Types of cache registration: temporary and permanent

The registration of a temporarily registered cache may be deleted automatically after 1 day of inactivity (that is, no searches have been initiated by the cache pool in the last day). After deletion, the cache should be registered again. The default cache registration is temporary.
Permanent cache registrations do not expire and can be unregistered only via the API (#4 , #5 ). Permanent registration needs a unique cache ID that can be set or can be given at registration (#2 , #3 ). Please use permanent registration only if your application is continuously running (e.g. server applications).

Recommendations

  • An unregistered cache always reloads its structure cache before every search, so it's recommended to register at the startup of the application!

  • Registration should be done outside of a transaction.

Registration and unregistration of cache ID

CacheRegistrationUtil is designed to handle registration process. For registration operations a connected ConnectionHandler (ch in the examples) is needed in almost every case - except when setting a permanent cache ID (see #3 ).

  • #1 - Registration without using a cache ID
        CacheRegistrationUtil cru = new CacheRegistrationUtil(ch);
        cru.registerCache();

    By default the registration will be temporary, using the default random cache ID. If a permanent cache ID (see #3) was set before, the registration will be permanent, using the given ID.

  • #2 - Registration with a cache ID (can be used only for permanent caches)
        String identifier = "unique_cache_identifier";
        CacheRegistrationUtil cru = new CacheRegistrationUtil(ch);
        cru.registerPermanentCache(identifier);

    The last line has two effects:

a) Changes the default cache ID to identifier.
b) Registers identifier as permanent cache ID.

  • #3 - Setting a permanent cache ID
        String identifier = "unique_cache_identifier";
        CacheRegistrationUtil.setPermanentCacheID(identifier);

    The second line changes the default cache ID to identifier and sets the cache registration type to permanent, so subsequent calls to registerCache() (see #1) will register the cache as permanent!

  • #4 - Unregistering the cache without using a cache ID
        CacheRegistrationUtil cru = new CacheRegistrationUtil(ch);
        cru.unregisterCache();

    Unregisters the current cache's cache ID.

  • #5 - Unregistering the cache using a cache ID
        String identifier = "unique_cache_identifier";
        CacheRegistrationUtil cru = new CacheRegistrationUtil(ch);
        cru.unregisterCache(identifier);

    Unregisters the given cache ID (temporary or permanent).

Accessing Structure Tables

Structure tables used by JChem reside in databases of RDBMS-s. See the administration guide for more details on managing these tables.
To access a structure table, a JDBC connection has to be built between the application and database. The tables can be handled by

  • executing SQL statements (java.sql.Statement), or

  • using objects from the JChem Class Library.
    JChem's classes expect the following parameters:
    An initialized java.sql.Connection object, usually contained in a ConnectionHandler object.

  • The name of the structure table. In the case of RDBMS-s supporting schemes, the owner of the table should also be specified followed by a dot before the actual name of the table (e.g.: cduser.structures). If you are unsure about the name, check the content of the JChemProperties table in the database. Though structure tables can be manipulated without using JChem's classes, you must be careful not to harm the integrity of the database. Only custom data fields of jchem tables are allowed to be manipulated directly by SQL statements - otherwise the JChem API must be used.
    Java examples for creating database tables using JChem API are available in Java and HTML format.

Demo applications

It is suggested to setup and try one of the demos included in the JChem package before starting the development of a custom application powered by JChem.