Basic Java - JDBC Constructs

The goal of this discussion is to provide in skeleton form some of the basic Java constructs used to interact with a DBMS through JDBC.  It is task-oriented, cast in terms of common operations performed with respect to a DBMS.

Key resources for this discussion are included in the java.sql package.  Peruse it carefully, especially the ResultSet and ResultSetMetaData classes.

You may wish to keep in mind the architecture shown below.  The discussion here is focused on the methods included in the Mapper object, especially its Add, Search, Update and Delete methods.

 

Specifying the Location of the DBMS: the URL


The location of the database is specified through a special URL.  It doesn't look like most WWW URL's.  It includes three segments:  a protocol, a subprotocol, and a network address.  It has the following general form:

protocol:subprotocol:address

Note the use of colons to separate the components.  Consider the following example:

jdbc:mysql://classroom.cs.unc.edu/jbsdb

It specifies that jdbc is the protocol, mysql the subprotocol, and the database to be sued -- jbsdb - is located on a computer called classroom  in domain cs.unc.edu. One could also specify a port for the DBMS server, but DBMSs normally run on a particular port and the driver is smart enough to know that default port.

 

Java Context

Since there is no way to know if the DBMS server and/or the network are functioning, the Java statements that involve real-time interaction with a database must be placed within a try-catch construct.

try {

	// your jdbc code goes here

}  // end try
catch (SQLException e) {
		
	System.out.println ("SQL Exception:  " + e.getMessage ());

}  // end catch SQLException

 

Establishing a connection with the DBMS

Establishing a connection with a DBMS is a two step process.  First, you must load a driver for the particular DBMS.  Second you must create a Connection object, by using the driver.

Class.forName ("com.mysql.jdbc.Driver");

Connection connection = DriverManager.getConnection 
  ( url, "your_dbms_logon", "your_dbms_password" );
 

Building a Query

All interaction with the DBMS is through SQL queries.  there are four basic kinds of queries: additions (INSERT), searches (SELECT), updates (UPDATE), and deletions (DELETE).  As a convention, many people capitalize SQL keywords to differentiate them from attributes and values.

One practice I have found useful is to programmatically build-up the query string.

INSERT

//INSERT INTO ABBasic ( personID, NameFirst, NameLast, City ) 
//VALUES ( "some_numeber_string", "john", "smith", "carrboro" );


String sqlString = "";

sqlString += "INSERT INTO Person";
sqlString += " ( " + "personID" + "nameFirst" + "nameLast" + "city" + " )";
sqlString += " VALUES";
sqlString += " ( " + personID + "," + firstName + "," + lastName + "," + city + " )";

 Note that personID, firstName, lastName, and city are variables whose values will be substituted in the above expression.
 

SELECT

//SELECT * FROM ABBasic WHERE nameLast = "smith" AND ( email LIKE "%jbs%" );

String sqlString = "SELECT * FROM Person WHERE "; 

sqlString += "owner = '" + dataBean.getOwner() + "' AND ( ";
sqlString += "personID LIKE '%" + dataBean.getPersonID() + "%' AND ";
sqlString += "nameFirst LIKE '%"+ dataBean.getNameFirst() + "%' AND ";
sqlString += "url LIKE '%" 	+ dataBean.getUrl() + "%' ";

sqlString += ")" ;

UPDATE

//UPDATE ABBasic SET nameFirst = 'john' WHERE personID = '222';

String sqlString = "UPDATE Person SET ";

sqlString += "nameFirst = '" + dataBean.getNameFirst() + "', ";
sqlString += "url = '" + dataBean.getUrl() + "' ";

sqlString += " WHERE personID = '" + dataBean.getPersonID() + "' ";

DELETE

//DELETE FROM ABBasic WHERE PersonID = 'personID';

String sqlString = "DELETE FROM Person WHERE personID = ";
sqlString += "'" + personID + "' ";

 

Executing the Query

Queries are executed through JDBC Statements.  One first creates an instance of a statement and then calls a particular method on that statement, normally either an executeQuery or an executeUpdate. 

Statement stmt = connection.createStatement ();
int i = stmt.executeUpdate(sqlString);
ResultSet resultSet = stmt.executeQuery(sqlString);

Updates return an integer indicating the number of rows affected.  Queries return a JDBC object called a ResultSet. 


ResultSets

ResultSets are two-dimensional "tables" returned by queries.  One important point to understand is that the are not Serializable.  Consequently, all processing of them must be done on the server side (or in whatever program receives them from the driver).

One normally processes them much as one would a two-dimensional matrix; that is, by iterating out the columns, first, and down the rows, second, rather than the other way around.  In fact, this is required by some drivers and early versions of the JDBC.

ResultSets are accompanied by ResultSetMetaData.  The latter enables one to find out the number of columns in the table, the names of the columns, their data type, etc.  More on this below.

The following code iterates across the columns and down the rows, providing access to each value:

int numCols = resultSet.getMetaData().getColumnCount ();

while ( resultSet.next() ) {

	for (int i=1; i<=numCols; i++) {
	    resultSet.getString(i); 
	    // do something with it!!!
	}  // end for

}  // end while

Several other useful methods include the following:

With Java 1.2, the cursor that is used to move through the ResultSet was extended so that it can go to a specified row, move up as well as down, go to the first, previous, and last items, etc.  But, be aware that with earlier versions of Java, the cursor can move only down and it can go through the ResultSet only once!

 

ResultSetMetaData

Often the program will need to access not just to the data returned by a DBMS but to information about the data.  In the code above, the program needed to know how many columns were included in the table to iterate across the columns. 

Data is returned in object form with access provided through various getSpecifiedType methods, such as getString, above.  Whereas the programmer will often know the expected type for a given column, this may not always be the case.  This is a second type of information provided through the ResultSetMetaData class.

Some of the more useful methods are listed below: