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:
DataStoreDriver
DataStoreDriver is the JDBC driver for the JDataStore
database. The driver supports both local and remote access. Both types of access require a user name (any string, with no setup required).
For information on connecting to a database using the JDataStore driver, see "Tutorial: Connecting to a database using the JDataStore
JDBC driver".
InterClient
InterClient is a JDBC driver that you can use to connect to InterBase. InterClient can be installed by running the InterClient installation program. Once installed, InterClient can access InterBase sample data using the ConnectionDescriptor
.
For information on connecting to a database using InterClient, see "Tutorial: Connecting to a database using InterClient".
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.
JDataStore
JDBC driverThis tutorial assumes you are familiar with the JBuilder design tools. If not, see the online help topic "Designing a user interface."
This tutorial outlines:
Database
component to your application"
Database
connection properties"
Database
component in your application"
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.
Database
component to your applicationThe 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,
Frame1.java
in the content pane, then select the Design tab at the bottom of the AppBrowser.Select the DataExpress tab from the component palette. Click the Database
component.
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
connection propertiesThe 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:
promptPassword
to true
, you should also call openConnection()
for your database. openConnection()
determines when the password dialog is displayed and when the database connection is made.
openConnection()
at the end of the main frame's jbInit()
method.
If you don't explicitly open the connection, it will try to open when a component or data set first needs data.
The following steps describe how to set connection properties through the UI designer to the sample JDataStore
database.
JDataStore
. If you have not already done so, see "Setting up JDataStore
for the samples".
database1
in the component tree.
connection
property's value in the Inspector, and click the ellipsis button to open the Connection property editor.
Set the following properties:
Property | Description |
---|---|
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: |
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:
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.
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"));
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.
Set the DBDisposeMonitor's dataAwareComponentContainer
property to this
.
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,
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.
Make sure InterServer is running (it should be).
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.
Click the Design tab on Frame1.java
at the bottom of content pane.
Select the Data Express tab on the component palette, and click the Database
component .
Click anywhere in the content pane or UI designer to add the Database
component to your application.
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,
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. Set the following properties:
Property | Value |
---|---|
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:
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.
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.
Driver could not be loaded. InterClient has not been added to the CLASSPATH. Add the interclient.jar file to the JBuilder startup script CLASSPATH, or to your environment's CLASSPATH before launching JBuilder.
Database
component in your applicationDatabase
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".
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.
Database
connection
property, or write code to set the ConnectionDescriptor
promptPassword
parameter to true
.
At runtime and when you show live data in the Designer, a user name and password dialog will display. A valid user name and password must be entered before data will display.
Add an instance of dbSwing
DBPasswordPrompter
to your application.
This option gives you more control over user name/password handling. You can specify what information is required (only user name, only password, or both), how many times the user can attempt to enter the information, and other properties. The OK button will be disabled until the necessary information is supplied. The dialog is displayed when you call its showDialog()
method. This allows you to control when it appears. Be sure to present it early in your application, before any visual component tries to open your database and display data. The designer doesn't call showDialog()
, so you need to specify user name and password in the ConnectionDescriptor
when you activate the designer.
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).
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";
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
.
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(); } } } }