Simple Database Access Using the JDBC Interfaces in JAVA

Simple Database Access Using the JDBC Interfaces in JAVA 

An application for which database independence is paramount, in other words, one in which you want to write a program that can use different databases using a JDBC interface, should be written to the JDBC specification without using database-specific calls and without making use of SQL that is not part of the ANSI SQL-2 standard. In such code, no reference should be made to a specific implementation of JDBC. Writing a simple database application using only JDBC calls involves the following steps:

1. Ask the DriverManager for a Connection implementation.
2. Ask the Connection for a Statement or subclass of Statement to execute your SQL.
3. For subclasses of Statement, bind any parameters to be passed to the prepared statement.
4. Execute the Statement.
5. For queries, process the ResultSet returned from the query. Do this for each result set (if you have multiple result sets) until none are left.
6. For other statements, check the return value for the number of rows affected.
7. Close the Statement.
8. Process any number of such statements and then close the connection.

The Counter Applet Example

The counter applet discussed earlier in this chapter provides a simple example of JDBC programming. Using the JDBC interfaces, this applet connects to a database, determines how many times the page on which it appears has been hit, updates the page to reflect the new hit, and displays the number of hits. To use this example, you need a database engine to run your database and a JDBC driver to access that database engine. If you do not have a database engine, download mSQL and JDBC, which are both free for noncommercial use. Links to mSQL and the JDBC class may be found through

http://www.imaginary.com/Java/.

In addition, you need to create a table called t_counter with the fields counter_file (chAR(100), PRIMARY KEY) and counter_num (INT, NOT NULL). The following mSQL script creates the table:

DROP TABLE t_counter\p\g


CREATE TABLE t_counter(

        counter_file    chAR(100)    PRIMARY KEY,

        counter_num     INT          NOT NULL
)\p\g

The applet consists of two classes, Counter and Database. The Counter class is the subclass of applet that provides the user interface to the applet. It contains two instance variables: count, which is the number this applet is supposed to display, the number of page hits, and database, which is an instance of the Database class that provides wrappers for the JDBC access needed by the applet.
Counter does not define any new methods; rather, it simply overrides the java.applet.Applet.init() and java.applet.Applet.paint() methods. The init() method is used to create a Database instance and find out from it what the page hit count is for display. The paint() method displays the page hit count.
This interesting JDBC-related work is all encapsulated inside the Database class. This class has a single instance variable, connection, which is an instance of a JDBC Connection implementation. The connection variable is initialized in the Database class constructor:

public Database(String url, String user, String pass)
 throws java.sql.SQLException  {

     connection = DriverManager.getConnection(url, user, pass);

}

By getting an instantiated Connection object, the applet is ready to access whatever database it needs.

Tip:

As of the printing of this book, the java.sql package has not been incorporated into Java browsers such as Netscape. Due to a security feature of such browsers, which prevents the loading of classes in the java.* namespace, the applet examples in this chapter will not work properly. So how do I know they work at all? To get an applet using the java.sql classes to work, simply rename your java.sql packages to something else and recompile them. That moves them from the java.* namespace so that such browsers can load them. This problem does not affect stand-alone applications and it will not apply once the JDBC specification is finalized and java.sql classes are incorporated into the browser releases.

The applet uses the getCount() method to calculate how many page hits this particular access to the Web page represents. That seemingly benign query actually represents several steps:

1. Create a Statement object.
2. Formulate and execute the SELECT query.
3. Process the result.
4. Increment the hit count.
5. Format and execute an UPDATE or INSERT statement.
6. Close the Statement and Connection objects.

The Statement is created through the JDBC call:

java.sql.Statement statement = connection.createStatement();

You want the number of hits for this page from the t_counter table:

sql = "SELECT counter_num FROM t_counter " +

     "WHERE counter_file = '" + page + "'";

result_set = statement.executeQuery(sql);

The result_set variable now holds the results of the query. For queries that return multiple rows, an application loops through the next() method in the result set until no more rows exist. This query should only return one row with one column, unless the page has never been hit. If the page has never been hit, the query will not find any rows and the count variable should be set to 0:

if( !result_set.next() ) count = 0;

Otherwise, you need to retrieve that row into the count variable as an integer:

else count = result_set.getInt(1);

After incrementing the count to reflect this new hit, close out the Statement object and get a new one to prepare for the UPDATE:

count++;

statement.close();

statement = connection.create Statement();

If this is the first time the page is being hit, the applet needs to INSERT a new row into the database. Otherwise, it should UPDATE the existing row:

if( count == 1 ) {
    sql = "INSERT INTO t_counter " +

           "(counter_file, counter_num) " +

            "VALUES ('" + file + "', " + count + ")";
}

else {
    sql = "UPDATE t_counter " +

          "SET counter_num = " + count + " " +

           "WHERE counter_file = '" + file + "'";
}

statement.executeUpdate(sql);

The method then cleans up and returns the hit count.
Listing 15.1 puts the whole applet together.

---

Listing 15.1. The Counter applet.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.awt.Graphics;

public class Counter extends java.applet.Applet {

    Database db;

    String count;

    public void init() {

        String driver = getParameter("driver");

        String url = getParameter("url");
        String user = getParameter("user");

        String pass = getParameter("password");
        String page = getParameter("page");

        try {

            Class.forName(driver).newInstance();

            db = new Database(url, user, pass);

            count = db.getCount(page);

        }
   catch( java.sql.SQLException e ) {

            e.printStackTrace();
            count = "Database exception";

        }
   catch( Exception e ) {

            e.printStackTrace();

            count = "Unable to load driver";

        }

    }



    public void paint(Graphics g) {


        g.setFont(new java.awt.Font(getParameter("font"),
                                          &nbs p;     java.awt.Font.BOLD, 14));

        g.drawString(count, 5, 15);

}

}



class Database {

    private Connection connection;


    public Database(String url, String user, String pass)
 throws java.sql.SQLException {

    connection = DriverManager.getConnection(url, user, pass);

    }



public String getCount(String page) {

        int count = 0;


        try {
            java.sql.Statement statement =
                 connection.createStatement();

            java.sql.ResultSet result_set;

                   String sql;

           sql = "SELECT counter_num FROM t_counter " +
                                "WHERE counter_file = '" +
                page + "'";


          result_set = statement.executeQuery(sql);

            if( !result_set.next() ) count = 0;

                           else count = result_set.getInt(1);


            count++;

           statement.close();


            statement = connection.createStatement();

            if( count == 1 ) {

                           sql = "INSERT INTO t_counter " +
                                 "(counter_file, counter_num) " +
                                 "VALUES ('" + page + "', " +count+ ")";

            }  else {

                          sql = "UPDATE t_counter " +
                                "SET counter_num = " + count + " " +
                                "WHERE counter_file = '" + page + "'";

            }


            statement.executeUpdate(sql);
                   statement.close();
                   connection.close();

           }
  catch( java.sql.SQLException e ) {

            e.printStackTrace();

        }


    return ("" + count);

    }

}

---

Note:

How are drivers registered with the DriverManager? In the previous example, it was done by specifically loading the driver passed into the program through the driver parameter. A JDBC-compliant driver must notify the DriverManager of its existence when it is instantiated. The preferred method of listing multiple JDBC drivers for the DriverManager is through the jdbc.drivers property.