CHAPTER 3

ORACLE JAVA DATABASE CONNECTIVITY

CHAPTER CONTRIBUTOR: Dr. K. Ruth Ramya

3.1 INTRODUCTION TO ORACLE JAVA DATABASE CONNECTIVITY

Java Database Connectivity (JDBC) is an application programming interface (API) for Java that
allows Java applications to access and manipulate data stored in relational databases. JDBC provides a
standard way for Java applications to connect to different databases, regardless of the specific
database vendor or product.
There are many reasons why you might want to use JDBC in your Java applications:

Portability: JDBC allows you to write Java applications that can access data stored in a variety of
databases, without having to write separate code for each database.
Ease of use: JDBC provides a simple and easy-to-use API for accessing and manipulating
database data.
Performance: JDBC is a high-performance API that can efficiently access and manipulate data in
large databases.
Pluggability: JDBC is a pluggable API, which means that you can use different JDBC drivers to
connect to different databases.

JDBC works by providing a set of classes and interfaces that represent the different components
of a database connection, such as the database connection, the SQL statement, and the result set.
These classes and interfaces allow Java applications to interact with the database in a standard way,
regardless of the specific database vendor or product.

The core components of JDBC are:

Driver manager: The driver manager is responsible for loading the appropriate JDBC driver for
the database that you want to connect to.
Connection: A connection represents a connection to a database. You use a connection to execute
SQL statements against the database.
Statement: A statement represents an SQL statement that you want to execute against the
database.
ResultSet: A result set contains the results of an SQL query.
 How do I use JDBC?

To use JDBC, you need to follow these steps:

1. Load the JDBC driver for the database that you want to connect to.
2. Establish a connection to the database.
3. Create a statement object.
4. Execute an SQL statement using the statement object.
5. Process the results of the SQL statement.
6. Close the connection to the database.

3.2 JAVA DATABASE CONNECTIVITY WITH ORACLE

To connect java application with the oracle database, we need to follow 5 following steps. In this
example, we are using Oracle 10g as the database. So we need to know following information for the
oracle database:
Driver class: The driver class for the oracle database is oracle.jdbc.driver.OracleDriver.
Connection URL: The connection URL for the oracle10G database is
jdbc:oracle:thin:@localhost:1521:xe where jdbc is the API, oracle is the database, thin is the driver,
localhost is the server name on which oracle is running, we may also use IP address, 1521 is the port
number and XE is the Oracle service name. You may get all these information from the tnsnames.ora
file.
Username: The default username for the oracle database is system.
Password: It is the password given by the user at the time of installing the oracle database.

Create a Table
Before establishing connection, let's first create a table in oracle database. Following is the SQL
query to create a table.
create table emp(id number(10),name varchar2(40),age number(3));

Example to Connect Java Application with Oracle database

In this example, we are connecting to an Oracle database and getting data from emp table. Here,
system and oracle are the username and password of the Oracle database.

import java.sql.*;
class OracleCon{
public static void main(String args[]){
try{
//step1 load the driver class
Class.forName("oracle.jdbc.driver.OracleDriver");

//step2 create the connection object

Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");

//step3 create the statement object

Statement stmt=con.createStatement();

//step4 execute query

ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));

//step5 close the connection object

con.close();

}catch(Exception e){ System.out.println(e);}

}
}
download this example
The above example will fetch all the records of emp table.

To connect java application with the Oracle database ojdbc14.jar file is required to be loaded.

download the jar file ojdbc14.jar

Two ways to load the jar file:
paste the ojdbc14.jar file in jre/lib/ext folder
set classpath
1) paste the ojdbc14.jar file in JRE/lib/ext folder:
Firstly, search the ojdbc14.jar file then go to JRE/lib/ext folder and paste the jar file here.
2) set classpath:
There are two ways to set the classpath:
temporary
 permanent
How to set the temporary classpath:
Firstly, search the ojdbc14.jar file then open command prompt and write:
C:>set classpath=c:\folder\ojdbc14.jar;.;
How to set the permanent classpath:
Go to environment variable then click on new tab. In variable name write classpath and in
variable value paste the path to ojdbc14.jar by appending ojdbc14.jar;.; as C:\oraclexe\app\oracle\
product\10.2.0\server\jdbc\lib\ojdbc14.jar;.;

3.3 CONNECTIONS AND SECURITY

Database administrators can gather together a set of capabilities and permissions and then grant
those privileges and permissions to users using the sophisticated Oracle roles feature in Oracle
Database. As a result, it becomes easier to manage users and access control because fewer users will
require individualized privileges.
3.4 Data Sources
Data sources are standard, general-use objects for specifying databases or other resources to use.
The JDBC 2.0 extension application programming interface (API) introduced the concept of data
sources. For convenience and portability, data sources can be bound to Java Naming and Directory
Interface (JNDI) entities, so that you can access databases by logical names.

The data source facility provides a complete replacement for the previous JDBC DriverManager
facility. You can use both facilities in the same application, but it is recommended that you transition
your application to data sources.

Overview of Oracle Data Source Support for JNDI

The JNDI standard provides a way for applications to find and access remote services and
resources. These services can be any enterprise services. However, for a JDBC application, these
services would include database connections and services.

JNDI enables an application to use logical names in accessing these services, removing vendor-
specific syntax from application code. JNDI has the functionality to associate a logical name with a
particular source for a desired service.

All Oracle JDBC data sources are JNDI-referenceable. The developer is not required to use this
functionality, but accessing databases through JNDI logical names makes the code more portable.
 eatures and Properties of Data Sources
By using the data source functionality with JNDI, you do not need to register the vendor-specific
JDBC driver class name and you can use logical names for URLs and other properties. This ensures
that the code for opening database connections is portable to other environments.

The DataSource Interface and Oracle Implementation

A JDBC data source is an instance of a class that implements the standard javax.sql.DataSource
interface:

public interface DataSource

{
Connection getConnection() throws SQLException;
Connection getConnection(String username, String password)
throws SQLException;
...
}
Oracle implements this interface with the OracleDataSource class in the oracle.jdbc.pool package.
The overloaded getConnection method returns a connection to the database.

Fig 3.1 Connection procedure to Oracle database server

To use other values, you can set properties using appropriate setter methods. For alternative user
names and passwords, you can also use the getConnection method that takes these parameters as
input. This would take priority over the property settings.
3.4 DATA SOURCES FOR DATA BASE CONNECTIVITY

Table 8-1 Standard Data Source Properties

Name Type Description
databaseName String Name of the particular database on the server.
dataSourceName String Name of the underlying data source class. For connection pooling,
this is an underlying pooled connection data source class. For distributed transactions, this is an
underlying XA data source class.
description String Description of the data source.
 networkProtocol String Network protocol for communicating with the server. For Oracle, this
applies only to the JDBC Oracle Call Interface (OCI) drivers and defaults to tcp.
password String Password for the connecting user.
portNumberint Number of the port where the server listens for requests
serverNameString Name of the database server
user String Name for the login
Table 3-1 Oracle Extended Data Source Properties
Note:
For security reasons, there is no getPassword() method.
3.5 CREATE DATA SOURCE NAME

Name Type Description

connectionCacheName String Specifies the name of the cache. This cannot be changed
after the cache has been created.
connection¬Cache-Properties java.util.Properties Specifies properties for implicit
connection cache.
connectionCachingEnabled Boolean Specifies whether implicit connection cache is in
use.
connectionProperties java.util.Properties Specifies the connection properties.
driverType String Specifies Oracle JDBC driver type. It can be one of oci, thin, or kprb.
fastConnectionFailoverEnabled Boolean Specifies whether Fast Connection Failover
is in use.
implicitCachingEnabled Boolean Specifies whether the implicit statement connection
cache is enabled.
loginTimeout int Specifies the maximum time in seconds that this data source will wait
while attempting to connect to a database.
logWriter java.io.PrintWriter Specifies the log writer for this data source.
maxStatements int Specifies the maximum number of statements in the application
cache.
serviceName String Specifies the database service name for this data source.
tnsEntry String Specifies the TNS entry name. The TNS entry name corresponds to the TNS
entry specified in the tnsnames.ora configuration file.
Enable this OracleXADataSource property when using the Native XA feature with the OCI
driver, to access Oracle pre-8.1.6 databases and later. If the tnsEntry property is not set when using
the Native XA feature, then a SQLException with error code ORA-17207 is thrown
url String Specifies the URL of the database connection string. Provided as a convenience, it
can help you migrate from an older Oracle Database. You can use this property in place of the Oracle
tnsEntry and driverType properties and the standard portNumber, networkProtocol, serverName, and
databaseName properties.
nativeXA Boolean Allows an OracleXADataSource using the Native XA feature with
the OCI driver, to access Oracle pre-8.1.6 databases and later. If the nativeXA property is enabled, be
sure to set the tnsEntry property as well. This property is only for OracleXADatasource.
This DataSource property defaults to false.
ONSConfiguration String Specifies the ONS configuration string that is used to remotely
subscribe to FAN/ONS events.

Note:
• This table omits properties that supported the deprecated connection cache based on
OracleConnectionCache.
• Because Native XA performs better than Java XA, use Native XA whenever possible.
Use the setConnectionProperties method to set the properties of the connection and the
setConnectionCacheProperties method to set the properties of the connection cache.
If you are using the server-side internal driver, that is, the driverType property is set to kprb, then
any other property settings are ignored.
If you are using the JDBC Thin or OCI driver, then note the following:
• A URL setting can include settings for user and password, as in the following example, in
which case this takes precedence over individual user and password property settings:
Copy jdbc:oracle:thin:HR/hr@localhost:5221:orcl
• Settings for user and password are required, either directly through the URL setting or
through the getConnection call. The user and password settings in a getConnection call take
precedence over any property settings.
• If the url property is set, then any tnsEntry, driverType, portNumber, networkProtocol,
serverName, and databaseName property settings are ignored.
• If the tnsEntry property is set, which presumes the url property is not set, then any
databaseName, serverName, portNumber, and networkProtocol settings are ignored.
• If you are using an OCI driver, which presumes the driverType property is set to oci, and the
networkProtocol is set to ipc, then any other property settings are ignored.
Also, note that getConnectionCacheName() will return the name of the cache only if the
ConnectionCacheName property of the data source is set after caching is enabled on the data source.

3.6 CREATING A DATA SOURCE INSTANCE AND CONNECTING

 This section shows an example of the most basic use of a data source to connect to a database,
without using JNDI functionality. Note that this requires vendor-specific, hard-coded property
settings.

Create an OracleDataSource instance, initialize its connection properties as appropriate, and get a
connection instance, as in the following example:
Program:
OracleDataSource ods = new OracleDataSource();
ods.setDriverType("oci");
ods.setServerName("localhost");
ods.setNetworkProtocol("tcp");
ods.setDatabaseName(<database_name>);
ods.setPortNumber(5221);
ods.setUser("HR");
ods.setPassword("hr");

3.7 REGISTER THE DATA SOURCE

Register the Data Source

Once you have initialized the connection properties of the OracleDataSource instance ods, as
shown in the preceding example, you can register this data source instance with JNDI, as in the
following example:

Copy
Context ctx = new InitialContext();
ctx.bind("jdbc/sampledb", ods);
Calling the JNDI InitialContext() constructor creates a Java object that references the initial JNDI
naming context. System properties, which are not shown, instruct JNDI which service provider to use.

The ctx.bind call binds the OracleDataSource instance to a logical JNDI name. This means that
anytime after the ctx.bind call, you can use the logical name jdbc/sampledb in opening a connection to
the database described by the properties of the OracleDataSource instance ods. The logical name
jdbc/sampledb is logically bound to this database.
 The JNDI namespace has a hierarchy similar to that of a file system. In this example, the JNDI
name specifies the subcontext jdbc under the root naming context and specifies the logical name
sampledb within the jdbc subcontext.

The Context interface and InitialContext class are in the standard javax.naming package.

Note:The JDBC 2.0 Specification requires that all JDBC data sources be registered in the jdbc
naming subcontext of a JNDI namespace or in a child subcontext of the jdbc subcontext.
Open a Connection

To perform a lookup and open a connection to the database logically bound to the JNDI name, use
the logical JNDI name. Doing this requires casting the lookup result, which is otherwise a Java
Object, to OracleDataSource and then using its getConnection method to open the connection.

Here is an example:

OracleDataSource odsconn = (OracleDataSource)ctx.lookup("jdbc/sampledb");

Connection conn = odsconn.getConnect

3.8 CONFIGURING DATABASE REMOTE LOGIN

Before the JDBC Thin driver can connect to the database as SYSDBA, you must configure the
user, because Oracle Database security system requires a password file for remote connections as an
administrator. Perform the following:

Set a password file on the server-side or on the remote database, using the orapwd password
utility. You can add a password file for user SYS as follows:
In UNIX
orapwd file=$ORACLE_HOME/dbs/orapwORACLE_SID entries=200
Enter password: password
In Microsoft Windows
orapwd file=%ORACLE_HOME%\database\PWDORACLE_SID.ora entries=200
Enter password: password
In this case, file is the name of the password file, password is the password for user SYS. It can be
altered using the ALTER USER statement in SQL Plus. You should set entries to a value higher than
the number of entries you expect.
 Enable remote login as SYSDBA. This step grants SYSDBA and SYSOPER system privileges to
individual users and lets them connect as themselves.
Stop the database, and add the following line to initservice_name.ora, in UNIX, or init.ora, in
Microsoft Windows:

Copy
remote_login_passwordfile=exclusive
The initservice_name.ora file is located at ORACLE_HOME/dbs/ and also at
ORACLE_HOME/admin/db_name/pfile/. Ensure that you keep the two files synchronized.

The init.ora file is located at %ORACLE_BASE%\ADMIN\db_name\pfile\.

Change the password for the SYS user. This is an optional step.
PASSWORD sys
Changing password for sys
New password: password
Retype new password: password
Verify whether SYS has the SYSDBA privilege.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
---------------------- --------- ---------
SYS TRUE TRUE
Restart the remote database.

3.9 USING SYS LOGIN TO MAKE A REMOTE CONNECTION

//This example works regardless of language settings of the database.

/** case of remote connection using sys **/
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
// create an OracleDataSource
OracleDataSource ods = new OracleDataSource();
// set connection properties
java.util.Properties prop = new java.util.Properties();
prop.put("user", "sys");
 prop.put("password", "sys");
prop.put("internal_logon", "sysoper");
ods.setConnectionProperties(prop);
// set the url
// the url can use oci driver as well as:
// url = "jdbc:oracle:oci8:@remotehost"; the remotehost is a remote database
String url = "jdbc:oracle:thin:@localhost:5221/orcl";
ods.setURL(url);
// get the connection
Connection conn = ods.getConnection();

8 Using Bequeath Connection and SYS Logon

The following example illustrates how to use the internal_logon and SYSDBA arguments to
specify the SYS login. This example works regardless of the database's national-language settings of
the database.

Copy
/** Example of bequeath connection **/
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

// create an OracleDataSource instance

OracleDataSource ods = new OracleDataSource();

// set neccessary properties

java.util.Properties prop = new java.util.Properties();
prop.put("user", "sys");
prop.put("password", "sys");
prop.put("internal_logon", "sysdba");
ods.setConnectionProperties(prop);

// the url for bequeath connection

String url = "jdbc:oracle:oci8:@";
ods.setURL(url);
// retrieve the connection
Connection conn = ods.getConnection();

SELF ASSESSMENT QUESTIONS

1. What is JDBC?
2. What are the benefits of using JDBC?
3. What are the core components of JDBC?
4. How do you load a JDBC driver?
5. How do you establish a connection to a database using JDBC?
6. How do you create a statement object?
7. How do you execute an SQL statement using JDBC?
8. How do you process the results of an SQL query?
9. How do you close a connection to a database using JDBC?
10. What are some common JDBC exceptions?
11. Advanced JDBC Concepts

12. What is the difference between a Statement and a PreparedStatement?

13. How do you use PreparedStatements to improve performance?
14. What is batching in JDBC?
15. How do you use batching to improve performance?
16. What are transactions in JDBC?
17. How do you manage transactions in JDBC?
18. What are the different JDBC connection modes?
19. How do you use different connection modes in JDBC?
20. What are data sources in JDBC?
21. How do you use data sources in JDBC?
22. Additional Questions

23. What are some of the best practices for using JDBC?
24. What are some common mistakes that people make when using JDBC?
25. What are some resources that you can use to learn more about JDBC?
TERMINAL QUESTIONS
 1. What is the JDBC driver manager and its role in establishing database connections?
2. Describe the different types of JDBC statements (Statement, PreparedStatement,
CallableStatement) and their usage scenarios.
3. Explain the concept of result sets in JDBC and how to process them effectively.
4. How do you establish a connection to an Oracle database using JDBC?
5. What is the difference between a Type-1 and Type-4 JDBC driver?
6. How do you handle exceptions and errors in JDBC applications?
7. What are some best practices for writing secure JDBC code?
8. How do you optimize JDBC performance for large-scale applications?
9. Intermediate JDBC Questions

10. Explain how to use JDBC transactions to ensure data integrity.

11. Describe the concept of data sources and their role in connection pooling.
12. How do you implement batch processing using JDBC?
13. What are some advanced JDBC features such as rowsets and metadata?
14. How do you integrate JDBC with other Java technologies like servlets and JSP?
15. Explain how to troubleshoot common JDBC connection and query execution issues.
16. What are some performance optimization techniques for JDBC applications?
17. Advanced JDBC Questions

18. How do you implement custom JDBC drivers for Oracle databases?
19. Describe how to use JDBC to interact with Oracle object-relational features, such as stored
procedures, functions, and packages.
20. Explain how to integrate JDBC with Oracle enterprise features, such as Oracle Data Guard
and Oracle Real Application Clusters.
21. Discuss the role of JDBC in Oracle Cloud Infrastructure (OCI) cloud-based database services.
22. How do you secure JDBC applications against common SQL injection attacks?
23. Discuss the performance implications of different JDBC connection pooling strategies.
24. What are some emerging trends and future directions in JDBC development?