Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Bidirectional OneToOne Mapping Issue (EclipseLink ignoring Outer Left Join on query when doing a query for null on a sub object)
Bidirectional OneToOne Mapping Issue [message #805129] Thu, 23 February 2012 11:09 Go to next message
Jonathan Jones is currently offline Jonathan Jones
Messages: 4
Registered: January 2012
Junior Member
We have a simple 1-1 relationship where OjbectA may optionaly have an instance of ObjectB

ObjectB (if it exists) must have an instance of ObjectA

ObjectB is the owner of the relationship.

Imagine the simplified scenario of car making. A car may exist in the process and it may or may not yet have an engine installed.

Therefore
DDL
CREATE TABLE `car` (
  `PK_CAR_ID` int(11) NOT NULL,
  `CAR_MODEL` varchar(45) DEFAULT NULL,
) 
CREATE TABLE `engine` (
  `PK_ENGINE_ID` int(11) NOT NULL,
  `CAPACITY` varchar(45) DEFAULT NULL,
  `FK_CAR_ID` int(11) NOT NULL
) 


Car Class
@Entity
@Table(name="car")
public class Car implements Serializable {
	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	@Column(name="PK_CAR_ID", insertable=true, updatable=true, unique=true, nullable=false)
	private int pkCarId;

	@Column(name="CAR_MODEL", length=45)
	private String carModel;

	//bi-directional one-to-one association to Engine
	@OneToOne(optional=true,cascade=CascadeType.ALL)
	@JoinColumn(name="PK_CAR_ID", referencedColumnName="FK_CAR_ID", nullable=true, insertable=false, updatable=false)
	@JoinFetch(JoinFetchType.OUTER)
	private Engine engine;

....// REST OF THE ACCESOR METHODS 



Engine Class
@Entity
@Table(name="engine")
public class Engine implements Serializable {
	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	@Column(name="PK_ENGINE_ID", unique=true, nullable=false)
	private int pkEngineId;

	
	@Column(name="FK_CAR_ID")
	private int fkCarId;
	
	
	@Column(length=45)
	private String capacity;

	//bi-directional one-to-one association to Car
	@OneToOne(optional=false,mappedBy="engine",cascade=CascadeType.ALL)
	private Car car;

....// REST OF THE ACCESOR METHODS 



What we would like to do is find all Cars that dont have engines installed.

The sql works natively.. as in
select * from car as c left outer join engine as e on c.PK_CAR_ID=e.FK_CAR_ID where e.FK_CAR_ID is null

This returns me the results I am after.

However we try and do the equivalent in a JPA query as per the following but it seems to ignore the left outer join and replaces our where clause with the primary key from the car table and returns us no results.

	String queryStr = "SELECT c FROM " + Car.class.getSimpleName() + " c WHERE c.engine is null";
    	Query query = entityManager.createQuery(queryStr);
    	List<Car> results = query.getResultList();
    	System.out.println("Done = " + results.size());


Output
INFO: EclipseLink, version: Eclipse Persistence Services - 2.3.0.v20110604-r9504

FINE: SELECT t1.PK_CAR_ID, t1.CAR_MODEL, t0.PK_ENGINE_ID, t0.CAPACITY, t0.FK_CAR_ID FROM engine t0, car t1 WHERE ((t1.PK_CAR_ID IS NULL) AND (t0.FK_CAR_ID = t1.PK_CAR_ID))


I am assuming we have got something configured incorrectly on our relationship but cant for the life of us work out what. We have tried allsorts of configurations with no joy. If someone has experienced this before we would gratefully welcome any advice.

Many many thanks in advance.

Jonathan Jones


[Updated on: Thu, 23 February 2012 11:13]

Report message to a moderator

Re: Bidirectional OneToOne Mapping Issue [message #805294 is a reply to message #805129] Thu, 23 February 2012 15:45 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1023
Registered: July 2009
Senior Member
Hello,

Using c.engine forces an inner join between Car and engine, overriding the JoinFetch annotation on the relationship. Try instead using outer join notations:
"SELECT c FROM " + Car.class.getSimpleName() + " c LEFT JOIN c.engine e WHERE e is null"



Also, since your foreign key is in the Engine table, you need to make Engine control the relationship (and define the join column). Ie:
@OneToOne
@JoinColumn(name="FK_CAR_ID", insertable=false, updatable=false)
private Car car;

The Car->Engine would then use the mappedby instead. These two changes will remove the t1.PK_CAR_ID IS NULL and give you the SQL you are expecting.

Best Regards,
Chris
Re: Bidirectional OneToOne Mapping Issue [message #805329 is a reply to message #805294] Thu, 23 February 2012 16:37 Go to previous message
Jonathan Jones is currently offline Jonathan Jones
Messages: 4
Registered: January 2012
Junior Member
Hi Chris

Many thanks for taking the time to respond and your advice.

We actually had the entities configured that way round initially but thought we would see how eclipse created them for us as we were struggling so ended up with them the way we have them now.

I think the key piece of information you have supplied is
"Using c.engine forces an inner join between Car and engine, overriding the JoinFetch annotation on the relationship"

We are in the middle of a migration from another JPA layer and were hoping to just be able to modify our annotations and not the query statements, but we should be able to work with that solution.

Thanks again.

Jonathan







Previous Topic:Not creating the Unique constraint on the DB (postgreSQL)
Next Topic:Internal Exception: Can not set Clob to String
Goto Forum:
  


Current Time: Sat Sep 20 10:06:54 GMT 2014

Powered by FUDForum. Page generated in 0.07579 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software