Return to index

JDBC

TJI includes an easy to use, simple database that makes developing projects that involve a database very easy. Of course, if you wish you can use any JDBC ('Java DataBase Connectivity') compliant database when developing your TJI java projects.

TJI includes a Database Management System (DBMS) and JDBC driver that allows your Java projects to access the TJI database. The 'DB' tab provides an easy to use, graphical Database Manager that you can use for creating tables, adding data and testing queries, among other things.

In TJI, all databases are stored in directory <homePath>/databases - or a subdirectory to match the database name. A table consists of 2 files. The first is essential and is the table definition file; it has the file extension .def. The second holds the actual data, if any; it has the file extension .kdb. TJI's database files follow an early dBase format which is simple and widely understood by many database systems. This doesn't necessarily matter much as one is likely to use a small test database while developing your project and the 'real' one on deployment. At deployment, your program will use a different driver and database, though a database with the same structure.

TJI's database can be used for developing all types of project - from JSP / Servlet web applications to Swing applications to simple console-based applications. Indeed, a simple console-based example project that creates, populates and queries two tables is available from our website. There is also an example project that queries a database from a JSP page, based on the entries in an HTML form.

Currently TJI's database provides a powerful set of basic functionality. Here is a summary of what is, and what is not yet, supported.

Data types currently supported :

Strings: CHAR(n), VARCHAR(n); n specifies the maximum length of the string type for that column.

Int type: INTEGER, INT, TINYINT, BIGINT

Float type: FLOAT, REAL, DECIMAL, DOUBLE

Boolean type: BINARY

Date type: DATE - Note that you must enter dates in standard SQL format : yyyy-mm-dd

Conditions

WHERE supports AND but not yet OR for combining simple conditions.

Conditions must take the form of A=B or A<>B where A must be a column name and B can be either another column name or a value. A>B, A<B, A>=B and A<=B are also supported for all numeric types and the type Date. In the case of Dates, A<B is true if A is 'before' B (that is, older).

LIKE : In SQL, '_' is used to mean any character in that position, and '%' any number of characters in that position (including 0). Currently, TJI's LIKE only supports the patterns %abc (ends with abc), abc% (starts with abc) and %abc% (contains sequence abc).

Not yet supported : DISTINCT, ORDER BY, IN, BETWEEN, GROUP BY and functions (such as AVG and COUNT).

Next we will discuss how to open a connection with your DBMS, and then, since what JDBC does is to send your SQL code to your DBMS, we will demonstrate some SQL code. After that, we will show you how easy it is to use JDBC to pass these SQL statements to your DBMS - and process the results that are returned.

 

JDBC

Establishing a Connection

The first thing you need to do is establish a connection with the DBMS you want to use. This involves two steps:

(1) loading the driver, and

(2) making the connection.

1. Loading the Driver

Loading the driver (or drivers) you want to use is very simple and involves just one line of code for each. If, for example, you want to use the JDBC-ODBC Bridge driver, the following code will load it:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

To use TJI's JDBC Driver (which you can only use within TJI), you would use the following line of code:

Class.forName("KDriver");

You do not need to create an instance of a driver and register it with the DriverManager because calling Class.forName will do that for you automatically.

When you have loaded a driver, it is available for making a connection with a matching DBMS.

2. Making the Connection

The second step in establishing a connection is to have the appropriate driver connect to the DBMS. The following line of code illustrates the general idea:

Connection con = DriverManager.getConnection
      (url, "myLogin", "myPassword");

This step is also simple, with the hardest thing being what to supply for url. If you are using the JDBC-ODBC Bridge driver, the JDBC URL will start with jdbc:odbc:. The rest of the URL is generally your data source name or database system. So, if you are using ODBC to access an ODBC data source called "Fred", for example, your JDBC URL could be jdbc:odbc:Fred. In place of "myLogin" you put the name you use to log in to the DBMS; in place of "myPassword" you put your password for the DBMS. So if you log in to your DBMS with a login name of "Fernanda" and a password of "J8", the following two lines of code will establish a connection:

 String url = "jdbc:odbc:Fred";
Connection con = DriverManager.getConnection
      (url, "Fernanda", "J8");

If you are using a JDBC driver developed by a third party, the documentation will tell you what subprotocol to use, that is, what to put after jdbc: in the JDBC URL. For example, if the driver developer has registered the name acme as the subprotocol, the first and second parts of the JDBC URL will be jdbc:acme:. The driver documentation will also give you guidelines for the rest of the JDBC URL. This last part of the JDBC URL supplies information for identifying the data source.

In TJI, login name and password are not used. The database name part of the url is not required by TJI - TJI will create / look for tables in the 'default' database in this case. The default database tables will be found in directory <homePath>/databases. This directory will be created automatically by TJI. So the following code will make a connection to TJI's database:

String url = "jdbc:KDB:";
Connection con = DriverManager.getConnection(url, "", "");

If you wish to create your tables in distinct directories so that each set of tables has its own database name, you can do so by specifying a databse name. This name will be used as the subdirectory name. So, suppose you wish to create or access a database named 'Foo'. The directory where the table files will be stored will be <homePath>/databases/Foo and the code to make the connection will be

String url = "jdbc:KDB:Foo";
Connection con = DriverManager.getConnection(url, "", "");

Note that TJI will create the subdirectory automatically if it does not already exist.

If one of the drivers you loaded recognizes the JDBC URL supplied to the method DriverManager.getConnection (in particular, the sub-protocal name), that driver will establish a connection to the DBMS specified in the JDBC URL. The DriverManager class, true to its name, manages all of the details of establishing the connection for you behind the scenes. Unless you are writing a driver, you will probably never use any of the methods in the interface Driver, and the only DriverManager method you really need to know is DriverManager.getConnection.

The connection returned by the method DriverManager.getConnection is an open connection you can use to create JDBC statements that pass your SQL statements to the DBMS. In the previous example, con is an open connection, and we will use it in the examples that follow.

A program sends messages to the DBMS in a format called SQL. Let's take a look at an example SQL statement. The following example creates a new table.

CREATE TABLE coffees
   (name CHAR(32),
    supplier_id INTEGER)

This code does not end with a DBMS statement terminator, which can vary from DBMS to DBMS. For example, Oracle uses a semicolon (;) to indicate the end of a statement, and Sybase uses the word 'GO' . The driver you are using will automatically supply the appropriate statement terminator, and you will not need to include it in your JDBC (that is, Java) code.

In the CREATE TABLE statement, key words are written in all capital letters, and each item is on a separate line. SQL does not require either; these conventions simply make statements easier to read and are widely followed practices (particularly the use of uppercase). Table and column names are case-sensitive, as are any quoted values.

So far we have written the SQL statement that creates the table coffees. Now let's put quotation marks around it (to make it a string) and assign that string to a variable called createTableCoffees so that we can use the variable in our JDBC code.

As just shown, the DBMS does not care about where lines are divided, but in the Java programming language, a String object that extends beyond one line will not compile. Consequently, when you write a string over more than one line, you need to enclose each line in quotation marks and use a plus sign (+) to concatenate them. For example:

String createTableCoffees = "CREATE TABLE coffees " +
    "(name CHAR(32), supplier_id INTEGER)";

The data types used in the CREATE TABLE statement are generic SQL types (also called 'JDBC types') that are defined in the class java.sql.Types. DBMSs generally use these standard types.


Creating JDBC Statements

A Statement object is what holds and then sends your SQL statement to the DBMS. You simply create a Statement object and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For a SELECT statement, the method to use is executeQuery. For statements that create or modify tables, the method to use is executeUpdate.

It takes an instance of an active connection to create a Statement object. In the following example, the Connection object con is used to create the Statement object stmt:

Statement stmt = con.createStatement();

At this point stmt exists, but it does not have an SQL statement to pass on to the DBMS. We need to supply that to the method we use to execute stmt. For example, in the following code fragment, we supply executeUpdate with the SQL statement from the example above:

stmt.executeUpdate("CREATE TABLE coffees " +
    "(name CHAR(32), supplier_id INTEGER)");

Because we made a string out of the SQL statement and assigned it to the variable createTableCoffees, we can write the code in this simpler form:

stmt.executeUpdate(createTableCoffees);

Executing Statements

We used the method executeUpdate because the SQL statement contained in createTableCoffees is a DDL (data definition language) statement. Statements that create a table, alter a table, or drop (i.e. delete) a table are all examples of DDL statements and are executed with the method executeUpdate.

As you might expect from its name, the method executeUpdate is also used to execute SQL statements that update a table. In practice, executeUpdate is used far more often to update tables than it is to create them because a table is created once but may be updated many times.

The method used for executing queries is executeQuery. This method is used to execute SELECT statements, which comprise the vast majority of SQL statements. You will see how to use this method shortly.


Entering Data into a Table

We have shown how to create the table coffees by specifying the names of the columns and the data types to be stored in those columns, but this only sets up the structure of the table. The table does not yet contain any data.

We will now enter our data into the table one row at a time, supplying the information to be stored in each column of that row. Note that the values to be inserted into the columns must be listed in the same order that the columns were declared when the table was created, which is the default order.

The following code inserts one row of data, with Colombian in the column name and 101 in supplier_id . Just as we did in the code that created the table coffees, we will create a Statement object and then execute it using the method executeUpdate.

Because the SQL statement will not quite fit on one line on the page, we have split it into two strings concatenated by a plus sign (+) so that it will compile. Pay special attention to the need for a space between coffees and VALUES. This space must be within the quotation marks and may be after coffees or before VALUES; without a space, the SQL statement will erroneously be read as "INSERT INTO coffeesVALUES . . ." .

Statement stmt = con.createStatement();
stmt.executeUpdate(
    "INSERT INTO coffees " +
    "VALUES ('Colombian', 101)");

Because double quotes are used to define the Java string, it is easiest to use single quotes for any quoted values (quotes are used for string type values), although SQL accepts either.

The code that follows inserts a second row into the table coffees. Note that we can just reuse the Statement object stmt rather than having to create a new one for each execution.

stmt.executeUpdate("INSERT INTO coffees " +
    "VALUES ('French_Roast', 49)");

Values for the remaining rows can be inserted as follows:

stmt.executeUpdate("INSERT INTO coffees " +
    "VALUES ('Espresso', 150)");
stmt.executeUpdate("INSERT INTO coffees " +
    "VALUES ('Colombian_Decaf', 101)");
stmt.executeUpdate("INSERT INTO coffees " +
    "VALUES ('French_Roast_Decaf', 49)");

 

Getting Data from a Table

Now that the table coffees has values in it, we can write a SELECT statement to access those values. The star (*) in the following SQL statement indicates that all columns should be selected. Since there is no WHERE clause to narrow down the rows from which to select, the following SQL statement selects and returns the whole table:

SELECT * FROM coffees

The result, which is the entire table, will look similar to the following: 

name		        			supplier_id
---------------			--------------
Colombian					9
French_Roast				3
Espresso						15
Colombian_Decaf			7
French_Roast_Decaf		12

The result above is what you would see if you entered the SQL query directly to the database system. In TJI you can do this in the 'DB' tab. When we access a database through a Java application, we will need to retrieve the results so that we can process and use them. You will see how to do this in the next section.

Here is another example of a SELECT statement; this one will get just a list of coffee names:

SELECT name FROM coffees

The results of this query will look something like this:

name	
--------	
Colombian	
French_Roast	
Espresso	
Colombian_Decaf	
French_Roast_Decaf

To specify multiple columns, simply place a comma between column names. For example:

SELECT name, supplier_id FROM coffees

The SELECT statement above generates the names and supplier_ids of all of the coffees in the table. The following SQL statement limits (filters) the coffees selected to those which have a supplier_id of less than 10 by using a WHERE clause with one simple condition - supplier_id < 10

SELECT name, supplier_id
FROM coffees
WHERE supplier_id < 10

The result would look similar to this:

name						supplier_id
--------					-------
Colombian				9
French_Roast			3
Colombian Decaf	 	7

 

Retrieving Values from Result Sets

We now show how you send the above SELECT statements from a program written in the Java programming language and how you get the results we showed.

JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. The following code demonstrates declaring the ResultSet object rs and assigning the results of our earlier query to it:

ResultSet rs = stmt.executeQuery
      ("SELECT name, supplier_id FROM coffees");

Using the Method next

The variable rs , which is an instance of ResultSet, contains the rows of names and supplier_ids shown in the result set example above. In order to access the names and supplier_ids, we will go to each row and retrieve the values according to their types.

The method next moves what is called a 'cursor' to the next row and makes that row (called the 'current row') the one upon which we can operate. Since the cursor is initially positioned just above the first row of the ResultSet object, the first call to method next moves the cursor to the first row and makes it the current row. Successive invocations of method next move the cursor down one row at a time from top to bottom.

With the JDBC 2.0 API, covered in the next section, you can move the cursor backwards, to specific positions, and to positions relative to the current row in addition to simply moving the cursor forward.

Using the getXXX Methods

We can use the getXXX method of the appropriate type to retrieve the value in each column. For example, the first column in each row of rs is name, which stores a value of SQL type CHAR. The method for retrieving a value of SQL type CHAR is getString. The second column in each row stores a value of SQL type INTEGER, and the method for retrieving values of that type is getInteger.

The following code accesses the values stored in the current row of rs and prints a line with the name followed by three spaces and the price. Each time the method next is invoked, the next row becomes the current row, and the loop continues until there are no more rows in rs.

String query = "SELECT name, supplier_id FROM coffees";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
      String s = rs.getString("name");
      int n = rs.getInt("supplier_id");
      System.out.println(s + "   " + n);
}

The output will look something like this:

Colombian   9
French_Roast   3
Espresso   15
Colombian_Decaf   7
French_Roast_Decaf   12


Using the ResultSetMetaData Class

An instance of this class is created for each ResultSet and can be obtained with method rs.getMetaData(). This class holds information on the structure of the table, such as the number of columns and the column names. The following code excerpt illustrates how one might print out the the column names of result set instance rs.

ResultSetMetaData meta=rs.getMetaData();
int num_cols=meta.getColumnCount();
for (int i=1; i<=num_cols; i++) {
      String col_name=meta.getColumnName(i);
      System.out.println(col_name);
}

Other useful ResultSetMetaData methods :

  • java.sqlTypes getColumnType(int column)
  • String getColumnTypeName(int column)
  • int getColumnDisplaySize(int column)

Database Connectivity from JSP pages

The following example illustrates one way to run an SQL query from a JSP page and process the results into an HTML table. It uses a tag handler to accomplish this and converts the result set to a Collection of Collections that is easily processed by two embedded standard c:forEach tags. The sql:select tag used mirrors that in the standard classes. The source code for this tag handler is available from our web site together with the JSP project.

<html>
    <body>
        <table>
            <sql:query 
                 sql="SELECT id, car_name FROM cars" var="data" />
            <c:forEach items="${data}" var="row">
                <tr>
                    <c:forEach items="${row}" var="x">
                        <td> <b> ${x} </b> </td>
                    </c:forEach>
                </tr>
            </c:forEach>
        </table>
    </body>
</html>

Tag sql:query places the Collection of Collections it creates from the result set into an attribute named 'data'. 'data' is a Collection of rows; each row is itself a Collection - of all the column values. The first c:forEach tag iterates through each row in the result set Collection 'data', making each row Collection available as an attribute called 'row'. The second c:forEach tag then iterates through each column value in each row Collection. Expression language is used to get the values of the attributes set by the c:forEach tags.

It is also possible to allow parts of an SQL statement to be filled in by a form - that is, expression language and an additional param tag can rewrite the SQL statement based on the parameters sent from a form to the handing JSP page. Here is the key part of the form-handing JSP page. Notice the <sql:param> tag.

<sql:query
   var="data"
   sql="SELECT name, car_name 
             FROM cars, people 
             WHERE pe_id=? AND car_id=id" >
          <sql:param value="${param.pe_id}" />
</sql:query>

Each ? in the sql:query tag's sql attribute is replaced, in order, with the values in the sql:param tags within its body. See DB example project 3 for all the source code - the form, form handler JSP page, and the SQL tag handlers.

This approach - using SQL tags - is fine for simple tasks although there is a case to be made that business logic - such as the SQL statement - should not be in the presentation layer (JSP page). To achieve this separation, and to make more complex use of database connectivity from a JSP page, or pages, we can turn to the use of Java Beans.

We suggest you download the example DB projects from our website ('Resources' page) and experiment with them. You can also examine the tables they create in TJI's Database Manager ('DB' tab).

The next guide discusses SQL statements in more detail.

 

Download and run ...

Example JDBC / SQL projects are available from the 'Resources' page of our web site.

 

Note: some parts of this document are based on the JDBC tutorial available at http://java.sun.com.


Return to index