6. Database Management with the JDBC API

6.1. Overview

There are many databases on the market. To standardize database access under MS Windows, Microsoft developed an interface called ODBC (Open DataBase Connectivity). This layer hides the specific features of each database behind a standard interface. There are many ODBC drivers available under MS Windows that facilitate database access. Here, for example, is a list of ODBC drivers installed on a Win95 machine:

An application relying on these drivers can use any of the above databases without modification.

To enable Java applications to also take advantage of the ODBC interface, Sun created the JDBC (Java Database Connectivity) interface, which acts as an intermediary between the Java application and the ODBC interface:

6.2. Key steps in database operations

6.2.1. Introduction

In a Java application using a database with the JDBC interface, the following steps are generally involved:

1. Connecting to the database
2. Sending SQL queries to the database
3. Receiving and processing the results of these queries
4. Closing the connection

Steps 2 and 3 are performed repeatedly, with the connection being closed only at the end of database operations. This is a relatively standard pattern that you may be familiar with if you have used a database interactively. We will detail each of these steps using an example. We will consider an Access database named ARTICLES with the following structure:

name	type
code	4-character item code
name	its name (string)
price	its price (actual)
current_stock	current stock (integer)
min_stock	the minimum stock (integer) below which the item must be restocked

This ACCESS database is defined as a "user" data source in the ODBC Database Manager:

Its properties are specified using the Configure button as follows:

This configuration essentially involves associating the ARTICLES database with the Access file articles.mdb corresponding to this database. Once this is done, the ARTICLES database is accessible to applications using the ODBC interface.

6.2.2. The connection step

To use a database, a Java application must first establish a connection. This is done using the following class method:

Connection DriverManager.getConnection(String URL, String id, String mdp)

where

DriverManager: a Java class containing the list of drivers available to the application

Connection: a Java class that establishes a link between the application and the database, through which the application will send SQL queries to the database and receive results

URL: name identifying the database. This name is analogous to Internet URLs. This is why it is part of the URL class. However, the Internet is not involved here at all. The URL has the following form:

    **jdbc:driver\_name:source\_name;param=val1;param2=val2**

In our examples, where we will use only ODBC drivers, the driver is called **odbc**. The third part of the URL consists of the source name and any parameters. In our examples, these will be ODBC sources known to the system. Thus, the URL for the *Articles* data source defined earlier will be

    **jdbc:odbc:Articles**

id: User ID (login)

mdp: User password

In summary, the program connects to a database:

• identified by a name (URL)
• under a user’s credentials (id, password)

If these three parameters are correct, and if the driver capable of establishing the connection between the Java application and the specified database exists, then a connection is established between the Java application and the database. This connection is represented in the program by the Connection object returned by the DriverManager class. Since this connection may fail for various reasons, it may throw an exception. We will therefore write:

    Connection connection=null;
    URL base=...;
    String id = ...;
    String password = ...;
try{
        connection = DriverManager.getConnection(base, id, mdp);
} catch (Exception e){
        // handle the exception
}

To connect to a database, you must have the appropriate driver. In our examples, this will be the ODBC driver capable of handling the requested database. While this driver must be available in the list of ODBC drivers on the machine, you must also have the Java class that will interface with it. To do this, the application will request the necessary class as follows:

    Class.forName(String className)

The Class class is in no way related to the JDBC interface. It is a general class management class. Its static method forName allows a class to be loaded dynamically, thereby making its static attributes and methods available. The class that interfaces with the MS Windows ODBC drivers is called “sun.jdbc.odbc.JdbcOdbcDriver”. We would therefore write (the method may throw an exception):

try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch (Exception e){
    // handle the exception (class does not exist)
}

The classes required for the JDBC interface are located in the java.sql package. Therefore, at the beginning of the program, we write:

    import java.sql.*;

Here is a program that allows you to connect to a database:

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

// call: pg DRIVER URL UID PASSWORD
// Connects to the database URL using the JDBC driver
// the user UID is identified by a password MDP

public class connection1{
    static String syntax = "pg DRIVER URL UID PASSWORD";

    public static void main(String arg[]){
        // Check the number of arguments
        if(arg.length < 2 || arg.length > 4)
            error(syntax, 1);
        // connect to the database
        Connection connect = null;
        String uid = "";
        String password = "";
        if (arg.length >= 3) uid = arg[2];
        if (arg.length == 4) mdp = arg[3];        
        try{
            Class.forName(arg[0]);
            connect = DriverManager.getConnection(arg[1], uid, password);
            System.out.println("Connection to database " + arg[1] + " established");
        } catch (Exception e){
            error("Error " + e, 2);
        }
        // Close the database
        try{
            connect.close();
            System.out.println("Database " + arg[1] + " closed");
        } catch (Exception e){}
    }// main

    public static void error(String msg, int exitCode){
        System.err.println(msg);
        System.exit(exitCode);
    }
}// class    

Here is an example of execution:

E:\data\java\jdbc\0>java connection1 sun.jdbc.odbc.JdbcOdbcDriver jdbc:odbc:articles
Connection to the jdbc:odbc:articles database established
The jdbc:odbc:articles database has been closed

6.2.3. Sending queries to the database

The JDBC interface allows SQL queries to be sent to the database connected to the Java application, as well as the processing of the results of these queries. SQL (Structured Query Language) is a standardized query language for relational databases. There are several types of queries:

• database query statements (SELECT)
• database update queries (INSERT, DELETE, UPDATE)
• queries for creating/deleting tables (CREATE, DELETE)

We assume here that the reader is familiar with the basics of the SQL language.

6.2.3.1. The Statement class

To send any SQL query to a database, the Java application must have an object of type Statement. This object will store, among other things, the text of the query. This object is necessarily linked to the current connection. It is therefore a method of the established connection that allows you to create the Statement objects needed to issue SQL queries. If connection is the object representing the connection to the database, a Statement object is obtained as follows:

    Statement query = connection.CreateStatement();

Once a Statement object has been obtained, SQL queries can be executed. This is done differently depending on whether the query is a query to retrieve data or to update the database.

6.2.3.2. Executing a query to retrieve data from the database

A query is typically of the following type:

    select col1, col2,... from table1, table2,...
    where condition
    order by expression
    ...

Only the keywords in the first line are required; the others are optional. There are other keywords not shown here.

1. A join is performed with all tables listed after the `FROM` keyword
2. Only the columns following the `select` keyword are retained
3. Only the rows that satisfy the condition of the `where` keyword are retained
4. The resulting rows, sorted according to the expression in the `ORDER BY` keyword, form the result of the query.

The result of a SELECT is a table. If we consider the previous ARTICLES table and want the names of the items whose current stock is below the minimum threshold, we would write: SELECT name FROM articles WHERE current\_stock < min\_stock**. If we want them sorted alphabetically by name, we would write: select name from articles where current_stock < minimum_stock order by name

To execute this type of query, the Statement class provides the executeQuery method:

    ResultSet executeQuery(String query)

where query is the text of the SELECT query to be executed.

Thus, if

• connection is the object representing the connection to the database
• Statement s = connection.createStatement() creates the Statement object needed to execute SQL queries
• ResultSet rs = s.executeQuery("select name from articles where current_stock < minimum_stock") executes a SELECT query and assigns the query's result rows to an object of type ResultSet.

6.2.3.3. The ResultSet class: result of a SELECT query

A ResultSet object represents a table, i.e., a set of rows and columns. At any given time, only one row of the table is accessible, known as the current row. Upon initial creation of the ResultSet, the current row is row #1 if the ResultSet is not empty. To move to the next row, the ResultSet class provides the next method:

    boolean next()

This method attempts to move to the next row in the ResultSet and returns true if successful, false otherwise. If successful, the next row becomes the new current row. The previous row is lost and cannot be retrieved. The ResultSet table has columns col1, col2, ... To access the various fields of the current row, the following methods are available:

Type getType("coli") 

to retrieve the "coli" field from the current row. Type refers to the type of the coli field. The getString method is often used on all fields, which allows you to retrieve the field’s content as a string. You can then convert it if necessary. If you do not know the column name, you can use the methods

Type getType(i) 

where i is the index of the desired column (i>=1).

6.2.3.4. A first example

Here is a program that displays the contents of the ARTICLES database created earlier:

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

// displays the contents of a system database named ARTICLES

public class articles1{

    static final String DB = "ARTICLES";        // database to use

    public static void main(String arg[]){

        Connection connect = null;        // connection to the database
        Statement S = null;                // object for executing queries
        ResultSet RS = null;            // result table from a query
        try{
            // database connection
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connect = DriverManager.getConnection("jdbc:odbc:" + DB, "", "");
            System.out.println("Connection to database " + DB + " established");
            // Create a Statement object
            S = connect.createStatement();
            // executing a SELECT query
            RS = S.executeQuery("select * from " + DB);
            // Iterate through the result set
            while(RS.next()){                // as long as there is a row to process
                // display it on the screen
                System.out.println(RS.getString("code") + "," +
                    RS.getString("name")+","+
                    RS.getString("price") + "," +
                    RS.getString("current_stock")+","+
                    RS.getString("min_stock"));
            }// next line
        } catch (Exception e){
            error("Error " + e, 2);
        }
        // Close the database
        try{
            connect.close();
            System.out.println("Database " + DB + " closed");
        } catch (Exception e){}
    }// main

    public static void error(String msg, int exitCode){
        System.err.println(msg);
        System.exit(exitCode);
    }
}// class    

The results are as follows:

Connection to the ARTICLES database established
a300,bike,1202,30,2
d600,bow,5000,10,2
d800,canoe,1502,12,6
x123,rifle,3000,10,2
s345,water skis,1800,3,2
f450,trial3,3,3,3
f807,sperm whale,200000,0,0
z400,leopard,500000,1,1
g457,panther,800000,1,1
ARTICLES database closed

6.2.3.5. The ResultSetMetadata class

In the previous example, we know the names of the ResultSet columns. If we don’t know them, we cannot use the getType(column_name) method. Instead, we use getType(column_number). However, to obtain all the columns, we would need to know how many columns the ResultSet has. The ResultSet class does not provide this information. It is the ResultSetMetaData class that provides it. More generally, this class provides information about the table’s structure, i.e., the nature of its columns.

We access information about the structure of a ResultSet by first instantiating a ResultSetMetaData object. If RS is a ResultSet, the associated ResultSetMetaData is obtained by:

    RS.getMetaData()

There are two useful methods in the ResultSetMetaData class:

1. int getColumnCount(), which returns the number of columns in the ResultSet
2. String getColumnLabel(int i), which returns the name of column i in the ResultSet (i >= 1)

6.2.3.6. A second example

The previous program displayed the contents of the ARTICLES database. Here, we write a program that executes any SQL SELECT query the user types on the keyboard against the ARTICLES database.

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

// displays the contents of the ARTICLES system database

public class sql1{

    static final String DB = "ARTICLES";        // database to use

    public static void main(String arg[]){

        Connection connect = null;        // connection to the database
        Statement S = null;                // object for executing queries
        ResultSet RS = null;            // result table from a query
        String select;                    // text of the SQL SELECT query
        int numColumns;                // number of columns in the ResultSet

        // Create a keyboard input stream
        BufferedReader in = null;
        try{
            in = new BufferedReader(new InputStreamReader(System.in));
        } catch (Exception e) {
            error("Error opening keyboard stream ("+e+")", 3);
        }
        try{
            // Connect to the database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connect = DriverManager.getConnection("jdbc:odbc:" + DB, "", "");
            System.out.println("Connection to database " + DB + " established");
            // Create a Statement object
            S = connect.createStatement();
            // Loop to execute SQL queries typed at the keyboard
            System.out.print("Query: ");
            select = in.readLine();
            while(!select.equals("end")){
                // execute the query
                RS = S.executeQuery(select);
                // number of columns
                nbColumns = RS.getMetaData().getColumnCount();
                // process the result set
                System.out.println("Results obtained\n\n");
                while(RS.next()){                // as long as there is a row to process
                    // display it on the screen
                    for(int i=1;i<nbColumns;i++)
                        System.out.print(RS.getString(i)+",");
                    System.out.println(RS.getString(nbColumns));
                }// next line
                // next query
                System.out.print("Query: ");
                select = in.readLine();                
            }// while
        } catch (Exception e){
            error("Error " + e, 2);
        }
        // Close the database and the input stream
        try{
            connect.close();
            System.out.println("Database " + DB + " closed");
            in.close();
        } catch (Exception e){}
    }// main

    public static void error(String msg, int exitCode){
        System.err.println(msg);
        System.exit(exitCode);
    }
}// class    

Here are some of the results obtained:

Connection to the ARTICLES database established
Query: select * from articles order by price desc
Results obtained


g457,panther,800000,1,1
z400,leopard,500000,1,1
f807,sperm whale,200000,0,0
d600,bow,5000,10,2
x123,rifle,3000,10,2
s345,water skis,1800,3,2
d800,canoe,1502,12,6
a300,bicycle,1202,30,2
f450,test3,3,3,3

Query: select name, price from items where price >10000 order by price desc
Results obtained


panther,800000
leopard,500000
sperm whale,200000

6.2.3.7. Execute a database update query

A Statement object is used to store SQL queries. The method this object uses to execute SQL update queries (INSERT, UPDATE, DELETE) is no longer the executeQuery method discussed earlier, but the executeUpdate method:

    int executeUpdate(String query)

The difference lies in the result: while executeQuery returned the result set (ResultSet), executeUpdate returns the number of rows affected by the update operation.

6.2.3.8. A third example

We’ll revisit the previous program and modify it slightly: the queries typed on the keyboard are now update queries for the ARTICLES database.

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

// displays the contents of the ARTICLES system database

public class sql2{

    static final String DB = "ARTICLES";    // database to use

    public static void main(String arg[]){

        Connection connect = null;        // connection to the database
        Statement S = null;                // object for executing queries
        ResultSet RS = null;            // result table from a query
        String sqlUpdate;                // text of the SQL update query
        int numberOfRows;                    // number of rows affected by an update

        // Create a keyboard input stream
        BufferedReader in = null;
        try{
            in = new BufferedReader(new InputStreamReader(System.in));
        } catch (Exception e) {
            error("Error opening keyboard stream ("+e+")", 3);
        }
        try{
            // Connect to the database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connect = DriverManager.getConnection("jdbc:odbc:" + DB, "", "");
            System.out.println("Connection to database " + DB + " established");
            // Create a Statement object
            S = connect.createStatement();
            // Loop to execute SQL queries typed at the keyboard
            System.out.print("Query: ");
            sqlUpdate = in.readLine();
            while(!sqlUpdate.equals("end")){
                // execute the query
                nbLines = S.executeUpdate(sqlUpdate);
                // tracking
                System.out.println(nbLines + " row(s) were updated");
                // next query
                System.out.print("Query: ");
                sqlUpdate = in.readLine();                
            }// while
        } catch (Exception e){
            error("Error " + e, 2);
        }
        // Close the database and the input stream
        try{
            // release resources associated with the database
            RS.close();
            S.close();
            connect.close();
            System.out.println("Database " + DB + " closed");
            // Close keyboard input
            in.close();
        } catch (Exception e){}
    }// main

    public static void error(String msg, int exitCode){
        System.err.println(msg);
        System.exit(exitCode);
    }
}// class    

Here are the results of various runs of the sql1 and sql2 programs:

List of rows in the ARTICLES database:

E:\data\java\jdbc\0>java sql1
Connection to the ARTICLES database established
Query: select name,current_stock from articles
Results obtained


bike,30
bow,10
canoe,12
rifle,10
water skiing, 3
trial, 3.3
sperm whale,0
leopard,1
panther,1

We modify certain lines:

E:\data\java\jdbc\0>java sql2
Connection to the ARTICLES database established
Query: update articles set stock_actu=stock_actu+1 where stock_actu>10
2 rows were updated

Verification:

E:\data\java\jdbc\0>java sql1
Connection to the ARTICLES database established
Query: select name, current_stock from articles
Results obtained

bike,31
bow,10
canoe,13
rifle,10
water skiing, 3
trial, 3.3
sperm whale,0
leopard, 1
panther,1

Add a line:

E:\data\java\jdbc\0>java sql2
Connection to the ARTICLES database established
Query: insert into articles (code, name, price, current_stock, min_stock) values ('x400', 'new', 200, 20, 10)
1 row(s) updated

Verification:

E:\data\java\jdbc\0>java sql1
Connection to the ARTICLES database established
Query: select name,current_stock from articles
Results obtained

bike,31
bow,10
canoe,13
rifle,10
water skiing, 3
trial, 3.3
sperm whale,0
leopard, 1
panther,1
new,20

Deleting a row:

E:\data\java\jdbc\0>java sql2
Connection to the ARTICLES database established
Query: delete from articles where code='x400'
1 row(s) updated
Query: end

Verification:

E:\data\java\jdbc\0>java sql1
Connection to the ARTICLES database established
Query: select name,current_stock from articles
Results obtained


bike,31
bow,10
canoe,13
rifle,10
water skiing,3
trial,3,3
sperm whale,0
leopard,1
panther,1

6.2.3.9. Execute any SQL query

The Statement object required for executing SQL queries has an execute method capable of executing any type of SQL query:

    boolean execute(String query)

The returned value is the Boolean true if the query returned a ResultSet* (executeQuery*), and false if it returned a number (executeUpdate*). The resulting ResultSet can be retrieved using the getResultSet method, and the number of updated rows using the getUpdateCount* method. Thus, we would write:

Statement S=...;
ResultSet RS = ...;
int numRows;
String query=...;
// execute an SQL query
if (S.execute(query)){
    // we have a result set
    RS = S.getResultSet();
    // process the ResultSet
    ...
} else {
    // it was an update query
    nbLines = S.getUpdateCount();
    ...
}

6.2.3.10. Fourth example

We take the concept from programs sql1 and sql2 and apply it to a program sql3 that can now execute any SQL query typed on the keyboard. To make the program more general, the characteristics of the database to be used are passed as parameters to the program.

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

// call: pg DRIVER URL UID PASSWORD
// Connects to the database URL using the JDBC driver
// the user UID is identified by a password MDP

public class sql3{

    static String syntax = "pg DRIVER URL UID PASSWORD";

    public static void main(String arg[]){



        // Check the number of arguments
        if(arg.length < 2 || arg.length > 4)
            error(syntax, 1);

        // initialize connection parameters
        Connection connect = null;
        String uid = "";
        String password = "";
        if (arg.length >= 3) uid = arg[2];
        if(arg.length==4) password=arg[3];        

        // other data
        Statement S = null;                        // query execution object
        ResultSet RS = null;                    // result table from a query
        String sqlText;                            // text of the SQL query to execute
        int numberOfRows;                                // number of rows affected by an update
        int numberOfColumns;                            // number of columns in a ResultSet

        // create a keyboard input stream
        BufferedReader in = null;
        try{
            in = new BufferedReader(new InputStreamReader(System.in));
        } catch (Exception e) {
            error("Error opening keyboard stream ("+e+")", 3);
        }
        try{
            // Connect to the database
            Class.forName(arg[0]);
            connect = DriverManager.getConnection(arg[1], uid, mdp);
            System.out.println("Connection to database " + arg[1] + " established");
            // Create a Statement object
            S = connect.createStatement();
            // Loop to execute SQL queries typed at the keyboard
            System.out.print("Query: ");
            sqlText = in.readLine();
            while(!sqlText.equals("end")){
                // execute the query
                try{
                    if(S.execute(sqlText)){
                        // we have obtained a ResultSet - we process it
                        RS = S.getResultSet();
                        // number of columns
                        nbColumns = RS.getMetaData().getColumnCount();
                        // Process the result set
                        System.out.println("\nResults obtained\n-----------------\n");
                        while(RS.next()){                // as long as there is a row to process
                            // display it on the screen
                            for(int i=1;i<nbColumns;i++)
                                System.out.print(RS.getString(i)+",");
                            System.out.println(RS.getString(nbColumns));
                        }// next row of the ResultSet
                    } else {
                        // it was an update query
                        nbRows = S.getUpdateCount();
                        // follow-up
                        System.out.println(nbLines + " row(s) were updated");
                    }//if
                } catch (Exception e){
                    System.out.println("Error " + e);
                }
                // next request
                System.out.print("\nNew Request: ");
                sqlText = in.readLine();                
            }// while
        } catch (Exception e){
            error("Error " + e, 2);
        }
        // Close the database and the input stream
        try{
            // release resources associated with the database
            RS.close();
            S.close();
            connect.close();
            System.out.println("Database " + arg[1] + " closed");
            // Close keyboard input
            in.close();
        } catch (Exception e){}
    }// main

    public static void error(String msg, int exitCode){
        System.err.println(msg);
        System.exit(exitCode);
    }
}// class    

We create the following query file:

select * from articles
update articles set stock_min=stock_min+5 where stock_min<5
SELECT name, min_stock FROM items
insert into articles (code, name, price, current_stock, minimum_stock) values ('x400', 'new', 100, 20, 10)
SELECT * FROM items
delete from products where code='x400'
SELECT * FROM items
end

The program is run as follows:

E:\data\java\jdbc\0>java sql3 sun.jdbc.odbc.JdbcOdbcDriver jdbc:odbc:articles <queries>results

The program reads its input from the queries file and writes its output to the results file. The results obtained are as follows:

Connection to the jdbc:odbc:articles database established

Query: (query 1 from the queries file: select * from articles)
Results obtained
-----------------

a300,bike,1202,31,3
d600,bow,5000,10,3
d800,canoe,1502,13,7
x123,rifle,3000,10,3
s345,water skis,1800,3,3
f450,trial3,3,3,4
f807,sperm whale,200000,0,1
z400,leopard,500000,1,2
g457,panther,800000,1,2

New Query: (query 2 from the query file: update articles set stock_mini=stock_mini+5 where stock_mini<5)

8 rows have been updated

New Query: (query 3 from the query file: select name, stock_min from items)

Results obtained
-----------------

bike,8
bow,8
canoe,7
rifle,8
water skis, 8
trial, 3.9
sperm whale,6
leopard,7
panther,7

New Query: (query 4 from the queries file: insert into articles (code,name,price,current_stock,min_stock) values ('x400','new',100,20,10))

1 row(s) updated

New Query: (query 5 from the query file: select * from articles)

Results obtained
-----------------

a300,bike,1202,31,8
d600,bow,5000,10,8
d800,canoe,1502,13,7
x123,rifle,3000,10,8
s345,water skis,1800,3,8
f450,trial3,3,3,9
f807,sperm whale,200000,0,6
z400,leopard,500000,1,7
g457,panther,800000,1,7
x400,new,100,20,10

New Query: (query 6 from the query file: delete from articles where code='x400')


1 row(s) were updated

New Query: (query 7 from the query file: select * from articles)

Results obtained
-----------------

a300,bike,1202,31,8
d600,bow,5000,10,8
d800,canoe,1502,13,7
x123,rifle,3000,10,8
s345,water skis,1800,3,8
f450,trial3,3,3,9
f807,sperm whale,200000,0,6
z400,leopard,500000,1,7
g457,panther,800000,1,7

6.3. Tax Calculation with a database

The last time we addressed the problem of calculating taxes, we used a graphical interface and the data was stored in a file. We’ll revisit this version, now assuming the data is in an ODBC-MySQL database. MySQL is an open-source DBMS that can be used on various platforms, including Windows and Linux. With this DBMS, a database named dbimpots has been created, containing a single table called impots. Access to the database is controlled by a username/password, in this case admimpots/mdpimpots. The screenshot shows how to use the dbimpots database with MySQL:

C:\Program Files\EasyPHP\mysql\bin>mysql -u admimpots -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 3.23.49-max-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use dbimpots;
Database changed

mysql> show tables;
+--------------------+
| Tables_in_dbimpots |
+--------------------+
| taxes             |
+--------------------+
1 row in set (0.00 sec)

mysql> describe taxes;
+---------+--------+------+-----+---------+-------+
| Field   | Type   | Null | Key | Default | Extra |
+---------+--------+------+-----+---------+-------+
| limits | double | YES  |     | NULL    |       |
| coeffR  | double | YES  |     | NULL    |       |
| coeffN  | double | YES  |     | NULL    |       |
+---------+--------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> select * from taxes;
+---------+--------+---------+
| limits | coeffR | coeffN  |
+---------+--------+---------+
|   12620 |      0 |       0 |
|   13,190 |   0.05 |     631 |
|   15640 |    0.1 |  1290.5 |
|   24,740 |   0.15 |  2,072.5 |
|   31,810 |    0.2 |  3,309.5 |
|   39,970 |   0.25 |    4,900 |
|   48,360 |    0.3 |    6,898 |
|   55,790 |   0.35 |  9,316.5 |
|   92,970 |    0.4 |   12,106 |
|  127,860 |   0.45 |   16,754 |
|  151,250 |    0.5 | 23,147.5 |
|  172,040 |   0.55 |   30,710 |
|  195,000 |    0.6 |   39,312 |
|       0 |   0.65 |   49,062 |
+---------+--------+---------+
14 rows in set (0.00 sec)

mysql>quit

The application's graphical interface is as follows:

The graphical interface has undergone some changes:

No.	type	name	role
1	JTextField	txtConnection	ODBC database connection string
2	JScrollPane	JScrollPane1	container for the Textarea 3
3	JTextArea	txtStatus	displays status messages, including error messages

The connection string entered in (1) has the following format: DSN;login;password with

DSN	being the DSN name of the ODBC data source
login	the identity of a user with read access to the database
password	their password

The dbimpots database was created manually using MySQL. It is converted into an ODBC data source as follows:

• Launch the 32-bit ODBC Data Source Administrator

• use the [Add] button to add a new ODBC data source

• Select the MySQL driver and click [Finish]

• The MySQL driver requests some information:

1	The DSN name to give the ODBC data source—it can be anything
2	the machine on which the MySQL DBMS is running—here, localhost. It is worth noting that the database could be a remote database. Local applications using the ODBC data source would not notice this. This would be the case, in particular, for our Java application.
3	the MySQL database to use. MySQL is a DBMS that manages relational databases, which are sets of tables linked together by relationships. Here, we specify the name of the database being managed.
4	The name of a user with access rights to this database
5	their password

Once the ODBC data source has been defined, we can test our program:

Let's take a look at the code that has been modified compared to the graphical version without a database. Here is the code for the *impots* class used so far:

// creation of a `taxes` class

public class taxes{

    // the data needed to calculate the tax
    // comes from an external source

    private double[] limits, taxRate, taxN;

    // constructor
    public Taxes(double[] LIMITS, double[] COEFFR, double[] COEFFN) throws Exception{
        // we check that the 3 arrays are the same size
        boolean OK = LIMITS.length == COEFFR.length && LIMITS.length == COEFFN.length;
        if (!OK) throw new Exception("The three arrays provided do not have the same size("+
                                LIMITES.length + "," + COEFFR.length + "," + COEFFN.length + ""));
        // All good
        this.limits = LIMITS;
        this.coeffR = COEFFR;
        this.coeffN = COEFFN;
    }//constructor

    // tax calculation
    public long calculate(boolean married, int numChildren, int salary){
        // calculate the number of shares
        double nbParts;
        if (married) nbParts = (double)nbChildren / 2 + 2;
        else nbParts = (double)nbChildren / 2 + 1;
        if (numberOfChildren >= 3) numberOfShares += 0.5;
        // Calculate taxable income & family quotient
        double income = 0.72 * salary;
        double QF = income / nbParts;
        // calculate tax
        limits[limits.length-1] = QF + 1;
        int i = 0;
        while(QF > limits[i]) i++;
        // return result
        return (long)(income * coeffR[i] - nbParts * coeffN[i]);
    }//calculate
}//class

This class constructs the three arrays limits, coeffR, and coeffN from three arrays passed as parameters to its constructor. We decide to add a new constructor that allows us to construct the same three arrays from a database:

  public impots(String dsnIMPOTS, String userIMPOTS, String mdpIMPOTS)
      throws SQLException, ClassNotFoundException{

    // dsnIMPOTS: DSN name of the database
    // userIMPOTS, mdpIMPOTS: database login/password

For this example, we decide not to implement this new constructor in the *impots* class but in a derived class, *impotsJDBC*:

// Imported packages
import java.sql.*;
import java.util.*;

public class impotsJDBC extends impots{
  // Add a constructor to create
  // the limits, coeffr, and coeffn arrays from the
  // impots table in a database
  public impotsJDBC(String dsnIMPOTS, String userIMPOTS, String mdpIMPOTS)
      throws SQLException, ClassNotFoundException {

    // dsnIMPOTS: DSN name of the database
    // userIMPOTS, mdpIMPOTS: login/password for accessing the database

    // data arrays
    ArrayList aLimites = new ArrayList();
    ArrayList aCoeffR = new ArrayList();
    ArrayList aCoeffN = new ArrayList();

    // database connection
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection connect = DriverManager.getConnection("jdbc:odbc:" + dsnIMPOTS, userIMPOTS, mdpIMPOTS);
    // Create a Statement object
    Statement S = connect.createStatement();
    // SELECT query
    String select = "select limites, coeffr, coeffn from impots";
    // Execute the query
    ResultSet RS = S.executeQuery(select);
    while(RS.next()){
      // process the current row
      aLimites.add(RS.getString("limites"));
      aCoeffR.add(RS.getString("coeffr"));
      aCoeffN.add(RS.getString("coeffn"));
    }// next line
    // close resources
    RS.close();
    S.close();
    connect.close();
    // transfer data to bounded arrays
    int n = aLimits.size();
    limits = new double[n];
    coeffR = new double[n];
    coeffN = new double[n];
    for (int i = 0; i < n; i++) {
      limits[i] = Double.parseDouble((String)aLimits.get(i));
      coeffR[i] = Double.parseDouble((String)aCoeffR.get(i));
      coeffN[i] = Double.parseDouble((String)aCoeffN.get(i));
    }//for
  }//constructor
}//class

The constructor reads the contents of the impots table from the database passed to it as parameters and populates the three arrays: limites, coeffR, and coeffN. A number of errors may occur. The constructor does not handle them but "passes them up" to the calling program:

  public impotsJDBC(String dsnIMPOTS, String userIMPOTS, String mdpIMPOTS)
      throws SQLException, ClassNotFoundException {

If we look closely at the previous code, we can see that the impotsJDBC class directly uses the limits, coeffR, and coeffN fields of its base class impots. Since these are declared private:

    private double[] limites, coeffR, coeffN;

the impotsJDBC class does not have direct access to these fields. We therefore make an initial modification to the base class by writing:

  protected double[] limites = null;
  protected double[] coeffR = null;
  protected double[] coeffN = null;

The protected attribute allows classes derived from the impots class to have direct access to fields declared with this attribute. We need to make a second modification. The constructor of the child class impotsJDBC is declared as follows:

  public impotsJDBC(String dsnIMPOTS, String userIMPOTS, String mdpIMPOTS)
      throws SQLException, ClassNotFoundException {

We know that before constructing an object of a child class, we must first construct an object of the parent class. To do this, the child class’s constructor must explicitly call the parent class’s constructor using a super(....) statement. Here, this is not done because we cannot see which constructor of the parent class we could call. There is currently only one, and it is not suitable. The compiler will then search the parent class for a parameterless constructor that it could call. It does not find one, and this generates a compilation error. We therefore add a parameterless constructor to our impots class:

  // empty constructor
  protected impots(){}

We declare it as "protected" so that it can only be used by child classes. The skeleton of the impots class now looks like this:

public class taxes{

  // the data needed to calculate the tax
  // comes from an external source

  protected double[] limits = null;
  protected double[] coeffR = null;
  protected double[] coeffN = null;

  // empty constructor
  protected impots(){}

  // constructor
  public impots(double[] LIMITS, double[] COEFFR, double[] COEFFN) throws Exception{
...........
  }//constructor

  // tax calculation
  public long calculate(boolean married, int numChildren, int salary){
.............
    }//calculate
}//class

The action for the Initialize menu in our application becomes the following:

  void mnuInitialize_actionPerformed(ActionEvent e) {
    // retrieve the connection string
    Pattern separator = Pattern.compile("\\s*;\\s*");
    String[] fields = separator.split(txtConnection.getText().trim());
    // there must be three fields
    if(fields.length!=3){
      // error
      txtStatus.setText("Incorrect connection string (DSN;uid;mdp)");
      // return to the user interface
      txtConnection.requestFocus();
      return;
    }//if
    // load data
    try{
      // create the impotsJDBC object
      objImpots = new impotsJDBC(fields[0], fields[1], fields[2]);
      // confirmation
      txtStatus.setText("Data loaded");
      // The salary can be modified
      txtSalary.setEditable(true);
      // no further changes allowed
      mnuInitialize.setEnabled(false);
      txtConnection.setEditable(false);
    } catch (Exception ex) {
      // problem
      txtStatus.setText("Error: " + ex.getMessage());
      // end
      return;
    }//catch
  }

Once the objImpots object has been created, the application is identical to the graphical application already written. The reader is invited to refer to it.

6.4. Exercises

6.4.1. Exercise 1

Provide a graphical interface for the previous sql3 program.

6.4.2. Exercise 2

A Java applet can only access a database through the server from which it was loaded. Since an applet does not have access to the disk of the machine on which it is running, the database cannot be on the client machine using the applet. We are therefore in the following situation:

The machine running the applet, the server, and the machine hosting the database may be three different machines. Here, we assume that the database is located on the server.

Problem 1

Write the following server application in Java:

• The server application runs on a port passed to it as a parameter
• When a client connects, the server application sends the message

200 - Welcome - Send your request

• The client then sends the parameters needed to connect to a database and the query it wants to execute in the following format:

Java driver/base URL/UID/PW/SQL query

The parameters are separated by a slash. The first four parameters are those of the sql3 program described in this chapter.

• The server application then establishes a connection with the specified database, which must be located on the same machine as the server, and executes the SQL query on it. The results are returned to the client in the following format:

100 - row1
100 - row2

...

if it is the result of a Select query, or

101 - nbLines

to return the number of rows affected by an update query. If a database connection error or query execution error occurs, the application returns

500 - Error message

• Once the query has been executed, the server application closes the connection.

Problem 2

Create a Java applet that queries the server described above. You may draw inspiration from the graphical interface in Exercise 1. Since the server port may vary, it will be entered via the applet’s interface. The same applies to all parameters required to send the row:

Java driver/base URL/UID/password/SQL query

that the client must send to the server.

6.4.3. Exercise 3

The following text describes a problem originally intended to be solved in Visual Basic. Adapt it for processing in Java within an applet. This applet will rely on the server from Exercise 2. The graphical interface may be modified to account for the new execution context.

We propose to create an application that highlights the various possible update operations on a table in an ACCESS database. The ACCESS database is named articles.mdb. It has a single table named articles that lists the items sold by a company. Its structure is as follows:

name	type
code	4-character item code
name	its name (string)
price	its price (actual)
current_stock	current stock (integer)
min_stock	the minimum stock (integer) below which the item must be restocked

We propose to view and update this table using the following form:

The controls on this form are as follows:

No.	type	Name	Function
1	textbox	record	Number of the displayed record enabled is false
2	textbox	code	item code
3	textbox	name	item name
4	textbox	price	item price
5	textbox	current	current stock of the item
6	textbox	minimum	minimum stock of the item
7	data	data1	Data control associated with the database databasename=path to the articles.mdb file recordsource=articles connect=access
8	HScrollBar	position	allows you to navigate the table
9	button	OK	allows you to confirm an update - appears only during an update
10	button	Cancel	allows you to cancel an update - appears only during the update
11	frame	frame1	for aesthetic purposes
12	textbox	basename	name of the open database enabled is set to false
13	textbox	sourcename	name of the open table enabled is set to false

---

Creating the sheet in VB

---

control	Special features
data1	The databasename and recordsource fields are filled in. databasename must refer to the Access database articles.mdb in your directory, and recordsource to the articles table.
code	This is a text box that we want to link to the code field of the current record in data1. To do this, we fill in two fields: datasource: Enter data1 to indicate that the text box is linked to the table associated with data1 datafield: select the code field from the articles table After these steps, the code text box will always contain the code field of the current record in data1. Conversely, changing the content of this text box will update the code field of the current record. Do the same for the other textboxes
name	datasource: data1 datafield: name
price	data source: data1 data field: price
current	data source: data1 data field: current_stock
minimum	data source: data1 data field: stock_min

---

The menus

---

The menu structure is as follows

Edit	Browse	Exit
Add	Back
Edit	Next
Delete	First
	Last

The roles of the various options are as follows:

Menu	name	function
Add	mnuadd	to add a new record to the items table
Delete	delete	to delete the currently displayed record from the items table
Edit	edit	to edit the currently displayed record in the items table
Previous	mnuprecedent	to go to the previous record
Next	down-next	to skip to the next recording
First	prev	to skip to the first track
Last	mnunlast	to skip to the last recording
Exit	mnuquit	to exit the application

---

Loading the sheet

---

During the form_load event, the table associated with data1 is opened (data1.refresh). If the table fails to open, an error message is displayed and the program terminates (end). Otherwise, the form is displayed with the first record from the articles table visible. No input is allowed in the fields (enabled property set to false). Input is only possible using the Add and Edit options. The OK and Cancel buttons are hidden (visible=false).

---

Part 1

---

We propose to build the procedures related to the various menu options as well as the OK and Cancel buttons. For now, we will ignore the following points:

• enabling/disabling certain menu options: for example, the Next option must be disabled if the cursor is on the last record in the table
• managing the horizontal scrollbar

Browse/Next menu

• moves to the next record (data1.RecordSet.MoveNext) if we are not at the end of the file (data1.RecordSet.EOF). Updates the record textbox (data1.RecordSet.AbsolutePosition / data1.RecordSet.RecordCount).

Browse/Previous menu

• moves to the previous record (data1.RecordSet.MovePrevious) if not at the beginning of the file (data1.RecordSet.BOF). Updates the record textbox.

Browse/First menu

• Moves to the first record (data1.RecordSet.MoveFirst) if the file is not empty (data1.RecordSet.RecordCount = 0). Updates the record textbox.

Browse/Last menu

• Moves to the last record (data1.RecordSet.MoveLast) if the file is not empty (data1.RecordSet.RecordCount = 0). Updates the record textbox.

Edit/Add menu

• allows you to add a record to the table
• enters Add Record mode (data1.recordset.addnew)
• Enables data entry in the 5 fields: code, name, price, etc. (enabled=true)
• Disables the Edit, Browse, and Exit menus (enabled=false)
• displays the OK and Cancel buttons (visible=true)

OK button

• saves a record update (data1.recordset.Update)
• hides the OK and Cancel buttons (visible=false)
• enables the Edit, Browse, and Exit options (enabled=true)
• updates the form text box

Cancel button

• cancels a record update (data1.recordset.CancelUpdate)
• hides the OK and Cancel buttons (visible=false)
• enables the Edit, Browse, and Exit options (enabled=true)
• updates the form text box

Edit/Modify menu

• allows you to edit the record displayed on the form
• enters record edit mode (data1.recordset.edit)
• Enables input in the 4 fields: name, price, etc. (enabled=true) but not in the code field (enabled=false)
• disables the Edit, Browse, and Exit menus (enabled=false)
• displays the OK and Cancel buttons (visible=true)

Edit/Delete menu

• allows you to delete (data1.recordset.delete) the displayed record from the table
• moves to the next record (data1.recordset.movenext)

Exit menu

• unloads the form (unload me)

form_unload event (cancel as integer)

• triggered by the unload me operation or by closing the form using Alt-F4 or a double-click on the system tray icon, so not necessarily by the exit option.
• displays the question “Do you really want to exit the application?” with two Yes/No buttons (msgbox with style=vbyes+vbno)
• if the answer is No (=vbno), set cancel to -1 and exit the form_unload procedure. cancel set to -1 indicates that closing the window is denied.
• If the answer is Yes (=vbyes), the database is closed (data1.recordset.close, data1.database.close).

---

Part 2 - Menu Management

---

Here, we focus on enabling/disabling menus. After each operation that changes the current record, we will call a procedure that we can call Oueston. This procedure will check the following conditions:

. If the file is empty, we

• disable the Browse, Edit/Delete menus,
• enable the others

. If the current record is the first record, we

• disable Browse/Previous
• will allow the rest

. If the current record is the last one,

• disable Browse/Next
• will allow the rest

---

Part 3 - Managing the horizontal slider

---

A horizontal slider has three important fields:

• min: its minimum value
• max: its maximum value
• value: its current value

Initializing the slider

Upon loading (form_load), the slider will be initialized as follows:

• min=0
• max=data1.recordset.recordcount-1
• value=1

Note that immediately after opening the database (data1.refresh), the number of records in the table represented by data1.recordset.recordcount is incorrect. You must move to the end of the table (MoveLast), then return to the beginning of the table (MoveFirst) for it to be correct.

Direct action on the drive

The position of the slider’s cursor represents the position in the table.

When the user changes the slider (named position here), the position_change event is triggered. In this event, we will change the current record in the table so that it reflects the movement made on the slider. To do this, we will use the absoluteposition field of data1.recordset. When we assign the value i to this field, record #i in the table becomes the current record. Records are numbered starting from 0 and therefore have a number in the range [0,data1.recordset.recordcount-1]. In the position_change procedure, we simply write

    data1.recordset.absoluteposition = position.value

so that the current record displayed on the form reflects the movement made on the joystick.

Once this is done, we will then call the Oueston procedure to update the menus.

Updating the DCS

Since the position of the drive’s cursor must reflect the position in the table, the drive’s value must be updated every time there is a change in the current record in the table, triggered by one of the menus. Since each of these calls the Oueston procedure, it is best to place this update within that procedure as well. Simply write here:

    position.value=data1.recordset.absoluteposition

---

Part 4 - Search Option

---

We add the Browse/Search option, which allows the user to view an item by entering its code.

When this option is enabled, the following steps occur:

• the system switches to Add New mode (Addnew), solely to avoid modifying the current record that was open when the option was enabled,
• We enable input in the code field and clear the contents of the record field,
• the menus are disabled, and the OK and Cancel buttons are displayed
• When the user clicks OK, we must search for the record corresponding to the code entered by the user. However, the OK_click procedure is already used for the Browse/Add and Browse/Modify options. To distinguish between these cases, we need to manage a global variable, which we will call "state" here, that will have three possible values: "add," "modify," and "search." The procedures linked to the OK and Cancel buttons will use this variable to determine the context in which they are called.
• If state is “search,” in the procedure linked to OK, we
  • cancel the addnew operation (data1.recordset.cancelupdate) because we did not intend to add a record. Note that the current record then reverts to the one that was on the screen before the Browse/Search operation.
  • Build the search criteria based on the code and launch the search (data1.recordset.findfirst criteria),
  • if the search fails (data1.recordset.nomatch=true), we notify the user, then return to add mode (addnew) and exit the OK procedure. The user will need to re-enter a new code or select the Cancel option.
  • If the search is successful, the found record becomes the new active record. We restore the menus, hide the OK/Cancel buttons, disable input in the code field, and exit the procedure.
• . If the status is "search," in the procedure linked to Cancel, we
  • cancel the addnew operation (data1.recordset.cancelupdate). The system will then automatically return to the record that was active before the Browse/Search operation.
  • Restore the menus, hide the OK/Cancel buttons, disable input in the code field, and exit the procedure.

---

Part 5 - Code Management

---

An item must be uniquely identified by its code. Ensure that in the Browse/Add option, the addition is rejected if the record to be added has an item code that already exists in the table.

6.4.4. Exercise 4

Here we present a web application based on the server from Exercise 2. It is a basic e-commerce application.

The customer orders items using the following web interface:

They can perform the following operations:

• select an item from the drop-down list
• specify the desired quantity
• confirm their purchase by clicking the Buy button
• Their purchase is displayed in the list of purchased items
• he can remove items from this list by selecting an item and clicking the Remove button
• When they click the Summary button, they see the following summary:

The summary allows the user to view the details of their invoice. The user can access details about a selected item in the dropdown list by clicking the "Information" button:

Once the user has requested the invoice summary, they can confirm it on the next page. To do so, they must enter their email address and confirm their order using the appropriate button.

Before submitting the order, the app asks for confirmation:

Once the order is confirmed, the app processes it and displays a confirmation page:

In reality, the app does not process the order. It simply sends an email to the user asking them to pay for their purchases:

Dear Customer,

Please find below the details of your order from the SuperPrix store. It will be delivered to you upon receipt of your check made payable to SuperPrix and sent to the following address:

SuperPrix 
ISTIA 
62 Notre-Dame du Lac Ave. 
49000 Angers 
France

Thank you very much for your order

---------------------------------------- 
Your order 
---------------------------------------- 
item, quantity, unit price, total 
======================================== 
bicycle, 2, 1,202.00 F, 2,404.00 F 
water skis, 3, 1,800.00 F, 5,400.00 F
Total due: 7804 F

Question: Create the equivalent of this web application using a Java applet.