JDBC

1
JDBC

• This lecture is from
• http//sunsite.tus.ac.jp/java/JDK1.1/docs/guide/jd
  bc/getstart/1.1TOC.doc.html

2
JDBC

• JDBCTM is a JavaTM API for executing SQL
  statements
• JDBC is a trademarked name and is not an acronym
• JDBC is often thought of as standing for "Java
  Database Connectivity
• JDBC is a set of classes and interfaces written
  in the Java programming language
• Makes it possible to write database applications
  using a pure Java API

3
JDBC Advantages

• Send SQL statements to any relational database,
  e.g. Oracle, Sybase
• With Java and JDBC can run on different platforms
  
• JDBC extends what can be done in Java
• Publish a web page containing an applet that uses
  information obtained from a remote database
• Allow any query string to be passed through to an
  underlying DBMS driver

4
JDBC can do

• JDBC makes it possible to do three things
• establish a connection with a database
• send SQL statements
• process the results
• You need to include in your code
• import java.sql.

5
Models

• JDBC API supports both two-tier and three-tier
  models for database access

6
JavaSoft

• JavaSoft provides three JDBC product components
  as part of the Java Developer's Kit (JDK)
• the JDBC driver manager,
• the JDBC driver test suite, and
• the JDBC-ODBC bridge.

7

• Components needed to access a DB
• Load a driver
• Connect to the database
• Send an SQL statement after created or prepared
  or called
• executeQuery
• executeUpdate
• execute
• Process result
• ResultSet

8
Driver class

• By calling the method Class.forName. This
  explicitly loads the driver class
• Class.forName ("oracle.jdbc.driver.OracleDriver")

9
Example

• Sample code
• http//sunsite.tus.ac.jp/java/JDK1.1/docs/guide/jd
  bc/getstart/SimpleSelect.doc.html

10
Opening a Connection

• Call the method DriverManager.getConnection
• method takes a string containing a URL of the
  form
• jdbc
• Connection conn DriverManager.getConnection
  ("jdbcoracleoci8_at_students", user, pass)

11
Sending SQL Statements

• Statement- -created by the method createStatement
  
• simple SQL statements with no parameters
• PreparedStatement- -created by the method
  prepareStatement
• SQL statements that take one or more parameters
  as input arguments (IN)
• Simple statements executed frequently
• CallableStatement- -created by the method
  prepareCall
• CallableStatement objects are used to execute SQL
  stored procedures

12

• First you create the statement with one of
• createStatement
• prepareStatement
• prepareCall
• Then you execute it and place in a ResultSet with
  one of
• executeQuery
• executeUpdate
• execute

13
1. createStatement

• provides three different methods for executing
  SQL statements
• executeQuery
• executeUpdate
• execute

14
1. createStatement

• executeQuery
• designed for statements that produce a single
  result set object (e.g. a table)
• Statement stmt conn.createStatement ()
• ResultSet rset stmt.executeQuery
• ("SELECT FROM employee")

15
1. createStatement

• executeUpdate
• used to execute INSERT, UPDATE, or DELETE
  statements and also SQL DDL statements create
  table, etc.
• modification of one or more columns in zero or
  more rows in a table
• return value of executeUpdate is an integer
  indicating the number of rows that were affected

16
1. createStatement

• The execute method
• used when return more than one ResultSet object,
  more than one update count, or a combination of
  ResultSet objects and update counts
• stored procedure could perform an update, then a
  select, then an update, then a select
• dynamically executing an unknown SQL string

17
2. prepareStatement

• Has its own versions of the methods executeQuery,
  executeUpdate and execute
• Statement objects do not themselves contain an
  SQL statement
• One must be provided as the argument to the
  Statement.execute methods
• CallableStatement objects inherit the
  PreparedStatement forms of these methods

18
2. prepareStatement

• Objects
• Instances of PreparedStatement contain an SQL
  statement that has already been compiled
• PreparedStatement object may have one or more IN
  parameters denoted with ?
• their execution can be faster than that of
  Statement objects

19
2. prepareStatement

• PreparedStatement pstmt con.prepareStatement(
  "UPDATE table4 SET m ? WHERE x ?")
• pstmt.setLong(1, 123456789)
• pstmt.setLong(2, 100000000)
• int rowCount pstmt.executeUpdate()
• Or alternatively, can execute with different
  values for parameters
• pstmt.setString(1, "Hi")
• for (int i 0 i
• pstmt.setInt(2, i)
• int rowCount pstmt.executeUpdate()

20
ResultSet (cursor)

• ResultSet contains all of the rows which
  satisfied the conditions in an SQL statement
• ResultSet.next method is used to move to the next
  row of the ResultSet

21
Example

• Statement stmt conn.createStatement ()
• ResultSet rset stmt.executeQuery
• ("SELECT FROM employee")
• while (rset.next ())
• System.out.println (rset.getString
  ("lname"))

22
ResultSet - Rows and Cursors

• ResultSet maintains a cursor which points to its
  current row of data
• Initially it is positioned before the first row
• If a database allows positioned updates or
  positioned deletes, the name of the cursor needs
  to be supplied as a parameter to the update or
  delete command
• cursor name can be obtained by calling the method
  getCursorName

23
ResultSet - Retrieving columns

• getXXX methods provide the means for retrieving
  column values from the current row
• Note that columns are numbered from left to right
  starting with column 1 (useful for )
• String s rs.getString("title")
• String s rs.getString(2)

24
ResultSet - Example

• Statement stmt conn.createStatement()
• ResultSet r stmt.executeQuery("SELECT a, b, c
  FROM Table1")
• while (r.next()) // print the values for the
  current row.
• int i r.getInt("a")
• String s r.getString("b")
• float f r.getFloat("c")
• System.out.println("ROW " i " " s " "
  f)

25
ResultSet - Column Info

• Information about the columns in a ResultSet is
  available by calling the method
  ResultSet.getMetaData
• object returned gives the number, types, and
  properties of its ResultSet object's columns

26
Data Types and Conversions

• JDBC driver attempts to convert the underlying
  data to the specified Java type
• Can user getString for most types

27
Transactions

• Most JDBC drivers will support transactions
• Five levels of transactions last is
  serializability
• DatabaseMetaData supplies information describing
  the level of transaction support
• Allow a value to be read before it has been
  committed ("dirty reads")
• Use setIsolationLevel to change the transaction
  isolation level. The new level will be in effect
  for the rest of the connection session
• con.setTransactionIsolation(TRANSACTION_READ_UNCO
  MMITTED)

28
JDBC at UA

• How to access databases using JDBC
• Make sure that your CLASSPATH environment
  variable contains "." and
• "C\Program Files\Oracle\ora90\jdbc\lib\classes12
  .zip". (Here "C\Program Files\Oracle\ora90" is
  the Oracle home directory.)
• Copy Test1.java to your directory.
• REMEMBER the name of the file must be the same
  as the name of the class in your program.
• Type in "javac Test1.java" in a "Command Prompt"
  window to compile.
• Type in "java Test1" in the "Command Prompt"
  window to execute.