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
] 
Search
Help
Register
Login
Home
