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 11: | Line 11: | ||
the EclipseLink code would be: | the EclipseLink code would be: | ||
− | <font color=" | + | <font color="#000000">'''<font color="#7f0055">package</font>'''<font color="#7f0055"> test</font><font color="#7f0055"><nowiki>;</nowiki></font> |
− | <font color=" | + | <font color="#3f7f59">// javase imports</font> |
− | '' | + | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> java</font><font color="#7f0055">.</font><font color="#7f0055">util</font><font color="#7f0055">.</font><font color="#7f0055">Vector</font><font color="#7f0055"><nowiki>;</nowiki></font> |
− | <font color=" | + | <font color="#3f7f59">// EclipseLink imports</font> |
− | ''</font><font color=" | + | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">internal</font><font color="#7f0055">.</font><font color="#7f0055">helper</font><font color="#7f0055">.</font><font color="#7f0055">NonSynchronizedVector</font><font color="#7f0055"><nowiki>;</nowiki></font> |
+ | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">logging</font><font color="#7f0055">.</font><font color="#7f0055">SessionLog</font><font color="#7f0055"><nowiki>;</nowiki></font> | ||
+ | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">platform</font><font color="#7f0055">.</font><font color="#7f0055">database</font><font color="#7f0055">.</font><font color="#7f0055">oracle</font><font color="#7f0055">.</font><font color="#7f0055">Oracle10Platform</font><font color="#7f0055"><nowiki>;</nowiki></font> | ||
+ | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">platform</font><font color="#7f0055">.</font><font color="#7f0055">database</font><font color="#7f0055">.</font><font color="#7f0055">oracle</font><font color="#7f0055">.</font><font color="#7f0055">PLSQLStoredProcedureCall</font><font color="#7f0055"><nowiki>;</nowiki></font> | ||
+ | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">queries</font><font color="#7f0055">.</font><font color="#7f0055">DataModifyQuery</font><font color="#7f0055"><nowiki>;</nowiki></font> | ||
+ | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">sessions</font><font color="#7f0055">.</font><font color="#7f0055">DatabaseLogin</font><font color="#7f0055"><nowiki>;</nowiki></font> | ||
+ | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">sessions</font><font color="#7f0055">.</font><font color="#7f0055">DatabaseSession</font><font color="#7f0055"><nowiki>;</nowiki></font> | ||
+ | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">sessions</font><font color="#7f0055">.</font><font color="#7f0055">Project</font><font color="#7f0055"><nowiki>;</nowiki></font> | ||
+ | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">sessions</font><font color="#7f0055">.</font><font color="#7f0055">Session</font><font color="#7f0055"><nowiki>;</nowiki></font> | ||
+ | '''<font color="#7f0055">import</font>'''<font color="#7f0055"> static org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">platform</font><font color="#7f0055">.</font><font color="#7f0055">database</font><font color="#7f0055">.</font><font color="#7f0055">oracle</font><font color="#7f0055">.</font><font color="#7f0055">OraclePLSQLTypes</font><font color="#7f0055">.</font><font color="#7f0055">PLSQLBoolean</font><font color="#7f0055"><nowiki>;</nowiki></font> | ||
− | + | '''<font color="#7f0055">public</font>''' '''<font color="#7f0055">class</font>''' TestClass { | |
− | + | ||
− | + | '''<font color="#7f0055">public</font>''' '''<font color="#7f0055">static</font>''' '''<font color="#7f0055">String</font>''' DATABASE_USERNAME = <font color="#2a00ff">"scott"</font><nowiki>; | |
− | + | </nowiki>'''<font color="#7f0055">public</font>''' '''<font color="#7f0055">static</font>''' '''<font color="#7f0055">String</font>''' DATABASE_PASSWORD = <font color="#2a00ff">"tiger"</font><nowiki>; | |
− | + | </nowiki>'''<font color="#7f0055">public</font>''' '''<font color="#7f0055">static</font>''' '''<font color="#7f0055">String</font>''' DATABASE_URL = <font color="#2a00ff">"jdbc:oracle:thin:@localhost:1521:ORCL"</font><nowiki>; | |
− | + | </nowiki>'''<font color="#7f0055">public</font>''' '''<font color="#7f0055">static</font>''' '''<font color="#7f0055">String</font>''' DATABASE_DRIVER = <font color="#2a00ff">"oracle.jdbc.driver.OracleDriver"</font><nowiki>; | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | </nowiki>'''<font color="#7f0055">public</font>''' '''<font color="#7f0055">static</font>''' '''<font color="#7f0055">void</font>''' main('''<font color="#7f0055">String</font>'''[] args) { | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | Project project = '''<font color="#7f0055">new</font>''' Project(); | |
− | s | + | DatabaseLogin login = '''<font color="#7f0055">new</font>''' DatabaseLogin(); |
+ | login.setUserName(DATABASE_USERNAME); | ||
+ | login.setPassword(DATABASE_PASSWORD); | ||
+ | login.setConnectionString(DATABASE_URL); | ||
+ | login.setDriverClassName(DATABASE_DRIVER); | ||
+ | login.setDatasourcePlatform('''<font color="#7f0055">new</font>''' Oracle10Platform()); | ||
+ | project.setDatasourceLogin(login); | ||
+ | Session s = project.createDatabaseSession(); | ||
+ | s.setLogLevel(SessionLog.FINE); | ||
+ | ((DatabaseSession)s).login(); | ||
− | + | PLSQLStoredProcedureCall call = '''<font color="#7f0055">new</font>''' PLSQLStoredProcedureCall(); | |
− | + | call.setProcedureName(<font color="#2a00ff">"bool_in_test"</font>); | |
+ | call.addNamedArgument(<font color="#2a00ff">"X"</font>, PLSQLBoolean); | ||
+ | DataModifyQuery query = '''<font color="#7f0055">new</font>''' DataModifyQuery(); | ||
+ | query.addArgument(<font color="#2a00ff">"X"</font>); | ||
+ | query.setCall(call); | ||
+ | '''<font color="#7f0055">Vector</font>''' queryArgs = '''<font color="#7f0055">new</font>''' NonSynchronizedVector(); | ||
+ | queryArgs.add('''<font color="#7f0055">Integer</font>'''.valueOf(1)); | ||
+ | s.executeQuery(query, queryArgs); | ||
+ | } | ||
+ | } | ||
+ | </font> | ||
<pre> | <pre> |
Revision as of 14:07, 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_in_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.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]