Java JDBC VS Java in Database

We can use Java code directly in PLSQL to manipulate database (insert data , update , do transaction .... etc) , and also we can write java code to manipulate database through JDBC connection.

When you have two solutions to do the same task, you should wait and choose the best solution.
So in my post today I will illustrate the difference between embedded java in PLSQL and Java run through JDBC connection.

For our demo I will create java code to delete and insert record in SCOTT.EMP table.
I will write code to run from PLSQL and write the same code to run through JDBC connection.

I created below Java class contains dmlOperation() method which do delete and insert record in SCOTT.EMP table and track time used for finishing transaction.

 import java.sql.*;  
 public class JdbcVsPlsql {  
   public static void dmlOperation() throws SQLException {  
     int empNo = 999;  
     String empName = "Mahmoud";  
     String empJob = "DEV";  
     long startTime, endTime;  
     Connection conn = null;  
     Statement stmt = null;  
     if (System.getProperty("oracle.jserver.version") != null) {  
       // You are in the database, already connected,then use the default  
       conn = DriverManager.getConnection("jdbc:default:connection:");  
       System.out.println("Running in OracleJVM,in the database!");  
     } else {  
       // You are not in the database,then you need to connect to the database  
       DriverManager.registerDriver(new oracle.jdbc.OracleDriver());  
       conn = DriverManager.getConnection("jdbc:oracle:thin:", "SCOTT", "TIGER");  
       System.out.println("Running in JDK VM, outside the database;");  
       conn.setAutoCommit(false);  
     }  
     // Start timing  
     startTime = System.currentTimeMillis();  
     stmt = conn.createStatement();  
     String deleteStatement = "DELETE FROM SCOTT.EMP WHERE EMPNO= " + empNo;  
     String insertStatement =  
       "INSERT INTO SCOTT.EMP(EMPNO, ENAME, JOB)VALUES(" + empNo + ",'" + empName + "','" + empJob + "')";  
     stmt.executeUpdate(deleteStatement);  
     stmt.executeUpdate(insertStatement);  
     // Close the Statement  
     stmt.close();  
     // Stop timing  
     endTime = System.currentTimeMillis();  
     System.out.println("====> Duration of Delete and Insert Operation: " + (int)(endTime - startTime) +  
               " Milliseconds");  
     // Close the connection  
     conn.close();  
   }  
   public static void main(String[] args) throws SQLException {  
     dmlOperation();  
   }  
 }  

I will create java source for JdbcVsPlsql class as below

 CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "JdbcVsPlsql"   
   AS   
 import java.sql.*;  
 public class JdbcVsPlsql {  
   public static void dmlOperation() throws SQLException {  
     int empNo = 999;  
     String empName = "Mahmoud";  
     String empJob = "DEV";  
     long startTime, endTime;  
     Connection conn = null;  
     Statement stmt = null;  
     if (System.getProperty("oracle.jserver.version") != null) {  
       // You are in the database, already connected,then use the default  
       conn = DriverManager.getConnection("jdbc:default:connection:");  
       System.out.println("Running in OracleJVM,in the database!");  
     } else {  
       // You are not in the database,then you need to connect to the database  
       DriverManager.registerDriver(new oracle.jdbc.OracleDriver());  
       conn = DriverManager.getConnection("jdbc:oracle:thin:", "SCOTT", "TIGER");  
       System.out.println("Running in JDK VM, outside the database;");  
       conn.setAutoCommit(false);  
     }  
     // Start timing  
     startTime = System.currentTimeMillis();  
     stmt = conn.createStatement();  
     String deleteStatement = "DELETE FROM SCOTT.EMP WHERE EMPNO= " + empNo;  
     String insertStatement =  
       "INSERT INTO SCOTT.EMP(EMPNO, ENAME, JOB)VALUES(" + empNo + ",'" + empName + "','" + empJob + "')";  
     stmt.executeUpdate(deleteStatement);  
     stmt.executeUpdate(insertStatement);  
     // Close the Statement  
     stmt.close();  
     // Stop timing  
     endTime = System.currentTimeMillis();  
     System.out.println("====> Duration of Delete and Insert Operation: " + (int)(endTime - startTime) +  
               " Milliseconds");  
     // Close the connection  
     conn.close();  
   }  
   public static void main(String[] args) throws SQLException {  
     dmlOperation();  
   }  
 }  

Now I will create PLSQl wrapper procedure for dmlOperation() method in JdbcVsPlsql class

 CREATE OR REPLACE PROCEDURE DMLOPERATION  
 AS  
   LANGUAGE JAVA  
   NAME 'JdbcVsPlsql.dmlOperation () ';  

After creating PLSQL procedure and Java Class
let's now run every one 5 times and record the estimated time to execute and get the final average for each one.

to test from PLSQL you can use below code

 BEGIN  
   DBMS_JAVA.SET_OUTPUT (100000);  
   FOR I IN 1 .. 5  
   LOOP  
    DMLOPERATION;  
   END LOOP;  
 END;  

to test from JDBC connection you can run java class from Jdeveloper

	Java Class (JDBC)	Java from PLSQL
1st	62 Milliseconds	8 Milliseconds
2nd	61 Milliseconds	4 Milliseconds
3rd	64 Milliseconds	4 Milliseconds
4th	61 Milliseconds	4 Milliseconds
5th	60 Milliseconds	5 Milliseconds
Average	61.6 Milliseconds	5 Milliseconds

Regarding average result, using java from PLSQL is faster than using JDBC connection.
The time to execute may differ at your machine because of hardware difference specification.

Thanks
Mahmoud A. El-Sayed