JDBC Java Database Connectivity

1
JDBC Java Database Connectivity
2
Introduction to JDBC

• JDBC is used for accessing databases from Java
  applications
• Information is transferred from relations to
  objects and vice-versa
• databases optimized for searching/indexing
• objects optimized for engineering/flexibility

3
JDBC Architecture
We will use this one
These are Java classes
Oracle Driver
Oracle
Java Application
DB2 Driver
JDBC
DB2
Network
Postgres Driver
Postgres
4
JDBC Architecture (cont.)
Application
JDBC
Driver

• Java code calls JDBC library
• JDBC loads a driver
• Driver talks to a particular database
• An application can work with several databases by
  using all corresponding drivers
• Ideal can change database engines without
  changing any application code (not always in
  practice)

5
Seven Steps

• 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

6
Registering the Driver

• To use a specific driver, we need to instantiate
  it and register it within the driver manager
• Driver driver new oracle.jdbc.OracleDriver()
• DriverManager.registerDriver(driver)

7
A Modular Alternative

• We can register the driver indirectly using the
  statement
• Class.forName("oracle.jdbc.driver.OracleDriver
  ")
• Class.forName loads the specified class
• When OracleDriver is loaded, it automatically
• creates an instance of itself
• registers this instance with the DriverManager
• Hence, the driver class can be given as an
  argument of the application

8
An Example

• // A driver for imaginary1
• Class.forName("ORG.img.imgSQL1.imaginary1Driver")
  
• // A driver for imaginary2
• Driver driver  new ORG.img.imgSQL2.imaginary2Driv
  er()
• DriverManager.registerDriver(driver)
• //A driver for oracle
• Class.forName("oracle.jdbc.driver.OracleDriver") 
     

imaginary1
Oracle
imaginary2
Registered Drivers
9
Connecting to the Database

• Every database is identified by a URL
• Given a URL, DriverManager looks for the driver
  that can talk to the corresponding database
• DriverManager tries all registered drivers, until
  a suitable one is found

10
Connecting to the Database

• Connection con  DriverManager.
• getConnection("jdbcimaginaryDB1")

acceptsURL("jdbcimaginaryDB1")?
a
r
r
imaginary1
Oracle
imaginary2
Registered Drivers
Read more in DriverManager API
11
The URLs in CS

• In CS, a URL has the following structure
• jdbcoraclethinname/password_at_sol41521stud
• For example
• jdbcoraclethinsnoopy/snoopy_at_sol41521stud

Also, your login
Your login
The machine on which our Oracle runs
The standard port of Oracle
12
Interaction with the Database

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

13
Querying with Statement
String queryStr "SELECT FROM Member "
"WHERE Lower(Name) 'harry potter'" Statemen
t stmt con.createStatement() ResultSet rs
stmt.executeQuery(queryStr)

• The executeQuery method returns a ResultSet
  object representing the query result.
• Will be discussed later

14
Changing DB with Statement
String deleteStr "DELETE FROM Member "
"WHERE Lower(Name) 'harry potter'" Statemen
t 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
About Prepared Statements

• 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 ?
• Hence, Prepared Statements can be though of as
  statements that contain placeholders to be
  substituted later with actual values

16
Querying with PreparedStatement
String queryStr "SELECT FROM Items "
"WHERE Name ? and Cost lt ?" PreparedStateme
nt pstmt con.prepareStatement(queryStr) pstmt
.setString(1, "t-shirt") pstmt.setInt(2,
1000) ResultSet rs pstmt.executeQuery()
17
Updating with PreparedStatement
String deleteStr DELETE FROM Items "
"WHERE Name ? and Cost gt ?"
PreparedStatement pstmt con.prepareStatement
(deleteStr) pstmt.setString(1,
"t-shirt") pstmt.setInt(2, 1000) int delnum
pstmt.executeUpdate()
18
Statements vs. PreparedStatements Be Careful!

• Are these the same? What do they do?

String val "abc" PreparedStatement pstmt
con.prepareStatement("select from R where
A?") pstmt.setString(1, val) ResultSet rs
pstmt.executeQuery()
String val "abc" Statement stmt
con.createStatement( ) ResultSet rs
stmt.executeQuery("select from R where A"
val)
19
Statements vs. PreparedStatements Be Careful!

• Will this work?
• No!!! A ? can only be used to represent a
  column value

PreparedStatement pstmt con.prepareStatemen
t("select from ?") pstmt.setString(1,
myFavoriteTableString)
20
Timeout

• Use setQueryTimeOut(int seconds) of Statement to
  set a timeout for the driver to wait for a
  statement to be completed
• If the operation is not completed in the given
  time, an SQLException is thrown
• What is it good for?

21
ResultSet

• ResultSet objects provide access to the tables
  generated as results of executing a Statement
  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

22
ResultSet Methods

• 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

23
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

24
ResultSet Example
Statement stmt  con.createStatement() ResultSet 
rs  stmt. executeQuery("select name,age from Emp
loyees")     // Print the result while(rs.next())
  System.out.print(rs.getString(1)  "") Syst
em.out.println(rs.getShort("age"))
25
Mapping Java Types to SQL Types

• SQL type Java Type
• CHAR, VARCHAR, LONGVARCHAR String
• NUMERIC, DECIMAL java.math.BigDecimal
• BIT boolean
• TINYINT byte
• SMALLINT short
• INTEGER int
• BIGINT long
• REAL float
• FLOAT, DOUBLE double
• BINARY, VARBINARY, LONGVARBINARY byte
• DATE java.sql.Date
• TIME java.sql.Time
• TIMESTAMP java.sql.Timestamp

26
More Information
A detailed overview of type mapping and type
conversion can be found at http//java.sun.com/j2s
e/1.3/docs/guide/jdbc/getstart/mapping.html
27
Null Values

• In SQL, NULL means the field is empty
• Not the same as 0 or ""
• In JDBC, you must explicitly ask if the last-read
  field was null
• ResultSet.wasNull(column)
• For example, getInt(column) will return 0 if the
  value is either 0 or NULL!

28
Null Values

• When inserting null values into placeholders of
  Prepared Statements
• Use the method setNull(index, Types.sqlType) for
  primitive types (e.g. INTEGER, REAL)
• You may also use the setType(index, null) for
  object types (e.g. STRING, DATE).

29
ResultSet Meta-Data
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.getC
olumnLabel(i)" ")
Many more methods in the ResultSetMetaData API
30
Database Time

• Times in SQL are notoriously non-standard
• Java defines three classes to help
• java.sql.Date
• year, month, day
• java.sql.Time
• hours, minutes, seconds
• java.sql.Timestamp
• year, month, day, hours, minutes, seconds,
  nanoseconds
• usually use this one

31
Cleaning Up After Yourself

• Remember to close the Connections, Statements,
  Prepared Statements and Result Sets

con.close() stmt.close() pstmt.close() rs.close
()
32
Dealing With 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()
33
Transaction Management
34
Transactions and JDBC

• Transaction more than one statement that must
  all succeed (or all fail) together
• e.g., updating several tables due to customer
  purchase
• 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

35
Example

• Suppose we want to transfer money from bank
  account 13 to account 72

PreparedStatement pstmt con.prepareStatement("u
pdate BankAccount set amount amount
? where accountId ?") pstmt.setInt(1,-100)
pstmt.setInt(2, 13) pstmt.executeUpdate() pst
mt.setInt(1, 100) pstmt.setInt(2,
72) pstmt.executeUpdate()
What happens if this update fails?
36
Transaction Management

• Transactions are not explicitly opened and closed
• 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

37
AutoCommit
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

38
Fixed Example
con.setAutoCommit(false) try
PreparedStatement pstmt con.prepareStatement("u
pdate BankAccount set amount amount
? where accountId ?")
pstmt.setInt(1,-100) pstmt.setInt(2, 13)
pstmt.executeUpdate() pstmt.setInt(1, 100)
pstmt.setInt(2, 72) pstmt.executeUpdate()
con.commit() catch (SQLException e)
con.rollback()
39
Isolation Levels

• How do different transactions interact? Do they
  see what another has written?
• Possible problems
• Dirty Reads one transaction reads data written
  by another uncommitted transaction
• Unrepeatable Reads two different results are
  seen when reading the same row twice in the same
  transaction
• Phantom Reads rows are added to (or deleted
  from) a table between two readings of this table
  in a single transaction

40
Isolation Levels

• JDBC defines four isolation modes

41
Isolation Levels

• Set the transaction mode using setTransactionIsola
  tion() of class Connection
• Oracle only implements
• TRANSACTION_SERIALIZABLE
• An exception may be thrown if serializability
  isnt possible
• TRANSACTION_READ_COMMITED
• This is the default

42
Level READ_COMMITED

• Transaction 1
• insert into A values(1)
• insert into A values(2)
• commit

• Transaction 2
• select from A
• select from A

Question Is it possible for a transaction to see
1 in A, but not 2?
Question Is it possible for the 2 queries to
give different answers for level SERIALIZABLE?
1
2
Table A
43
Large Objects
44
LOBs Large OBjects

• Two types
• CLOB Character large object (a lot of
  characters)
• BLOB Binary large object (a lot of bytes)
• Actual data is not stored in the table with the
  CLOB/BLOB column, only a pointer to the data
• Oracle does not support these objects as in the
  specification, so a special treatment is required
• We will see how BLOBs are managed
• Handling CLOBs is similar

45
Storing BLOBs

• Suppose that we have a binary source (e.g., a
  file, a socket, etc.) that is readable through a
  Java InputStream object istream
• Suppose that we want to store the source content
  in a table MyBlobs(name varchar, content BLOB)

46
Storing BLOBs (cont)

• First, we set AutoCommit to false
• con.setAutoCommit(false)
• Next, we insert a row with an empty BLOB
• Statement stmt con.createStatement()
• stmt.executeUpdate("insert into myblobs
  values('b1',empty_blob()")
• Now, retrieve the BLOB
• ResultSet rs
• stmt.executeQuery("select content from myblobs
  where name 'b1'")
• rs.next() BLOB bl (BLOB) (rs.getBlob(1))

47
Storing BLOBs (cont)

• We can now get the BLOB's output stream
• OutputStream blStream bl.getBinaryOutputStream()
  
• Next, we write the content into the stream
• int bytesRead 0 byte data new
  byte4096
• while ((bytesRead fileStream.read(data))
  gt 0) blStream.write(data,0,bytesRead)
• Finally, we close the resources and commit
• rs.close() stmt.close() blStream.close()
• con.commit()

48
Retrieving BLOBs

• BLOB retrieval is simpler that storage
• Suppose that we want to write our BLOB to ostream
• First, we get the BLOB
• Statement stmt con.createStatement()
• ResultSet rs
• stmt.executeQuery("select content from
  binaryFiles where name 'b1'")
• rs.next() BLOB bl (BLOB) (rs.getBlob(1))
• Next, get the input stream of the BLOB
• InputStream blStream bl.getBinaryStream()

49
Retrieving BLOBs (cont)

• Now, we read the BLOB content through the stream
• int bytesRead 0 byte data new byte4096
• while ((bytesRead blStream.read(data)) gt 0)
• ostream.write(data, 0, bytesRead)
• Finally, we close the resources and commit
• rs.close() stmt.close() blStream.close()
• con.commit()