Connecting to a database

The Database component handles the JDBC connection to a SQL server and is required for all database applications involving server data. JDBC is the JavaSoft Database Application Programmer Interface, a library of components and classes developed by JavaSoft 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. JDBC drivers can either be entirely written in Java so that they can be downloaded as part of an applet, or they can be implemented using native methods to bridge to existing database access libraries. For more information about JDBC, visit the JavaSoft JDBC Database Access API Web page at http://splash.javasoft.com/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. (See Installing JBuilder, JDBC, and the JDBC-ODBC Bridge.)

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 is that it can be downloaded as part of an applet and is cross-platform. Some of the driver options that ship with the JBuilder Client/Server edition are:

You can connect JBuilder applications to remote or local SQL databases, or to databases created with other Borland applications such as C++ Builder, Delphi, IntraBuilder, Paradox, or Visual dBASE. To do so, look at the underlying database that your application connects to and determine whether the database is a local or remote (SQL) database.

To connect to a remote SQL database, you need either of the following:

The two options when connecting to local, non-SQL databases such as Paradox or Visual dBASE are:

If you encounter any problems connecting to a JDBC database, see the topic Troubleshooting JDBC database connections in the tutorials.

This tutorial assumes you are familiar with the JBuilder design tools. For more information on these tools, see JBuilder's visual design tools in the Getting Started Guide.

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 Database classes which hold references to JDBC connections automatically close the connection when the Database object is garbage collected.

Adding a Database component to your application

The Database component is a JDBC-specific component that manages a JDBC connection. QueryDataSet and ProcedureDataSet components have a database property. Multiple data sets can share the same database, and often will.

To add the Database component to your application,

  1. Create a new project and application files using the Project and Application Wizards. (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 All 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 all defaults.

  2. Open the UI Designer by highlighting the Frame1.java file in the Navigation pane, then selecting the Design tab at the bottom of the AppBrowser.

  3. Select the Data Express tab from the Component Palette. Click the Database component.

    Database icon

  4. Click anywhere in the Component Tree 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 Component tree window:

    Database in Component tree

Setting Database connection properties

The Database connection property specifies the JDBC connection URL, User Name, Password, and optional JDBC driver(s). 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.

You can access the ConnectionDescriptor object programmatically or you can set connection properties through the user interface. The following steps describe how to do this in the UI Designer and provide the resulting code.

  1. Select the Database object in the Component Tree. Double-click the connection property in the Inspector window to open the connection property editor.

  2. Set the following properties:
    PropertyDescription

    Connection URL The Universal Resource Locator (URL) of the database, for example, jdbc:odbc:DataSet Tutorial. Select the Choose URL button to select an URL from a list of previously selected URLs, available data sources, or DataGateway sources.
    Username The user name authorized to access the server database, for example, SYSDBA.
    Password The password for the authorized user, for example, masterkey.
    Prompt user for password Whether to prompt the user for a password each time.
    Driver Class The class name of the JDBC driver that corresponds to the URL, for example, sun.jdbc.odbc.JdbcOdbcDriver.

  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. When successful, the dialog looks like this:

    Connection descriptor dialog

  4. 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 like this:

    database1.setConnection(new borland.sql.dataset.ConnectionDescriptor("jdbc:odbc:DataSet
        Tutorial", "SYSDBA", "masterkey", false, "sun.jdbc.odbc.JdbcOdbcDriver"));
    

Using the Database component in your application

Now that your application includes the Database component, you'll want to use a component that needs it. JBuilder uses queries and stored procedures to return a set of data. The components implemented for this purpose are QueryDataSet and ProcedureDataSet (ProcedureDataSet components are available in the Client/Server version). These components work with the Database component to access the SQL server database. For tutorials on how to use these components, see: If you work with a complex data model or need to change data components in your application, consider encapsulating 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 JBCL DataModule, see Separating database access logic and business rules from the user interface.