Java
Database Connectivity
(JDBC)
Prepared by : Elen T. Capariño
JDBC
JDBC is a standard interface for connecting to
relational databases from Java.
The JDBC classes and interfaces are in the
[Link] package.
JDBC 1.22 is part of JDK 1.1; JDBC 2.0 is part of
Java 2
Overview of Querying a Database With JDBC
Connect
Query
Process
results
Close
Stage 1: Connect
Connect Register the driver
Query Connect to the database
Process
results
Close
A JDBC Driver
Is an interpreter that translates JDBC
method calls to vendor-specific database
commands
Database
JDBC calls commands
Driver
Database
Implements interfaces in [Link]
Can also provide a vendor’s extensions to
the JDBC standard
About JDBC URLs
JDBC uses a URL to identify the database
connection.
jdbc:<subprotocol>:<subname>
Database
Protocol Subprotocol
identifier
jdbc:oracle:<driver>:@<database>
Example: jdbc:mysql://localhost:3306/
How to Make the Connection
1. Load JDBC Driver
[Link](driver);
[Link](driver);
// or
[Link]("[Link]");
2. Connect to the database
Connection conn = [Link]
(URL, userid, password);
Connection conn = [Link]
(url + dbName, userName, password);
Example of establishing a connection
Stage 2: Query
Connect
Query Create a statement
Process Query the database
results
Close
The Statement Object
A Statement object sends your SQL
statement to the database.
You need an active connection to create
a JDBC statement.
Statement has three methods to execute
a SQL statement:
– executeQuery() for QUERY
statements
– executeUpdate() for INSERT,
UPDATE, DELETE, or DDL
statements
– execute() for either type of statement
How to Query the Database
1. Create an empty statement object.
Statement stmt = [Link]();
2. Execute the statement.
ResultSet rset = [Link](statement);
int count = [Link](statement);
boolean isquery = [Link](statement);
Querying the Database: Examples
Execute a select statement.
Statement stmt = [Link]();
ResultSet rset = [Link]
("select RENTAL_ID, STATUS from ACME_RENTALS");
• Execute a delete statement.
Statement stmt = [Link]();
int rowcount = [Link]
("delete from ACME_RENTAL_ITEMS where rental_id = 1011");
Stage 3: Process the Results
Connect
Query
Step through the results
Process Assign results to Java
results variables
Close
The ResultSet Object
JDBC returns the results of a query in a
ResultSet object.
A ResultSet maintains a cursor pointing to
its current row of data.
Use next() to step through the result set
row by row.
getString(), getInt(), and so on assign
each value to a Java variable..
How to Process the Results
1. Step through the result set.
while ([Link]()) { … }
2. Use getXXX() to get each column value.
String val = String val =
[Link](colname); [Link](colIndex);
while ([Link]()) {
String title = [Link]("TITLE");
String year = [Link]("YEAR");
… // Process or display the data
}
How to Handle SQL Null Values
Java primitive types cannot have null
values.
Do not use a primitive type when your
query might return an SQL null.
Use [Link]() to determine
whether a column has a null value.
while ([Link]()) {
String year = [Link]("YEAR");
if ([Link]() {
… // Handle null value
}
…}
Mapping Database Types to Java Types
ResultSet maps database types to
Java types.
ResultSet rset = [Link]
("select RENTAL_ID, RENTAL_DATE, STATUS
from ACME_RENTALS");
int id = [Link](1);
Date rentaldate = [Link](2);
String status = [Link](3);
Col Name Type
RENTAL_ID NUMBER
RENTAL_DATE DATE
STATUS VARCHAR2
Stage 4: Close
Connect
Query
Close the result set
Process
results Close the statement
Close Close the connection
How to Close the Connection
1. Close the ResultSet object.
[Link]();
2. Close the Statement object.
[Link]();
3. Close the connection (not necessary
for server-side driver).
[Link]();
The PreparedStatement Object
• A PreparedStatement object
holds precompiled SQL statements.
• Use this object for statements you want
to execute more than once.
• A prepared statement can contain
variables that you supply each time
you execute the statement.
How to Create a Prepared Statement
[Link] the driver and create the database
connection.
[Link] the prepared statement, identifying
variables with a question mark (?).
PreparedStatement pstmt =
[Link]("update ACME_RENTALS
set STATUS = ? where RENTAL_ID = ?");
PreparedStatement pstmt =
[Link]("select STATUS from
ACME_RENTALS where RENTAL_ID = ?");
How to Create a Prepared Statement
[Link] the driver and create the
database connection.
[Link] the prepared statement,
identifying variables with a question
mark (?).
Example :
PreparedStatement pstmt = [Link]
("select STATUS from ACME_RENTALS where RENTAL_ID = ?");
PreparedStatement ps = null;
ps = [Link]("INSERT INTO tbl_product (key_product, key_category,
fld_product_name, fld_inventory_qty, fld_unit_price) “ + "values (?, ?, ?, ?, ?)");
How to Execute a Prepared Statement
1. Supply values for the variables.
[Link](index, value);
2. Execute the statement.
[Link]();
[Link]();
Example : [Link](1, product.getKey_product());
[Link](2, product.getKey_category());
[Link](3, product.getFld_product_name());
[Link](4, product.getFld_inventory_qty());
[Link](5, (int) product.getFld_unit_price());
[Link]();
A Simple JDBC Program…
