JDBC - Oracle
About
Definition of the JDBC Oracle API or how to connect to a JDBC oracle and made database operations.
Articles Related
Architecture
A simple application includes the following classes:
- DataHandler.java. This class contains all the methods that are used to implement the important functions of the 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.
- MyTable.java. This class is a JavaBean that holds a single myTable 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.
Oracle JDBC Package
Important packages of the Oracle JDBC library include the following:
- 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 and other Oracle-specific features, including Oracle performance enhancements.
- 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.
- oracle.jdbc.pool: This package includes the OracleDataSource class that is used to get a connection to the database. The overloaded getConnection method returns a physical connection to the database.
How to
Initiate a connection
To initiate a connection from the Java application, you use the Connection object from the JDBC application programming interface (API).
In Java, you use an instance of the DataSource object to get a connection to the database.
The DataSource interface provides a complete replacement for the previous JDBC DriverManager class.
Oracle implements the javax.sql.DataSource interface with the OracleDataSource class in the oracle.jdbc.pool package.
The overloaded getConnection method returns a physical connection to the database.
// Methode to create a connection
public void getDBConnection() throws SQLException {
try {
jdbcUrl = "jdbc:oracle:thin:@192.168.135.131:1521:direp";
userid = "hr";
password = "hr";
// Declaration of an OracleDataSource Instance
OracleDataSource ds;
// Creation of a new OracleDataSource Object:
ds = new OracleDataSource();
// Setting the URL for the DataSource object
ds.setURL(jdbcUrl);
// Connection to the data source
conn = ds.getConnection(userid, password);
} catch (SQLException ex) {
logException(ex);
}
}
All jdbc url can be found here: Jdbc - URL (and Driver Class)
Define a SQL query statement
Static
The createStatement method is used to define a SQL query statement. The Statement object is used to run static SQL queries that can be coded into the application.
In addition, for scenarios where many similar queries with differing update values must be run on the database, you use the OraclePreparedStatement object, which extends the Statement object.
Dynamic
Dynamic SQL, or generating SQL statements on the fly. For scenarios where many similar queries with differing update values must be run on the database, you can use the OraclePreparedStatement object, which extends the Statement object.
This is done by substituting the literal update values with bind variables.
You can also use stored PL/SQL functions on the database by calling stored procedures through the OracleCallableStatement object.
To run static SQL queries on the database, you use the Statement object. However, to run multiple similar queries or perform multiple updates that affect many columns in the database, it is not feasible to hard-code each query in your application.
You use OraclePreparedStatement when you run the same SQL statement multiple times.
If you use OraclePreparedStatement functionality, the SQL statement you want to run is precompiled and stored in a PreparedStatement object, and you can run it as many times as required without compiling it every time it is run. If the data in the statement changes, you can use bind variables as placeholders for the data and then provide literal values at run time.
OraclePreparedStatement pstmt = conn.prepareStatement("UPDATE Employees
SET salary = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)
The advantages of using the OraclePreparedStatement interface include:
- You can batch updates by using the same PreparedStatement object
- You can improve performance because the SQL statement that is run many times is compiled only the first time it is run.
- You can use bind variables to make the code simpler and reusable.
- You avoid SQL injection
Execute a Query and display the result
You use the executeQuery method to run queries on the database and produce a set of rows that match the query conditions. These results are contained in a ResultSet object.
ResultSet
JDBC - Resultset (SELECT|SQL query)
Select
public Employee findEmployeeById(int id) throws SQLException {
try {
// Create a new instance of the Employee bean
Employee selectedEmp = new Employee();
// Connect to the database
getDBConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
sqlString = "SELECT * FROM Employees WHERE employee_id = " + id;
System.out.println("\nExecuting: " + sqlString);
// Run the query and use a ResultSet object to contain the result.
rset = stmt.executeQuery(sqlString);
while (rset.next()) {
selectedEmp.setEmployeeId(new Integer(rset.getInt("employee_id")));
selectedEmp.setFirstName(rset.getString("first_name"));
selectedEmp.setLastName(rset.getString("last_name"));
selectedEmp.setEmail(rset.getString("email"));
selectedEmp.setPhoneNumber(rset.getString("phone_number"));
selectedEmp.setHireDate(rset.getDate("hire_date"));
selectedEmp.setSalary(new Double(rset.getDouble("salary")));
selectedEmp.setJobId(rset.getString("job_id"));
}
return selectedEmp;
} catch (SQLException ex) {
logException(ex);
} finally {
closeAll();
}
}
Update
public String updateEmployee(int employee_id, String first_name, String last_name, String email,
String phone_number, String salary, String job_id) throws SQLException {
try {
//create an instance of the Employee bean, containing details for the selected employee:
Employee oldEmployee = findEmployeeById(employee_id);
// Create a Statement object and specify the ResultSet type as before.
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
// Create a StringBuffer to accumulate details of the SQL UPDATE statement that needs to be built:
StringBuffer columns = new StringBuffer(255);
if (first_name != null && !first_name.equals(oldEmployee.getFirstName())) {
columns.append("first_name = '" + first_name + "'");
}
if (last_name != null && !last_name.equals(oldEmployee.getLastName())) {
if (columns.length() > 0) {
columns.append(", ");
}
columns.append("last_name = '" + last_name + "'");
}
if (email != null && !email.equals(oldEmployee.getEmail())) {
if (columns.length() > 0) {
columns.append(", ");
}
columns.append("email = '" + email + "'");
}
if (phone_number != null && !phone_number.equals(oldEmployee.getPhoneNumber())) {
if (columns.length() > 0) {
columns.append(", ");
}
columns.append("phone_number = '" + phone_number + "'");
}
if (salary != null && !salary.equals(oldEmployee.getSalary().toString())) {
if (columns.length() > 0) {
columns.append(", ");
}
columns.append("salary = '" + salary + "'");
}
if (job_id != null && !job_id.equals(oldEmployee.getJobId())) {
if (columns.length() > 0) {
columns.append(", ");
}
columns.append("job_id = '" + job_id + "'");
}
if (columns.length() > 0) {
sqlString = "UPDATE Employees SET " + columns.toString() + " WHERE employee_id = " + employee_id;
System.out.println("\nExecuting: " + sqlString);
stmt.execute(sqlString);
} else {
System.out.println("Nothing to do to update Employee Id: " + employee_id);
}
return "success";
} catch (SQLException ex) {
logException(ex);
return "failure";
} finally {
closeAll();
}
}
Insert
public String addEmployee(String first_name, String last_name, String email, String phone_number, String job_id,
int salary) throws SQLException {
try {
getDBConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
sqlString =
"INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, '" + first_name + "','" + last_name +
"','" + email +
"','" + phone_number + "'," + "SYSDATE, '" + job_id + "', " + salary + ",.30,100,80)";
System.out.println("\nInserting: " + sqlString);
stmt.execute(sqlString);
return "success";
} catch (SQLException ex) {
logException(ex);
return "failure";
} finally {
closeAll();
}
}
Access stored procedures
To access stored procedures on Oracle Database, you use the OracleCallableStatement object.
You can access stored procedures on databases using the OracleCallableStatement interface. This interface extends the OraclePreparedStatement interface. The OracleCallableStatement interface consists of standard JDBC escape syntax to call stored procedures. You may use this with or without a result parameter. However, if you do use a result parameter, it must be registered as an OUT parameter. Other parameters that you use with this interface can be either IN, OUT, or both.
These parameters are set by using accessor methods inherited from the OraclePreparedStatement interface. IN parameters are set by using the setXXX methods and OUT parameters are retrieved by using the getXXX methods, XXX being the Java data type of the parameter.
A CallableStatement can also return multiple ResultSet objects.
As an example, you can create an OracleCallableStatement to call the stored procedure called foo, as follows:
OracleCallableStatement cs = (OracleCallableStatement)
conn.prepareCall("{call foo(?)}");
You can pass the string bar to this procedure in one of the following two ways:
cs.setString(1,"bar"); // JDBC standard
// or...
cs.setStringAtName(X, "value"); // Oracle extension that will replace the binding variable :X with the value "value"
For PL/SQL blocks or stored procedure calls, you can use the following qualifiers to differentiate between input and output variables: IN, OUT, and IN OUT. Input variable values are set by using setXXX methods and OUT variable values can be retrieved by using getXXX methods, where XXX is the Java data type of the values. This depends on the SQL data types of the columns that you are accessing in the database.
Calling Stored Procedures
public String addEmployeeStoredProcedure(String first_name, String last_name,
String email, String phone_number, String job_id,
int salary) throws SQLException {
try {
getDBConnection();
sqlString = "begin hr.insert_employee(?,?,?,?,?,?); end;";
CallableStatement callstmt = conn.prepareCall(sqlString);
callstmt.setString(1, first_name);
callstmt.setString(2, last_name);
callstmt.setString(3, email);
callstmt.setString(4, phone_number);
callstmt.setString(5, job_id);
callstmt.setInt(6, salary);
System.out.println("\nInserting with stored procedure: " +
sqlString);
callstmt.execute();
return "success";
}
catch ( SQLException ex ) {
System.out.println("Possible source of error: Make sure you have created the stored procedure");
logException( ex );
return "failure";
}
}
Calling Stored Function
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@<hoststring>");
ods.setUser("hr");
ods.setPassword("hr");
Connection conn = ods.getConnection();
CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
cs.registerOutParameter(1,Types.CHAR);
cs.setString(2, "aa");
cs.executeUpdate();
String result = cs.getString(1);
Executing Oracle PL/SQL block
CallableStatement cs3 = conn.prepareCall
( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
( "begin ? := func(?,?); end;" ) ; // stored func
Use Cursor Variables
Oracle JDBC drivers support cursor variables with the REF CURSOR types, which are not a part of the JDBC standard. REF CURSOR types are supported as JDBC result sets.
CURSORS contain query results and metadata. A REF CURSOR (or CURSOR variable) data type contains a reference to a cursor. It can be passed between:
- the RDBMS and the client,
- or between PL/SQL and Java in the database.
It can also be returned from a query or a stored procedure.
REF CURSOR instances are not scrollable.
public ResultSet getJobs() throws SQLException {
try {
getDBConnection();
String jobquery = "begin ? := get_jobs; end;";
CallableStatement callStmt = conn.prepareCall(jobquery);
callStmt.registerOutParameter(1, OracleTypes.CURSOR);
callStmt.execute();
rset = (ResultSet)callStmt.getObject(1);
} catch ( SQLException ex ) {
logException( ex );
}
return rset;
}
Exception
Each JDBC method throws a SQLException if a database access error occurs. For this reason, any method in an application that executes such a method must handle the exception.
A SQLException object instance provides information on a database access error or other errors. Each SQLException instance provides many types of information, including a string describing the error, which is used as the Java Exception message, available via the getMessage method.
More on exception, see Java - Exception (Closeable)
Get unconnected
In the application, you must explicitly close all:
- ResultSet,
- Statement,
- and Connection
objects after you are through using them.
When you close the Connection object, you are unconnected from the database.
The close methods clean up memory and release database cursors.
Therefore, if you do not explicitly close ResultSet and Statement objects, serious memory leaks may occur, and you may run out of cursors in the database. You must then close the connection.
public void closeAll() {
//Within the method body, check whether the ResultSet object is open as follows:
if (rset != null) {
// If it is open, close it and handle any exceptions as follows:
try {
rset.close();
} catch (Exception ex) {
}
rset = null;
}
// Repeat the same actions with the Statement object.
if (stmt != null) {
try {
stmt.close();
} catch (Exception ex) {
}
stmt = null;
}
// Finally, close the Connection object.
if (conn != null) {
try {
conn.close();
} catch (Exception ex) {
}
conn = null;
}
}
Support
java.sql.SQLException: Stream has already been closed
When trying to retrieve a LONG column, you may get this problem
`java.sql.SQLException: Stream has already been closed
at oracle.jdbc.driver.LongAccessor.getBytesInternal(LongAccessor.java:156)
at oracle.jdbc.driver.LongAccessor.getBytes(LongAccessor.java:126)
at oracle.jdbc.driver.LongAccessor.getString(LongAccessor.java:201)
at oracle.jdbc.driver.T4CLongAccessor.getString(T4CLongAccessor.java:450)
at oracle.jdbc.driver.CharCommonAccessor.getObject(CharCommonAccessor.java:788)
at oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java:1108)
at hotitem.db.dataset.DataSetUtil.printDataTypeInformation(DataSetUtil.java:53)
at hotitem.db.dataset.DataSetLoaderTest.printColumnInfTest(DataSetLoaderTest.java:93)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at
Be sure that you don't get the value twice such as
Object obj = resultSet.getObject(i);
obj = resultSet.getObject(i);
otherwise read this, Streaming Data Precautions in Database JDBC Developer's Guide and Reference