Difference between revisions of "EclipseLink/Examples/JPA/nonJDBCArgsToStoredProcedures"

From Eclipsepedia

Jump to: navigation, search
(How to handle nonJDBC arguments for Oracle Stored Procedures)
(How to handle nonJDBC arguments for Oracle Stored Procedures)
Line 10: Line 10:
  
 
the EclipseLink code would be:
 
the EclipseLink code would be:
 +
 +
<font color="RED">'''package'''</font> test<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
  <font color="GREEN">''// javase imports
 +
''</font><font color="RED">'''import'''</font> java<font size="+1" color="BLUE">'''.'''</font>util<font size="+1" color="BLUE">'''.'''</font>Vector<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
<font color="GREEN">''// EclipseLink imports
 +
''</font><font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>internal<font size="+1" color="BLUE">'''.'''</font>helper<font size="+1" color="BLUE">'''.'''</font>NonSynchronizedVector<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>logging<font size="+1" color="BLUE">'''.'''</font>SessionLog<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>platform<font size="+1" color="BLUE">'''.'''</font>database<font size="+1" color="BLUE">'''.'''</font>oracle<font size="+1" color="BLUE">'''.'''</font>Oracle10Platform<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>platform<font size="+1" color="BLUE">'''.'''</font>database<font size="+1" color="BLUE">'''.'''</font>oracle<font size="+1" color="BLUE">'''.'''</font>PLSQLStoredProcedureCall<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>queries<font size="+1" color="BLUE">'''.'''</font>DataModifyQuery<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>sessions<font size="+1" color="BLUE">'''.'''</font>DatabaseLogin<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>sessions<font size="+1" color="BLUE">'''.'''</font>DatabaseSession<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>sessions<font size="+1" color="BLUE">'''.'''</font>Project<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>sessions<font size="+1" color="BLUE">'''.'''</font>Session<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
<font color="RED">'''import'''</font> <font color="RED">'''static'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>platform<font size="+1" color="BLUE">'''.'''</font>database<font size="+1" color="BLUE">'''.'''</font>oracle<font size="+1" color="BLUE">'''.'''</font>OraclePLSQLTypes<font size="+1" color="BLUE">'''.'''</font>PLSQLBoolean<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
<font color="RED">'''public'''</font> <font color="RED">'''class'''</font> TestClass <font size="+1" color="BLUE">'''{'''</font>
 +
 +
    <font color="RED">'''public'''</font> <font color="RED">'''static'''</font> String DATABASE_USERNAME <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="PURPLE">"scott"</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
    <font color="RED">'''public'''</font> <font color="RED">'''static'''</font> String DATABASE_PASSWORD <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="PURPLE">"tiger"</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
    <font color="RED">'''public'''</font> <font color="RED">'''static'''</font> String DATABASE_URL <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="PURPLE">"jdbc:oracle:thin:@localhost:1521:ORCL"</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
    <font color="RED">'''public'''</font> <font color="RED">'''static'''</font> String DATABASE_DRIVER <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="PURPLE">"oracle.jdbc.driver.OracleDriver"</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
   
 +
    <font color="RED">'''public'''</font> <font color="RED">'''static'''</font> <font color="RED">'''void'''</font> main<font size="+1" color="BLUE">'''('''</font>String<font size="+1" color="BLUE">'''['''</font><font size="+1" color="BLUE">''']'''</font> args<font size="+1" color="BLUE">''')'''</font> <font size="+1" color="BLUE">'''{'''</font>
 +
 +
        Project project <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="RED">'''new'''</font> Project<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
        DatabaseLogin login <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="RED">'''new'''</font> DatabaseLogin<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
        login<font size="+1" color="BLUE">'''.'''</font>setUserName<font size="+1" color="BLUE">'''('''</font>DATABASE_USERNAME<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
        login<font size="+1" color="BLUE">'''.'''</font>setPassword<font size="+1" color="BLUE">'''('''</font>DATABASE_PASSWORD<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
        login<font size="+1" color="BLUE">'''.'''</font>setConnectionString<font size="+1" color="BLUE">'''('''</font>DATABASE_URL<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
        login<font size="+1" color="BLUE">'''.'''</font>setDriverClassName<font size="+1" color="BLUE">'''('''</font>DATABASE_DRIVER<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
        login<font size="+1" color="BLUE">'''.'''</font>setDatasourcePlatform<font size="+1" color="BLUE">'''('''</font><font color="RED">'''new'''</font> Oracle10Platform<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
        project<font size="+1" color="BLUE">'''.'''</font>setDatasourceLogin<font size="+1" color="BLUE">'''('''</font>login<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
        Session s <font size="+1" color="BLUE"><nowiki>=</nowiki></font> project<font size="+1" color="BLUE">'''.'''</font>createDatabaseSession<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
        s<font size="+1" color="BLUE">'''.'''</font>setLogLevel<font size="+1" color="BLUE">'''('''</font>SessionLog<font size="+1" color="BLUE">'''.'''</font>FINE<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
        <font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">'''('''</font>DatabaseSession<font size="+1" color="BLUE">''')'''</font>s<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''.'''</font>login<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
        PLSQLStoredProcedureCall call <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="RED">'''new'''</font> PLSQLStoredProcedureCall<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
        call<font size="+1" color="BLUE">'''.'''</font>setProcedureName<font size="+1" color="BLUE">'''('''</font><font color="PURPLE">"bool_in_test"</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
        call<font size="+1" color="BLUE">'''.'''</font>addNamedArgument<font size="+1" color="BLUE">'''('''</font><font color="PURPLE">"X"</font><font size="+1" color="BLUE">''','''</font> PLSQLBoolean<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
        DataModifyQuery query <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="RED">'''new'''</font> DataModifyQuery<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
        query<font size="+1" color="BLUE">'''.'''</font>addArgument<font size="+1" color="BLUE">'''('''</font><font color="PURPLE">"X"</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
        query<font size="+1" color="BLUE">'''.'''</font>setCall<font size="+1" color="BLUE">'''('''</font>call<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
        Vector queryArgs <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="RED">'''new'''</font> NonSynchronizedVector<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
        queryArgs<font size="+1" color="BLUE">'''.'''</font>add<font size="+1" color="BLUE">'''('''</font>Integer<font size="+1" color="BLUE">'''.'''</font>valueOf<font size="+1" color="BLUE">'''('''</font><font color="BROWN">1</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
        s<font size="+1" color="BLUE">'''.'''</font>executeQuery<font size="+1" color="BLUE">'''('''</font>query<font size="+1" color="BLUE">''','''</font> queryArgs<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
 +
 +
    <font size="+1" color="BLUE">'''}'''</font>
 +
<font size="+1" color="BLUE">'''}'''</font>
 +
 +
<pre>
 +
[EPS Info]: 2007.11.23 01:03:23.515--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])--TopLink, version: Eclipse Persistence Services - @VERSION@ (Build @BUILD_NUMBER@)
 +
[EPS Config]: 2007.11.23 01:03:23.531--DatabaseSessionImpl(15674464)--Connection(4877503)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
 +
platform=>Oracle10Platform
 +
user name=> "scott"
 +
datasource URL=> "jdbc:oracle:thin:@localhost:1521:ORCL"
 +
))
 +
[EPS Config]: 2007.11.23 01:03:23.875--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--Connected: jdbc:oracle:thin:@localhost:1521:ORCL
 +
User: SCOTT
 +
Database: Oracle  Version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
 +
With the Partitioning, OLAP and Data Mining options
 +
Driver: Oracle JDBC driver  Version: 10.2.0.1.0
 +
[EPS Info]: 2007.11.23 01:03:23.890--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])-- login successful
 +
[EPS Fine]: 2007.11.23 01:03:23.968--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
 +
DECLARE
 +
  X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
 +
BEGIN
 +
  bool_in_test(X=>X_TARGET);
 +
END;
 +
bind => [:1 => 1]
 +
</pre>

Revision as of 14:04, 23 November 2007

{available as of EclipseLink 1.0M2}

How to handle nonJDBC arguments for Oracle Stored Procedures

The standard way of handling a Stored Procedure is to build an instance of org.eclipse.persistence.queries.StoredProcedureCall. However, the arguments must be compatible with the JDBC specification.

To handle nonJDBC arguments (e.g. BOOLEAN, PLS_INTEGER, PL/SQL record, etc.) a new sub-class has been created: org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall:

For the target procedure:

procedure bool_test(x IN BOOLEAN)

the EclipseLink code would be:

package test;

 // javase imports
import java.util.Vector;

// EclipseLink imports
import org.eclipse.persistence.internal.helper.NonSynchronizedVector;

import org.eclipse.persistence.logging.SessionLog;
import org.eclipse.persistence.platform.database.oracle.Oracle10Platform;

import org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall;

import org.eclipse.persistence.queries.DataModifyQuery;
import org.eclipse.persistence.sessions.DatabaseLogin;

import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.sessions.Project;

import org.eclipse.persistence.sessions.Session;
import static org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes.PLSQLBoolean;

public class TestClass {

    public static String DATABASE_USERNAME = "scott";

    public static String DATABASE_PASSWORD = "tiger";
    public static String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";

    public static String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver";
    
    public static void main(String[] args) {

        Project project = new Project();
        DatabaseLogin login = new DatabaseLogin();

        login.setUserName(DATABASE_USERNAME);
        login.setPassword(DATABASE_PASSWORD);

        login.setConnectionString(DATABASE_URL);
        login.setDriverClassName(DATABASE_DRIVER);

        login.setDatasourcePlatform(new Oracle10Platform());
        project.setDatasourceLogin(login);

        Session s = project.createDatabaseSession();
        s.setLogLevel(SessionLog.FINE);

        ((DatabaseSession)s).login();

        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();

        call.setProcedureName("bool_in_test");
        call.addNamedArgument("X", PLSQLBoolean);

        DataModifyQuery query = new DataModifyQuery();
        query.addArgument("X");

        query.setCall(call);
        Vector queryArgs = new NonSynchronizedVector();

        queryArgs.add(Integer.valueOf(1));
        s.executeQuery(query, queryArgs);

    }
}
[EPS Info]: 2007.11.23 01:03:23.515--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])--TopLink, version: Eclipse Persistence Services - @VERSION@ (Build @BUILD_NUMBER@)
[EPS Config]: 2007.11.23 01:03:23.531--DatabaseSessionImpl(15674464)--Connection(4877503)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
	platform=>Oracle10Platform
	user name=> "scott"
	datasource URL=> "jdbc:oracle:thin:@localhost:1521:ORCL"
))
[EPS Config]: 2007.11.23 01:03:23.875--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--Connected: jdbc:oracle:thin:@localhost:1521:ORCL
	User: SCOTT
	Database: Oracle  Version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
	Driver: Oracle JDBC driver  Version: 10.2.0.1.0
[EPS Info]: 2007.11.23 01:03:23.890--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])-- login successful
[EPS Fine]: 2007.11.23 01:03:23.968--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
DECLARE
  X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
BEGIN
  bool_in_test(X=>X_TARGET);
END;
	bind => [:1 => 1]