20.4 JDBC PROGRAMMING: INVOKING SQL THROUGH JAVA

JDBC is a programming interface that communicates your SQL commands to a database, retrieves the results, analyzes the results in whatever way you want them analyzed, displays the results retrieved from a database, and so on.

This section shows two JDBC programs. The goal of the first program is to make the reader familiar with some of the more basic classes of the java.sql package. This we do by constructing a couple of database tables and then querying them, just as we did in our first command-line SQL session in the previous section. The goal of the second JDBC program is to show how information can be rapidly loaded into a database table from a file.

As we will show in our first example, all communication with a database is through the executeQuery method of Statement, a class in the java.sql package. A Statement object is constructed by invoking the createStatement method on an object of type Connection, which represents the communication link with the database. But, as mentioned earlier, at the very beginning one must first register an appropriate driver with the driver manager. Since we will be using a MySQL database, we would need to register the mm.mysql.Driver driver with the JDBC DriverManager by

       Class.forName( "org.gjt.mm.mysql.Driver").newInstance();

This invocation results in an automatic registration of the driver with the JDBC DriverManager.

When a JDBC program queries a table with SELECT, the object returned is of type ResultSet, another class defined in java.sql. To display the information in a ResultSet retrieval, one must first figure out its structure, meaning the number of rows and columns in the retrieved object. All such structural information regarding a ResultSet object resides in the corresponding ResultMetaData object. For example, if rs is a ResultSet object, to figure out the number of columns in this object, we can say

        ResultSetMetaData rsmd = rs.getMetaData();        int numCols = rsmd.getColumnCount();

One often does not need to know explicitly the number of rows in a ResultSet object. The operator next, when invoked on a ResultSet object, takes the flow of control automatically to the next row. Therefore, once we have figured out the number of columns in the ResultSet object rs as above, we can set up the following print loop to display all the contents of the ResultSet:

      while ( rs.next() ) {          for ( int i = 1; i <= numCols; i++ ) {              if ( i > 1 ) System.out.print( " | " );                  System.out.print( rs.getString( i ) );           }           System.out.println( "" );      }

Here is the source code for the first example:

 
 //DBFriends1.java import java.sql.*; class DBFriends1 {     public static void main( String[] args )     {         try {             Class.forName( "org.gjt.mm.mysql.Driver").newInstance();             String url = "jdbc:mysql:///test";            Connection con =  DriverManager.getConnection(url);            Statement stmt =  con.createStatement();                  stmt.executeQuery(  "SET AUTOCOMMIT=1" );            stmt.executeQuery(  "DROP TABLE IF EXISTS Friends" );            stmt.executeQuery(  "DROP TABLE IF EXISTS Rovers" );            // new table (Friends):            stmt.executeQuery(                "CREATE TABLE Friends(Name CHAR (30) PRIMARY KEY,  +                                      "Phone INT, Email CHAR(30))" );            stmt.executeQuery(                "INSERT INTO Friends VALUES ( 'Ziggy Zaphod',                                    4569876," + "'ziggy@sirius' )" );            stmt.executeQuery(                "INSERT INTO Friends VALUES ( 'Yo Yo Ma', 3472828, "  +                                              "yoyo@yippy' )" );           stmt.executeQuery(               "INSERT INTO Friends VALUES ( 'Gogo Gaga',                                   27278927," + " 'gogo'@garish')" );                 //new table (Rovers):                 stmt.executeQuery(               "CREATE TABLE Rovers ( Name CHAR (30) NOT NULL, " +                                            "RovingTime CHAR(10))" );           stmt.executeQuery(               "INSERT INTO Rovers VALUES ( 'Dusty Dodo','2 pm' )"));           stmt.executeQuery(               "INSERT INTO Rovers VALUES ( 'Yo Yo Ma', '8 pm' )" );           stmt.executeQuery(               "INSERT INTO Rovers VALUES ( 'BeBe Beaut', '6 pm')" );           // Query: which Friends are Rovers ?           ResultSet rs = stmt.executeQuery(           SELECT Friends.Name, Rovers.RovingTime FROM Friends, "                  +  "Rovers  WHERE  Friends.Name  = Rovers.Name" );                ResultSetMetaData rsmd = rs.getMetaData();          int numCols = rsmd.getColumnCount();          while (rs.next() ) {for (int i = 1; i <= numCols; i++) {                if (i > 1) System.out.print(" | ");                     System.out.print(rs.getString(i));           }           System.out.println("");      }      rs.close();      con.close();           } catch(Exception ex) {System.out.println(ex);}      } }

To compile this program, you'd need to tell javac how to locate the database driver. If the driver is in a JAR file named mm.mysql-2.0.7-bin.jar, an invocation like the following should work

      javac -classpath .:∼/mm.mysql-2.0.7-bin.jar DBFriends1.java

You'd also need to specify the classpath for the java application launcher:

      java -classpath .:∼/mm.mysql-2.0.7-bin.jar DBFriends1

Of course, you'd need to make sure that the MySQL server is on and running before you run the above JDBC program. (See the footnote in Section 20.2 dealing with the starting and the stopping of the database server.) The output of this program is

      Yo Yo Ma | 8 pm

Large databases can obviously not be created from within JDBC programs one row at a time. The information that you want to enter into a database is more likely to be found in the form of what's known as a flat file. For illustration, we may have the following information in a flat file called Friends. txt:

 Doctor Cosmos  876--6547  zinger@zoros  68  0 73  galacticSoccer Yo Yo Ma       838--9393  yoyo@yahoo    56  1  0  violaHockey Zinger Zaphod  939--1717  dodo--dada    23  0  2  tennin Bebe Beaut     84--83838  bebe@parlor   18  1  3  tennis

Each row of this text file has a name, a phone number, an e-mail address, age, whether or not married, number of kids, and the name of the favorite sport. The entries in each row are tab separated, but can also be supplied in what's known as the comma separated values (csv) form. You can also have flat files in which each field is given a fixed number of positions.

So let's say that we want a database system to read this flat file and create a Friends database. In some systems this can be done with the help of another text file, known usually as a database table's schema, which tells the system how to interpret the position of each data item in a row of the flat file. In MySQL, the same is most easily accomplished by first creating a table directly with the CREATE TABLE command and then invoking LOAD DATA INFILE to read in the data from the flat file into the database table.

The following JDBC program executes the MySQL statement LOAD DATA INFILE for the creation of two database tables named Friends and SportsClub. Whereas the table Friends is created from the flat file shown above, the table SportsClub is created from the flat file:

 Hobo Hooter           45   hockey         4 Doctor Cosmos         68   galacticSoccer 9 Zinger Zaphod         23   tennis         2 Bebe Beaut            84   tennis         10

where the entries are in the following order: name, age, sport, and the level at which sport is played. For the query, the program executes the SQL statement SELECT … WHERE to seek out friends who play tennis at the sports club.

 
 //DBFriends2.java import java.sql.*; class DBFriends2 {      public static void main(String[] args)      {           try {               Class.forName("org.gjt.mm.mysql.Driver").newInstance();              String url = "jdbc:mysql:///test";              Connection con = DriverManager.getConnection(url);              Statement stmt = con.createStatement();              stmt.executeQuery("SET AUTOCOMMIT=1");              stmt.executeQuery("DROP TABLE IF EXISTS Friends");              stmt.executeQuery("DROP TABLE IF EXISTS SportsClub");              stmt.executeQuery(              "CREATE TABLE Friends (Name CHAR (30) PRIMARY KEY,"+                                    "Phone CHAR (15), Email CHAR(30),"+                                       "Age TINYINT(3), Married BOOL,+                                "NumKids TINYINT (3), Sport CHAR(20))" ); stmt.executeQuery( "CREATE TABLE SportsClub (Name CHAR (30) PRIMARY KEY", +                             "Age TINYINT (3), Sport CHAR(20)," +                           "Level Char(20))" );           stmt.executeQuery(                "LOAD DATA LOCAL INFILE 'Friends.txt' INTO TABLE" +                "Friends");           stmt.executeQuery(                "LOAD DATA LOCAL INFILE 'SportsClub.txt' INTO" +                          "TABLE SportsClub");           // which of the Friends also play tennis at the club:           ResultSet rs = stmt.executeQuery(                "SELECT Friends.Name, SportsClub.Level FROM Friends,"                + "SportsClub WHERE"                + "Friends.Name = SportsClub.Name AND"                + "Friends.Sport = SportsClub.Sport AND"                + "Friends.Sport = 'tennis' ");           ResultSetMetaData rsmd = rs.getMetaData();           int numCols = rsmd.getColumnCount();           while (rs.next()) {                for (int i = 1; i <= numCols; i++) {                     if (i > 1)                          System.out.print("plays      tennis at level");                System.out.print(rs.getString(i));                }                System.out.println("");           }           rs.close();           con.close();         } catch(Exception ex) {System.out.println(ex);}      } }

The program can be compiled and executed with the command line invocations similar to those shown for the first example. The output of this program is

      Zinger Zaphod plays tennis at level 2      Bebe Beaut plays tennis at level 10