Java Data Base Connectivity Lecturer Dr Pavle Mogin

1
Java Data Base ConnectivityLecturer Dr Pavle
Mogin
2
Plan for Java Database Connectivity (JDBC)

• Motives for using JDBC
• Architecture
• JDBC Classes and Interfaces
• JDBC Driver Management
• Connections
• Executing SQL statements
• Result Set
• Matching Data Types
• Exceptions
• Reading
• The JavaTM Tutorial
• http//www.mcs.vuw.ac.nz/technical/java/tutorial/i
  ndex.html

3
Motives for Using JDBC

• Real database applications in industry often need
  DML (Data Manipulation Language) features of SQL
  and a greater flexibility of a general purpose
  programming language (GPPL), also called host
  language.
• SQL is needed because of its
• Simplicity and
• Efficiency of data manipulation
• A GPPL is needed for its support of
• Complex computation,
• Complex structures of large programs,
• GUI, and
• Integration with other applications

4
Embedding SQL into a GPPL

• A database application is written in GPPL with
  SQL statements embedded in it.
• When an application establishes a connection with
  a database, SQL statements are used to retrieve
  data and manipulate the database.
• But SQL queries retrieve sets and multisets of
  tuples, while GPPL often do not support set and
  multiset data types.
• This mismatch is resolved by providing a handle
  on the set. Application iterates over the set
  using the handle.

5
Different SQL Embedding Techniques

• Techniques that provide for source code to DBMS
  independence only
• Embedded SQL (static SQL statements),
• Dynamic SQL (allows creating SQL statements at
  the run time),
• Techniques that provide for both source and
  object code to DBMS independence
• ODBC Open Data Base Connectivity (C)
• JDBC Java Data Base Connectivity

6
O J Database Connectivity

• The goal of ODBC and JDBC software systems is to
  allow connecting an application program to almost
  any database system
• Applications using ODBC or JDBC are DBMS
  independent on both source code and executable
  code level (providing that the SQL standard is
  satisfied)
• This independence is achieved by introducing an
  extra level of indirection
• All program interactions with a specific DBMS are
  accomplished through a DBMS specific driver

7
O J DBC
Application Program
Driver Manager
O or J DBC Call (SQL)
Driver (Informix )
Select a Driver
Driver
SQL translated Into DBMS specific commands
Driver (SQL Server )
Driver (Oracle )
Driver Loading

Driver (PostgreSQL)
SQL Server
IDS
Oracle
PostgreSQL

8
JDBC Architecture

• An application (program)
• A driver manager
• Several database specific drivers, and
• Database systems

9
1. JDBC Application

• An application program
• Dynamically loads the drivers needed,
• Initiates a connection with a database,
• Sets transaction boundaries (BEGIN,, COMMIT
  ROLLBACK),
• Acquires locks,
• Submits SQL statements,
• Receives data,
• Process data,
• Process error messages,
• Decides whether to commit or roll-back a
  transaction, and
• Disconnects from the database to terminate a
  session

10
2. JDBC Driver Manager

• The driver manager
• Loads the drivers needed,
• Passes JDBC function calls from the application
  to the correct driver,
• Handles JDBC initialization from the application,
  and
• Performs some rudimentary error checking

11
3. JDBC Driver and the DBMS

• A database specific driver
• Establishes connection with a database,
• Submits data manipulation requests,
• Accepts returning results,
• Translates DBMS specific data types into Java
  data types,
• Translates error messages
• A DBMS
• Processes data manipulation commands and
• Returns results

12
JDBC Classes and Interfaces

• JDBC is a collection of Java classes and
  interfaces
• It contains methods for
• Connecting to a remote database,
• Executing SQL statements,
• Iterating over sets of results from SQL
  statements,
• Transaction management, and
• Exception handling
• All these are contained in the
• java.sql
• package

13
JDBC DriverManagemer class

• The package java.sql contains the DriverManager
  class
• The DriverManager class has methods
• registerDriver(),
• deregisterDriver()
• to enable dynamic addition and deletion of
  drivers
• The 1st step in connecting to a database is to
  load an appropriate JDBC driver.

14
Registering a Driver

• The method
• public static native Class.forName(String s)
• throws ClassNotFoundException
• in the java.lang.Class class will, for
  name"driver_name" return the requested Driver
  class end execute its static constructor.
• This Driver object will register itself with the
  DriverManager class
• Example A PostgreSQL driver is loaded using
• Class.forName("org.postgresql.Driver")

15
Establishing a Connection

• A session with a database is started through
  creation of a Connection object
• Connection con DriverManager.getConnection(url,
  userId, password)
• where url is a object of URL class.

16
The url Argument of getConnection()

• The url argument is a URL of the format
• jdbcdrivertypedatabase,
• jdbc is a constant,
• drivertype is the type of the database we want
  to connect (e.g. postrgresql), and
• database is the address of the actual database
  of the form
• //hostnameportnumber/database_name
• Finally, a possible URL would be
• String s
• "jdbcpostgresql//db.mcs.vuw.ac.nz/
• userId "_jdbc"

17
Connection Interface

• The interface java.sql.Connection has a number of
  classes and methods that are used
• To control transactional behavior of a Connection
  object,
• To create and execute SQL statements,
• To iterate over the result returned by a DBMS,
  and
• To finish interaction with a database by closing
  the connection
• After acquiring a connection (with the name say
  con) and before it is closed, the same connection
  can be used for executing several transactions

18
Controlling Transaction Behavior-Start

• By default, a Connection automatically commits
  changes after executing each SQL statement
• The method
• public abstract void setAutoCommit( boolean
  autoCommit) throws SQLException
• is applied onto a Connection object to designate
  the start of a
• transaction (BEGIN point) by assigning a
  value false to
• autoCommit con.setAutoCommit(false)
• A commit operation makes permanent all changes
  made under commitment control since the previous
  commit or rollback operation.
• A rollback operation removes all changes made
  since the previous commit or rollback operation.

19
Controlling Transaction Behavior-End

• A transaction is terminated using
• Either
• public abstract void commit() throws
• SQLException
• or
• public abstract void rollback() throws
• SQLException
• and (after any of them)
• con.setAutoCommit(true)
• on the Connection object

20
Executing SQL Statements

• JDBC supports three different ways of executing
  SQL statements
• Statement,
• PreparedStatement, and
• CallableStatement

21
Statement Class and Its Subclasses

• The Statement class is the base class of the
  three classes used to submit queries to a DBMS
• Its objects are used to forward to a DBMS SQL
  queries that are executed without repetition
  within a transaction and that have no parameters,
• The PreparedStatement objects are used for SQL
  statements with parameters or for those that are
  executed multiple times (in a loop)
• SQL statements of PreparedStatement objects may
  be precompiled yielding better performance
• CallableStatement objects are used with stored
  procedures and are out of the scope of the course

22
Submitting a SQL Query to a DBMS

• The following steps should be performed in order
  to submit a SQL statement to a DBMS either using
  a Statement (S) or PreparedStatement (PS) object
• Define a SQL query as a String
• Create a S or PS object
• If the SQL statement is one of CREATE, INSERT,
  DELETE, UPDATE, or SET type
• Apply executeUpdate() method onto a S or PS
  object
• Else (the SQL statement is of the SELECT type)
• Create a ResultSet object
• Feed into the ResultSet object the return value
  of applying executeQuery() method onto S or PS
  object

23
Statement Objects With executeUpdate

• String sql"INSERT INTO Grades "
  "VALUES (007007,C305,A)"
• Statement stmt con.createStatement()
• int return_value stmt.executeUpdate(sql)
• For INSERT, DELETE, and UPDATE queries, the
  return value will be the number of tuples
  affected
• For CREATE or SET, the return value should be 0

24
ResultSet Object

• The executeQuery() method returns an object of
  the type set (or superset)
• This set object should be assigned to an object
  of the ResultSet class
• The ResultSet class has the next() method that
  allows traversing the set in a tuple at a time
  fashion
• Initially, the ResultSet object is positioned
  before the first tuple of the result
• The method next() returns true if there is a next
  tuple in the result, otherwise false
• After executing next(), the ResultSet object
  contains the pointer to the current tuple

25
Statement Objects With executeQuery

• String sql"SELECT FROM Grades" "WHERE
  StudentId007007"
• Statement stmt con.createStatement()
• ResultSet rs stmt.executeQuery(sql)
• while (rs.next())
• // extracting data from rs tuples
• // data processing

26
Extracting Data from the Result

• To match Java and database data types, JDBC
  specifies mappings and provides accessor methods
  in the ResultSet class
• ...
• ResultSet rs stmt.executeQuery(sql)
• int j_studId
• String j_courseId
• String j_grade
• while (rs.next())
• j_studIdrs.getInt("StudentId")
• j_courseIdrs.getString("CourseId")
• j_graders.getString(3)
• // 3 is the column number in the result

27
PreparedStatement With executeUpdate

• String sql"INSERT INTO Grades VALUES (?,?,?)"
• PreparedStatement prstmt
• con.prepareStatement(s
  ql)
• boolean endfalse
• while(!end)
• // suppose j_studId, j_courseId, j_grade, and
  end are
• // dynamically initialized to desired values
• prstmt.setInt(1, j_studId)
• prstmt.setString(2, j_courseId)
• prstmt.setString(3, j_grade)
• int return_value prstmt.exectuteUpdate()

28
(PreparedStatement with executeQuery)

• String select "SELECT FROM Grades WHERE
  StudentId ?"
• PreparedStatement prstmt
  con.prepareStatement(select)
• // suppose j_studId is initialized on the
• // desired value
• prstmt.setInt(1, j_studId)
• ResultSet rs prstmt.exectuteQuery()
• while(rs.next())

29
Finishing a Transaction

• Suppose a transaction is initiated by a
• con.setAutoCommit(false)
• command
• If it finishes successfully, then
• public abstract void commit() throws
  SQLException
• method should be applied on the Connection
  object
• Otherwise
• public abstract void rollback() throws
  SQLException
• After any of con.commit() and con.rollback(), you
  issue
• con.setAutoCommit(true)

30
Closing a Connection

• Before exiting from an application program all
  connections acquired should be closed by applying
• public abstract void close()
• throws SQLException
• method on each of them

31
Exceptions

• Most of the methods in java.sql can throw an
  exception of the type SQLException if an error
  occurs
• In addition to inhereted getMessage() method,
  SQLException class has two additional methods for
  providing error information
• public String getSQLState() that returns an SQL
  state identifier according to SQL1999 standard,
  and
• public int getErrorCode() that retrieves a
  vendor specific error code
• Each JDBC method that throws an exception has to
  be placed inside a try block followed by a catch
  block

32
Code to See Exceptions

• try
• / Code that could generate an exception
  goes here. If an exception is generated, the
  catch block below will print out information
  about it/
• catch (SQLException ex)
• System.println(ex.getMessage())
• System.println(ex.getSQLState())
• System.println(ex.getErrorCode())

33
Summary

• JDBC architecture
• An application program,
• A Driver Manager,
• Database specific Drivers, and
• Databases
• Scenario of a JDBC session
• Drivers needed are dynamically loaded,
• Connections with databases are established,
• Each connection has transaction semantics,
• Transactions are executed by
• Submitting SQL statements,
• Retrieving results,
• Processing errors,
• Committing or roll-backing transactions, and
• Disconnecting from databases to terminate
  interaction