I have a many-to-many join between two entities. One is an employee class. The other is a Device class.
I am trying to find the one employee who has a certain employee number and who is associated with a device. When I execute the query
Code:
select e from Employee e where e.employeeNumber = :employeeNumber and :device MEMBER OF e.devices
I get no results... I was sure I had persisted an entity that matched the search criteria, so I added some code to my catch block.
Instead of querying on employee number and device, I just queried on the employee number. I then iterated through the the devices associated with the employee. What I found was that the device was in its list...
I am wondering why the "MEMBER OF" portion of my query is not working... Why do I get no results if the employee number matches and the device is in the employee's device list?
FYI - The System.out.println portion of this code prints this:
Employee Device Size: 1
**** Device: 1 - time.jpa.Device@d198582
**** Compare: 1 - time.jpa.Device@d198582
indicating to me that the device passed to the method (the one used in the "MEMBER OF" portion of the query is the device that the employee holds...
Any help is appreciated...
Hibernate version: JPA with Hibernate version 3.2.1 GA
Code between sessionFactory.openSession() and session.close():Code:
public Employee findEmployeeByEmployeeNumberAndDevice(int employeeNumber, Device device)
{
Employee employee = null;
Query query = em.createQuery("select e from Employee e where e.employeeNumber = :employeeNumber and :device MEMBER OF e.devices");
query.setParameter("employeeNumber", employeeNumber);
query.setParameter("device", device);
try
{
employee = (Employee)query.getSingleResult();
}
catch (NoResultException e)
{
e.printStackTrace();
// Extra code to figure out what is going on...
query = em.createQuery("select e from Employee e where e.employeeNumber = :employeeNumber");
query.setParameter("employeeNumber", employeeNumber);
List<Employee> list = query.getResultList();
for (Employee em : list)
{
System.out.println("Employee Device Size: " + em.getDevices().size());
for (Device de : em.getDevices())
{
System.out.println("**** Device: " + de.getDeviceID() + " - " + de.toString());
System.out.println("**** Compare: " + device.getDeviceID() + " - " + device.toString());
}
}
// end extra code
}
return employee;
}
Full stack trace of any exception that occurs:Code:
javax.persistence.NoResultException: No entity found for query
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:82)
at net.brianirwin.time.ejb.CollectorBean.findEmployeeByEmployeeNumberAndDevice(CollectorBean.java:106)
at net.brianirwin.time.ejb.PunchCollectorBean.insertPunchWithDeviceID(PunchCollectorBean.java:52)
at net.brianirwin.time.ejb.PunchCollectorBeanTest.testInsertPunchWithDeviceID(PunchCollectorBeanTest.java:88)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:168)
at org.jmock.core.VerifyingTestCase.runBare(Unknown Source)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:81)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Name and version of the database you are using:Assuming it is Hypersonic... I am running this test with EJB3Unit
The generated SQL (show_sql=true):Code:
Hibernate: select employee0_.employeeID as employeeID53_, employee0_.lastName as lastName53_, employee0_.firstName as firstName53_, employee0_.defaultDivisionID as default15_53_, employee0_.devicePunchCode as devicePu4_53_, employee0_.password as password53_, employee0_.active as active53_, employee0_.divisionSupervisorID as divisio14_53_, employee0_.dsiplayOnReports as dsiplayO7_53_, employee0_.employeeNumber as employee8_53_, employee0_.shift as shift53_, employee0_.startDate as startDate53_, employee0_.endDate as endDate53_, employee0_.exempt as exempt53_, employee0_.deviceData as deviceData53_ from Employee employee0_ where employee0_.employeeNumber=? and (? in (select device2_.deviceID from DeviceEmployee devices1_, Device device2_ where employee0_.employeeID=devices1_.employeeID and devices1_.deviceID=device2_.deviceID))
Other code:Here is the Employee class code for the Many-to-Many join with Devices
Code:
/** All devices this employee is associated with */
@ManyToMany(fetch=FetchType.LAZY, cascade=CascadeType.ALL, mappedBy="employees")
//@JoinTable(name="DeviceEmployee")
@JoinTable(name="DeviceEmployee", joinColumns={@JoinColumn(name="employeeID")},
inverseJoinColumns={@JoinColumn(name="deviceID")})
private List<Device> devices;
Login
FAQ
Search
