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
Read More
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
