CS276 Advanced Oracle Using Java

1
CS276 Advanced Oracle Using Java

• Chapter 3
• The Basics of JDBC Programming

2
The Oracle JDBC Drivers

• Thin driver
• OCI driver
• Server-side internal driver
• Server-side Thin driver

3
Importing the JDBC Packages

• There are two sets of JDBC packages
• The standard JDBC packages from Sun Microsystems
• Enable your Java programs to access the basic
  features of most databases, including
• Oracle
• SQL Server
• DB2
• MySQL
• Access
• Oracles extension packages
• Enable your programs to access all of the Oracle
  specific features, as well as the Oracle specific
  performance

4
Importing the JDBC Packages

• To use JDBC packages in your programs you should
• import java.sql.

5
Registering the Oracle JDBC Drivers

• There are two ways to register the Oracle JDBC
  drivers
• The First is to use the forName( ) method of the
  class java.lang.Class
• Class.forName(oracle.jdbc.OracleDriver)
• The Second way is to use the registerDriver( ) of
  the java.sql.DriverManager class
• DriverManager.registerDriver(
• new oracle.jdbc.OracleDriver( ))

6
Opening a Database Connection

• Connecting to the Database Using the
  getConnection() Method of the DriverManager Class
• DriverManager.getConnection(URL, username,
  password)

7
Opening a Database ConnectionConnecting to the
Database Using the getConnection() Method of the
DriverManager Class

• The Database URL
• driver_name_at_driver_information
• driver_name
• jdbcoraclethin
• jdbcoracleoci
• jdbcoracleoci8
• jdbcoracleoci7

8
Opening a Database ConnectionConnecting to the
Database Using the getConnection() Method of the
DriverManager Class

• driver_information
• host_nameportdatabase_SID for Oracle JDBC Thin
  driver
• For all the Oracle drivers may also use Oracle
  Net keyword_value pairs
• (description(address(hosthost_name)(protocoltc
  p)
• (portport))(connect_data(siddatabase_SID)))

9
Opening a Database ConnectionConnecting to the
Database Using the getConnection() Method of the
DriverManager Class

• host_name the name of the machine on which the
  database ia running
• port The port number on which the Oracle Net
  database listener waits for requests 1521 is the
  default port number.
• database_SID for Oracle JDBC Thin driver

10
Opening a Database ConnectionConnecting to the
Database Using the getConnection() Method of the
DriverManager Class

• For example
• Connection myConnection DriverManager.getConnect
  ion(jdbcoracleoci_at_
• (description(address(hostlocalhost)
• (protocoltcp)(port1521))(connect_data(sidORCL
  ))),
• scott,
• tiger

11
Opening a Database ConnectionConnecting to the
Database Using an Oracle Data Source

• There are three steps that must performed to use
  an Oracle Data Source
• Create an Oracle data source object of the
• oracle.jdbc.pool.OracleDataSource class
• Set the Oracle data source object attributes
  using set methods
• Connect to the database via the Oracle data
  source object using getConnection() method

12
Opening a Database ConnectionConnecting to the
Database Using an Oracle Data Source

• Step 1 Create an Oracle data source object of
  the
• oracle.jdbc.pool.OracleDataSource class
• OracleDataSource myDataSourcenew
  OracleDataSource()

13
Opening a Database ConnectionConnecting to the
Database Using an Oracle Data Source

• Step 2
• Set the Oracle data source object attributes
  using set methods
• The oracle.jdbc.pool.OracleDataSource class
  actually implements interface provided with JDBC.
  
• The javax.sql.DataSource interface defines a
  number of attributes, which are listed in Table
  3-1, page 93
• The oracle.jdbc.pool.OracleDataSource class
  provides an additional set of attributes( See
  Table 3-2, page94)

14
Oracle Data Source Attributes ( Table 3-1, page
93)
15
Oracle Data Source Attributes( Table 3-1, page
93)
16
oracle.jdbc.pool.OracleDataSource class
additional set of attributes( See Table 3-2,
page94)
17
Opening a Database ConnectionConnecting to the
Database Using an Oracle Data Source

• Examples using set methods
• myDataSource.setServerName(localhost)
• myDataSource.setDatabaseName(ORCL)
• myDataSource.setDriverType(Thin)
• myDataSource.setNetworkProtocol(tcp)
• myDataSource.setPortNumber(1521)
• myDataSource.setUser(scott)
• myDataSource.setPassword(tiger)

18
Opening a Database ConnectionConnecting to the
Database Using an Oracle Data Source

• Examples using get methods
• String serverName myDataSource.getServerName()
• String databaseName myDataSource.getDatabaseName(
  )
• String driverType myDataSource.getDriverType()
• String networkProtocol
• myDataSource.getNetworkProtocol()
• int portNumber myDataSource.getPortNumber()

19
Opening a Database ConnectionConnecting to the
Database Using an Oracle Data Source

• Step 3 Connect to the database via the Oracle
  data source object using getConnection() method
• Connection myConnection myDataSource.getConnecti
  on()
• You can pass a username and password as
  parameters
• Connection myConnection myDataSource.getConnecti
  on(store_user, store_password) In this case,
  the username and password will override scott and
  tiger, previously set in myDataSource

20
Opening a Database Connection

• // declare Connection and Statement objects
• Connection myConnection null
• Statement myStatement null
• try
• // register the Oracle JDBC drivers
• DriverManager.registerDriver(
• new oracle.jdbc.OracleDriver()
• )
• // create a Connection object, and connect
  to the database
• // as store_user using the Oracle JDBC Thin
  driver
• myConnection DriverManager.getConnection(
• "jdbcoraclethin_at_localhost1521ORCL",
• "store_user",
• "store_password"
• )

21
Opening a Database Connection

• // declare Connection and Statement objects
• Connection myConnection null
• Statement myStatement null
• try
• // register the Oracle JDBC drivers
• DriverManager.registerDriver(
• new oracle.jdbc.OracleDriver() )

22
Opening a Database Connection

• // create a Connection object, and connect
  to the database
• // as store_user using the Oracle JDBC Thin
  driver
• myConnection DriverManager.getConnection(
• "jdbcoraclethin_at_localhost1521ORCL",
• "store_user",
• "store_password"
• )

23
Perform DML statements

• // create a Statement object
• myStatement myConnection.createStatement()
  
• // create variables and objects used to
  represent
• // column values
• int id 6
• String firstName "Jason"
• String lastName "Red"
• java.sql.Date dob new java.sql.Date(69,
  1, 1)
• java.sql.Time dobTime
• java.sql.Timestamp dobTimestamp
• String phone "800-555-1216"

24
Perform DML statements

• // perform SQL INSERT statement to add a
  new row to the
• // customers table using the values set in
  the previous
• // step - the executeUpdate() method of the
  Statement
• // object is used to perform the INSERT
• myStatement.executeUpdate(

25
Perform DML statements

• "INSERT INTO customers "
• "(id, first_name, last_name, dob, phone)
  VALUES ("
• id ", '" firstName "', '"
  lastName "', "
• "TO_DATE('" dob "', 'YYYY, MM, DD'),
  '" phone "')"
• )
• System.out.println("Added row to customers
  table")

26
Perform DML statements

• // perform SQL UPDATE statement to modify the
  first_name
• // column of customer 1
• firstName "Jean"
• myStatement.executeUpdate(
• "UPDATE customers "
• "SET first_name '" firstName "' "
• "WHERE id 1"
• )

27
Perform DML statements

• System.out.println("Updated row in
  customers table")
• // perform SQL DELETE statement to remove
  customer 5
• myStatement.executeUpdate(
• "DELETE FROM customers "
• "WHERE id 5"
• )
• System.out.println("Deleted row row from
  customers table")

28
Control Transactions

• // disable auto-commit mode
• myConnection.setAutoCommit(false)
• // commit any chages
• myConnection.commit()
• // rollback the changes made to the database
• myConnection.rollback()

29
Retrieving Rows from the DataBase

• Because a SELECT statement may return more than
  one row, executeQuery() returns an object that
  stores the rows - ResultSet object
• There are Three steps to read rows from the
  database
• Step 1 Create a ResultSet object, and populate it
  using a SELECT statement
• Step 2 Read the column values from the ResultSet
  object using get methods
• Step 3 Close the ResultSet object

30
Use ResultSet objects to retrieve rowsStep 1
Create and Populate a ResultSet object

• // create a ResultSet object, and populate it
  with the
• // result of a SELECT statement that
  retrieves the
• // id, first_name, last_name, dob, and
  phone columns
• // for all the rows from the customers
  table - the
• // executeQuery() method of the Statement
  object is used
• // to perform the SELECT

31
Use ResultSet objects to retrieve rowsStep 1
Create and Populate a ResultSet object

• ResultSet customerResultSet
  myStatement.executeQuery(
• "SELECT id, first_name, last_name, dob,
  phone "
• "FROM customers"
• )
• System.out.println("Retrieved rows from
  customers table")

32
Use ResultSet objects to retrieve rowsStep 2
Read the Column Values from the ResultSet object

• To read the column values for the rows stored in
  a ResultSet object, the ResultSet class provides
  a series of get methods.
• Before, you need to understand how the data types
  used to represent values in Oracle may be mapped
  to compatible Java data types

33
Oracle and Java Types
34
Oracle and Java Types

• From this table, you can see that an Oracle
  INTEGER is compatible with a Java int.
• So, the id column (INTEGER) of the customers
  table may be stored in a Java int .
• Similarly, the first_name, last_name, and phone
  column values may be stored in Java String
  variables

35
Oracle and Java Types

• The Oracle DATE type stores a year, month, day,
  hour, minute, and second.
• You may use
• java.sql.Date to store the date part of the dob
  column value
• java.sql.Time to store the time part
• java.sql.Timestamp to store both the date and the
  time parts

36
Oracle and Java Types

• Examples how to declare Java variables
• int id 0
• String firstName null
• String lastname null
• java.sql.Date dob null
• String phone null

37
Oracle and Java Types

• The int and String types are part of the core
  Java language
• java,.sql.Date ia part of JDBC
• However, JDBC doesnt cover all types used by
  Oracle
• You must use oracle.sql.ROWID type to store
  Oracle ROWID
• So, Oracle provides a number of additional types
  in oracle.sql package ( later in this chapter)

38
Use the get methods to Read Column Values

• // loop through the rows in the ResultSet object
  using the
• // next() method, and use the get methods
  to read the values
• // retrieved from the database columns
• while (customerResultSet.next())
• id customerResultSet.getInt("id")
• firstName customerResultSet.getString("f
  irst_name")

39
Use the get methods to Read Column Values

• lastName customerResultSet.getString("la
  st_name")
• dob customerResultSet.getDate("dob")
• dobTime customerResultSet.getTime("dob")
  
• dobTimestamp customerResultSet.getTimest
  amp("dob")
• phone customerResultSet.getString("phone
  ")

40
Use the get methods to Read Column Values

• System.out.println("id " id)
• System.out.println("firstName "
  firstName)
• System.out.println("lastName "
  lastName)
• System.out.println("dob " dob)
• System.out.println("dobTime "
  dobTime)
• System.out.println("dobTimestamp "
  dobTimestamp)
• System.out.println("phone " phone)
• // end of while loop
• // close this ResultSet object using the
  close() method
• customerResultSet.close()

41
Perform DDL statements

• // perform a SQL DDL CREATE TABLE statement to
  create a new table
• // that may be used to store customer
  addresses
• myStatement.execute(
• "CREATE TABLE addresses ("
• " id INTEGER CONSTRAINT addresses_pk
  PRIMARY KEY,"
• " customer_id INTEGER CONSTRAINT
  addresses_fk_customers "

42
Perform DDL statements

• " REFERENCES customers(id),"
• " street VARCHAR2(20) NOT NULL,"
• " city VARCHAR2(20) NOT NULL,"
• " state CHAR(2) NOT NULL"
• ")"
• )

43
Closing Your JDBC Objects

• myStatement.close()
• myConnection.close()

44
Example ProgramBasicExample1.java

• The program performs the following tasks
• BasicExample1.java shows how to
• - import the JDBC packages
• - load the Oracle JDBC drivers
• - connect to a database
• - perform DML statements
• - control transactions
• - use ResultSet objects to retrieve rows
• - use the get methods
• - perform DDL statements

45
How to use prepared SQL statements

• // create a PreparedStatement object
• PreparedStatement myPrepStatement
  myConnection.prepareStatement(
• "INSERT INTO products "
• "(id, type_id, name, description, price)
  VALUES ("
• "?, ?, ?, ?, ?"
• ")"
• )

46
How to use prepared SQL statements

• There are two things
• The preparedStatement() method is used to specify
  the SQL statement
• Qustion mark characters (?) are used to indicate
  the positions where you will later provide to be
  used when the SQL statement is actually run

47
How to use prepared SQL statements

• The following example features a loop that shows
  the use of set methods to bind the attributes of
  the Product objects in productArray to the
  PreparedStatement object.
• The execute metod is used to run the SQL statement

48
How to use prepared SQL statements

• for (int counter 0 counter lt
  product_array.length counter )
• myPrepStatement.setInt(1, product_arraycounter.
  id)
• myPrepStatement.setInt(2, product_arraycounter.
  typeId)
• myPrepStatement.setString(3, product_arraycounte
  r.name)
• myPrepStatement.setString(4, product_arraycounter
  .description)
• myPrepStatement.setDouble(5, product_arraycounter
  .price)
• myPrepStatement.execute()
• // end of for loop

49
How to use prepared SQL statements

• The program BasicExample2.java contains the
  statements shown in this section

50
The Oracle JDBC Extensions

• oracle.sql Contains the classes that support
  all Oracle types
• import oracle.sql
• oracle.jdbc Contains the interfaces that
  support access to an Oracle database
• import oracle.jdbc

51
The Oracle JDBC ExtensionsThe oracle.sql package
52
The Oracle JDBC ExtensionsThe oracle.sql package
The oracle.sql.NUMBER Class oracle.sql.NUMBER id
new oracle.sql.NUMBER(6) int idInt
id.intValue() example for floating point
number oracle.sql.NUMBER id new
oracle.sql.NUMBER(19.95)
53
The Oracle JDBC ExtensionsThe oracle.sql package
The oracle.sql.NUMBER Class You can use following
examples with 5 methods from this class float
priceFloat price.floatValue() double
priceDouble price.doubleValue() java.math.BigDe
cimal priceBigDec price.bigDecimalValue()
string priceString price.stringValue()
54
The Oracle JDBC ExtensionsThe oracle.sql package

• The oracle.sql.CHAR Class
• There are two steps you must folow to create your
  own oracle.sql.CHAR object
• Create an oracle.sql.CharacterSet object
  containig the character set you wish to use
• Create an oracle.sql.CHAR object using previous
  oracle.sql.CharacterSet object to specify the
  character set.

55
The Oracle JDBC ExtensionsThe oracle.sql package

• The oracle.sql.CHAR Class
• Create an oracle.sql.CharacterSet object
  containig the character set you wish to use
• oracle.sql.CharacterSet myCharSet
  CharacterSet.make(CharacterSet.US7ASCII_CHARSET)

56
The Oracle JDBC ExtensionsThe oracle.sql package
The oracle.sql.CHAR Class 2.Create an
oracle.sql.CHAR object oracle.sql.CHAR firstName
new oracle.sql.CHAR( Jason,
myCharSet) String firstNameString
firstName.stringValue() System.out.println(first
NameString firstNameString)
57
The Oracle JDBC ExtensionsThe oracle.sql package
The oracle.sql.Date Class is compatible with the
database DATE type oracle.sql.DATE dob new
oracle.sql.DATE( 1969-02-01
135412) String dobString dob.stringValue()
System.out.println(firstNameString
firstNameString)
58
The Oracle JDBC ExtensionsThe oracle.sql package
The oracle.sql.ROWIDClass is compatible with the
database ROWID type oracle.sql.ROWID ROWID
59
The Oracle JDBC ExtensionsThe oracle.JDBC Package
The Classes and Interfaces of the oracle.jdbc
Package ( See Table, page 129).
60
The Oracle JDBC ExtensionsThe oracle.JDBC Package
61
The Oracle JDBC ExtensionsThe oracle.JDBC Package
62
The Oracle JDBC ExtensionsThe oracle.JDBC Package
63
The Oracle JDBC ExtensionsThe oracle.JDBC Package
64
The Oracle JDBC ExtensionsThe oracle.JDBC Package
65
The Oracle JDBC ExtensionsThe oracle.JDBC Package
66
The Oracle JDBC ExtensionsThe oracle.JDBC Package
Using an OraclePreparedStatement Object //
create an OraclePreparedStatement object
OraclePreparedStatement myPrepStatement
(OraclePreparedStatement) myConnection.prepareStat
ement( "INSERT INTO customers "
"(id, first_name, last_name, dob, phone)
VALUES (" "?, ?, ?, ?, ?"
")" )
67
The Oracle JDBC ExtensionsThe oracle.JDBC Package
Using an OraclePreparedStatement Object //
bind the objects to the OraclePreparedStatement
using the // appropriate set methods
myPrepStatement.setNUMBER(1, id)
myPrepStatement.setCHAR(2, firstName)
myPrepStatement.setCHAR(3, lastName)
myPrepStatement.setDATE(4, dob) // set the
phone column to NULL myPrepStatement.setNull
(5, OracleTypes.CHAR)
68
The Oracle JDBC ExtensionsThe oracle.JDBC Package
Using an OraclePreparedStatement Object // run
the PreparedStatement myPrepStatement.execut
e() System.out.println("Added row to
customers table")
69
The Oracle JDBC ExtensionsThe oracle.JDBC Package
Using an OracleResultSet Object // retrieve
the ROWID, id, first_name, last_name, dob, and
// phone columns for this new row using an
OracleResultSet // object Statement
myStatement myConnection.createStatement()

70
The Oracle JDBC ExtensionsThe oracle.JDBC Package
OracleResultSet customerResultSet
(OracleResultSet) myStatement.executeQuery(
"SELECT ROWID, id, first_name, last_name,
dob, phone " "FROM customers "
"WHERE id 6" )
System.out.println("Retrieved row from customers
table")
71
The Oracle JDBC ExtensionsThe oracle.JDBC Package
Using an OracleResultSet Object // declare an
oracle.sql.ROWID object to store the ROWID, and
// an oracle.sql.CHAR object to store the
phone column oracle.sql.ROWID rowid
oracle.sql.CHAR phone new oracle.sql.CHAR("",
myCharSet)
72
The Oracle JDBC ExtensionsThe oracle.JDBC Package
Using an OracleResultSet Object // display the
column values for row using the // get
methods to read the values while
(customerResultSet.next()) rowid
customerResultSet.getROWID("ROWID") id
customerResultSet.getNUMBER("id")
firstName customerResultSet.getCHAR("first_name"
) lastName customerResultSet.getCHAR("l
ast_name") dob customerResultSet.getDAT
E("dob") phone customerResultSet.getCHA
R("phone") (see next slide)
73
The Oracle JDBC ExtensionsThe oracle.JDBC Package
Using an OracleResultSet Object
System.out.println("rowid "
rowid.stringValue()) System.out.println("
id " id.stringValue())
System.out.println("firstName " firstName)
System.out.println("lastName "
lastName) System.out.println("dob "
dob.stringValue()) System.out.println("ph
one " phone) // end of while loop
74
Example ProgramBasicExample3.java