Java Hibernate Criteria Many-To-One

Question

I am trying to use hibernate with a Many-to-one relationship, as shown below:

I have a service table and for each service, we have a programId.

public class Service {
   ...
   @ManyToOne
   @JoinColumn(name="PROGRAM_LV_ID", referencedColumnName = "ID")
   private Program program;
}

In the BD, I have the records:

Service Table

id = 1, programid = 2

id = 2, programid = 2

id = 3, programid = 3

Program Table

id = 2, name = "program2"

id = 3, name = "program3"

I am trying to do something like:

public List<Service> getServicesForProgram(long id) {
    Criteria criteria = getSession().createCriteria(Service.class, "s");
        criteria.createAlias("s.program", "p");
        criteria.add(Restrictions.eq("p.id", id));

        return (List<Service>)criteria.list();
}

When I pass 2 to the method, I get 4 records instead of 2. Somehow the results are duplicated and I get the services with ids 1 and 2 twice.

Can someone help me with why the duplication is happening?

All help is greatly appreciated.

Thanks.

Answer 1

Your query in plain SQL is actually something like:

    SELECT * from service p, program where s.id = 1;

It just combines both records from service and program with id = 1 in the service table.

And hence if there are 2 records in the program table(where primary key is 1 and 2), and 2 in the service(where the foreign key is 1), all it will do is fetch 4 rows[2 x 2].

To fetch the records, you can use the DISTINCT criteria as:

   Criteria cr = getSession().createCriteria(Service.class);
   cr.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);