JDBC –
Java DataBase Connectivity
What is JDBC?
“An API that lets you access virtually any tabular data
source from the Java programming language”
JDBC Data Access API – JDBC Technology Homepage
What’s an API?
See J2SE documentation
What’s a tabular data source?
“… access virtually any data source, from relational
databases to spreadsheets and flat files.”
JDBC Documentation
We’ll focus on accessing Oracle/MySQL databases
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
Database Drivers
Think of a database as just another device
connected to your computer
like other devices it has a driver program to
relieves you of having to do low level
programming to use the database
the driver provides you with a high level api to
the database
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
ODBC
Open Data Base Connectivity
Developed by Microsoft for the Windows
platform as the way for Windows applications
to access Microsoft databases (SQL Server,
FoxPro, Access)
Has become an industry standard
Most data base vendors supply native, odbc,
and jdbc drivers for their data base products
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
General Architecture
What design pattern is
implied in this
architecture?
What does it buy for us?
Why is this architecture
also multi-tiered?
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
JDBC Architecture
Java Application
JDBC API
Data Base Drivers
Access SQL DB2 Informix MySQL Sybase
Server
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
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
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
https://downloads.mysql.com/archives/installer/
https://visualstudio.microsoft.com/downloads/
https://www.javaguides.net/2019/07/login-application-using-
java-swing-jdbc-mysql-example-tutorial.html
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-
usagenotes-connect-drivermanager.html
https://www.javaguides.net/2019/07/login-application-using-
java-swing-jdbc-mysql-example-tutorial.html
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
1. Establish a connection
import java.sql.*;
Load the vendor specific driver
Class.forName("oracle.jdbc.driver.OracleDriver");
What do you think this statement does, and how?
Dynamically loads a driver class, for Oracle database
Make the connection
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@oracle-prod:1521:OPROD",
username, passwd);
What do you think this statement does?
Establishes connection to database by obtaining
a Connection object
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
2. Create JDBC statement(s)
Statement stmt = con.createStatement() ;
Creates a Statement object for sending SQL statements
to the database
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
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);
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
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");
Prof. Anand Motwani, Faculty - SCSE,
} VIT Bhopal University
Close connection
stmt.close();
con.close();
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
Transactions and JDBC
JDBC allows SQL statements to be grouped together into a
single transaction
Transaction control is performed by the Connection object,
default mode is auto-commit, I.e., each sql statement is treated
as a transaction
We can turn off the auto-commit mode with
con.setAutoCommit(false);
And turn it back on with con.setAutoCommit(true);
Once auto-commit is off, no SQL statement will be committed
until an explicit is invoked con.commit();
At this point all changes done by the SQL statements will be
made permanent in the database.
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
Handling Errors with
Exceptions
Programs should recover and leave the database in
a consistent state.
If a statement in the try block throws an exception or
warning, it can be caught in one of the
corresponding catch statements
How might a finally {…} block be helpful here?
E.g., you could rollback your transaction in a
catch { …} block or close database connection and
free database related resources in finally {…} block
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
Another way to access database
(JDBC-ODBC)
What’s a bit different
about this
architecture?
Why add yet
another layer?
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
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");
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
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); }
}
}
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
Experiment steps performed
Create new Java project in eclipse.
Create new class (.java) in src folder within new project.
Create a new folder (libs) in project created in 1.
Add all jar files from ucanaccess binary folder to libs folder
created in step-3 (downloaded from:
https://sourceforge.net/projects/ucanaccess/
Now create a MS-Access Database with 2-3 columns (attributes).
We have created a DB StudentInformation.
Then a table tbl_Student with attributes: ID (PK), s_id and s_branch
Applied proper Exception handling.
To write the program, follow the 5 steps given in slide-9.
Finally the source code is provided (MyFirstSQL.java)
Prof. Anand Motwani, Faculty - SCSE,
VIT Bhopal University
