JDBC

1
JDBC Java Database Connectivity

• Representation and Management of Data on the
  Internet

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
Overview
RMI
JDBC
CORBA
java.net
TCP/IP
Network OS
4
Working With Oracle

• Add to your .cshrc the following
• if (HOST sol4) then
• setenv ORACLE_HOME /opt/oracle
• else
• setenv ORACLE_HOME /usr/local/oracle8i
• endif
• setenv PATH ORACLE_HOME/binPATH
• setenv ORACLE_SID stud

5
Using Oracle

• If a student whose login is Snoopy wants to work
  directly with Oracle
• sqlplus snoopy/snoopy_at_stud.cs
• Note we use the login for a password! (Dont
  change your password)

6
Seven Steps

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

7
Packages to Import

• In order to connect to the Oracle database from
  java, import the following packages
• java.sql. (usually enough)
• javax.sql. (for advanced features, such as
  scrollable result sets)

8
JDBC Architecture
Application
JDBC
Driver

• Java code calls JDBC library
• JDBC loads a driver
• Driver talks to a particular database
• Can have more than one driver -gt more than one
  database
• Ideal can change database engines without
  changing any application code

9
Loading the Driver

• We can register the Driver indirectly using the
  Java statement
• Class.forName(oracle.jdbc.driver.OracleDriver")
  
• Calling Class.forName, automatically
• creates an instance of the driver
• registers the driver with the DriverManager

10
Another Option

• Another option is to create an instance of the
  driver and register it with the Driver Manager
• Driver driver new oracle.jdbc.OracleDriver()
• DriverManager.registerDriver(driver)

11
The DriverManager

• The DriverManager tries all the drivers
• Uses the first one that works
• When a driver class is first loaded, it registers
  itself with the DriverManager
• Therefore, to load a driver, just register it!

12
Connecting to the Database
String path "jdbcoraclethin" String host
"sol4" String port "1521" String db
"stud" String login "snoopy" String url
path login "/" login "_at_" host
"" port "" db Class.forName("oracle.jdb
c.driver.OracleDriver") Connection con
DriverManager.getConnection(conStr)
This is actually the password
13
Connection Methods

• Statement createStatement()
• returns a new Statement object
• PreparedStatement prepareStatement(String sql)
• returns a new PreparedStatement object
• CallableStatement prepareCall(String sql)
• returns a new CallableStatement object
• Why all these different kinds of statements?
  Optimization.

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

• Statements are used for queries that are only
  issued once.
• The executeQuery method returns a ResultSet
  object representing the query result.

15
Changing DB with Statement
String deleteStr DELETE FROM Member "
"WHERE Lower(Name) lord voldemort" Statem
ent 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.

16
About Prepared Statements

• Prepared Statements are used for queries that are
  executed many times.
• They are parsed only once.
• Using setString(i, value) (setInt(i, value),
  etc.) the i-th question mark is set to the given
  value.

17
Querying with PreparedStatement
String queryStr "SELECT FROM Program "
"WHERE Name ? and Cost lt ? PreparedStateme
nt pstmt con.prepareStatement(queryStr) pstmt
.setString(1, Unfogging the Future) pstmt.setIn
t(2, 1000) ResultSet rs pstmt.executeQuery()
18
Changing DB with PreparedStatement
String deleteStr DELETE FROM Program "
"WHERE Name ? and Cost lt ?
PreparedStatement pstmt con.prepareStatement
(deleteStr) pstmt.setString(1, Unfogging the
Future) pstmt.setInt(2, 1000) int delnum
pstmt.executeUpdate()
19
Timeout

• Use setQueryTimeOut(int seconds) 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?

20
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)
21
Statements vs. PreparedStatements Be Careful!

• Will this always work?

Statement stmt con.createStatement(
) ResultSet rs stmt.executeQuery(select
from R where A val )

• The moral When getting input from the user,
  always use a PreparedStatement

22
Statements vs. PreparedStatements Be Careful!

• Will this work?

PreparedStatement pstmt con.prepareStatemen
t(select from ?) pstmt.setString(1,
myFavoriteTableString)
23
ResultSet

• A ResultSet provides access to a table of data
  generated by executing a Statement.
• Only one ResultSet per Statement can be open at
  once.
• The table rows are retrieved in sequence.
• A ResultSet maintains a cursor pointing to its
  current row of data.
• The 'next' method moves the cursor to the next
  row.

24
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

25
ResultSet Methods

• Type getType(int columnIndex)
• returns the given field as the given type
• fields indexed starting at 1 (not 0)
• Type getType(String columnName)
• same, but uses name of field
• less efficient
• int findColumn(String columnName)
• looks up column index given column name

26
ResultSet Methods

• String getString(int columnIndex)
• boolean getBoolean(int columnIndex)
• byte getByte(int columnIndex)
• short getShort(int columnIndex)
• int getInt(int columnIndex)
• long getLong(int columnIndex)
• float getFloat(int columnIndex)
• double getDouble(int columnIndex)
• Date getDate(int columnIndex)
• Time getTime(int columnIndex)
• Timestamp getTimestamp(int columnIndex)

27
ResultSet Methods

• String getString(String columnName)
• boolean getBoolean(String columnName)
• byte getByte(String columnName)
• short getShort(String columnName)
• int getInt(String columnName)
• long getLong(String columnName)
• float getFloat(String columnName)
• double getDouble(String columnName)
• Date getDate(String columnName)
• Time getTime(String columnName)
• Timestamp getTimestamp(String columnName)

28
isNull

• In SQL, NULL means the field is empty
• Not the same as 0 or
• In JDBC, you must explicitly ask if a field is
  null by calling ResultSet.isNull(column)

29
Printing Query Output Result Set (1)
Print Column Headers
ResultSetMetaData rsmd rs.getMetaData() int
numcols rsmd.getColumnCount() for (int i 1
i lt numcols i) if (i gt 1)
System.out.print(",") System.out.print(rsmd.getC
olumnLabel(i))
30
Printing Query Output Result Set (2)
while (rs.next()) for (int i 1 i lt
numcols i) if (i gt 1) System.out.print(",")
System.out.print(rs.getString(i)) System.
out.println("")

• To get the data in the i-th column
  rs.getString(i)
• To get the data in column Abc
  rs.getString(Abc)

31
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

32
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

33
Cleaning Up After Yourself

• Remember to close the Connections, Statements,
  PreparedStatements and ResultSets

con.close() stmt.close() pstmt.close() rs.close
()
34
Dealing With Exceptions

• A exception can have more exceptions in it.

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()
35
Advanced Topics
36
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 is
  stored there
• I will show how to use a CLOB BLOBs are similar

37
Retrieving a CLOB
create table userComments( user
varchar(50), comment CLOB) )
We can also use getAsciiStream() which returns an
InputStream
ResultSet rs stmt.executeQuery(select
comment from userComments) while (rs.next)
Clob c rs.getClob(comment) Reader reader
c.getCharacterStream() doSomething(reader)
38
Inserting a CLOB
PreparedStatement pstmt con.prepareStatement(
insert into userComments values(sara,
?)) Reader reader new FileReader(fileName) p
stmt.setCharacterStream(1, reader,
Integer.MAX_VALUE) pstmt.executeUpdate()
We can also use setAsciiStream() which gets an
InputStream
39
Transactions

• Transaction more than one statement which 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 abort

40
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?
41
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

42
AutoCommit
Connection.setAutoCommit(boolean val)

• If you set AutoCommit to false, you must
  explicitly commit or rollback the transaction
  using Connection.commit() and Connection.rollback(
  )
• In order to work with LOBs, you usually have to
  set AutoCommit to false, while retrieving the
  data
• Note DDL statements in a transaction may be
  ignored or may cause a commit to occur. The
  behavior is DBMS dependent

43
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 (Exception e)
con.rollback()
44
Isolation Levels

• How do different transactions interact? Do they
  see what another has written?
• JDBC supports 4 isolation modes
• Set using Connection.setTransactionIsolation
• Oracle only implements
• TRANSACTION_SERIALIZABLE
• TRANSACTION_READ_COMMITED

45
Isolation Levels (cont.)

• TRANSACTION_SERIALIZABLE transactions are
  equivalent to serial transactions
• TRANSACTION_READ_COMMITED A transaction can only
  read values that have been committed

46
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