Advanced Java Programming - JDBC

Advanced Java Programming
 - JDBC
Structure
7.1 Introduction
Objectives
Self Assessment Questions
7.2 JDBC Product Components
Self Assessment Questions
7.3 A Relational Database Overview
7.4 JDBC Architecture
Self Assessment Questions
7.5 Establishing a Connection
Self Assessment Questions
7.6 Retrieving Values from Result Sets
Self Assessment Questions
7.7 Updating Tables
7.8 Creating Complete JDBC Applications
7.9 Running the Sample Applications
7.10 Summary
7.11 Terminal Questions
7.1 Introduction
The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database.
JDBC helps you to write java applications that manage these three programming activities:
1. Connect to a data source, like a database
2. Send queries and update statements to the database
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 160
3. Retrieve and process the results received from the database in answer to your query
The following simple code fragment gives a simple example of these three steps:
Connection.con=DriverManager.getConnection("jdbc:myDriver:wombat", "myLogin","myPassword");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) {
int x = rs.getInt("a");
String s = rs.getString("b");
float f = rs.getFloat("c");
}
This short code fragment instantiates a DriverManager object to connedatabase driver and log into the database, instantiates a Statement object that carries your SQL language query to the database; instantiates a ResultSet object that retrieves the results of your query, and executes a simple while loop, which retrieves and displays those results. It's that simple.
Objectives
In this chapter, you will learn about the:-
 JDBC Product Component.
 How to handle Database through Java
 JDBC Architecture
Self Assessment Questions
1. What do you mean by JDBC?
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 161
7.2 JDBC Product Components
JDBC includes four components:
TheJDBCAPI – The JDBC™ API provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment.
The JDBC API is part of the Java platform, which includes the Java™ Standard Edition (Java™ SE ) and the Java™ Enterprise Edition (Java™ EE). The JDBC 4.0 API is divided into two packages: java.sql and javax.sql. Both packages are included in the Java SE and Java EE platforms.
JDBCDriverManager – The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture.The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface™ (JNDI) naming service to establish a connection with a data source. You can use either connecting mechanism, but using a DataSource object is recommended whenever possible.
JDBCTestSuite –
The JDBC driver test suite helps you to determine that JDBC drivers will run your program. These tests are not comprehensive or exhaustive, but they do exercise many of the important features in the JDBC API.
JDBC-ODBCBridge – The Java Software bridge provides JDBC access via ODBC drivers. Note that you need to load ODBC binary code onto each client machine that uses this driver. As a result, the ODBC driver is most appropriate on a corporate
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 162
network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.
This Trail uses the first two of these these four JDBC components to connect to a database and then build a java program that uses SQL commands to communicate with a test Relational Database. The last two components are used in specialized environments to test web applications, or to communicate with ODBC-aware DBMSs.
Self Assessment Questions
2. What are the different components of JDBC?
7.3 A Relational Database Overview
A database is a means of storing information in such a way that information can be retrieved from it. In simplest terms, a relational database is one that presents information in tables with rows and columns. A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). Data in a table can be related according to common keys or concepts, and the ability to retrieve related data from a table is the basis for the term relational database. A Database Management System (DBMS) handles the way data is stored, maintained, and retrieved. In the case of a relational database, a Relational Database Management System (RDBMS) performs these tasks. DBMS as used in this book is a general term that includes RDBMS. Integrity Rules
Relational tables follow certain integrity rules to ensure that the data they contain stay accurate and are always accessible. First, the rows in a relational table should all be distinct. If there are duplicate rows, there can be problems resolving which of two possible selections is the correct one. For most DBMSs, the user can specify that duplicate rows are not allowed,
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 163
and if that is done, the DBMS will prevent the addition of any rows that duplicate an existing row.
A second integrity rule of the traditional relational model is that column values must not be repeating groups or arrays. A third aspect of data integrity involves the concept of a null value. A database takes care of situations where data may not be available by using a null value to indicate that a value is missing. It does not equate to a blank or zero. A blank is considered equal to another blank, a zero is equal to another zero, but two null values are not considered equal.
When each row in a table is different, it is possible to use one or more columns to identify a particular row. This unique column or group of columns is called a primary key. Any column that is part of a primary key cannot be null; if it were, the primary key containing it would no longer be a complete identifier. This rule is referred to as entity integrity.
Table 1.2 illustrates some of these relational database concepts. It has five columns and six rows, with each row representing a different employee.
Employee_Number
First_name
Last_Name
Date_of_Birth
10001
Axel
Washington
28-Aug-43
10083
Arvid
Sharma
24-Nov-54
10120
Jonas
Ginsberg
01-Jan-69
10005
Florence
Wojokowski
04-Jul-71
10099
Sean
Washington
21-Sep-66
10035
Elizabeth
Yamaguchi
24-Dec-59
The primary key for this table would generally be the employee number because each one is guaranteed to be different. (A number is also more efficient than a string for making comparisons.) It would also be possible to use First_Name and Last_Name because the combination of the two also identifies just one row in our sample database. Using the last name alone
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 164
would not work because there are two employees with the last name of "Washington." In this particular case the first names are all different, so one could conceivably use that column as a primary key, but it is best to avoid using a column where duplicates could occur. If Elizabeth Taylor gets a job at this company and the primary key is First_Name, the RDBMS will not allow her name to be added (if it has been specified that no duplicates are permitted). Because there is already an Elizabeth in the table, adding a second one would make the primary key useless as a way of identifying just one row. Note that although using First_Name and Last_Name is a unique composite key for this example, it might not be unique in a larger database. Note also that Table 1.2 assumes that there can be only one car per employee. SELECT Statements
SQL is a language designed to be used with relational databases. There is a set of basic SQL commands that is considered standard and is used by all RDBMSs. For example, all RDBMSs use the SELECT statement.
A SELECT statement, also called a query, is used to get information from a table. It specifies one or more column headings, one or more tables from which to select, and some criteria for selection. The RDBMS returns rows of the column entries that satisfy the stated requirements. A SELECT statement such as the following will fetch the first and last names of employees who have company cars:
SELECT First_Name, Last_Name
FROM Employees
WHERE Car_Number IS NOT NULL
The result set (the set of rows that satisfy the requirement of not having null in the Car_Number column) follows. The first name and last name are
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 165
printed for each row that satisfies the requirement because the SELECT statement (the first line) specifies the columns First_Name and Last_Name. The FROM clause (the second line) gives the table from which the columns will be selected.
FIRST_NAME LAST_NAME
Axel Washington
Florence Wojokowski
The following code produces a result set that includes the whole table because it asks for all of the columns in the table Employees with no restrictions (no WHERE clause). Note that SELECT * means "SELECT all columns."
SELECT *
FROM Employees WHERE Clauses
The WHERE clause in a SELECT statement provides the criteria for selecting values. For example, in the following code fragment, values will be selected only if they occur in a row in which the column Last_Name begins with the string 'Washington'.
SELECT First_Name, Last_Name
FROM Employees
WHERE Last_Name LIKE 'Washington%'
The keyword LIKE is used to compare strings, and it offers the feature that patterns containing wildcards can be used. For example, in the code fragment above, there is a percent sign (%) at the end of 'Washington', which signifies that any value containing the string 'Washington' plus zero or more additional characters will satisfy this selection criterion. So 'Washington' or 'Washingtonian' would be matches, but 'Washing' would not
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 166
be. The other wildcard used in LIKE clauses is an underbar (_), which stands for any one character. For example,
WHERE Last_Name LIKE 'Ba_man'
would match 'Batman', 'Barman', 'Badman', 'Balman', 'Bagman', 'Bamman', and so on.
The code fragment below has a WHERE clause that uses the equal sign (=) to compare numbers. It selects the first and last name of the employee who is assigned car 12.
SELECT First_Name, Last_Name
FROM Employees
WHERE Car_Number = 12
The next code fragment selects the first and last names of employees whose employee number is greater than 10005:
SELECT First_Name, Last_Name
FROM Employees
WHERE Employee_Number > 10005
WHERE clauses can get rather elaborate, with multiple conditions and, in some DBMSs, nested conditions. This overview will not cover complicated WHERE clauses, but the following code fragment has a WHERE clause with two conditions; this query selects the first and last names of employees whose employee number is less than 10100 and who do not have a company car.
SELECT First_Name, Last_Name
FROM Employees
WHERE Employee_Number < 10100 and Car_Number IS NULL
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 167
A special type of WHERE clause involves a join, which is explained in the next section. Joins
A distinguishing feature of relational databases is that it is possible to get data from more than one table in what is called a join. Suppose that after retrieving the names of employees who have company cars, one wanted to find out who has which car, including the make, model, and year of car. This information is stored in another table, Cars, shown in Table 1.3.
Car Number
Make
Model
5
Honda
Civic DX
12
Toyota
Corolla
Car Number
Make
Model
Table 1.3: Cars
There must be one column that appears in both tables in order to relate them to each other. This column, which must be the primary key in one table, is called the foreign key in the other table. In this case, the column that appears in two tables is Car_Number, which is the primary key for the table Cars and the foreign key in the table Employees. If the 1996 Honda Civic were wrecked and deleted from the Cars table, then Car_Number 5 would also have to be removed from the Employees table in order to maintain what is called referential integrity. Otherwise, the foreign key column (Car_Number) in Employees would contain an entry that did not refer to anything in Cars. A foreign key must either be null or equal to an existing primary key value of the table to which it refers. This is different from a primary key, which may not be null. There are several null values in the Car_Number column in the table Employees because it is possible for an employee not to have a company car.
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 168
The following code asks for the first and last names of employees who have company cars and for the make, model, and year of those cars. Note that the FROM clause lists both Employees and Cars because the requested data is contained in both tables. Using the table name and a dot (.) before the column name indicates which table contains the column.
SELECT Employees.First_Name, Employees.Last_Name, Cars.Make,
Cars.Model, Cars.Year
FROM Employees, Cars
WHERE Employees.Car_Number = Cars.Car_Number
This returns a result set that will look similar to the following:
FIRST_NAME LAST_NAME MAKE MODEL YEAR
Axel Washington Honda CivicDX 1996
Florence Wojokowski Toyota Corolla 1999 Common SQL Commands
SQL commands are divided into categories, the two main ones being Data Manipulation Language (DML) commands and Data Definition Language (DDL) commands. DML commands deal with data, either retrieving it or modifying it to keep it up-to-date. DDL commands create or change tables and other database objects such as views and indexes.
A list of the more common DML commands follows:
SELECT – used to query and display data from a database. The SELECT statement specifies which columns to include in the result set. The vast majority of the SQL commands used in applications are SELECT statements.
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 169
INSERT – adds new rows to a table. INSERT is used to populate a newly created table or to add a new row (or rows) to an already-existing table.
DELETE – removes a specified row or set of rows from a table
UPDATE – changes an existing value in a column or group of columns in a table
The more common DDL commands follow:
CREATE TABLE – creates a table with the column names the user provides. The user also needs to specify a type for the data in each column. Data types vary from one RDBMS to another, so a user might need to use metadata to establish the data types used by a particular database. CREATE TABLE is normally used less often than the data manipulation commands because a table is created only once, whereas adding or deleting rows or changing individual values generally occurs more frequently.
DROP TABLE – deletes all rows and removes the table definition from the database. A JDBC API implementation is required to support the DROP TABLE command as specified by SQL92, Transitional Level. However, support for the CASCADE and RESTRICT options of DROP TABLE is optional. In addition, the behavior of DROP TABLE is implementation-defined when there are views or integrity constraints defined that reference the table being dropped.
ALTER TABLE – adds or removes a column from a table. It also adds or drops table constraints and alters column attributes Result Sets and Cursors
The rows that satisfy the conditions of a query are called the result set. The number of rows returned in a result set can be zero, one, or many. A user can access the data in a result set one row at a time, and a cursor provides
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 170
the means to do that. A cursor can be thought of as a pointer into a file that contains the rows of the result set, and that pointer has the ability to keep track of which row is currently being accessed. A cursor allows a user to process each row of a result set from top to bottom and consequently may be used for iterative processing. Most DBMSs create a cursor automatically when a result set is generated.
Earlier JDBC API versions added new capabilities for a result set's cursor, allowing it to move both forward and backward and also allowing it to move to a specified row or to a row whose position is relative to another row. Transactions
When one user is accessing data in a database, another user may be accessing the same data at the same time. If, for instance, the first user is updating some columns in a table at the same time the second user is selecting columns from that same table, it is possible for the second user to get partly old data and partly updated data. For this reason, DBMSs use transactions to maintain data in a consistent state (data consistency) while allowing more than one user to access a database at the same time (data concurrency).
A transaction is a set of one or more SQL statements that make up a logical unit of work. A transaction ends with either a commit or a rollback, depending on whether there are any problems with data consistency or data concurrency. The commit statement makes permanent the changes resulting from the SQL statements in the transaction, and the rollback statement undoes all changes resulting from the SQL statements in the transaction.
A lock is a mechanism that prohibits two transactions from manipulating the same data at the same time. For example, a table lock prevents a table from being dropped if there is an uncommitted transaction on that table. In some
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 171
DBMSs, a table lock also locks all of the rows in a table. A row lock prevents two transactions from modifying the same row, or it prevents one transaction from selecting a row while another transaction is still modifying it. Stored Procedures
A stored procedure is a group of SQL statements that can be called by name. In other words, it is executable code, a mini-program, that performs a particular task that can be invoked the same way one can call a function or method. Traditionally, stored procedures have been written in a DBMS-specific programming language. The latest generation of database products allows stored procedures to be written using the Java programming language and the JDBC API. Stored procedures written in the Java programming language are bytecode portable between DBMSs. Once a stored procedure is written, it can be used and reused because a DBMS that supports stored procedures will, as its name implies, store it in the database.
The following code is an example of how to create a very simple stored procedure using the Java programming language. Note that the stored procedure is just a static Java method that contains normal JDBC code. It accepts two input parameters and uses them to change an employee's car number.
Do not worry if you do not understand the example at this point. The code example below is presented only to illustrate what a stored procedure looks like. You will learn how to write the code in this example in the tutorials that follow.
import java.sql.*;
public class UpdateCar {
public static void UpdateCarNum(int carNo, int empNo)
throws SQLException {
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 172
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DriverManager.getConnection("jdbc:default:connection");
pstmt = con.prepareStatement(
"UPDATE EMPLOYEES SET CAR_NUMBER = ? " +
"WHERE EMPLOYEE_NUMBER = ?");
pstmt.setInt(1, carNo);
pstmt.setInt(2, empNo);
pstmt.executeUpdate();
}
finally {
if (pstmt != null) pstmt.close();
}
}
} Metadata
Databases store user data, and they also store information about the database itself. Most DBMSs have a set of system tables, which list tables in the database, column names in each table, primary keys, foreign keys, stored procedures, and so forth. Each DBMS has its own functions for getting information about table layouts and database features. JDBC provides the interface DatabaseMetaData, which a driver writer must implement so that its methods return information about the driver and/or DBMS for which the driver is written. For example, a large number of methods return whether or not the driver supports a particular functionality. This interface gives users and tools a standardized way to get metadata. In
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 173
general, developers writing tools and drivers are the ones most likely to be concerned with metadata.
7.4 JDBC Architecture
The JDBC API supports both two-tier and three-tier processing models for database access.
Fig. 7.4.1: Two-tier Architecture for Data Access
In the two-tier model, a Java application talks directly to the data source. This requires a JDBC driver that can communicate with the particular data source being accessed. A user's commands are delivered to the database or other data source, and the results of those statements are sent back to the user. The data source may be located on another machine to which the user is connected via a network. This is referred to as a client/server configuration, with the user's machine as the client, and the machine housing the data source as the server. The network can be an intranet, which, for example, connects employees within a corporation, or it can be the Internet.
In the three-tier model, commands are sent to a "middle tier" of services, which then sends the commands to the data source. The data source processes the commands and sends the results back to the middle tier,
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 174
which then sends them to the user. MIS directors find the three-tier model very attractive because the middle tier makes it possible to maintain control over access and the kinds of updates that can be made to corporate data. Another advantage is that it simplifies the deployment of applications. Finally, in many cases, the three-tier architecture can provide performance advantages.
Error!
Fig. 7.4.2: Three-tier Architecture for Data Access
Until recently, the middle tier has often been written in languages such as C or C++, which offer fast performance. However, with the introduction of optimizing compilers that translate Java bytecode into efficient machine-specific code and technologies such as Enterprise JavaBeans™, the Java platform is fast becoming the standard platform for middle-tier development. This is a big plus, making it possible to take advantage of Java's robustness, multithreading, and security features.
With enterprises increasingly using the Java programming language for writing server code, the JDBC API is being used more and more in the middle tier of a three-tier architecture. Some of the features that make JDBC
APPLICATION
JDBC
Driver
Manager
JDBC-
ODBC
Bridge
Driver
Access Driver
SQL Driver
MS-Access
Data-
base
MS_SQL Data-base
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 175
a server technology are its support for connection pooling, distributed transactions, and disconnected rowsets. The JDBC API is also what allows access to a data source from a Java middle tier.
Self Assessment Questions
3. What is the difference between relational database and simple database ?
4. What do you mean by Metadata ?
7.5 Establishing a Connection
First, you need to establish a connection with the DBMS you want to use. Typically, a JDBC™ application connects to a target data source using one of two mechanisms:
DriverManager: This fully implemented class requires an application to load a specific driver, using a hardcoded URL. As part of its initialization, the DriverManager class attempts to load the driver classes referenced in the jdbc.drivers system property. This allows you to customize the JDBC Drivers used by your applications.
DataSource: This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object's properties are set so that it represents a particular data source.
Establishing a connection involves two steps: Loading the driver, and making the connection. Loading the Driver
Loading the driver you want to use is very simple. It involves just one line of code in your program. To use the Java DB driver, add the following line of code:
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 176
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Your driver documentation provides the class name to use. In the example above, EmbeddedDriver is one of the drivers for Java DB.
Calling the Class.forName automatically creates an instance of a driver and registers it with the DriverManager, so you don't need to create an instance of the class. If you were to create your own instance, you would be creating an unnecessary duplicate, but it would do no harm.
After you have loaded a driver, it can make a connection with a DBMS. Making the Connection
The second step in establishing a connection is to have the appropriate driver connect to the DBMS. Using the DriverManager Class
The DriverManager class works with the Driver interface to manage the set of drivers available to a JDBC client. When the client requests a connection and provides a URL, the DriverManager is responsible for finding a driver that recognizes the URL and for using it to connect to the corresponding data source. Connection URLs have the following form:
jdbc:derby:[propertyList]
The dbName portion of the URL identifies a specific database. A database can be in one of many locations: in the current working directory, on the classpath, in a JAR file, in a specific Java DB database home directory, or in an absolute location on your file system.
If you are using a vendor-specific driver, such as Oracle, the documentation will tell you what subprotocol to use, that is, what to put after jdbc: in the JDBC URL. For example, if the driver developer has registered the name OracleDriver as the subprotocol, the first and second parts of the JDBC URL will be jdbc.driver.OracleDriver. The driver documentation will also give you
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 177
guidelines for the rest of the JDBC URL. This last part of the JDBC URL supplies information for identifying the data source.
The getConnection method establishes a connection:
Connection conn = DriverManager.getConnection("jdbc:derby:COFFEES");
In place of " myLogin " you insert the name you use to log in to the DBMS; in place of " myPassword " you insert your password for the DBMS. So, if you log in to your DBMS with a login name of " Fernanda " and a password of " J8, " just these two lines of code will establish a connection:
String url = "jdbc:derby:Fred";
Connection con = DriverManager.getConnection(url, "Fernanda", "J8");
If one of the drivers you loaded recognizes the JDBC URL supplied to the method DriverManager.getConnection, that driver establishes a connection to the DBMS specified in the JDBC URL. The DriverManager class, true to its name, manages all of the details of establishing the connection for you behind the scenes. Unless you are writing a driver, you probably won't use any of the methods in the interface Driver, and the only DriverManager method you really need to know is DriverManager.getConnection
The connection returned by the method DriverManager.getConnection is an open connection you can use to create JDBC statements that pass your SQL statements to the DBMS. In the previous example, con is an open connection, and you use it in the examples that follow. Using a DataSource Object for a connection
Using a DataSource object increases application portability by making it possible for an application to use a logical name for a data source instead of having to supply information specific to a particular driver. The following example shows how to use a DataSource to establish a connection:
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 178
You can configure a DataSource using a tool or manually. For example, Here is an example of a DataSource lookup:
InitialContext ic = new InitialContext()
DataSource ds = ic.lookup("java:comp/env/jdbc/myDB");
Connection con = ds.getConnection();
DataSource ds = (DataSource) org.apache.derby.jdbc.ClientDataSource()
ds.setPort(1527);
ds.setHost("localhost");
ds.setUser("APP")
ds.setPassword("APP");
Connection con = ds.getConnection();
DataSource implementations must provide getter and setter methods for each property they support. These properties typically are initialized when the DataSource object is deployed.
VendorDataSource vds = new VendorDataSource();
vds.setServerName("my_database_server");
String name = vds.getServerName(); JDBC-ODBC Bridge Driver
For normal use, you should obtain a commercial JDBC driver from a vendor such as your database vendor or your database middleware vendor. The JDBC-ODBC Bridge driver provided with JDBC is recommended only for development and testing, or when no other alternative is available.
Self Assessment Questions
5. What is the purpose of JDBC-ODBC bridge driver ?
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 179
7.6 Retrieving Values from Result Sets
In the previous lesson, ResultSet was briefly mentioned. Now, you will learn the details of the ResultSet interface. The ResultSet interface provides methods for retrieving and manipulating the results of executed queries, and ResultSet objects can have different functionality and characteristics. These characteristics are result set type, result set concurrency, and cursor holdability. A table of data representing a database result set is usually generated by executing a statement that queries the database.
The type of a ResultSet object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the ResultSet object.
The sensitivity of the ResultSet object is determined by one of three different ResultSet types:
TYPE_FORWARD_ONLY – The result set is not scrollable; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database materializes the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.
TYPE_SCROLL_INSENSITIVE – The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
TYPE_SCROLL_SENSITIVE – The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 180
Now, you'll see how to send the above SELECT statements from a program written in the Java™ programming language and how you get the results we showed.
JDBC™ returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. In addition, the Statement methods executeQuery and getResultSet both return a ResultSet object, as do various DatabaseMetaData methods. The following code demonstrates declaring the ResultSet object rs and assigning the results of our earlier query to it by using the executeQuery method.
Before you can take advantage of these features, however, you need to create a scrollable ResultSet object. The following line of code illustrates one way to create a scrollable ResultSet object:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
This code is similar to what you have used earlier, except that it adds two arguments to the createStatement method. The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_ INSENSITIVE, and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE . The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 181
first, and because both parameters are of type int, the compiler will not complain if you switch the order.
Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.
Using the ResultSet Methods
The variable srs, which is an instance of ResultSet, contains the rows of coffees and prices shown in the result set example above. In order to access the names and prices. A ResultSet object maintains a cursor, which points to its current row of data.
When a ResultSet object is first created, the cursor is positioned before the first row. To move the cursor, you can use the following methods:
next() – moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.
previous() – moves the cursor backwards one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.
first() – moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSet object does not contain any rows.
last() – moves the cursor to the last row in the ResultSet object. Returns true if the cursor is now positioned on the last row and false if the ResultSet object does not contain any rows.
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 182
beforeFirst() – positions the cursor at the start of the ResultSet object, before the first row. If the ResultSet object does not contain any rows, this method has no effect.
afterLast() – positions the cursor at the end of the ResultSet object, after the last row. If the ResultSet object does not contain any rows, this method has no effect.
relative(int rows) – moves the cursor relative to its current position.
absolute(int row) – positions the cursor on the row-th row of the ResultSet object.
Once you have a scrollable ResultSet object, srs in the previous example, you can use it to move the cursor around in the result set. Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to the method next moves the cursor to the first row and makes it the current row. Successive invocations of the method next move the cursor down one row at a time from top to bottom.
Using the getXXX Methods
The ResultSet interface declares getter methods (getBoolean, getLong, and so on) for retrieving column values from the current row. Your application can retrieve values using either the index number of the column or the name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.
Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 183
in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.
The getXXX method of the appropriate type retrieves the value in each column. For example, the first column in each row of srs is COF_NAME, which stores a value of SQL type VARCHAR. The method for retrieving a value of SQL type VARCHAR is getString. The second column in each row stores a value of SQL type FLOAT, and the method for retrieving values of that type is getFloat. The following code accesses the values stored in the current row of srs and prints a line with the name followed by three spaces and the price. Each time the method next is invoked, the next row becomes the current row, and the loop continues until there are no more rows in rs.
The method getString is invoked on the ResultSet object srs, so getString retrieves (gets) the value stored in the column COF_NAME in the current row of srs . The value that getString retrieves has been converted from an SQL VARCHAR to a String in the Java programming language, and it is assigned to the String object s.
Note that although the method getString is recommended for retrieving the SQL types CHAR and VARCHAR, it is possible to retrieve any of the basic SQL types with it. (You cannot, however, retrieve the new SQL3 datatypes with it. We will discuss SQL3 types later in this tutorial.) Getting all values with getString can be very useful, but it also has its limitations. For instance, if it is used to retrieve a numeric type, getString converts the numeric value to a Java String object, and the value has to be converted back to a numeric type before it can be operated on as a number. In cases where the value is treated as a string anyway, there is no drawback. Further, if you want an
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 184
application to retrieve values of any standard SQL type other than SQL3 types, use the getString method.
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(
"SELECT COF_NAME, PRICE FROM COFFEES");
while (srs.next()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}
The output will look something like this:
Colombian 7.99
French_Roast 8.99
Espresso 9.99
Colombian_Decaf 8.99
French_Roast_Decaf 9.99
You can process all of the rows is srs going backward, but to do this, the cursor must start out located after the last row. You can move the cursor explicitly to the position after the last row with the method afterLast. Then the method previous() moves the cursor from the position after the last row to the last row, and then to the previous row with each iteration through the
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 185
while loop. The loop ends when the cursor reaches the position before the first row, where the method previous() returns false .
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
srs.afterLast();
while (srs.previous()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}
The printout will look similar to this:
French_Roast_Decaf 9.99
Colombian_Decaf 8.99
Espresso 9.99
French_Roast 8.99
Colombian 7.99
As you can see, the printout for each has the same values, but the rows are in the opposite order.
The situation is similar with the method getFloat except that it retrieves the value stored in the column PRICE, which is an SQL FLOAT, and converts it to a Java float before assigning it to the variable n.
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 186
JDBC offers two ways to identify the column from which a getXXX method gets a value. One way is to give the column name, as was done in the example above. The second way is to give the column index (number of the column), with 1 signifying the first column, 2 , the second, and so on. Using the column number instead of the column name looks like this:
String s = srs.getString(1);
float n = srs.getFloat(2);
The first line of code gets the value in the first column of the current row of rs (column COF_NAME), converts it to a Java String object, and assigns it to s. The second line of code gets the value stored in the second column of the current row of rs , converts it to a Java float, and assigns it to n. Note that the column number refers to the column number in the result set, not in the original table.
You can move the cursor to a particular row in a ResultSet object. The methods first, last, beforeFirst, and afterLast move the cursor to the row indicated in their names. The method absolute will move the cursor to the row number indicated in the argument passed to it. If the number is positive, the cursor moves the given number from the beginning, so calling absolute(1) puts the cursor on the first row. If the number is negative, the cursor moves the given number from the end, so calling absolute(-1) puts the cursor on the last row. The following line of code moves the cursor to the fourth row of srs:
srs.absolute(4);
If srs has 500 rows, the following line of code moves the cursor to row 497:
srs.absolute(-4);
Three methods move the cursor to a position relative to its current position. As you have seen, the method next moves the cursor forward one row, and
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 187
the method previous moves the cursor backward one row. With the method relative, you can specify how many rows to move from the current row and also the direction in which to move. A positive number moves the cursor forward the given number of rows; a negative number moves the cursor backward the given number of rows. For example, in the following code fragment, the cursor moves to the fourth row, then to the first row, and finally to the third row:
srs.absolute(4); // cursor is on the fourth row
. . .
srs.relative(-3); // cursor is on the first row
. . .
srs.relative(2); // cursor is on the third row
The method getRow lets you check the number of the row where the cursor is positioned. For example, you can use getRow to verify the current position of the cursor in the previous example as follows:
srs.absolute(4);
int rowNum = srs.getRow(); // rowNum should be 4
srs.relative(-3);
int rowNum = srs.getRow(); // rowNum should be 1
srs.relative(2);
int rowNum = srs.getRow(); // rowNum should be 3
Four additional methods let you verify whether the cursor is at a particular position. The position is stated in their names: isFirst, isLast, isBeforeFirst, isAfterLast. These methods all return a boolean and can therefore be used in a conditional statement. For example, the following code fragment tests to see whether the cursor is after the last row before invoking the method previous in a while loop. If the method isAfterLast returns false, the cursor is
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 188
not after the last row, so the method afterLast is invoked. This guarantees that the cursor will be after the last row and that using the method previous in the while loop will cover every row in srs.
if (srs.isAfterLast() == false) {
srs.afterLast();
}
while (srs.previous()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}
In summary, JDBC allows you to use either the column name or the column number as the argument to a getXXX method. Using the column number is slightly more efficient, and there are some cases where the column number is required. In general, though, supplying the column name is essentially equivalent to supplying the column number.
JDBC allows a lot of latitude as far as which getXXX methods you can use to retrieve the different SQL types. For example, the method getInt can be used to retrieve any of the numeric or character types. The data it retrieves will be converted to an int; that is, if the SQL type is VARCHAR , JDBC will attempt to parse an integer out of the VARCHAR. The method getInt is recommended for retrieving only SQL INTEGER types, however, and it cannot be used for the SQL types BINARY, VARBINARY, LONGVARBINARY, DATE , TIME, or TIMESTAMP.
Self Assessment Questions
6. What is the purpose of resultset?
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 189
7.7 Updating Tables
Next, you learn to update rows in a result set, using methods in the Java programming language rather than having to send an SQL command.
Updating a row in a ResultSet object is a two-phase process. First, the new value for each column being updated is set, and then the change is applied to the row. The row in the underlying data source is not updated until the second phase is completed.
The ResultSet interface contains two update methods for each JDBC™ type, one specifying the column to be updated as an index and one specifying the column name as it appears in the select list. Column names supplied to updater methods are case insensitive. If a select list contains the same column more than once, the first instance of the column will be updated.
First, you need to create a ResultSet object that is updatable. To do this, supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method, as you have seen in previous examples. The Statement object it creates produces an updatable ResultSet object each time it executes a query. The following code fragment illustrates creating the updatable ResultSet object uprs. Note that the code also makes uprs scrollable. An updatable ResultSet object does not necessarily have to be scrollable, but when you are making changes to a result set, you generally want to be able to move around in it. With a scrollable result set, you can move to rows you want to change, and if the type is TYPE_SCROLL_SENSITIVE, you can get the new value in a row after you have changed it.
Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 190
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
The ResultSet object uprs looks something like this:
COF_NAME PRICE
Colombian 7.99
French_Roast 8.99
Espresso 9.99
Colombian_Decaf 8.99
French_Roast_Decaf 9.99
The method updateRow applies all column changes to the current row. The changes are not made to the row until updateRow has been called. You can use the cancelUpdates method to back out changes made to the row before the updateRow method is called.
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
ResultSet srs = stmt.executeQuery("select COF_Name from COFFEES " +
"where price = 7.99");
srs.next();
srs.updateString("COF_NAME", "Foldgers");
srs.updateRow();
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 191
An update is the modification of a column value in the current row. Let's suppose that you want to raise the price of French Roast Decaf coffee to 10.99:
uprs.last();
uprs.updateFloat("PRICE", 10.99);
Update operations affect column values in the row where the cursor is positioned, so in the first line the ResultSet uprs calls the method last to move its cursor to the last row (the row where the column COF_NAME has the value FRENCH_ROAST_DECAF). Once the cursor is on the last row, all of the update methods you call will operate on that row until you move the cursor to another row. The second line changes the value in the PRICE column to 10.99 by calling the method updateFloat. This method is used because the column value we want to update is a float in the Java programming language.
The ResultSet. updateXXX methods take two parameters: the column to update and the new value to put in that column. As with the ResultSet.getXXX methods, the parameter designating the column may be either the column name or the column number. There is a different updateXXX method for updating each datatype ( updateString, updateBigDecimal, updateInt, and so on) just as there are different getXXX methods for retrieving different datatypes.
At this point, the price in uprs for French Roast Decaf will be 10.99, but the price in the table COFFEES in the database will still be 9.99. To make the update take effect in the database and not just the result set, we must call the ResultSet method updateRow. Here is what the code should look like to update both uprs and COFFEES :
uprs.last();
uprs.updateFloat("PRICE", 10.99f);
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 192
uprs.updateRow();
If you had moved the cursor to a different row before calling the method updateRow, the update would have been lost. If, on the other hand, you realized that the price should really have been 10.79 instead of 10.99, you could have cancelled the update to 10.99 by calling the method cancelRowUpdates. You have to invoke cancelRowUpdates before invoking the method updateRow; once updateRow is called, calling the method cancelRowUpdates does nothing. Note that cancelRowUpdates cancels all of the updates in a row, so if there are many invocations of the updateXXX methods on the same row, you cannot cancel just one of them. The following code fragment first cancels updating the price to 10.99 and then updates it to 10.79:
uprs.last();
uprs.updateFloat("PRICE", 10.99);
uprs.cancelRowUpdates();
uprs.updateFloat("PRICE", 10.79);
uprs.updateRow();
In this example, only one column value was updated, but you can call an appropriate updateXXX method for any or all of the column values in a single row. The concept to remember is that updates and related operations apply to the row where the cursor is positioned. Even if there are many calls to updateXXX methods, it takes only one call to the method updateRow to update the database with all of the changes made in the current row.
If you want to update the price for COLOMBIAN_DECAF as well, you have to move the cursor to the row containing that coffee. Because the row for COLOMBIAN_DECAF immediately precedes the row for FRENCH_ROAST_DECAF, you can call the method previous to position the cursor on the row for COLOMBIAN_DECAF. The following code fragment
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 193
changes the price in that row to 9.79 in both the result set and the underlying table in the database:
uprs.previous();
uprs.updateFloat("PRICE", 9.79);
uprs.updateRow();
All cursor movements refer to rows in a ResultSet object, not rows in the underlying database. If a query selects five rows from a database table, there will be five rows in the result set, with the first row being row 1, the second row being row 2, and so on. Row 1 can also be identified as the first, and, in a result set with five rows, row 5 is the last.
The ordering of the rows in the result set has nothing at all to do with the order of the rows in the base table. In fact, the order of the rows in a database table is indeterminate. The DBMS keeps track of which rows were selected, and it makes updates to the proper rows, but they may be located anywhere in the table. When a row is inserted, for example, there is no way to know where in the table it has been inserted.
7.8 Creating Complete JDBC Applications
Up to this point, you have seen only code fragments. Now you will see some samples.The first sample code creates the table COFFEES; the second one inserts values into the table and prints the results of a query. The third application creates the table SUPPLIERS, and the fourth populates it with values. After you have run this code, you can try a query that is a join between the tables COFFEES and SUPPLIERS, as in the fifth code example. The sixth code sample is an application that demonstrates a transaction and also shows how to set placeholder parameters in a PreparedStatement object using a for loop.
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 194
Because they are complete applications, they include some elements of the Java programming language we have not shown before in the code fragments. We will explain these elements briefly here.
Putting Code in a Class Definition
In the Java™ programming language, any code you want to execute must be inside a class definition. You type the class definition in a file and give the file the name of the class with .java appended to it. So if you have a class named MySQLStatement, its definition should be in a file named MySQLStatement.java.
Importing Classes to Make Them Visible
The first thing to do is to import the packages or classes you will be using in the new class. The classes in our examples all use the java.sql package (the JDBC™ API), which becomes available when the following line of code precedes the class definition:
import java.sql.*;
The star ( * ) indicates that all of the classes in the package java.sql are to be imported. Importing a class makes it visible and means that you do not have to write out the fully qualified name when you use a method or field from that class. If you do not include " import java.sql.*; " in your code, you will have to write " java.sql. " plus the class name in front of all the JDBC fields or methods you use every time you use them. Note that you can import individual classes selectively rather than a whole package. Java does not require that you import classes or packages, but doing so makes writing code a lot more convenient.
Any lines importing classes appear at the top of all the code samples, as they must if they are going to make the imported classes visible to the class
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 195
being defined. The actual class definition follows any lines that import classes.
Using the main Method
If a class is to be executed, it must contain a static public main method. This method comes right after the line declaring the class and invokes the other methods in the class. The keyword static indicates that this method operates on a class level rather than on individual instances of a class. The keyword public means that members of any class can access this method. Since we are not just defining classes to be used by other classes but instead want to run them, the example applications in this chapter all include a main method.
Using try and catch Blocks
Something else all the sample applications include is try and catch blocks. These are the Java programming language's mechanism for handling exceptions. Java requires that when a method throws an exception, there be some mechanism to handle it. Generally a catch block will catch the exception and specify what happens (which you may choose to be nothing). In the sample code, we use two try blocks and two catch blocks. The first try block contains the method Class.forName, from the java.lang package. This method throws a ClassNotFoundException, so the catch block immediately following it deals with that exception. The second try block contains JDBC methods, which all throw SQLExceptions, so one catch block at the end of the application can handle all of the rest of the exceptions that might be thrown because they will all be SQLException objects.
Retrieving Exceptions
JDBC lets you see the warnings and exceptions generated by your DBMS and by the Java compiler. To see exceptions, you can have a catch block
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 196
print them out. For example, the following two catch blocks from the sample code print out a message explaining the exception:
try {
// Code that could generate an exception goes here.
// If an exception is generated, the catch block below
// will print out information about it.
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
try {
Class.forName("myDriverClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
If you were to run CreateCOFFEES.java twice, you would get an error message similar to this:
SQLException: There is already an object named 'COFFEES'
in the database.
Severity 16, State 1, Line 1
This example illustrates printing out the message component of an SQLException object, which is sufficient for most situations.
There are actually three components, however, and to be complete, you can print them all out. The following code fragment shows a catch block that is
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 197
complete in two ways. First, it prints out all three parts of an SQLException object: the message (a string that describes the error), the SQL state (a string identifying the error according to the X/Open SQLState conventions), and the vendor error code (a number that is the driver vendor's error code number). The SQLException object ex is caught, and its three components are accessed with the methods getMessage , getSQLState , and getErrorCode .
The second way the following catch block is complete is that it gets all of the exceptions that might have been thrown. If there is a second exception, it will be chained to ex, so ex.getNextException is called to see if there is another exception. If there is, the while loop continues and prints out the next exception's message, SQLState, and vendor error code. This continues until there are no more exceptions.
try {
// Code that could generate an exception goes here.
// If an exception is generated, the catch block below
// will print out information about it.
} catch(SQLException ex) {
System.out.println("\n--- SQLException caught ---\n");
while (ex != null) {
System.out.println("Message: "
+ ex.getMessage ());
System.out.println("SQLState: "
+ ex.getSQLState ());
System.out.println("ErrorCode: "
+ ex.getErrorCode ());
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 198
ex = ex.getNextException();
System.out.println("");
}
}
If you were to substitute the catch block above into the code and run it after the table COFFEES had already been created, you would get the following printout:
– SQLException caught –
Message: There is already an object named 'COFFEES' in the database.
Severity 16, State 1, Line 1
SQLState: 42501
ErrorCode: 2714
The vendor error code is specific to each driver, so you need to check your driver documentation for a list of error codes and what they mean.
Retrieving Warnings
SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. For example, a warning might let you know that a privilege you attempted to revoke was not revoked. Or a warning might tell you that an error occurred during a requested disconnection.
A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object. If getWarnings returns a warning, you can call the SQLWarning
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 199
method getNextWarning on it to get any additional warnings. Executing a statement automatically clears the warnings from a previous statement, so they do not build up. This means, however, that if you want to retrieve warnings reported on a statement, you must do so before you execute another statement.
The following code fragment illustrates how to get complete information about any warnings reported on the Statement object stmt and also on the ResultSet object rs :
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select COF_NAME from COFFEES");
while (rs.next()) {
String coffeeName = rs.getString("COF_NAME");
System.out.println("Coffees available at the Coffee Break: ");
System.out.println(" " + coffeeName);
SQLWarning warning = stmt.getWarnings();
if (warning != null) {
System.out.println("\n---Warning---\n");
while (warning != null) {
System.out.println("Message: "
+ warning.getMessage());
System.out.println("SQLState: "
+ warning.getSQLState());
System.out.print("Vendor error code: ");
System.out.println(warning.getErrorCode());
System.out.println("");
warning = warning.getNextWarning();
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 200
}
}
SQLWarning warn = rs.getWarnings();
if (warn != null) {
System.out.println("\n---Warning---\n");
while (warn != null) {
System.out.println("Message: "
+ warn.getMessage());
System.out.println("SQLState: "
+ warn.getSQLState());
System.out.print("Vendor error code: ");
System.out.println(warn.getErrorCode());
System.out.println("");
warn = warn.getNextWarning();
}
}
}
Warnings are actually rather uncommon. Of those that are reported, by far the most common warning is a DataTruncation warning, a subclass of SQLWarning. All DataTruncation objects have an SQLState of 01004, indicating that there was a problem with reading or writing data. DataTruncation methods let you find out in which column or parameter data was truncated, whether the truncation was on a read or write operation, how many bytes should have been transferred, and how many bytes were actually transferred.
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 201
7.9 Running the Sample Applications
You are now ready to actually try out some sample code. The directory book.html contains complete, runnable applications that illustrate concepts presented in this chapter and the next. You can download this sample code from the JDBC web site located at:
Before you can run one of these applications, you will need to edit the file by substituting the appropriate information for the following variables:
url
the JDBC URL; parts one and two are supplied by your driver, and the third part specifies your data source
myLogin
your login name or user name
myPassword
your password for the DBMS
myDriver.ClassName
the class name supplied with your driver
The first example application is the class CreateCoffees , which is in a file named CreateCoffees.java. Below are instructions for running CreateCoffees.java on the three major platforms.
The first line in the instructions below compiles the code in the file CreateCoffees.java . If the compilation is successful, it will produce a file named CreateCoffees.class , which contains the bytecodes translated from the file CreateCoffees.java . These bytecodes will be interpreted by the Java Virtual Machine, which is what makes it possible for Java code to run on any machine with a Java Virtual Machine installed on it.
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 202
The second line of code is what actually makes the code run. Note that you use the name of the class, CreateCoffees , not the name of the file, CreateCoffees.class .
UNIX
javac CreateCoffees.java
java CreateCoffees
Windows 95/NT
javac CreateCoffees.java
java CreateCoffees
7.10 Summary
JDBC
JDBC provides a database-programming interface for Java Programs. A Java Program can send queries to a database using the JDBC driver.
The java.sql Package
The java.sql package contains classes that help in connecting to a database, send SQL statements to the database, and process the query results.
The Connection Object
The Connection object represents a connection with a database. It can be initialized using the getConnection () method of the DriverManager class.
The PreparedStatement Object
The PreparedStatement object allows you to execute parameterized queries. It can be initialized using the preparedStatement () method of the Connection object.
Advanced Java Programming Unit 7
Sikkim Manipal University Page No. 203
The setString () Method
The setString () method sets the query parameters of the PreparedStatement Object.
The executeQuery () Method
The executeQuery () method executes, the query statement present in the PreparedStatement object and returns the number of rows affected by the query.
7.11 Terminal Questions
1. All shoppers who register with the Toy Universe Online Shopping Mall need to be allocated a password. Create a JDBC application that generates a ShopperId and accepts personal details along with a password from a first-time shopper, and stores these details in the Toy Universe database.
You need prepare a test report. You also need to get a review done from your tem leader.
2. Draw and explain the JDBC Application Architecture?
3. Explain the procedure for connecting to the database?