1 Using Java with Oracle Database

Oracle Database is a relational database that you can use to store, use, and modify data. The Java Database Connectivity (JDBC) standard is used by Java applications to access and manipulate data in relational databases.

JDBC is an industry-standard application programming interface (API) that lets you access a RDBMS using SQL from Java. JDBC is based on the X/Open SQL Call Level Interface (CLI) and complies with the Entry Level of the JDBC escape standard. Each vendor implements the JDBC Specification with its own extensions.

See Also:

http://www.oracle.com/technetwork/java/overview-141217.html

This guide shows you how to use a simple Java application to connect to Oracle Database 12c Release 1 (12.1) and access and modify data within the database.

This chapter introduces you to the Java application created in this guide, and to the tools you can use to develop the Java application in the following topics:

1.1 Using Java to Connect to Oracle Database 12c Release 1 (12.1)

JDBC is a database access protocol that enables you connect to a database and run SQL statements and queries on the database. The core Java class libraries provide the JDBC APIs, java.sql and javax.sql. However, JDBC is designed to allow vendors to supply drivers that offer the necessary specialization for a particular database.

Note:

Oracle Database 12c Release 1 (12.1) supports JDK 6 and JDK 7.

The following sections describe Oracle support for the JDBC standard:

1.1.1 Oracle JDBC Thin Driver

Oracle recommends using the JDBC Thin Driver for most requirements. JDBC-OCI is only needed for OCI-specific features. The Thin driver will work on any system that has a suitable Java virtual machine (JVM).

The JDBC Thin Driver is a pure Java, Type IV driver. It supports the JavaTM 2 Platform Standard Edition 5.0, also known as Java Development Kit (JDK) 5. It also includes support for JDK 6. It is platform-independent and does not require any additional Oracle software for client-side application development. The JDBC Thin Driver communicates with the server using SQL*Net to access Oracle Database 12c Release 1 (12.1).

You can access the Oracle-specific JDBC features and the standard features by using the oracle.jdbc package.

1.1.2 Oracle JDBC Packages

Oracle support for the JDBC API is provided through the oracle.jdbc and oracle.sql packages. These packages support all Java Development Kit (JDK) releases from 1.5 through 1.6.

oracle.sql

The oracle.sql package supports direct access to data in SQL format. This package consists primarily of classes that provide Java mappings to SQL data types and their support classes. Essentially, the classes act as Java wrappers for SQL data. The characters are converted to Java chars and, then, to bytes in the UCS-2 character set.Each of the oracle.sql.* data type classes extends oracle.sql.Datum, a superclass that includes functions and features common to all the data types. Some of the classes are for JDBC 2.0-compliant data types. In addition to data type classes, the oracle.sql package supports classes and interfaces for use with objects and collections.

oracle.jdbc

The interfaces of the oracle.jdbc package define the Oracle extensions to the interfaces in the java.sql package. These extensions provide access to Oracle SQL-format data. They also provide access to other Oracle-specific features, including Oracle performance enhancements.

The key classes and interfaces of this package provide methods that support standard JDBC features and perform tasks such as:

  • Returning Oracle statement objects

  • Setting Oracle performance extensions for any statement

  • Binding oracle.sql.* types into prepared and callable statements

  • Retrieving data in oracle.sql format

  • Getting meta information about the database and result sets

  • Defining integer constants used to identify SQL types

1.2 Using JDeveloper to Create JDBC Applications

The Java application tutorial in this guide uses Oracle JDeveloper release 11.1.1 as the integrated development environment (IDE) for developing the Java application and creating Web pages for users to view and change the data.

Oracle JDeveloper is an IDE with support for modeling, developing, debugging, optimizing, and deploying Java applications and Web services.

JDeveloper provides features for you to write and test Java programs that access the database with SQL statements embedded in Java programs. For the database, JDeveloper provides functions and features to do the following:

  • Create a connection to a database

  • Browse database objects

  • Create, edit, or delete database objects

  • Create and edit PL/SQL functions, procedures, and packages

1.2.1 JDeveloper User Interface

Oracle JDeveloper is an IDE that uses windows for various application development tools. You can display or hide any of the windows, and you can dock them or undock them to create a desktop suited to your method of working.

In addition to these tools, JDeveloper provides a range of navigators to help you organize and view the contents of your projects. Application and System navigators show you the files in your projects, and a Structure window shows you the structure of individual items.

You can arrange the windows as you choose, and can close and open them from the View menu. Figure 1-1 shows the default layout of some of the available navigators, palettes, and work areas in the JDeveloper user interface (GUI).

Figure 1-1 JDeveloper User Interface

Description of Figure 1-1 follows
Description of "Figure 1-1 JDeveloper User Interface"

See Also:

Working with Windows in the IDE, in the JDeveloper online Help

1.2.2 JDeveloper Tools

For creating a Java application, JDeveloper provides the following tools to simplify the process:

  • Structure window, which provides a tree view of all of the elements in the application currently being edited be it Java, XML, or JSP/HTML.

  • Java Visual Editor, which you can use to assemble the elements of a user interface quickly and easily.

  • JSP/HTML Visual Editor, which you can use to visually edit HTML and JSP pages.

  • Java Source Editor, which provides extensive features for helping in writing the Java code, such as distinctive highlighting for syntax and semantic errors, assistance for adding and sorting import statements, the Java Code Insight feature, and code templates.

    Note:

    The Java Code Insight feature is a facility that provides context-specific, intelligent input when creating code in the Java Source Editor. In this guide, you will see many instances of how you can use Java Code Insight to insert code.

  • Component Palette, from which you select the user interface components, such as buttons and text areas, that you want to display on your pages.

  • Property Inspector, which gives a simple way of setting properties of items such as user interface components.

Refer to Figure 1-1 to get a better idea of these tools.

1.3 Overview of Sample Java Application

This guide shows you how to create an application using Java, JDBC and Oracle ADF. In this application, you build in functions and features that:

  1. Allow users to log in and validate the user name and password.

  2. Establish a connection to the database.

  3. Query the database for data and retrieve the data using a JavaBean.

  4. Display the data using JavaServer Pages (JSP) technology.

  5. Allow users to insert, update, or delete records.

  6. Access and modify information from a master-detail application.

  7. Handle exceptions.

Note:

The application connects to the HR schema that ships with Oracle Database 12c Release 1 (12.1).

Overview of Application Web Pages (JSP Pages)

Figure 1-2 shows the relationships among the pages developed for this application.

Figure 1-2 Web Pages in the Sample Application

Description of Figure 1-2 follows
Description of "Figure 1-2 Web Pages in the Sample Application"

A brief description of the Web pages in the sample application follows:

  • index.jsp

    This is the starting page of the application. It automatically forwards the user to the login page of the application, login.jsp.

  • login.jsp

    This page allows users to log in to the application. The user name, password, and host information are validated and used to create the connection descriptor to log in to the database.

  • login_action.jsp

    This is a nonviewable page that handles the authentication of the user-supplied login details from login.jsp. If authentication is successful, the page forwards the user to employees.jsp. Otherwise, it redisplays the login.jsp page including a message.

  • employees.jsp

    This is the main page of the application. It displays a list of all the employees in the HR schema for AnyCo Corporation and allows the user to filter the list of employees using any string. It also includes links to add, edit, and delete any user data. These actions, however, are handled by other JSP pages that are created specifically for each of these tasks.

  • insert.jsp

    The link to insert employee data on the employees.jsp page redirects the user to this page. This includes a form that accepts all the details for a new employee record. The details entered on this form are processed by the insert_action.jsp page.

  • insert_action.jsp

    This is a nonviewable page that handles the insertion of data for a new employee that is entered on the insert.jsp page.

  • edit.jsp

    The link to edit employee data on the employees.jsp page redirects the user to this page. This form displays current data of a single employee in text fields, and the user can edit this information.

  • update_action.jsp

    The submit action on the edit.jsp page directs the data to this nonviewable page, which inserts the edited data into the database.

  • delete_action.jsp

    The link to delete an employee record on the employees.jsp page is handled by this nonviewable page, which deletes the employee data and forwards the user back to the employees.jsp page.

Classes

The sample application includes the following classes:

  • DataHandler.java

    This class contains all the methods that are used to implement the important functions of the sample application. It includes methods that validate user credentials, connect to the database, retrieve employee data with and without filters, insert data, update data, handle exceptions, and so on.

  • Employees.java

    This class is a JavaBean that holds a single employee record. It contains accessor methods to get and set the values of each of the record fields. It also contains accessor methods to retrieve and modify employee records.

  • JavaClient.java

    This class is used only for testing the DataHandler class.

Note:

This application is developed throughout this guide in the form of a tutorial. It is recommended, therefore, that you read these chapters in sequence.

1.4 Resources

For more information about Oracle Database 12c Release 1:

  • Visit the Oracle Technology Network page

    http://www.oracle.com/technetwork/index.html

  • Visit the Oracle Database 12c Release 1 Online Documentation Library by performing the following steps:

    • Click Start and then Programs.

    • Select Oracle Database 12c Release 1, then Get Help, and then Read Documentation.

  • Visit the Oracle Database 12c Release 1 Online Discussion forum by performing the following steps:

    • Click Start and then Programs.

    • Select Oracle Database 12c Release 1, then Get Help, and then Go To Online Forum.