JDBC sample

JDBC Sample
/ Mitrovic Ivan, August 1997. /

This sample was written as an example for my article "JDBC" written for the most popular Yugoslav computer magazine PC Press.

1. Model explanation
JDBC is a part of the Java Enterprise API, and is the first cross platform, cross database approach to databases access from Java program. JDBC supports both the two - tier and three - tier Client / Server system tiers. Two - tier system involves GUI and business logic which are on the client system, and the database can be on the client or on a Server.
Three - tier system strategy is determined by three levels. GUI is based on the client system, which is first level, middle tier is application based on a Server (second level), which is connected to aspecified database (third level). As a sample of three - tier system I developed simple Java Client / Server system which is connected to MS Access97 database based on Server.
MS Access97 database, which represents virtual classroom, consists of three tables:

Students StudentsCourses Courses

                   ID       - AutoNumber (primary key)    StudentID - Number (long Integer)           ID    - Number (long Integer)
                   Name - Text                                       CourseID - Number (long Integer)           Title - Text
                   Sex     - Text
                   Age    - Number (long Integer)
                   Email - Text

As You can see, whenever particular Student selects a course to attend, Student's ID and Course's ID are inserted into
StudentsCourses Table. So, we can say that Students Table and Courses Table are   related over the StudentsCourses Table.
As a first operation, Student can fill her data into the Students Table. In order to do it, press ' Add to Student's Database '
button. Fill in requested informations about Yourself, and press 'Add to database' button. You will see the generated SQL
INSERT sentence which is, via sockets, delivered to Java Server application and sent to MSAccess97 database. You can't
change SQL sentence, it is automaticly generated by a client. Server takes the response from databse via JDBC - ODBC
bridge, and pass it to client.You can see the Server generated report in TextArea control labelled as 'Report'. If the sentence
was executed succesfully, You will see the message 'SQL sentence succesfully executed. Rows affected: 1'. If something went wrong, You will see the Error message in Report TextArea control.
When Student wants to apply on a particular Course, she will choose the second option (press 'Apply to a Course' button on the main menu). By choosing this option, Frame with two Choice controls   will appear.   You can see that the first Choice control contains all records from Students table (columns ID and Name), and the second Choice control contains Course ID and Title. Whenever this option is selected, Choice controls are automaticly updated. Client sends   SQL SELECT sentence to Server ( SELECT ID, Name   FROM Students, SELECT ID, Title FROM Courses )   and, after   the database   'treatmant', selected records are automaticly added to Choice controls. In order to check this, go back to first option, insert new student, go on second option and You will see the students Name and ID as the last Choice Item. When 'Apply to a course'    button is pressed the client generates SQL INSERT sentence in order to insert records in the StudentsCourses Table.
Third option, 'Explore the Database' is the most powerfull. You can send Your own   SQL querries to   database
as well as querries generated by little querry builder.   Querry builder    builds the    SQL   SELECT   sentence based on selected options.   Simply   check the Columns You want to see in Report, insert conditions, orders and press ' Make Querry ' button. You will see the generated SQL sentence in the TextArea labelled as 'SQL sentence sent to a database'. If You want to execute that   sentence   press ' Execute   Querry '   button,   or You can simply   type Your own SQL sentence and press 'Execute Querry' button. You can even send 'UPDATE' and 'DELETE' SQL sentences.   The sentence, for   example, can be:
INSERT INTO   Courses   (Title)   VALUES   ('New Course Title'); This will insert new course Titled as   'New Course Title'
in Table Courses or SELECT COUNT (*) FROM Students WHERE Sex = 'Female', which will retrive   how many female Students are in the Students Table, etc. Use the Querry builder first to see the SQL syntax, and You will be able to make Your own Querrys easy. Especcialy be aware of the indexes. If You want to make reports with   records   from b oth Students and Courses Table, don't forget to include 'WHERE Students.ID = StudentsCourses.StudentID AND Course.ID =
StudentsCourses.CourseID', because those two Tables are related via StudentsCourses Table. If You,   for example,   want   to
see what Courses female Students attend and You want Querry   builder to generate SQL sentence,   check Name, Title, in WHERE clause select radio button Female, select ORDER BY if You want, press 'Make Querry' button   and when sentence is generated, press 'Execute Querry' button. You will see the results in Report TextArea control. Or, for example, if You want to set the right age for all ladies, simply type UPDATE Students SET Age = 70 WHERE Sex = 'Female';
NOTE:
You are restricted to create new tables ('CREATE' sentence) or drop existing tables ('DROP' sentence).   Server checks
SQL sentence and doesn't execute 'CREATE' and 'DROP' sentences.

2. How does this thing work?
Java Server application, which is based on server establishes socket connection on port 9000. Server accepts clients and adds each client to Vector of Channels, which is class that extends Thread. The code is simply straightforward, and if You have ever made, for example, Java Chat system, You will easilly understand it. But, there are two things that need to be e xplained. First, the Server response is not sent to all clients (like, for example in Chat system) but only to a particular client that sent the request to Server. The code which do this:

int position = vektor.indexOf (this);

// finding the position of particular client in Vector vektor

Channel c = (Channel) vektor.elementAt (position);

// extracting the particular client

try {

synchronized (c.dos) {
c.dos.writeUTF (msg1);

// writing to DataOutputStream

}

c.dos.flush ();

}
...

This is, for example, analogous to sending the private messages in Chat systems.
Second, the Server application doesn't know which SQL statement will be sent from client. If the statement is,   for example,
INSERT or UPDATE, number of affected rows will be returned as a response . If number is 0, that means that no rows were affected or that 'CREATE' or 'DROP' statement was executed. In this example, Server enables executing of 'CREATE'   and 'DROP' sentences. As we don't know what kind of SQL sentence will be sent from client, we can't use executeUpdate (which stands for ' INSERT ' and ' UPDATE ' sentences ) or executeQuerry method ( which is used for ' SELECT ' statements ). In order to accomplish this we will use the execute method which, as result can retreive int ( for ' INSERT ' and ' UPDATE ' ),
which gives the number of rows affected by the statement, or can retreive ResultSet if sentence was ' SELECT ' type. So, we
will check what kind of response is retrieved after the SQL sentence execution. If it is int, we will send appropriate   message and if it is a ResulSet, we will extract result columns using the ResultSetMetaData and   send them   to client. Code which accomplishes that responsible task:

try {
// executing of SQL sentence which is contained in String msg
stmt.execute (msg);
}

catch (Exception e) {
   e.printStackTrace ();
   msg1 = "Error! Check the SQL Sentence!" + "\n" + e.toString() +
   "\nServer message: end";
   send ();
   return;
}

try {

int rows = stmt.getUpdateCount ();

msg1 = "";

// if it was 'INSERT' or 'UPDATE'

   if (rows >= 0) {
    msg1 = "The sentence was succesfully executed. Rows affected: " + rows +
    "\nServer message: end";
   }

ResultSet result = stmt.getResultSet ();

// if it was 'SELECT'

   if (result != null) {
    try {
     ResultSetMetaData rsmd = result.getMetaData ();
     int cols = rsmd.getColumnCount ();

while (result.next ()) {

      for (int i = 1; i <= cols; i ++) {
       if (i > 1) msg1 = msg1 + "   ,   ";
       msg1 = msg1 + result.getString (i);
      }

// sending resulted row to client

send ();
pass = true;

}

msg1 = "Server message: end";
...

Source code of Server.java.
Client , SQLclient.class which extends Applet establishes connection with Serveron 9000 port, receives user's GUI actions, generates SQL sentences and send them to Server application. The code is straightforward and only interesting thing can be
creating of Choice controls which contain database data.

// String select can be SELECT ID, Name FROM Students or
// SELECT ID, Title FROM Courses

public Choice makeChoice (String select) {

Choice ch;
ch = new Choice ();

try {

//sending SQL sentence to Server

dos.writeUTF (select);
dos.flush ();
}

catch (IOException e) {
getAppletContext ().showStatus (e.toString ());
}

String line = "";

try {

while (!line.endsWith ("Server message: end")) {

//reading the Server response, line by line
//adding line to Choice until Server response ends

line = dis.readUTF ();

if (!line.endsWith ("Server message: end"))
ch.addItem (line);

}

catch (IOException e) {
getAppletContext ().showStatus (e.toString ());
}

return ch;

}
...

Source code: SQLclient.java, InputFrame.java, ImageButton.java

3. Well...
JDBC gives Java enormus power in approach of databases access. Java application can connect several databases on several hosts. Just imagine enterprise whose clients and employees can access databases on enterprise's host across Internet or intranet.
Sample above shows only the basics of JDBC. It uses old fashion Socket connection to a Server. The best approach would be to communicate via ORB, where client can access particular method in Server application and vice versa. But, for sake of simplicity, I didn't consider that approach.
There are more powerful posibilities JDBC provides, such as Prepared Statements or Callable Statements, Transactions etc. It is up to You to experiment with the particular database.
JDBC has some handycaps. It doesn't support Bookmarks and Cursor functions which are very important, for example, for
"scrolling" through the resulted rows (You can make improvisation like adding resulted rows to vector or array and scrooll through it.)

NOTE: CODE WAS COMPILED WITH JDK 1.1.3 BUT DEPRECATED EVENT HANDLING IS USED IN ORDER TO BE SUPPORTED BY BROWSERS THAT DON'T SUPPORT JDK 1.1

3. ...let's see it

.
.