Database Connection using Java

Which database management system?

The goal of this part is to show you how best we can combine Database and prolog. We have shown you how to call prolog on Servlet. We will show you in next section how to connect prolog to Relational Database on Servlet/Java, but before that we have to learn how to use Relational Database on Servlet/Java.

The first question should be, then, "Which database shall we employ?" Sincerely speaking, we do not have a best solution because there is no free, open-source relational database runnable on Windows 9X. We may try one of the followings:

• MySQL on Linux: We install MySQL on Linux, calling it from our PC.
• PostgreSQL on Linux: We install PostgreSQL on Linux, calling it from our PC.
• MySQL on PC: We install MySQL for Windows on our PC.
• Access on PC: We install Access on our PC.

My first choice is the first option, that is, we install MySQL on a Linux machine and call the database from our PC running on Windows9X. Why? well, I suppose that you work on Windows9X only for developing software, not for running your server to respond to the requests from public. In the end you may want to install your web applications on more reliable machines, i.e, a PC running on Linux. It makes sense, therefore, to install the database management system on the PC running on Linux, to which you will import your web applications for public access. Duplicating a database on your PC for development and the other running as webserver, is not good idea at all.

It is more sensitive question, why we opt for MySQL, not for PostgreSQL. Generally speaking, MySQL is smaller and efficient than PostgreSQL, but some of the functionalities available with PostgreSQL are missing. I believe that MySQL is sufficient to building web applications for experimentation. We may have to employ PostgreSQL to build bigger systems in the future, but for the moment we only build small systems to study how we can import ideas from AI to Web. For rapid prototyping, we prefer MySQL for its efficiency to PostgreSQL.

There are a number of good texts on JDBC. I do therefore not go into detail in the following and only explain the points which you might miss.

Download and Installation

Installing MySQL Server

You can download the package from MySQL Page. You can find in the package a detailed instruction for installing mySQL on Linux.

Register yourself

After installing MySQL server, you have to add yourself to the user list. The crucial point in registering yourself is to grant you to get an access to the server from a remote PC other than localhost. If you allow yourself only to login the server on localhost, you will never establish a connection from your PC to the server through LAN.

Consult the section 6.13 titled "Adding new user privileges to MySQL" of MySQL Rererence Manual (mine is Version 3.23.7-alpha). To repeat the explanation, you can grant yourself the previlege to login the server with the right to do anything you like from any machine on the net as follows:

shell> mysql -u root -p
Enter password: (Here you enter the password for root)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38 to server version: 3.22.27

Type 'help' for help.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> GRANT ALL PRIVILEGES ON *.* TO fuji@"%"
       IDENTIFIED BY 'YourPassword' WITH GRANT OPTION;

shell> mysql -h ks15e0f00 -u fuji -p

Check finally if you have registered yourself as you intended. That is, you login the server as explained above, choosing the database, 'mysql'. Execute the command, 'select * from user;', then. You will see your host privilege displayed as '%' in the first column if everything went well.

Installing JDBC driver

My preference goes to MM MySQL JDBC Driver developed by Mark Matthews. Download the package from his web page. The latest is the version 2.0.2, whose file you can find as mm.mysql-2.0.2-bin.jar (10.7.2000). To install the driver, simply place the jar file in your classpath.

MySQL Basics

We go through very basics of MySQL. The material given here is apparently insufficient even to build toy systems. The reader not familiar with relational databases and SQL, is strongly rocommended to consult other texts on these topics.

Creating Databases

We build a sample database which will store the pairs of people who stand in 'child_of' relation. Let 'mysqltest' be the database. To create the database, you type in as follows:

mysql> CREATE DATABASE mysqltest;
Query OK, 1 row affected (0.00 sec)

Creating Tables

Before creating a table in a database, you have to first choose the database as your working database. To choose 'mysqltest' database, type in the command as below:

mysql> use mysqltest;
Database changed

We create a table to keep records on the people who stand in child_of relation. An image of the table is depicted as follows:

mysql> CREATE TABLE child_of
    -> (child CHAR(10),
    -> parent CHAR(10));
Query OK, 0 rows affected (0.09 sec)

mysql> show tables;
+---------------------+
| Tables in mysqltest |
+---------------------+
| child_of            |
+---------------------+
1 row in set (0.00 sec)

You add to the table the pair of 'joe' and 'ralf' as follows:

mysql> INSERT into child_of (child, parent) VALUES('joe', 'ralf');
Query OK, 1 row affected (0.00 sec)

mysql> select * from child_of;
+-------+--------+
| child | parent |
+-------+--------+
| joe   | ralf   |
+-------+--------+
1 row in set (0.00 sec)

mysql> INSERT into child_of (child, parent) VALUES('mary', 'joe');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into child_of (child, parent) VALUES('steve', 'joe');
Query OK, 1 row affected (0.00 sec)

mysql> select * from child_of;
+-------+--------+
| child | parent |
+-------+--------+
| joe   | ralf   |
| mary  | joe    |
| steve | joe    |
+-------+--------+
3 rows in set (0.00 sec)

Database connection

Prerequisite

We use the JDBC driver to connect to our mySQL server from Java programs. We assume that you have already installed mm.mysql, a JDBC driver for MySQL, from Mark Matthews' web page. Installation is simple; you only need to place mm.mysql-2.0.2-bin.jar in your classpath.

Sample Code

We show you the simplest program which logs in our MySQL server to read and display all the records in child_of table of mysqltest database. Below is the sample code:

/*
  A sample code to connect to mySQL server using mm.mysql(2.0.2)
*/

import java.io.*;
import java.sql.*;

public class JDBCSample
{
    public static void main( String argv[] ) 
    {
        try {
            Class.forName( "org.gjt.mm.mysql.Driver" );
            String url = "jdbc:mysql://ks15e0f00/mysqltest?user=YourID&password=YourPassword";
            Connection con = DriverManager.getConnection( url );

            Statement select = con.createStatement();
            String sql = "select * from child_of";
            sql = new String(sql.getBytes("SJIS"), "8859_1" );
            ResultSet res = select.executeQuery( sql );

            int count = 0;
            while( res.next() ) {
                count++;
                String result = res.getString( "child" ) + "\t" + res.getString( "parent" );
                result = new String( result.getBytes("8859_1"), "SJIS" );
                System.out.println( result );
            }
            System.out.println( "Record count=" + count );
            select.close();
            con.close();
        }
        catch ( Exception e ) {
            e.printStackTrace();
        }
    }
}

Test-run

Before going into detail, we had better show you how it works. You have to compile JDBCSample.java by typing 'javac JDBCSample.java'. You can then execute the program as follows:

~/docs/fp/programs/jdbc> java JDBCSample
joe     ralf
mary    joe
steve   joe
Record count=3

Explanation about the code

Packages

You have to use the packages under java.sql.

import java.io.*;
import java.sql.*;

Establishing a connection

The first line loads the driver for connecting the MySQL server. The second line specifies the database to connect with its machine name and the name of the database (i.e., 'mysqltest'), followed by your userID and password. The third line establishes the connedtion.

Class.forName( "org.gjt.mm.mysql.Driver" );
String url = "jdbc:mysql://ks15e0f00/mysqltest?user=YourID&password=YourPassword";
Connection con = DriverManager.getConnection( url );

Executing the query

The first line prepares for the statement to be sent to MySQL server. The second line construct the query sentence. The third line will convert a query coded in SJIS (Japanese) to binary. (We store data coded in Japanese as binary on our MySQL server. You do not need to include this line if you will not use Japanese characters.) The last line execute the query to receive the result to 'res'.

Statement select = con.createStatement();
String sql = "select * from child_of";
sql = new String(sql.getBytes("SJIS"), "8859_1" );
ResultSet res = select.executeQuery( sql );

Reading out the result

We go read out the result line by line. The first line sets the counter to zero, which will be referred to for printing out the number of lines retrieved at end. In the while loop, we read out the data in the columns of 'child' and 'parent' and concatenate them to store the result in 'result'. The fourth line is for converting the data format to SJIS from binary. The result is printed out to the last line. At end, the number of lines is displayed.

int count = 0;
while( res.next() ) {
    count++;
    String result = res.getString( "child" ) + "\t" + res.getString( "parent" );
    result = new String( result.getBytes("8859_1"), "SJIS" );
    System.out.println( result );
}
System.out.println( "Record count=" + count );

Closing up

We finally finish the query and close the connection.

select.close();
con.close();