JDBC Java Database Connectivity

1
JDBC Java Database Connectivity

• Presented By
• Haseeb Yousaf
• Priya Wadhwa

2
What is JDBC ??

• The Java Database Connectivity (JDBC) API for
  database-independent connectivity between the
  Java and a wide range of databases.
• Information is transferred from relations to
  objects and vice-versa
• databases optimized for searching/indexing
• objects optimized for engineering/flexibility

3
JDBC Architecture
Diagram taken from www.javadesign.info/JSE/JDBC/j
dbc_architecture
4
Types Of JDBC Driver

• There are four types of JDBC drivers
• Type 1 JDBC-ODBC Bridge Driver
• Type 2 Native-API/Partly Java Driver
• Type 3 Network Protocol Driver
• Type 4 Native Protocol Driver

5
Type 1 JDBC-ODBC Bridge Driver

• Also know as JDBC-ODBC Bridge
• The Driver makes use of the ODBC driver to
  connect to the database.
• OS Platform dependent.
• Driver has to be installed on all Client Machine.
• Not fully written in Java
• Not recommended for Web

JDBC-ODBC Driver Architecture
6
TYPE 2 NATIVE-API/PARTLY JAVA DRIVER

• Also known as the Native-API Driver.
• Makes use of the client-side libraries of the
  database to connect.
• Dont have to use any ODBC drivers.
• All Client machines has to install driver for
  each DB.

Native API/Partly Java Drivers
7
TYPE 3 NETWORK PROTOCOL DRIVER

• Also known as the Pure Java Driver for database
  Middleware
• Follows a three-tiered approach where the client
  sends the JDBC calls through the network to a
  middle-tier server
• No database library is required on the client
  side.

Type 3 Network Protocol Driver Architecture
8
Type 4 Native Protocol Driver

• Most Commonly Used Driver.
• Also known as the Direct to Database Pure Java
  Driver.
• It converts the JDBC calls directly into database
  specific calls.
• Platform Independent.
• Drivers are written in Java and runs with JVM.
• Vendor Specific Drivers are needed on client.
• Easy to deploy on Different OS

Type 4 Native Protocol driver Architecture
9
Connection Process Flow

• Load the driver
• Define the connection URL
• Establish the connection
• Create a Statement object
• Execute a query using the Statement
• Process the result
• Close the connection

10
Loading the drivers

• Two ways we can load the drivers in our Java
  Application
• 1. Internally
• 2. Externally
• INTERNALLY
• Using a single command we can laod a driver
  internally
• Class.forName(DB.jdbc.driver.DBdriver")
• (where Class.forName loads the specified
  class.)
• EXTERNALLY
• To use a specific driver, we need to instantiate
  it and register it within the driver manager
• Driver driver new DB.jdbc.DBDriver()
• DriverManager.registerDriver(driver)

11
Connecting to the Database

• Every database is identified by a URL.
• DriverManager class manages connections looks
  for the driver that can communicate to the
  corresponding database.
• The simple command can be use to connect to the
  specific database.
• Connection con  DriverManage
  r.
• getConnection("jdbcDB")
• Example of URL for database
• Connection con DriverManager.getConnection(
  uml.cs.uga.edu3306, abc,xyz)

12
INTERACTION WITH THE DATABASE

• Three different interfaces are used
• Statement, PreparedStatement, CallableStatemen
• We use Statement objects in order to
• Query the database
• Update the database
• All are interfaces, hence cannot be instantiated
• They are created by the Connection

13
Executing a Statement

• String stmt "SELECT FROM Student "
• "WHERE studID 1"
• Statement stmt con.createStatement()
• ResultSet rs stmt.executeQuery(stmt)
• The executeQuery method returns a ResultSet
  object representing the query result.

14
Updating the Database

• String delStmt "DELETE FROM Student
  "
• "WHERE
  studID 1"
• Statement stmt con.createStatement()
• int delnum stmt.executeUpdate(deleteStr)
  
• executeUpdate is used for data manipulation
  insert, delete, update, create table, etc.
  (anything other than querying!)
• executeUpdate returns the number of rows modified

15
Prepared Statements

• Prepared Statements Interface introduce for
  dynamic execution.
• Prepared Statements are used for queries that are
  executed many times
• They are parsed (compiled) by the DBMS only once
• Column values can be set after compilation
• Instead of values, use ?
• Prepared Statements can be thought of as
  statements that contain placeholders to be
  substituted later with actual values

16
Execute Query with PreparedStatement

• String ppstmt
• "SELECT FROM Course "
• "WHERE id ? And name ?"
• PreparedStatement pstmt con.prepareStatement(pp
  stmt)
• pstmt.setInt(1, 101)
• pstmt.setString(2, XYZ)
• ResultSet rs pstmt.executeQuery()

17
Updating Querying

• String delStr
• DELETE FROM Product "
• "WHERE pid ? And pname gt ?"
• PreparedStatement pstmt con.prepareStatement(de
  lStr)
• pstmt.setString(2, XYZ)
• pstmt.setInt(1, 1000)
• int delnum pstmt.executeUpdate()

18
Restrictions in PreparedStatements

• Cant Use PreparedStatement for all records
• Only can be use to retrieve a single column
  value.
• E.g.
• PreparedStatement pstmt
  con.prepareStatement("select from ?")
• pstmt.setString(1, myFavoriteTableString)
  
• This statement doesnt work as it is retrieving
  the whole table
• ? is used for conditional statements.

19
ResultSet

• ResultSet objects provide access to the tables
  generated as results of executing queries
• Only one ResultSet per Statement can be open at
  the same time!
• The table rows are retrieved in sequence
• A ResultSet maintains a cursor pointing to its
  current row
• The next() method moves the cursor to the next
  row

20
Methods for ResultSet

• boolean next()
• activates the next row
• the first call to next() activates the first row
• 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

21
ResultSet Methods

• Type getType(int columnIndex)
• returns the given field as the given type
• indices start at 1 and not 0!
• Type getType(String columnName)
• same, but uses name of field
• less efficient
• For example getString(columnIndex),
  getInt(columnName), getTime, getBoolean,
  getType,...
• int findColumn(String columnName)
• looks up column index given column name

22
ResultSet Example

• Statement stmt  con.createStatement
  ()
• ResultSet rs  stmt. executeQuery("select 
  name,age from Employees")    
• // Print the result
• while(rs.next()) 
   System.out.print(rs.getString(1)  "") 
  System.out.println(rs.getShort("age"))
• In the above example,, the records that are
  generated by the execute query are stored as
  RecordSet Object. The RecordSet method next
  is used to retrieve the required columns
  information.

23
ResultSet MetaData

• A ResultSetMetaData is an object that can be used
  to get information about the properties of the
  columns in a ResultSet object
• An example write the columns of the result set
• ResultSetMetaData rsmd
  rs.getMetaData()
• int numcols rsmd.getColumnCount(
  )
• for (int i 1 i lt numcols
  i)
• System.out.print(rsmd.getColumnLabel(i)
  " ")
• This metadata object is using getColumnLabel()
  method to retrieve the column name of the result
  set columns

24
Stored Procedures in JDBC

• A stored procedure is a set of SQL queries that
  are grouped together to perform a certain task
• They can be used to perform DDL, DML and other
  SQL tasks.
• Different DBMS have different syntax to create a
  stored procedure.
• eg. Create procedure studid()
• as
• select studID from student s,
  course c where s.studID c.studID

25
Stored Procedures in JDBC

• In order to call a stored procedure in a JDBC ,
  you have to store it as a String object
• eg
• String callproc Create procedure
  studid()
  
  as select studID from student s, course c
  where s.studID c.studID
• As the stored procedure is stored in a String ,,
  it becomes a normal object.
• Statement object can be used to compile and store
  this stored procedure as a DB object as..
• Statement stmt
  con.createStatement()
• stmt.executeQuery(call
  proc)

26
CallableStatement Interface.

• The third kind of interface for executing
  statements is CallableStatements.
• preparedCall() method is used to call a procedure
  that is stored as a database object.
• CallableStatement cs
  con.preparedCall(callproc)
• ResultSet rs cs.executeQuery()
• The class CallableStatement is a subclass of
  PreparedStatement.
• CallableStatement can be used for IN, OUT and
  INOUT parameters

27
Closing the Objects

• Remember to close the Connections, Statements,
  Prepared Statements and Result Sets
• Four methods for
• con.close()
• stmt.close()
• pstmt.close()
• rs.close()

28
EXCEPTIONS

• An SQLException is actually a list of exceptions

catch (SQLException e) while (e ! null)
System.out.println(e.getSQLState()) System.ou
t.println(e.getMessage()) System.out.println(e.g
etErrorCode()) e e.getNextException()
29
TRANSACTION MANAGEMENT

• Transaction
• More than one statement that must all succeed
  (or all fail) together
• If one fails, the system must reverse all
  previous actions
• Also cant leave DB in inconsistent state halfway
  through a transaction
• COMMIT complete transaction
• ROLLBACK cancel all actions

30
Transaction and JDBC

• Transactions are not explicitly opened and closed
  in JDBC
• The connection has a state called AutoCommit mode
• if AutoCommit is true, then every statement is
  automatically committed
• if AutoCommit is false, then every statement is
  added to an ongoing transaction
• Default true

31
Setting the Auto-Commit

• The following statement is used to set the Auto
  Comment
• setAutoCommit(boolean val)
• If you set AutoCommit to false, you must
  explicitly commit or rollback the transaction
  using Connection.commit() and Connection.rollback(
  )
• Note DDL statements (e.g., creating/deleting
  tables) in a transaction may be ignored or may
  cause a commit to occur
• The behavior is DBMS dependent

32

• Thank You