Java 1.2 Unleashed
One of the most powerful and exciting elements in the Java API is the java.sql package. This package adds database programming capabilities to Java and is referred to as the JDBC API. The JDBC was originally developed as a separate package from the JDK 1.02 but is an integral part of the JDK as of JDK 1.1. JDK 1.2 supports JDBC 2.0.
NOTE: Many people think JDBC stands for Java Database Connectivity. However, according to JavaSoft, JDBC is not an acronym.
The JDBC is a powerful tool for Java development. By providing database connectivity to applications and applets, it enables these programs to tap into commercial, government, university, and other databases. This results in information-rich Java applications that can be used to replace and enhance current legacy information systems.
In this chapter, you'll learn the basics of the JDBC. You'll learn about the classes of the java.sql package and how to work with JDBC drivers. You'll also learn how to connect to databases, execute SQL statements, and work with result sets. This chapter provides a quick introduction to the java.sql package. Chapter 45, "Using JDBC," expands on the programming concepts introduced in this chapter.
In order to use JDBC, you'll need a database server and a database driver. Because most readers have access to Windows 95, I'll be using Microsoft Access as my database server. You can choose to use Access or some other server. After you learn how to connect to your database, the type of server that you're using won't matter--JDBC provides a server-independent approach to database access. That's one of JDBC's major benefits!
You'll also need a database driver, which provides the linkage between the JDBC and your database. The JDBC comes with a JDBC-ODBC bridge. This bridge allows you to access databases via Microsoft's Open Database Connectivity API. However, the JDBC-ODBC bridge is a temporary solution to database connectivity and has some significant drawbacks, such as requiring the bridge to be installed on your database users' com- puters.
I will use the JDBC driver of IDS Software (http://www.idssoftware.com). The IDS JDBC driver is a pure Java driver that supports zero installation for applets. This means that you can provide database access via applets without requiring software to be preinstalled on the computers of applet users.
NOTE: Consult the JDBC home page at http://www.javasoft.com/products/jdbc/ for information on the JDBC drivers that will work with your database server.
While you may use the JDBC driver of your choice for the examples in this book, I recommend the IDS driver because of its ease of use, great documentation, and zero installation features. If you are interested in using the IDS JDBC driver, check the IDS Web page for the availability of an evaluation copy. If you intend to develop Java-based database applications, it will be well worth obtaining a licensed version of the IDS software.
NOTE: If you use the IDS JDBC driver with Microsoft Access, make sure you have installed version 3.5 or later of Microsoft's ODBC database drivers. These drivers are available at ftp://ftp.microsoft.com/Softlib/MSLFILES/WX1350.EXE.
TIP: You can check the version of your ODBC database drivers by opening the 32-bit ODBC Control Panel applet and clicking on the ODBC Drivers tab.
The DriverManager class of java.sql is used to manage the JDBC drivers that are installed on your system. These drivers may be installed by setting the jdbc.drivers system property or by loading the driver class using the forName() method of the Class class. The DriverApp program, presented in the section "The DriverApp Program" later in this chapter, shows how to load a JDBC driver using the forName() method.
The DriverManager class does not provide a constructor, and all of its methods are static. The getDrivers() method returns an enumeration of all the JDBC drivers that are installed on your system. The getConnection() method is used to establish a connection to a database. This method is provided in the following three forms:
getConnection(String url) getConnection(String url,String userID,String password) getConnection(String url,Properties arguments)
The first form takes a String argument that specifies the URL of the database. You'll learn about database URLs shortly. The second form takes two additional strings: the user ID and password required to access a database. The third form takes an additional Properties argument that specifies a list of connection arguments, such as user ID, password, database name, and so on. Consult your JDBC driver documentation for more information on which method to use with those drivers.
The URLs used to establish database connections vary with the JDBC drivers that you use, but they are of the following form:
jdbc:subprotocol:subname
All JDBC database protocols begin with jdbc:. The subprotocol is used to identify either the connection mechanism or the JDBC driver. For example, the JDBC-ODBC bridge uses protocols of the form jdbc:odbc:subname and the IDS JDBC driver uses protocols of the form jdbc:ids:subname.
The subname of a database protocol identifies the database and provides other parameters that depend on the subprotocol and JDBC driver. For example, I use the following URL to establish a database connection to the Microsoft Access database named DataSetName on the host cx122974-a.cv1.sdca.home.com on protocol port 80:
jdbc:ids://cx122974-a.cv1.sdca.home.com:80/ conn?dbtype=odbc&dsn=DataSetName
The IDS Server can be configured to support Web service and database access via the same TCP protocol port (80).
NOTE: Consult your JDBC driver's documentation for information on the subprotocol and subname you should use to establish a database connection.
Although the getDrivers() and getConnection() methods are the most important methods of the DriverManager class, it provides other methods that support driver and general database management:
The Driver interface is implemented by JDBC drivers. Writing a JDBC driver consists of creating a Java class that implements the Driver interface. Although you most likely won't have to worry about writing your own JDBC driver, there are a few useful methods in the Driver interface:
An array of objects of the DriverPropertyInfo class is returned by the getPropertyInfo() method of the Driver class to provide information about a driver that can be used to establish a database connection. The DriverPropertyInfo class provides the following five field variables to describe a property of a driver:
The DriverApp program, shown in Listing 44.1, illustrates the use of the DriverManager and DriverPropertyInfo classes and the Driver interface.
When I run the program, it provides the following output:
Available drivers:
Driver: ids.sql.IDSDriver
Major version: 2
Minor version: 5
JDBC compliant: true
Properties:
Name: dsn
Description: Data Source Name or Database Name
Value: null
Required: true
Name: user
Description: User ID
Value: null
Required: false
Name: password
Description: Password
Value: null
Required: false
Driver: sun.jdbc.odbc.JdbcOdbcDriver
Major version: 1
Minor version: 2001
JDBC compliant: true
If you use a different driver than the IDS driver, you'll have to replace the class name ids.sql.IDSDriver in the statement
Class.forName("ids.sql.IDSDriver");
with your JDBC driver's class name in order for the program to work properly. If you delete the preceding statement, the program only displays information about the Sun JDBC-ODBC driver.
The IDS Server runs on both Windows 95 and Windows NT.
NOTE: Make sure that you put the IDS classes (C:\IDSServer\classes) in your CLASSPATH. Otherwise, DriverApp won't be able to find your IDS driver (ids.sql.IDSDriver).
DriverApp provides useful information about your driver that you can use to set up a database connection. It loads the ids.sql.IDSDriver and the sun.jdbc.odbc.JdbcOdbcDriver classes by invoking the forName() method of the Class class. It then invokes the getDrivers() method of DriverManager to return an Enumeration of the loaded drivers. A while loop is used to iterate through the Enumeration object and display information about each driver.
The name of the driver is retrieved by invoking the getClass() method to retrieve the driver's class and the getName() method to retrieve the name of the class. The getMajorVersion(), getMinorVersion(), and jdbcCompliant() methods return additional information about each driver.
An array of DriverPropertyInfo objects is returned by the getPropertyInfo() method of the Driver class. The name, description, value, choices, and required fields of each object are displayed.
The sample program output shows that the ids.sql.IDSDriver driver is version 2.5, is JDBC-compliant, and takes the required dsn and optional user and password properties. However, the sun.jdbc.odbc.JdbcOdbcDriver driver revealed little information about itself.
import java.sql.*;
import java.util.*;
class DriverApp {
public static void main(String args[]) {
try{
// Load the database drivers
Class.forName("ids.sql.IDSDriver");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Obtain a list of the loaded drivers
Enumeration drivers = DriverManager.getDrivers();
System.out.println("Available drivers:");
while(drivers.hasMoreElements()){
Driver driver=(Driver)drivers.nextElement();
// Display information about each driver
System.out.println(" Driver: "+driver.getClass().getName());
System.out.println(" Major version: "+driver.getMajorVersion());
System.out.println(" Minor version: "+driver.getMinorVersion());
System.out.println(" JDBC compliant: "+driver.jdbcCompliant());
// Get driver properties
DriverPropertyInfo props[] = driver.getPropertyInfo("",null);
if(props!=null){
// Display each property and its value
System.out.println(" Properties: ");
for(int i=0;i<props.length;++i){
System.out.println(" Name: "+props[i].name);
System.out.println(" Description: "+props[i].description);
System.out.println(" Value: "+props[i].value);
if(props[i].choices!=null){
System.out.println(" Choices: ");
for(int j=0;j<props[i].choices.length;++j)
System.out.println(" "+props[i].choices[j]);
}
System.out.println(" Required: "+props[i].required);
}
}
}
}catch(Exception ex){
System.out.println(ex);
System.exit(0);
}
}
}
The getConnection() method of DriverManager is used to establish a connection to a database. In this section, you'll see how a database connection is established. But first let's cover two important interfaces related to database connections--Connection and DatabaseMetaData. You'll then use the ConnectApp program to connect to a database and return information about the database to which you are connected.
When a database connection is established using the getConnection() method of DriverManager, the getConnection() method returns an object that implements the Connection interface. This interface defines methods for interacting with the database via the established connection. It also defines several constants that describe the manner in which the database supports the committing of database transactions. Approaches to committing transactions are covered in Chapter 45. You don't need to understand these concepts to complete this chapter.
The methods of the Connection interface are used to manage a database connection, obtain information about a connection, roll back or commit database transactions, and prepare SQL statements for execution.
A number of methods are defined by the Connection interface. Consult the API documentation of the Connection interface for a complete description of these methods. Particular methods of interest include the following:
NOTE: SQL stands for Structured Query Language. It is a language for adding information to a database or retrieving information that is contained in a database. Chapter 43, "Database Fundamentals," covers SQL in more detail.
The DatabaseMetaData interface provides a vast amount of information about the database to which a connection is established. It defines several constants for describing database attributes and over 100 methods. You can use these methods to retrieve almost any type of information about a database's structure and capabilities. It is well worth the time to scan through the API documentation of DatabaseMetaData to see what kind of information is available through these methods.
NOTE: Databases differ in the extent to which they support the methods of DatabaseMetaData. For example, Microsoft Access 97 supports the simple methods used in the ConnectApp program in the following section, but tends to choke on more advanced metadata queries. In addition, a database may provide a particular capability, but that capability may not be supported by the driver you use to access the database.
The ConnectApp program, shown in Listing 44.2, establishes a connection to a database and returns information about that database. It illustrates the use of the Connection and DatabaseMetaData classes. The ConnectApp program connects to a sample database provided with the IDS JDBC driver. If you've downloaded and installed an evaluation copy of the IDS Server and JDBC driver, you'll be able to run ConnectApp as shown. Otherwise, if you feel comfortable doing so, you can tailor ConnectApp to your driver and database. Otherwise, just try to read through the program's output and correlate it with its description.
NOTE: If you are using the IDS Server and JDBC driver, don't forget to install the IDSExamples data set, as specified in the installation instructions contained in the IDS User Guide.
WARNING: If you use a different driver than the IDS driver, you'll have to adjust the parameters you use to establish a database connection. Check your driver's documentation for instructions on how to set up a database connection.
Be sure to start your IDS Server before running ConnectApp. When ConnectApp is run, it generates the following output:
Database: ACCESS version 3.5 Jet User name: admin
The name of my database is reported as ACCESS with version number 3.5 Jet. It also identifies me as accessing the database as user admin.
The output produced by ConnectApp may be underwhelming, but don't be concerned--you'll be able to produce mountains of information once you learn how to use ResultSet objects. The important point is that we established a successful database connection and were able to access metadata about the database.
ConnectApp begins by loading the IDS driver using the forName() method of the Class class. It then creates a database URL for that driver and the IDSExamples data set that comes with the IDS Server and driver.
NOTE: The IDS Server is a database access server and a Web server. It is used to support the connection of the IDS JDBC driver with a variety of database servers. See Chapter 43 for more information about database access servers.
The URL of the database consists of the JDBC protocol identifier (jdbc:), followed by the IDS subprotocol identifier (ids:), followed by the subname. The subname consists of the host name and port of my database server (cx122974-a.cv1.sdca.home.com:80), the connection parameter identifier (conn), the database type (dbtype=odbc), and the data set name (dsn='IDSExamples'). Other characters of the URL are used as separators.
NOTE: Substitute your host name for cx122974-a.cv1.sdca.home.com in the database URL.
The getConnection() method of DriverManager establishes the connection and returns an object of the Connection interface. The getMetaData() method is invoked for this Connection object to obtain information about the database to which the connection is established.
The getDatabaseProductName(), getDatabaseProductVersion(), and getUserName() methods of the DatabaseMetaData interface are used to retrieve information about the database product and the user name associated with the connection.
import java.sql.*;
import java.util.*;
class ConnectApp {
public static void main(String args[]) {
try{
// Load the IDS driver
Class.forName("ids.sql.IDSDriver");
String url="jdbc:ids://cx122974-a.cv1.sdca.home.com:80/";
url+="conn?dbtype=odbc&dsn='IDSExamples'";
// Connect to the database
Connection connection=DriverManager.getConnection(url);
// Get database meta data
DatabaseMetaData meta=connection.getMetaData();
// Display meta data information
System.out.print("Database: "+meta.getDatabaseProductName());
System.out.println(" version "+meta.getDatabaseProductVersion());
System.out.println("User name: "+meta.getUserName());
// Close the database
connection.close();
}catch(Exception ex){
System.out.println(ex);
System.exit(0);
}
}
}
When a database query is executed, the results of the query are returned as a table of data organized according to rows and columns. The ResultSet interface is used to provide access to this tabular data. Query results are returned as ResultSet objects that provide access to the tabular data, one row at a time.
A ResultSet object maintains a pointer to a row within the tabular results. This pointer is referred to as a cursor. When a ResultSet object is returned from a query, the cursor initially points immediately before the first row of the table. The next() method of the ResultSet class is used to move the cursor to the next row of the table. The next() method returns a boolean value that is true if the next row is returned and false if the end of the table is reached. The next() method is used to successively step through the rows of the tabular results.
The ResultSet interface provides a number of get methods that allow the column entries for a row to be returned as a variety of primitive and reference types, ranging from simple integers to streams and arbitrary objects. The getMetaData() method returns a ResultSetMetaData object that contains information about a row.
The ResultSetMetaData interface provides constants and methods that are used to obtain information about ResultSet objects. The getColumnCount() method returns the number of columns in the tabular data accessed by a ResultSet. The getColumnName() method returns the names of each column in the database from which the data was retrieved. The getColumnType() method returns a column's SQL type. The Type class defines constants that are used to identify SQL types. Other methods of ResultMetaData are used to access additional column properties, such as the column's display width, number formats, and read/write status.
NOTE: The columns in a ResultSet are accessed beginning with index 1 instead of index 0.
The ResultApp program, shown in Listing 44.3, illustrates the use of the ResultSet and ResultMetaData interfaces. It retrieves information from the IDSExamples database that comes with the IDS JDBC driver. This database defines three tables (courses, departments, and employees) that correspond to those of a typical university. The tables are filled with sample data. The ResultApp program executes an SQL statement that queries the database for the entire contents of the courses table. The program's results follow:
Course_ID | Department_ID | CourseNumber | CourseLevel | CourseName 35 | BIOL | 100 | Basic | Physiology 37 | BIOL | 300 | Intermediate | Plant Biology 38 | BIOL | 600 | Advanced | Microbiology 39 | BIOL | 310 | Intermediate | Neurobiology 40 | BIOL | 620 | Advanced | Neurobiology 41 | CHEM | 100 | Basic | General Chemistry 42 | CHEM | 300 | Intermediate | Analytical Chemistry 44 | ECON | 100 | Basic | Financial Accounting 45 | ECON | 110 | Basic | Business Law 51 | MATH | 100 | Basic | Calculus I 52 | MATH | 300 | Intermediate | Calculus II 59 | MATH | 600 | Advanced | Linear Algebra 60 | ECON | 220 | Intermediate | Microeconomics 61 | CHEM | 600 | Advanced | Organic Chemistry
The first line displays the column names of the table. Subsequent lines display the data contained in the table.
NOTE: You'll learn how to execute SQL statements in the next section.
The ResultApp program begins by establishing a connection to the IDSExamples database in the same manner as the ConnectApp program. It invokes the createStatement() method of the Connection class to create a Statement object to be used to query the database. The SELECT * FROM customer SQL query statement is then executed using the executeQuery() method of the Statement class. This statement queries the database, and its results are returned as a ResultSet object. The ResultSet object is displayed using the displayResults() method.
The displayResults() method invokes the getMetaData() method of a ResultSet object to access a ResultSetMetaData object that describes the ResultSet object. The getColumnCount() method of the ResultSetMetaData object identifies the number of columns in the ResultSet object. The names of these columns are retrieved using the getColumnName() method of ResultSetMetaData.
A while loop steps through each row of the ResultSet object using the next() method. The getString() method of the ResultSet class is used to retrieve each row's column entry and display it to the console. Note that the columns are accessed beginning with index 1.
import java.sql.*;
import java.util.*;
class ResultApp {
public static void main(String args[]) {
try{
// Load the IDS driver
Class.forName("ids.sql.IDSDriver");
String url="jdbc:ids://cx122974-a.cv1.sdca.home.com:80/";
url+="conn?dbtype=odbc&dsn='IDSExamples'";
// Connect to the database
Connection connection=DriverManager.getConnection(url);
Statement statement = connection.createStatement();
String sql="SELECT * FROM courses";
// Execute the SQL statement and retrieve the result set
ResultSet result = statement.executeQuery(sql);
displayResults(result);
// Close the database connection
connection.close();
}catch(Exception ex){
System.out.println(ex);
System.exit(0);
}
}
static void displayResults(ResultSet r) throws SQLException {
// Get result set meta data
ResultSetMetaData rmeta = r.getMetaData();
// Use meta data to determine the number of columns in each row
// of the result set
int numColumns=rmeta.getColumnCount();
// Print out values of each column
for(int i=1;i<=numColumns;++i) {
if(i<numColumns)
System.out.print(rmeta.getColumnName(i)+" | ");
else
System.out.println(rmeta.getColumnName(i));
}
while(r.next()){
for(int i=1;i<=numColumns;++i) {
if(i<numColumns)
System.out.print(r.getString(i)+" | ");
else
System.out.println(r.getString(i).trim());
}
}
}
}
SQL is the language used to interact with database servers. SQL statements can be used to add information to a database, update or delete existing database information, or retrieve information from a database. The purpose of the java.sql package is to let you execute SQL statements from Java. Chapter 43 provides an introduction to SQL. This section discusses the JDBC API interfaces that are used to execute SQL statements.
In the ResultApp program, you created an object of the Statement interface by invoking the createStatement() method of the Connection interface. You then invoked the executeQuery() method of the Statement interface, passing it the SELECT * FROM courses SQL statement as an argument. This resulted in the query being processed by the database and a ResultSet object being returned. This example illustrates the simplicity with which SQL statements are executed via the JDBC.
The Statement interface defines methods that are used to interact with databases via the execution of SQL statements. These methods also support the processing of query results returned via ResultSet objects and provide control over the mechanics of query processing. The execute(), executeQuery(), and executeUpdate() methods are the primary methods of interest in the Statement interface.
The executeQuery() method executes an SQL statement (such as the SELECT statement) that queries a database and returns a ResultSet object. The executeUpdate() method executes an SQL statement (such as an INSERT, UPDATE, or DELETE statement) that updates the database and returns the integer value of the row count associated with the SQL statement, or 0 if the statement did not return a result.
The execute() method executes an SQL statement that is written as a String object. It returns a boolean value indicating whether a ResultSet object was produced as the result of the statement's execution. The getResultSet() and getMoreResults() methods are used to retrieve the ResultSet object. If the statement's execution returns an update count, execute() returns false. The update count can be retrieved using the getUpdateCount() method.
The PreparedStatement interface extends the Statement interface to define methods that are used to work with precompiled SQL statements. The use of precompiled SQL statements provides a more efficient way of executing frequently used SQL statements.
PreparedStatement objects may be used with parameterized SQL statements. These parameterized SQL statements replace a constant SQL expression with a question mark (?). For example, the following parameterized statement retrieves an unspecified column from the courses table:
SELECT ? FROM courses
The preceding statement needs to be instantiated with the name of the column to be retrieved. The parameterized fields are said to be instantiated by IN parameter values.
NOTE: The implementation of SQL varies from database to database. However, the SQL presented in this book is commonly supported by all databases.
The PreparedStatement interface provides several set methods for setting the values of IN parameters. These methods are organized into the type of value to which a parameter is to be set.
PreparedStatement provides its own version of the execute(), executeQuery(), and executeUpdate() methods of the Statement interface. These methods do not specify the SQL statement to be used in the query. Instead, the SQL statement is specified as an argument to the prepareStatement() method of the Connection interface that is used to create the PreparedStatement object.
The CallableStatement interface extends the PreparedStatement interface to implement stored SQL procedures. CallableStatement objects are created via the prepareCall() method of the Connection class. A stored SQL procedure is passed as an argument to the prepareCall() method. It may be parameterized using question marks in the same manner as that discussed for the PreparedStatement interface. However, the CallableStatement interface allows some parameters to be OUT parameters, which are used to return values from the SQL procedure call. The registerOutParameter() method of the CallableStatement interface is used to identify the type of OUT parameter. In addition, several get methods are provided to retrieve the value returned by an OUT parameter.
The StatementApp program, shown in Listing 44.4, allows you to execute an SQL statement that is passed as a command-line parameter. The program uses the IDS driver to link to the IDSExamples data set used in the previous examples of this chapter. You can use this program to brush up on your SQL. Execute the program with the "SELECT * FROM courses" SQL statement to obtain a listing of the contents of the courses table:
java StatementApp "SELECT * FROM courses" SELECT * FROM courses Course_ID | Department_ID | CourseNumber | CourseLevel | CourseName 35 | BIOL | 100 | Basic | Physiology 37 | BIOL | 300 | Intermediate | Plant Biology
38 | BIOL | 600 | Advanced | Microbiology
39 | BIOL | 310 | Intermediate | Neurobiology 40 | BIOL | 620 | Advanced | Neurobiology 41 | CHEM | 100 | Basic | General Chemistry 42 | CHEM | 300 | Intermediate | Analytical Chemistry 44 | ECON | 100 | Basic | Financial Accounting 45 | ECON | 110 | Basic | Business Law 51 | MATH | 100 | Basic | Calculus I 52 | MATH | 300 | Intermediate | Calculus II 59 | MATH | 600 | Advanced | Linear Algebra 60 | ECON | 220 | Intermediate | Microeconomics 61 | CHEM | 600 | Advanced | Organic Chemistry
The economics classes don't belong with all of the math and science courses. Let's delete them by using the "DELETE FROM courses WHERE Department_ID = `ECON'" SQL statement:
java StatementApp "DELETE FROM courses WHERE Department_ID Â= `ECON'" DELETE FROM courses WHERE Department_ID = `ECON' Use the "SELECT * FROM courses" statement to verify that the economics classes have been deleted: java StatementApp "SELECT * FROM courses" SELECT * FROM courses Course_ID | Department_ID | CourseNumber | CourseLevel | CourseName 35 | BIOL | 100 | Basic | Physiology 37 | BIOL | 300 | Intermediate | Plant Biology 38 | BIOL | 600 | Advanced | Microbiology 39 | BIOL | 310 | Intermediate | Neurobiology 40 | BIOL | 620 | Advanced | Neurobiology 41 | CHEM | 100 | Basic | General Chemistry 42 | CHEM | 300 | Intermediate | Analytical Chemistry 51 | MATH | 100 | Basic | Calculus I 52 | MATH | 300 | Intermediate | Calculus II 59 | MATH | 600 | Advanced | Linear Algebra 61 | CHEM | 600 | Advanced | Organic Chemistry
Which courses are missing from the list? These poor students are being deprived of a Java education. Enter the following INSERT SQL statements to round out their education:
java StatementApp "INSERT INTO courses VALUES (`34', `JAVA', Â `999', `Basic', `Intro to Java')" INSERT INTO courses VALUES (`34', `JAVA', `999', `Basic', `Intro to Java') java StatementApp "INSERT INTO courses VALUES (`43', `JAVA', Â `999', `Intermediate', `AWT Programming')" INSERT INTO courses VALUES (`43', `JAVA', `999', `Intermediate', `AWT Programmin Âg') java StatementApp "INSERT INTO courses VALUES (`62', `JAVA' Â, `999', `Advanced', `Database Programming')" INSERT INTO courses VALUES (`62', `JAVA', `999', `Advanced', `Database Programmi Âng')
Use the "SELECT * FROM courses" statement to redisplay the courses table:
java StatementApp "SELECT * FROM courses" SELECT * FROM courses Course_ID | Department_ID | CourseNumber | CourseLevel | CourseName 35 | BIOL | 100 | Basic | Physiology 37 | BIOL | 300 | Intermediate | Plant Biology 38 | BIOL | 600 | Advanced | Microbiology 39 | BIOL | 310 | Intermediate | Neurobiology 40 | BIOL | 620 | Advanced | Neurobiology 41 | CHEM | 100 | Basic | General Chemistry 42 | CHEM | 300 | Intermediate | Analytical Chemistry 51 | MATH | 100 | Basic | Calculus I 52 | MATH | 300 | Intermediate | Calculus II 59 | MATH | 600 | Advanced | Linear Algebra 61 | CHEM | 600 | Advanced | Organic Chemistry 34 | JAVA | 999 | Basic | Intro to Java 43 | JAVA | 999 | Intermediate | AWT Programming 62 | JAVA | 999 | Advanced | Database Programming
All the Java courses were appended to the end of the table. Let's sort the result set by the Course_ID column:
java StatementApp "SELECT * FROM courses ORDER BY Course_ID" SELECT * FROM courses ORDER BY Course_ID Course_ID | Department_ID | CourseNumber | CourseLevel | CourseName 34 | JAVA | 999 | Basic | Intro to Java 35 | BIOL | 100 | Basic | Physiology 37 | BIOL | 300 | Intermediate | Plant Biology 38 | BIOL | 600 | Advanced | Microbiology 39 | BIOL | 310 | Intermediate | Neurobiology 40 | BIOL | 620 | Advanced | Neurobiology
41 | CHEM | 100 | Basic | General Chemistry
42 | CHEM | 300 | Intermediate | Analytical Chemistry 43 | JAVA | 999 | Intermediate | AWT Programming 51 | MATH | 100 | Basic | Calculus I 52 | MATH | 300 | Intermediate | Calculus II 59 | MATH | 600 | Advanced | Linear Algebra 61 | CHEM | 600 | Advanced | Organic Chemistry 62 | JAVA | 999 | Advanced | Database Programming
The Database Programming course could apply to any programming language. Let's use the UPDATE statement to change it to Advanced JDBC.
java StatementApp "UPDATE courses SET CourseName = `Advanced ÂJDBC' WHERE Department_ID = `JAVA' AND CourseLevel = `Advanced'" UPDATE courses SET CourseName = `Advanced JDBC' WHERE Department_ID = `JAVA' AND Â CourseLevel = `Advanced'
Once again, we'll display the courses table, sorted by the Course_ID column:
java StatementApp "SELECT * FROM courses ORDER BY Course_ID" SELECT * FROM courses ORDER BY Course_ID Course_ID | Department_ID | CourseNumber | CourseLevel | CourseName 34 | JAVA | 999 | Basic | Intro to Java 35 | BIOL | 100 | Basic | Physiology 37 | BIOL | 300 | Intermediate | Plant Biology 38 | BIOL | 600 | Advanced | Microbiology 39 | BIOL | 310 | Intermediate | Neurobiology 40 | BIOL | 620 | Advanced | Neurobiology 41 | CHEM | 100 | Basic | General Chemistry 42 | CHEM | 300 | Intermediate | Analytical Chemistry 43 | JAVA | 999 | Intermediate | AWT Programming 51 | MATH | 100 | Basic | Calculus I 52 | MATH | 300 | Intermediate | Calculus II 59 | MATH | 600 | Advanced | Linear Algebra 61 | CHEM | 600 | Advanced | Organic Chemistry 62 | JAVA | 999 | Advanced | Advanced JDBC
The StatementApp program provides a lot of capability in a few lines of code. That's because it allows you to enter SQL statements directly to the database. It begins in the same way as ResultApp by loading the ids.sql.IDSDriver, connecting to the IDSExamples data set, and creating a Statement object. It then passes the program's command-line argument to the execute() method of the Statement interface. If the execute() method returns a true value, it invokes the getResultSet() method of the Statement interface to retrieve the ResultSet object of a query operation. It then invokes the displayResults() method to display the ResultSet.
import java.sql.*;
import java.util.*;
class StatementApp {
public static void main(String args[]) {
if(args.length!=1){
System.out.println("Usage: java StatementApp sql");
System.exit(0);
}
try{
// Load IDS driver
Class.forName("ids.sql.IDSDriver");
String url="jdbc:ids://cx122974-a.cv1.sdca.home.com:80/";
url+="conn?dbtype=odbc&dsn='IDSExamples'";
// Connect to database
Connection connection=DriverManager.getConnection(url);
Statement statement = connection.createStatement();
String sql=args[0];
System.out.println(sql);
// Execute SQL statement
boolean hasResults = statement.execute(sql);
if(hasResults){
// Retrieve result set
ResultSet result = statement.getResultSet();
if(result!=null) displayResults(result);
}
// Close database connection
connection.close();
}catch(Exception ex){
System.out.println(ex);
System.exit(0);
}
}
static void displayResults(ResultSet r) throws SQLException {
// Get meta data about result set
ResultSetMetaData rmeta = r.getMetaData();
// Use meta data to determine the number of columns
// in the result set
int numColumns=rmeta.getColumnCount();
// Display values of each column
for(int i=1;i<=numColumns;++i) {
if(i<numColumns)
System.out.print(rmeta.getColumnName(i)+" | ");
else
System.out.println(rmeta.getColumnName(i));
}
while(r.next()){
for(int i=1;i<=numColumns;++i) {
if(i<numColumns)
System.out.print(r.getString(i)+" | ");
else
System.out.println(r.getString(i).trim());
}
}
}
}
The java.sql package provides the Date, Time, and Timestamp classes as extensions to java.util.Date. They may be used as objects within database transactions. The Date class represents ANSI SQL DATE values in the YYYY-MM-DD format. The Time class represents ANSI SQL TIME values in the HH:MM:SS format. The Timestamp class represents ANSI SQL TIMESTAMP (DATE and TIME) values.
In this chapter, you were introduced to the JDBC. You learned about the classes of the java.sql package and learned how to work with JDBC drivers, connect to databases, execute SQL statements, and work with result sets. This chapter provided a quick introduction to the java.sql package. The next chapter, "Using JDBC," expands upon this chapter and provides further examples of the information you've learned.
© Copyright 1998, Macmillan Publishing. All rights reserved.