Chapter Four

Java - Database connectivity

 JDBC ─ INTRODUCTION
• JDBC stands for Java Database Connectivity, which is a
standard Java API for database independent
connectivity between the Java programming
language, and a wide range of databases.
• The JDBC library includes APIs for each of the tasks
mentioned below that are commonly associated with
database usage.
– Making a connection to a database.
– Creating SQL or MySQL statements.
– Executing SQL or MySQL queries in the database.
– Viewing & Modifying the resulting records.
 Con’t…
• Fundamentally, JDBC is a specification that provides a
complete set of interfaces that allows for portable access to
an underlying database. Java can be used to write different
types of executables, such as:
– Java Applications
– Java Applets
– Java Servlets
– Java ServerPages (JSPs)
– Enterprise JavaBeans (EJBs).
• All of these different executables are able to use a JDBC driver
to access a database, and take advantage of the stored data.
• JDBC provides the same capabilities as ODBC, allowing Java
programs to contain database independent code
 Pre-Requisite
• Before moving further, you need to have a good
understanding of the following two subjects:
– Core JAVA Programming
– SQL or MySQL Database
 JDBC Architecture
• The JDBC API supports both two-tier and three-tier processing
models for database access but in general, JDBC Architecture
consists of two layers:
– JDBC API: This provides the application-to-JDBC Manager connection.
– JDBC Driver API: This supports the JDBC Manager-to-Driver
Connection.
• The JDBC API uses a driver manager and database-specific drivers
to provide transparent connectivity to heterogeneous databases.
• The JDBC driver manager ensures that the correct driver is used
to access each data source. The driver manager is capable of
supporting multiple concurrent drivers connected to multiple
heterogeneous databases.
 Con’t…
• Following is the architectural diagram, which shows the
location of the driver manager with respect to the JDBC
drivers and the Java application:
 JDBC Driver Types
• JDBC uses drivers to translate
generalized JDBC calls into vendor-
specific database calls
– Drivers exist for most popular databases
– Four Classes of JDBC drivers exist
• JDBC-ODBC Bridge plus ODBC driver, Type 1.
• Native-API, partly Java driver, Type 2.
• JDBC-Net, pure Java driver, Type 3.
• Native-protocol, pure Java driver, Type 4.
 Type 1 Driver – JDBC-ODBC Bridge
• Is a database driver implementation that employs the ODBC
driver to connect to the database.
• The driver is implemented in the sun.jdbc.odbc.JdbcOdbcDriver
class and comes with the Java 2 SDK, Standard Edition.
• platform specific. I.e. Only to Microsoft platform.
 Con’t…
Advantages:
• Almost any database, for which ODBC driver is installed,
can be accessed.
Disadvantages:
• Performance overhead since the calls have to go through
the JDBC overhead bridge to the ODBC driver, then to
the native database connectivity interface.
• The ODBC driver needs to be installed on the client
machine.
• Considering the client-side software needed, this might not
be suitable for applets
 Type 2 Driver – Native API Driver
Type II driver communicates directly with native
API
– Type II makes calls directly to the native API calls
– More efficient since there is one less layer to contend with (i.e. no ODBC)
– It is dependent on the existence of a native API for a database
– You cannot use a Type 2 driver in an applet since applets cannot load native
code.
– A Type 2 JDBC driver may require some Database Management System (DBMS)
networking software to be installed.

Functions:
• This type of driver converts JDBC calls into calls to the client API
for that database.
• Client -> JDBC Driver -> Vendor Client DB Library -> Database
 Con’t…
Advantage
• Better performance than Type 1 since no jdbc
to odbc translation is needed.
Disadvantages
• The Native driver needs to be installed on the
each client machine.
• The Vendor client library needs to be installed
on client machine.
 Type 3 Driver – The Network Protocol
Driver
• Is a database driver implementation which makes use of
a middle-tier between the calling program and the database.
• The middle-tier (application server) converts JDBC calls
directly or indirectly into the vendor-specific database
protocol.
• The type 3 driver is written entirely in Java. The same driver
can be used for multiple databases.
• platform-independent
• Type 3 JDBC drivers are the most flexible JDBC solution
because they do not require any native binary code on the
client.
 Con’t…
Functions:
• Follows a three tier communication approach.
• Can interface to multiple databases - Not vendor specific.
• The JDBC Client driver written in java, communicates with a
middleware-net-server using a database independent protocol,
and then this net server translates this request into database
commands for that database.
• Thus the client driver to middleware communication is database
independent.
• Client -> JDBC Driver -> Middleware-Net Server -> Any Database
 Con’t…
Advantages
• Since the communication between client and the middleware server is
database independent, there is no need for the vendor db library on the
client machine. Also the client to middleware needn’t be changed for a
new database.
• The Middleware Server can provide typical middleware services
like caching (connections, query results, and so on), load balancing,
logging, auditing etc.
• Can be used in internet since there is no client side software needed.
• At client side a single driver can handle any database. (It works provided
the middlware supports that database!)
Disadvantages
• Requires database-specific coding to be done in the middle tier.
• An extra layer added may result in a time-bottleneck.
Type 4 Driver - The Native Protocol Driver

• Is a database driver implementation that

converts JDBC calls directly into the vendor-
specific database protocol.
• The type 4 driver is written completely in Java
and is hence platform independent.
• It provides better performance over the type 1
and 2 drivers as it does not have the overhead
of conversion of calls into ODBC or database
API calls.
 Con’t…
Functions:
• Type 4 drivers are entirely written in Java that communicates directly with a
vendor's database through socket connections. No translation or
middleware layers are required, improving performance.
• The driver converts JDBC calls into the vendor-specific database protocol so
that client applications can communicate directly with the database server.
• Completely implemented in Java to achieve platform independence.
• Client Machine -> Native protocol JDBC Driver -> Database server
Advantages
• These drivers don't translate the requests into db request to ODBC or pass it
to client API for the database, nor do they need a middleware layer for
request indirection. Thus the performance is considerably improved.
Disadvantage
• At client side, a separate driver is needed for each database.
 Common JDBC Components
• The JDBC API provides the following interfaces and classes:
DriverManager: Loads database drivers and manages connections
between the application and the driver
– This class manages a list of database drivers. Matches
connection requests from the java application with the proper
database driver using communication sub protocol. The first
driver that recognizes a certain subprotocol under JDBC will be
used to establish a database Connection.
Driver: This interface handles the communications with the
database server. You will interact directly with Driver objects
very rarely. Instead, you use DriverManager objects, which
manages objects of this type. It also abstracts the details
associated with working with Driver objects.
 Con’t…
• Connection: This interface with all methods for contacting a
database. The connection object represents communication
context, i.e., all communication with database is through
connection object only.
• Statement: You use objects created from this interface to submit
the SQL statements to the database. Some derived interfaces
accept parameters in addition to executing stored procedures.
• ResultSet: These objects hold data retrieved from a database
after you execute an SQL query using Statement objects. It acts
as an iterator to allow you to move through its data.
• SQLException: This class handles any errors that occur in a
database application.
Connecting to Database
 JDBC ─ SQL SYNTAX
• This section gives an overview of SQL, which is a prerequisite to
understand JDBC concepts.
• After going through this chapter, you will be able to Create, Read,
Update, and Delete (often referred to as CRUD operations) data from
a database.
Create Database:The CREATE DATABASE statement is used for creating
a new database. The syntax is −
SQL> CREATE DATABASE DATABASE_NAME;
Example
• The following SQL statement creates a Database named EMP:
SQL> CREATE DATABASE EMP;
Drop Database:The DROP DATABASE statement is used for deleting an
existing database. The syntax is:
SQL> DROP DATABASE DATABASE_NAME;
Create Table
The CREATE TABLE statement is used for creating a new table. The syntax is −

SQL> CREATE TABLE table_name

(
column_name column_data_type,
column_name column_data_type,
column_name column_data_type
...
);

SQL> CREATE TABLE Employees

(
id INT NOT NULL,
age INT NOT NULL,
first VARCHAR(255),
last VARCHAR(255),
PRIMARY KEY ( id )
);
INSERT Data
The syntax for INSERT, looks similar to the following, where column1, column2,
and so on represents the new data to appear in the respective columns −

SQL> INSERT INTO table_name VALUES (column1,

column2, ...);

Example
The following SQL INSERT statement inserts a new row in the Employees database
created earlier −

SQL> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');

SELECT Data
The SELECT statement is used to retrieve data from a database. The
syntax for SELECT is −

SQL> SELECT column_name, column_name, ...

FROM table_name
WHERE conditions;

Example
The following SQL statement selects the age, first and last columns from the
Employees table, where id column is 100 −

SQL> SELECT first, last, age

FROM Employees
WHERE id = 100;
The following SQL statement selects the age, first and last columns from the
Employees table where first column contains Zara −

SQL> SELECT first, last, age

FROM Employees
WHERE first LIKE '%Zara%';
UPDATE Data
The UPDATE statement is used to update data. The syntax for UPDATE is −

SQL> UPDATE table_name

SET column_name = value, column_name = value, ...
WHERE conditions;

Example
The following SQL UPDATE statement changes the age column of the employee
whose id is 100 −

SQL> UPDATE Employees SET age=20 WHERE id=100;

DELETE Data
The DELETE statement is used to delete data from tables. The
syntax for DELETE is −

SQL> DELETE FROM table_name WHERE conditions;

Example
The following SQL DELETE statement deletes the record of the employee whose id
is 100 −

SQL> DELETE FROM Employees WHERE id=100;

 3. JDBC ─ ENVIRONMENT
Install Java
• Install J2SE Development Kit 5.0 (JDK 5.0) from Java Official Site.
• Make sure following environment variables are set as described below:
 JAVA_HOME: This environment variable should point to the directory where you installed
the JDK,
e.g. C:\Program Files\Java\jdk1.5.0.
 CLASSPATH: This environment variable should have appropriate paths set,
e.g. C:\Program Files\Java\jdk1.5.0_20\jre\lib.
 PATH: This environment variable should point to appropriate JRE bin,
e.g. C:\Program Files\Java\jre1.5.0_20\bin.
It is possible you have these variable set already, but just to make
sure here's how to check:
– Go to the control panel and double-click on System.
– Go to the Advanced tab and click on the Environment Variables.
– Now check if all the above mentioned variables are set properly.
• You automatically get both JDBC packages java.sql and javax.sql, when
you install J2SE Development Kit 5.0 (JDK 5.0).
 Con’t…
• How add mysql driver to a project
1. In Netbeans, create a new project called
TestMySqlDriver
2.On the project explorer, expand the project and right
click on Libraries
 3. Click on Add JAR/Folder option
4. Browse and choose the driver.
5. Click on open

12/16/2024 29
 JDBC
Basic Steps

• Import the necessary classes

• Load the JDBC driver
• Identify the data source (Define the
Connection URL)
• Establish the Connection
• Create a Statement Object
• Execute query string using Statement Object
• Retrieve data from the returned ResultSet
Object
• Close ResultSet & Statement & Connection
Object in order

12/16/2024 30
 • Import the packages − Requires that you include the
packages containing the JDBC classes needed for
database programming. Most often, using import
java.sql.* will suffice.

12/16/2024 31
 JDBC
Driver Manager
• DriverManager provides a common access
layer on top of different database drivers
– Responsible for managing the JDBC drivers available
to an application
– Hands out connections to the client code
• Maintains reference to each driver
– Checks with each driver to determine if it can handle
the specified URL
– The first suitable driver located is used to create a
connection
• DriverManager class can not be instantiated
– All methods of DriverManager are static
– Constructor is private

12/16/2024 32
 JDBC Driver
Loading
• Required prior to communication with a database using
JDBC
• It can be loaded
– dynamically using Class.forName(String drivername)
– System Automatically loads driver using jdbc.drivers
system property
• An instance of driver must be registered with
DriverManager class
• Each Driver class will typically
– create an instance of itself and register itself with the
driver manager
– Register that instance automatically by calling
RegisterDriver method of the DriverManager class
• Thus the code does not need to create an instance of the
class or register explicitly using registerDriver(Driver)
class
12/16/2024 33
 JDBC Driver
Loading: class.forName()
• Using forName(String) from java.lang.Class
instructs the JVM to find, load and link the
class identified by the String
e.g try {
Class.forName(“COM.cloudscape.core.JDBCDriver
”);
} catch (ClassNotFoundException e) {
System.out.println(“Driver not found”);
e.printStackTrace();
}
• At run time the class loader locates the driver
class and loads it
– All static initializations during this loading
– Note that the name of the driver is a literal string
12/16/2024 thus the driver does not need to be present at 34
How to load JDBC driver for mysql

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

12/16/2024 35
 JDBC Driver
Loading: System Property
• Put the driver name into the jdbc drivers System property
– When a code calls one of the methods of the driver manager,
the driver manager looks for the jdbc.drivers property
– If the driver is found it is loaded by the Driver Manager
– Multiple drivers can be specified in the property
– Each driver is listed by full package specification and class
name
– a colon is used as the delimiter between the each driver
e.g jdbc.drivers=com.pointbase.jdbc.jdbcUniversalDriver
• For specifying the property on the command line use:
– java -Djdbc.drivers=com.pointbase.jdbc.jdbcUniversalDriver
MyApp
• A list of drivers can also be provided using the Properties
file
– System.setProperty(“jdbc.drivers”,
“COM.cloudscape.core.JDBCDriver”);
–
12/16/2024 DriverManager only loads classes once so the system 36
property must be set prior to the any DriverManager method
 JDBC
URLs
• JDBC Urls provide a way to identify a database
• Syntax:
<protocol>:<subprotocol>:<protocol>
– Protocol: Protocol used to access database (jdbc
here)
– Subprotocol: Identifies the database driver
– Subname: Name of the resource
• Example
– Jdbc:cloudscape:Movies
– Jdbc:odbc:Movies

12/16/2024 37
 RDBMS Database URL format
MySQL jdbc:mysql://hostname:portNumber/databaseName

ORACLE jdbc:oracle:thin:@hostname:portNumber:databaseNa
me

DB2 jdbc:oracle:thin:@hostname:portNumber:databaseNa
me

PostgreSQ jdbc:postgresql://hostname:portNumber/
L databaseName

Microsoft SQL jdbc:sqlserver://hostname:portNumber;databaseName=dataBaseNam

Server

12/16/2024 38
Connection
Creation

Open a connection − Requires using the DriverManager.getConnection()

method to create a Connection object, which represents a physical connection
with the database.

Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)

12/16/2024 39
• Required to communicate with a database via JDBC
• Three separate methods:
public static Connection getConnection(String url)
public static Connection getConnection(String url, Properties info)
public static Connection getConnection(String url, String user,
String password)
• Code Example (Access)
try {// Load the driver class
System.out.println("Loading Class driver");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Define the data source for the driver
String sourceURL = "jdbc:odbc:music”;
// Create a connection through the DriverManager class
System.out.println("Getting Connection");
Connection databaseConnection =
DriverManager.getConnection(sourceURL);
}
catch (ClassNotFoundException cnfe) {
System.err.println(cnfe); }
catch (SQLException sqle) {
System.err.println(sqle);}
12/16/2024 40
 Connection
Creation
• Code Example (mysql)
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String sourceURL = ""jdbc:mysql://localhost:3306/rvu";
String user = “root";
String password = "“
Connection
databaseConnection=DriverManager.getConnection(sourceURL,u
ser, password );
System.out.println("Connected Connection");
}
catch (ClassNotFoundException cnfe) {
System.err.println(cnfe);
}
catch (SQLException sqle) {
System.err.println(sqle);
}
12/16/2024 41
 Connection
Creation
• Code Example (Oracle)
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String sourceURL =
"jdbc:oracle:thin:@delilah.bus.albany.edu:1521:databasename";
String user = "goel";
String password = "password";
Connection
databaseConnection=DriverManager.getConnection(sourceURL,user,
password );
System.out.println("Connected Connection"); }
catch (ClassNotFoundException cnfe) {
System.err.println(cnfe); }
catch (SQLException sqle) {
System.err.println(sqle);}

12/16/2024 42
 Connection
Closing
• Each machine has a limited number of connections
(separate thread)
– If connections are not closed the system will run out of
resources and freeze
• Naïve Way: • Correct way (Use the finally
– Syntax: public void close() throws SQLException
try { clause)
Connection conn try{
= Connection conn =
DriverManager.getConnection(u Driver.Manager.getConnection(u
rl); rl);
// Jdbc Code // JDBC Code
… } catch (SQLException sqle) {
} catch (SQLException sqle) { sqle.printStackTrace();
sqle.printStackTrace(); } finally {
} try {
conn.close(); conn.close();
} catch (Exception e) {
• SQL exception in the Jdbc code e.printStackTrace();
will prevent execution to reach }
}
conn.close()
12/16/2024 43
 Statement
Types
• Statements in JDBC abstract the SQL
statements
• Primary interface to the tables in the database
• Used to create, retrieve, update & delete data
(CRUD) from a table
– Syntax: Statement statement =
connection.createStatement();
• Three types of statements each reflecting a
specific SQL statements
1. Normal statements:
2. Prepared statements:
3. Callable statements: Stored procedures

12/16/2024 44
 Statement Use this for general-purpose access to your database.
Useful when you are using static SQL statements at runtime. The Statement
interface cannot accept parameters.

• Statement used to send SQL commands to the

database

Syntax:

public Statement createStatement() throws

SQLException
Statement statement =
connection.createStatement();
Statement stmt = null;
try {
stmt = conn.createStatement( );
...
}
catch (SQLException e) {
...
}
12/16/2024 finally { 45
...
 Closing Statement Object
• Just as you close a Connection object to save database resources, for the same
reason you should also close the Statement object.

• A simple call to the close() method will do the job. If you close the Connection object
first, it will close the Statement object as well. However, you should always explicitly
close the Statement object to ensure proper cleanup.

Statement stmt = null;

try {
stmt = conn.createStatement( );
...
}
catch (SQLException e) {
...
}
finally {
12/16/2024 stmt.close(); 46
}
• PreparedStatement Use this when you plan to use
the SQL statements many times. The PreparedStatement
interface accepts input parameters at runtime.
public PreparedStatement prepareStatement(String sql)
throws SQLException
PreparedStatement pstatement =
prepareStatement(sqlString);

12/16/2024 47
 Creating PreparedStatement Object
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
...
}
catch (SQLException e) {
...
}

• All parameters in JDBC are represented by the ? symbol, which is known as the
parameter marker. You must supply values for every parameter before executing
the SQL statement.

The setXXX() methods bind values to the parameters, where XXX represents the Java
data type of the value you wish to bind to the input parameter. If you forget to supply
the values, you will receive an SQLException.

Each parameter marker is referred by its ordinal position. The first marker represents
position 1, the next position 2, and so forth. This method differs from that of Java
array indices, which starts at 0.
12/16/2024 48
Closing PreparedStatement Object
• Just as you close a Statement object, for the same reason you should
also close the PreparedStatement object.

12/16/2024 49
The CallableStatement Objects

• CallableStatement used to call stored procedures

• public CallableStatement prepareCall(String sql) throws
SQLException

• Just as a Connection object creates the Statement and PreparedStatement

objects, it also creates the CallableStatement object, which would be used to
execute a call to a database stored procedure.

12/16/2024 50
 Statement
Release
• Statement can be used try{
multiple times for sending Connection conn =
Driver.Manager.getConnect
a query ion(url);
• It should be released when Statement stmt =
it is no longer required conn.getStatement();
– Statement.close(): // JDBC Code
– It releases the JDBC } catch (SQLException
resources immediately sqle) {
instead of waiting for the
statement to close sqle.printStackTrace();
automatically via garbage } finally {
collection try {stmt.close();
• conn.close();
Garbage collection is done
} catch (Exception e) {
when an object is
unreachable e.printStackTrace();
– An object is reachable if }
there is a chain of }
12/16/2024 reference that reaches the 51
object from some root
 JDBC
Logging
• DriverManager provides methods for managing
output
– DriverManagers debug output can be directed to a
printwriter
public static void setLogWriter(PrintWriter pw)
– PrintWriter can be wrapped for any writer or
OutputStream
– Debug statements from the code can be sent to the
log as well.
public static void println(String s)
• Code
FileWriter fw = new FileWriter(“mydebug.log”);
PrintWriter pw = new PrintWriter(fw);
// Set the debug messages from Driver manager to pw
DriverManager.setLogWriter(pw);
12/16/2024 52
// Send in your own debug messages to pw
 Querying the Database

12/16/2024 53
 Executing Queries
Methods
• Two primary methods in statement interface used
for executing Queries
– executeQuery Used to retrieve data from a database
– executeUpdate: Used for creating, updating & deleting
data
• executeQuery used to retrieve data from database
– Primarily uses Select commands
• executeUpdate used for creating, updating &
deleting data
– SQL should contain Update, Insert or Delete commands
• Uset setQueryTimeout to specify a maximum delay
to wait for results

12/16/2024 54
 Executing Queries
Data Definition Language (DDL)
• Data definition language queries use
executeUpdate
• Syntax: int executeUpdate(String sqlString)
throws SQLException
– It returns an integer which is the number of rows
updated
– sqlString should be a valid String else an exception is
thrown
• Example 1: Create a new table
Statement statement = connection.createStatement();
String sqlString =
“Create Table Catalog”
+ “(Title Varchar(256) Primary Key Not Null,”+
+ “LeadActor Varchar(256) Not Null, LeadActress
Varchar(256) Not Null,”
12/16/2024 + “Type Varchar(20) Not Null, ReleaseDate Date Not 55
 Executing Queries
DDL (Example)
• Example 2: Update table
Statement statement = connection.createStatement();
String sqlString =
“Insert into Catalog”
+ “(Title, LeadActor, LeadActress, Type, ReleaseDate)”
+ “Values(‘Gone With The Wind’, ‘Clark Gable’, ‘Vivien
Liegh’,”
+ “’Romantic’, ‘02/18/2003’ ”
Statement.executeUpdate(sqlString);
– executeUpdate returns a 1 since one row is added

12/16/2024 56
 Executing Queries
Data Manipulation Language (DML)
• Data definition language queries use executeQuery
• Syntax
ResultSet executeQuery(String sqlString) throws
SQLException
– It returns a ResultSet object which contains the results of
the Query
• Example 1: Query a table
Statement statement = connection.createStatement();
String sqlString = “Select Catalog.Title, Catalog.LeadActor,
Catalog.LeadActress,” +
“Catalog.Type, Catalog.ReleaseDate From
Catalog”;
ResultSet rs = statement.executeQuery(sqlString);

12/16/2024 57
 ResultSet
Definition
• ResultSet contains the results of the database query that
are returned
• Allows the program to scroll through each row and read all
columns of data
• ResultSet provides various access methods that take a
column index or column name and returns the data
– All methods may not be applicable to all resultsets depending
on the method of creation of the statement.
• When the executeQuery method returns the ResultSet the
cursor is placed before the first row of the data
– Cursor refers to the set of rows returned by a query and is
positioned on the row that is being accessed
– To move the cursor to the first row of data next() method is
invoked on the resultset
– If the next row has a data the next() results true else it returns
false and the cursor moves beyond the end of the data
• First column has index 1, not 0
12/16/2024 58
 ResultSet
• ResultSet contains the results of the database query that
are returned
• Allows the program to scroll through each row and read all
the columns of the data
• ResultSet provides various access methods that take a
column index or column name and returns the data
– All methods may not be applicable to all resultsets depending
on the method of creation of the statement.
• When the executeQuery method returns the ResultSet the
cursor is placed before the first row of the data
– Cursor is a database term that refers to the set of rows
returned by a query
– The cursor is positioned on the row that is being accessed
– First column has index 1, not 0
• Depending on the data numerous functions exist
– getShort(), getInt(), getLong()
– getFloat(), getDouble()
– getClob(), getBlob(),
–
12/16/2024 getDate(), getTime(), getArray(), getString() 59
 ResultSet
• Examples:
– Using column Index:
Syntax:public String getString(int columnIndex) throws
SQLException
e.g. ResultSet rs = statement.executeQuery(sqlString);
String data = rs.getString(1)
– Using Column name
public String getString(String columnName) throws
SQLException
e.g. ResultSet rs = statement.executeQuery(sqlString);
String data = rs.getString(Name)
• The ResultSet can contain multiple records.
– To view successive records next() function is used on
the ResultSet
– Example: while(rs.next()) {
12/16/2024 60
– System.out.println(rs.getString); }
 Scrollable ResultSet
• ResultSet obtained from the statement created using the no
argument constructor is:
– Type forward only (non-scrollable)
– Not updateable
• To create a scrollable ResultSet the following statement
constructor is required
– Statement createStatement(int resultSetType, int
resultSetConcurrency)
• ResultSetType determines whether it is scrollable. It can have the
following values:
– ResultSet.TYPE_FORWARD_ONLY
– ResultSet.TYPE_SCROLL_INSENSITIVE (Unaffected by changes to
underlying database)
– ResultSet.TYPE_SCROLL_SENSITIVE (Reflects changes to underlying
database)
• ResultSetConcurrency determines whether data is updateable. Its
possible values are
– CONCUR_READ_ONLY
– CONCUR_UPDATEABLE
12/16/2024 61
• Not all database drivers may support these functionalities
 Scrollable ResultSet
• On a scrollable ResultSet the following commands can be
used
– boolean next(), boolean previous(), boolean first(), boolean
last()
– void afterLast(), void beforeFirst()
– boolean isFirst(), boolean isLast(), boolean isBeforeFirst(),
boolean isAfterLast()
• Example

12/16/2024 62
 RowSet
• ResultSets limitation is that it needs to stay connected to
the data source
– It is not serializable and can not transporting across the
network
• RowSet is an interface which removes the limitation
– It can be connected to a dataset like the ResultSet
– It can also cache the query results and detach from the database
• RowSet is a collection of rows
• RowSet implements a custom reader for accessing any
tabular data
– Spreadsheets, Relational Tables, Files
• RowSet object can be serialized and hence sent across the
network
• RowSet object can update rows while diconnected fro the
data source
– It can connect to the data source and update the data
• 12/16/2024
Three separate implementations of RowSet 63
 RowSet
• RowSet is derived from the BaseRowSet
– Has SetXXX(…) methods to supply necessary information for
making connection and executing a query
• Once a RowSet gets populated by execution of a query or
from some other data source its data can be manipulated or
more data added
• Three separate implementations of RowSet exist
– CachedRowSet: Disconnected from data source, scrollable &
serilaizable
– JdbcRowSet: Maintains connection to data source
– WebRowSet: Extension of CachedRowSet that can produce
representation of its contents in XML

12/16/2024 64
 MetaData
• Meta Data means data about data
• Two kinds of meta data in JDBC
– Database Metadata: To look up information about the database
(here)
– ResultSet Metadata: To get the structure of data that is
returned (later)
• Example
– connection.getMetaData().getDatabaseProductName()
– connection.getMetaData().getDatabaseProductVersion()
• Sample Code:
private void showInfo(String driver,String url,String user,String password,
String table,PrintWriter out) {
Class.forName(driver);
Conntection con = DriverManager.getConnection(url, username,
password);
DatabaseMetaData dbMetaData = connection.getMetaData();
String productName = dbMetaData.getDatabaseProductName();
System.out.println("Database: " + productName);
String productVersion = dbMetaData.getDatabaseProductVersion();
12/16/2024 System.out.println("Version: " + productVersion); 65
}
 Source Code

12/16/2024 66
 create database if not exists demo;
use demo;
drop table if exists employees;

CREATE TABLE `employees` (

`id` int(11) NOT NULL AUTO_INCREMENT, `last_name`
varchar(64) DEFAULT NULL, `
first_name` varchar(64) DEFAULT NULL,
`email` varchar(64) DEFAULT NULL,
`department` varchar(64) DEFAULT NULL,
`salary` DECIMAL(10,2) DEFAULT NULL, PRIMARY KEY
(`id`))
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT
CHARSET=latin1;

12/16/2024 67
 INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`,
`department`, `salary`)
VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`,

`department`, `salary`)
VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`,

`department`, `salary`)
VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);

12/16/2024 68
 INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`,
`department`, `salary`)
VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`,

`department`, `salary`)
VALUES (4,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`,

`department`, `salary`)
VALUES (5,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);

12/16/2024 69
 INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`,
`department`, `salary`)
VALUES (6,'Doe','John','john.doe@foo.com', 'HR', 55000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`,

`department`, `salary`)
VALUES (7,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`,

`department`, `salary`)
VALUES (8,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);

12/16/2024 70
 //Executing Select Query
import java.sql.*;
public class JdbcDemo {

public static void main(String[] args) throws SQLException { Connection

myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
String dbUrl = "jdbc:mysql://localhost:3306/demo";
String user = “root";
String pass = "";

try { // 1. Get a connection to database

myConn = DriverManager.getConnection(dbUrl,
user, pass); //
2. Create a statement myStmt =
myConn.createStatement();

12/16/2024 71
 // 3. Execute SQL query myRs =
myStmt.executeQuery("select * from employees");

// 4. Process the result set

while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", " +
myRs.getString("first_name"));
}
}
catch (Exception exc) {
exc.printStackTrace();
}

12/16/2024 72
 //Executing Insert Query
import java.sql.*;
public class JdbcDemo {

public static void main(String[] args) throws SQLException { Connection

myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
String dbUrl = "jdbc:mysql://localhost:3306/demo";
String user = "student";
String pass = "student";

try { // 1. Get a connection to database

myConn = DriverManager.getConnection(dbUrl,
user, pass); //
2. Create a statement myStmt =
myConn.createStatement();

12/16/2024 73
 // 3. Insert a new employee System.out.println("Inserting a
new employee to database\n"); int rowsAffected =
myStmt.executeUpdate("insert into employees " +
"(last_name, first_name, email, department, salary) " +
"values " + "('Wright',
'Eric', 'eric.wright@foo.com', 'HR', 33000.00)");

12/16/2024 74
 // 4. Verify this by getting a list of employees
myRs = myStmt.executeQuery("select * from employees order by last_name");

// 5. Process the result set

while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", " +
myRs.getString("first_name"));
} }
catch (Exception exc) {
exc.printStackTrace();
}
finally {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
12/16/2024 75
}}
 finally {
if (myRs != null) { myRs.close();

}
if (myStmt != null) { myStmt.close();

}
if (myConn != null) {
myConn.close();
}
}
}

12/16/2024 76
 myConn.createStatement();
// 3.
Execute SQL query myRs =
myStmt.executeQuery("select * from
employees");
// 4. Process the
result set while
(myRs.next()) {

System.out.println(myRs.getString("last_name"
) + ", " + myRs.getString("first_name"));
} }
catch (Exception exc) {

exc.printStackTrace(); }
finally {
if (myRs != null) {
myRs.close();
}
if
(myStmt != null) {
myStmt.close();
}
if (myConn !=
12/16/2024 null) { 77
 //Executing Update Query
import java.sql.*;
public class JdbcDemo {

public static void main(String[] args) throws SQLException { Connection

myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
String dbUrl = "jdbc:mysql://localhost:3306/demo";
String user = “root";
String pass = "";

try { // 1. Get a connection to database

myConn = DriverManager.getConnection(dbUrl,
user, pass); //
2. Create a statement myStmt =
myConn.createStatement();

12/16/2024 78
 int rowsAffected = myStmt.executeUpdate(
"update employees " +
"set email='john.doe@luv2code.com' " +
"where last_name='Doe' and first_name='John'");
// Call helper method to display the
employee's information System.out.println("AFTER
THE UPDATE..."); displayEmployee(myConn, "John",
"Doe"); }

catch (Exception exc) {

exc.printStackTrace();
} finally {
close(myConn,
myStmt, myRs); } }

12/16/2024 79
 //Executing Delete Query
import java.sql.*;
public class JdbcDemo {

public static void main(String[] args) throws SQLException { Connection

myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
String dbUrl = "jdbc:mysql://localhost:3306/demo";
String user = “root";
String pass = "";

try { // 1. Get a connection to database

myConn = DriverManager.getConnection(dbUrl,
user, pass); //
2. Create a statement myStmt =
myConn.createStatement();

12/16/2024 80
 int rowsAffected = myStmt.executeUpdate(
"delete from employees " +
"where last_name='Doe' and first_name='John'");

} catch (Exception exc) {

exc.printStackTrace(); }
finally { close(myConn,
myStmt, myRs); } }

12/16/2024 81
• Prepared Statement

12/16/2024 82
 import java.sql.*;public class Driver {
public static void main(String[] args) throws SQLException {
Connection myConn = null;
PreparedStatement myStmt = null;
ResultSet myRs = null;
try { /
/ 1. Get a connection to database
myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", “root" ,
""); /
/ 2. Prepare statement
myStmt = myConn.prepareStatement("select * from employees where salary > ? and
department=?");
// 3. Set the parameters
myStmt.setDouble(1, 80000);
myStmt.setString(2, "Legal");

12/16/2024 83
 // 4. Execute SQL query
myRs = myStmt.executeQuery();
// 5. Display the result set
display(myRs); //
// Reuse the prepared statement: salary > 25000, department = HR
//
System.out.println("\n\nReuse the prepared statement: salary > 25000, department =
HR");
// 6. Set the parameters
myStmt.setDouble(1, 25000);
myStmt.setString(2, "HR");
// 7. Execute SQL query
myRs = myStmt.executeQuery();
// 8. Display the result set
display(myRs);
}

12/16/2024 84
 catch (Exception exc) {
exc.printStackTrace();
}
finally {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {

myConn.close();
}
}
}

12/16/2024 85
 private static void display(ResultSet myRs) throws SQLException {
while (myRs.next()) {
String lastName = myRs.getString("last_name");
String firstName = myRs.getString("first_name"); double
salary = myRs.getDouble("salary"); String department =
myRs.getString("department");
System.out.printf("%s, %s, %.2f, %s\n", lastName, firstName, salary,
department);
}
}}

12/16/2024 86
 Connecting to Microsoft Access
/** // Load the driver
* The code allows a user to connect to the MS Access Database and try {
* run queries on the database. A sample query execution is provided // Load the driver class
* in this code. This is developed to help the students get initially Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
* connected to the database.
* // Define the data source for the driver
* @author Sanjay Goel String sourceURL = "jdbc:odbc:music";
* @company School of Business, University at Albany
* // Create a connection through the DriverManager class
* @version 1.0 Connection databaseConnection
* @created April 01, 2002 - 9:05 AM = DriverManager.getConnection(sourceURL);
* System.out.println("Connected Connection");
* Notes 1: Statement is an interface hence can not be instantiated
* using new. Need to call createStatement method of connection class // Create Statement
* Statement statement = databaseConnection.createStatement();
* Notes 2: Use executeQuery for DML queries that return a resultset String queryString
* e.g., SELECT and Use executeUpdate for DDL & DML which do not = "SELECT recordingtitle, listprice FROM
* return Result Set e.g. (Insert Update and Delete) & DDL (Create recordings";
* Table, Drop Table, Alter Table)
* // Execute Query
* */ ResultSet results = statement.executeQuery(queryString);

import java.sql.*; // Print results

while (results.next()){
public class ConnectAccess {
System.out.println(results.getString("recordingtitle") +
/** "\t" +
results.getFloat("listprice"));
* This is the main function which connects to the Access database
}
* and runs a simple query
*
// Close Connection
* @param String[] args - Command line arguments for the program
databaseConnection.close();
* @return void
}
* @exception none
catch (ClassNotFoundException cnfe) {
*
System.err.println(cnfe);
*/
}
public static void main(String[] args) {
catch (SQLException sqle) {
System.err.println(sqle);
}
}
}
12/16/2024 87
 Connecting to Oracle
/** // Load the driver
* The code allows a user to connect to the ORACLE Database and run try {
* queries on the database. A sample query execution is provided in // Load the driver class
* this code. This is developed to help the students get initially Class.forName("oracle.jdbc.driver.OracleDriver");
* connected to the database.
* // Define the data source for the driver
* @author Sanjay Goel String sourceURL
* @company School of Business, University at Albany =
* "jdbc:oracle:thin:@delilah.bus.albany.edu:1521:bodb01";
* @version 1.0
* @created April 01, 2002 - 9:05 AM // Create a connection through the DriverManager class
* String user = "goel";
* Notes 1: Statement is an interface hence can not be instantiated String password = "goel";
* using new. Need to call createStatement method of connection class Connection databaseConnection
* = DriverManager.getConnection(sourceURL, user,
* Notes 2: Use executeQuery for DML queries that return a resultset password);
* e.g., SELECT and Use executeUpdate for DDL & DML which do not System.out.println("Connected to Oracle");
* return Result Set e.g. (Insert Update and Delete) & DDL (Create
* Table, Drop Table, Alter Table) // Create a statement
* Statement statement = databaseConnection.createStatement();
* */
// Create a query String
import java.sql.*; String sqlString = "SELECT artistid, artistname FROM
artistsandperformers";
public class ConnectOracle {
// Close Connection
databaseConnection.close();
/**
}
* This is the main function which connects to the Oracle database
catch (ClassNotFoundException cnfe) {
* and executes a sample query
System.err.println(cnfe);
*
}
* @param String[] args - Command line arguments for the program
catch (SQLException sqle) {
* @return void
System.err.println(sqle);
* @exception none
}
*
}
*/
}
public static void main(String[] args) {

12/16/2024 88
 Connecting to Cloudscape
/** // Create a connection through the DriverManager class
* The code allows a user to connect to the Cloudscape Database and Connection databaseConnection =
* run queries on the database. A sample query execution is provided DriverManager.getConnection(sourceURL);
* in this code. This is developed to help the students get initially System.out.println("Connected Connection");
* connected to the database.
* // Create a statement
* @author Sanjay Goel Statement statement = databaseConnection.createStatement();
* @company School of Business, University at Albany
* // Create an SQL statement
* @version 1.0 String sqlString = "SELECT artistid, artistname FROM
* @created April 01, 2002 - 9:05 AM artistsandperformers";
*
* Notes 1: Statement is an interface hence can not be instantiated // Run Query
* using new. Need to call createStatement method of connection class ResultSet results = statement.executeQuery(sqlString);
*
* Notes 2: Use executeQuery for DML queries that return a resultset // Print Results
* e.g., SELECT and Use executeUpdate for DDL & DML which do not while(results.next()) {
* return Result Set e.g. (Insert Update and Delete) & DDL (Create System.out.println(results.getInt("artistid") + "\t" +
* Table, Drop Table, Alter Table)
results.getString("artistname"));
*
}
* */
// Close Connection
import java.sql.*;
databaseConnection.close();
}
public class ConnectCloudscape {
catch (ClassNotFoundException cnfe) {
System.err.println(cnfe);
public static void main(String[] args) {
}
catch (SQLException sqle) {
// Load the driver
System.err.println(sqle);
try {
}
// Load the driver class
}
Class.forName("COM.cloudscape.core.JDBCDriver");
}
// Define the data source for the driver
String sourceURL = "jdbc:cloudscape:Wrox4370.db";

12/16/2024 89
 Prepared Statement
Import java.sql.*; // code from IVOr horton

public class AuthorDatabase {

public static void main(String[] args) {
try {
String url = “jdbc:odbc:library”;
String driver = “sun.jdbc.odbc.JdbcOdbcDriver”;
String user = “goel”
String password = “password”;
// Load the Driver
Class.forName(driver);
Connection connection = DriverManager.getConnection();
String sqlString = “UPDATE authors SET lastname = ?
Authid = ?”;
PreparedStatement ps =
connection.prepareStatement(sqlString);
// Sets first placeholder to Allamaraju
ps.setString(1, “Allamaraju”);
// Sets second placeholder to 212
ps.setString(2, 212);
// Executes the update
int rowsUpdated = ps.executeUpdate();
System.out.println(“Number of rows changed = “ +
rowsUpdated);
connection.close();
}
catch (ClassNotFoundException cnfe) {
System.out.println(“Driver not found”);
cnfe.printStackTrace();
}
catch (SQLException sqle) {
System.out.println(“Bad SQL statement”);
sqle.printStackTrace();
12/16/2024
} 90
 Access Data Source
• Create a database
• Select DataSources (ODBC) from the control panel
(Start Settings ControlPanelDataSourcesAdministrativeToolsData
Sources)
• Select the System DSN tab
• On ODBC data source administrator click on add
• Select the database driver as Microsoft Access Driver

12/16/2024 91
 Access Data Source
• Fill the ODBC Microsoft Access Setup Form
– Write Data Source Name (Name of the data source
that you have in the program)
– Add description of database
– Click on select and browse the directory to pick a
database file
– Click on OK

12/16/2024 92
 Advanced Topics

12/16/2024 93
 JDBC – Data Types

12/16/2024 94
 Prepared Statement
• PreparedStatement provides a means to create a
reusable statement that is precompiled by the
database
• Processing time of an SQL query consists of
– Parsing the SQL string
– Checking the Syntax
– Checking the Semantics
• Parsing time is often longer than time required to
run the query
• PreparedStatement is used to pass an SQL string
to the database where it can be pre-processed for
execution

12/16/2024 95
 Prepared Statement
• It has three main uses
– Create parameterized statements such that data for
parameters can be dynamically substituted
– Create statements where data values may not be
character strings
– Precompiling SQL statements to avoid repeated
compiling of the same SQL statement
• If parameters for the query are not set the driver
returns an SQL Exception
• Only the no parameters versions of
executeUpdate() and executeQuery() allowed with
prepared statements.

12/16/2024 96
 Prepared Statement
• Example
// Creating a prepared Statement
String sqlString = “UPDATE authors SET lastname = ? Authid =
?”;
PreparedStatement ps =
connection.prepareStatement(sqlString);
ps.setString(1, “Allamaraju”); // Sets first placeholder to
Allamaraju
ps.setString(2, 212); // Sets second placeholder to
212
ps.executeUpdate(); // Executes the update

12/16/2024 97
 Callable Statements & Stored
Procedures
• Stored Procedures
– Are procedures that are stored in a database.
– Consist of SQL statements as well as procedural
language statements
– May (or may not) take some arguments
– May (or may not) return some values
• Advantages of Stored Procedures
– Encapsulation & Reuse
– Transaction Control
– Standardization
• Disadvantages
– Database specific (lose independence)
• Callable statements provide means of using stored
procedures in the database
12/16/2024 98