Java Application with Oracle

1
Java Application with Oracle

• Information and Computer Science
• University of Hawaii at Manoa
• April 2004
• Sang Sup Kim

2
Database Drivers

• A JDBC driver is a middleware layer that
  translates the JDBC calls to the vendor-specific
  APIs.
• A driver is nothing but an implementation of
  various interfaces specified in java.sql and
  javax.sql package.

3
JDBC Driver Type 1JDBC-ODBC Bridge
Data Source
JAVA Application
JDBC API
JDBC-ODBC Bridge
ODBC API
ODBC Layer
4
JDBC Driver Type 2Part Java, Part Native Driver
JAVA Application
Data Source
Vender Specific API
JDBC API
JDBC Driver
5
JDBC Type 3Intermediate Database Access Server
Data Source
JAVA Application
JDBC Driver Server
JDBC API
JDBC Driver
Native Driver
6
JDBC Driver Type 4Pure Java Drivers
Data Source
JAVA Application
JDBC API
JDBC Driver
7
Loading a Database Driver and Opening Connection

• JDBC URL represents a driver, and additional
  driver-specific information to locate a database
  and to connect to it.
• jdbcltsubprotoclegtltsubnamegt
• example in Oracle
• jdbcoracleltdriver typegt_at_ltdatabasegt
• 1. jdbcoracleoci8scott/tiger_at_ics321
• 2. jdbcoraclethinscott/tiger/_at_myhost1521orc1

8
DriverManager

• Applications use the DriverManager class to
  obtain connections.

• 17 private final String DRIVER_NAME
  "oracle.jdbc.driver.OracleDriver"
• private final String CONNECTION_URL
  "jdbcoracleoci8SYSTEM/AUZIE_at_ICS321"
• 79 public Connection connect(String user,
  String pwd)
• 80 throws
  SQLException
• 81 String url "jdbcoracleoci8" user
  "/" pwd "_at_ICS321"
• 82 try
• 83 Class.forName(DRIVER_NAME)
• 84
• 85 catch (ClassNotFoundException e)
• 86 System.out.println ("Could not load the
  Oracle driver")
• 87
• 88
• 89 Connection connection DriverManager.getCo
  nnection(url)
• 90 System.out.println("Connected to "
  user)
• 91 return connection
• 92
• 93

9
Establishing Connection

• 17 private final String DRIVER_NAME
  "oracle.jdbc.driver.OracleDriver"
• 79 public Connection connect(String user,
  String pwd)
• 80 throws
  SQLException
• 81 String url "jdbcoracleoci8" user
  "/" pwd "_at_ICS321"
• 82 try
• 83 Class.forName(DRIVER_NAME)
• 84
• 85 catch (ClassNotFoundException e)
• 86 System.out.println ("Could not load the
  Oracle driver")
• 87
• 88
• 89 Connection connection DriverManager.getCo
  nnection(url)
• 90 System.out.println("Connected to "
  user)
• 91 return connection
• 92
• 93

10
Some Public Methods of Connection

• 26 public void createUser(String userName,
  String pwd) throws SQLException
• 27 Connection connection this.connect("SYSTE
  M", "AUZIE0308")
• 28 String query "CREATE USER " userName
  " IDENTIFIED BY " pwd ""
• 29 Statement stmt connection.createStatement
  ()
• 30 try
• 31 stmt.executeUpdate(query)
• 32
• 33 catch (SQLException e)
• 34 System.out.println(query)
• 35 System.out.println("Could not create a
  user.")
• 36 while (e ! null)
• 37 System.out.println("Message "
  e.getMessage())
• 38 e e.getNextException()
• 39
• 40 return
• 41
• 42 System.out.println("A User Created.")
• 43 stmt.close()
• 44

11
Creating and Executing SQL Statements

• 26 public void createUser(String userName,
  String pwd) throws SQLException
• 27 Connection connection this.connect("SYSTE
  M", "AUZIE0308")
• 28 String query "CREATE USER " userName
  " IDENTIFIED BY " pwd ""
• 29 Statement stmt connection.createStatement
  ()
• 30 try
• 31 stmt.executeUpdate(query)
• 32
• 33 catch (SQLException e)
• 34 System.out.println(query)
• 35 System.out.println("Could not create a
  user.")
• 36 while (e ! null)
• 37 System.out.println("Message "
  e.getMessage())
• 38 e e.getNextException()
• 39
• 40 return
• 41
• 42 System.out.println("A User Created.")
• 43 stmt.close()
• 44

12
Creating and Executing SQL Statements (Cont.)

• 38 public ArrayList select(Connection conn,
  String id) throws SQLException
• 39 String query "Select ID, FIRST_NAME,
  MIDDLE_NAME, LAST_NAME,"
• 40 "ADDRESS, DOB, ZIP from
  PERSON "
• 41 "WHERE PERSON.ID " "'"
  id "'"
• 42 Statement stmt conn.createStatement()
• 43 ArrayList list new ArrayList()
• 44 int count 0
• 45 try
• 46 ResultSet rs stmt.executeQuery(query)
• 47 while (rs.next())
• 48 //System.out.println("ID "
  list.add(rs.getString("ID")))
• 49 System.out.println("FIRST NAME "
  list.add(rs.getString("FIRST_NAME")))
• 50 System.out.println("MIDDLE NAME "
  list.add(rs.getString("MIDDLE_NAME")))
• 51 System.out.println("LAST NAME "
  list.add(rs.getString("LAST_NAME")))
• 52 //System.out.println("ADDRESS "
  list.add(rs.getString("ADDRESS")))
• 53 //System.out.println("DOB "
  list.add(rs.getDate("DOB")))
• 54 //System.out.println("ZIP "
  list.add(rs.getString("ZIP")))
• 55 count
• 56

13
Querying the Database

• 45 try
• 46 ResultSet rs stmt.executeQuery(query)
• 47 while (rs.next())
• 48 //System.out.println("ID "
  list.add(rs.getString("ID")))
• 49 System.out.println("FIRST NAME "
  list.add(rs.getString("FIRST_NAME")))
• 50 System.out.println("MIDDLE NAME "
  list.add(rs.getString("MIDDLE_NAME")))
• 51 System.out.println("LAST NAME "
  list.add(rs.getString("LAST_NAME")))
• 52 //System.out.println("ADDRESS "
  list.add(rs.getString("ADDRESS")))
• 53 //System.out.println("DOB "
  list.add(rs.getDate("DOB")))
• 54 //System.out.println("ZIP "
  list.add(rs.getString("ZIP")))
• 55 count
• 56
• 57
• 58 catch (SQLException e)
• 59 System.out.println("Could not
  select...")
• 60 while (e ! null)
• 61 System.out.println("Message "
  e.getMessage())
• e e.getNextException()

14
Links

• JDBC Getting Started http//java.sun.com/j2se/1.
  4.2/docs/guide/jdbc/getstart/GettingStartedTOC.fm.
  html
• Oracle JDBC FAQ http//otn.oracle.com/tech/java/
  sqlj_jdbc/htdocs/jdbc_faq.htm