EE361 Object Oriented

Programming
JDBC
JDBC
 JDBC stands for Java Database Connectivity
 JDBC is a standard Java API for providing
connectivity between the Java programming language, and
a wide range of database
 It provides a standard library for Java programs to
connect to a database
 It generalizes common database access functions
into a set of common classes and methods
 Abstracts vendor specific details into a code library
making the connectivity to multiple databases
transparent to user
JDBC
 JDBC API Standardizes:
 Way to establish connection to database
 Approach to initiating queries
 Method to create stored procedures
 Data structure of the query result
Java and the database

 Database is used to store data. It is also known as

persistent storage as the data is stored and can be
retrieved anytime.
 Java and database are used almost everywhere to store
persistent data and retrieve it when required.
JDBC ARCHITECTURE
JDBC ARCHITECTURE
 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
Common JDBC Components

 DriverManager: This class manages a list of database drivers. Matches

connection requests from the java application with the proper database
driver using communication subprotocol.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.,
 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: The objects created from this interface is used to submit the
SQL statements to the database.
 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.

JDBC Drivers
 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
1. Type I
2. Type II
3. Type III
4. Type IV
 JDBC
Drivers (Type I)
 Type I Type 1 only runs on platforms where ODBC is available. ODBC must be
configured separately
 A common Type I driver defines a JDBC to ODBC bridge
 ODBC is the database connectivity for databases
 JDBC driver translates JDBC calls to corresponding ODBC calls
 Thus if ODBC driver exists for a database this bridge can be used to communicate
with the database from a Java application
 Inefficient and narrow solution
 Inefficient, because it goes through multiple layers
 Narrow, since functionality of JDBC code limited to whatever ODBC supports
Driver (Type I)

ODBC API
Native API
Client
Application API Protocol Database Specific Database
Protocol

9 11/8/2019
 JDBC
Drivers (Type II)
 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
Driver (Type II)

Native API
Client
Application Database
API Protocol Database Specific
Protocol

10 11/8/2019
 JDBC
Drivers (Type III)
 Type III driver make calls to a middleware component running on
another server
 This communication uses a database independent net protocol
 Middleware server then makes calls to the database using database-
specific protocol
 The program sends JDBC call through the JDBC driver to the middle
tier
 Middle-tier may use Type I or II JDBC driver to communicate with the
database.
Driver (Type III)

Client Middleware
Application Net Protocol Server Database Specific Database
Protocol

11 11/8/2019
 JDBC
Drivers (Type IV)
 Type IV driver is an all-Java driver that is also called a thin
driver
 It issues requests directly to the database using its native protocol
 It can be used directly on platform with a JVM
 Most efficient since requests only go through one layer
 Simplest to deploy since no additional libraries or middle-ware
Driver (Type IV)

Client
Application Database Specific Database
Protocol

12 11/8/2019
Structured Query Language (SQL)

 Standard language for querying and manipulating data

 Structured Query Language

 Information stored in the database is in tables and the
language used to query information from the database is
SQL.
 Using SQL we can query a table based on the
requirement.
 Table name Attribute names
Tables in SQL

Product

PName Price Category Manufacturer

Gizmo $19.99 Gadgets GizmoWorks

Powergizmo $29.99 Gadgets GizmoWorks

SingleTouch $149.99 Photography Canon

MultiTouch $203.99 Household Hitachi

Tuples or rows
Introduction to SQL
 SQL functions fit into two broad categories:
 Data definition language
 SQL includes commands to:
 Create database objects, such as tables, indexes, and views
 Define access rights to those database objects
 Data manipulation language
 Includes commands to insert, update, delete, and retrieve data within
database tables

15
CRUD OPERATIONS
• CRUD stands for create, read, update, delete.
• Create statement in SQL looks like
– Create table mytab ( mynum number , name varchar2(25));
• READ statement looks like
– Select * from mytab where mynum=25;
• UPDATE statement looks as
– Update mytab set mynum=88 where mynum=25;
• DELETE statement like
– Delete from mytab where mynum=88;
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  The following SQL statement

used for creating a new table. The creates a table named
syntax is: Employees with four columns:
 SQL> CREATE TABLE
 SQL> CREATE TABLE Employees
table_name
( (
column_name id INT NOT NULL,
column_data_type, age INT NOT NULL,
column_name column_data_type, first VARCHAR(255),
column_name column_data_type
last VARCHAR(255),
... PRIMARY KEY ( id )
); );
Drop Table

 The DROP TABLE statement is used for deleting an

existing table. The syntax is:
 SQL> DROP TABLE table_name;
 Example
The following SQL statement deletes a table named
Employees:
 SQL> DROP TABLE Employees;
INSERT
 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, ...);
 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;
 The WHERE clause can use the comparison operators such as
=, !=, <, >, <=,and >=, as well as the BETWEEN and LIKE
operators.
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;
UPDATE
 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;
CRUD and Java
 Java can invoke CRUD operations using JDBC
 JDBC is Java Database Connectivity and there are 4 types
of drivers which form a bridge between java and a
database
 The Operations communicated by java will be translated
in a form understood by the database by the drivers.
JDBC Drivers
• Type 1 Driver - JDBC-ODBC bridge
– This is an ODBC driver, which is open source
• Type 2 Driver – Native API driver
– This is more like the OCI (Oracle Call Interface) call interface
is converted to native calls of the database.
• Type 3 Driver – Network protocol driver
– This is achieved by using a Java Driver in a middleware
• Type 4 Driver – Native Protocol Driver
– This is a driver written purely in Java Language
• We Usually prefer to depend on the Type 4
driver
– Eg: Oracle thin driver
JDBC- STEPS involved in connecting to
data base
 Import the JDBC class (java.sql.*)
 Load the JDBC drivers
 Connect to the database
 Interact with the database using JDBC
 Disconnect with the database
Load the JDBC drivers
 Class.forName("oracle.jdbc.driver.OracleDriver").newInst
ance();
Connect to the database
 Connection Conn =
DriverManager.getConnection(url,username,password);
 url is of the form:
 Jdbc:oracle:drivertype:@database
 Example: "jdbc:oracle:thin:@erg.csci.unt.edu:1521:ERG"
The Connection Object
 Statement
 prepareStatement
 CallableStatement
Using Statement Object
 Statement Stmt = Conn.createStatement();
 ResultSet RS = Stmt.executeQuery("SELECT * from
user_tables");
The ResultSet Class
 The resultset class provides access to a table of data
generated by executing a query.
 Methods:
 Next
 Close
 getString(int columnIndex)
ResultSet Metadata
 ResultSetMetData rsetmd = rs.getMetaData()
 Method:
 getColumnCount
 getColumnDisplaySize(int column)
 getColumnTypeName(int column)
Close
 RS.close();
 Stmt.close();
 Conn.close();
Basic steps to use
a database in Java
 1.Establish a connection
 2.Create JDBC Statements
 3.Execute SQL Statements
 4.GET ResultSet
 5.Close connections

33
1. Establish a connection

 import java.sql.*;
 Load the vendor specific driver
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Dynamically loads a driver class, for Oracle database
 Make the connection
 Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@oracle-prod:1521:OPROD",
username, passwd);
 Establishes connection to database by obtaining
a Connection object

34
2. Create JDBC statement(s)
 Statement stmt = con.createStatement() ;
 Creates a Statement object for sending SQL statements to the
database

35
Executing SQL Statements
 String createLehigh = "Create table Lehigh " +
"(SSN Integer not null, Name VARCHAR(32), " +
"Marks Integer)";
stmt.executeUpdate(createLehigh);
//What does this statement do?

 String insertLehigh = "Insert into Lehigh values“

+ "(123456789,abc,100)";
stmt.executeUpdate(insertLehigh);

36
Get ResultSet
String queryLehigh = "select * from Lehigh";

ResultSet rs = Stmt.executeQuery(queryLehigh);
//What does this statement do?

while (rs.next()) {
int ssn = rs.getInt("SSN");
String name = rs.getString("NAME");
int marks = rs.getInt("MARKS");
}
37
Close connection
 stmt.close();
 con.close();

38
Sample program

import java.sql.*;
class Test {
public static void main(String[] args) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //dynamic loading of driver
String filename = "c:/db1.mdb"; //Location of an Access database
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= filename.trim() + ";DriverID=22;READONLY=true}"; //add on to end
Connection con = DriverManager.getConnection( database ,"","");
Statement s = con.createStatement();
s.execute("create table TEST12345 ( firstcolumn integer )");
s.execute("insert into TEST12345 values(1)");
s.execute("select firstcolumn from TEST12345");

39
Sample program(cont)
ResultSet rs = s.getResultSet();
if (rs != null) // if rs == null, then there is no ResultSet to view
while ( rs.next() ) // this will step through our data row-by-row
{ /* the next line will get the first column in our current row's ResultSet
as a String ( getString( columnNumber) ) and output it to the screen */
System.out.println("Data from column_name: " + rs.getString(1) );
}
s.close(); // close Statement to let the database know we're done with it
con.close(); //close connection
}
catch (Exception err) { System.out.println("ERROR: " + err); }
}
}

40