Oracle® Database
2 Day + Java Developer’s Guide
11g Release 1
B28765-03
February 2012
Oracle Database 2 Day + Java Developer's Guide, 11g Release 1
B28765-03
Copyright © 2007, 2012, Oracle and/or its affiliates. All rights reserved.
Primary Authors: Deepa Aswani, Rosslynne Hefferan, Maitreyee Chaliha
Contributing Authors: Kathleen Heap, Simon Law
Contributors: Kuassi Mensah, Chris Schalk, Christian Bauwens, Mark Townsend, Paul Lo,
Venkatasubramaniam Iyer
This software and related documentation are provided under a license agreement containing restrictions on
use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your
license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,
transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse
engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is
prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If
you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it
on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data
delivered to U.S. Government customers are "commercial computer software" or "commercial technical data"
pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As
such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and
license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of
the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software
License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software or hardware is developed for general use in a variety of information management
applications. It is not developed or intended for use in any inherently dangerous applications, including
applications that may create a risk of personal injury. If you use this software or hardware in dangerous
applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other
measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages
caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of
their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks
are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,
Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced
Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products,
and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly
disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle
Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your
access to or use of third-party content, products, or services.
iii
Contents
Preface ............................................................................................................................................................... xiii
Audience..................................................................................................................................................... xiii
Documentation Accessibility................................................................................................................... xiii
Related Documents ................................................................................................................................... xiv
Conventions ............................................................................................................................................... xiv
1 Using Java with Oracle Database
Using Java to Connect to Oracle Database .......................................................................................... 1-1
Oracle JDBC Thin Driver................................................................................................................... 1-2
Oracle JDBC OCI Driver ................................................................................................................... 1-2
Oracle JDBC Packages ....................................................................................................................... 1-2
Using JDeveloper to Create JDBC Applications ................................................................................ 1-3
JDeveloper User Interface................................................................................................................. 1-3
JDeveloper Tools ................................................................................................................................ 1-4
Overview of Sample Java Application................................................................................................. 1-5
Advanced Application Development Using Developer Frameworks ........................................... 1-7
2 Getting Started with the Application
What You Need to Install ........................................................................................................................ 2-1
Oracle Database Server...................................................................................................................... 2-1
Modifying the HR Schema for the JDBC Application........................................................... 2-1
Oracle Database Client ...................................................................................................................... 2-2
J2SE or JDK.......................................................................................................................................... 2-2
Integrated Development Environment........................................................................................... 2-3
Web Server .......................................................................................................................................... 2-3
Verifying the Oracle Database Client Installation ............................................................................ 2-4
Checking Installed Directories and Files ........................................................................................ 2-4
Checking the Environment Variables ............................................................................................. 2-4
Determining the JDBC Driver Version............................................................................................ 2-4
Installing Oracle JDeveloper ................................................................................................................. 2-5
JDeveloper Studio Edition: Base Installation and Full Installation............................................. 2-5
Steps to Install JDeveloper................................................................................................................ 2-6
Starting JDeveloper............................................................................................................................ 2-6
iv
3 Connecting to Oracle Database
Connecting to Oracle Database from JDeveloper.............................................................................. 3-1
JDeveloper Connection Navigator................................................................................................... 3-1
Creating a Database Connection...................................................................................................... 3-2
Browsing the Data Using the Connection Navigator ................................................................... 3-3
Setting Up Applications and Projects in JDeveloper........................................................................ 3-5
Using the JDeveloper Application Navigator................................................................................ 3-6
Creating an Application and a Project............................................................................................ 3-6
Viewing the Javadoc and Source Code Available in the Project Scope .................................... 3-7
Connecting to Oracle Database from a Java Application ................................................................. 3-8
Overview of Connecting to Oracle Database................................................................................. 3-8
Specifying Database URLs................................................................................................................ 3-9
Using the Default Service Feature of the Oracle Database Client..................................... 3-10
Creating a Java Class in JDeveloper............................................................................................. 3-11
Java Libraries ................................................................................................................................... 3-12
Overview of the Oracle JDBC Library .................................................................................. 3-12
Overview of the JSP Runtime Library .................................................................................. 3-12
Adding JDBC and JSP Libraries.................................................................................................... 3-12
Importing JDBC Packages.............................................................................................................. 3-13
Declaring Connection-Related Variables..................................................................................... 3-13
Creating the Connection Method ................................................................................................. 3-14
4 Querying for and Displaying Data
Overview of Querying for Data in Oracle Database......................................................................... 4-1
SQL Statements................................................................................................................................... 4-1
Query Methods for the Statement Object ....................................................................................... 4-2
Result Sets ........................................................................................................................................... 4-2
Features of ResultSet Objects .................................................................................................... 4-3
Summary of Result Set Object Types....................................................................................... 4-3
Querying Data from a Java Application .............................................................................................. 4-4
Creating a Method in JDeveloper to Query Data.......................................................................... 4-4
Testing the Connection and the Query Methods .......................................................................... 4-5
Creating JSP Pages ................................................................................................................................... 4-7
Overview of Page Presentation........................................................................................................ 4-7
JSP Tags ........................................................................................................................................ 4-8
Scriptlets....................................................................................................................................... 4-8
HTML Tags.................................................................................................................................. 4-8
HTML Forms ............................................................................................................................... 4-8
Creating a Simple JSP Page............................................................................................................... 4-8
Adding Static Content to a JSP Page ............................................................................................... 4-9
Adding a Style Sheet to a JSP Page............................................................................................... 4-10
Adding Dynamic Content to the JSP Page: Database Query Results ......................................... 4-11
Adding a JSP useBean Tag to Initialize the DataHandler Class............................................... 4-11
Creating a Result Set....................................................................................................................... 4-12
Adding a Table to the JSP Page to Display the Result Set......................................................... 4-14
Filtering a Query Result Set ................................................................................................................ 4-15
Creating a Java Method for Filtering Results............................................................................. 4-15
v
Testing the Query Filter Method .................................................................................................. 4-16
Adding Filter Controls to the JSP Page........................................................................................ 4-17
Displaying Filtered Data in the JSP Page..................................................................................... 4-18
Adding Login Functionality to the Application.............................................................................. 4-19
Creating a Method to Authenticate Users................................................................................... 4-19
Creating a Login Page .................................................................................................................... 4-21
Preparing Error Reports for Failed Logins.................................................................................. 4-21
Creating the Login Interface.......................................................................................................... 4-22
Creating a JSP Page to Handle Login Action............................................................................. 4-23
Testing the JSP Page.............................................................................................................................. 4-24
5 Updating Data
Creating a JavaBean ................................................................................................................................. 5-1
Creating a JavaBean in JDeveloper.................................................................................................. 5-1
Defining the JavaBean Properties and Methods............................................................................ 5-2
Updating Data from a Java Class .......................................................................................................... 5-4
Creating a Method to Identify an Employee Record .................................................................... 5-4
Creating a Method to Update Employee Data............................................................................... 5-5
Adding a Link to Navigate to an Update Page.............................................................................. 5-8
Creating a JSP Page to Edit Employee Data................................................................................... 5-9
Creating a JSP Page to Handle an Update Action...................................................................... 5-11
Inserting an Employee Record............................................................................................................ 5-12
Creating a Method to Insert Data ................................................................................................. 5-12
Adding a Link to Navigate to an Insert Page.............................................................................. 5-14
Creating a JSP Page to Enter New Data....................................................................................... 5-14
Creating a JSP Page to Handle an Insert Action......................................................................... 5-16
Deleting an Employee Record ............................................................................................................ 5-17
Creating a Method for Deleting Data........................................................................................... 5-17
Adding a Link to Delete an Employee......................................................................................... 5-18
Creating a JSP Page to Handle a Delete Action.......................................................................... 5-19
Exception Handling .............................................................................................................................. 5-19
Adding Exception Handling to Java Methods............................................................................ 5-20
Creating a Method for Handling Any SQLException ............................................................... 5-21
Navigation in the Sample Application ............................................................................................. 5-21
Creating a Starting Page for an Application ............................................................................... 5-22
6 Enhancing the Application: Advanced JDBC Features
Using Dynamic SQL ................................................................................................................................ 6-1
Using OraclePreparedStatement...................................................................................................... 6-1
Using OracleCallableStatement ....................................................................................................... 6-2
Using Bind Variables......................................................................................................................... 6-2
Calling Stored Procedures ...................................................................................................................... 6-3
Creating a PL/SQL Stored Procedure in JDeveloper ................................................................... 6-4
Creating a Method to Use the Stored Procedure........................................................................... 6-5
Allowing Users to Choose the Stored Procedure.......................................................................... 6-6
Calling the Stored Procedure from the Application ..................................................................... 6-8