Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Examples/JPA/nonJDBCArgsToStoredProcedures"
< EclipseLink | Examples | JPA
(→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]