Wrox Press Java Programming 24-Hour Trainer 2nd (2015)
Lesson 21. Working with Relational DBMS Using JDBC
Business applications usually store data in the databases. In most of the enterprise applications, Relational Database Management Systems (RDBMSes) are used as data storage. They store the data records in tables. Each record (such as that of an employee) is represented by a table row, which consists of one or more columns or record fields (for example, name, address, hire date). RDBMSes understand the SQL language.
The most popular RDBMSes are Oracle, DB2, Sybase, Microsoft SQL Server, and MySQL Server. This lesson uses Apache Derby DB (also known as Java DB), which is included with Java SE for Windows, or you can separately install it on a Mac or Linux machine.
NoSQL Database Management Systems
Some database management systems are not relational—they don’t store data as rows and columns. Such database management systems are known as NoSQL databases (for example, MongoDB, Cassandra, Couchbase, and so on). This book doesn’t cover NoSQL databases. Refer to http://nosql-database.org for further information.
Java includes two packages that contain classes required for work with DBMSes: java.sql and javax.sql. The former contains commonly used classes such as Connection, Statement, and ResultSet. The latter is used for supporting database connectivity on the server side, containing classes such as DataSource and RowSet.
The JDBC API is not DBMS-specific—if you write a program that uses JDBC classes to retrieve/update data in Oracle, you’ll be using the same classes to work with MySQL Server or DB2. You just need the JDBC drivers from the corresponding DBMS vendor; the drivers hide their database specifics behind the same public JDBC API.
JDBC drivers either pass SQL statements from Java to a DBMS for execution or simply execute a program stored inside a DBMS (called a stored procedure). If some data has been retrieved as the result of these actions, your Java program will handle it by making appropriate calls to the JDBC API. Over the past 15 years the JDBC specification has been evolving and, at the time of this writing, most drivers comply with JDBC version 4.1.
In this lesson, all communications with the DBMS are made by supplying SQL statements to the JDBC API. There is an alternative way of working with data by using Java Persistence API (JPA), which is covered in Lesson 32. Some people prefer using object-relational mapping (ORM) frameworks, such as Hibernate, or those that reduce mundane JDBC programming like MyBatis; these frameworks are not covered in this book.
JDBC Driver Types
A JDBC driver plays the role of the middleman between a Java program and a DBMS. Drivers are available from database vendors, from Oracle, and from third-party vendors of Java application servers.
There are four general types of JDBC drivers:
· A Type 1 driver is a JDBC-ODBC bridge that enables Java programs to work with the database using ODBC drivers from Microsoft. The drawbacks of ODBC drivers are that they are slower than the others, must be installed and configured on each user’s machine, and work only on Windows machines. The Type 1 JDBC driver has rarely been used lately, and Oracle has removed it as of Java 8 run time.
· A Type 2 driver consists of Java classes that work in conjunction with the non-Java native drivers provided by the DBMS vendor. These drivers work much faster than Type 1, but they also require installation and configuration on the machine on which Java programs run.
· A Type 3 driver is called a middleware driver and can be provided by vendors of application servers. It consists of two parts: The client portion performs a DBMS-independent SQL call, which is then translated to a specific DBMS protocol by the server portion of the driver.
· A Type 4 driver is a pure Java driver, which usually comes as a .jar file and performs direct calls to the database server. It does not need any configuration on the client’s machine, other than including the name of the main driver’s class in your Java code. That’s why it’s also known as thethin driver. For example, Java applets can be packaged with this type of driver, which can be automatically downloaded to the user’s machine along with the applets themselves.
For simplicity, this lesson uses JDBC drivers of Type 4, but many production systems can deploy Type 3 drivers to provide better performance.
Installing Derby DB and Creating a Database
Derby DB (also known as JavaDB) is a small DBMS that you wouldn’t use for serious production systems, but it’s great for learning JDBC or to use for many small systems. If you’re using Windows OS, it’s already installed with Java SDK in the folder that looks similar to this one: c:\Program Files\Java\jdk1.8.0_25\db. The numbers reflect the major and minor versions of Java.
Modify your system variable PATH so it starts with the following: c:\Program Files\Java\jdk1.8.0_25\db\bin;
If you work in something other than Windows OS, download and install Derby DB from http://db.apache.org/derby. Derby DB is well-documented and if you haven’t had a chance to work with relational DBMSes, download and read the “Getting Started with Derby” manual. The installation of Derby DB comes down to downloading and unzipping one file. At the time of this writing the filename is db-derby-10.10.2.0-bin.zip. Unzip it and rename the folder as derby.
The configuration process is also simple; read the steps required for your operating system at the Swinburne University web page at http://goo.gl/Q5a01N. You need to set the DERBY_HOME environment variable to point to your Derby DB installation directory. For example, I’ve unzipped Derby into my root directory on my Mac OS computer and added the following two lines to the file .bash_profile:
export PATH=~/derby/bin:$PATH
export DERBY_HOME=~/derby
To insure that your Derby DB is properly installed, open a Command or Terminal window and enter the command sysinfo. You should see an output describing the Derby DB install.
Derby DB has an interactive command-line utility called ij that you can use to create databases and tables and populate them with data, among other actions. I show you how to create a sample database and a table to store data about employees.
First open a Command or Terminal window and issue the command startNetworkServer.exe (or startNetworkServer if you use Mac OS).
The Derby DB server starts by displaying the message similar to this one:
Apache Derby Network Server - 10.10.2.0 - (1582446) started and ready to
accept connections on port 1527
Open another command window and start ij—you’ll see the ij> prompt. Now try connecting to the database Lesson21 by issuing the following command:
connect 'jdbc:derby://localhost:1527/Lesson21;create=true';
This command tries to connect to the database Lesson21 and creates it if no such database is found. The next ij command creates a database table—Employee—to store records that consist of three fields: EMPNO, ENAME, and JOB_TITLE. The first field is stored as an integer, and the other two as simple text (varchar) allowing 50 and 150 characters respectively.
CREATE TABLE Employee (
EMPNO int NOT NULL,
ENAME varchar (50) NOT NULL,
JOB_TITLE varchar (150) NOT NULL
);
Finally, to populate the table with some data, issue the INSERT command in ij:
INSERT INTO Employee values (7369,'John Smith', 'Clerk'), (7499,
'Joe Allen','Salesman'), (7521,'Mary Lou','Director');
If you want to ensure that the records were successfully created in the database, in the ij utility issue the SELECT SQL statement to retrieve the data:
Select * from Employee;
You see the data about the three employees that were added by the INSERT SQL command. If you are not familiar with the syntax of SQL, refer to the tutorial at http://www.sqlcourse.com.
Sample JDBC Program
In this section you see the steps that you can perform to retrieve the data in any Java program that works with a relational database using JDBC. A sample program implements all of these steps to display the list of employees from the database table Employee.
1. Load the JDBC driver using the method forName() of the Java class Class. You have to find out the name of the class to load from the JDBC driver’s documentation. In the case of JavaDB, you can skip this step. If you work with Oracle DBMSes, you can load a Type 4 JDBC driver with the following Java statement:
2. Class.forName("oracle.jdbc.driver.OracleDriver");
3. Get the database connection to the database Lesson21 by calling
DriverManager.getConnection(url, user, password);
In the case of Derby DB, you don’t have to supply the user and the password; simply provide the URL of your database, for example:
DriverManager.getConnection("jdbc:derby:Lesson21");
4. Create an instance of the Java class Statement:
Connection.createStatement();
As an alternative, you can create PreparedStatement or CallableStatement, which are explained later in this lesson in the “The PreparedStatement Class” and “The CallableStatement Class” sections.
5. To run SQL Select queries, your program can include a statement similar to this one:
Statement.executeQuery("Select * from Employee");
For SQL queries, which produce more than one result, you can use the method execute() of the class Statement.
For Insert, Update, and Delete SQL statement, use the method updateQuery(). For example:
String myInsertStmt = "INSERT INTO Employee values " +
"(1234,'John Bush', 'Clerk')";
Statement.updateQuery(myInsertStmt);
6. To process a received data loop through the ResultSet object, use the following:
7. while (ResultSet.next()) {
8. // get the values from each column here
}
9. Free system resources by closing the ResultSet, Statement, and Connection objects.
All these steps are implemented in the class EmployeeList, shown in Listing 21-1, which prints the records from the table Employee. Even though you don’t need to explicitly load the driver for Derby DB with Class.forName(), the location of the driver class has to be known to your program, otherwise you’ll get a “No suitable driver” error. Either add derbyclient.jar located in the lib directory in your Derby installation to the CLASSPATH system variable, or just add it as an external .jar to your Eclipse project (see the project menu Properties → Java Build Panel → Add External JARs).
Listing 21-1: The EmployeeList program
class EmployeeList {
public static void main(String argv[]) {
String sqlQuery = "SELECT * from Employee";
// Open autocloseable Connection, Statement and get the result set
try (Connection conn = DriverManager.getConnection(
"jdbc:derby://localhost:1527/Lesson21");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sqlQuery); ) {
// Process each column in the result set and print the data
while (rs.next()){
int empNo = rs.getInt("EMPNO");
String eName = rs.getString("ENAME");
String job = rs.getString("JOB_TITLE");
System.out.println(""+ empNo + ", " + eName + ", " + job );
}
} catch( SQLException se ) {
System.out.println ("SQLError: " + se.getMessage ()
+ " code: " + se.getErrorCode ());
} catch( Exception e ) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
The output of the EmployeeList program looks like this:
7369, John Smith, CLERK
7499, Joe Allen, SALESMAN
7521, Mary Lou, Director
When you execute any SQL statements, always include error-handling code. Catching the SQLException is the right way to get the error message. Note that the code in Listing 21-1 calls the method getErrorCode() to extract the database-specific error code from the SQLException object.
Processing Result Sets
Let’s take a closer look at the code in Listing 21-1. After rs = stmt.executeQuery(sqlQuery), the cursor rs is positioned before the very first record (row) of the result set in memory, if any. Each row contains as many fields (columns) as were specified in the SQL Select statement. Each of the values is extracted by an appropriate method based on the data type of the field. The names of these methods are self-explanatory: rs.getString(), rs.getInt(), and so on. If you know the name of a column from the result, use it as a method argument:
int empNo = rs.getInt("EMPNO");
String eName = rs.getString("ENAME");
If you don’t know the column names, specify the relative position of the column (they start with 1) from the result set:
int empNo = rs.getInt(1);
String eName = rs.getString(2);
You can also query the database table to figure our the column names and their types with the help of the class ResultSetMetaData explained later in this lesson. JDBC drivers automatically convert the data from the database types to the corresponding Java types: For example, Derby’s varchar becomes Java’sString.
The class EmployeeList just prints the retrieved data in a loop. You can also place the result set data in a Java collection object for further processing. The ResultSet object holds the database connection and is not serializable. That’s why common practice for programming server-side operations with DBMSes is to create a class representing a row from the result set and populate, say, an ArrayList or other Java collection with its instances.
Listing 21-2 shows an example of such a class, which can represent one employee record. Classes that hold only the value of some data are often called value objects. Because in distributed applications such objects may need to be transferred between different computers, they are also known as Data Transfer Objects (DTOs).
Listing 21-2: The EmployeeDTO
class EmployeeDTO{
//private properties
private int empNo;
private String eName;
private String jobTitle;
//setters
public void setEmpNo(int val){empNo=val;}
public void setEName(String val){eName=val;}
public void setJobTitle(String val){jobTitle=val;}
// getters
public int getEmpNo(){return empNo;}
public String getEName(){return eName;}
public String getJobTitle(){return jobTitle;}
}
EmployeeDTO declares private variables to store the data but access to this data is performed via public setters and getters, the methods that allow external code to set and get the appropriate values. This technique can be useful when some application-specific logic has to be applied at the moment when some code needs to get or modify the properties of the class EmployeeDTO.
For example, you can place some authorization code inside the setter to ensure that the external object has enough permissions to change the property jobTitle. If the business logic of obtaining such authorization changes in the future, you need to modify only the code inside the setter, but the external code remains unchanged.
The next code snippet shows how to prepare a collection of EmployeeDTO objects while processing the result set retrieved by the SQL Select statement.
// Create an object for collection of employees
ArrayList<EmployeeDTO> employees = new ArrayList<>();
// Process ResultSet and populate the collection
while (rs.next()){
EmployeeDTO currentEmp = new EmployeeDTO();
currentEmp.setEmpNo(rs.getInt("EMPNO"));
currentEmp.setEName(rs.getString("ENAME"));
currentEmp.setJobTitle(rs.getString("JOB_TITLE"));
employees.add(currentEmp);
}
If this code is deployed on the server’s JVM and you need to send the data to another computer that runs, say, a Swing client, you can consider applying Java serialization here for sending a collection of employees to the front. But make sure that the class EmployeeDTO implements theSerializable interface. In case of a web client, consider serializing the EmployeeDTO into JSON data format discussed in Lesson 33.
The PreparedStatement Class
Listing 21-1 uses the class Statement to create an object capable of executing SQL. But this is not the only way to supply SQL to the JDBC API. The class PreparedStatement is a subclass of Statement, but it pre-compiles the SQL statement before executing it.
With PreparedStatement you can create SQL with parameters that are dynamically passed by the program. Suppose you need to execute the query “SELECT * from EMP WHERE empno=...” multiple times, providing the empno values from the array empNumbers[]. If you use the classStatement as in the following code snippet, the variable sqlQuery has to be modified and pre-compiled on each iteration of the loop:
for (int i=0;i<empNumbers.length; i++){
sqlQuery="SELECT * from Employee WHERE empno=" + employees[i];
stmt.executeQuery(sqlQuery);
}
The class PreparedStatement offers a more efficient solution:
PreparedStatement stmt=conn.prepareStatement(
" SELECT * from Employee WHERE empno=?");
for (int i=0;i<employees.length; i++){
// pass the array's value that substitutes the question mark
stmt.setInt(1,employees[i]);
stmt.executeQuery();
}
In this case, the SQL statement is compiled only once and parameters are provided by the appropriate setXXX() method depending on the data type. The SQL statement may have several parameters (question marks), and the first argument of the setter enables you to specify each parameter’s number. For example:
PreparedStatement stmt=conn.prepareStatement(
"SELECT * from Employee WHERE empno=? and ename=?");
for (int i=0;i<empNumbers.length; i++){
stmt.setInt(1,empNumbers[i];)
stmt.setString(2,empNames[i];)
stmt.executeQuery();
}
If you need to pass a NULL value as a parameter, use the method setNull().
The CallableStatement Class
This class extends PreparedStatement and is used for executing database stored procedures from Java. Let’s say there is a stored procedure entitled changeEmpTitle that takes two parameters: empno and title. Here’s the code to execute this stored procedure:
CallableStatement stmt = conn.prepareCall("{call changeEmpTitle(?,?)}");
stmt.setInt(1,7566);
stmt.setString (2,"Salesman");
stmt.executeUpdate();
If a stored procedure returns some values using output parameters, each of the OUT data types has to be registered before the statement is executed. The next code snippet shows you an example of executing a stored procedure that has two parameters: The first is an input parameter, and the second is an output parameter by which the stored procedure can return the result of its execution to the Java program:
CallableStatement stmt = conn.prepareCall(
("{call getEmpTitle(?,?) }");
stmt.setInt(1, 7566);
stmt.registerOutParameter(2,java.sql.Types.VARCHAR);
stmt.executeQuery();
String title=stmt.getString(2);
The ResultSetMetaData Class
JDBC enables you to process result sets when the number of returned values is unknown. Imagine that you need to write a program that can accept any SQL Select statement, execute it, and display the retrieved data. With the class ResultSetMetaData, you can dynamically find out how many columns there are in the result set, as well as their names and data types. The following code fragment gets the number of the database table columns in the result set and for each of them identifies and prints the column name and type:
String sqlQuery = "select * from Employee";
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsMeta = rs.getMetaData();
int colCount = rsMeta.getColumnCount();
for (int i = 1; i <= colCount; i++) {
System.out.println(
" Column name: " + rsMeta.getColumnName(i) +
" Column type: " + rsMeta.getColumnTypeName(i));
}
This simple but powerful technique is used internally by ORM frameworks that can “magically” generate database models and automatically generate Java classes representing database entities.
Listing 21-3 shows a Java program called ShowAnyData that prints a result set based on any SQL Select statement passed from the command line. For example, it can be started as follows:
java ShowAnyData "Select * from Employee"
Listing 21-3: Using ResultSetMetaData
class ShowAnyData {
public static void main(String args[]) {
if (args.length==0){
System.out.println(
"Usage: java ShowAnyData SQLSelectStatement");
System.out.println(
"For example: java ShowAnyData \"Select * from Employee\"");
System.exit(1);
}
try (Connection conn = DriverManager.getConnection(
"jdbc:derby://localhost:1527/Lesson21");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(args[0]);) {
// Find out the number of columns, their names
// and display the data
ResultSetMetaData rsMeta = rs.getMetaData();
int colCount = rsMeta.getColumnCount();
for (int i = 1; i <= colCount; i++) {
System.out.print(rsMeta.getColumnName(i) + " ");
}
System.out.println();
while (rs.next()){
for (int i = 1; i <= colCount; i++) {
System.out.print(rs.getString(i) + " ");
}
System.out.print("\n"); // new line character
}
} catch( SQLException se ) {
System.out.println ("SQLError: " + se.getMessage ()
+ " code: " + se.getErrorCode ());
} catch( Exception e ) {
e.printStackTrace();
}
}
}
The output of the ShowAnyData program is the same as that of EmployeeList shown in Listing 21-1. But the ShowAnyData program can execute any SQL SELECT statement as long as you are specifying valid database and table(s) names. Note that the code in ShowAnyData first ensures that you have passed the command-line argument. If you run this program from a command line, don’t forget to include the SQL statement in double quotes. In Eclipse you can specify a command-line argument by selecting the Arguments tab in the Run Configuration panel.
Scrollable Result Sets and Rowset
In all the preceding examples, the code traversed the result set using the method next(), which moves the cursor only forward. Another option is to create a scrollable result set so the cursor can be moved back and forth if need be. There is a two-argument version of the methodcreateStatement(). The first argument specifies the type of scrolling (TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE) and the second makes the result set updateable or read-only (CONCUR_READ_ONLY or CONCUR_UPDATABLE). For example,
Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * from Employee");
The TYPE_FORWARD_ONLY parameter allows only forward movement of the cursor. The difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE is in whether scrolling reflects changes that have been made to the result set. The next example sets the cursor at the end of the result set and moves the cursor backward:
rs.afterLast();
while (rs.previous()){
int empNo = rs.getInt("EMPNO");
String eName = rs.getString("ENAME");
String job = rs.getString("JOB_TITLE");
System.out.println(""+ empNo + ", " + eName + ", " + job);
}
You can also move the cursor to a specific row by using the following self-explanatory methods:
rs.absolute(25); // moves the cursor to the 25th row
rs.relative(-4); // moves the cursor to the 21st row
rs.first();
rs.last();
rs.beforeFirst();
If the result set is updatable (CONCUR_UPDATABLE) then you can modify the underlying database table while scrolling. For example, the following statements update the job title of the employee based on the current cursor’s position:
rs.updateString("JOB_TITLE","Manager");
rs.updateRow();
Scrollable result sets enable you to traverse the result set in both directions, but they have a drawback: They hold the database connection, which may be required by another thread or program. The package javax.sql includes the interface RowSet, which is inherited from ResultSet. RowSetgets the data from the database, then disconnects, but still allows Java to work with the data. The package javax.sql.rowset has several concrete classes that implement RowSet, such as CachedRowSet, FilteredRowSet, and WebRowSet. The latter can turn RowSet into an XML stream to be sent to another tier in the distributed application.
Transactional Updates
Transaction is a logical unit of work. Sometimes several database modifications have to be processed as one transaction, and if one of the updates fails, the whole transaction has to be rolled back. These database operations have to be explicitly committed (finalized) in case of success. If you set the auto-commit parameter on the database connection to false, the database transaction is not committed until the code explicitly calls the method commit(), as in the following example:
try{
conn.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.addBatch("insert into Orders " +
"values(123, 'Buy','IBM',200)");
stmt.addBatch("insert into OrderDetail " +
"values('JSmith', 'Broker131', '05/20/02')");
stmt.executeBatch();
conn.commit(); // Transaction succeded
}catch(SQLException e){
conn.rollback(); // Transaction failed
e.printStackTrace();
}
In the preceding code snippet, two Insert statements have to be executed as one transaction, and if any of them fails, an exceptionis thrown and the method rollback() undoes all the changes, including those that succeeded.
Connection Pools and DataSource
Up until now you’ve been running all sample Java programs on your own computer. But imagine a distributed application in which multiple clients make requests to the same server, which has to process their SQL queries. Because obtaining a connection to the database is a slow process, it would be very inefficient to start every SQL request by obtaining a database connection and disconnecting after the request is complete. Such applications should reuse the same opened connection for multiple requests.
The package javax.sql includes the interface DataSource, which is an alternative to DriverManager. Vendors of JDBC drivers for servers implement this interface, and a DataSource is typically preconfigured for a certain number of connections (the connection pool). It is published in a directory using the JNDI interface. In such a setup, all clients’ requests get their database connections from this DataSource object, eliminating the need to open and close a new connection for each request. The DataSource objects are typically used on the server side bound to JNDI. But you can create an instance of a DataSource in any Java application. Chapter 29 provides an example of working with DataSource objects.
Try It
In this assignment you modify the class Portfolio from Chapter 21, which was just printing some hard-coded statements. Now you create and populate the database table Portfolio and then read and display the data from there.
Lesson Requirements
You should have Java installed.
NOTE You can download the code and resources for this “Try It” from the book’s web page at www.wrox.com/go/javaprog24hr2e. You can find them in the Lesson21.zip.
Hint
Obtaining a database connection is a slow operation, and doing it from inside the method run() every time you start a new thread is not the best solution. Consider creating a database connection up front and passing it to the thread before starting it.
Step-by-Step
1. In the database Lesson21 create the table Portfolio using the following SQL statement:
2. create table Portfolio(
3. id INTEGER NOT NULL,
4. symbol VARCHAR(10) NOT NULL,
5. quantity INTEGER NOT NULL,
6. price NUMERIC NOT NULL, PRIMARY KEY (id)
);
7. Populate the table Portfolio with three records, for stocks traded under the symbols IBM, AMZN, and AAPL respectively:
8. insert into Portfolio values (1,'IBM',500,105.50),
(2,'AMZN',1000,15.25),(3,'AAPL',2000,272.50);
9. Create a new Eclipse project.
10.Create a class called Portfolio that is similar to the one shown in Listing 17-5 from Lesson 17:
11. public class Portfolio implements Runnable {
12. public void run() {
13. System.out.println( "You have 500 shares of IBM ");
14. }
}
15. Modify the code of Portfolio: instead of just printing “You have 500 shares of IBM,” have it connect to the database, select all the data from the table Portfolio, and print the symbol, quantity, and total value. Calculate the total value by multiplying price by quantity.
16.Create a testing class called ShowMyPortfolio that instantiates and starts the thread Portfolio.
17.Test this program.
TIP Please select the videos for Lesson 21 online at www.wrox.com/go/javaprog24hr2e. You will also be able to download the code and resources for this lesson from the website.