Connecting to a database

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

The Database component handles the JDBC connection to a SQL server and is required for all database applications involving server data. JDBC is the Sun Database Application Programmer Interface, a library of components and classes developed by Sun to access remote data sources. The components are collected in the java.sql package and represent a generic, low-level SQL database access framework.

The JDBC API defines Java classes to represent database connections, SQL statements, result sets, database metadata, etc. It allows a Java programmer to issue SQL statements and process the results. JDBC is the primary API for database access in Java. The JDBC API is implemented via a driver manager that can support multiple drivers connecting to different databases. For more information about JDBC, visit the Sun JDBC Database Access API web site at http://java.sun.com/products/jdbc/.

JBuilder uses the JDBC API to access the information stored in databases. Many of JBuilder's data-access components and classes use the JDBC API. Therefore, these classes must be properly installed in order to use the JBuilder database connectivity components. In addition, you need an appropriate JDBC driver to connect your database application to a remote server. Drivers can be grouped into two main categories: drivers implemented using native methods that bridge to existing database access libraries, or all-Java based drivers. Drivers that are not all-Java must run on the client (local) system. All-Java based drivers can be loaded from the server or locally. The advantages to using a driver entirely written in Java are that it can be downloaded as part of an applet and is cross-platform.

Some versions of JBuilder include JDBC drivers. Check the Borland web site at (http://www.borland.com/jbuilder/) for availability of JDBC drivers in the JBuilder versions, or contact the technical support department of your server software company for availability of JDBC drivers. Some of the driver options that may ship with JBuilder are:

You can connect JBuilder applications to remote or local SQL databases, or to databases created with other Borland applications such as C++ Builder or Delphi. To do so, look at the underlying database that your application connects to and connect to that database using its connection URL.


Tutorial: Connecting to a database using the JDataStore JDBC driver

This tutorial assumes you are familiar with the JBuilder design tools. If not, see the online help topic "Designing a user interface."

This tutorial outlines:

Note: When you no longer need a Database connection, you should explicitly call the Database.closeConnection() method in your application. This ensures that the JDBC connection is not held open when it is not needed and allows the JDBC connection instance to be garbage collected.

Adding a Database component to your application

The Database component is a JDBC-specific component that manages a JDBC connection. To access data using a QueryDataSet or a ProcedureDataSet component, you must set the database property of the component to an instantiated Database component. Multiple data sets can share the same database, and often will.

In a real world database application, you would probably place the Database component in a data module. Doing so allows all applications that access the database to have a common connection. To learn more about data modules, see "Using data modules to simplify data access".

To add the Database component to your application,

  1. Create a new project and application files using the Application wizard. (You can optionally follow this tutorial to add data connectivity to an existing project and application.) To create a new project and application files:

    1. Select File|Close from the JBuilder menu to close existing applications. If you do not do this step before you do the next step, the new application files will be added to the existing project.
    2. Select File|New and double-click the Application icon. Accept (or modify) all defaults.

  2. Open the UI designer by selecting the file Frame1.java in the content pane, then select the Design tab at the bottom of the AppBrowser.
  3. Select the DataExpress tab from the component palette. Click the Database component.

    Database icon

  4. Click anywhere in the designer window to add the Database component to your application. This adds the following line of code to the Frame class:


    Database database1 = new Database();
    

The Database component appears in the content pane, looking like this:

Database component in the content pane

Setting Database connection properties

The Database connection property specifies the JDBC driver, connection URL, user name, and password. The JDBC connection URL is the JDBC method for specifying the location of a JDBC data provider (for example, SQL server). It contains all the information necessary for making a successful connection, including user name and password.

You can access the ConnectionDescriptor object programmatically, or you can set connection properties through the Inspector. If you access the ConnectionDescriptor programmatically, follow these guidelines:

The following steps describe how to set connection properties through the UI designer to the sample JDataStore database.

Note: To use the sample database, you will need to make sure your system is set up for JDataStore. If you have not already done so, see "Setting up JDataStore for the samples".

  1. Select database1 in the component tree.
  2. Select the connection property's value in the Inspector, and click the ellipsis button to open the Connection property editor.
  3. Set the following properties:
    PropertyDescription

    Driver The class name of the JDBC driver that corresponds to the URL, for this example, select com.borland.datastore.jdbc.DataStoreDriver from the list.
    URL The Universal Resource Locator (URL) of the database, for this example. The default value is jdbc:borland:dslocal:directoryAndFile.jds. Click the Browse button to select the Local JDataStore Database, which is located in /jbuilder/samples/JDataStore/datastores/employee.jds. Use the Browse button to select this file to reduce the chance of making a typing error. When selected, the URL will look similar to this:

    Unix:
    jdbc:borland:dslocal:/usr/local/jbuilder/samples/JDataStore/datastores/employee.jds
    Windows:
    jdbc:borland:dslocal:C:\jbuilder\samples\JDataStore\datastores\employee.jds

    Username The user name authorized to access the server database. For the sample tutorials, any user name will work.
    Password The password for the authorized user. For the tutorials, no password is required.
    Prompt user for password Whether to prompt the user for a password when opening the database connection.

    The dialog looks like this:

    Connection descriptor dialog

  4. Click the Test Connection button to check that the connection properties have been correctly set. The connection attempt results are displayed beside the Test Connection button.

    If JDataStore was installed with JBuilder, you will be prompted for your serial number and password the first time you use it. If you don't have the JDataStore information at this time, you can enter it later by selecting Tools|JDataStore Explorer, then selecting License Manager from its File menu.

    If you cannot successfully connect to the sample database, make sure to set up your computer to use the JDataStore sample database. See "Setting up JDataStore for the samples" for more information.

  5. Click OK to exit the dialog and write the connection properties to the source code when the connection is successful.

    The source code, if the example above is followed, looks similar to this:

    database1.setConnection(new
    	com.borland.dx.sql.dataset.ConnectionDescriptor(
    	"jdbc:borland:dslocal:
    	/usr/local/jbuilder/samples/JDataStore/datastores/employee.jds",
    	, ,	false, "com.borland.datastore.jdbc.DataStoreDriver"));
    
  6. Select a DBDisposeMonitor component from the More dbSwing tab. Click in the content pane to add it to the application. The DBDisposeMonitor will close the JDataStore when the window is closed.

  7. Set the DBDisposeMonitor's dataAwareComponentContainer property to this.

Tip: Once a database URL connection is successful, you can use the JDBC Explorer to browse JDBC-based meta-database information and database schema objects in the JDataStore, and to execute SQL statements, and browse and edit data in existing tables.


Tutorial: Connecting to a database using InterClient JDBC drivers

This section discusses adding a Database component, which is a JDBC-specific component that manages a JDBC connection, and setting the properties of this component that enable you to access sample InterBase data.

In a real world database application, you would probably place the Database component in a data module. Doing so allows all applications that access the database to have a common connection. To learn more about data modules, see "Using data modules to simplify data access".

To add the Database component to your application and connect to the InterBase sample files,

  1. Make sure to follow the steps in "Setting up InterBase and InterClient" and "Adding a JDBC driver to JBuilder" to make sure your system is correctly set up for accessing the sample InterBase files.

  2. Make sure InterServer is running (it should be).

  3. Close all projects and create a new application, or add data connectivity to an existing project and application. You can create a new project and application files by selecting File|New, and double-clicking the Application icon. Select all defaults.

    JBuilder will create the necessary files and display them in the AppBrowser project pane. The file Frame1.java will be open in the content pane. Frame1.java will contain the user interface components for this application.

  4. Click the Design tab on Frame1.java at the bottom of content pane.

  5. Select the Data Express tab on the component palette, and click the Database component  db_icon.gif - 290 Bytes.

  6. Click anywhere in the content pane or UI designer to add the Database component to your application.

  7. Set the Database connection property to specify the JDBC driver, connection URL, user name, and password.

    The JDBC connection URL is the JDBC method for specifying the location of a JDBC data provider (i.e., SQL server). It can actually contain all the information necessary for making a successful connection, including user name and password.

    To set the connection property,

    1. Make sure the Database object is selected in the content pane. Double-click the connection property in the Inspector to open the connection property editor. In this example, the data resides on a Local InterBase server. If your data resides on a remote server, you would type the IP address of the server instead of "localhost" entered here.
    2. Set the following properties:

      PropertyValue
      Driver interbase.interclient.Driver
      URL Browse to the sample InterBase file, employee.gdb, located in your InterBase /examples directory. The entry in the URL field will look similar to this:
      Unix:
      jdbc:interbase://localhost//usr/interbase/examples/employee.gdb
      Windows:
      jdbc:interbase://localhost/D:\InterBaseCorp\InterBase\examples\database\employee.gdb

      Username SYSDBA
      Password masterkey

      The dialog looks like this:

      Connection descriptor dialog

    3. Click the Test Connection button to check that the connection properties have been correctly set. The connection attempt results are displayed directly beneath the Test Connection button. See "Common connection error messages" for solutions to some typical connection problems.

    4. Click OK to exit the dialog and write the connection properties to the source code when the connection is successful.

Tip: Once a database URL connection is successful, you can use the JDBC Explorer to browse JDBC-based meta-database information and database schema objects, as well as execute SQL statements, and browse and edit data in existing tables.

Common connection error messages

Listed below are some common connection errors and solutions:


Using the Database component in your application

Now that your application includes the Database component, you'll want to add another DataExpress component that retrieves data from the data source to which you are connected. JBuilder uses queries and stored procedures to return a set of data. The components implemented for this purpose are QueryDataSet and ProcedureDataSet. These components work with the Database component to access the SQL server database. For tutorials on how to use these components, see:

Most of the sample applications and tutorials use a Database connection to the sample EMPLOYEE JDataStore, as described here. A few of the sample applications and tutorials, particularly those that use stored procedures to retrieve or save data, use a connection to the InterBase employee database through the InterClient JDBC driver.

For most database applications, you would probably encapsulate the Database and other DataExpress components in a DataModule instead of directly adding them to an application's Frame. For more information on using the DataExpress package's DataModule, see "Using data modules to simplify data access".


Prompting for user name and password

When developing a database application, it is convenient to include a user name and password in the ConnectionDescriptor so that you do not have to supply this information each time you use the designer or run your application. If you set the ConnectionDescriptor through the designer, the designer writes the code for you. Before you deploy your application, you will probably want to remove the user name and password from the code, prompting the user for the information at runtime instead, particularly if you distribute the source code or if different users have different access rights. You have several options for prompting a user for their user name and password at runtime.


Pooling JDBC connections

For applications which require many database connections, you should consider connection pooling. Connection pooling provides significant performance gains, especially in cases where large numbers of database connections are opened and closed.

JDataStore provides several components for dealing with JDBC 2.0 DataSources and connection pooling. Use of these components requires J2EE. If your version of JBuilder does not include J2EE.jar, you will need to download it from Sun, and add it to your project as a required library. See "Adding a required library to a project" for instructions on adding a required library.

The basic idea behind connection pooling is simple. In an application that opens and closes many database connections, it is efficient to keep unused Connection objects in a pool for future re-use. This saves the overhead of having to open a new physical connection each time a connection is opened.

Here are the main DataSource and connection pooling components provided by JDataStore:

JDBCDataSource is an implementation of the javax.sql.DataSource interface. JDBCDataSource can create a connection to a JDataStore, or any other JDBC driver, based on its JDBC connection properties, but it does no connection pooling. Because it is an implementation of javax.sql.DataSource, it can be registered with a JNDI naming service. For information on JNDI naming services, consult the JDK documentation, or http://www.javasoft.com.

JDBCConnectionPool is also an implementation of javax.sql.DataSource, and therefore can be registered with a JNDI naming service. JDBCConnectionPool can be used to provide connection pooling with any JDBC driver. It creates connections based on its JDBC connection properties. JDBCConnectionPool has various properties for connection pool management, for instance, properties specifying a minimum and maximum number of connections.

When using JdbcConnectionPool, you are required to set the connectionFactory property. This allows JdbcConnectionPool to create javax.sql.PooledConnection objects. The connectionFactory property setting must refer to an implementation of javax.sql.ConnectionPoolDataSource (such as JdbcConnectionFactory). The connectionFactory property can also be set by using the dataSourceName property. The dataSourceName property takes a String, which it will look up in the JNDI naming service to acquire the implementation of javax.sql.ConnectionPoolDataSource.

To get a connection from the pool, you will usually call JdbcConnectionPool.getConnection(). The connection returned by this method does not support distributed transactions, but it can work with any JDBC driver.

JDBCConnectionPool also provides support for distributed transactions (XA), but this feature is only available when JDBCConnectionPool is used in conjunction with the JDataStore JDBC driver, and is only useful when combined with a distributed transaction manager, such as the Inprise Application Server. For more information on JDBCConnectionPool's XA support, see "Connection pooling and distributed transaction support" in the JDataStore Developer's Guide.

JdbcConnectionFactory is an implementation of javax.sql.ConnectionPoolDataSource. It is used to create javax.sql.PooledConnection objects for a connection pool implementation like JDBCConnectionPool.

JDBCConnectionPool and JDBCConnectionFactory are easily used together, but they can also each be used separately. The decoupling of these two components provides more flexibility. For example, JDBCConnectionFactory could be used with another connection pooling component which uses a different strategy than JDBCConnectionPool. JDBCConnectionFactory can be used with any JDBC 2.0 connection pool implementation that allows a javax.sql.ConnectionPoolDataSource implementation (like JDBCConnectionFactory) to provide its javax.sql.PooledConnections.

JDBCConnectionPool's efficient pooling strategy, on the other hand, could be used with another connection factory implementation. JDBCConnectionPool can be used with any JDBC driver that provides a connection factory component which implements javax.sql.ConnectionPoolDataSource.

Now that we've given you an overview of the classes involved in connection pooling, it's time to explain a bit more about how they work:

The JdbcConnectionPool.getConnection() method tries to save the overhead of opening a new connection by using a connection that is already in the pool. When a lookup is performed to find a connection in the pool, a match is found if the user name equals the user name that was originally used to create the pooled connection. Password is not considered when trying to match a user. A new connection is requested from the factory only if no match is found in the pool.

Connection pooling is a relatively simple, but very powerful API. Most of the difficult work, like keeping track of pooled connections, and deciding whether to use an existing pooled connection or open a new one, is done completely internally.

When an application uses connection pooling, a connection should always be explicitly closed when no longer in use. This allows the connection to be returned to the pool for later use, which improves performance.

The factory which creates connections for the pool should use the same property settings for all of them, except for the user name and password. A connection pool, therefore, accesses one database, and all its connections have the same JDBC connection property settings (but can have different usernames/passwords).

Optimizing performance of JConnectionPool

The lookup mechanism for finding a pooled connection that shares the same user name does a quick scan comparing user name string references. If possible, pass in the same String instance for all connection requests. One way to ensure this is to always use a constant name specified as follows for connection pooling:

public static final String POOL_USER = "CUSTOMER_POOL_USER";

Logging output

Both JdbcConnectionPool and JdbcConnectionFactory have PrintWriter properties. Most log output has the form of:

[<class instance hashcode>]:<class name>.<method name>(…)

Any hex values displayed in [] in the log files are hashCode() values for an Object.

Example:

The following is a trivial example of using connection pooling. This data module code fragment shows the most important and most basic lines of code you will need in an application using connection pooling, without making too many assumptions about what your specific application may need to do with this technology. For a non-trivial example of connection pooling, refer to the Web Bench sample in samples/JDataStore/WebBench. For more information about data modules, see "Using data modules to simplify data access".

import com.borland.dx.dataset.*;
import com.borland.dx.sql.dataset.*;
import com.borland.javax.sql.*;

public class DataModule1 implements DataModule {
  
  private static DataModule1 myDM;
  private static final String POOL_USER = "POOL_USER";
  private static JdbcConnectionFactory factory;
  private static JdbcConnectionPool pool;
  private static Database database1;

  public DataModule1() {
    try {
      jbInit();
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }

  private void jbInit() throws Exception {
  	// Instantiate connection factory
	factory = new JdbcConnectionFactory();
	
	// The next line sets the URL to a
	// local JDataStore file. The specific 
	// URL will depend on the location 
	// of your JDataStore file.
	factory.setUrl("jdbc:borland:dslocal:<path><file name>");
	factory.setUser(POOL_USER);
	factory.setPassword("");
	
	// Instantiate the connection pool
	pool = new JdbcConnectionPool();
	// Assign the connection factory as
	// the factory for this pool
	pool.setConnectionFactory(factory);
	
	// Instantiate a Database object
	database1 = new Database();
	// Assign the pool as the data source
	// for the Database object
	database1.setDataSource(pool);
  }

  public static DataModule1 getDataModule() {
    if (myDM == null) {
      myDM = new DataModule1();
    }
    return myDM;
  }

  public static JdbcConnectionPool getPool() {
    return pool;
  }

  public static Database getDatabase() {
    return database1;
  }

}
You will probably write the code for the application logic in a separate source file. The next code fragment shows how to request connections from the pool, and later, how to make sure the connections are returned to the pool. It also shows how to make sure the pool is shut down when the application ends.
public class doSomething {

  static DataModule1 dm = null;

  public doSomething() {
  }

  public static void main(String args[]){
  
  	// Several of the methods called here could
	// throw exceptions, so exception handling
	// is necessary.
	
	try{
		// Instantiate the data module
		dm = new DataModule1();
		java.sql.Connection con = null;
		
		// This application gets 100 connections
		// and returns them to the pool.
		for (int i=0; i<100; i++){
		
			try{
				// Get a connection
				con = dm.getPool().getConnection();
			}
			
			catch(Exception e){
				e.printStackTrace();
			}
			
			finally{
				// Return the connection to the pool
				con.close();
			}
      	}
    }
    catch(Exception x){
      x.printStackTrace();
    }
    finally{
		try{
			// Shut down the pool before the
			// the application exits.
			dm.getPool().shutdown();
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
    }


  }
}