Hi Gordon, Peter,
Attached are changes required to add a test.
A test simpleConcatTestWithParameters is added to attached
JUnitEJBQLSimpleTestSuite
I also needed to modify AdvancedTableCreator to add not null constraint
to BUYER_NAME and DESCRIP because the columns are part of a unique
constraint and derby does not allow a nullable column to be part of
unique constraint
While running JUnitEJBQLSimpleTestSuite against derby I noticed that
simpleConcatTest and simpleReverseConcatTest are failing because the
ejbql contains inlined string which are generated as binding in sql. No
cast is generated for this case. and the test fails. Peter informed me
that you have a fix for this in your local workspace which will fix this
issue.
Ran all tests from entity-persistence-tests against oracle successfully
I had a copy/paste issue in DB2Platform.java that I sent you where
writer.write(paramaterMarker); was duplicated. I have fixed this issue
in attached file.
----------------------------------------------------------------------------------------------
Not related to these changes, While running the test I noticed that a
extra comparison is generated in where clause. It is harmless for this
test. Do you think it might cause trouble for other cases?
SELECT t0.EMP_ID, t1.EMP_ID, t0.F_NAME, t0.L_NAME, t0.VERSION,
t1.SALARY, t0.START_DATE, t0.END_DATE, t0.ADDR_ID, t0.manager_EMP_ID
FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (((t0.F_NAME = VARCHAR(? ||
?)) AND (t1.EMP_ID = t0.EMP_ID)) AND (t1.EMP_ID = t0.EMP_ID))
Thanks,
Mitesh
Mitesh Meswani wrote:
> Hi Tom, Gordon, Peter,
>
> Please find attached the final changes that I intend to checkin for
> your review. I am working on a test case which I will send out in a
> short while.
> I have added another method to Expression so as to not change the
> public API. Added comemnts around type in ParameterExpression to
> indicate that it might not be always available.
> Adding inline what we discussed over phone for benefit of others....
>
> Peter Krogh wrote:
>> Thanks Mitesh,
>>
>> I have a few concerns with this fix.
>> 1. Public API has been changed (getParameter). We can't do
>> that. A new get parameter must be added that takes the third
>> argument.Indicate that it is internal using the INTERNAL: convention
>> in the javadocs
>>
> Done.
>> 2. The next concern is that type is now used everywhere, and
>> people will expect it to be set all the time. I doubt that it will
>> be. I think that this can be derisked by renaming the variable, or
>> comments.
>>
> Done.
>> 3. I am honestly quite concerned about the change that will
>> change what we print everytime we bind a string in DB2 and Derby.
>> CAST (? AS VARCHAR(32672)). I would much rather see a change for
>> this Derby work around issolated to Concat. What is the expected
>> impact on CTS? All strings will now be printed this way, is that
>> expected to work?
>>
> This workaround was suggested to us by DB2 driver team. We had been
> using this kind of CAST for our cmp2.x impl and have not seen any
> issue so far.
>> 4. Have you tested this fix with binding off? Will the SQL
>> generated look like: "VARCHAR( 'hi ' || 'there')"
>>
> Yes. The SQL would like above and yes the SQL would execute without
> any issues.
>
> Thanks,
> Mitesh
>>
>> I am wondering if non-bound values support the CAST (...) function?
>> (like this: CAST ('hi' AS VARCHAR(32672)) || CAST ('there' AS
>> VARCHAR(32672). ). If so, then maybe issolating this change to the
>> CONCAT function is better than changing the way EVERY string is bound
>> in DB2 and Derby.
>>
>> If this is the change, you could simply change the concat expression
>> operator to include the CAST in it and not change all the binding of
>> strings. I realize that your fix opens the door for other CASTable
>> types, but I am not sure that this is the right point to be adding
>> this type of wide sweeping change.
>>
>>
>> -----Original Message-----
>> From: Mitesh Meswani [mailto:mitesh.meswani_at_Sun.COM]
>> Sent: Friday, March 17, 2006 2:54 AM
>> To: persistence_at_glassfish.dev.java.net
>> Subject: Fix for issue 400
>>
>>
>> Attached code fixes issue 400. I still need to do some work/testing
>> before I can send this out for final review.
>>
>> Michael,
>> As you had suspected, the type information of parameter is not
>> propagated down to ParamaterExpression. I put together a primitive
>> mechanism to propagate this information.
>> -ParameterNode#generateExpression now passes getType() to
>> builder.getParameter
>> - Expression.getParameter now takes an extra argument "Object type"
>> which is passed and stored in ParameterExpression
>>
>> I consume the type in DB2Platform#writeParameterMarker. I am
>> currently assuming that type is a Class object and compare it to
>> String.Class to generate appropriate cast. Questions to you:
>> What are the possible type that a node can take?
>> Do you see any possible issues/improvements to the type propagation
>> mechanism above ?
>>
>> Thanks,
>> Mitesh
>>
>>
>>
/*
* The contents of this file are subject to the terms
* of the Common Development and Distribution License
* (the "License"). You may not use this file except
* in compliance with the License.
*
* You can obtain a copy of the license at
* glassfish/bootstrap/legal/CDDLv1.0.txt or
*
https://glassfish.dev.java.net/public/CDDLv1.0.html.
* See the License for the specific language governing
* permissions and limitations under the License.
*
* When distributing Covered Code, include this CDDL
* HEADER in each file and include the License file at
* glassfish/bootstrap/legal/CDDLv1.0.txt. If applicable,
* add the following below this CDDL HEADER, with the
* fields enclosed by brackets "[]" replaced with your
* own identifying information: Portions Copyright [yyyy]
* [name of copyright owner]
*/
// Copyright (c) 1998, 2006, Oracle. All rights reserved.
/*
* The contents of this file are subject to the terms
* of the Common Development and Distribution License
* (the "License"). You may not use this file except
* in compliance with the License.
*
* You can obtain a copy of the license at
* glassfish/bootstrap/legal/CDDLv1.0.txt or
*
https://glassfish.dev.java.net/public/CDDLv1.0.html.
* See the License for the specific language governing
* permissions and limitations under the License.
*
* When distributing Covered Code, include this CDDL
* HEADER in each file and include the License file at
* glassfish/bootstrap/legal/CDDLv1.0.txt. If applicable,
* add the following below this CDDL HEADER, with the
* fields enclosed by brackets "[]" replaced with your
* own identifying information: Portions Copyright [yyyy]
* [name of copyright owner]
*/
package oracle.toplink.essentials.testing.tests.ejb.ejbqltesting;
import java.math.BigDecimal;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import junit.framework.Assert;
import junit.framework.Test;
import junit.framework.TestSuite;
import oracle.toplink.essentials.expressions.Expression;
import oracle.toplink.essentials.expressions.ExpressionBuilder;
import oracle.toplink.essentials.expressions.ExpressionMath;
import oracle.toplink.essentials.sessions.Session;
import oracle.toplink.essentials.queryframework.ReadAllQuery;
import oracle.toplink.essentials.queryframework.ReadObjectQuery;
import oracle.toplink.essentials.queryframework.ReportQuery;
import oracle.toplink.essentials.sessions.UnitOfWork;
import oracle.toplink.essentials.testing.models.cmp3.advanced.Address;
import oracle.toplink.essentials.testing.models.cmp3.advanced.Employee;
import oracle.toplink.essentials.testing.models.cmp3.advanced.EmployeePopulator;
import oracle.toplink.essentials.testing.models.cmp3.advanced.PhoneNumber;
import oracle.toplink.essentials.testing.models.cmp3.advanced.SmallProject;
import oracle.toplink.essentials.testing.framework.junit.JUnitTestCase;
import junit.extensions.TestSetup;
import oracle.toplink.essentials.internal.ejb.cmp3.EntityManagerImpl;
import oracle.toplink.essentials.sessions.DatabaseSession;
import oracle.toplink.essentials.internal.sessions.AbstractSession;
import oracle.toplink.essentials.testing.models.cmp3.advanced.AdvancedTableCreator;
import oracle.toplink.essentials.threetier.Server;
/**
* <p>
* <b>Purpose</b>: Test simple EJBQL functionality.
* <p>
* <b>Description</b>: This class creates a test suite, initializes the database
* and adds tests to the suite.
* <p>
* <b>Responsibilities</b>:
* <ul>
* <li> Run tests for simple EJBQL functionality
* </ul>
* @see oracle.toplink.essentials.testing.models.cmp3.advanced.EmployeePopulator
* @see JUnitDomainObjectComparer
*/
public class JUnitEJBQLSimpleTestSuite extends JUnitTestCase {
static JUnitDomainObjectComparer comparer; //the global comparer object used in all tests
public JUnitEJBQLSimpleTestSuite()
{
super();
}
public JUnitEJBQLSimpleTestSuite(String name)
{
super(name);
}
//This method is run at the end of EVERY test case method
public void tearDown()
{
clearCache();
}
//This suite contains all tests contained in this class
public static Test suite()
{
TestSuite suite = new TestSuite();
suite.setName("JUnitEJBQLSimpleTestSuite");
suite.addTest(new JUnitEJBQLSimpleTestSuite("baseTestCase"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleABSTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleBetweenAndTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleBetweenTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleConcatTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleConcatTestWithParameters"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleConcatTestWithConstants1"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleDoubleOrTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleEqualsBracketsTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleEqualsTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleEqualsTestWithJoin"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleEqualsWithAs"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("collectionMemberIdentifierEqualsTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("abstractSchemaIdentifierEqualsTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("abstractSchemaIdentifierNotEqualsTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleInOneDotTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleInTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleLengthTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleLikeTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleLikeTestWithParameter"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleLikeEscapeTestWithParameter"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleNotBetweenTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleNotEqualsVariablesInteger"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleNotInTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleNotLikeTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleOrFollowedByAndTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleOrFollowedByAndTestWithStaticNames"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleOrTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleParameterTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleParameterTestChangingParameters"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleReverseAbsTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleReverseConcatTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleReverseEqualsTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleReverseLengthTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleReverseParameterTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleReverseSqrtTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleReverseSubstringTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleSqrtTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleSubstringTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleNullTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleNotNullTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("distinctTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("conformResultsInUnitOfWorkTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleModTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleIsEmptyTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleIsNotEmptyTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleApostrohpeTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("simpleEscapeUnderscoreTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("smallProjectMemberOfProjectsTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("smallProjectNOTMemberOfProjectsTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("selectCountOneToOneTest")); //bug 4616218
suite.addTest(new JUnitEJBQLSimpleTestSuite("selectOneToOneTest")); //employee.address doesnt not work
suite.addTest(new JUnitEJBQLSimpleTestSuite("selectPhonenumberDeclaredInINClauseTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("selectSimpleMemberOfWithParameterTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("selectSimpleNotMemberOfWithParameterTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("selectSimpleBetweenWithParameterTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("selectSimpleInWithParameterTest"));
suite.addTest(new JUnitEJBQLSimpleTestSuite("selectAverageQueryForByteColumnTest"));
return new TestSetup(suite) {
//This method is run at the end of the SUITE only
protected void tearDown() {
clearCache();
}
//This method is run at the start of the SUITE only
protected void setUp() {
//get session to start setup
DatabaseSession session = JUnitTestCase.getServerSession();
//create a new EmployeePopulator
EmployeePopulator employeePopulator = new EmployeePopulator();
new AdvancedTableCreator().replaceTables(session);
//initialize the global comparer object
comparer = new JUnitDomainObjectComparer();
//set the session for the comparer to use
comparer.setSession((AbstractSession)session.getActiveSession());
//Populate the tables
employeePopulator.buildExamples();
//Persist the examples in the database
employeePopulator.persistExample(session);
}
};
}
//Test case for selecting ALL employees from the database
public void baseTestCase()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
List expectedResult = em.getActiveSession().readAllObjects(Employee.class);
clearCache();
List result = em.createQuery("SELECT OBJECT(emp) FROM Employee emp").getResultList();
Assert.assertTrue("Base Test Case Failed", comparer.compareObjects(result, expectedResult));
}
//Test case for ABS function in EJBQL
public void simpleABSTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee) (em.getActiveSession().readAllObjects(Employee.class).firstElement());
clearCache();
String ejbqlString;
ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "ABS(emp.salary) = ";
ejbqlString = ejbqlString + expectedResult.getSalary();
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("ABS test failed", comparer.compareObjects(result, expectedResult));
}
//Test case for AND function in EJBQL
public void simpleBetweenAndTest()
{
BigDecimal empId = new BigDecimal(0);
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee employee = (Employee) (em.getActiveSession().readAllObjects(Employee.class).lastElement());
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("id").between(empId, employee.getId());
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.id BETWEEN " + empId + "AND " + employee.getId();
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Between And test failed", comparer.compareObjects( result, expectedResult));
}
//Test case for Between function in EJBQL
public void simpleBetweenTest()
{
BigDecimal empId = new BigDecimal(0);
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee employee = (Employee) (em.getActiveSession().readAllObjects(Employee.class).lastElement());
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("id").between(empId, employee.getId());
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.id BETWEEN " + empId.toString() + "AND " + employee.getId().toString();
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Between test failed", comparer.compareObjects(result, expectedResult));
}
//Test case for concat function in EJBQL
public void simpleConcatTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee) (em.getActiveSession().readAllObjects(Employee.class).firstElement());
clearCache();
String partOne, partTwo;
String ejbqlString;
partOne = expectedResult.getFirstName().substring(0, 2);
partTwo = expectedResult.getFirstName().substring(2);
ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "emp.firstName = ";
ejbqlString = ejbqlString + "CONCAT(\"";
ejbqlString = ejbqlString + partOne;
ejbqlString = ejbqlString + "\", \"";
ejbqlString = ejbqlString + partTwo;
ejbqlString = ejbqlString + "\")";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Concat test failed", comparer.compareObjects(result, expectedResult));
}
//Test case for concat function in EJBQL taking parameters
public void simpleConcatTestWithParameters()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee) (em.getActiveSession().readAllObjects(Employee.class).firstElement());
clearCache();
String partOne, partTwo;
String ejbqlString;
partOne = expectedResult.getFirstName().substring(0, 2);
partTwo = expectedResult.getFirstName().substring(2);
ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "emp.firstName = ";
ejbqlString = ejbqlString + "CONCAT(";
ejbqlString = ejbqlString + ":partOne";
ejbqlString = ejbqlString + ", ";
ejbqlString = ejbqlString + ":partTwo";
ejbqlString = ejbqlString + ")";
List result = em.createQuery(ejbqlString).setParameter("partOne", partOne).setParameter("partTwo", partTwo).getResultList();
Assert.assertTrue("Concat test failed", comparer.compareObjects(result, expectedResult));
}
//Test case for concat function with constants in EJBQL
public void simpleConcatTestWithConstants1()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee emp = (Employee) (em.getActiveSession().readAllObjects(Employee.class).firstElement());
String partOne;
String ejbqlString;
partOne = emp.getFirstName();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("firstName").concat("Smith").like(partOne + "Smith");
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector)em.getActiveSession().executeQuery(raq);
clearCache();
ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "CONCAT(emp.firstName,\"Smith\") LIKE ";
ejbqlString = ejbqlString + "\"" + partOne + "Smith\"";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Concat test with constraints failed", comparer.compareObjects(result, expectedResult));
}
//Test case for double OR function in EJBQL
public void simpleDoubleOrTest()
{
Employee emp1, emp2, emp3;
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
emp1 = (Employee) (em.getActiveSession().readAllObjects(Employee.class).firstElement());
emp2 = (Employee) (em.getActiveSession().readAllObjects(Employee.class).elementAt(1));
emp3 = (Employee) (em.getActiveSession().readAllObjects(Employee.class).elementAt(2));
clearCache();
Vector expectedResult = new Vector();
expectedResult.add(emp1);
expectedResult.add(emp2);
expectedResult.add(emp3);
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.id = " + emp1.getId() + "OR emp.id = " + emp2.getId() + "OR emp.id = " + emp3.getId();
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Double OR test failed", comparer.compareObjects(result, expectedResult));
}
//Test case for equals brackets in EJBQL
public void simpleEqualsBracketsTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee) (em.getActiveSession().readAllObjects(Employee.class).firstElement());
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "( emp.firstName = ";
ejbqlString = ejbqlString + "\"" + expectedResult.getFirstName() + "\")";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Equals brackets test failed", comparer.compareObjects(result,expectedResult));
}
//Test case for equals in EJBQL
public void simpleEqualsTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee) (em.getActiveSession().readAllObjects(Employee.class).firstElement());
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "emp.firstName = ";
ejbqlString = ejbqlString + "\"" + expectedResult.getFirstName() + "\"";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Equals test failed", comparer.compareObjects(expectedResult,result));
}
//Test case for equals with join in EJBQL
public void simpleEqualsTestWithJoin()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.anyOf("managedEmployees").get("address").get("city").equal("Ottawa");
Vector expectedResult = em.getActiveSession().readAllObjects(Employee.class,whereClause);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp, IN(emp.managedEmployees) managedEmployees " +
"WHERE managedEmployees.address.city = 'Ottawa'";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Equals test with Join failed", comparer.compareObjects(result, expectedResult));
}
public void simpleEqualsWithAs()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee) (em.getActiveSession().readAllObjects(Employee.class).firstElement());
clearCache();
Vector employeesUsed = new Vector();
employeesUsed.add(expectedResult);
String ejbqlString = "SELECT OBJECT(emp) FROM Employee AS emp WHERE emp.id = " + expectedResult.getId();
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Equals test with As failed", comparer.compareObjects(expectedResult,result));
}
public void collectionMemberIdentifierEqualsTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).firstElement();
clearCache();
PhoneNumber phoneNumber = (PhoneNumber) ((Vector) expectedResult.getPhoneNumbers()).firstElement();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp, IN (emp.phoneNumbers) phone " +
"WHERE phone = ?1";
List result = em.createQuery(ejbqlString).setParameter(1,phoneNumber).getResultList();
Assert.assertTrue("CollectionMemberIdentifierEqualsTest failed", comparer.compareObjects(expectedResult,result));
}
public void abstractSchemaIdentifierEqualsTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).firstElement();
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp = ?1";
List result = em.createQuery(ejbqlString).setParameter(1,expectedResult).getResultList();
Assert.assertTrue("abstractSchemaIdentifierEqualsTest failed", comparer.compareObjects(expectedResult,result));
}
public void abstractSchemaIdentifierNotEqualsTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Vector expectedResult = (Vector) em.getActiveSession().readAllObjects(Employee.class);
clearCache();
Employee emp = (Employee) expectedResult.firstElement();
expectedResult.removeElementAt(0);
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp <> ?1";
List result = em.createQuery(ejbqlString).setParameter(1,emp).getResultList();
Assert.assertTrue("abstractSchemaIdentifierNotEqualsTest failed", comparer.compareObjects(result, expectedResult));
}
public void simpleInOneDotTest()
{
//select a specifif employee using Expr Bob Smithn
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ReadObjectQuery roq = new ReadObjectQuery(Employee.class);
ExpressionBuilder empBldr = new ExpressionBuilder();
Expression exp1 = empBldr.get("firstName").equal("Bob");
Expression exp2 = empBldr.get("lastName").equal("Smith");
roq.setSelectionCriteria(exp1.and(exp2));
Employee expectedResult = (Employee) em.getActiveSession().executeQuery(roq);
clearCache();
PhoneNumber empPhoneNumbers = (PhoneNumber) ((Vector) expectedResult.getPhoneNumbers()).elementAt(0);
String ejbqlString = "SelecT OBJECT(emp) from Employee emp, in (emp.phoneNumbers) phone " +
"Where phone.areaCode = \"" + empPhoneNumbers.getAreaCode() + "\"" + "AND emp.firstName = \"" + expectedResult.getFirstName() + "\"";
ejbqlString = ejbqlString + "AND emp.lastName = \"" + expectedResult.getLastName() + "\"";
Employee result = (Employee) em.createQuery(ejbqlString).getSingleResult();
Assert.assertTrue("Simple In Dot Test failed", comparer.compareObjects(result, expectedResult));
}
public void selectAverageQueryForByteColumnTest() {
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
String ejbqlString = "Select AVG(emp.salary)from Employee emp";
Object result = em.createQuery(ejbqlString).getSingleResult();
Assert.assertTrue("AVG result type [" + result.getClass() + "] not of type Double", result.getClass() == Double.class);
}
public void simpleInTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.id IN (" + expectedResult.getId().toString() + ")";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple In Test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleLengthTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Assert.assertFalse("Warning SQL doesnot support LENGTH function", ((Session) JUnitTestCase.getServerSession()).getPlatform().isSQLServer());
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
clearCache();
String ejbqlString;
ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "LENGTH ( emp.firstName ) = ";
ejbqlString = ejbqlString + expectedResult.getFirstName().length();
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Length Test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleLikeTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
clearCache();
String partialFirstName = expectedResult.getFirstName().substring(0, 3) + "%";
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.firstName LIKE \"" + partialFirstName + "\"";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Like Test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleLikeTestWithParameter()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee emp = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
String partialFirstName = "%" + emp.getFirstName().substring(0, 3) + "%";
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
Vector parameters = new Vector();
parameters.add(partialFirstName);
ExpressionBuilder eb = new ExpressionBuilder();
Expression whereClause = eb.get("firstName").like(partialFirstName);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.firstName LIKE ?1";
List result = em.createQuery(ejbqlString).setParameter(1,partialFirstName).getResultList();
Assert.assertTrue("Simple Like Test with Parameter failed", comparer.compareObjects(result, expectedResult));
}
public void simpleLikeEscapeTestWithParameter()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Address expectedResult = new Address();
expectedResult.setCity("TAIYUAN");
expectedResult.setCountry("CHINA");
expectedResult.setProvince("SHANXI");
expectedResult.setPostalCode("030024");
expectedResult.setStreet("234 RUBY _Way");
Server serverSession = (Server) JUnitTestCase.getServerSession();
Session clientSession = serverSession.acquireClientSession();
UnitOfWork uow = clientSession.acquireUnitOfWork();
uow.registerObject(expectedResult);
uow.commit();
//test the apostrophe
String ejbqlString = "SELECT OBJECT(address) FROM Address address WHERE address.street LIKE :pattern ESCAPE :esc";
String patternString = null;
Character escChar = null;
// \ is always treated as escape in MySQL. Therefore ESCAPE '\' is considered a syntax error
if (((EntityManagerImpl)em.getDelegate()).getServerSession().getPlatform().isMySQL()) {
patternString = "234 RUBY $_Way";
escChar = new Character('$');
} else {
patternString = "234 RUBY \\_Way";
escChar = new Character('\\');
}
List result = em.createQuery(ejbqlString).setParameter("pattern", patternString).
setParameter("esc", escChar).getResultList();
Assert.assertTrue("Simple Escape Underscore test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleNotBetweenTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee emp1 = (Employee)em.getActiveSession().readAllObjects(Employee.class).firstElement();
Employee emp2 = (Employee)em.getActiveSession().readAllObjects(Employee.class).lastElement();
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
ExpressionBuilder eb = new ExpressionBuilder();
Expression whereClause = eb.get("id").between(emp1.getId(), emp2.getId()).not();
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector)getServerSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "emp.id NOT BETWEEN ";
ejbqlString = ejbqlString + emp1.getId().toString();
ejbqlString = ejbqlString + " AND ";
ejbqlString = ejbqlString + emp2.getId().toString();
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Not Between Test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleNotEqualsVariablesInteger()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Vector expectedResult = em.getActiveSession().readAllObjects(Employee.class);
clearCache();
Employee emp = (Employee) expectedResult.elementAt(0);
expectedResult.removeElementAt(0);
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.id <> " + emp.getId();
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Like Test with Parameter failed", comparer.compareObjects(result, expectedResult));
}
public void simpleNotInTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee emp = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
ExpressionBuilder builder = new ExpressionBuilder();
Vector idVector = new Vector();
idVector.add(emp.getId());
Expression whereClause = builder.get("id").notIn(idVector);
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.id NOT IN (" + emp.getId().toString() + ")";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Not In Test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleNotLikeTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee emp = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
String partialFirstName = emp.getFirstName().substring(0, 3) + "%";
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("firstName").notLike(partialFirstName);
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.firstName NOT LIKE \"" + partialFirstName + "\"";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Not Like Test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleOrFollowedByAndTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee emp1 = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
Employee emp2 = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(1);
Employee emp3 = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(2);
Vector expectedResult = new Vector();
expectedResult.add(emp1);
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.id = " + emp1.getId() + " OR emp.id = " + emp2.getId() + " AND emp.id = " + emp3.getId();
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Or followed by And Test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleOrFollowedByAndTestWithStaticNames()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("firstName").equal("John").or(
builder.get("firstName").equal("Bob").and(builder.get("lastName").equal("Smith")));
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.firstName = \"John\" OR emp.firstName = \"Bob\" AND emp.lastName = \"Smith\"";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Or followed by And With Static Names Test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleOrTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee emp1 = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
Employee emp2 = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(1);
Vector expectedResult = new Vector();
expectedResult.add(emp1);
expectedResult.add(emp2);
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.id = " + emp1.getId() + "OR emp.id = " + emp2.getId();
List result = em.createQuery(ejbqlString).getResultList();
clearCache();
Assert.assertTrue("Simple Or Test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleParameterTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
String parameterName = "firstName";
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("firstName").equal(builder.getParameter(parameterName));
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
raq.addArgument(parameterName);
Vector parameters = new Vector();
parameters.add(expectedResult.getFirstName());
Vector employees = (Vector)getServerSession().executeQuery(raq, parameters);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE " + "emp.firstName = ?1 ";
List result = em.createQuery(ejbqlString).setParameter(1,parameters.get(0)).getResultList();
Assert.assertTrue("Simple Parameter Test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleParameterTestChangingParameters()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee emp1 = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
Employee emp2 = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(1);
String parameterName = "firstName";
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("firstName").equal(builder.getParameter(parameterName));
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
raq.addArgument(parameterName);
Vector firstParameters = new Vector();
firstParameters.add(emp1.getFirstName());
Vector secondParameters = new Vector();
secondParameters.add(emp2.getFirstName());
Vector firstEmployees = (Vector)getServerSession().executeQuery(raq, firstParameters);
clearCache();
Vector secondEmployees = (Vector)getServerSession().executeQuery(raq, secondParameters);
clearCache();
Vector expectedResult = new Vector();
expectedResult.addAll(firstEmployees);
expectedResult.addAll(secondEmployees);
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE " + "emp.firstName = ?1 ";
List firstResultSet = em.createQuery(ejbqlString).setParameter(1,firstParameters.get(0)).getResultList();
clearCache();
List secondResultSet = em.createQuery(ejbqlString).setParameter(1,secondParameters.get(0)).getResultList();
clearCache();
Vector result = new Vector();
result.addAll(firstResultSet);
result.addAll(secondResultSet);
Assert.assertTrue("Simple Parameter Test Changing Parameters failed", comparer.compareObjects(result, expectedResult));
}
public void simpleReverseAbsTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE "+ expectedResult.getSalary() + " = ABS(emp.salary)";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Reverse Abs test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleReverseConcatTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
clearCache();
String partOne = expectedResult.getFirstName().substring(0, 2);
String partTwo = expectedResult.getFirstName().substring(2);
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "CONCAT(\"";
ejbqlString = ejbqlString + partOne;
ejbqlString = ejbqlString + "\", \"";
ejbqlString = ejbqlString + partTwo;
ejbqlString = ejbqlString + "\")";
ejbqlString = ejbqlString + " = emp.firstName";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Reverse Concat test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleReverseEqualsTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "\"" + expectedResult.getFirstName() + "\"";
ejbqlString = ejbqlString + " = emp.firstName";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Reverse Equals test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleReverseLengthTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + expectedResult.getFirstName().length();
ejbqlString = ejbqlString + " = LENGTH(emp.firstName)";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Reverse Length test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleReverseParameterTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee emp = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
clearCache();
String parameterName = "firstName";
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("firstName").equal(builder.getParameter(parameterName));
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
raq.addArgument(parameterName);
Vector parameters = new Vector();
parameters.add(emp.getFirstName());
Vector expectedResult = (Vector)getServerSession().executeQuery(raq, parameters);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "?1 = emp.firstName ";
List result = em.createQuery(ejbqlString).setParameter(1,parameters.get(0)).getResultList();
Assert.assertTrue("Simple Reverse Parameter test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleReverseSqrtTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ExpressionBuilder expbldr = new ExpressionBuilder();
Expression whereClause = expbldr.get("firstName").equal("SquareRoot").and(expbldr.get("lastName").equal("TestCase1"));
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
double salarySquareRoot = Math.sqrt((new Double(((Employee) expectedResult.firstElement()).getSalary()).doubleValue()));
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + salarySquareRoot;
ejbqlString = ejbqlString + " = SQRT(emp.salary)";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Reverse Square Root test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleReverseSubstringTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
clearCache();
String firstNamePart;
String ejbqlString;
firstNamePart = expectedResult.getFirstName().substring(0, 2);
ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "\"" + firstNamePart + "\"";
ejbqlString = ejbqlString + " = SUBSTRING(emp.firstName, 1, 2)";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Reverse SubString test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleSqrtTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ExpressionBuilder expbldr = new ExpressionBuilder();
Expression whereClause = expbldr.get("firstName").equal("SquareRoot").and(expbldr.get("lastName").equal("TestCase1"));
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
double salarySquareRoot = Math.sqrt((new Double(((Employee) expectedResult.firstElement()).getSalary()).doubleValue()));
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "SQRT(emp.salary) = ";
ejbqlString = ejbqlString + salarySquareRoot;
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Square Root test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleSubstringTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readAllObjects(Employee.class).elementAt(0);
clearCache();
String firstNamePart = expectedResult.getFirstName().substring(0, 2);
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "SUBSTRING(emp.firstName, 1, 2) = \"";//changed from 0, 2 to 1, 2(ZYP)
ejbqlString = ejbqlString + firstNamePart + "\"";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple SubString test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleNullTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee nullEmployee = new Employee();
nullEmployee.setFirstName(null);
nullEmployee.setLastName("Test");
Server serverSession = (Server) JUnitTestCase.getServerSession();
Session clientSession = serverSession.acquireClientSession();
UnitOfWork uow = clientSession.acquireUnitOfWork();
uow.registerObject(nullEmployee);
uow.commit();
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("firstName").isNull();
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.firstName IS NULL";
List result = em.createQuery(ejbqlString).getResultList();
uow = clientSession.acquireUnitOfWork();
uow.deleteObject(nullEmployee);
uow.commit();
Assert.assertTrue("Simple Null test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleNotNullTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee nullEmployee = new Employee();
nullEmployee.setFirstName(null);
nullEmployee.setLastName("Test");
Server serverSession = (Server) JUnitTestCase.getServerSession();
Session clientSession = serverSession.acquireClientSession();
UnitOfWork uow = clientSession.acquireUnitOfWork();
uow.registerObject(nullEmployee);
uow.commit();
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("firstName").isNull().not();
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.firstName IS NOT NULL";
List result = em.createQuery(ejbqlString).getResultList();
uow = clientSession.acquireUnitOfWork();
uow.deleteObject(nullEmployee);
uow.commit();
Assert.assertTrue("Simple Not Null test failed", comparer.compareObjects(result, expectedResult));
}
public void distinctTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ReadAllQuery raq = new ReadAllQuery();
ExpressionBuilder employee = new ExpressionBuilder();
Expression whereClause = employee.get("lastName").equal("Smith");
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
raq.useDistinct();
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT DISTINCT OBJECT(emp) FROM Employee emp WHERE emp.lastName = \'Smith\'";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Distinct test failed", comparer.compareObjects(result, expectedResult));
}
public void conformResultsInUnitOfWorkTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ReadObjectQuery readObjectQuery = new ReadObjectQuery();
readObjectQuery.setReferenceClass(Employee.class);
readObjectQuery.setEJBQLString("SELECT OBJECT(emp) FROM Employee emp WHERE emp.id = ?1");
readObjectQuery.conformResultsInUnitOfWork();
readObjectQuery.addArgument("1", Integer.class);
//ServerSession next
Server serverSession =((EntityManagerImpl)em.getDelegate()).getServerSession().getProject().createServerSession();
serverSession.setSessionLog(getServerSession().getSessionLog());
serverSession.login();
UnitOfWork unitOfWork = serverSession.acquireUnitOfWork();
Employee newEmployee = new Employee();
newEmployee.setId(new Integer(9000));
unitOfWork.registerObject(newEmployee);
Vector testV = new Vector();
testV.addElement(new Integer(9000));
Employee result = (Employee)unitOfWork.executeQuery(readObjectQuery, testV);
Assert.assertTrue("Conform Results In Unit of Work using ServerSession failed", comparer.compareObjects(result, newEmployee));
serverSession.logout();
}
public void simpleModTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Assert.assertFalse("Warning SQL/Sybase doesnot support MOD function", ((Session) JUnitTestCase.getServerSession()).getPlatform().isSQLServer() || ((Session) JUnitTestCase.getServerSession()).getPlatform().isSybase());
ReadAllQuery raq = new ReadAllQuery();
ExpressionBuilder employee = new ExpressionBuilder();
Expression whereClause = ExpressionMath.mod(employee.get("salary"), 2).greaterThan(0);
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE MOD(emp.salary, 2) > 0";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Mod test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleIsEmptyTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.isEmpty("phoneNumbers");
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector)em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.phoneNumbers IS EMPTY";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Is empty test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleIsNotEmptyTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.notEmpty("phoneNumbers");
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector)em.getActiveSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE emp.phoneNumbers IS NOT EMPTY";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple is not empty test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleApostrohpeTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Vector addresses = em.getActiveSession().readAllObjects(Address.class);
clearCache();
Address expectedResult = new Address();
Iterator addressesIterator = addresses.iterator();
while(addressesIterator.hasNext()){
expectedResult = (Address)addressesIterator.next();
if(expectedResult.getStreet().indexOf("Lost") != -1){
break;
}
}
String ejbqlString = "SELECT OBJECT(address) FROM Address address WHERE ";
ejbqlString = ejbqlString + "address.street = '234 I''m Lost Lane'";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple apostrophe test failed", comparer.compareObjects(result, expectedResult));
}
public void simpleEscapeUnderscoreTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Address expectedResult = new Address();
expectedResult.setCity("Perth");
expectedResult.setCountry("Canada");
expectedResult.setProvince("ONT");
expectedResult.setPostalCode("Y3Q2N9");
expectedResult.setStreet("234 Wandering _Way");
Server serverSession = (Server) JUnitTestCase.getServerSession();
Session clientSession = serverSession.acquireClientSession();
UnitOfWork uow = clientSession.acquireUnitOfWork();
uow.registerObject(expectedResult);
uow.commit();
//test the apostrophe
String ejbqlString = "SELECT OBJECT(address) FROM Address address WHERE ";
// \ is always treated as escape in MySQL. Therefore ESCAPE '\' is considered a syntax error
if (((EntityManagerImpl)em.getDelegate()).getServerSession().getPlatform().isMySQL()) {
ejbqlString = ejbqlString + "address.street LIKE '234 Wandering $_Way' ESCAPE '$'";
} else {
ejbqlString = ejbqlString + "address.street LIKE '234 Wandering \\_Way' ESCAPE '\\'";
}
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Escape Underscore test failed", comparer.compareObjects(result, expectedResult));
}
public void smallProjectMemberOfProjectsTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ReadAllQuery query = new ReadAllQuery();
Expression selectionCriteria = new ExpressionBuilder().anyOf("projects").equal(
new ExpressionBuilder(SmallProject.class));
query.setSelectionCriteria(selectionCriteria);
query.setReferenceClass(Employee.class);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(query);
clearCache();
//setup the EJBQL to do the same
String ejbqlString = "SELECT OBJECT(employee) FROM Employee employee, SmallProject sp WHERE ";
ejbqlString = ejbqlString + "sp MEMBER OF employee.projects";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple small Project Member Of Projects test failed", comparer.compareObjects(result, expectedResult));
}
public void smallProjectNOTMemberOfProjectsTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
//query for those employees with Project named "Enterprise" (which should be
//a SmallProject)
ReadObjectQuery smallProjectQuery = new ReadObjectQuery();
smallProjectQuery.setReferenceClass(SmallProject.class);
smallProjectQuery.setSelectionCriteria(
new ExpressionBuilder().get("name").equal("Enterprise"));
SmallProject smallProject = (SmallProject)em.getActiveSession().executeQuery(smallProjectQuery);
ReadAllQuery query = new ReadAllQuery();
query.addArgument("smallProject");
Expression selectionCriteria = new ExpressionBuilder().noneOf("projects",
new ExpressionBuilder().equal(new ExpressionBuilder().getParameter("smallProject")));
query.setSelectionCriteria(selectionCriteria);
query.setReferenceClass(Employee.class);
Vector arguments = new Vector();
arguments.add(smallProject);
Vector expectedResult = (Vector)em.getActiveSession().executeQuery(query, arguments);
//setup the EJBQL to do the same
String ejbqlString = "SELECT OBJECT(employee) FROM Employee employee WHERE ";
ejbqlString = ejbqlString + "?1 NOT MEMBER OF employee.projects";
List result = em.createQuery(ejbqlString).setParameter(1,smallProject).getResultList();
Assert.assertTrue("Simple small Project NOT Member Of Projects test failed", comparer.compareObjects(result, expectedResult));
}
//This test demonstrates the bug 4616218, waiting for bug fix
public void selectCountOneToOneTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ReportQuery query = new ReportQuery();
query.setReferenceClass(PhoneNumber.class);
query.addCount("COUNT", new ExpressionBuilder().get("owner").distinct());
query.returnSingleAttribute();
query.dontRetrievePrimaryKeys();
query.setName("selectEmployeesThatHavePhoneNumbers");
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(query);
clearCache();
//setup the EJBQL to do the same
String ejbqlString = "SELECT COUNT(DISTINCT phone.owner) FROM PhoneNumber phone";
List result = (List) em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Select Count One To One test failed", expectedResult.elementAt(0).equals(result.get(0)));
}
public void selectOneToOneTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Address.class);
query.useDistinct();
ExpressionBuilder employeeBuilder = new ExpressionBuilder(Employee.class);
Expression selectionCriteria = new ExpressionBuilder(Address.class).equal(
employeeBuilder.get("address")).and(
employeeBuilder.get("lastName").like("%Way%")
);
query.setSelectionCriteria(selectionCriteria);
Vector expectedResult = (Vector)em.getActiveSession().executeQuery(query);
clearCache();
//setup the EJBQL to do the same
String ejbqlString = "SELECT DISTINCT employee.address FROM Employee employee WHERE employee.lastName LIKE '%Way%'";
List result = (List) em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple Select One To One test failed", comparer.compareObjects(result, expectedResult));
}
public void selectPhonenumberDeclaredInINClauseTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ReadAllQuery query = new ReadAllQuery();
ExpressionBuilder employeeBuilder = new ExpressionBuilder(Employee.class);
Expression phoneAnyOf = employeeBuilder.anyOf("phoneNumbers");
ExpressionBuilder phoneBuilder = new ExpressionBuilder(PhoneNumber.class);
Expression selectionCriteria = phoneBuilder.equal(employeeBuilder.anyOf("phoneNumbers")).and(
phoneAnyOf.get("number").notNull());
query.setSelectionCriteria(selectionCriteria);
query.setReferenceClass(PhoneNumber.class);
query.addAscendingOrdering("number");
query.addAscendingOrdering("areaCode");
Vector expectedResult = (Vector)em.getActiveSession().executeQuery(query);
clearCache();
//setup the EJBQL to do the same
String ejbqlString = "Select Distinct Object(p) from Employee emp, IN(emp.phoneNumbers) p WHERE ";
ejbqlString = ejbqlString + "p.number IS NOT NULL ORDER BY p.number, p.areaCode";
List result = (List) em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Simple select Phonenumber Declared In IN Clause test failed", comparer.compareObjects(result, expectedResult));
}
public void selectSimpleMemberOfWithParameterTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Employee expectedResult = (Employee)em.getActiveSession().readObject(Employee.class);
PhoneNumber phone = new PhoneNumber();
phone.setAreaCode("613");
phone.setNumber("1234567");
phone.setType("cell");
Server serverSession = (Server) JUnitTestCase.getServerSession();
Session clientSession = serverSession.acquireClientSession();
UnitOfWork uow = clientSession.acquireUnitOfWork();
PhoneNumber phoneClone = (PhoneNumber) uow.registerObject(phone);
Employee empClone = (Employee) uow.registerObject(expectedResult);
phoneClone.setOwner(empClone);
empClone.addPhoneNumber(phoneClone);
uow.registerObject(phone);
uow.commit();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp " +
"WHERE ?1 MEMBER OF emp.phoneNumbers";
Vector parameters = new Vector();
parameters.add(phone);
List result = em.createQuery(ejbqlString).setParameter(1,phone).getResultList();
uow = clientSession.acquireUnitOfWork();
uow.deleteObject(phone);
uow.commit();
Assert.assertTrue("Select simple member of with parameter test failed", comparer.compareObjects(result, expectedResult));
}
public void selectSimpleNotMemberOfWithParameterTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Vector expectedResult = (Vector)em.getActiveSession().readAllObjects(Employee.class);
clearCache();
Employee emp = (Employee) expectedResult.get(0);
expectedResult.remove(0);
PhoneNumber phone = new PhoneNumber();
phone.setAreaCode("613");
phone.setNumber("1234567");
phone.setType("cell");
Server serverSession = (Server) JUnitTestCase.getServerSession();
Session clientSession = serverSession.acquireClientSession();
UnitOfWork uow = clientSession.acquireUnitOfWork();
emp = (Employee)uow.readObject(emp);
PhoneNumber phoneClone = (PhoneNumber)uow.registerObject(phone);
phoneClone.setOwner(emp);
emp.addPhoneNumber(phoneClone);
uow.commit();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp " +
"WHERE ?1 NOT MEMBER OF emp.phoneNumbers";
Vector parameters = new Vector();
parameters.add(phone);
List result = em.createQuery(ejbqlString).setParameter(1,phone).getResultList();
uow = clientSession.acquireUnitOfWork();
uow.deleteObject(phone);
uow.commit();
Assert.assertTrue("Select simple Not member of with parameter test failed", comparer.compareObjects(result, expectedResult));
}
public void selectSimpleBetweenWithParameterTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Vector employees = (Vector)em.getActiveSession().readAllObjects(Employee.class);
BigDecimal empId1 = new BigDecimal(0);
Employee emp2 = (Employee)employees.lastElement();
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
ExpressionBuilder eb = new ExpressionBuilder();
Expression whereClause = eb.get("id").between(empId1, emp2.getId());
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector)getServerSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "emp.id BETWEEN ?1 AND ?2";
List result = (List) em.createQuery(ejbqlString).setParameter(1,empId1).setParameter(2,emp2.getId()).getResultList();
Assert.assertTrue("Simple select between with parameter test failed", comparer.compareObjects(result, expectedResult));
}
public void selectSimpleInWithParameterTest()
{
oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
Vector employees = (Vector)em.getActiveSession().readAllObjects(Employee.class);
BigDecimal empId1 = new BigDecimal(0);
Employee emp2 = (Employee)employees.lastElement();
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(Employee.class);
ExpressionBuilder eb = new ExpressionBuilder();
Vector vec = new Vector();
vec.add(empId1);
vec.add(emp2.getId());
Expression whereClause = eb.get("id").in(vec);
raq.setSelectionCriteria(whereClause);
Vector expectedResult = (Vector)getServerSession().executeQuery(raq);
clearCache();
String ejbqlString = "SELECT OBJECT(emp) FROM Employee emp WHERE ";
ejbqlString = ejbqlString + "emp.id IN (?1, ?2)";
List result = (List) em.createQuery(ejbqlString).setParameter(1,empId1).setParameter(2,emp2.getId()).getResultList();
Assert.assertTrue("Simple select between with parameter test failed", comparer.compareObjects(result, expectedResult));
}
public static void main(String[] args)
{
junit.swingui.TestRunner.main(args);
}
}
/*
* The contents of this file are subject to the terms
* of the Common Development and Distribution License
* (the "License"). You may not use this file except
* in compliance with the License.
*
* You can obtain a copy of the license at
* glassfish/bootstrap/legal/CDDLv1.0.txt or
*
https://glassfish.dev.java.net/public/CDDLv1.0.html.
* See the License for the specific language governing
* permissions and limitations under the License.
*
* When distributing Covered Code, include this CDDL
* HEADER in each file and include the License file at
* glassfish/bootstrap/legal/CDDLv1.0.txt. If applicable,
* add the following below this CDDL HEADER, with the
* fields enclosed by brackets "[]" replaced with your
* own identifying information: Portions Copyright [yyyy]
* [name of copyright owner]
*/
// Copyright (c) 1998, 2006, Oracle. All rights reserved.
package oracle.toplink.essentials.platform.database;
import java.io.*;
import java.sql.*;
import java.util.*;
import oracle.toplink.essentials.exceptions.ValidationException;
import oracle.toplink.essentials.expressions.*;
import oracle.toplink.essentials.internal.helper.*;
import oracle.toplink.essentials.queryframework.*;
import oracle.toplink.essentials.internal.databaseaccess.*;
import oracle.toplink.essentials.internal.sessions.AbstractSession;
import oracle.toplink.essentials.internal.expressions.ParameterExpression;
/**
* <p><b>Purpose</b>: Provides DB2 specific behaviour.
* <p><b>Responsibilities</b>:<ul>
* <li> Native SQL for byte[], Date, Time, & Timestamp.
* <li> Support for table qualified names.
* </ul>
*
* @since TOPLink/Java 1.0
*/
public class DB2Platform extends oracle.toplink.essentials.platform.database.DatabasePlatform {
/**
* INTERNAL:
* Append a byte[] in native DB@ format BLOB(hexString) if usesNativeSQL(),
* otherwise use ODBC format from DatabasePLatform.
*/
protected void appendByteArray(byte[] bytes, Writer writer) throws IOException {
if (usesNativeSQL()) {
writer.write("BLOB(x'");
Helper.writeHexString(bytes, writer);
writer.write("')");
} else {
super.appendByteArray(bytes, writer);
}
}
/**
* INTERNAL:
* Appends the Date in native format if usesNativeSQL() otherwise use ODBC format from DatabasePlatform.
* Native format: 'mm/dd/yyyy'
*/
protected void appendDate(java.sql.Date date, Writer writer) throws IOException {
if (usesNativeSQL()) {
appendDB2Date(date, writer);
} else {
super.appendDate(date, writer);
}
}
/**
* INTERNAL:
* Write a timestamp in DB2 specific format (mm/dd/yyyy).
*/
protected void appendDB2Date(java.sql.Date date, Writer writer) throws IOException {
writer.write("'");
// PERF: Avoid deprecated get methods, that are now very inefficient and used from toString.
Calendar calendar = Helper.allocateCalendar();
calendar.setTime(date);
if ((calendar.get(Calendar.MONTH) + 1) < 10) {
writer.write('0');
}
writer.write(Integer.toString(calendar.get(Calendar.MONTH) + 1));
writer.write('/');
if (calendar.get(Calendar.DATE) < 10) {
writer.write('0');
}
writer.write(Integer.toString(calendar.get(Calendar.DATE)));
writer.write('/');
writer.write(Integer.toString(calendar.get(Calendar.YEAR)));
writer.write("'");
Helper.releaseCalendar(calendar);
}
/**
* INTERNAL:
* Write a timestamp in DB2 specific format (yyyy-mm-dd-hh.mm.ss.ffffff).
*/
protected void appendDB2Timestamp(java.sql.Timestamp timestamp, Writer writer) throws IOException {
// PERF: Avoid deprecated get methods, that are now very inefficient and used from toString.
Calendar calendar = Helper.allocateCalendar();
calendar.setTime(timestamp);
writer.write(Helper.printDate(calendar));
writer.write('-');
if (calendar.get(Calendar.HOUR_OF_DAY) < 10) {
writer.write('0');
}
writer.write(Integer.toString(calendar.get(Calendar.HOUR_OF_DAY)));
writer.write('.');
if (calendar.get(Calendar.MINUTE) < 10) {
writer.write('0');
}
writer.write(Integer.toString(calendar.get(Calendar.MINUTE)));
writer.write('.');
if (calendar.get(Calendar.SECOND) < 10) {
writer.write('0');
}
writer.write(Integer.toString(calendar.get(Calendar.SECOND)));
writer.write('.');
Helper.releaseCalendar(calendar);
// Must truncate the nanos to six decimal places,
// it is actually a complex algorithm...
String nanoString = Integer.toString(timestamp.getNanos());
int numberOfZeros = 0;
for (int num = Math.min(9 - nanoString.length(), 6); num > 0; num--) {
writer.write('0');
numberOfZeros++;
}
if ((nanoString.length() + numberOfZeros) > 6) {
nanoString = nanoString.substring(0, (6 - numberOfZeros));
}
writer.write(nanoString);
}
/**
* INTERNAL:
* Write a timestamp in DB2 specific format (yyyy-mm-dd-hh.mm.ss.ffffff).
*/
protected void appendDB2Calendar(Calendar calendar, Writer writer) throws IOException {
int hour;
int minute;
int second;
if (!Helper.getDefaultTimeZone().equals(calendar.getTimeZone())) {
// Must convert the calendar to the local timezone if different, as dates have no timezone (always local).
Calendar localCalendar = Helper.allocateCalendar();
JavaPlatform.setTimeInMillis(localCalendar, JavaPlatform.getTimeInMillis(calendar));
hour = calendar.get(Calendar.HOUR_OF_DAY);
minute = calendar.get(Calendar.MINUTE);
second = calendar.get(Calendar.SECOND);
Helper.releaseCalendar(localCalendar);
} else {
hour = calendar.get(Calendar.HOUR_OF_DAY);
minute = calendar.get(Calendar.MINUTE);
second = calendar.get(Calendar.SECOND);
}
writer.write(Helper.printDate(calendar));
writer.write('-');
if (hour < 10) {
writer.write('0');
}
writer.write(Integer.toString(hour));
writer.write('.');
if (minute < 10) {
writer.write('0');
}
writer.write(Integer.toString(minute));
writer.write('.');
if (second < 10) {
writer.write('0');
}
writer.write(Integer.toString(second));
writer.write('.');
// Must truncate the nanos to six decimal places,
// it is actually a complex algorithm...
String millisString = Integer.toString(calendar.get(Calendar.MILLISECOND));
int numberOfZeros = 0;
for (int num = Math.min(3 - millisString.length(), 3); num > 0; num--) {
writer.write('0');
numberOfZeros++;
}
if ((millisString.length() + numberOfZeros) > 3) {
millisString = millisString.substring(0, (3 - numberOfZeros));
}
writer.write(millisString);
}
/**
* INTERNAL:
* Append the Time in Native format if usesNativeSQL() otherwise use ODBC format from DAtabasePlatform.
* Native Format: 'hh:mm:ss'
*/
protected void appendTime(java.sql.Time time, Writer writer) throws IOException {
if (usesNativeSQL()) {
writer.write("'");
writer.write(Helper.printTime(time));
writer.write("'");
} else {
super.appendTime(time, writer);
}
}
/**
* INTERNAL:
* Append the Timestamp in native format if usesNativeSQL() is true otherwise use ODBC format from DatabasePlatform.
* Native format: 'YYYY-MM-DD-hh.mm.ss.SSSSSS'
*/
protected void appendTimestamp(java.sql.Timestamp timestamp, Writer writer) throws IOException {
if (usesNativeSQL()) {
writer.write("'");
appendDB2Timestamp(timestamp, writer);
writer.write("'");
} else {
super.appendTimestamp(timestamp, writer);
}
}
/**
* INTERNAL:
* Append the Timestamp in native format if usesNativeSQL() is true otherwise use ODBC format from DatabasePlatform.
* Native format: 'YYYY-MM-DD-hh.mm.ss.SSSSSS'
*/
protected void appendCalendar(Calendar calendar, Writer writer) throws IOException {
if (usesNativeSQL()) {
writer.write("'");
appendDB2Calendar(calendar, writer);
writer.write("'");
} else {
super.appendCalendar(calendar, writer);
}
}
protected Hashtable buildFieldTypes() {
Hashtable fieldTypeMapping = new Hashtable();
fieldTypeMapping.put(Boolean.class, new FieldTypeDefinition("SMALLINT DEFAULT 0", false));
fieldTypeMapping.put(Integer.class, new FieldTypeDefinition("INTEGER", false));
fieldTypeMapping.put(Long.class, new FieldTypeDefinition("INTEGER", false));
fieldTypeMapping.put(Float.class, new FieldTypeDefinition("FLOAT", false));
fieldTypeMapping.put(Double.class, new FieldTypeDefinition("FLOAT", false));
fieldTypeMapping.put(Short.class, new FieldTypeDefinition("SMALLINT", false));
fieldTypeMapping.put(Byte.class, new FieldTypeDefinition("SMALLINT", false));
fieldTypeMapping.put(java.math.BigInteger.class, new FieldTypeDefinition("BIGINT", false));
fieldTypeMapping.put(java.math.BigDecimal.class, new FieldTypeDefinition("DECIMAL", 15));
fieldTypeMapping.put(Number.class, new FieldTypeDefinition("DECIMAL", 15));
fieldTypeMapping.put(String.class, new FieldTypeDefinition("VARCHAR", 255));
fieldTypeMapping.put(Character.class, new FieldTypeDefinition("CHAR", 1));
fieldTypeMapping.put(Byte[].class, new FieldTypeDefinition("BLOB", 64000));
fieldTypeMapping.put(Character[].class, new FieldTypeDefinition("CLOB", 64000));
fieldTypeMapping.put(byte[].class, new FieldTypeDefinition("BLOB", 64000));
fieldTypeMapping.put(char[].class, new FieldTypeDefinition("CLOB", 64000));
fieldTypeMapping.put(java.sql.Blob.class, new FieldTypeDefinition("BLOB", 64000));
fieldTypeMapping.put(java.sql.Clob.class, new FieldTypeDefinition("CLOB", 64000));
fieldTypeMapping.put(java.sql.Date.class, new FieldTypeDefinition("DATE", false));
fieldTypeMapping.put(java.sql.Time.class, new FieldTypeDefinition("TIME", false));
fieldTypeMapping.put(java.sql.Timestamp.class, new FieldTypeDefinition("TIMESTAMP", false));
return fieldTypeMapping;
}
/**
* INTERNAL:
* returns the maximum number of characters that can be used in a field
* name on this platform.
*/
public int getMaxFieldNameSize() {
return 128;
}
/**
* INTERNAL:
* returns the maximum number of characters that can be used in a foreign key
* name on this platform.
*/
public int getMaxForeignKeyNameSize() {
return 18;
}
/**
* INTERNAL:
* Return the catalog information through using the native SQL catalog selects.
* This is required because many JDBC driver do not support meta-data.
* Willcards can be passed as arguments.
*/
public Vector getNativeTableInfo(String table, String creator, AbstractSession session) {
String query = "SELECT * FROM SYSIBM.SYSTABLES WHERE TBCREATOR NOT IN ('SYS', 'SYSTEM')";
if (table != null) {
if (table.indexOf('%') != -1) {
query = query + " AND TBNAME LIKE " + table;
} else {
query = query + " AND TBNAME = " + table;
}
}
if (creator != null) {
if (creator.indexOf('%') != -1) {
query = query + " AND TBCREATOR LIKE " + creator;
} else {
query = query + " AND TBCREATOR = " + creator;
}
}
return session.executeSelectingCall(new oracle.toplink.essentials.queryframework.SQLCall(query));
}
/**
* INTERNAL:
* Used for sp calls.
*/
public String getProcedureCallHeader() {
return "CALL ";
}
/**
* INTERNAL:
*/
public String getSelectForUpdateString() {
return " FOR UPDATE";
}
/**
* INTERNAL:
* Used for stored procedure defs.
*/
public String getProcedureEndString() {
return "END";
}
/**
* INTERNAL:
* Used for stored procedure defs.
*/
public String getProcedureBeginString() {
return "BEGIN";
}
/**
* INTERNAL:
* Used for stored procedure defs.
*/
public String getProcedureAsString() {
return "";
}
/**
* INTERNAL:
* This is required in the construction of the stored procedures with
* output parameters
*/
public boolean shouldPrintOutputTokenAtStart() {
return true;
}
/**
* INTERNAL:
* This method returns the query to select the timestamp
* from the server for DB2.
*/
public ValueReadQuery getTimestampQuery() {
if (timestampQuery == null) {
timestampQuery = new ValueReadQuery();
timestampQuery.setSQLString("SELECT DISTINCT CURRENT TIMESTAMP FROM SYSIBM.SYSTABLES");
}
return timestampQuery;
}
/**
* INTERNAL:
* Initialize any platform-specific operators
*/
protected void initializePlatformOperators() {
super.initializePlatformOperators();
addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToUpperCase, "UCASE"));
addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToLowerCase, "LCASE"));
addOperator(concatOperator());
addOperator(ExpressionOperator.simpleTwoArgumentFunction(ExpressionOperator.Instring, "Locate"));
addOperator(todayOperator());
addOperator(currentDateOperator());
//CR#2811076 some missing DB2 functions added.
addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToNumber, "DECIMAL"));
addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToChar, "CHAR"));
addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.DateToString, "CHAR"));
addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToDate, "DATE"));
}
public boolean isDB2() {
return true;
}
/**
* INTERNAL:
* Builds a table of maximum numeric values keyed on java class. This is used for type testing but
* might also be useful to end users attempting to sanitize values.
* <p><b>NOTE</b>: BigInteger & BigDecimal maximums are dependent upon their precision & Scale
*/
public Hashtable maximumNumericValues() {
Hashtable values = new Hashtable();
values.put(Integer.class, new Integer(Integer.MAX_VALUE));
values.put(Long.class, new Long((long)Integer.MAX_VALUE));
values.put(Float.class, new Float(123456789));
values.put(Double.class, new Double((double)Float.MAX_VALUE));
values.put(Short.class, new Short(Short.MAX_VALUE));
values.put(Byte.class, new Byte(Byte.MAX_VALUE));
values.put(java.math.BigInteger.class, new java.math.BigInteger("999999999999999"));
values.put(java.math.BigDecimal.class, new java.math.BigDecimal("0.999999999999999"));
return values;
}
/**
* INTERNAL:
* Builds a table of minimum numeric values keyed on java class. This is used for type testing but
* might also be useful to end users attempting to sanitize values.
* <p><b>NOTE</b>: BigInteger & BigDecimal minimums are dependent upon their precision & Scale
*/
public Hashtable minimumNumericValues() {
Hashtable values = new Hashtable();
values.put(Integer.class, new Integer(Integer.MIN_VALUE));
values.put(Long.class, new Long((long)Integer.MIN_VALUE));
values.put(Float.class, new Float(-123456789));
values.put(Double.class, new Double((double)Float.MIN_VALUE));
values.put(Short.class, new Short(Short.MIN_VALUE));
values.put(Byte.class, new Byte(Byte.MIN_VALUE));
values.put(java.math.BigInteger.class, new java.math.BigInteger("-999999999999999"));
values.put(java.math.BigDecimal.class, new java.math.BigDecimal("-0.999999999999999"));
return values;
}
/**
* INTERNAL:
* Allow for the platform to ignore exceptions.
* This is required for DB2 which throws no-data modified as an exception.
*/
public boolean shouldIgnoreException(SQLException exception) {
if (exception.getMessage().equals("No data found") || exception.getMessage().equals("No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table") || (exception.getErrorCode() == 100)) {
return true;
}
return super.shouldIgnoreException(exception);
}
/**
* INTERNAL:
* JDBC defines and outer join syntax, many drivers do not support this. So we normally avoid it.
*/
public boolean shouldUseJDBCOuterJoinSyntax() {
return false;
}
/**
* The Concat operator is of the form
* .... VARCHAR ( <operand1> || <operand2> )
*/
private ExpressionOperator concatOperator() {
ExpressionOperator exOperator = new ExpressionOperator();
exOperator.setType(ExpressionOperator.FunctionOperator);
exOperator.setSelector(ExpressionOperator.Concat);
Vector v = new Vector(5);
v.addElement("VARCHAR(");
v.addElement(" || ");
v.addElement(")");
exOperator.printsAs(v);
exOperator.bePrefix();
exOperator.setNodeClass(ClassConstants.FunctionExpression_Class);
return exOperator;
}
/**
* INTERNAL:
* Create the sysdate operator for this platform
*/
protected ExpressionOperator todayOperator() {
return ExpressionOperator.simpleFunctionNoParentheses(ExpressionOperator.Today, "CURRENT TIMESTAMP");
}
/**
* INTERNAL:
* Create the current date operator for this platform
*/
protected ExpressionOperator currentDateOperator() {
return ExpressionOperator.simpleFunctionNoParentheses(ExpressionOperator.currentDate, "CURRENT DATE");
}
/**
* INTERNAL:
* Build the identity query for native sequencing.
*/
public ValueReadQuery buildSelectQueryForNativeSequence() {
ValueReadQuery selectQuery = new ValueReadQuery();
StringWriter writer = new StringWriter();
writer.write("VALUES IDENTITY_VAL_LOCAL()");
selectQuery.setSQLString(writer.toString());
return selectQuery;
}
/**
* INTERNAL:
* Append the receiver's field 'identity' constraint clause to a writer
*/
public void printFieldIdentityClause(Writer writer) throws ValidationException {
try {
writer.write(" GENERATED ALWAYS AS IDENTITY");
} catch (IOException ioException) {
throw ValidationException.fileError(ioException);
}
}
/**
* INTERNAL:
* If native sequencing is being used on DB2 then the values must be
* retrieved after the insert.
* This method is to be used *ONLY* by sequencing classes
*/
public boolean shouldNativeSequenceAcquireValueAfterInsert() {
return true;
}
/**
* Return true if the receiver uses host sequence numbers, generated on the database.
* DB2 does through AS IDENTITY field types.
*/
public boolean supportsNativeSequenceNumbers() {
return true;
}
/**
* INTERNAL:
*/
public boolean supportsGlobalTempTables() {
return true;
}
/**
* INTERNAL:
*/
protected String getCreateTempTableSqlPrefix() {
return "DECLARE GLOBAL TEMPORARY TABLE ";
}
/**
* INTERNAL:
*/
public DatabaseTable getTempTableForTable(DatabaseTable table) {
DatabaseTable tempTable = super.getTempTableForTable(table);
tempTable.setTableQualifier("session");
return tempTable;
}
/**
* INTERNAL:
*/
protected String getCreateTempTableSqlSuffix() {
return " ON COMMIT DELETE ROWS NOT LOGGED";
}
/**
* INTERNAL:
*/
protected String getCreateTempTableSqlBodyForTable(DatabaseTable table) {
return " LIKE " + table.getQualifiedName();
}
/**
* INTERNAL:
*/
public boolean dontBindUpdateAllQueryUsingTempTables() {
return true;
}
public void writeParameterMarker(Writer writer, ParameterExpression parameter) throws IOException {
// DB2 requires cast around parameter markers if both operands of certian
// operators are parameter markers
// This method generates CAST for parameter markers whose type is correctly
// identified by the query compiler
String paramaterMarker = "?";
Object type = parameter.getType();
if(type != null) {
// TODO: Introduce a "switch/case" to generate cast for all the types
// TODO: Switch to use methods from TypeHelper for type comparision
if (type.equals(String.class)) {
// Binding a string parameter.
String castType = "VARCHAR(32672)";
paramaterMarker = "CAST (? AS " + castType + " )";
}
}
writer.write(paramaterMarker);
}
}
/*
* The contents of this file are subject to the terms
* of the Common Development and Distribution License
* (the "License"). You may not use this file except
* in compliance with the License.
*
* You can obtain a copy of the license at
* glassfish/bootstrap/legal/CDDLv1.0.txt or
*
https://glassfish.dev.java.net/public/CDDLv1.0.html.
* See the License for the specific language governing
* permissions and limitations under the License.
*
* When distributing Covered Code, include this CDDL
* HEADER in each file and include the License file at
* glassfish/bootstrap/legal/CDDLv1.0.txt. If applicable,
* add the following below this CDDL HEADER, with the
* fields enclosed by brackets "[]" replaced with your
* own identifying information: Portions Copyright [yyyy]
* [name of copyright owner]
*/
// Copyright (c) 1998, 2006, Oracle. All rights reserved.
package oracle.toplink.essentials.testing.models.cmp3.advanced;
import oracle.toplink.essentials.tools.schemaframework.*;
public class AdvancedTableCreator extends oracle.toplink.essentials.tools.schemaframework.TableCreator {
public AdvancedTableCreator() {
setName("EJB3EmployeeProject");
addTableDefinition(buildADDRESSTable());
addTableDefinition(buildEMPLOYEETable());
// addTableDefinition(buildEMPLOYEE_SEQTable());
addTableDefinition(buildLARGEPROJECTTable());
addTableDefinition(buildPHONENUMBERTable());
addTableDefinition(buildPROJECTTable());
addTableDefinition(buildPROJECT_EMPTable());
addTableDefinition(buildSALARYTable());
addTableDefinition(buildPLATINUMBUYERTable());
addTableDefinition(buildBUYERTable());
}
public static TableDefinition buildADDRESSTable() {
TableDefinition table = new TableDefinition();
table.setName("CMP3_ADDRESS");
FieldDefinition fieldID = new FieldDefinition();
fieldID.setName("ADDRESS_ID");
fieldID.setTypeName("NUMERIC");
fieldID.setSize(15);
fieldID.setSubSize(0);
fieldID.setIsPrimaryKey(true);
fieldID.setIsIdentity(true);
fieldID.setUnique(false);
fieldID.setShouldAllowNull(false);
table.addField(fieldID);
FieldDefinition fieldSTREET = new FieldDefinition();
fieldSTREET.setName("STREET");
fieldSTREET.setTypeName("VARCHAR2");
fieldSTREET.setSize(60);
fieldSTREET.setSubSize(0);
fieldSTREET.setIsPrimaryKey(false);
fieldSTREET.setIsIdentity(false);
fieldSTREET.setUnique(false);
fieldSTREET.setShouldAllowNull(true);
table.addField(fieldSTREET);
FieldDefinition fieldCITY = new FieldDefinition();
fieldCITY.setName("CITY");
fieldCITY.setTypeName("VARCHAR2");
fieldCITY.setSize(60);
fieldCITY.setSubSize(0);
fieldCITY.setIsPrimaryKey(false);
fieldCITY.setIsIdentity(false);
fieldCITY.setUnique(false);
fieldCITY.setShouldAllowNull(true);
table.addField(fieldCITY);
FieldDefinition fieldPROVINCE = new FieldDefinition();
fieldPROVINCE.setName("PROVINCE");
fieldPROVINCE.setTypeName("VARCHAR2");
fieldPROVINCE.setSize(60);
fieldPROVINCE.setSubSize(0);
fieldPROVINCE.setIsPrimaryKey(false);
fieldPROVINCE.setIsIdentity(false);
fieldPROVINCE.setUnique(false);
fieldPROVINCE.setShouldAllowNull(true);
table.addField(fieldPROVINCE);
FieldDefinition fieldPOSTALCODE = new FieldDefinition();
fieldPOSTALCODE.setName("P_CODE");
fieldPOSTALCODE.setTypeName("VARCHAR2");
fieldPOSTALCODE.setSize(67);
fieldPOSTALCODE.setSubSize(0);
fieldPOSTALCODE.setIsPrimaryKey(false);
fieldPOSTALCODE.setIsIdentity(false);
fieldPOSTALCODE.setUnique(false);
fieldPOSTALCODE.setShouldAllowNull(true);
table.addField(fieldPOSTALCODE);
FieldDefinition fieldCOUNTRY = new FieldDefinition();
fieldCOUNTRY.setName("COUNTRY");
fieldCOUNTRY.setTypeName("VARCHAR2");
fieldCOUNTRY.setSize(60);
fieldCOUNTRY.setSubSize(0);
fieldCOUNTRY.setIsPrimaryKey(false);
fieldCOUNTRY.setIsIdentity(false);
fieldCOUNTRY.setUnique(false);
fieldCOUNTRY.setShouldAllowNull(true);
table.addField(fieldCOUNTRY);
return table;
}
public static TableDefinition buildEMPLOYEETable() {
TableDefinition table = new TableDefinition();
// SECTION: TABLE
table.setName("CMP3_EMPLOYEE");
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field.setName("EMP_ID");
field.setTypeName("NUMERIC");
field.setSize(15);
field.setShouldAllowNull(false );
field.setIsPrimaryKey(true );
field.setUnique(false );
field.setIsIdentity(true );
table.addField(field);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field1 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field1.setName("F_NAME");
field1.setTypeName("VARCHAR");
field1.setSize(40);
field1.setShouldAllowNull(true );
field1.setIsPrimaryKey(false );
field1.setUnique(false );
field1.setIsIdentity(false );
table.addField(field1);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field2 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field2.setName("L_NAME");
field2.setTypeName("VARCHAR");
field2.setSize(40);
field2.setShouldAllowNull(true );
field2.setIsPrimaryKey(false );
field2.setUnique(false );
field2.setIsIdentity(false );
table.addField(field2);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field3 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field3.setName("START_DATE");
field3.setTypeName("DATE");
field3.setSize(23);
field3.setShouldAllowNull(true );
field3.setIsPrimaryKey(false );
field3.setUnique(false );
field3.setIsIdentity(false );
table.addField(field3);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field4 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field4.setName("END_DATE");
field4.setTypeName("DATE");
field4.setSize(23);
field4.setShouldAllowNull(true );
field4.setIsPrimaryKey(false );
field4.setUnique(false );
field4.setIsIdentity(false );
table.addField(field4);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field8 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field8.setName("ADDR_ID");
field8.setTypeName("NUMERIC");
field8.setSize(15);
field8.setShouldAllowNull(true );
field8.setIsPrimaryKey(false );
field8.setUnique(false );
field8.setIsIdentity(false );
field8.setForeignKeyFieldName("CMP3_ADDRESS.ADDRESS_ID");
table.addField(field8);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field9 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field9.setName("MANAGER_EMP_ID");
field9.setTypeName("NUMERIC");
field9.setSize(15);
field9.setShouldAllowNull(true );
field9.setIsPrimaryKey(false );
field9.setUnique(false );
field9.setIsIdentity(false );
field9.setForeignKeyFieldName("CMP3_EMPLOYEE.EMP_ID");
table.addField(field9);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field10 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field10.setName("VERSION");
field10.setTypeName("NUMERIC");
field10.setSize(15);
field10.setShouldAllowNull(true );
field10.setIsPrimaryKey(false );
field10.setUnique(false );
field10.setIsIdentity(false );
table.addField(field10);
/* ForeignKeyConstraint foreignKeyEMPLOYEE_ADDRESS = new ForeignKeyConstraint();
foreignKeyEMPLOYEE_ADDRESS.setName("EMPLOYEE_ADDRESS");
foreignKeyEMPLOYEE_ADDRESS.setTargetTable("ADDRESS");
foreignKeyEMPLOYEE_ADDRESS.addSourceField("ADDR_ID");
foreignKeyEMPLOYEE_ADDRESS.addTargetField("ID");
table.addForeignKeyConstraint(foreignKeyEMPLOYEE_ADDRESS);
ForeignKeyConstraint foreignKeyEMPLOYEE_MANAGER = new ForeignKeyConstraint();
foreignKeyEMPLOYEE_MANAGER.setName("EMPLOYEE_MANAGER");
foreignKeyEMPLOYEE_MANAGER.setTargetTable("CMP3_EMPLOYEE");
foreignKeyEMPLOYEE_MANAGER.addSourceField("MANAGER_ID");
foreignKeyEMPLOYEE_MANAGER.addTargetField("EMP_ID");
table.addForeignKeyConstraint(foreignKeyEMPLOYEE_MANAGER);
*/ return table;
}
public static TableDefinition buildEMPLOYEE_SEQTable() {
TableDefinition table = new TableDefinition();
// table.setName("SEQUENCE");
table.setName("CMP3_EMPLOYEE_SEQ");
FieldDefinition fieldSEQ_COUNT = new FieldDefinition();
fieldSEQ_COUNT.setName("SEQ_COUNT");
fieldSEQ_COUNT.setTypeName("NUMBER");
fieldSEQ_COUNT.setSize(15);
fieldSEQ_COUNT.setSubSize(0);
fieldSEQ_COUNT.setIsPrimaryKey(false);
fieldSEQ_COUNT.setIsIdentity(false);
fieldSEQ_COUNT.setUnique(false);
fieldSEQ_COUNT.setShouldAllowNull(false);
table.addField(fieldSEQ_COUNT);
FieldDefinition fieldSEQ_NAME = new FieldDefinition();
fieldSEQ_NAME.setName("SEQ_NAME");
fieldSEQ_NAME.setTypeName("VARCHAR2");
fieldSEQ_NAME.setSize(80);
fieldSEQ_NAME.setSubSize(0);
fieldSEQ_NAME.setIsPrimaryKey(true);
fieldSEQ_NAME.setIsIdentity(false);
fieldSEQ_NAME.setUnique(false);
fieldSEQ_NAME.setShouldAllowNull(false);
table.addField(fieldSEQ_NAME);
return table;
}
public static TableDefinition buildLARGEPROJECTTable() {
TableDefinition table = new TableDefinition();
table.setName("CMP3_LPROJECT");
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field.setName("PROJ_ID");
field.setTypeName("NUMERIC");
field.setSize(15);
field.setShouldAllowNull(false );
field.setIsPrimaryKey(true );
field.setUnique(false );
field.setIsIdentity(false );
field.setForeignKeyFieldName("CMP3_PROJECT.PROJ_ID");
table.addField(field);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field1 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field1.setName("BUDGET");
field1.setTypeName("DOUBLE PRECIS");
field1.setSize(32);
field1.setShouldAllowNull(true );
field1.setIsPrimaryKey(false );
field1.setUnique(false );
field1.setIsIdentity(false );
table.addField(field1);
return table;
}
public static TableDefinition buildPHONENUMBERTable() {
TableDefinition table = new TableDefinition();
table.setName("CMP3_PHONENUMBER");
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field.setName("OWNER_ID");
field.setTypeName("NUMERIC");
field.setSize(15);
field.setShouldAllowNull(false );
field.setIsPrimaryKey(true );
field.setUnique(false );
field.setIsIdentity(false );
field.setForeignKeyFieldName("CMP3_EMPLOYEE.EMP_ID");
table.addField(field);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field1 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field1.setName("TYPE");
field1.setTypeName("VARCHAR");
field1.setSize(15);
field1.setShouldAllowNull(false );
field1.setIsPrimaryKey(true );
field1.setUnique(false );
field1.setIsIdentity(false );
table.addField(field1);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field2 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field2.setName("AREA_CODE");
field2.setTypeName("VARCHAR");
field2.setSize(3);
field2.setShouldAllowNull(true );
field2.setIsPrimaryKey(false );
field2.setUnique(false );
field2.setIsIdentity(false );
table.addField(field2);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field3 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field3.setName("NUMB");
field3.setTypeName("VARCHAR");
field3.setSize(8);
field3.setShouldAllowNull(true );
field3.setIsPrimaryKey(false );
field3.setUnique(false );
field3.setIsIdentity(false );
table.addField(field3);
/* ForeignKeyConstraint foreignKeyPHONE_OWNER = new ForeignKeyConstraint();
foreignKeyPHONE_OWNER.setName("PHONE_OWNER");
foreignKeyPHONE_OWNER.setTargetTable("CMP3_EMPLOYEE");
foreignKeyPHONE_OWNER.addSourceField("EMP_ID");
foreignKeyPHONE_OWNER.addTargetField("EMP_ID");
table.addForeignKeyConstraint(foreignKeyPHONE_OWNER);
*/
return table;
}
public static TableDefinition buildPROJECTTable() {
TableDefinition table = new TableDefinition();
table.setName("CMP3_PROJECT");
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field.setName("PROJ_ID");
field.setTypeName("NUMERIC");
field.setSize(15);
field.setShouldAllowNull(false );
field.setIsPrimaryKey(true );
field.setUnique(false );
field.setIsIdentity(true );
table.addField(field);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field1 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field1.setName("PROJ_TYPE");
field1.setTypeName("VARCHAR");
field1.setSize(1);
field1.setShouldAllowNull(true );
field1.setIsPrimaryKey(false );
field1.setUnique(false );
field1.setIsIdentity(false );
table.addField(field1);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field2 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field2.setName("PROJ_NAME");
field2.setTypeName("VARCHAR");
field2.setSize(30);
field2.setShouldAllowNull(true );
field2.setIsPrimaryKey(false );
field2.setUnique(false );
field2.setIsIdentity(false );
table.addField(field2);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field3 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field3.setName("DESCRIP");
field3.setTypeName("VARCHAR");
field3.setSize(200);
field3.setShouldAllowNull(true );
field3.setIsPrimaryKey(false );
field3.setUnique(false );
field3.setIsIdentity(false );
table.addField(field3);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field4 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field4.setName("LEADER_ID");
field4.setTypeName("NUMERIC");
field4.setSize(15);
field4.setShouldAllowNull(true );
field4.setIsPrimaryKey(false );
field4.setUnique(false );
field4.setIsIdentity(false );
field4.setForeignKeyFieldName("CMP3_EMPLOYEE.EMP_ID");
table.addField(field4);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field5 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field5.setName("VERSION");
field5.setTypeName("NUMERIC");
field5.setSize(15);
field5.setShouldAllowNull(true );
field5.setIsPrimaryKey(false );
field5.setUnique(false );
field5.setIsIdentity(false );
table.addField(field5);
/* ForeignKeyConstraint foreignKeyPROJECT_LEADER = new ForeignKeyConstraint();
foreignKeyPROJECT_LEADER.setName("PROJECT_LEADER");
foreignKeyPROJECT_LEADER.setTargetTable("CMP3_EMPLOYEE");
foreignKeyPROJECT_LEADER.addSourceField("LEADER_ID");
foreignKeyPROJECT_LEADER.addTargetField("EMP_ID");
table.addForeignKeyConstraint(foreignKeyPROJECT_LEADER);
*/
return table;
}
public static TableDefinition buildPROJECT_EMPTable() {
TableDefinition table = new TableDefinition();
table.setName("CMP3_EMP_PROJ");
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field.setName("EMPLOYEES_EMP_ID");
field.setTypeName("NUMERIC");
field.setSize(15);
field.setShouldAllowNull(false );
field.setIsPrimaryKey(true );
field.setUnique(false );
field.setIsIdentity(false );
field.setForeignKeyFieldName("CMP3_EMPLOYEE.EMP_ID");
table.addField(field);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field1 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field1.setName("PROJECTS_PROJ_ID");
field1.setTypeName("NUMERIC");
field1.setSize(15);
field1.setShouldAllowNull(false );
field1.setIsPrimaryKey(true );
field1.setUnique(false );
field1.setIsIdentity(false );
field1.setForeignKeyFieldName("CMP3_PROJECT.PROJ_ID");
table.addField(field1);
/* ForeignKeyConstraint foreignKeyPROJECT_EMPLOYEE = new ForeignKeyConstraint();
foreignKeyPROJECT_EMPLOYEE.setName("PROJECT_EMPLOYEE");
foreignKeyPROJECT_EMPLOYEE.setTargetTable("CMP3_EMPLOYEE");
foreignKeyPROJECT_EMPLOYEE.addSourceField("EMP_ID");
foreignKeyPROJECT_EMPLOYEE.addTargetField("EMP_ID");
table.addForeignKeyConstraint(foreignKeyPROJECT_EMPLOYEE);
ForeignKeyConstraint foreignKeyEMPLOYEE_PROJECT = new ForeignKeyConstraint();
foreignKeyEMPLOYEE_PROJECT.setName("EMPLOYEE_PROJECT");
foreignKeyEMPLOYEE_PROJECT.setTargetTable("CMP3_PROJECT");
foreignKeyEMPLOYEE_PROJECT.addSourceField("EMP_ID");
foreignKeyEMPLOYEE_PROJECT.addTargetField("EMP_ID");
table.addForeignKeyConstraint(foreignKeyEMPLOYEE_PROJECT);
*/
return table;
}
public static TableDefinition buildSALARYTable() {
TableDefinition table = new TableDefinition();
table.setName("CMP3_SALARY");
FieldDefinition fieldEMP_ID = new FieldDefinition();
fieldEMP_ID.setName("EMP_ID");
fieldEMP_ID.setTypeName("NUMERIC");
fieldEMP_ID.setSize(15);
fieldEMP_ID.setSubSize(0);
fieldEMP_ID.setIsPrimaryKey(true);
fieldEMP_ID.setIsIdentity(false);
fieldEMP_ID.setUnique(false);
fieldEMP_ID.setShouldAllowNull(false);
fieldEMP_ID.setForeignKeyFieldName("CMP3_EMPLOYEE.EMP_ID");
table.addField(fieldEMP_ID);
FieldDefinition fieldSALARY = new FieldDefinition();
fieldSALARY.setName("SALARY");
fieldSALARY.setTypeName("NUMBER");
fieldSALARY.setSize(15);
fieldSALARY.setSubSize(0);
fieldSALARY.setIsPrimaryKey(false);
fieldSALARY.setIsIdentity(false);
fieldSALARY.setUnique(false);
fieldSALARY.setShouldAllowNull(true);
table.addField(fieldSALARY);
return table;
}
public static TableDefinition buildPLATINUMBUYERTable() {
TableDefinition table = new TableDefinition();
table.setName("CMP3_PBUYER");
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field.setName("BUYER_ID");
field.setTypeName("NUMERIC");
field.setSize(15);
field.setShouldAllowNull(false );
field.setIsPrimaryKey(true );
field.setUnique(false );
field.setIsIdentity(false );
field.setForeignKeyFieldName("CMP3_BUYER.BUYER_ID");
table.addField(field);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field1 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field1.setName("PURCHASES");
field1.setTypeName("DOUBLE PRECIS");
field1.setSize(32);
field1.setShouldAllowNull(true );
field1.setIsPrimaryKey(false );
field1.setUnique(false );
field1.setIsIdentity(false );
table.addField(field1);
return table;
}
public static TableDefinition buildBUYERTable() {
TableDefinition table = new TableDefinition();
table.setName("CMP3_BUYER");
java.util.Vector uniqueKeys = new java.util.Vector();
String[] unq1 = {"BUYER_ID", "BUYER_NAME"};
String[] unq2 = {"BUYER_ID", "DESCRIP"};
uniqueKeys.addElement(unq1);
uniqueKeys.addElement(unq2);
table.setUniqueKeys(uniqueKeys);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field.setName("BUYER_ID");
field.setTypeName("NUMERIC");
field.setSize(15);
field.setShouldAllowNull(false );
field.setIsPrimaryKey(true );
field.setUnique(false );
field.setIsIdentity(true );
table.addField(field);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field2 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field2.setName("BUYER_NAME");
field2.setTypeName("VARCHAR");
field2.setSize(30);
field2.setShouldAllowNull(false);
field2.setIsPrimaryKey(false );
field2.setUnique(false );
field2.setIsIdentity(false );
table.addField(field2);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field3 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field3.setName("DESCRIP");
field3.setTypeName("VARCHAR");
field3.setSize(200);
field3.setShouldAllowNull(false);
field3.setIsPrimaryKey(false );
field3.setUnique(false );
field3.setIsIdentity(false );
table.addField(field3);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field4 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field4.setName("DTYPE");
field4.setTypeName("VARCHAR");
field4.setSize(200);
field4.setShouldAllowNull(true );
field4.setIsPrimaryKey(false );
field4.setUnique(false );
field4.setIsIdentity(false );
table.addField(field4);
// SECTION: FIELD
oracle.toplink.essentials.tools.schemaframework.FieldDefinition field5 = new oracle.toplink.essentials.tools.schemaframework.FieldDefinition();
field5.setName("VERSION");
field5.setTypeName("NUMERIC");
field5.setSize(15);
field5.setShouldAllowNull(true );
field5.setIsPrimaryKey(false );
field5.setUnique(false );
field5.setIsIdentity(false );
table.addField(field5);
return table;
}
}