JDBC

1
JDBC
2
JDBC OVERVIEW

• The JDBC API is the industry standard for
  database-independent connectivity between the
  Java programming language and a wide range of
  databases.
• The JDBC API provides a call-level API for
  SQL-based database access.
• JDBC technology allows you to use the Java
  programming language to exploit "Write Once, Run
  Anywhere" capabilities for applications that
  require access to enterprise data.

3
JDBC ARCHITECTURE

• The JDBC API contains two major sets of
  interfaces the first is the JDBC API for
  application writers, and the second is the
  lower-level JDBC driver API for driver writers.
• JDBC technology drivers fit into one of four
  categories, shown in the next slide.
• Applications and applets can access databases via
  the JDBC API using pure Java JDBC
  technology-based drivers, as shown in the next
  slide.

4
JDBC ARCHITECTURE




TYPE 1
TYPE 2
5
JDBC ARCHITECTURE

• TYPE1
• This style of driver converts JDBC calls into the
  network protocol used directly by DBMSs, allowing
  a direct call from the client machine to the DBMS
  server and providing a practical solution for
  intranet access.
• TYPE2
• This style of driver translates JDBC calls into
  the middleware vendor's protocol, which is then
  translated to a DBMS protocol by a middleware
  server. The middleware provides connectivity to
  many different databases

6
JDBC ARCHITECTURE
D
TYPE 3
TYPE 4
7
JDBC ARCHITECTURE

• TYPE3
• This style of driver converts JDBC calls into the
  network protocol used directly by DBMSs, allowing
  a direct call from the client machine to the DBMS
  server and providing a practical solution for
  intranet access.
• TYPE4
• This style of driver translates JDBC calls into
  the middleware vendor's protocol, which is then
  translated to a DBMS protocol by a middleware
  server. The middleware provides connectivity to
  many different databases.

8
JDBC Drivers

• Vendor-specific implementation of the JDBC
  interfaces
• Transparent to the database application
  developers.

9
JDBC specifications
JDBC 2.0 Specification
JDBC 2.0 Core API (java.sql package) Scrollable
ResultSets Streams
JDBC 1.0 Specification Basic database connectivity
JDBC 2.0 Standard Extension API (javax.sql
package) DataSource Connection pooling
Distributed transactions Rowsets
10
LOADING THE DRIVER

• The class oracle.jdbc.driver.OracleDriver is
  dynamically loaded into the Java runtime using
  Class.forName(...).
• Class.forName("oracle.jdbc.driver.OracleDriver")
• You need to import the package java.sql into your
  code

11
CONNECTING TO THE DATABASE

• Use the following method
• Connection DriverManager.getConnection(String
  connectionString)
• Example
• Connection conn DriverManager.getConnection(
  jdbcoraclethin_at_129.110.49.211521last,
  scott, tiger)

12
Connection String

• The format of the connection string
• URL,username,password
• The URL should be
• jdbcoraclethin_at_129.110.49.211521last
• 129.110.49.21 is Ip Address of delphi.utdallas.edu
  
• 1521 is port number for oracle
• last is oracle SID
• Let
• User Name is scott
• Password is tiger
• So the Connection string should be
• jdbcoraclethin_at_129.110.49.211521last,scott
  ,tiger

13
DRIVER MANAGER -getConnection() method
14
QUERIES Statement Object

• Executes the SQL statement
• Returns the results of the SQL statement

15
createStatement()
Example Statement stmt conn.createStatement()
16
executeQuery()

• No assumption is made on the validity of the
  query
• If the SQL executes successfully it returns a
  ResultSet object containing rows from the
  database
• If the SQL fails it will raise a SQLException

17
Executing a Statement - Example

• ResultSet rs
• stmt.executeStatement(select name from pets)
• ResultSet

Initial cursor position
next()
next()
18
ResultSet Object

• A table of data representing a database result
  set
• Maintains a cursor pointing to its current row of
  data
• Initially the cursor is positioned before the
  first row
• The next() method moves the cursor to the next
  row
• next() returns false when there are no more rows
  in the ResultSet object
• By default ResultSet object is not updatable and
  has a cursor that moves forward only

19
Basic Getter Methods of ResultSet

• int getInt(int columnIndex)
• int getInt(String columnName)
• String getString(int columnIndex)
• String getString(String columnName)
• Date getDate(int columnIndex)
• Date getDate(String columnName)

20
HANDLING ERRORS

• Connector/J Driver throws a SQLException for
• errors connecting with the database
• errors executing SQL statements
• To know more about an exception use the
  SQLException methods
• getMessage()
• getSQLState()
• getErrorCode()

21
executeUpdate() method
Returns either the row count for INSERT, UPDATE
or DELETE statements, or 0 for SQL statements
that return nothing
22
Inserting a Row - Example

• Statement stmt connection.createStatement()
• int i stmt.executeUpdate(INSERT INTO pet
  VALUES(12, minou, Gwen, cat))

23
Updating a Row - Example

• Statement stmt connection.createStatement()
• int i stmt.executeUpdate(UPDATE pet SET
  ownerLucy where owner Gwen )

24
Deleting a Row - Example

• Statement stmt connection.createStatement()
• int i stmt.executeUpdate(DELETE FROM pet WHERE
  owner Gwen )

25
Stored Function

• To create a PL/SQL stored function in your oracle
  database using jdbc
• Create a Statement Object.
• Statement stmt conn.createStatement()
• Write a string defining your function.
• String str Write your PL/SQL function here
• To create the function
• stmt.executeUpdate(str)

26
Calling PL/SQL Stored Function

• String cmd "? call YourFunction (?,?)"
• Here the function has two parameters
• Create a Callable Statement Object
• CallableStatement cstmt conn.prepareCall (cmd)
  
• (May throw a SQLException.)
• You need to register parameters before calling
  the function
• cstmt.setInt(1, 1) // first placeholder has an
  integer value of 50
• cstmt.setString(2, Some String") //second is a
  string
• To call the function
• cstmt.executeQuery()

27
Calling Procedure

• Creating a PL/SQL stored procedure is is similar
  to creating a PL/SQL stored function.
• The difference is in calling the stored
  procedure.
• String cmd call YourProcedure (?,?)"

28
Sample Code (TestProcedure.java)

• import java.sql.
• import oracle.jdbc.driver.
• import oracle.sql.
• class TestProcedure
• public static void main(String args) throws
  Exception
• Class.forName("oracle.jdbc.driver.OracleDr
  iver")
• Connection conn DriverManager.getConnect
  ion("jdbcoraclethin_at_129.110.49.211521last",
  args0,args1)
• String createProcedure "create or
  replace procedure HELLO_STOREDPROCEDURE(str IN
  varchar2) "
• "as BEGIN INSERT INTO TEST(TEST_COL)
  VALUES (str) END "
• Statement stmt conn.createStatement()
  
• stmt.executeUpdate(createProcedure)
• OracleCallableStatement cs
  (OracleCallableStatement)conn.prepareCall( "call
  HELLO_STOREDPROCEDURE (?) ")
• cs.setString(1, "Hello World!")
• cs.execute()
• stmt.close()
• cs.close()
• conn.close()

29
Example(contd.)

• To compile
• You need to log into delphi server
• Go to the folder where you have saved
  TestProcedure.java
• Run the command
• javac -classpath /oracle/binaries/jdbc/lib/classes
  12.zip TestProcedure.java
• To execute
• Run the command
• java -classpath ./oracle/binaries/jdbc/lib/classe
  s12.zip TestProcedure ltuser namegt ltpasswordgt
• Full version of the code can be download from
• http//www.utdallas.edu/skn051000/cs6360/HW-3/Tes
  tProcedure.java

30
DISCONNECTING FROM THE DATABASE

• When you are finished close the connection to the
  database to release resources
• Make sure you first close all component that use
  that connection
• Close components in the reverse order you opened
  them.
• rs.close()
• stmt.close()
• conn.close()

31
Useful Links

• http//java.sun.com/docs/books/tutorial/jdbc/basic
  s/index.html
• http//java.sun.com/j2se/1.4.2/docs/api/java/sql/p
  ackage-summary.html
• http//www.geocities.com/joao_kramer/documents/doc
  _20000919_1.htm
• http//java.sun.com/j2se/1.3/docs/guide/jdbc/getst
  art/connection.html
• http//w2.syronex.com/jmr/edu/db/oracle-and-java

32
Advanced Topics (Not needed for HW-3)
33
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

34
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?
35
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

36
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

37
Scrollable ResultSet

• Statement createStatement( int resultSetType, int
  resultSetConcurrency)
• resultSetType
• ResultSet.TYPE_FORWARD_ONLY
• -default same as in JDBC 1.0
• -allows only forward movement of the cursor
• -when rset.next() returns false, the data is no
  longer available and the result set is closed.
• ResultSet.TYPE_SCROLL_INSENSITIVE
• -backwards, forwards, random cursor movement.
• -changes made in the database are not seen in the
  result set object in Java memory.
• ResultSetTYPE_SCROLL_SENSITIVE
• -backwards, forwards, random cursor movement.
• -changes made in the database are seen in the
• result set object in Java memory.

38
Scrollable ResultSet (contd)

• resultSetConcurrency
• ResultSet.CONCUR_READ_ONLY
• This is the default (and same as in JDBC 1.0) and
  allows only data to be read from the database.
• ResultSet.CONCUR_UPDATABLE
• This option allows for the Java program to make
  changes to the database based on new methods and
  positioning ability of the cursor.
• Example
• Statement stmt conn.createStatement(
  ResultSet.TYPE_SCROLL_INSENSITIVE,
  ResultSet.CONCUR_READ_ONLY)
• ResultSetrset stmt.executeQuery( SHOW TABLES)

39
Scrollable ResultSet (contd)

• public boolean absolute(int row) throws
  SQLException
• -If the given row number is positive, this method
  moves the cursor to the given row number (with
  the first row numbered 1).
• -If the row number is negative, the cursor moves
  to a relative position from the last row.
• -If the row number is 0, an SQLException will be
  raised.
• public boolean relative(int row) throws
  SQLException
• This method call moves the cursor a relative
  number of rows, either positive or negative.
• An attempt to move beyond the last row (or before
  the first row) in the result set positions the
  cursor after the last row (or before the first
  row).
• public boolean first() throws SQLException
• public boolean last() throws SQLException
• public boolean previous() throws SQLException
• public boolean next() throws SQLException

40
Scrollable ResultSet (contd)

• public void beforeFirst() throws SQLException
• public void afterLast() throws SQLException
• public boolean isFirst() throws SQLException
• public boolean isLast() throws SQLException
• public boolean isAfterLast() throws SQLException
• public boolean isBeforeFirst() throws
  SQLException
• public int getRow() throws SQLException
• getRow() method retrieves the current row number
  The first row is number 1, the second number 2,
  and so on.

41
JDBC Usage in Industry

• Apace DbUtils (http//jakarta.apache.org/commons/d
  butils/)
• ORM (Object Relational Mappers)
• Hibernate (http//www.hibernate.org/),
• JDO (http//java.sun.com/products/jdo/),
• TopLink (http//www.oracle.com/technology/products
  /ias/toplink/index.html)

42
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)" ")
43
Using JDBC Meta Data

• Using JDBC MetaData
• Metadata data about the data
• Not How many customers have sales over
  100K? but
• How many columns have a datatype of BIT
  (boolean)?
• System-wide data
• connection.getMetaData().getDatabaseProductName(
  )
• connection.getMetaData().getDatabaseProductVersi
  on()
• Table-specific data
• resultSet.getMetaData().getColumnCount()
• When using the result, remember that
• the index starts at 1, not 0
• resultSet.getMetaData().getColumnName()

44
Using Meta Data

• private void showTable(String driver,
• String url,
• String username,
• String password,
• String tableName,
• PrintWriter out)
• try
• Class.forName(driver)
• Connection connection
• DriverManager.getConnection(url, username,
  password)
• DatabaseMetaData dbMetaData connection.getMetaDa
  ta()
• out.println("ltULgt")
• String productName dbMetaData.getDatabaseProduct
  Name()
• out.println(" ltLIgtltBgtDatabaselt/Bgt "
  productName)
• String productVersion
• dbMetaData.getDatabaseProductVersion()
• out.println(" ltLIgtltBgtVersionlt/Bgt "
  productVersion)
• out.println("lt/ULgt")

45
Using MetaData(Contd)

• Statement statement connection.createStatement()
  
• String query
• "SELECT FROM " tableName
• ResultSet resultSet statement.executeQuery(query
  )
• out.println("ltTABLE BORDER1gt")
• ResultSetMetaData resultsMetaData
  resultSet.getMetaData()
• int columnCount resultsMetaData.getColumnCount()
  
• out.println("ltTRgt")
• for(int i1 iltcolumnCount1 i)
• out.print("ltTHgt" resultsMetaData.getColumnName(i
  ))
• while(resultSet.next())
• out.println("ltTRgt")
• for(int i1 iltcolumnCount1 i)
• out.print("ltTDgt" resultSet.getString(i))
• out.println()
• out.println("lt/TABLEgt")

46
THANKYOU