/*******************************************************************************
* Copyright (c) 1998, 2015 Oracle and/or its affiliates. All rights reserved.
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
* which accompanies this distribution.
* The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
* and the Eclipse Distribution License is available at
* http://www.eclipse.org/org/documents/edl-v10.php.
*
* Contributors:
* Oracle - initial API and implementation from Oracle TopLink
* Markus KARG - SQL Anywhere now using WATCOM-SQL instead of Transact-SQL.
******************************************************************************/
package org.eclipse.persistence.testing.tests.customsqlstoredprocedures;
import org.eclipse.persistence.exceptions.DatabaseException;
import org.eclipse.persistence.exceptions.ValidationException;
import java.io.IOException;
import java.io.Writer;
import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.descriptors.ClassDescriptor;
import org.eclipse.persistence.sessions.*;
import org.eclipse.persistence.tools.schemaframework.*;
import org.eclipse.persistence.mappings.*;
import org.eclipse.persistence.queries.*;
import org.eclipse.persistence.testing.models.employee.domain.Project;
import org.eclipse.persistence.testing.models.employee.domain.SmallProject;
import org.eclipse.persistence.testing.models.employee.domain.LargeProject;
import org.eclipse.persistence.testing.models.employee.domain.Employee;
import org.eclipse.persistence.testing.models.employee.relational.EmployeeSystem;
/**
* <b>Purpose</b>: To define system behavior.
* <p><b>Responsibilities</b>: <ul>
* <li> Login and return an initialized database session.
* <li> Create and populate the database.
* </ul>
*/
public class EmployeeCustomSQLSystem extends EmployeeSystem {
public void addDescriptors(DatabaseSession session) {
session.logout();
super.addDescriptors(session);
setCustomSQL(session);
// Force re-initialize.
session.login();
}
public StoredProcedureDefinition buildOracleDeleteProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Delete_Employee");
proc.addArgument("P_EMP_ID", Long.class);
proc.addStatement("Delete FROM SALARY where EMP_ID = P_EMP_ID");
proc.addStatement("Delete FROM EMPLOYEE where EMP_ID = P_EMP_ID");
return proc;
}
public StoredProcedureDefinition buildOracleInsertProcedure() {
// Assume no identity.
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Insert_Employee");
proc.addArgument("P_EMP_ID", Long.class);
proc.addArgument("P_SALARY", Integer.class);
proc.addArgument("P_END_DATE", java.sql.Date.class);
proc.addArgument("P_MANAGER_ID", Long.class);
proc.addArgument("P_START_DATE", java.sql.Date.class);
proc.addArgument("P_F_NAME", String.class, 40);
proc.addArgument("P_L_NAME", String.class, 40);
proc.addArgument("P_GENDER", String.class, 1);
proc.addArgument("P_ADDR_ID", Long.class);
proc.addArgument("P_VERSION", Long.class);
proc.addArgument("P_START_TIME", java.sql.Time.class);
proc.addArgument("P_END_TIME", java.sql.Time.class);
proc.addStatement("Insert INTO EMPLOYEE (EMP_ID, END_DATE, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, VERSION, START_TIME, END_TIME) " + "VALUES (P_EMP_ID, P_END_DATE, P_MANAGER_ID, P_START_DATE, P_F_NAME, P_L_NAME, P_GENDER, P_ADDR_ID, P_VERSION, P_START_TIME, P_END_TIME)");
proc.addStatement("Insert INTO SALARY (SALARY, EMP_ID) VALUES (P_SALARY, P_EMP_ID)");
return proc;
}
public PackageDefinition buildOraclePackage() {
PackageDefinition types = new PackageDefinition();
types.setName("Cursor_Type");
types.addStatement("Type Any_Cursor is REF CURSOR");
return types;
}
public StoredProcedureDefinition buildOracleReadAllProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Read_All_Employees");
proc.addOutputArgument("RESULT_CURSOR", "CURSOR_TYPE.ANY_CURSOR");
proc.addStatement("OPEN RESULT_CURSOR FOR Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
return proc;
}
public StoredProcedureDefinition buildOracle2OutCursorsProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Read_Emp_Add");
proc.addOutputArgument("CUR1", "CURSOR_TYPE.ANY_CURSOR");
proc.addOutputArgument("CUR2", "CURSOR_TYPE.ANY_CURSOR");
proc.addStatement("OPEN CUR1 FOR Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
proc.addStatement("OPEN CUR2 FOR Select a.* from ADDRESS a");
return proc;
}
public StoredProcedureDefinition buildOracleReadObjectProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Read_Employee");
proc.addArgument("P_EMP_ID", Long.class);
proc.addOutputArgument("RESULT_CURSOR", "CURSOR_TYPE.ANY_CURSOR");
proc.addStatement("OPEN RESULT_CURSOR FOR Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.EMP_ID = P_EMP_ID");
return proc;
}
public StoredProcedureDefinition buildOracleStoredProcedureInOutPut() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("StoredProcedure_InOutput");
proc.addInOutputArgument("P_EMP_ID", Long.class);
proc.addInOutputArgument("P_F_NAME", String.class);
proc.addStatement("P_EMP_ID := P_EMP_ID");
proc.addStatement("P_F_NAME := P_F_NAME");
return proc;
}
public StoredProcedureDefinition buildOracleStoredProcedureTimestamp() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("StoredProcedure_Timestamp");
proc.addInOutputArgument("CURRENT_DATE", java.sql.Timestamp.class);
proc.addStatement("CURRENT_DATE := CURRENT_DATE");
return proc;
}
public StoredProcedureDefinition buildOracleStoredProcedureInOutOutIn() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("StoredProcedure_InOut_Out_In");
proc.addInOutputArgument("P_INOUT", Long.class);
proc.addOutputArgument("P_OUT", Long.class);
proc.addArgument("P_IN", Long.class);
proc.addStatement("P_OUT := P_INOUT");
proc.addStatement("P_INOUT := P_IN");
return proc;
}
public StoredProcedureDefinition buildOracleStoredProcedureARRAY() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("StoredProcedure_ARRAY");
proc.addOutputArgument("P_OUT", "TEST_STRING_ARRAY");
proc.addStatement("P_OUT := TEST_STRING_ARRAY(10)");
return proc;
}
public static VarrayDefinition buildVARRAYTypeDefinition() {
VarrayDefinition definition = new VarrayDefinition();
definition.setName("TEST_STRING_ARRAY");
definition.setSize(20);
definition.setType(String.class);
definition.setTypeSize(30);
return definition;
}
public StoredFunctionDefinition buildOracleStoredFunctionInOutOutIn() {
StoredFunctionDefinition func = new StoredFunctionDefinition();
func.setName("StoredFunction_InOut_Out_In");
func.addInOutputArgument("P_INOUT", Long.class);
func.addOutputArgument("P_OUT", Long.class);
func.addArgument("P_IN", Long.class);
func.setReturnType(Long.class);
func.addStatement("P_OUT := P_INOUT");
func.addStatement("P_INOUT := P_IN");
func.addStatement("RETURN P_OUT");
return func;
}
public NestedTableDefinition buildOracleLOOKUPTABLETYPE() {
NestedTableDefinition ntd = new NestedTableDefinition();
ntd.setName("SF_LOOKUP_TBL");
ntd.setTypeName("SF_LOOKUP_RECORD");
return ntd;
}
public TypeDefinition buildOracleLOOKUPRECORDTYPE() {
TypeDefinition td = new TypeDefinition();
td.setName("SF_LOOKUP_RECORD");
FieldDefinition fd1 = new FieldDefinition();
fd1.setName("ATTR_1");
fd1.setTypeName("VARCHAR2");
fd1.setSize(200);
td.addField(fd1);
FieldDefinition fd2 = new FieldDefinition();
fd2.setName("ATTR_2");
fd2.setTypeName("VARCHAR2");
fd2.setSize(200);
td.addField(fd2);
return td;
}
public PackageDefinition buildOraclePackageStoredFunctionResultCursor() {
PackageDefinition pkgd = new PackageDefinition();
pkgd.setName("PackageFunction_ResultCursor");
pkgd.addStatement("TYPE REF_CURSOR IS REF CURSOR");
pkgd.addStatement("FUNCTION BUSINESS_DATE (P_CODE IN VARCHAR2, P_LOOKUP_TBL IN SF_LOOKUP_TBL) RETURN REF_CURSOR");
return pkgd;
}
public StoredProcedureDefinition buildOracleUpdateProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Update_Employee");
proc.addArgument("P_EMP_ID", Long.class);
proc.addArgument("P_SALARY", Integer.class);
proc.addArgument("P_END_DATE", java.sql.Date.class);
proc.addArgument("P_MANAGER_ID", Long.class);
proc.addArgument("P_START_DATE", java.sql.Date.class);
proc.addArgument("P_F_NAME", String.class, 40);
proc.addArgument("P_L_NAME", String.class, 40);
proc.addArgument("P_GENDER", String.class, 1);
proc.addArgument("P_ADDR_ID", Long.class);
proc.addArgument("P_START_TIME", java.sql.Time.class);
proc.addArgument("P_END_TIME", java.sql.Time.class);
proc.addOutputArgument("O_ERROR_CODE", Long.class);
proc.addStatement("Update SALARY set SALARY = P_SALARY WHERE (EMP_ID = P_EMP_ID)");
proc.addStatement("Update EMPLOYEE set END_DATE = P_END_DATE, MANAGER_ID = P_MANAGER_ID, " + "START_DATE = P_START_DATE, F_NAME = P_F_NAME, L_NAME = P_L_NAME, GENDER = P_GENDER, ADDR_ID = P_ADDR_ID " + "WHERE (EMP_ID = P_EMP_ID)");
return proc;
}
public StoredProcedureDefinition buildSQLServerDeleteProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Delete_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addStatement("Delete FROM SALARY where EMP_ID = @EMP_ID");
proc.addStatement("Delete FROM EMPLOYEE where EMP_ID = @EMP_ID");
return proc;
}
public StoredProcedureDefinition buildSQLServerInsertProcedure() {
// Assume no identity.
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Insert_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addArgument("SALARY", Integer.class);
proc.addArgument("END_DATE", java.sql.Date.class);
proc.addArgument("MANAGER_ID", Long.class);
proc.addArgument("START_DATE", java.sql.Date.class);
proc.addArgument("F_NAME", String.class, 40);
proc.addArgument("L_NAME", String.class, 40);
proc.addArgument("GENDER", String.class, 1);
proc.addArgument("ADDR_ID", Long.class);
proc.addOutputArgument("VERSION", Long.class);
proc.addArgument("START_TIME", java.sql.Time.class);
proc.addArgument("END_TIME", java.sql.Time.class);
proc.addStatement("Insert INTO EMPLOYEE (EMP_ID, END_DATE, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, VERSION, START_TIME, END_TIME) " + "VALUES (@EMP_ID, @END_DATE, @MANAGER_ID, @START_DATE, @F_NAME, @L_NAME, @GENDER, @ADDR_ID, @VERSION, @START_TIME, @END_TIME)");
proc.addStatement("Insert INTO SALARY (SALARY, EMP_ID) VALUES (@SALARY, @EMP_ID)");
proc.addStatement("SELECT @VERSION = 952");
return proc;
}
public StoredProcedureDefinition buildSQLServerReadAllProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Read_All_Employees");
proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
return proc;
}
public StoredProcedureDefinition buildSQLServerReadObjectProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Read_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.EMP_ID = @EMP_ID");
return proc;
}
public StoredProcedureDefinition buildSQLServerSelectWithOutputAndResultSetProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Select_Output_and_ResultSet");
proc.addArgument("ARG1", Long.class);
proc.addOutputArgument("VERSION", Long.class);
proc.addStatement("SELECT @VERSION = 23");
proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.F_NAME = 'Bob'");
return proc;
}
public StoredProcedureDefinition buildSQLServerSelectWithOutputProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Select_Employee_using_Output");
proc.addArgument("ARG1", Long.class);
proc.addOutputArgument("VERSION", Long.class);
proc.addStatement("SELECT @VERSION = 23");
return proc;
}
public StoredProcedureDefinition buildSQLServerUpdateProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Update_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addArgument("SALARY", Integer.class);
proc.addArgument("END_DATE", java.sql.Date.class);
proc.addArgument("MANAGER_ID", Long.class);
proc.addArgument("START_DATE", java.sql.Date.class);
proc.addArgument("F_NAME", String.class, 40);
proc.addArgument("L_NAME", String.class, 40);
proc.addArgument("GENDER", String.class, 1);
proc.addArgument("ADDR_ID", Long.class);
proc.addArgument("START_TIME", java.sql.Time.class);
proc.addArgument("END_TIME", java.sql.Time.class);
proc.addStatement("Update SALARY set SALARY = @SALARY WHERE (EMP_ID = @EMP_ID)");
// ELBug 339072 - A Row is inserted with version value as 'null' as optimistic locking
// is turned off. During update, it executes the statement using version = null check
// in where clause which fails to update the row. So, removing version field from
// following update statement to fix the issue.
proc.addStatement("Update EMPLOYEE set END_DATE = @END_DATE, MANAGER_ID = @MANAGER_ID, " + "START_DATE = @START_DATE, F_NAME = @F_NAME, L_NAME = @L_NAME, GENDER = @GENDER, ADDR_ID = @ADDR_ID " + " WHERE (EMP_ID = @EMP_ID)");
return proc;
}
public StoredProcedureDefinition buildSybaseDeleteProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Delete_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addStatement("Delete FROM SALARY where EMP_ID = @EMP_ID");
proc.addStatement("Delete FROM EMPLOYEE where EMP_ID = @EMP_ID");
return proc;
}
public StoredProcedureDefinition buildSybaseInsertProcedure() {
// Assume no identity.
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Insert_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addArgument("SALARY", Integer.class);
proc.addArgument("END_DATE", java.sql.Date.class);
proc.addArgument("MANAGER_ID", Long.class);
proc.addArgument("START_DATE", java.sql.Date.class);
proc.addArgument("F_NAME", String.class, 40);
proc.addArgument("L_NAME", String.class, 40);
proc.addArgument("GENDER", String.class, 1);
proc.addArgument("ADDR_ID", Long.class);
proc.addArgument("START_TIME", java.sql.Time.class);
proc.addArgument("END_TIME", java.sql.Time.class);
proc.addArgument("VERSION", Long.class);
proc.addOutputArgument("OUT_VERSION", Long.class);
proc.addStatement("Insert INTO EMPLOYEE (EMP_ID, END_DATE, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, VERSION, START_TIME, END_TIME) " + "VALUES (@EMP_ID, @END_DATE, @MANAGER_ID, @START_DATE, @F_NAME, @L_NAME, @GENDER, @ADDR_ID, @VERSION, @START_TIME, @END_TIME)");
proc.addStatement("Insert INTO SALARY (SALARY, EMP_ID) VALUES (@SALARY, @EMP_ID)");
proc.addStatement("Select @OUT_VERSION = 952");
return proc;
}
public StoredProcedureDefinition buildSybaseReadAllProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Read_All_Employees");
proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
return proc;
}
public StoredProcedureDefinition buildMySQL2ResultSetProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Read_Emp_Add");
proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
proc.addStatement("Select a.* from ADDRESS a");
return proc;
}
public StoredProcedureDefinition buildSybaseReadObjectProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Read_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.EMP_ID = @EMP_ID");
return proc;
}
public StoredProcedureDefinition buildSybaseSelectWithOutputAndResultSetProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Select_Output_and_ResultSet");
proc.addArgument("ARG1", Long.class);
proc.addOutputArgument("VERSION", Long.class);
proc.addStatement("SELECT @VERSION = 23");
proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.F_NAME = 'Bob'");
return proc;
}
public StoredProcedureDefinition buildSybaseWithoutParametersProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Rise_All_Salaries");
proc.addStatement("Update SALARY set SALARY = SALARY * 1.1");
return proc;
}
public StoredProcedureDefinition buildSybaseUpdateProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Update_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addArgument("SALARY", Integer.class);
proc.addArgument("END_DATE", java.sql.Date.class);
proc.addArgument("MANAGER_ID", Long.class);
proc.addArgument("START_DATE", java.sql.Date.class);
proc.addArgument("F_NAME", String.class, 40);
proc.addArgument("L_NAME", String.class, 40);
proc.addArgument("GENDER", String.class, 1);
proc.addArgument("ADDR_ID", Long.class);
proc.addArgument("VERSION", Long.class);
proc.addArgument("START_TIME", java.sql.Time.class);
proc.addArgument("END_TIME", java.sql.Time.class);
proc.addStatement("Update SALARY set SALARY = @SALARY WHERE (EMP_ID = @EMP_ID)");
proc.addStatement("Update EMPLOYEE set END_DATE = @END_DATE, MANAGER_ID = @MANAGER_ID, " + "START_DATE = @START_DATE, F_NAME = @F_NAME, L_NAME = @L_NAME, GENDER = @GENDER, ADDR_ID = @ADDR_ID, " + "VERSION = @VERSION + 1 WHERE ((EMP_ID = @EMP_ID) AND (VERSION = @VERSION))");
return proc;
}
private static StoredProcedureDefinition buildSQLAnywhereDeleteProcedure() {
final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
procedure.setName("Delete_Employee");
procedure.addArgument("_EMP_ID", Long.class);
procedure.addStatement("DELETE FROM SALARY WHERE EMP_ID = _EMP_ID");
procedure.addStatement("DELETE FROM EMPLOYEE WHERE EMP_ID = _EMP_ID");
return procedure;
}
private static StoredProcedureDefinition buildSQLAnywhereInsertProcedure() {
// Assume no identity.
final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
procedure.setName("Insert_Employee");
procedure.addArgument("_EMP_ID", Long.class);
procedure.addArgument("_SALARY", Integer.class);
procedure.addArgument("_END_DATE", java.sql.Date.class);
procedure.addArgument("_MANAGER_ID", Long.class);
procedure.addArgument("_START_DATE", java.sql.Date.class);
procedure.addArgument("_F_NAME", String.class, 40);
procedure.addArgument("_L_NAME", String.class, 40);
procedure.addArgument("_GENDER", String.class, 1);
procedure.addArgument("_ADDR_ID", Long.class);
procedure.addArgument("_START_TIME", java.sql.Time.class);
procedure.addArgument("_END_TIME", java.sql.Time.class);
procedure.addArgument("_VERSION", Long.class);
procedure.addOutputArgument("_OUT_VERSION", Long.class);
procedure.addStatement("INSERT INTO EMPLOYEE (EMP_ID, END_DATE, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, VERSION, START_TIME, END_TIME) VALUES (_EMP_ID, _END_DATE, _MANAGER_ID, _START_DATE, _F_NAME, _L_NAME, _GENDER, _ADDR_ID, _VERSION, _START_TIME, _END_TIME)");
procedure.addStatement("INSERT INTO SALARY (SALARY, EMP_ID) VALUES (_SALARY, _EMP_ID)");
procedure.addStatement("SET _OUT_VERSION = 952");
return procedure;
}
private static StoredProcedureDefinition buildSQLAnywhereReadAllProcedure() {
final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
procedure.setName("Read_All_Employees");
procedure.addStatement("SELECT E.*, S.* FROM EMPLOYEE E JOIN SALARY S ON E.EMP_ID = S.EMP_ID");
return procedure;
}
private static StoredProcedureDefinition buildSQLAnywhereReadObjectProcedure() {
final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
procedure.setName("Read_Employee");
procedure.addArgument("_EMP_ID", Long.class);
procedure.addStatement("SELECT E.*, S.* FROM EMPLOYEE E JOIN SALARY S ON E.EMP_ID = S.EMP_ID WHERE E.EMP_ID = _EMP_ID");
return procedure;
}
private static StoredProcedureDefinition buildSQLAnywhereSelectWithOutputAndResultSetProcedure() {
final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
procedure.setName("Select_Output_and_ResultSet");
procedure.addArgument("ARG1", Long.class);
procedure.addOutputArgument("VERSION", Long.class);
procedure.addStatement("SET VERSION = 23");
procedure.addStatement("SELECT E.*, S.* FROM EMPLOYEE E JOIN SALARY S ON E.EMP_ID = S.EMP_ID WHERE E.F_NAME = 'Bob'");
return procedure;
}
private static StoredProcedureDefinition buildSQLAnywhereWithoutParametersProcedure() {
final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
procedure.setName("Rise_All_Salaries");
procedure.addStatement("UPDATE SALARY SET SALARY = SALARY * 1.1");
return procedure;
}
private static StoredProcedureDefinition buildSQLAnywhereUpdateProcedure() {
final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
procedure.setName("Update_Employee");
procedure.addArgument("_EMP_ID", Long.class);
procedure.addArgument("_SALARY", Integer.class);
procedure.addArgument("_END_DATE", java.sql.Date.class);
procedure.addArgument("_MANAGER_ID", Long.class);
procedure.addArgument("_START_DATE", java.sql.Date.class);
procedure.addArgument("_F_NAME", String.class, 40);
procedure.addArgument("_L_NAME", String.class, 40);
procedure.addArgument("_GENDER", String.class, 1);
procedure.addArgument("_ADDR_ID", Long.class);
procedure.addArgument("_VERSION", Long.class);
procedure.addArgument("_START_TIME", java.sql.Time.class);
procedure.addArgument("_END_TIME", java.sql.Time.class);
procedure.addStatement("UPDATE SALARY SET SALARY = _SALARY WHERE EMP_ID = _EMP_ID");
procedure.addStatement("UPDATE EMPLOYEE SET END_DATE = _END_DATE, MANAGER_ID = _MANAGER_ID, START_DATE = _START_DATE, F_NAME = _F_NAME, L_NAME = _L_NAME, GENDER = _GENDER, ADDR_ID = _ADDR_ID, VERSION = _VERSION + 1 WHERE EMP_ID = _EMP_ID AND VERSION = _VERSION");
return procedure;
}
public StoredProcedureDefinition buildDB2SelectWithOutputAndResultSetProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("OUT_RES_TEST");
proc.addOutputArgument("OUT1", Integer.class);
proc.addOutputArgument("OUT2", Integer.class);
proc.addOutputArgument("OUT3", Integer.class);
proc.addStatement("DECLARE C2 CURSOR WITH RETURN FOR SELECT F_NAME FROM EMPLOYEE");
// DB2 driver seems to have an issue if you assign a value to outputs... so leave as null for now.
//proc.addStatement("SELECT MAX(SALARY) INTO OUT1 FROM SALARY");
//proc.addStatement("SELECT MAX(SALARY) INTO OUT2 FROM SALARY");
//proc.addStatement("SELECT MAX(SALARY) INTO OUT3 FROM SALARY");
proc.addStatement("OPEN C2");
return proc;
}
class PervasiveStoredProcedureDefinition extends StoredProcedureDefinition {
protected String returnString;
public PervasiveStoredProcedureDefinition() {
this.returnString = "";
}
public void setReturnString (String theString) {
this.returnString = theString;
}
protected void printReturn(Writer writer, AbstractSession session) throws ValidationException {
try {
writer.write(returnString);
} catch (IOException ioException) {
throw ValidationException.fileError(ioException);
}
}
}
public StoredProcedureDefinition buildPervasiveDeleteProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Delete_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addStatement("Delete FROM SALARY where EMP_ID = :EMP_ID");
proc.addStatement("Delete FROM EMPLOYEE where EMP_ID = :EMP_ID");
return proc;
}
public StoredProcedureDefinition buildPervasiveInsertProcedure() {
// Assume no identity.
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Insert_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addArgument("SALARY", Integer.class);
proc.addArgument("END_DATE", java.sql.Date.class);
proc.addArgument("MANAGER_ID", Long.class);
proc.addArgument("START_DATE", java.sql.Date.class);
proc.addArgument("F_NAME", String.class, 40);
proc.addArgument("L_NAME", String.class, 40);
proc.addArgument("GENDER", String.class, 1);
proc.addArgument("ADDR_ID", Long.class);
proc.addArgument("START_TIME", java.sql.Time.class);
proc.addArgument("END_TIME", java.sql.Time.class);
proc.addStatement("Insert INTO EMPLOYEE (EMP_ID, END_DATE, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, START_TIME, END_TIME) " + "VALUES (:EMP_ID, :END_DATE, :MANAGER_ID, :START_DATE, :F_NAME, :L_NAME, :GENDER, :ADDR_ID, :START_TIME, :END_TIME)");
proc.addStatement("Insert INTO SALARY (SALARY, EMP_ID) VALUES (:SALARY, :EMP_ID)");
return proc;
}
public StoredProcedureDefinition buildPervasiveReadAllProcedure() {
PervasiveStoredProcedureDefinition proc = new PervasiveStoredProcedureDefinition();
proc.setReturnString (
" RETURNS (" +
"EMP_ID BIGINT, " +
"F_NAME CHAR(40), " +
"L_NAME CHAR(40), " +
"START_DATE DATE, " +
"END_DATE DATE, " +
"START_TIME TIME, " +
"END_TIME TIME, " +
"GENDER CHAR(1), " +
"ADDR_ID BIGINT, " +
"MANAGER_ID BIGINT, " +
"VERSION BIGINT, " +
"EMP_ID BIGINT, " +
"SALARY INTEGER " +
")"
);
proc.setName("Read_All_Employees");
proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
return proc;
}
public StoredProcedureDefinition buildPervasiveReadObjectProcedure() {
PervasiveStoredProcedureDefinition proc = new PervasiveStoredProcedureDefinition();
proc.setReturnString (
" RETURNS (" +
"EMP_ID BIGINT, " +
"F_NAME CHAR(40), " +
"L_NAME CHAR(40), " +
"START_DATE DATE, " +
"END_DATE DATE, " +
"START_TIME TIME, " +
"END_TIME TIME, " +
"GENDER CHAR(1), " +
"ADDR_ID BIGINT, " +
"MANAGER_ID BIGINT, " +
"VERSION BIGINT, " +
"EMP_ID BIGINT, " +
"SALARY INTEGER " +
")"
);
proc.setName("Read_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.EMP_ID = :EMP_ID");
return proc;
}
public StoredProcedureDefinition buildPervasiveStoredProcedureInOutPut() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("StoredProcedure_InOutput");
proc.addInOutputArgument("EMP_ID", Long.class);
proc.addInOutputArgument("F_NAME", String.class);
proc.addStatement("SET :EMP_ID = :EMP_ID");
proc.addStatement("SET :F_NAME = :F_NAME");
return proc;
}
public StoredProcedureDefinition buildPervasiveSelectWithOutputAndResultSetProcedure() {
PervasiveStoredProcedureDefinition proc = new PervasiveStoredProcedureDefinition();
proc.setReturnString (
" RETURNS (" +
"EMP_ID BIGINT, " +
"F_NAME CHAR(40), " +
"L_NAME CHAR(40), " +
"START_DATE DATE, " +
"END_DATE DATE, " +
"START_TIME TIME, " +
"END_TIME TIME, " +
"GENDER CHAR(1), " +
"ADDR_ID BIGINT, " +
"MANAGER_ID BIGINT, " +
"VERSION BIGINT, " +
"EMP_ID BIGINT, " +
"SALARY INTEGER " +
")"
);
proc.setName("Select_Output_and_ResultSet");
proc.addArgument("ARG1", Long.class);
proc.addOutputArgument("VERSION", Long.class);
proc.addStatement("SET :VERSION = 23");
proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.F_NAME = 'Bob'");
return proc;
}
public StoredProcedureDefinition buildPervasiveSelectWithOutputProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Select_Employee_using_Output");
proc.addArgument("ARG1", Long.class);
proc.addOutputArgument("VERSION", Long.class);
proc.addStatement("SET :VERSION = 23");
return proc;
}
public StoredProcedureDefinition buildPervasiveUpdateProcedure() {
StoredProcedureDefinition proc = new StoredProcedureDefinition();
proc.setName("Update_Employee");
proc.addArgument("EMP_ID", Long.class);
proc.addArgument("SALARY", Integer.class);
proc.addArgument("END_DATE", java.sql.Date.class);
proc.addArgument("MANAGER_ID", Long.class);
proc.addArgument("START_DATE", java.sql.Date.class);
proc.addArgument("F_NAME", String.class, 40);
proc.addArgument("L_NAME", String.class, 40);
proc.addArgument("GENDER", String.class, 1);
proc.addArgument("ADDR_ID", Long.class);
proc.addArgument("START_TIME", java.sql.Time.class);
proc.addArgument("END_TIME", java.sql.Time.class);
proc.addStatement("Update SALARY set SALARY = :SALARY WHERE (EMP_ID = :EMP_ID)");
proc.addStatement("Update EMPLOYEE set END_DATE = :END_DATE, MANAGER_ID = :MANAGER_ID, " + "START_DATE = :START_DATE, F_NAME = :F_NAME, L_NAME = :L_NAME, GENDER = :GENDER, ADDR_ID = :ADDR_ID " + " WHERE (EMP_ID = :EMP_ID)");
return proc;
}
/**
* Also creates the procs.
*/
public void createTables(DatabaseSession session) {
super.createTables(session);
org.eclipse.persistence.internal.databaseaccess.DatabasePlatform platform = session.getLogin().getPlatform();
SchemaManager schema = new SchemaManager((session));
if (platform.isSQLServer()) {
schema.replaceObject(buildSQLServerDeleteProcedure());
schema.replaceObject(buildSQLServerReadAllProcedure());
schema.replaceObject(buildSQLServerReadObjectProcedure());
schema.replaceObject(buildSQLServerInsertProcedure());
schema.replaceObject(buildSQLServerUpdateProcedure());
schema.replaceObject(buildSQLServerSelectWithOutputProcedure());
schema.replaceObject(buildSQLServerSelectWithOutputAndResultSetProcedure());
}
if (platform.isSybase()) {
session.getLogin().handleTransactionsManuallyForSybaseJConnect();
schema.replaceObject(buildSybaseDeleteProcedure());
schema.replaceObject(buildSybaseReadAllProcedure());
schema.replaceObject(buildSybaseReadObjectProcedure());
schema.replaceObject(buildSybaseInsertProcedure());
schema.replaceObject(buildSybaseUpdateProcedure());
schema.replaceObject(buildSybaseSelectWithOutputAndResultSetProcedure());
schema.replaceObject(buildSybaseWithoutParametersProcedure());
}
if (platform.isMySQL()) {
schema.replaceObject(buildMySQL2ResultSetProcedure());
}
if (platform.isSQLAnywhere()) {
schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereDeleteProcedure());
schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereReadAllProcedure());
schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereReadObjectProcedure());
schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereInsertProcedure());
schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereUpdateProcedure());
schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereSelectWithOutputAndResultSetProcedure());
schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereWithoutParametersProcedure());
}
if (platform.isOracle()) {
// Drop the dependent type before calling replaceObject to avoid error.
try {
session.executeQuery(new DataModifyQuery("DROP TYPE SF_LOOKUP_TBL FORCE"));
} catch (Exception exception) {
}
try {
session.executeQuery(new DataModifyQuery("DROP TYPE SF_LOOKUP_RECORD FORCE"));
} catch (Exception exception) {
}
schema.replaceObject(buildOracleStoredProcedureInOutPut());
schema.replaceObject(buildOracleStoredProcedureInOutOutIn());
schema.replaceObject(buildOracleStoredProcedureTimestamp());
schema.replaceObject(buildOracleStoredProcedureARRAY());
schema.replaceObject(buildVARRAYTypeDefinition());
schema.replaceObject(buildOracleStoredFunctionInOutOutIn());
schema.replaceObject(buildOraclePackage());
schema.replaceObject(buildOracleDeleteProcedure());
schema.replaceObject(buildOracleReadAllProcedure());
schema.replaceObject(buildOracleReadObjectProcedure());
schema.replaceObject(buildOracleInsertProcedure());
schema.replaceObject(buildOracleUpdateProcedure());
schema.replaceObject(buildOracle2OutCursorsProcedure());
schema.replaceObject(buildOracleLOOKUPRECORDTYPE());
schema.replaceObject(buildOracleLOOKUPTABLETYPE());
schema.replaceObject(buildOraclePackageStoredFunctionResultCursor());
try {
session.executeNonSelectingCall(new SQLCall("CREATE OR REPLACE PACKAGE BODY PackageFunction_ResultCursor IS " +
"FUNCTION BUSINESS_DATE (P_CODE IN VARCHAR2, P_LOOKUP_TBL IN SF_LOOKUP_TBL) RETURN REF_CURSOR IS " +
"L_CURSOR REF_CURSOR; BEGIN OPEN L_CURSOR FOR SELECT SYSDATE FROM DUAL; RETURN L_CURSOR; " +
"END BUSINESS_DATE; " +
"END PackageFunction_ResultCursor;"));
} catch (Exception exception) {
}
}
if (platform.isDB2()) {
schema.replaceObject(buildDB2SelectWithOutputAndResultSetProcedure());
}
if (platform.isPervasive()) {
schema.replaceObject(buildPervasiveReadAllProcedure());
schema.replaceObject(buildPervasiveInsertProcedure());
schema.replaceObject(buildPervasiveDeleteProcedure());
schema.replaceObject(buildPervasiveInsertProcedure());
schema.replaceObject(buildPervasiveReadAllProcedure());
schema.replaceObject(buildPervasiveReadObjectProcedure());
schema.replaceObject(buildPervasiveStoredProcedureInOutPut());
schema.replaceObject(buildPervasiveSelectWithOutputAndResultSetProcedure());
schema.replaceObject(buildPervasiveSelectWithOutputProcedure());
schema.replaceObject(buildPervasiveUpdateProcedure());
}
}
protected void setCommonSQL(Session session) {
ClassDescriptor empDescriptor = session.getDescriptor(Employee.class);
empDescriptor.getQueryManager().setDoesExistSQLString("select EMP_ID FROM EMPLOYEE WHERE EMP_ID = #EMP_ID");
OneToOneMapping managerMapping = (OneToOneMapping)empDescriptor.getMappingForAttributeName("manager");
managerMapping.setSelectionSQLString("select * FROM EMPLOYEE WHERE EMP_ID = #MANAGER_ID");
OneToOneMapping oneToOne = (OneToOneMapping)empDescriptor.getMappingForAttributeName("address");
oneToOne.setSelectionSQLString("select * FROM ADDRESS WHERE ADDRESS_ID = #ADDR_ID");
OneToManyMapping oneToMany = (OneToManyMapping)empDescriptor.getMappingForAttributeName("managedEmployees");
oneToMany.setSelectionSQLString("select E.*, S.* FROM EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID AND E.MANAGER_ID = #EMP_ID");
DirectCollectionMapping directCollection = (DirectCollectionMapping)empDescriptor.getMappingForAttributeName("responsibilitiesList");
directCollection.setSelectionSQLString("select DESCRIP FROM RESPONS WHERE EMP_ID = #EMP_ID");
directCollection.setDeleteAllSQLString("delete FROM RESPONS WHERE EMP_ID = #EMP_ID");
directCollection.setInsertSQLString("insert into RESPONS (EMP_ID, DESCRIP) values (#EMP_ID, #DESCRIP)");
ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
manyToMany.setDeleteAllSQLString("delete FROM PROJ_EMP WHERE EMP_ID = #EMP_ID");
manyToMany.setDeleteSQLString("delete FROM PROJ_EMP WHERE EMP_ID = #EMP_ID AND PROJ_ID = #PROJ_ID");
manyToMany.setInsertSQLString("insert into PROJ_EMP (EMP_ID, PROJ_ID) values (#EMP_ID, #PROJ_ID)");
ClassDescriptor projectDescriptor = session.getDescriptor(Project.class);
projectDescriptor.getQueryManager().setDoesExistSQLString("select PROJ_ID FROM PROJECT WHERE PROJ_ID = #PROJ_ID");
DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
deleteQuery.addCall(new SQLCall("delete FROM PROJECT WHERE PROJ_ID = #PROJ_ID"));
deleteQuery.addCall(new SQLCall("delete FROM LPROJECT WHERE PROJ_ID = #PROJ_ID"));
projectDescriptor.getQueryManager().setDeleteQuery(deleteQuery);
// Must disable locking for project delete to work.
projectDescriptor.setOptimisticLockingPolicy(null);
ClassDescriptor smallProjectDescriptor = session.getDescriptor(SmallProject.class);
smallProjectDescriptor.setOptimisticLockingPolicy(null);
ClassDescriptor largeProjectDescriptor = session.getDescriptor(LargeProject.class);
largeProjectDescriptor.setOptimisticLockingPolicy(null);
}
protected void setCustomSQL(Session session) {
setCommonSQL(session);
if (session.getLogin().getPlatform().isSybase()) {
setSybaseSQL(session);
} else if (session.getLogin().getPlatform().isSQLAnywhere()) {
EmployeeCustomSQLSystem.setSQLAnywhereSQL(session);
} else if (session.getLogin().getPlatform().isSQLServer()) {
setSQLServerSQL(session);
} else if (session.getLogin().isAnyOracleJDBCDriver()) {// Require output cursor support.
setOracleSQL(session);
} else if (session.getLogin().getPlatform().isPervasive()) {
setPervasiveSQL(session);
} else {
ClassDescriptor empDescriptor = session.getDescriptor(new Employee());
empDescriptor.getQueryManager().setReadObjectSQLString("select E.*, S.* FROM EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID AND E.EMP_ID = #EMP_ID");
empDescriptor.getQueryManager().setReadAllSQLString("select E.*, S.* FROM EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
ReadAllQuery readQuery = new ReadAllQuery();
readQuery.addCall(new SQLCall("select P.*, L.* FROM LPROJECT L, PROJECT P, PROJ_EMP PE WHERE ((P.PROJ_ID = L.PROJ_ID) AND (PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID))"));
readQuery.addCall(new SQLCall("select P.* FROM PROJECT P, PROJ_EMP PE WHERE (PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID) AND (P.PROJ_TYPE = 'S')"));
manyToMany.setCustomSelectionQuery(readQuery);
UpdateObjectQuery updateQuery = new UpdateObjectQuery();
updateQuery.addCall(new SQLCall("update EMPLOYEE SET END_DATE = #END_DATE, MANAGER_ID = #MANAGER_ID, START_DATE = #START_DATE, F_NAME = #F_NAME, L_NAME = #L_NAME, GENDER = #GENDER, ADDR_ID = #ADDR_ID, VERSION = ##VERSION WHERE ((EMP_ID = #EMP_ID) AND (VERSION = #VERSION))"));
updateQuery.addCall(new SQLCall("update SALARY SET SALARY = #SALARY WHERE (EMP_ID = #EMP_ID)"));
empDescriptor.getQueryManager().setUpdateQuery(updateQuery);
InsertObjectQuery insertQuery = new InsertObjectQuery();
insertQuery.addCall(new SQLCall("insert INTO EMPLOYEE (END_DATE, EMP_ID, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, VERSION, START_TIME, END_TIME) VALUES (#END_DATE, #EMP_ID, #MANAGER_ID, #START_DATE, #F_NAME, #L_NAME, #GENDER, #ADDR_ID, #VERSION, #START_TIME, #END_TIME)"));
insertQuery.addCall(new SQLCall("insert INTO SALARY (SALARY, EMP_ID) VALUES (#SALARY, #EMP_ID)"));
empDescriptor.getQueryManager().setInsertQuery(insertQuery);
DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
deleteQuery.addCall(new SQLCall("delete FROM EMPLOYEE WHERE EMP_ID = #EMP_ID"));
deleteQuery.addCall(new SQLCall("delete FROM SALARY WHERE EMP_ID = #EMP_ID"));
empDescriptor.getQueryManager().setDeleteQuery(deleteQuery);
}
}
protected void setOracleSQL(Session session) {
ClassDescriptor empDescriptor = session.getDescriptor(new Employee());
StoredProcedureCall call;
// Currently the rowcount does not work, so disable locking.
empDescriptor.setOptimisticLockingPolicy(null);
ReadObjectQuery readQuery = new ReadObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Read_Employee");
call.addNamedArgument("P_EMP_ID", "EMP_ID");
call.useNamedCursorOutputAsResultSet("RESULT_CURSOR");
readQuery.setCall(call);
empDescriptor.getQueryManager().setReadObjectQuery(readQuery);
ReadAllQuery readAllQuery = new ReadAllQuery();
call = new StoredProcedureCall();
call.setProcedureName("Read_All_Employees");
call.useNamedCursorOutputAsResultSet("RESULT_CURSOR");
readAllQuery.setCall(call);
empDescriptor.getQueryManager().setReadAllQuery(readAllQuery);
DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Delete_Employee");
call.addNamedArgument("P_EMP_ID", "EMP_ID");
deleteQuery.setCall(call);
empDescriptor.getQueryManager().setDeleteQuery(deleteQuery);
InsertObjectQuery insertQuery = new InsertObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Insert_Employee");
call.addNamedArgument("P_EMP_ID", "EMP_ID");
call.addNamedArgument("P_SALARY", "SALARY");
call.addNamedArgument("P_END_DATE", "END_DATE");
call.addNamedArgument("P_MANAGER_ID", "MANAGER_ID");
call.addNamedArgument("P_START_DATE", "START_DATE");
call.addNamedArgument("P_F_NAME", "F_NAME");
call.addNamedArgument("P_L_NAME", "L_NAME");
call.addNamedArgument("P_GENDER", "GENDER");
call.addNamedArgument("P_ADDR_ID", "ADDR_ID");
call.addNamedArgument("P_VERSION", "VERSION");
call.addNamedArgument("P_START_TIME", "START_TIME");
call.addNamedArgument("P_END_TIME", "END_TIME");
insertQuery.setCall(call);
empDescriptor.getQueryManager().setInsertQuery(insertQuery);
UpdateObjectQuery updateQuery = new UpdateObjectQuery();
call = new StoredProcedureCall();
call.setUsesBinding(true);
call.setShouldCacheStatement(true);
call.setProcedureName("Update_Employee");
call.addNamedArgument("P_EMP_ID", "EMP_ID");
call.addNamedArgument("P_SALARY", "SALARY");
call.addNamedArgument("P_END_DATE", "END_DATE");
call.addNamedArgument("P_MANAGER_ID", "MANAGER_ID");
call.addNamedArgument("P_START_DATE", "START_DATE");
call.addNamedArgument("P_F_NAME", "F_NAME");
call.addNamedArgument("P_L_NAME", "L_NAME");
call.addNamedArgument("P_GENDER", "GENDER");
call.addNamedArgument("P_ADDR_ID", "ADDR_ID");
call.addNamedArgument("P_START_TIME", "START_TIME");
call.addNamedArgument("P_END_TIME", "END_TIME");
call.addNamedOutputArgument("O_ERROR_CODE", "O_ERROR_CODE", Long.class);
updateQuery.setCall(call);
empDescriptor.getQueryManager().setUpdateQuery(updateQuery);
ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
manyToMany.setSelectionSQLString("select P.*, L.* FROM LPROJECT L, PROJECT P, PROJ_EMP PE WHERE ((P.PROJ_ID = L.PROJ_ID (+)) AND (PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID))");
}
protected void setPervasiveSQL(Session session) {
ClassDescriptor empDescriptor = session.getDescriptor(new Employee());
StoredProcedureCall call;
// Currently the rowcount does not work, so disable locking.
empDescriptor.setOptimisticLockingPolicy(null);
session.getLogin().getPlatform().setUsesNativeSQL(true);
ReadObjectQuery readQuery = new ReadObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Read_Employee");
call.addNamedArgument("EMP_ID");
call.setReturnsResultSet(true);
readQuery.setCall(call);
empDescriptor.getQueryManager().setReadObjectQuery(readQuery);
ReadAllQuery readAllQuery = new ReadAllQuery();
call = new StoredProcedureCall();
call.setProcedureName("Read_All_Employees");
call.setReturnsResultSet(true);
readAllQuery.setCall(call);
empDescriptor.getQueryManager().setReadAllQuery(readAllQuery);
DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Delete_Employee");
call.addNamedArgument("EMP_ID");
deleteQuery.setCall(call);
empDescriptor.getQueryManager().setDeleteQuery(deleteQuery);
InsertObjectQuery insertQuery = new InsertObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Insert_Employee");
call.setUsesBinding(true);
call.setShouldCacheStatement(true);
call.addNamedArgument("EMP_ID");
call.addNamedArgument("SALARY");
call.addNamedArgument("END_DATE");
call.addNamedArgument("MANAGER_ID");
call.addNamedArgument("START_DATE");
call.addNamedArgument("F_NAME");
call.addNamedArgument("L_NAME");
call.addNamedArgument("GENDER");
call.addNamedArgument("ADDR_ID");
call.addNamedArgument("START_TIME");
call.addNamedArgument("END_TIME");
insertQuery.setCall(call);
empDescriptor.getQueryManager().setInsertQuery(insertQuery);
UpdateObjectQuery updateQuery = new UpdateObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Update_Employee");
call.addNamedArgument("EMP_ID");
call.addNamedArgument("SALARY");
call.addNamedArgument("END_DATE");
call.addNamedArgument("MANAGER_ID");
call.addNamedArgument("START_DATE");
call.addNamedArgument("F_NAME");
call.addNamedArgument("L_NAME");
call.addNamedArgument("GENDER");
call.addNamedArgument("ADDR_ID");
//call.addNamedArgument("VERSION");
call.addNamedArgument("START_TIME");
call.addNamedArgument("END_TIME");
updateQuery.setCall(call);
empDescriptor.getQueryManager().setUpdateQuery(updateQuery);
ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
manyToMany.setSelectionSQLString("select P.*, L.* FROM PROJ_EMP PE, PROJECT P LEFT OUTER JOIN LPROJECT L ON (L.PROJ_ID = P.PROJ_ID) WHERE ((PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID))");
}
protected void setSQLServerSQL(Session session) {
ClassDescriptor empDescriptor = session.getDescriptor(new Employee());
StoredProcedureCall call;
// Currently the rowcount does not work, so disable locking.
empDescriptor.setOptimisticLockingPolicy(null);
session.getLogin().getPlatform().setUsesNativeSQL(true);
ReadObjectQuery readQuery = new ReadObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Read_Employee");
call.addNamedArgument("EMP_ID");
call.setReturnsResultSet(true);
readQuery.setCall(call);
empDescriptor.getQueryManager().setReadObjectQuery(readQuery);
ReadAllQuery readAllQuery = new ReadAllQuery();
call = new StoredProcedureCall();
call.setProcedureName("Read_All_Employees");
call.setReturnsResultSet(true);
readAllQuery.setCall(call);
empDescriptor.getQueryManager().setReadAllQuery(readAllQuery);
DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Delete_Employee");
call.addNamedArgument("EMP_ID");
deleteQuery.setCall(call);
empDescriptor.getQueryManager().setDeleteQuery(deleteQuery);
InsertObjectQuery insertQuery = new InsertObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Insert_Employee");
call.setUsesBinding(true);
call.setShouldCacheStatement(true);
call.addNamedArgument("EMP_ID");
call.addNamedArgument("SALARY");
call.addNamedArgument("END_DATE");
call.addNamedArgument("MANAGER_ID");
call.addNamedArgument("START_DATE");
call.addNamedArgument("F_NAME");
call.addNamedArgument("L_NAME");
call.addNamedArgument("GENDER");
call.addNamedArgument("ADDR_ID");
//call.addNamedArgument("VERSION");
call.addNamedOutputArgument("VERSION", "EMPLOYEE.VERSION", java.math.BigDecimal.class);
call.addNamedArgument("START_TIME");
call.addNamedArgument("END_TIME");
insertQuery.setCall(call);
empDescriptor.getQueryManager().setInsertQuery(insertQuery);
UpdateObjectQuery updateQuery = new UpdateObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Update_Employee");
call.addNamedArgument("EMP_ID");
call.addNamedArgument("SALARY");
call.addNamedArgument("END_DATE");
call.addNamedArgument("MANAGER_ID");
call.addNamedArgument("START_DATE");
call.addNamedArgument("F_NAME");
call.addNamedArgument("L_NAME");
call.addNamedArgument("GENDER");
call.addNamedArgument("ADDR_ID");
//call.addNamedArgument("VERSION");
call.addNamedArgument("START_TIME");
call.addNamedArgument("END_TIME");
updateQuery.setCall(call);
empDescriptor.getQueryManager().setUpdateQuery(updateQuery);
ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
manyToMany.setSelectionSQLString("select P.*, L.* FROM PROJ_EMP PE, PROJECT P LEFT OUTER JOIN LPROJECT L ON (L.PROJ_ID = P.PROJ_ID) WHERE ((PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID))");
}
protected void setSybaseSQL(Session session) {
ClassDescriptor empDescriptor = session.getDescriptor(new Employee());
StoredProcedureCall call;
ReadObjectQuery readQuery = new ReadObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Read_Employee");
call.addNamedArgument("EMP_ID");
call.setReturnsResultSet(true);
readQuery.setCall(call);
empDescriptor.getQueryManager().setReadObjectQuery(readQuery);
ReadAllQuery readAllQuery = new ReadAllQuery();
call = new StoredProcedureCall();
call.setProcedureName("Read_All_Employees");
call.setReturnsResultSet(true);
readAllQuery.setCall(call);
empDescriptor.getQueryManager().setReadAllQuery(readAllQuery);
DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Delete_Employee");
call.addNamedArgument("EMP_ID");
deleteQuery.setCall(call);
empDescriptor.getQueryManager().setDeleteQuery(deleteQuery);
InsertObjectQuery insertQuery = new InsertObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Insert_Employee");
call.setUsesBinding(true);
call.setShouldCacheStatement(true);
call.addNamedArgument("EMP_ID");
call.addNamedArgument("SALARY");
call.addNamedArgument("END_DATE");
call.addNamedArgument("MANAGER_ID");
call.addNamedArgument("START_DATE");
call.addNamedArgument("F_NAME");
call.addNamedArgument("L_NAME");
call.addNamedArgument("GENDER");
call.addNamedArgument("ADDR_ID");
call.addNamedArgument("VERSION");
call.addNamedArgument("START_TIME");
call.addNamedArgument("END_TIME");
call.addNamedInOutputArgumentValue("OUT_VERSION", new Long(0), "EMPLOYEE.VERSION", Long.class);
insertQuery.setCall(call);
empDescriptor.getQueryManager().setInsertQuery(insertQuery);
UpdateObjectQuery updateQuery = new UpdateObjectQuery();
call = new StoredProcedureCall();
call.setProcedureName("Update_Employee");
call.addNamedArgument("EMP_ID");
call.addNamedArgument("SALARY");
call.addNamedArgument("END_DATE");
call.addNamedArgument("MANAGER_ID");
call.addNamedArgument("START_DATE");
call.addNamedArgument("F_NAME");
call.addNamedArgument("L_NAME");
call.addNamedArgument("GENDER");
call.addNamedArgument("ADDR_ID");
call.addNamedArgument("VERSION");
call.addNamedArgument("START_TIME");
call.addNamedArgument("END_TIME");
updateQuery.setCall(call);
empDescriptor.getQueryManager().setUpdateQuery(updateQuery);
ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
manyToMany.setSelectionSQLString("select P.*, L.* FROM PROJ_EMP PE, PROJECT P LEFT OUTER JOIN LPROJECT L ON (L.PROJ_ID = P.PROJ_ID) WHERE ((PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID))");
}
private static void setSQLAnywhereSQL(final Session session) {
final ClassDescriptor employeeDescriptor = session.getDescriptor(new Employee());
final StoredProcedureCall readEmployeeCall = new StoredProcedureCall();
readEmployeeCall.setProcedureName("Read_Employee");
readEmployeeCall.addNamedArgument("_EMP_ID", "EMP_ID");
readEmployeeCall.setReturnsResultSet(true);
employeeDescriptor.getQueryManager().setReadObjectQuery(new ReadObjectQuery(readEmployeeCall));
final StoredProcedureCall readAllEmployeesCall = new StoredProcedureCall();
readAllEmployeesCall.setProcedureName("Read_All_Employees");
readAllEmployeesCall.setReturnsResultSet(true);
employeeDescriptor.getQueryManager().setReadAllQuery(new ReadAllQuery(readAllEmployeesCall));
final StoredProcedureCall deleteEmployeeCall = new StoredProcedureCall();
deleteEmployeeCall.setProcedureName("Delete_Employee");
deleteEmployeeCall.addNamedArgument("_EMP_ID", "EMP_ID");
employeeDescriptor.getQueryManager().setDeleteQuery(new DeleteObjectQuery(deleteEmployeeCall));
final StoredProcedureCall insertEmployeeCall = new StoredProcedureCall();
insertEmployeeCall.setProcedureName("Insert_Employee");
insertEmployeeCall.setUsesBinding(true);
insertEmployeeCall.setShouldCacheStatement(true);
insertEmployeeCall.addNamedArgument("_EMP_ID", "EMP_ID");
insertEmployeeCall.addNamedArgument("_SALARY", "SALARY");
insertEmployeeCall.addNamedArgument("_END_DATE", "END_DATE");
insertEmployeeCall.addNamedArgument("_MANAGER_ID", "MANAGER_ID");
insertEmployeeCall.addNamedArgument("_START_DATE", "START_DATE");
insertEmployeeCall.addNamedArgument("_F_NAME", "F_NAME");
insertEmployeeCall.addNamedArgument("_L_NAME", "L_NAME");
insertEmployeeCall.addNamedArgument("_GENDER", "GENDER");
insertEmployeeCall.addNamedArgument("_ADDR_ID", "ADDR_ID");
// insertEmployeeCall.addNamedArgument("_VERSION", "VERSION");
insertEmployeeCall.addNamedArgument("_START_TIME", "START_TIME");
insertEmployeeCall.addNamedArgument("_END_TIME", "END_TIME");
// The order of the arguments shouldn't matter because they are named,
// but SQLAnywhere 10 for some reason can't handle named parameters
// (In JPA tests:
// CALL SProc_Read_InOut(address_id_v = ? , street_v = ? )
// bind => [17 => ADDRESS_ID, => STREET]
// fails with java.sql.SQLException: [Sybase][ODBC Driver]Invalid parameter type
// Until that fixed, naming is switched off
// (SQLAnywherePlatform.shouldPrintStoredProcedureArgumentNameInCall() returns false),
// and therefore arguments should be passed exactly in the same order as parameters defined in the sp.
// After this is fixed (m.b. in SQLAnywhere 11?) the order of the attributes should be returned to original
// (where it does NOT follow the order of sp parameters).
insertEmployeeCall.addNamedArgument("_VERSION", "VERSION");
insertEmployeeCall.addNamedInOutputArgumentValue("_OUT_VERSION", new Long(0), "EMPLOYEE.VERSION", Long.class);
employeeDescriptor.getQueryManager().setInsertQuery(new InsertObjectQuery(insertEmployeeCall));
final StoredProcedureCall updateEmployeeCall = new StoredProcedureCall();
updateEmployeeCall.setProcedureName("Update_Employee");
updateEmployeeCall.addNamedArgument("_EMP_ID", "EMP_ID");
updateEmployeeCall.addNamedArgument("_SALARY", "SALARY");
updateEmployeeCall.addNamedArgument("_END_DATE", "END_DATE");
updateEmployeeCall.addNamedArgument("_MANAGER_ID", "MANAGER_ID");
updateEmployeeCall.addNamedArgument("_START_DATE", "START_DATE");
updateEmployeeCall.addNamedArgument("_F_NAME", "F_NAME");
updateEmployeeCall.addNamedArgument("_L_NAME", "L_NAME");
updateEmployeeCall.addNamedArgument("_GENDER", "GENDER");
updateEmployeeCall.addNamedArgument("_ADDR_ID", "ADDR_ID");
updateEmployeeCall.addNamedArgument("_VERSION", "VERSION");
updateEmployeeCall.addNamedArgument("_START_TIME", "START_TIME");
updateEmployeeCall.addNamedArgument("_END_TIME", "END_TIME");
employeeDescriptor.getQueryManager().setUpdateQuery(new UpdateObjectQuery(updateEmployeeCall));
final ManyToManyMapping manyToMany = (ManyToManyMapping) employeeDescriptor.getMappingForAttributeName("projects");
manyToMany.setSelectionSQLString("SELECT P.*, L.* FROM PROJ_EMP PE JOIN PROJECT P ON PE.PROJ_ID = P.PROJ_ID LEFT OUTER JOIN LPROJECT L ON P.PROJ_ID = L.PROJ_ID WHERE PE.EMP_ID = #EMP_ID");
}
}