JAVA PROGRAMMING

Week 8: JDBC
Lecturer: NGUYỄN Thị Minh Tuyền
JDBC 2
• JDBC = Java DataBase Connectivity
• JDBC library includes APIs for the following tasks:
• Connect to databases
• Create SQL or MySQL statements
• Execute SQL or MySQL queries in the database.
• View and modify the resulting records.
• Database access is the same for all database vendors
• The JVM uses a JDBC driver to translate generalized
JDBC calls into vendor specific database calls.
• There are four general types of JDBC drivers

Java Programming
Implementations of JDBC drivers [1] 3
• Type 1: Drivers that implement the JDBC API as a
mapping to another data access API, such as ODBC
(Open Database Connectivity).
• Drivers of this type are generally dependent on a native library,
which limits their portability.
• The JDBC-ODBC Bridge is an example of a Type 1 driver.
• Type 2: Drivers that are written partly in the Java
programming language and partly in native code.
• These drivers use a native client library specific to the data
source to which they connect.
• Because of the native code, their portability is limited.
• Oracle's OCI (Oracle Call Interface) client-side driver is an
example of a Type 2 driver.

Java Programming
Implementations of JDBC drivers [2] 4
• Type 3: Drivers that use a pure Java client and
communicate with a middleware server using a database-
independent protocol.
• The middleware server then communicates the client's requests
to the data source.
• Type 4: Drivers that are pure Java and implement the
network protocol for a specific data source.
• The client connects directly to the data source.
• Java DB comes with two Type 4 drivers, an Embedded
driver and a Network Client Driver.
• MySQL Connector/J is a Type 4 driver.

Java Programming
Pure Java Driver (Type 4) 5
• Converts the JDBC API calls to direct network calls using
vendor-specific networking protocols by making direct
socket connections with the database
• The most efficient method to access database
• Both in performance and development time
• Simplest to deploy
• All major database vendors provide pure Java JDBC
drivers for their databases and they are also available
from third party vendors

Java Programming
 Java
Application 6

JDBC API

JDBC Driver
Manager

JDBC Driver JDBC Driver JDBC Driver JDBC Driver

ODBC
SQL
Oracle Data ...
Server
Source
Java Programming
Common JDBC Components [1] 7
• DriverManager
• Manages a list of database drivers.
• Matches connection requests from the Java application with the
proper database driver using communication sub protocol.
• Driver
• Handles the communications with the database server.
• Connection
• Establish the connection with a database.

Java Programming
Common JDBC Components [2] 8
• Statement
• You use objects created from this interface 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.

Java Programming
Setup JDBC environment 9
• Install Database
• Install Database Drivers
• Set Database Credential

Java Programming
Typical JDBC Programming Procedure 10
1. Load the database driver
2. Obtain a connection
3. Create and execute statements (SQL queries)
4. Use result sets (tables) to navigate through the results
5. Close the connection

Java Programming
Register JDBC Driver 11
• Class.forName()

• DriverManager.registerDriver()

Java Programming
 Example 1 12
1. try{
2. Class.forName("com.mysql.cj.jdbc.Driver");
3. }catch(ClassNotFoundException ex){
4. System.out.println("Error: unable to load driver class.");
5. System.exit(1);
6. }

Java Programming
 Example 2 13
1. try {
2. Driver myDriver = new com.mysql.cj.jdbc.Driver();
3. DriverManager.registerDriver(myDriver);
4. } catch (SQLException ex) {
5. System.out.println("Error: Unable to load driver class.");
6. System.exit(1);
7. }

Java Programming
Database URL Formulation 14
• To establish a connection:

getConnection(String url)

getConnection(String url, Properties prop)

getConnection(String url, String user,

String password)
• To close a connection:

close();

Java Programming
 15
DBMS JDBC driver name URL format
MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/
databaseName
ORACLE oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@hostname:
port Number:databaseName
DB2 com.ibm.db2.jcc.DB2Driver jdbc:db2://hostname:port
Number/databaseName
SQL Server com.microsoft.sqlserver.jdbc.SQL jdbc:sqlserver://hostname:port
ServerDriver Number/databaseName

Java Programming
 Using a Database URL with a
16
username and password
1. // Database URL
2. static final String DB_URL = "jdbc:mysql://localhost/";
3. // Database credentials
4. static final String USER = "username";
5. static final String PASS = "password";
6.

7. // Open a connection
8. Connection conn = DriverManager.getConnection(DB_URL,
9. USER, PASS);

Java Programming
 Using Only a Database URL 17
1. static final String DB_URL =
2. "jdbc:mysql://localhost?user=username&password=password";
3.

4. Connection conn = DriverManager.getConnection(DB_URL);

Java Programming
 Using a Database URL and a
18
Properties Object
1. static final String DB_URL = "jdbc:mysql://localhost/";
2.

3. Properties info = new Properties();

4. info.put("user", ”username");
5. info.put("password", ”password");
6.

Connection conn = DriverManager.getConnection(DB_URL, info);

Java Programming
 Interfaces 19
Interfaces Recommended Use

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.
PreparedStatement Use this when you plan to use the SQL statements
many times. The PreparedStatement interface
accepts input parameters at runtime.
CallableStatement Use this when you want to access the database
stored procedures. The CallableStatement
interface can also accept runtime input
parameters.

Java Programming
 Statement Objects 20
1. Statement stmt = null;
2. try {
3. stmt = conn.createStatement( );
4. //. . .
5. }
6. catch (SQLException e) {
7. //. . .
8. }
9. finally {
10. stmt.close();
11. }

Java Programming
Methods 21
• boolean execute (String SQL)
• Returns a boolean value of true if a ResultSet object can be
retrieved; otherwise, it returns false.
• Use this method to execute SQL DDL statements or when you
need to use truly dynamic SQL.
• int executeUpdate (String SQL)
• Returns the number of rows affected by the execution of the SQL
statement.
• Use this method to execute SQL statements for which you expect
to get a number of rows affected - for example, an INSERT,
UPDATE, or DELETE statement.
• ResultSet executeQuery (String SQL)
• Returns a ResultSet object.
• Use this method when you expect to get a result set, as you
would with a SELECT statement.
Java Programming
PreparedStatement 22
• PreparedStatement interface extends the Statement
interface
• Provides added functionality with a couple of advantages over a
generic Statement object.

Java Programming
 PreparedStatement Objects 23
1. PreparedStatement pstmt = null;
2. try {
3. String SQL = "Update Employees SET age = ? WHERE id = ?";
4. pstmt = conn.prepareStatement(SQL);
5. // . . .
6. } catch (SQLException e) {
7. // . . .
8. } finally {
9. pstmt.close();
10. }
11.

12.

Java Programming
 Example 24
1. System.out.println("Creating statement...");
2. String sql = "UPDATE Employees set age=? WHERE id=?";
3. PreparedStatement stmt = conn.prepareStatement(sql);
4.

5. //Bind values into the parameters.

6. stmt.setInt(1, 35); // This would set age
7. stmt.setInt(2, 102); // This would set ID
8.

9. // Let us update age of the record with ID = 102;

10. int rows = stmt.executeUpdate();
11. System.out.println("Rows impacted : " + rows );

Java Programming
ResultSet 25
• SQL statements that read data from a database query,
return the data in a result set.
• The SELECT statement is the standard way to select rows
from a database and view them in a result set.
• In Java: java.sql.ResultSet interface represents the
result set of a database query.
• A ResultSet object maintains a cursor that points to the
current row in the result set.
• The term "result set" refers to the row and column data contained
in a ResultSet object.

Java Programming
Three categories of ResultSet
methodes 26
• Navigational methods:
• Used to move the cursor around.
• Get methods:
• Used to view the data in the columns of the current row being
pointed by the cursor.
• Update methods:
• Used to update the data in the columns of the current row. The
updates can then be updated in the underlying database as well.
• The cursor is movable based on the properties of the
ResultSet.
• These properties are designated when the corresponding
Statement that generates the ResultSet is created.

Java Programming
ResultSet methodes 27
• These are connection methods to create statements with
desired ResultSet:
createStatement(int RSType, int
RSConcurrency);

prepareStatement(String SQL, int RSType,

int RSConcurrency);

prepareCall(String sql, int RSType, int

RSConcurrency);

• RSType: the type of a ResultSet object and

• RSConcurrency: one of two ResultSet constants for
specifying whether a result set is read-only or updatable.
Java Programming
 Types of ResultSet 28
Type Description
ResultSet.TYPE_FORWA The cursor can only move forward in the result
RD_ONLY set.
ResultSet.TYPE_SCROL The cursor can scroll forward and backward,
L_INSENSITIVE and the result set is not sensitive to changes
made by others to the database that occur after
the result set was created.
ResultSet.TYPE_SCROL The cursor can scroll forward and backward,
L_SENSITIVE and the result set is sensitive to changes made
by others to the database that occur after the
result set was created.

Java Programming
Concurrency of ResultSet 29
Concurrency Description
ResultSet.CONCUR_READ_ONLY Creates a read-only result set. This is
the default
ResultSet.CONCUR_UPDATABLE Creates an updateable result set.

Java Programming
 30
1. try {
2. Statement stmt = conn.createStatement(
3. ResultSet.TYPE_FORWARD_ONLY,
4. ResultSet.CONCUR_READ_ONLY);
5. }catch(Exception ex) {
6. // ....
7. }finally {
8. // ....
9. }

Java Programming
Navigating a Result Set [1] 31
• public void beforeFirst() throws SQLException
• Moves the cursor just before the first row.
• public void afterLast() throws SQLException
• Moves the cursor just after the last row.
• public boolean first() throws SQLException
• Moves the cursor to the first row.
• public void last() throws SQLException
• Moves the cursor to the last row.
• public boolean absolute(int row) throws SQLException
• Moves the cursor to the specified row.
• public boolean relative(int row) throws SQLException
• Moves the cursor the given number of rows forward or backward, from
where it is currently pointing.

Java Programming
Navigating a Result Set [2] 32
• public boolean previous() throws SQLException
• Moves the cursor to the previous row.
• This method returns false if the previous row is off the result set.
• public boolean next() throws SQLException
• Moves the cursor to the next row.
• This method returns false if there are no more rows in the result set.
• public int getRow() throws SQLException
• Returns the row number that the cursor is pointing to.
• public void moveToInsertRow() throws SQLException
• Moves the cursor to a special row in the result set that can be used to insert a
new row into the database.
• The current cursor location is remembered.
• public void moveToCurrentRow() throws SQLException
• Moves the cursor back to the current row if the cursor is currently at the insert
row; otherwise, this method does nothing

Java Programming
Data type 33
• JDBC driver converts the Java data type to the
appropriate JDBC type, before sending it to the database.
• It uses a default mapping for most data types.
• Example: a Java int is converted to an SQL INTEGER.
• Default mappings were created to provide consistency
between drivers.

Java Programming
 Java types to JDBC data types [1] 34
SQL JDBC/Java setXXX updateXXX
VARCHAR java.lang.String setString updateString
CHAR java.lang.String setString updateString
LONGVARCHAR java.lang.String setString updateString
BIT boolean setBoolean updateBoolean
NUMERIC java.math.BigDecimal setBigDecimal updateBigDecimal
TINYINT byte setByte updateByte
SMALLINT short setShort updateShort
INTEGER int setInt updateInt
BIGINT long setLong updateLong
REAL float setFloat updateFloat
FLOAT float setFloat updateFloat
Java Programming
Java types to JDBC data types [1] 35
SQL JDBC/Java setXXX updateXXX
DOUBLE double setDouble updateDouble
VARBINARY byte[ ] setBytes updateBytes
BINARY byte[ ] setBytes updateBytes
DATE java.sql.Date setDate updateDate
TIME java.sql.Time setTime updateTime
TIMESTAMP java.sql.Timestamp setTimestamp updateTimestamp
CLOB java.sql.Clob setClob updateClob
BLOB java.sql.Blob setBlob updateBlob
ARRAY java.sql.Array setARRAY updateARRAY
REF java.sql.Ref SetRef updateRef
STRUCT java.sql.Struct SetStruct updateStruct
Java Programming
Retrieve column value [1] 36
SQL JDBC/Java setXXX getXXX
VARCHAR java.lang.String setString getString
CHAR java.lang.String setString getString
LONGVARCHAR java.lang.String setString getString
BIT boolean setBoolean getBoolean
NUMERIC java.math.BigDecimal setBigDecimal getBigDecimal
TINYINT byte setByte getByte
SMALLINT short setShort getShort
INTEGER int setInt getInt
BIGINT long setLong getLong
REAL float setFloat getFloat
FLOAT float setFloat getFloat
Java Programming
Retrieve column value [2] 37
SQL JDBC/Java setXXX getXXX
DOUBLE double setDouble getDouble
VARBINARY byte[ ] setBytes getBytes
BINARY byte[ ] setBytes getBytes
DATE java.sql.Date setDate getDate
TIME java.sql.Time setTime getTime
TIMESTAMP java.sql.Timestamp setTimestamp getTimestamp
CLOB java.sql.Clob setClob getClob
BLOB java.sql.Blob setBlob getBlob
ARRAY java.sql.Array setARRAY getARRAY
REF java.sql.Ref SetRef getRef
STRUCT java.sql.Struct SetStruct getStruct
Java Programming
Date and Time Data Types 38
• java.sql.Date class maps to the SQL DATE type,
• java.sql.Time class maps to the SQL TIME data type.
• java.sql.Timestamp class maps to the SQL
TIMESTAMP data type.

Java Programming
 Example [1] 39
1. public class SqlDateTime {
2. public static void main(String[] args) {
3. // Get standard date and time
4. java.util.Date javaDate = new java.util.Date();
5. long javaTime = javaDate.getTime();
6. System.out.println("The Java Date is:" +
7. javaDate.toString());
8. // Get and display SQL DATE
9. java.sql.Date sqlDate = new java.sql.Date(javaTime);
10. System.out.println("The SQL DATE is: " +
11. sqlDate.toString());

Java Programming
 Example [2] 40
1. // Get and display SQL TIME
2. java.sql.Time sqlTime = new java.sql.Time(javaTime);
3. System.out.println("The SQL TIME is: " +
4. sqlTime.toString());
5. // Get and display SQL TIMESTAMP
6. java.sql.Timestamp sqlTimestamp = new
7. java.sql.Timestamp(javaTime);
8. System.out.println("The SQL TIMESTAMP is: " +
9. sqlTimestamp.toString());
10. }// end main
11. }// end SqlDateTime

Java Programming
Handling NULL values 41
• SQL's use of NULL values and Java's use of null are
different concepts à to handle SQL NULL values in Java:
• Avoid using getXXX( ) methods that return primitive data types.
• Use wrapper classes for primitive data types, and use the
ResultSet object's wasNull() method to test whether the
wrapper class variable that received the value returned by the
getXXX() method should be set to null.
• Use primitive data types and the ResultSet object's
wasNull() method to test whether the primitive variable that
received the value returned by the getXXX( ) method should be
set to an acceptable value that you've chosen to represent a
NULL.

Java Programming
 Handling NULL Values 42
1. Statement stmt = conn.createStatement( );
2. String sql = "SELECT id, first, last, age FROM Employees";
3. ResultSet rs = stmt.executeQuery(sql);
4.

5. int id = rs.getInt(1);
6. if(rs.wasNull()){
7. id = 0;
8. }

Java Programming
Transactions 43
• If your JDBC Connection is in auto-commit mode, which it
is by default, then every SQL statement is committed to
the database upon its completion.
• Three reasons to turn off the auto-commit and manage
your own transactions:
• To increase performance.
• To maintain the integrity of business processes.
• To use distributed transactions.
• To set or turn off auto-commit:
conn.setAutoCommit(boolean);

Java Programming
Commit & Rollback 44
• To commit the changes using Connection named conn:
conn.commit();
• To roll back updates to the database made using the
Connection named conn:
conn.rollback( );

Java Programming
1. try{
2. //Assume a valid connection object conn 45
3. conn.setAutoCommit(false);
4. Statement stmt = conn.createStatement();
5. String SQL = "INSERT INTO Employees " +
6. "VALUES (106, 20, 'Rita', 'Tez')";
7. stmt.executeUpdate(SQL);
8. //Submit a malformed SQL statement that breaks
9. String SQL = "INSERTED IN Employees " +
10. "VALUES (107, 22, 'Sita', 'Singh')";
11. stmt.executeUpdate(SQL);
12. // If there is no error.
13. conn.commit();
14. }catch(SQLException se){
15. // If there is any error.
16. conn.rollback();
17. } Java Programming
 Exceptions Handling 46
Method Description
getErrorCode( ) Gets the error number associated with the exception.
getMessage( ) Gets the JDBC driver's error message for an error, handled by
the driver or gets the Oracle error number and message for a
database error.
getSQLState( ) Gets the XOPEN SQLstate string. For a JDBC driver error, no
useful information is returned from this method. For a
database error, the five-digit XOPEN SQLstate code is
returned. This method can return null.
getNextException( ) Gets the next Exception object in the exception chain.
printStackTrace( ) Prints the current exception, or throwable, and it's backtrace
to a standard error stream.
printStackTrace(PrintS Prints this throwable and its backtrace to the print stream you
tream s) specify.
printStackTrace(Print Prints this throwable and it's backtrace to the print writer you
Writer w) specify.
1. import java.sql.*;
2. public class JDBCExample { 47
3. // JDBC driver name and database URL
4. static final String JDBC_DRIVER =
5. "com.mysql.jdbc.Driver";
6. static final String DB_URL = "jdbc:mysql://localhost/EMP";
7. // Database credentials
8. static final String USER = "username";
9. static final String PASS = "password";
10. public static void main(String[] args) {
11. Connection conn = null;
12. try{
13. //STEP 2: Register JDBC driver
14. Class.forName("com.mysql.jdbc.Driver");
15. //STEP 3: Open a connection
16. System.out.println("Connecting to database...");
17. conn = DriverManager.getConnection(DB_URL,USER,PASS);
Java Programming
1. //STEP 4: Execute a query
2.
3.
System.out.println("Creating statement...");
Statement stmt = conn.createStatement();
48
4. String sql;
5. sql = "SELECT id, first, last, age FROM Employees";
6. ResultSet rs = stmt.executeQuery(sql);
7. //STEP 5: Extract data from result set
8. while(rs.next()){
9. //Retrieve by column name
10. int id = rs.getInt("id");
11. int age = rs.getInt("age");
12. String first = rs.getString("first");
13. String last = rs.getString("last");
14. //Display values
15. System.out.print("ID: " + id);
16. System.out.print(", Age: " + age);
17. System.out.print(", First: " + first);
18. System.out.println(", Last: " + last);
19. }
Java Programming
20.
1. //STEP 6: Clean-up environment
2. rs.close(); 49
3. stmt.close();
4. conn.close();
5. }catch(SQLException se){
6. //Handle errors for JDBC
7. se.printStackTrace();
8. }catch(Exception e){ //Handle errors for Class.forName
9. e.printStackTrace();
10. }finally{ //finally block used to close resources
11. try{ if(conn!=null) conn.close();
12. }catch(SQLException se){ se.printStackTrace();
13. }//end finally try
14. }//end try
15. System.out.println("Goodbye!");
16. }//end main
17. }//end JDBCExample
Java Programming
 Examples 50

Java Programming
 Example: Create a DB 51
1. public class JDBCCreateDB {
2. // JDBC driver name and database URL
3. static final String JDBC_DRIVER =
4. "com.mysql.cj.jdbc.Driver";
5. static final String DB_URL = "jdbc:mysql://localhost/";
6. // Database credentials
7. static final String USER = ”user";
8. static final String PASS = ”password";
9. public static void main(String[] args) {
10. Connection conn = null;
11. Statement stmt = null;

Java Programming
 52
1. try {
2. // STEP 2: Register JDBC driver
3. Class.forName(JDBC_DRIVER);
4. // STEP 3: Open a connection
5.
System.out.println("Connecting to database...");
6.
conn = DriverManager.getConnection(DB_URL, USER, PASS);
7.
// STEP 4: Execute a query
8.
System.out.println("Creating database...");
9.
stmt = conn.createStatement();
10.
String sql = "CREATE DATABASE STUDENTS";
11.
stmt.executeUpdate(sql);
12.
System.out.println("Database created successfully...");
13.
} catch (SQLException se) { // Handle errors for JDBC
se.printStackTrace();
Java Programming
 53
1. } catch (Exception e) {
2. e.printStackTrace();
3. } finally {
4. // finally block used to close resources
5. try { if (stmt != null) stmt.close();
6. } catch (SQLException se2) { }
7. try { if (conn != null) conn.close();
8. } catch (SQLException se) { se.printStackTrace();
9. } // end finally try
10. } // end try
11. System.out.println("Goodbye!");
12. }// end main
13. }
14. Java Programming
 Drop a DB 54
1. System.out.println("Deleting database...");
2. stmt = conn.createStatement();
3.

4. String sql = "DROP DATABASE STUDENTS";

5. stmt.executeUpdate(sql);

Java Programming
 Create a table 55
1. stmt = conn.createStatement();
2.

3. String sql = "CREATE TABLE REGISTRATION " +

4. "(id INTEGER not NULL, " +
5. " first VARCHAR(255), ” +
6. " last VARCHAR(255), " +
7. " age INTEGER, " +
8. " PRIMARY KEY ( id ))";
9.

10. stmt.executeUpdate(sql);

Java Programming
 Drop a table 56
1. stmt = conn.createStatement();
2. String sql = "DROP TABLE REGISTRATION ";
3. stmt.executeUpdate(sql);

Java Programming
 Insert a record 57
1. stmt = conn.createStatement();
2. String sql = "INSERT INTO Registration "
3. + "VALUES (100, 'Zara', 'Ali', 18)";
4. stmt.executeUpdate(sql);
5. sql = "INSERT INTO Registration "
6. + "VALUES (101, 'Mahnaz', 'Fatma', 25)";
7. stmt.executeUpdate(sql);
8. sql = "INSERT INTO Registration "
9. + "VALUES (102, 'Zaid', 'Khan', 30)";
10. stmt.executeUpdate(sql);
11. sql = "INSERT INTO Registration "
12. + "VALUES(103, 'Sumit', 'Mittal', 28)";
13. stmt.executeUpdate(sql);
Java Programming
 Select a record 58
1. stmt = conn.createStatement();
2. String sql = "SELECT id, first, last, age FROM”
3. + “ Registration";
4. ResultSet rs = stmt.executeQuery(sql);
5. while (rs.next()) { // Retrieve by column name
6. int id = rs.getInt("id"); int age = rs.getInt("age");
7. String first = rs.getString("first");
8. String last = rs.getString("last");
9. // Display values
10. System.out.print("ID: " + id);
11. System.out.print(", Age: " + age);
12. System.out.print(", First: " + first);
13. System.out.println(", Last: " + last);
14. }
15. rs.close(); Java Programming
 Update a record 59
1. stmt = conn.createStatement();
2. String sql = "UPDATE Registration "
3. + "SET age = 30 WHERE id in (100, 101)";
4. stmt.executeUpdate(sql);

Java Programming
 Delete a record 60
1. stmt = conn.createStatement();
2. String sql = "DELETE FROM Registration " + "WHERE id = 101";
3. stmt.executeUpdate(sql);

Java Programming
 WHERE clause 61
1. stmt = conn.createStatement();
2. sql = "SELECT id, first, last, age FROM Registration"
3. + " WHERE id >= 101 ";
4. rs = stmt.executeQuery(sql);
5. while (rs.next()) { // Retrieve by column name
6. int id = rs.getInt("id"); int age = rs.getInt("age");
7. String first = rs.getString("first");
8. String last = rs.getString("last");
9. // Display values
10. System.out.print("ID: " + id);
11. System.out.print(", Age: " + age);
12. System.out.print(", First: " + first);
13. System.out.println(", Last: " + last);
14. } Java Programming
 62

QUESTION ?

Java Programming