Pass a object as query parameter and exclude the ID column

Question

I have the following:

@Entity
@NamedQuery(name = "listCarsBySecurity", query = "SELECT c FROM Car c WHERE c.security = :security"
public class Car {  
    @Id
    @GeneratedValue
    private Long id;

    @NotNull()
    @Column(nullable = false)   
    private String make;

    @NotNull()
@Column(nullable = false)
private String model;

    // Some more fields

    @NotNull()
@OneToOne (fetch = FetchType.LAZY, orphanRemoval=true)
private Security security = new Security();

    // Some getters and setters

As you can see, the Car class has a "Security" object which is LAZY fetched. The security class looks like:

@Entity public class Security {

@Id @GeneratedValue
private Long id;

// Security equipment. Add in alphanumerical order
private boolean abs;
private boolean airbag;
private boolean antispin;

// Some getters and setters

as you can see, the named query list try to list all cars which has a security entity equal to a provided security object.

The persistence method looks like:

 @Stateless
public class CarEJB {

    @PersistenceContext(unitName = "carcmsPU")
    private EntityManager em;

    public List<Car> listCarsBySecurity(Security security) {
        TypedQuery<Car> query = em.createNamedQuery("listCarsBySecurity", Car.class);
        query.setParameter("security", security);
        return query.getResultList();
    }

And a junit test looks like:

@Test
public void searchCar() throws Exception {
    // Looks up the EJBs        
    carEJB = (CarEJB) ctx.lookup("java:global/classes/CarEJB");

    // Create a new Ferrari with security = ABS brakes and Airbag
    Car car = new Car();
    car.setMake("Ferrari");
    car.setModel("Some model");
    car.setSubModel("Some sub model");
    car.setEngine("Some engine");
    car.setYear(1999);        
    car.getFuel().setGasoline(true);
    car.setGearbox(Gearbox.AUTOMATIC);
    car.setKilometres(323);
    car.setDescription("This is a description");
    Security security = new Security();
    security.setAbs(true);
    security.setAirbag(true);
    car.setSecurity(security);

    carEJB.createCar(car); // Persist

    // Create a new security object and match it to the former one
    Security securityObject = new Security();
    securityObject.setAbs(true);
    securityObject.setAirbag(true);


    List<Car> carList = carEJB.listCarsBySecurity(securityObject);

    assertTrue("Should contain at least 1 car with ABS and Airbag", carList.size() > 0 );
    for (Car carTemporary : carList) {
        System.out.println(carTemporary.toString());

    }



}

The thing is that the list does not contain any cars at all. And I think I know why; the named query does try to match the security_id with NULL (since I have not define it).

My question is: How can I perform a query by passing a object as a query parameter with no ID and by not specify all fields which shall be compared inside that object? (or how exclude the ID from a search)?

Best regards

Answer 1

You can define a named query using OR and passing each one of the object's attributes. You can also use Criteria API to build a query based on the fields you want to query about. Since you already have a named query I'll leave that one to you.

If you decide to go that way (tough field by field comparation is kind of insane if your entity has way too many attributes). Using criteria you can do something like this:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Car> query =  builder.createQuery(Car.class);
Root<Car> queryRoot = query.from(Car.class);
query.select(queryRoot);

Path<String> pathToYourField = root.get(yourField); //yourField is a variable containing the field. 
                                                    //You can store all the variables in a list, iterate
                                                    //over them and do this for each one.
query.where(builder.and(builder.equal(pathToYourField, "particularValue"))); //You compare the path against a value.
//Rest of the fields / paths

TypedQuery<Car> typedQuery = entityManager.createQuery(query);
List<Car> cars = typedQuery.getResultList();

EDIT: About performance, check this links:

1. JPA Criteria vs NamedQueries
2. Another answer regarding Criteria vs HQL
3. Criteria overhead discussion