EJ3 Problem. LEFT JOIN doesn磘 work with ManyToMany relations
I have two entities, Project and Tool that they have a many to many relation. I execute this sql query and I don`t get any result:
List<Project> resul = em.createQuery("SELECT p FROM Project p LEFT JOIN p.tools t").getResultList();
In the database there is one row in Project table that doesn't point to any Tool. I don't understand why doesnt' return any row.
@Entity
public class Project implements Serializable
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long idProject;
private String name;
private String description;
@ManyToMany
@JoinTable(name="Tool_Project",
joinColumns=@JoinColumn(name="IdProject"),
inverseJoinColumns=@JoinColumn(name="idTool"))
private Collection<Tool> tools;
....
}
@Entity
public class Tool implements Serializable
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long idTool;
private String name;
@ManyToMany(mappedBy="tools")
private Collection<Project> projects;
....
}
Thank you in advance.
Hayken
[1180 byte] By [
haykena] at [2007-11-27 4:31:52]
# 1
The sql query generated by toplink is erroneous.
In this url you can see the code bug. Besides It is marked with bug id (4240751).
http://fisheye5.cenqua.com/browse/glassfish/entity-persistence/src/java/oracle/toplink/essentials/internal/expressions/SQLSelectStatement.java?r=1.17
Oracle says that have fixed this bug in the comercial version of toplink but this mistake hasn磘 been corrected in toplink essentials
http://www.oracle.com/technology/products/ias/toplink/technical/relnotes/tl_relnotes.htm
I have downloaded the source code of toplink essentials and I fixed this bug. I have compiled it and I have tested. Now it works. I hope that fix soon this bug for future versions.
Now I am going to show this fixed bug. I have commented the parts of original code that doesn磘 work and marked that I have modified.
//Bug#4240751 Treat ManyToManyMapping separately for out join
} else if (outerExpression.getMapping().isManyToManyMapping()) {
// Must outerjoin each of the targets tables.
// The first table is joined with the mapping join criteria,
// the rest of the tables are joined with the additional join criteria.
// For example: EMPLOYEE t1 LEFT OUTER JOIN (PROJ_EMP t3 LEFT OUTER JOIN PROJECT t0 ON (t0.PROJ_ID = t3.PROJ_ID)) ON (t3.EMP_ID = t1.EMP_ID)
DatabaseTable relationTable = ((ManyToManyMapping)outerExpression.getMapping()).getRelationTable();
DatabaseTable relationAlias = ((Expression)getOuterJoinedMappingCriteria().elementAt(index)).aliasForTable(relationTable);
//writer.write(" LEFT OUTER JOIN (");
writer.write(" LEFT OUTER JOIN "); // hayken
writer.write(relationTable.getQualifiedName());
writer.write(" ");
outerJoinedAliases.addElement(relationAlias);
writer.write(relationAlias.getQualifiedName());
Vector tablesInOrder = oracle.toplink.essentials.internal.helper.NonSynchronizedVector.newInstance(3);
// glassfish issue 2440: store aliases instead of tables
// in the tablesInOrder. This allows to distinguish source
// and target table in case of an self referencing relationship.
tablesInOrder.add(sourceAlias);
tablesInOrder.add(relationAlias);
tablesInOrder.add(targetAlias);
TreeMap indexToExpressionMap = new TreeMap();
mapTableIndexToExpression((Expression)getOuterJoinedMappingCriteria().elementAt(index), indexToExpressionMap, tablesInOrder);
Expression sourceToRelationJoin = (Expression)indexToExpressionMap.get(new Integer(1));
Expression relationToTargetJoin = (Expression)indexToExpressionMap.get(new Integer(2));
// hayken {
writer.write(" ON ");
if (session.getPlatform() instanceof DB2MainframePlatform) {
((RelationExpression)sourceToRelationJoin).printSQLNoParens(printer);
} else {
sourceToRelationJoin.printSQL(printer);
}
// hayken }
//writer.write(" JOIN ");
writer.write(" LEFT OUTER JOIN "); // hayken
writer.write(targetTable.getQualifiedName());
writer.write(" ");
outerJoinedAliases.addElement(targetAlias);
writer.write(targetAlias.getQualifiedName());
writer.write(" ON ");
if (session.getPlatform() instanceof DB2MainframePlatform) {
((RelationExpression)relationToTargetJoin).printSQLNoParens(printer);
} else {
relationToTargetJoin.printSQL(printer);
}
Map tablesJoinExpression = (Map)getOuterJoinedAdditionalJoinCriteria().elementAt(index);
if(tablesJoinExpression != null && !tablesJoinExpression.isEmpty()) {
printAdditionalJoins(printer, outerJoinedAliases, outerExpression.getMapping().getReferenceDescriptor(), tablesJoinExpression);
}
/*writer.write(") ON ");
if (session.getPlatform() instanceof DB2MainframePlatform) {
((RelationExpression)sourceToRelationJoin).printSQLNoParens(printer);
} else {
sourceToRelationJoin.printSQL(printer);
}*/
}
hayken
