openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/mapping/TestCompositeIdTraversalInSQLMapping.java - openjpa - Git at Google

 /*
  * Licensed to the Apache Software Foundation (ASF) under one
  * or more contributor license agreements.  See the NOTICE file
  * distributed with this work for additional information
  * regarding copyright ownership.  The ASF licenses this file
  * to you under the Apache License, Version 2.0 (the
  * "License"); you may not use this file except in compliance
  * with the License.  You may obtain a copy of the License at
  *
  * http://www.apache.org/licenses/LICENSE-2.0
  *
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an
  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  * KIND, either express or implied.  See the License for the
  * specific language governing permissions and limitations
  * under the License.
  */
 package org.apache.openjpa.persistence.jdbc.mapping;

 import java.util.List;
 import javax.persistence.EntityManager;
 import javax.persistence.Query;

 import org.apache.openjpa.persistence.jdbc.common.apps.mappingApp.*;
 import org.apache.openjpa.persistence.common.utils.*;
 import junit.framework.*;


 public class TestCompositeIdTraversalInSQLMapping extends AbstractTestCase
 {


 	public TestCompositeIdTraversalInSQLMapping(String name)
 	{
 		super(name, "jdbccactusapp");
 	}


 	public void setUp()
 	{
 		deleteAll(OwnerOfEntityWithCompositeId.class);
 		deleteAll(EntityWithCompositeId.class);
 	}
 	private void persist(Integer uniqueId, String uniqueName, String relName)
 	{
 		OwnerOfEntityWithCompositeId owner = new OwnerOfEntityWithCompositeId();
 		EntityWithCompositeId relative = new EntityWithCompositeId();
 		owner.setName(uniqueName);
 		relative.setId(uniqueId);
 		relative.setName(relName);
 		relative.setValue("foo");
 		owner.setRelation(relative);

 		EntityManager em = currentEntityManager();
 		startTx(em);
 		em.persist(owner);
 		endTx(em);
 		endEm(em);
 	}
 	public void testTraversalWhenSQLSelectsBothEndOfTheRelation ()
 	{
 		EntityManager em = currentEntityManager();
 		Integer uid  = new Integer((int) (System.currentTimeMillis()%100000));
 		String uName = "P"+uid;
 		String rName = "R"+uName;

 		persist(uid, uName, rName);

 		String sql = "SELECT a.NAME as OWNER_NAME, a.RELATION_ID as REL_ID," +
 		             "a.RELATION_NAME as REL_NAME, b.ID as C_ID," +
 					 "b.NAME as C_NAME, b.VALUE as C_VALUE " +
 		             "FROM OWNER_OF_COMPOSITE_ID a, COMPOSITE_ID b " +
 		             "WHERE a.RELATION_ID=b.ID AND a.RELATION_NAME=b.NAME " +
 		             "AND a.NAME='" + uName + "'";

 		Query query = em.createNativeQuery(sql,
 			"SQLSelectsBothEndOfTheRelation");

 		List result = query.getResultList();

 		assertEquals(1,result.size());
 		Object object = result.get(0);
 		assertEquals(Object[].class, object.getClass());
 		Object[] array = (Object[])object;
 		assertEquals(2,array.length);
 		assertEquals(OwnerOfEntityWithCompositeId.class,array[0].getClass());
 		assertEquals(EntityWithCompositeId.class,array[1].getClass());

 		OwnerOfEntityWithCompositeId owner = (OwnerOfEntityWithCompositeId)
 			array[0];

 		EntityWithCompositeId relative = (EntityWithCompositeId)array[1];

 		assertEquals(uName, owner.getName());
 		assertEquals(owner.getRelation(), relative);
 		assertEquals(relative.getId(),uid);
 		assertEquals(relative.getName(), rName);
 		assertEquals("foo", relative.getValue());
 		endEm(em);
 	}

 	public void testTraversalWhenSQLSelectsOnlyOneEndOfTheRelation ()
 	{
 		EntityManager em = currentEntityManager();
 		Integer uid  = new Integer((int) (System.currentTimeMillis()%100000));
 		String uName = "P"+uid;
 		String rName = "R"+uName;

 		persist (uid, uName, rName);
 		String sql = "SELECT a.NAME as OWNER_NAME, a.RELATION_ID as REL_ID," +
 		             "a.RELATION_NAME as REL_NAME "+
 		             "FROM OWNER_OF_COMPOSITE_ID a " +
 		             "WHERE  a.NAME='" + uName + "'";

 		Query query = em.createNativeQuery(sql,
 			"SQLSelectsOnlyOneEndOfTheRelation");

 		List result = query.getResultList();

 		assertEquals(1,result.size());
 		Object object = result.get(0);
 		assertEquals(OwnerOfEntityWithCompositeId.class,object.getClass());

 		OwnerOfEntityWithCompositeId owner = (OwnerOfEntityWithCompositeId)
 			object;

 		EntityWithCompositeId relative = owner.getRelation();

 		assertEquals(uName, owner.getName());
 		assertEquals(relative.getId(),uid);
 		assertEquals(relative.getName(), rName);
 		assertEquals("foo", relative.getValue());
 		endEm(em);
 	}
 	public void testTraversalWhenSQLSelectsUnrelatedInstances ()
 	{
 		EntityManager em = currentEntityManager();
 		Integer uid1  = new Integer((int) (System.currentTimeMillis()%100000));
 		Integer uid2  = new Integer(uid1.intValue()+1);
 		String uName1 = "P"+uid1;
 		String rName1 = "R"+uName1;
 		String uName2 = "P"+uid2;
 		String rName2 = "R"+uName2;

 		persist(uid1, uName1, rName1);
 		persist(uid2, uName2, rName2);

 		String sql = "SELECT a.NAME as OWNER_NAME, a.RELATION_ID AS REL_ID," +
         "a.RELATION_NAME AS REL_NAME, " +
         "b.ID AS C_ID, b.NAME AS C_NAME, b.VALUE AS C_VALUE "+
         "FROM OWNER_OF_COMPOSITE_ID a, COMPOSITE_ID b " +
         "WHERE b.NAME='"+ rName2 +"' " +
         "AND a.NAME='" + uName1 + "'";

 		Query query = em.createNativeQuery(sql,
 				"SQLSelectsUnrelatedInstances");

 		List result = query.getResultList();

 		assertEquals(1,result.size());
 		Object object = result.get(0);
 		assertEquals(Object[].class, object.getClass());
 		Object[] array = (Object[])object;
 		assertEquals(2,array.length);
 		assertEquals(OwnerOfEntityWithCompositeId.class,array[0].getClass());
 		assertEquals(EntityWithCompositeId.class,array[1].getClass());

 		OwnerOfEntityWithCompositeId owner1 = (OwnerOfEntityWithCompositeId)
 			array[0];
 		EntityWithCompositeId relative1 = owner1.getRelation();

 		EntityWithCompositeId relative2 = (EntityWithCompositeId)array[1];

 		assertEquals(uName1, owner1.getName());
 		assertEquals(uid1, relative1.getId());
 		assertEquals(rName1, relative1.getName());
 		assertEquals(uid2, relative2.getId());
 		assertEquals(rName2, relative2.getName());
 		endEm(em);
 	}

 	public void testRecursiveTraversal ()
 	{
 		Integer rootId  = new Integer((int)(System.currentTimeMillis()%100000));

 		int depth = 3;
 		persistChainedRelative(rootId, depth);

 		String sql = createSelfJoinSQL("RECURSIVE_ENTITY",
 				depth,rootId);

 		EntityManager em = currentEntityManager();
 		Query query = em.createNativeQuery(sql, "SQLSelectsChainedRelation");

 		List result = query.getResultList();

 		assertEquals(1, result.size());
 		assertEquals(RecursiveEntityWithCompositeId.class,
 				result.get(0).getClass());

 		RecursiveEntityWithCompositeId root = (RecursiveEntityWithCompositeId)
 												result.get(0);
 		assertEquals(rootId,root.getId());
 		assertEquals("P"+rootId,root.getName());
 		for (int i=1; i<depth;i++){
 			RecursiveEntityWithCompositeId relative = root.getRelation();
 			Integer expecetedId = root.getId()+1;
 			assertEquals (expecetedId,relative.getId());
 			assertEquals ("P"+expecetedId, relative.getName());
 			root = relative;
 		}

 	}

 	void persistChainedRelative(Integer rootId, int depth)
 	{
 		RecursiveEntityWithCompositeId root =
 			new RecursiveEntityWithCompositeId();
 		root.setId(rootId);
 		root.setName("P"+rootId);
 		RecursiveEntityWithCompositeId head = root;
 		for (int i=1; i<=depth; i++)
 		{
 			RecursiveEntityWithCompositeId relation =
 				new RecursiveEntityWithCompositeId();
 			relation.setId(rootId+i);
 			relation.setName("P"+(rootId+i));
 			head.setRelation(relation);
 			head = relation;
 		}
 		EntityManager em = currentEntityManager();
 		startTx(em);
 		em.persist(root);
 		endTx(em);
 		endEm(em);
 	}

 	String createSelfJoinSQL(String table, int depth, int id)
 	{

 		StringBuffer sql = new StringBuffer("SELECT ");
 		for (int i=0; i<depth; i++)
 			sql.append("t"+i+".ID AS T"+i+"_ID, ")
 			   .append("t"+i+".NAME AS T"+i+"_NAME, ")
 			   .append("t"+i+".RELATION_ID AS T"+i+"_REL_ID, ")
 			   .append("t"+i+".RELATION_NAME AS T"+i+"_REL_NAME")
 			   .append((i==(depth-1))?" " : ", ");

 		sql.append(" FROM ");
 		for (int i=0; i<depth; i++)
 			sql.append(table + " t"+i)
 			   .append((i==(depth-1))?" " : ", ");

 		sql.append(" WHERE ");
 		for (int i=0; i<(depth-1); i++)
 			sql.append("t"+i+".RELATION_ID=t"+(i+1)+".ID AND ")
 			   .append("t"+i+".RELATION_NAME=t"+(i+1)+".NAME AND " );

 		sql.append("t0.ID="+id);

 		return sql.toString();
 	}
 }
	/*
	* Licensed to the Apache Software Foundation (ASF) under one
	* or more contributor license agreements. See the NOTICE file
	* distributed with this work for additional information
	* regarding copyright ownership. The ASF licenses this file
	* to you under the Apache License, Version 2.0 (the
	* "License"); you may not use this file except in compliance
	* with the License. You may obtain a copy of the License at
	*
	* http://www.apache.org/licenses/LICENSE-2.0
	*
	* Unless required by applicable law or agreed to in writing,
	* software distributed under the License is distributed on an
	* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	* KIND, either express or implied. See the License for the
	* specific language governing permissions and limitations
	* under the License.
	*/
	package org.apache.openjpa.persistence.jdbc.mapping;

	import java.util.List;
	import javax.persistence.EntityManager;
	import javax.persistence.Query;

	import org.apache.openjpa.persistence.jdbc.common.apps.mappingApp.*;
	import org.apache.openjpa.persistence.common.utils.*;
	import junit.framework.*;


	public class TestCompositeIdTraversalInSQLMapping extends AbstractTestCase
	{


	public TestCompositeIdTraversalInSQLMapping(String name)
	{
	super(name, "jdbccactusapp");
	}


	public void setUp()
	{
	deleteAll(OwnerOfEntityWithCompositeId.class);
	deleteAll(EntityWithCompositeId.class);
	}
	private void persist(Integer uniqueId, String uniqueName, String relName)
	{
	OwnerOfEntityWithCompositeId owner = new OwnerOfEntityWithCompositeId();
	EntityWithCompositeId relative = new EntityWithCompositeId();
	owner.setName(uniqueName);
	relative.setId(uniqueId);
	relative.setName(relName);
	relative.setValue("foo");
	owner.setRelation(relative);

	EntityManager em = currentEntityManager();
	startTx(em);
	em.persist(owner);
	endTx(em);
	endEm(em);
	}
	public void testTraversalWhenSQLSelectsBothEndOfTheRelation ()
	{
	EntityManager em = currentEntityManager();
	Integer uid = new Integer((int) (System.currentTimeMillis()%100000));
	String uName = "P"+uid;
	String rName = "R"+uName;

	persist(uid, uName, rName);

	String sql = "SELECT a.NAME as OWNER_NAME, a.RELATION_ID as REL_ID," +
	"a.RELATION_NAME as REL_NAME, b.ID as C_ID," +
	"b.NAME as C_NAME, b.VALUE as C_VALUE " +
	"FROM OWNER_OF_COMPOSITE_ID a, COMPOSITE_ID b " +
	"WHERE a.RELATION_ID=b.ID AND a.RELATION_NAME=b.NAME " +
	"AND a.NAME='" + uName + "'";

	Query query = em.createNativeQuery(sql,
	"SQLSelectsBothEndOfTheRelation");

	List result = query.getResultList();

	assertEquals(1,result.size());
	Object object = result.get(0);
	assertEquals(Object[].class, object.getClass());
	Object[] array = (Object[])object;
	assertEquals(2,array.length);
	assertEquals(OwnerOfEntityWithCompositeId.class,array[0].getClass());
	assertEquals(EntityWithCompositeId.class,array[1].getClass());

	OwnerOfEntityWithCompositeId owner = (OwnerOfEntityWithCompositeId)
	array[0];

	EntityWithCompositeId relative = (EntityWithCompositeId)array[1];

	assertEquals(uName, owner.getName());
	assertEquals(owner.getRelation(), relative);
	assertEquals(relative.getId(),uid);
	assertEquals(relative.getName(), rName);
	assertEquals("foo", relative.getValue());
	endEm(em);
	}

	public void testTraversalWhenSQLSelectsOnlyOneEndOfTheRelation ()
	{
	EntityManager em = currentEntityManager();
	Integer uid = new Integer((int) (System.currentTimeMillis()%100000));
	String uName = "P"+uid;
	String rName = "R"+uName;

	persist (uid, uName, rName);
	String sql = "SELECT a.NAME as OWNER_NAME, a.RELATION_ID as REL_ID," +
	"a.RELATION_NAME as REL_NAME "+
	"FROM OWNER_OF_COMPOSITE_ID a " +
	"WHERE a.NAME='" + uName + "'";

	Query query = em.createNativeQuery(sql,
	"SQLSelectsOnlyOneEndOfTheRelation");

	List result = query.getResultList();

	assertEquals(1,result.size());
	Object object = result.get(0);
	assertEquals(OwnerOfEntityWithCompositeId.class,object.getClass());

	OwnerOfEntityWithCompositeId owner = (OwnerOfEntityWithCompositeId)
	object;

	EntityWithCompositeId relative = owner.getRelation();

	assertEquals(uName, owner.getName());
	assertEquals(relative.getId(),uid);
	assertEquals(relative.getName(), rName);
	assertEquals("foo", relative.getValue());
	endEm(em);
	}
	public void testTraversalWhenSQLSelectsUnrelatedInstances ()
	{
	EntityManager em = currentEntityManager();
	Integer uid1 = new Integer((int) (System.currentTimeMillis()%100000));
	Integer uid2 = new Integer(uid1.intValue()+1);
	String uName1 = "P"+uid1;
	String rName1 = "R"+uName1;
	String uName2 = "P"+uid2;
	String rName2 = "R"+uName2;

	persist(uid1, uName1, rName1);
	persist(uid2, uName2, rName2);

	String sql = "SELECT a.NAME as OWNER_NAME, a.RELATION_ID AS REL_ID," +
	"a.RELATION_NAME AS REL_NAME, " +
	"b.ID AS C_ID, b.NAME AS C_NAME, b.VALUE AS C_VALUE "+
	"FROM OWNER_OF_COMPOSITE_ID a, COMPOSITE_ID b " +
	"WHERE b.NAME='"+ rName2 +"' " +
	"AND a.NAME='" + uName1 + "'";

	Query query = em.createNativeQuery(sql,
	"SQLSelectsUnrelatedInstances");

	List result = query.getResultList();

	assertEquals(1,result.size());
	Object object = result.get(0);
	assertEquals(Object[].class, object.getClass());
	Object[] array = (Object[])object;
	assertEquals(2,array.length);
	assertEquals(OwnerOfEntityWithCompositeId.class,array[0].getClass());
	assertEquals(EntityWithCompositeId.class,array[1].getClass());

	OwnerOfEntityWithCompositeId owner1 = (OwnerOfEntityWithCompositeId)
	array[0];
	EntityWithCompositeId relative1 = owner1.getRelation();

	EntityWithCompositeId relative2 = (EntityWithCompositeId)array[1];

	assertEquals(uName1, owner1.getName());
	assertEquals(uid1, relative1.getId());
	assertEquals(rName1, relative1.getName());
	assertEquals(uid2, relative2.getId());
	assertEquals(rName2, relative2.getName());
	endEm(em);
	}

	public void testRecursiveTraversal ()
	{
	Integer rootId = new Integer((int)(System.currentTimeMillis()%100000));

	int depth = 3;
	persistChainedRelative(rootId, depth);

	String sql = createSelfJoinSQL("RECURSIVE_ENTITY",
	depth,rootId);

	EntityManager em = currentEntityManager();
	Query query = em.createNativeQuery(sql, "SQLSelectsChainedRelation");

	List result = query.getResultList();

	assertEquals(1, result.size());
	assertEquals(RecursiveEntityWithCompositeId.class,
	result.get(0).getClass());

	RecursiveEntityWithCompositeId root = (RecursiveEntityWithCompositeId)
	result.get(0);
	assertEquals(rootId,root.getId());
	assertEquals("P"+rootId,root.getName());
	for (int i=1; i<depth;i++){
	RecursiveEntityWithCompositeId relative = root.getRelation();
	Integer expecetedId = root.getId()+1;
	assertEquals (expecetedId,relative.getId());
	assertEquals ("P"+expecetedId, relative.getName());
	root = relative;
	}

	}

	void persistChainedRelative(Integer rootId, int depth)
	{
	RecursiveEntityWithCompositeId root =
	new RecursiveEntityWithCompositeId();
	root.setId(rootId);
	root.setName("P"+rootId);
	RecursiveEntityWithCompositeId head = root;
	for (int i=1; i<=depth; i++)
	{
	RecursiveEntityWithCompositeId relation =
	new RecursiveEntityWithCompositeId();
	relation.setId(rootId+i);
	relation.setName("P"+(rootId+i));
	head.setRelation(relation);
	head = relation;
	}
	EntityManager em = currentEntityManager();
	startTx(em);
	em.persist(root);
	endTx(em);
	endEm(em);
	}

	String createSelfJoinSQL(String table, int depth, int id)
	{

	StringBuffer sql = new StringBuffer("SELECT ");
	for (int i=0; i<depth; i++)
	sql.append("t"+i+".ID AS T"+i+"_ID, ")
	.append("t"+i+".NAME AS T"+i+"_NAME, ")
	.append("t"+i+".RELATION_ID AS T"+i+"_REL_ID, ")
	.append("t"+i+".RELATION_NAME AS T"+i+"_REL_NAME")
	.append((i==(depth-1))?" " : ", ");

	sql.append(" FROM ");
	for (int i=0; i<depth; i++)
	sql.append(table + " t"+i)
	.append((i==(depth-1))?" " : ", ");

	sql.append(" WHERE ");
	for (int i=0; i<(depth-1); i++)
	sql.append("t"+i+".RELATION_ID=t"+(i+1)+".ID AND ")
	.append("t"+i+".RELATION_NAME=t"+(i+1)+".NAME AND " );

	sql.append("t0.ID="+id);

	return sql.toString();
	}
	}