Java Database Connectivity

(JDBC)
G A N E S H PA I
A S S T. P R O F E S S O R G D I I I
D E PA RT M E N T O F C S E
N M A M I T, N I T T E
Textbook
 Chapter 7

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 2

Overview of topics
 The Vendor Variation Problem
 What is JDBC?
 SQL and Versions of JDBC
 ODBC & JDBC-ODBC
 Simple Database Access
 Modifying the Database Contents
 Transactions
 Meta Data
 Scrollable ResultSets in JDBC 2.0
 Modifying Databases via Java Methods

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 3

JDBC  Several DB exists such as Oracle DB, MS SQL, MS Access, MySQL,
The Vendor Variation Problem PostgreSQL, SQLite, IBM Db2, MariaDB, IBM Informix, etc…

What is JDBC?  Internal format of databases and its associated database API vary
from vendor to vendor
SQL and Versions of JDBC
 Internal format of an Oracle database differs from that of Access
ODBC & JDBC-ODBC
database, while the format of MySQL database differs from both
Simple Database Access of these.
Modifying the Database Contents  Coping with these variation of internal format is the fundamental
problem
Transactions

Meta Data  Attempting to provide a general access method that will work for
all relational databases is the issue of concern
Scrollable ResultSets in JDBC 2.0
 This issue of connecting java program to any DB is addressed
Modifying Databases via Java Methods using the DB driver classes, providing vendor-specific API for that
database
 It is a mediating module that allows JDBC to communicate with
vendor-specific API for that database

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 4

JDBC  JDBC is used for accessing databases from Java Applications

The Vendor Variation Problem  Is a standard interface for connecting to relational databases from
Java
What is JDBC?
 Contains collection of API’s that lets you access virtually any
SQL and Versions of JDBC
tabular data source from the Java programming language
ODBC & JDBC-ODBC
 Information is transferred from relations to objects and vice-versa
Simple Database Access  databases optimized for searching/indexing
Modifying the Database Contents  objects optimized for engineering/flexibility

Transactions  A JDBC driver is a set of Java classes that implements JDBC interfaces,
targeting a specific database
Meta Data

Scrollable ResultSets in JDBC 2.0

Modifying Databases via Java Methods

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 5

 Database
Driver Classes

Oracle
These are Driver
Java classes
Oracle
API calls
Java Web/
Standalone JDBC MS SQL
Application Driver

MS SQL

MySQL
Driver

Network MySQL
JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 6
JDBC  The standard means of accessing a relational database is to use SQL
The Vendor Variation Problem (Structured Query Language)
What is JDBC?  JDBC 1.0 package, appeared in JDK 1.1, contains the core JDBC API in
java.sql package
SQL and Versions of JDBC

ODBC & JDBC-ODBC  Using this API, it is possible to access data from relational databases,
spreadsheets and flat files
Simple Database Access
 Provides DriverManager class for making a connection to DB
Modifying the Database Contents

Transactions
 With JDBC 2.0 in J2SE 1.4, extra functionality was introduced with the
additional package javax.sql
Meta Data
 DataSource interface was introduced that provides the preferred method
Scrollable ResultSets in JDBC 2.0
of making a connection to a database
Modifying Databases via Java Methods
 A DataSource object has properties that can be modified.
 Ex.: If the data source is moved to a different server, the property for the
server can be changed without requiring the code accessing the data
source to be changed

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 7

JDBC  Types of JDBC Drivers
The Vendor Variation Problem  Type 1: JDBC-ODBC bridge
What is JDBC?  Driver converts JDBC method calls into ODBC function calls.
SQL and Versions of JDBC  Type 2: Native-API driver
ODBC & JDBC-ODBC  Driver converts JDBC method calls into native calls of the
Simple Database Access database API
Modifying the Database Contents  Type 3: Network-Protocol driver (Middleware driver)
Transactions  middle-tier (application server) converts JDBC calls directly or
Meta Data indirectly into a vendor-specific database protocol
Scrollable ResultSets in JDBC 2.0
 Type 4: Database-Protocol driver (Pure Java driver) or thin driver
Modifying Databases via Java Methods
 Driver converts JDBC calls directly into a vendor-specific
database protocol

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 8

  Microsoft introduced its own solution to access databases that
JDBC have different internal formats: Open Database Connectivity
The Vendor Variation Problem (ODBC).

What is JDBC?  Oracle provides JDBC-ODBC bridge driver in package

sun.jdbc.odbc included in the Java Standard Edition
SQL and Versions of JDBC
 This driver converts the JDBC protocol into the corresponding
ODBC & JDBC-ODBC
ODBC one and allows Java programmers to access databases for
Simple Database Access which there are ODBC drivers.
Modifying the Database Contents

Transactions

Meta Data

Scrollable ResultSets in JDBC 2.0

Modifying Databases via Java Methods

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 9

Creating an ODBC Data Source
 Before an ODBC-driven database can be accessed via a Java program, it is necessary to
register the database as an ODBC Data Source.
 Database can then be referred to by its Data Source Name (DSN).
 Assuming that the database has already been created, the steps required to set up your own
ODBC Data Source is.

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 10

JDBC  Each JDBC driver must implement Connection, Statement,
The Vendor Variation Problem ResultSet, ResultSetMetaData, DatabaseMetaData interfaces
What is JDBC?  Implementing classes are then used to create objects of
SQL and Versions of JDBC Connection, Statement, ResultSet, ResultSetMetaData,
DatabaseMetaData objects respectively for concerned DB
ODBC & JDBC-ODBC

Simple Database Access

 Steps to access a database
1. Establish a connection to the database.
Modifying the Database Contents
2. Use the connection to create a Statement object and store a
Transactions reference to this object.
Meta Data 3. Use the Statement reference to run a specific query or update
Scrollable ResultSets in JDBC 2.0 statement and accept the result(s).

Modifying Databases via Java Methods

4. Manipulate and display the results (if a query) or check/show
number of database rows affected (for an update).
5. Repeat steps 3 and 4 as many times as required for further
queries/updates.
6. Close the connection.

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 11

 1. Establish a Connection to the Database
 Every database is identified by a URL

 Given a URL, DriverManager looks for the driver that can talk to the corresponding database

 A call to static method getConnection() of DriverManager class returns a Connection object

 getConnection() takes three String arguments:

 a URL-style address for the database
 a username
 a password
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWD);

 Format of database address/URL as per JDBC API specification: jdbc:<subprotocol>:<data-source>

 <sub-protocol>: specifies a database connection service (i.e., a driver: odbc, mysql, oracle )
 <data-source> : database locator (name/path)

 Ex.: jdbc:odbc:Finances, jdbc:mysql://localhost:3306/studentDB,

jdbc:mysql://192.168.2.1:3306/myDB

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 12

2. Create a Statement Object
 Statement object is created by calling the createStatement() method of Connection object
Statement stmt = connection.createStatement();

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 13

SQL commands & its classification

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 14

 3. Run a Query or Update and Accept the Result(s)
 SQL DQL (Data Query Language) retrieve data from a database (i.e., SELECT statements)
 executeQuery() method of Statement class executes DQL
 returns a ResultSet object

 SQL DML (Data Manipulation Language) statements change the contents of the database (viz., INSERT, DELETE and UPDATE
statements).
 executeUpdate() method of Statement class executes DML
 returns an integer indicating the number of database rows affected by the updating operation.

ResultSet rset = stmt.executeQuery(sql_statement);

int count = stmt.executeUpdate(sql_statement);

 Ex.:

ResultSet results = stmt.executeQuery("SELECT * FROM Accounts");

ResultSet results = stmt.executeQuery("SELECT * FROM Accounts WHERE balance < 1000");

int count = stmt.executeUpdate(“DELETE FROM Accounts WHERE accno=12345”)

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 15

4. Manipulate/Display/Check Result(s)
 ResultSet object contains database rows that satisfy the query’s search criteria.
 ResultSet uses a virtual cursor to point to a row of a virtual table.
 ResultSet interface contains methods for manipulating these rows
 boolean next()
 First call to next() activates the first row
 moves the ResultSet cursor/pointer to the next row of the virtual table
 returns false if there are no more rows
 void close()
 disposes of the ResultSet
 allows you to re-use the Statement that created it
 automatically called by most Statement methods

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 16

4. Manipulate/Display/Check Result(s)
 Type getType(int columnIndex)
 retrieve data via field position
 returns the given field as the given type
 indices start at 1
 Sample syntax: String getString(int columnIndex)
 Ex.: getInt(2), getString(1)
 Type getType(String columnName)
 retrieve data via field name
 Sample syntax: int getInt(String columnName), boolean getBoolean(String columnName)
 Ex.: getInt(“id”), getString(“address”)
 int findColumn(String columnName)
 looks up column index given column name

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 17

6. Close the Connection
 A connection is closed by calling close() method of Connection object
connection.close();
 Statement objects are closed via close() method of Statement object.
statement.close();

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 18

import java.sql.*; try { //Step 4
System.out.println();
public class JDBCSelect { while (results.next())
public static void main(String[] args) { {
Connection connection = null; System.out.println("Account no. " + results.getInt(1));
Statement statement = null; System.out.println("Account holder: " + results.getString(3) +
ResultSet results = null; " " + results.getString(2));
try { //Step 1 System.out.printf("Balance: %.2f \n“, results.getFloat(4));
connection = DriverManager.getConnection }
("jdbc:odbc:Finances", "", ""); }
} catch (SQLException sqlEx) {
catch (SQLException sqlEx) { System.out.println("Error retrieving data!" + sqlEx);
System.out.println("Cannot connect to database!"); System.exit(1);
System.exit(1); }
}
try { //Step 6…
try { //Step 2 connection.close();
statement = connection.createStatement(); }
String select = "SELECT * FROM Accounts"; catch (SQLException sqlEx) {
//Step 3 System.out.println("Unable to disconnect!" + sqlEx);
results = statement.executeQuery(select); System.exit(1);
} }
catch (SQLException sqlEx) { }
System.out.println("Cannot execute query!" + sqlEx); }
System.exit(1);
}

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 19

JDBC  Here we look at INSERT, DELETE and UPDATE statements of DML and use
The Vendor Variation Problem executeUpdate() method of Statement object to execute it
What is JDBC?  Ex.:
SQL and Versions of JDBC String query = "INSERT INTO Accounts VALUES (123456, 'Smith', 'John James’, 752.85)"
ODBC & JDBC-ODBC int result = stmt.executeUpdate(query);
Simple Database Access

Modifying the Database Contents String query = "UPDATE Accounts SET surname = 'Bloggs’, firstNames = 'Fred Joseph’
WHERE acctNum = 123456";
Transactions
int result = stmt.executeUpdate(query);
Meta Data

Scrollable ResultSets in JDBC 2.0

String query = "DELETE FROM Accounts WHERE balance < 100";
Modifying Databases via Java Methods
int result = stmt.executeUpdate(query);

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 20

import java.sql.*; String remove = "DELETE FROM Accounts WHERE balance < 100";
public class JDBCChange { result = statement.executeUpdate(remove);
private static Statement statement; if (result == 0) {
private static ResultSet results; System.out.println("\nUnable to delete record!");
}
public static void main(String[] args) {
System.out.println("\nNew contents of table:");
Connection connection = null;
displayTable();
try { //Step 1
connection = DriverManager.getConnection("jdbc:odbc:Finances", "", ""); //End of step 5.
} //Step 6…
catch (SQLException cnfEx) { connection.close();
System.out.println("Cannot connect to database!"); }
System.exit(1); catch (SQLException sqlEx) {
} System.out.println("SQL or connection error!");
sqlEx.printStackTrace();
try { //Step 2… System.exit(1);
statement = connection.createStatement(); }
System.out.println("\nInitial contents of table:"); }
//Steps 3 and 4
displayTable();
public static void displayTable() throws SQLException {
//Start of step 5
String select = "SELECT * FROM Accounts";
String insert = "INSERT INTO Accounts VALUES (123456,'Smith',"
+ "'John James',752.85)"; results = statement.executeQuery(select);
int result = statement.executeUpdate(insert); System.out.println();
if (result == 0) { while (results.next()) {
System.out.println("\nUnable to insert record!"); System.out.println("Account no. “ + results.getInt(1));
} System.out.println("Account holder: “ + results.getString(3)
String change = "UPDATE Accounts SET surname = 'Bloggs’, firstNames='Fred + " " + results.getString(2));
Joseph' WHERE acctNum = 123456"; System.out.printf("Balance: %.2f \n\n", results.getFloat(4));
result = statement.executeUpdate(change); }
if (result == 0) { }
System.out.println("\nUnable to update record!"); }
}
JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 21
JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 22
  SQL statements used to implement transaction processing are COMMIT and
ROLLBACK
JDBC
 Java has Connection interface methods commit() and rollback()
The Vendor Variation Problem

What is JDBC?  commit is used at the end of a transaction to commit/finalize database changes

SQL and Versions of JDBC  rollback is used to restore the database to the state it was in prior to the current
transaction
ODBC & JDBC-ODBC

Simple Database Access

 JDBC by default automatically commits each individual SQL statement that is
applied to a database.
Modifying the Database Contents
 May be changed by first executing Connection method setAutoCommit() with an
Transactions
argument of false (to switch off auto-commit) before commit and rollback.
Meta Data

Scrollable ResultSets in JDBC 2.0

Modifying Databases via Java Methods

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 23

  Meta data is ‘data about data’.

JDBC  2 categories of meta data available through the JDBC API:

The Vendor Variation Problem  data about the rows and columns returned by a query (about ResultSet
objects)
What is JDBC?
 provided by interface ResultSetMetaData , an object of which is
SQL and Versions of JDBC
returned by the ResultSet method getMetaData .
ODBC & JDBC-ODBC  data about the database as a whole
Simple Database Access  provided by interface DatabaseMetaData, an object of which is
Modifying the Database Contents returned by the Connection method getMetaData
Transactions  Information available from a ResultSetMetaData object includes:
Meta Data  the number of fields/columns in a ResultSet object;
Scrollable ResultSets in JDBC 2.0  the name of a specified field;

Modifying Databases via Java Methods

 the data type of a field;
 the maximum width of a field;
 the table to which a field belongs.

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 24

ResultSetMetaData Methods
 int getColumnCount()
 String getColumnname(int colnumber)
 int getColumnType(int colnumber)
 String getColumnTypeName(<colNumber>)

 8 SQL types represented in class java.sql.Types are:

 DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, NUMERIC, REAL, VARCHAR

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 25

  Able to move about in the virtual table.
JDBC  Supporting Methods in ResultSet
The Vendor Variation Problem  first(), last(), previous(), absolute(), relative(), getRow().
What is JDBC?  Statement object must be set up with this option.
SQL and Versions of JDBC  TYPE_FORWARD_ONLY
ODBC & JDBC-ODBC  TYPE_SCROLL_INSENSITIVE
Simple Database Access  TYPE_SCROLL_SENSITIVE
Modifying the Database Contents
…
Transactions
Statement stmt =
Meta Data connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE);
Scrollable ResultSets in JDBC 2.0
String query = “SELECT students FROM class WHERE type=‘not sleeping’ “;
Modifying Databases via Java Methods
ResultSet rs = stmt.executeQuery( query );

rs.previous(); / / go back in the RS

rs.relative(-5); / / go 5 records back
rs.relative(7); / / go 7 records forward
rs.absolute(100); / / go to 100th record
…
JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 26
ResultSet Modifiers
 Retrieved ResultSet can be modified.

 Pass as 3rd parameter to createStatement() method

 CONCUR_READ_ONLY(used as ResultSet.CONCUR_READ_ONLY)

 CONCUR_UPDATABLE (used as ResultSet.CONCUR_UPDATABLE)

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 27

ResultSet Modifiers
…
Statement stmt =
connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);

String query = “SELECT * FROM Accounts “;

ResultSet rs = stmt.executeQuery( query );
…
while ( rs.next() )
{
showRow();
}

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 28

JDBC  3 Ways to update
 Update values
The Vendor Variation Problem
 Inserting new rows.
What is JDBC?
 Delete rows
SQL and Versions of JDBC

ODBC & JDBC-ODBC

 updateXxx() method is used to change value of column in current row
of RS.
Simple Database Access
 updateRow() method: to update the RS with the changes done by
Modifying the Database Contents
updateXxx()
Transactions
 updateNull() to replace value with null value.
Meta Data

Scrollable ResultSets in JDBC 2.0

Modifying Databases via Java

Methods

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 29

…
Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
String query = “SELECT * FROM Accounts “;
ResultSet rs = stmt.executeQuery( query );
…

rs.absolute(2)
rs.updateInt(“acctNum”, 2547);
rs.updateRow();
…
rs.moveToInsertRow();
rs.updateInt("acctNum", 999999);
rs.updateString("surname", "Harrison");
rs.updateString("firstNames", "Christine Dawn");
rs.updateFloat("balance", 2500f);
rs.insertRow();
…
results.absolute(3); //Move to row 3.
results.deleteRow();
JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 30
End of JDBC

JDBC GANESH PAI, DEPT. OF CSE, NMAMIT, NITTE 31