Module 5 Notes JDBC

Advanced Java Programming Module 5
Module 5 : JDBC:
The concept of JDBC; JDBC Driver Types; JDBC packages; A brief overview of the JDBC Process;
Database Connection; Associating the JDBC/ODBC Bridge with the Database; Statement Objects;
ResultSet; Transaction Processing; Metadata, Data Types; Exceptions.
Textbook 2: Chapter 6
Module 5 : JDBC
Database Interaction in J2EE:
 J2EE applications commonly interact with databases through web services.
 Database access is achieved using Java data objects within the JDBC API.
Java Data Objects:
 Methods to open a connection to a DBMS.
 Transmit queries to insert, retrieve, modify, or delete data.
 Receive messages from DBMS containing data or query status.
Interaction with DBMS:
 Same connection used for sending and receiving messages.
 Messages from DBMS include rows of data or query status.
The concept of JDBC

DBMS Variety:
 Many industrial-strength DBMSs available (e.g., Oracle, DB2, Sybase).
Challenge for Sun Microsystems:
 Needed to develop a way for Java developers to access various DBMSs with high-level code.
Language Barrier:
 Each DBMS had its own way of interacting with programs.
 Code for one DBMS (e.g., Oracle) might not work for another (e.g., DB2).
Solution in 1996:
 Sun Microsystems created the JDBC driver and JDBC API.
 These allowed Java to be used for industrial-strength programming with DBMSs.
JDBC Driver Specification:
 Described the functionality of a JDBC driver.
 Encouraged DBMS manufacturers to build compatible JDBC drivers.
Translation Function:
 JDBC drivers acted as translators between DBMS messages and JDBC API messages.
Impact for Java Developers:
 Enabled use of high-level Java data objects to interact with DBMSs.
 Java data objects convert between DBMS messages and JDBC API messages.
 Functions of JDBC Drivers:
o Open a connection between DBMS and J2EE component.
o Translate SQL statements from J2EE component into DBMS-compatible messages.
o Return data to J2EE component as per JDBC specification.
o Provide error messages and transaction management routines as per JDBC specification.
o Close the connection between DBMS and J2EE component.
 Database Independence:
o JDBC drivers enable J2EE components to be database independent, aligning with Java's
platform independence philosophy.
o JDBC drivers are available for nearly every commercial DBMS.

 SQL Query Handling:
o SQL queries are passed from JDBC API through the JDBC driver to DBMS without validation.
o DBMS is responsible for implementing SQL statements contained in the query.
In summary, JDBC drivers facilitate communication between J2EE components and DBMS, ensuring platform
and database independence, and handling various functions from connection management to query translation
and error reporting.
JDBC Driver Types

Classified into four groups for different DBMS communication needs.
Type 1 JDBC-to-ODBC Driver:
 Created by Microsoft with the Open Database Connection (ODBC) program.
 Basis for Sun Microsystems' JDBC driver specifications and API.
 Also called the JDBC/ODBC Bridge.
 Translates DBMS calls between JDBC and ODBC specifications.
 Receives messages from J2EE components, translates them to ODBC format, then to DBMS
format.
 Not recommended for mission-critical applications due to potential performance issues from extra
translation steps.
Type 2 Java/Native Code Driver:
o Uses Java classes to generate platform-specific code for a specific DBMS.
o Provided by the DBMS manufacturer along with API classes.
o Disadvantage: Loss of code portability as API classes may not work with other DBMSs.
Type 3 JDBC Driver:
o Also known as the Java Protocol.
o Converts SQL queries into JDBC-formatted statements.
o JDBC-formatted statements are then translated into the DBMS-required format.
o Most commonly used JDBC driver.
Type 4 JDBC Driver:
o Also known as the Type 4 database protocol.
o Directly translates SQL queries into the format required by the DBMS.
o No need to convert SQL queries into JDBC-formatted statements.
o Fastest method for communicating SQL queries to the DBMS.
In summary, these driver types offer varying levels of compatibility and performance, with Type 4 being
the fastest and Type 3 the most commonly used, while Type 2 offers less portability and Type 1 involves
additional translation steps.
JDBC packages
1. JDBC API Packages:
- The JDBC API is contained in two packages: `java.sql` and `javax.sql`.
2. java.sql Package:
- Contains core Java data objects of the JDBC API.
- Provides basics for connecting to the DBMS and interacting with stored data.
- Part of the J2SE (Java 2 Standard Edition).
3. javax.sql Package:
- Extends `java.sql` and is part of J2EE (Java 2 Enterprise Edition).
- Includes Java data objects for advanced features.
- Interacts with Java Naming and Directory Interface (JNDI).
- Manages connection pooling and other advanced JDBC features.
In summary, `java.sql` provides fundamental JDBC functionalities within J2SE, while `javax.sql` extends
these functionalities with advanced features for J2EE.
A brief overview of the JDBC Process

1. Loading the JDBC Driver:
- Use `Class.forName()` to load the JDBC driver.
- Example for loading JDBC/ODBC Bridge driver:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
2. Connecting to the DBMS:

- Use `DriverManager.getConnection()` to connect to the DBMS.
- Pass the URL of the database, user ID, and password.
- Returns a `Connection` interface for database communication.
- Example for connecting to the CustomerInformation database:
String url = "jdbc:odbc:CustomerInformation";
String userID = "jim";
String password = "keogh";
Connection Db;
try {
Db = DriverManager.getConnection(url, userID, password);
} catch (Exception e) {
e.printStackTrace();
}
3. Create and Execute a SQL Statement

1. Create a Statement Object:
- Use `Connection.createStatement()`.
- Example: `DataRequest = Db.createStatement();`
2. Execute the SQL Query:
- Assign the SQL query to a `String` object.
- Use `Statement.executeQuery()`.
- Example:
String query = "SELECT * FROM Customers";
Results = DataRequest.executeQuery(query);
3. Process the ResultSet:
- `ResultSet` contains the response from the DBMS.
- Example: `Results = DataRequest.executeQuery(query);`
4. Close the Statement:
- Call `close()` on the `Statement` object.
- Example: `DataRequest.close();`
Connection Db;
Statement DataRequest;
ResultSet Results;
try {
DataRequest = Db.createStatement();
DataRequest.close();
} catch (Exception e) {
}
In summary, the process involves creating a `Statement` object, executing a SQL query, processing the
`ResultSet`, and closing the `Statement`.
4. Process Data Returned by the DBMS:

1. Assign ResultSet Object:
- The `java.sql.ResultSet` object holds the results from the DBMS.
- Example: `ResultSet Results;`
2. Use next() Method:
- Moves the pointer to the first row and checks if rows are present.
- Returns `false` if no rows, `true` if there are rows.
- Example:
boolean Records = Results.next();
if (!Records) {
System.out.println("No data returned");
return;
}
3. Extract Data Using getString():
- `getString()` copies the value of a specified column in the current row.
- Pass either column name or column number to `getString()`.
- Example:
String FirstName = Results.getString("FirstName");
String LastName = Results.getString("LastName");
4. Concatenate and Display Data:
- Combine first name and last name, and print the result.
- Example:
String printrow = FirstName + " " + LastName;
System.out.println(printrow);
5. Loop Through ResultSet:
- Use `do ... while` loop to process all rows.
- Example:
do {
FirstName = Results.getString("FirstName");
LastName = Results.getString("LastName");
printrow = FirstName + " " + LastName;
} while (Results.next());
### Complete Example Code:

ResultSet Results;
String FirstName;
String LastName;
String printrow;
if (!Records) {
return;
} else {
do {
FirstName = Results.getString("FirstName");
LastName = Results.getString("LastName");
}
This process allows a J2EE component to interact with the `ResultSet` object, extracting and displaying
data row by row.
5. Terminate the Connection to the DBMS

1. Use the `close()` method of the `Connection` object to end the connection to the database.
2. This method can throw an exception if there's a problem disconnecting from the database.
3. It's better to explicitly close any `ResultSet` before closing the `Connection`.
4. Example: `Db.close();`
Database Connection
1. J2EE and DBMS Connection: J2EE components connect to a DBMS through a JDBC driver, not directly
to the DBMS itself.
2. Loading the JDBC Driver: Before connecting, the JDBC driver needs to be loaded and registered with
the DriverManager in Java.
3. Purpose of Loading: Loading and registering the JDBC driver brings it into the Java Virtual Machine
(JVM) environment, making it available for use by J2EE components.
4. Class.forName() Method: This method is used to load the JDBC driver. For example,
"sun.jdbc.odbc.JdbcOdbcDriver" loads the JDBC/ODBC Bridge. You should replace this with the
appropriate JDBC driver for your DBMS.
5. Exception Handling: The `Class.forName()` method can throw a `ClassNotFoundException` if the JDBC
driver cannot be found or loaded. This error is caught and handled using a `catch` block.
Here's a simplified representation of the example code you provided:
try {
} catch (ClassNotFoundException error) {
System.err.println("Unable to load the JDBC/ODBC bridge: " +
error.getMessage());
System.exit(1);
}
This code attempts to load the JDBC/ODBC Bridge driver, handling any errors that occur during the
process.
The connection:
1. Connecting to Database:
- After loading and registering the JDBC driver, a J2EE component connects to the database.
- Database association with the JDBC driver is typically handled by administrators.

- Students often use Microsoft Access for learning as it's commonly available locally.
2. URL Format:
- JDBC URL format consists of:
- `jdbc`: Indicates the JDBC protocol.
- `<subprotocol>`: JDBC driver name.
- `<subname>`: Database name.
3. Establishing Connection:
- `DriverManager.getConnection()` methods request database access. They return a `Connection` object if
access is granted or throw a `SQLException` if not.
4. Two getConnection() Methods:
- Use `getConnection(String url)` if the DBMS grants universal access.
- Use `getConnection(String url, String user, String password)` for databases requiring user
authentication.
5. Example Code Simplified:
Connection db;
try {
db = DriverManager.getConnection(url, userID, password);
} catch (ClassNotFoundException | SQLException e) {
System.err.println("Error: " + e.getMessage());
System.exit(1);
}
6. Properties for Connection:
- Some DBMSs require additional properties besides user ID and password, referred to as properties.
- Properties are loaded from a text file into a `Properties` object and passed to `getConnection()` along
with the URL.
7. Example Code for Properties:

Properties props = new Properties();
try {
FileInputStream propFileStream = new
FileInputStream("DBProps.txt");
props.load(propFileStream);
db = DriverManager.getConnection(url, props);
} catch (IOException | ClassNotFoundException | SQLException e) {
System.exit(1);
}
This code demonstrates how to connect to a database using JDBC, handling different scenarios such as
simple authentication and additional properties required by the DBMS.
In this case, the J2EE component uses the getConnection(String url, String user, String password) method
as illustrated below
1. Loading Properties from File:
- A `Properties` object `props` is created to store database connection properties.

- The properties are loaded from a file named "DBProps.txt" using a `FileInputStream`.
2. Exception Handling:
- If an ÌOException` occurs while loading the properties file, an error message is printed, and the
program exits with status `1`.
3. Connecting Using Properties:
- The JDBC/ODBC Bridge driver is loaded.
- `DriverManager.getConnection(url, props)` is used to establish a connection to the database using the
properties loaded from the file.
4. Error Handling:
- If `ClassNotFoundException` occurs while loading the JDBC driver, an error message is printed, and
the program exits with status `2`.
- If `SQLException` occurs while connecting to the database, an error message is printed, and the
program exits with status `3`.
Here's a simplified version of the code you provided:

Connection db;
Properties props = new Properties();
try {
FileInputStream propFileStream = new
FileInputStream("DBProps.txt");
props.load(propFileStream);
} catch (IOException e) {
System.err.println("Error loading properties file: " +
e.getMessage());
System.exit(1);
}
try {
db = DriverManager.getConnection(url, props);
} catch (ClassNotFoundException e) {
System.err.println("Unable to load the JDBC/ODBC bridge: " +
e.getMessage());
System.exit(2);
} catch (SQLException e) {
System.err.println("Cannot connect to the database: " +
e.getMessage());
System.exit(3);
}
This code snippet now focuses on loading database connection properties from a file and using them to
establish a connection, with appropriate error handling for potential exceptions.
Timeout
1. Timeout in J2EE Environment:
- In J2EE applications, multiple components may compete for database access, potentially causing
performance issues.
- When a J2EE component attempts to connect to the database, the DBMS might not respond promptly
due to various reasons, like insufficient available connections.
2. Setting Timeout:
- To avoid waiting indefinitely for a response from the DBMS, the J2EE component can set a timeout
period using `DriverManager.setLoginTimeout(int seconds)`.
- This method specifies the maximum time (in seconds) that DriverManager will wait before timing out.
3. Retrieving Timeout Value:
- The current timeout value set in DriverManager can be retrieved using
`DriverManager.getLoginTimeout()`.
- It returns an ìnt` representing the timeout duration in seconds.
These points outline how timeouts can be managed in a J2EE application to prevent delays in database
access and potential performance degradation.
Associating the JDBC/ODBC Bridge with the Database

1. Open ODBC Data Source Administrator:
- Select Start | Settings | Control Panel.
- Select ODBC 32 to display the ODBC Data Source Administrator.
2. Add New User:
- Click the Add button to add a new user.
3. Select Driver:
- Choose the appropriate driver and click Finish.
- For Microsoft Access, select the Microsoft Access Driver.
4. Enter Data Source Name:
- Provide the name of the database as the Data Source Name.
5. Enter Description (Optional):
- Add a description for the data source if needed.
6. Select Database:
- Click the Select button to browse and select the database file.
- Click OK once the database is located.
7. Login and Password:
- Determine if a login name and password are required for the database.
- If needed, click the Advanced button to enter the login details.
8. Finish Setup:
- Click OK to close the ODBC Microsoft Access Setup dialog box.
- Select OK to close the ODBC Data Source Administrator dialog box.
Connection Pool
1. Per-Client Basis Connection:
- Each client must open its own database connection.
- This can lead to performance issues due to the limited number of available connections.
2. Connection Pooling:
- Introduced by JDBC 2.1 Standard Extension API.
- A connection pool is a collection of database connections that are reused.
- Prevents the need to repeatedly open and close connections.
3. Types of Connections:
- Physical Connection: Managed by the application server, reused by clients.
- Logical Connection: Obtained using `DataSource.getConnection()`, connected to an existing physical
connection.
4. Example Code for Connection Pool:
Context ctext = new InitialContext();
DataSource pool = (DataSource)

ctext.lookup("java:comp/env/jdbc/pool");
Connection db = pool.getConnection();
// Place code to interact with the database here
db.close();
5. Close Method:
- The `close()` method of the `DataSource` object closes the logical connection, not the physical one.
- The physical connection is reused by other J2EE components.
This summary outlines the steps for setting up the JDBC/ODBC bridge and explains the concept and usage
of connection pooling in a J2EE environment.
Statement Objects
The Statement Object
1. Opening Database Connection:
- Create and send an SQL query to access data.
- Use one of three types of Statement objects:
- `Statement`: Executes a query immediately.
- `PreparedStatement`: Executes a precompiled query.
- `CallableStatement`: Executes stored procedures.
2. Statement Object:
- Use when a query needs to be executed immediately without precompilation.
- Methods:
- èxecuteQuery()`: Executes a query and returns a `ResultSet`.
- èxecute()`: Used for multiple results.
- èxecuteUpdate()`: Executes queries like ÙPDATE`, `DELETE`, ÌNSERT`, and DDL statements,
returns the number of affected rows.
3. Example Program for èxecuteQuery()`:
- Open a database connection.
- Create a query to retrieve all rows from the `Customer` table.
- Execute the query and process the `ResultSet`.
- Close the `Statement` to free resources.

ResultSet Results;
Connection Db;
try {
System.err.println("Unable to load the JDBC/ODBC bridge." + error);
System.exit(1);
} catch (SQLException error) {
System.err.println("Cannot connect to the database." + error);
System.exit(2);
}
try {
// Place code here to interact with the ResultSet
System.err.println("SQL error." + error);
System.exit(3);
}
4. Example Program for èxecuteUpdate()`:

- Update the `PAID` column of the `Customers` table to 'Y' if `BALANCE` is zero.
- Use èxecuteUpdate()` method to perform the update and get the number of rows updated.

Connection Db;
int rowsUpdated;
try {
System.exit(1);
System.exit(2);
}
try {
String query = "UPDATE Customers SET PAID='Y' WHERE BALANCE=0";
rowsUpdated = DataRequest.executeUpdate(query);
System.exit(3);
}
5. Key Points:
- èxecuteQuery()` returns a `ResultSet` object.
- èxecuteUpdate()` returns an int indicating the number of rows affected.
- Always close `Statement` objects to free resources.
PreparedStatement Object
1. Purpose:
- Compiles SQL queries to reduce overhead when the same query is executed multiple times.
- Uses placeholders (`?`) for values that change with each execution.
2. Benefits:
- Precompiled once, saving time on repeated executions.
- Allows dynamic insertion of values into the query.
3. Using PreparedStatement:
- Similar to `Statement`, but with placeholders for dynamic values.
- Use `setXXX()` methods to replace placeholders with actual values.
4. Example Program:
- Connect to the database.
- Create a precompiled query with a placeholder.
- Set the value for the placeholder.
- Execute the query and process the `ResultSet`.
- Close the `PreparedStatement`.

ResultSet Results;
Connection Db;
try {
System.exit(1);
System.exit(2);
}
try {
String query = "SELECT * FROM Customers WHERE CustNumber = ?";
PreparedStatement pstatement = Db.prepareStatement(query);
pstatement.setString(1, "123");
Results = pstatement.executeQuery();
// Place code here to interact with the ResultSet
pstatement.close();
System.exit(3);
}
Db.close();
5. Key Points:
- `PreparedStatement` uses `?` as a placeholder for values.
- `setXXX()` methods set the actual values for the placeholders.
- Precompiled query improves performance when executed multiple times.
CallableStatement Object
1. Purpose:
- Used to call stored procedures in a J2EE application.
- Stored procedures are blocks of code identified by unique names, written in various languages
(PL/SQL, Transact-SQL, etc.).
2. Parameters:
- IN: Passes data to the stored procedure, set using `setXXX()` methods.
- OUT: Retrieves data from the stored procedure, registered using `registerOutParameter()` and retrieved
using `getXXX()` methods.
- INOUT: Both passes and retrieves data using a combination of `setXXX()` and `getXXX()` methods.
3. Using CallableStatement:
- Create a query to call the stored procedure.
- Use `prepareCall()` method of the `Connection` object to create a `CallableStatement`.
- Register OUT parameters.
- Execute the stored procedure.
- Retrieve the OUT parameters.
4. Example Program:
- Create a query to call the `LastOrderNumber` stored procedure.
- Register the OUT parameter.
- Execute the stored procedure.
- Retrieve the last order number.
- Close the `CallableStatement`.
import java.sql.*;
public class CallableStatementExample {

public static void main(String[] args) {
String lastOrderNumber;
Connection Db;
try {
System.err.println("Unable to load the JDBC/ODBC bridge." +
error);
System.exit(1);
return;
System.err.println("Cannot connect to the database." +
error);
System.exit(2);
return;
}
try {
String query = "CALL LastOrderNumber (?)";
CallableStatement cstatement = Db.prepareCall(query);
cstatement.registerOutParameter(1, Types.VARCHAR);
cstatement.execute();
lastOrderNumber = cstatement.getString(1);
cstatement.close();
System.out.println("Last Order Number: " +
lastOrderNumber);
System.exit(3);
}
try {
Db.close();
}
}
}
5. Key Points:
- Use `prepareCall()` to create `CallableStatement`.
- Register OUT parameters with `registerOutParameter()`.
- Execute the stored procedure with èxecute()`.
- Retrieve OUT parameters with `getXXX()` methods.
ResultSet
1. **Purpose**:
- Used to retrieve, update, and delete information stored in a database.
- The èxecuteQuery()` method sends the query to the DBMS and returns a `ResultSet` object containing
the requested data.
2. **Structure**:
- Data in `ResultSet` is organized into a virtual table with rows and columns.
- Contains metadata such as column names, sizes, and data types.
- A virtual cursor points to rows in the virtual table.
3. **Using the Cursor**:
- Initially positioned above the first row.
- Use the `next()` method to move the cursor to the next row.
- `next()` returns `true` if the row contains data, otherwise `false`.
4. **Retrieving Data**:
- Use `getXXX()` methods (e.g., `getString()`) to copy data from the current row into a Java collection or
variable.
- `getXXX()` methods are data type-specific and require a column number as a parameter.
5. **Example Program**:
- Define a query to retrieve customer first and last names.
- Move the cursor and retrieve data from the `ResultSet`.
- Print the retrieved data.

- Close the `Statement` and `Connection` objects.
import java.sql.*;
public class ResultSetExample {

String printrow;
String FirstName;
String LastName;
ResultSet Results;
Connection Db;
try {
error);
System.exit(1);
return;
error);
System.exit(2);
return;
}
try {
String query = "SELECT FirstName, LastName FROM Customers";
System.exit(3);
return;
}
try {
if (!Records) {
System.exit(4);
}
do {
FirstName = Results.getString(1);
LastName = Results.getString(2);
System.err.println("Data display error." + error);
System.exit(5);
}
try {
Db.close();
}
}
}
6. **Key Points**:
- Use `next()` to move the cursor through rows in the `ResultSet`.
- Use `getXXX()` methods to retrieve data from specific columns.
- Ensure proper exception handling and resource closure to avoid resource leaks.
Scrollabie ResultSet
1. **Advanced Cursor Movement**:
- Cursors can now move backwards or position at a specific row.
- Can specify the number of rows to return from the DBMS.
2. **Cursor Positioning Methods**:
- `first()`: Moves the cursor to the first row.
- `last()`: Moves the cursor to the last row.
- `previous()`: Moves the cursor to the previous row.
- àbsolute(int row)`: Positions the cursor at the specified row number.
- `relative(int rows)`: Moves the cursor relative to its current position. Positive values move forward,
negative values move backward.
- `getRow()`: Returns the current row number.
3. **Setting Up Scrollable `ResultSet`**:
- `Statement` object must handle a scrollable `ResultSet` using constants:
- `TYPE_FORWARD_ONLY`: Cursor moves only downward (default).
- `TYPE_SCROLL_INSENSITIVE`: Cursor moves both directions, insensitive to other component
changes.
- `TYPE_SCROLL_SENSITIVE`: Cursor moves both directions, sensitive to other component changes.
Example Program:
1. **Define Connection and Query**:
2. **Set up Scrollable `ResultSet`**:
3. **Move Cursor and Retrieve Data**:
4. **Print Data**:
5. **Close Resources**:
import java.sql.*;
public class ScrollableResultSetExample {

String printrow;
String FirstName;
String LastName;
ResultSet Results;
Connection Db;
try {
error);
System.exit(1);
return;
error);
System.exit(2);
return;
}
try {
DataRequest =
Db.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
System.exit(3);
return;
}
try {
if (!Records) {
System.exit(4);
}
Results.first();
Results.last();
Results.previous();
Results.absolute(10);
Results.relative(-2);
Results.relative(2);
do {
System.exit(5);
}
try {
Db.close();
}
}
}
Important Notes:
- **Cursor Control**: Ensure the correct setup of `ResultSet` type using `createStatement()`.
- **Error Handling**: Properly handle exceptions for better debugging.
- **Resource Management**: Always close database connections and statements to avoid resource leaks.
Not All JDBC Drivers Are Scrollable

- Use the following code to check if the driver supports scrollable `ResultSet` types.
Example Program to Check Driver Support:
import java.sql.*;
public class CheckScrollableResultSet {

Connection Db = null;
DatabaseMetaData meta = null;
boolean forward, insensitive, sensitive;
try {
Db =
DriverManager.getConnection("jdbc:odbc:CustomerInformation", "jim",
"keogh");
meta = Db.getMetaData();
forward =
meta.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY);
insensitive =
meta.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
sensitive =
meta.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
System.out.println("Forward only: " + forward);

System.out.println("Scroll insensitive: " + insensitive);
System.out.println("Scroll sensitive: " + sensitive);
} finally {
try {
if (Db != null) Db.close();
}
}
}
}
Specifying Number of Rows to Return:

- **Fetch Size**: Determines how many rows are fetched into the driver at one time.
- **Maximum Rows**: Limits the number of rows in the `ResultSet`, not the number fetched from the
DBMS.
Example Program to Set Fetch Size:

1. **Define Connection and Query**:
2. **Set Fetch Size**:
3. **Retrieve and Display Data**:
4. **Close Resources**:
import java.sql.*;
public class FetchSizeExample {

String printrow;
String FirstName;
String LastName;
ResultSet Results;
Connection Db;
try {

error);
System.exit(1);
return;
error);
System.exit(2);
return;
}
try {
DataRequest =
Db.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
DataRequest.setFetchSize(500);
System.exit(3);
return;
}
try {
while (Results.next()) {
}
System.exit(4);
}
try {
Db.close();
}
}
}
```
Important Notes:
- **Driver Compatibility**: Always check if your JDBC driver supports the scrollable `ResultSet` and
fetch size settings.
- **Performance Tuning**: Fetch size tuning is typically handled by a database administrator or network
engineer.
- **Resource Management**: Always close connections and statements properly to avoid resource leaks.
Updatable ResultSet
1. **Setting Up ResultSet**
- Use `CONCUR_UPDATABLE` with `createStatement()` method to make ResultSet updatable.
- `CONCUR_READ_ONLY` makes ResultSet read-only.
2. **Updating Values**
- Use ùpdateXxx()` methods (ùpdateString()`, ùpdateInt()`, etc.) to modify column values in the
current row.
- Example: `Results.updateString("LastName", "Smith");`
- Changes are local to ResultSet until ùpdateRow()` is called.
3. **Deleting a Row**
- Use `deleteRow()` to remove the current row from ResultSet.
- Example: `Results.deleteRow();`
- Also deletes from the underlying database.
4. **Inserting a Row**
- Use ùpdateXxx()` methods to set values for new row.
- Example: `Results.updateString(1, "Tom"); Results.updateString(2, "Smith");`
- Call ìnsertRow()` to add new row to ResultSet and update database.
### Example Program

try {
Connection Db = DriverManager.getConnection(url, userID, password);

Statement DataRequest =
Db.createStatement(ResultSet.CONCUR_UPDATABLE);
ResultSet Results = DataRequest.executeQuery(query);
if (!Results.next()) {
System.exit(4);
}
// Update example
Results.updateString("LastName", "Smith");
Results.updateRow();
// Delete example
Results.deleteRow();
// Insert example
Results.moveToInsertRow();
Results.updateString(1, "Tom");
Results.updateString(2, "Smith");
Results.insertRow();
Db.close();
} catch (ClassNotFoundException | SQLException error) {
System.err.println("Error: " + error.getMessage());
System.exit(5);
}
This example demonstrates updating, deleting, and inserting rows in a ResultSet, reflecting changes both
locally and in the underlying database.
Here's a simplified breakdown of the provided information about transaction processing using JDBC:
Transaction Processing
1. **Transaction Overview**
- A database transaction in JDBC consists of a set of SQL statements.
- All statements must succeed for the transaction to be committed; if one fails, the entire transaction must
be rolled back.
- `Connection.commit()` is used to finalize and commit a transaction.
- `Connection.setAutoCommit(false)` disables auto-commit mode, ensuring transactions are handled
manually.
2. **Handling Transactions**
- Begin by establishing a connection to the database.
- Use `setAutoCommit(false)` to disable auto-commit mode to manage transactions manually.
- Perform SQL statements (ÙPDATE`, ÌNSERT`, etc.) within the transaction.
- Use `commit()` to commit changes to the database.
- Use `rollback()` in case of any failure to revert changes made during the transaction.
3. **Savepoints**
- Savepoints (`setSavepoint()`) can be used to mark intermediate points within a transaction.
- Allows rolling back to a specific point without undoing earlier successful statements.
- Use `releaseSavepoint()` to remove a savepoint once it's no longer needed.
4. **Batch Statements**
- Batch processing allows grouping multiple SQL statements into a single transaction for efficiency.
- Use àddBatch()` to add SQL statements to the batch.
- Execute all statements in the batch with èxecuteBatch()`.
- Handle `BatchUpdateException` to manage errors during batch execution.
- `clearBatch()` clears the batch after execution.
Example Code Summary

Connection Database = null;
Statement DataRequest = null;
try {
// Establish connection
Database = DriverManager.getConnection(url, userID, password);
// Disable auto-commit
Database.setAutoCommit(false);
// Example SQL queries

String query1 = "UPDATE Customers SET Street = '5 Main Street'
WHERE FirstName = 'Bob'";
String query2 = "UPDATE Customers SET Street = '10 Main Street'
WHERE FirstName = 'Tim'";
// Create statements
DataRequest = Database.createStatement();
// Add queries to batch

DataRequest.addBatch(query1);
DataRequest.addBatch(query2);
// Execute batch
int[] updateCounts = DataRequest.executeBatch();
// Commit transaction
Database.commit();
// Close resources
Database.close();
} catch (ClassNotFoundException | SQLException | BatchUpdateException

e) {
// Handle exceptions
try {
if (Database != null) {
Database.rollback(); // Rollback transaction on error
}
} catch (SQLException ex) {
System.err.println("Rollback error: " + ex.getMessage());
}
} finally {
try {
if (DataRequest != null) {
DataRequest.clearBatch(); // Clear batch
DataRequest.close(); // Close statement
}
if (Database != null) {
Database.close(); // Close connection
}
} catch (SQLException ex) {
System.err.println("Error closing resources: " +

ex.getMessage());
}
}
This summary encapsulates the key points and processes involved in handling transactions, savepoints, and
batch statements using JDBC in a straightforward manner.
### ResultSet Holdability
- **Purpose**: Controls what happens to `ResultSet` objects when a transaction is committed.
- **Options**:
- `HOLD_CURSORS_OVER_COMMIT`: Keeps ResultSet objects open after commit.
- `CLOSE_CURSORS_AT_COMMIT`: Closes ResultSet objects when commit is called (default
behavior).
### RowSets
- **Purpose**: Encapsulates a ResultSet for use in disconnected scenarios in Enterprise Java Beans (EJB).
- **Advantages**: Allows EJBs to work with data without needing constant DBMS connection, suitable
for PDA clients.
- **Usage**: Provides a way to manipulate and navigate data rows from a table in a disconnected mode.
### Auto-Generated Keys

- **Purpose**: Many DBMS automatically generate unique keys (e.g., for primary keys) when new rows
are inserted.
- **Method**: Use `Statement.getGeneratedKeys()` to retrieve keys generated by the DBMS after an
insertion.
- **Usage**: Typically used to retrieve automatically generated primary keys for subsequent operations or
display.
These concepts are foundational in JDBC for managing ResultSet behavior, working with disconnected
data using RowSets, and retrieving auto-generated keys from database operations.
Here's a simplified explanation of `Metadata` and `ResultSet Metadata` in JDBC:
Metadata
- **Definition**: Metadata refers to data about data, providing information about the structure and
properties of databases, tables, columns, indexes, etc.
- **Access**: Accessed using the `DatabaseMetaData` interface in JDBC.
- **Retrieval**: Use `Connection.getMetaData()` to obtain a `DatabaseMetaData` object.
- **Common Methods**:
- `getDatabaseProductName()`: Retrieves the name of the database product.
- `getUserName()`: Retrieves the username used to connect to the database.
- `getURL()`: Retrieves the URL of the database.
- `getSchemas()`: Retrieves names of all schemas in the database.
- `getPrimaryKeys()`, `getProcedures()`, `getTables()`: Retrieve specific metadata like primary key
information, stored procedure names, and table names.
### ResultSet Metadata

- **Definition**: Metadata specific to a `ResultSet`, providing information about its structure.
- **Access**: Obtained using `ResultSet.getMetaData()`, returning a `ResultSetMetaData` object.
- **Common Methods**:
- `getColumnCount()`: Returns the number of columns in the `ResultSet`.
- `getColumnName(int column)`: Retrieves the name of the column specified by its index.
- `getColumnType(int column)`: Retrieves the data type of the column specified by its index.
- Provides extensive methods to fetch detailed information about each column in the `ResultSet`.
### Use Cases
- **Database Metadata**: Useful for dynamically understanding database structure, capabilities, and
configurations.
- **ResultSet Metadata**: Essential for processing and understanding the structure of data retrieved from
queries, aiding in dynamic handling of query results.
These metadata functionalities in JDBC are crucial for building robust applications that interact with
databases effectively, enabling detailed control and manipulation of database operations and query results.
Data Types
| SQL Type | Java Type |
|------------------|---------------------------|
| CHAR | String |
| VARCHAR | String |
| LONGVARCHAR | String |
| NUMERIC | java.math.BigDecimal |
| DECIMAL | java.math.BigDecimal |
| BIT | Boolean |
| TINYINT | Byte |
| SMALLINT | Short |
| INTEGER | Integer |
| BIGINT | Long |
| REAL | float |
| FLOAT | float |
| DOUBLE | double |
| BINARY | Byte[] |
| VARBINARY | Byte[] |
| LONGVARBINARY | byte[] |
| BLOB | java.sql.Blob |
| CLOB | java.sql.Clob |
| ARRAY | java.sql.Array |
| STRUCT | java.sql.Struct |
| REF | java.sql.Ref |
| DATALINK | java.net.URL |
| DATE | java.sql.Date |
| TIME | java.sql.Time |
| TIMESTAMP | java.sql.Timestamp |
This table lists common SQL data types along with their corresponding Java equivalents. It is used to
determine the appropriate method names (`setxxx()` and `getxxx()`) when working with JDBC to set and
retrieve values of specific data types.
Exceptions
1. **SQLException**
- **Description**: Thrown for SQL-related errors, such as syntax errors in SQL queries or database
connectivity issues.
- **Cause**: Commonly occurs due to syntax errors in SQL queries or issues with database connectivity.
It can also arise from accessing closed objects or invalid database operations.
- **Methods**:
- `getNextException()`: Retrieves details about the SQL error or `null` if no more exceptions exist.
- `getErrorCode()`: Retrieves vendor-specific error codes associated with the exception.
2. **SQLWarning**
- **Description**: Thrown for warnings received from the database server.
- **Cause**: Typically occurs when the database server issues warnings about the execution of SQL
statements, such as potential loss of data or other server-side notifications.
- **Methods**:
- `getWarnings()`: Retrieves the first warning reported by the Connection.
- `getNextWarning()`: Retrieves subsequent warnings after the initial one.
3. **DataTruncation**
- **Description**: Thrown when data is lost due to truncation of a data value.
- **Cause**: Occurs when data values being processed exceed the defined size or precision of the
database column, leading to truncation of the data.
- **Use Case**: Often encountered when inserting or updating data where the value provided exceeds
the maximum length or precision allowed by the database schema.
These exceptions provide mechanisms for handling errors, warnings, and data truncation scenarios
encountered during JDBC operations, ensuring robust error handling and data integrity in database
interactions.

Module 5 Notes JDBC

Uploaded by

Copyright:

Available Formats

Module 5 Notes JDBC

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Module 5 Notes JDBC

Uploaded by

Copyright:

Available Formats

Advanced Java Programming Module 5

The concept of JDBC

o JDBC drivers are available for nearly every commercial DBMS.

JDBC Driver Types

- Manages connection pooling and other advanced JDBC features.

A brief overview of the JDBC Process

2. Connecting to the DBMS:

3. Create and Execute a SQL Statement

4. Process Data Returned by the DBMS:

### Complete Example Code:

5. Terminate the Connection to the DBMS

- Database association with the JDBC driver is typically handled by administrators.

7. Example Code for Properties:

- A `Properties` object `props` is created to store database connection properties.

Here's a simplified version of the code you provided:

Associating the JDBC/ODBC Bridge with the Database

DataSource pool = (DataSource)

String url = "jdbc:odbc:CustomerInformation";

4. Example Program for `executeUpdate()`:

String url = "jdbc:odbc:CustomerInformation";

String url = "jdbc:odbc:CustomerInformation";

- Precompiled query improves performance when executed multiple times.

public class CallableStatementExample {

- Print the retrieved data.

public class ResultSetExample {

public class ScrollableResultSetExample {

Not All JDBC Drivers Are Scrollable

public class CheckScrollableResultSet {

System.out.println("Forward only: " + forward);

Specifying Number of Rows to Return:

Example Program to Set Fetch Size:

public class FetchSizeExample {

System.err.println("Unable to load the JDBC/ODBC bridge." +

### Example Program

String query = "SELECT FirstName, LastName FROM Customers";

Example Code Summary

// Example SQL queries

// Add queries to batch

} catch (ClassNotFoundException | SQLException | BatchUpdateException

System.err.println("Error closing resources: " +

### Auto-Generated Keys

Here's a simplified explanation of `Metadata` and `ResultSet Metadata` in JDBC:

### ResultSet Metadata

### Use Cases

You might also like