Problem with Hibernate Criteria, Projections and associations

[Peter Wagner]

Hi,

i have the following HQL and want to create an equivalent Criteria:

		Query query = sessionFactory.getCurrentSession()
						.createQuery("select " +
										"person.id as id, " +
										"person.namenskuerzelIntern as namenskuerzelIntern, " +
										"person.titel as titel, person.name as name, " +
										"person.vorname as vorname, " +
										"person.ort as ort, " +
										"person.anrede1 as anrede1" +
									" from Person2 person");
		
		query.setResultTransformer(Transformers.aliasToBean(Person2.class));

Person2.java:

public class Person2 implements java.io.Serializable {

	private Integer id;
	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "ANREDE1")
	private Anrede2 anrede1;
	private String titel;
	private String name;
	private String vorname;
        ...
}

The generated SQL loooks like this and works as expected:

    select
        person2x0_.PERSONENID as col_0_0_,
        person2x0_.TITEL as col_2_0_,
        person2x0_.NAME as col_3_0_,
        person2x0_.VORNAME as col_4_0_,
        person2x0_.ORT as col_5_0_,
        person2x0_.ANREDE1 as col_6_0_,
        anrede2x1_.ANREDE as BP1_15_,
        anrede2x1_.ANREDE12 as ANREDE2_15_,
        anrede2x1_.BEZEICHNUNG as BEZEICHN3_15_,
    from
        PERSONEN person2x0_ 
    inner join
        ANREDEN anrede2x1_ 
            on person2x0_.ANREDE1=anrede2x1_.ANREDE

With Hibernate Criteria i tried this:

		Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Person2.class);

		criteria.createCriteria("anrede1","anrede",CriteriaSpecification.LEFT_JOIN);
		
		criteria.setProjection(Projections.projectionList()
				.add(Projections.distinct(Projections.property("id").as("id")))
				.add(Projections.property("id"),"id")
				.add(Projections.property("namenskuerzelIntern"),"namenskuerzelIntern")
				.add(Projections.property("titel"),"titel")
				.add(Projections.property("name"),"name")
				.add(Projections.property("vorname"),"vorname")
				.add(Projections.property("ort"),"ort")
				.add(Projections.property("anrede1"),anrede1))
			.setResultTransformer(Transformers.aliasToBean(Person2.class))
			.addOrder(Order.asc("name"));
		
		return criteria.list();

But Hibernate generates the follwogin SQL:

    select
        distinct this_.PERSONENID as y0_,
        this_.PERSONENID as y1_,
        this_.NAMENSKUERZEL_INTERN as y2_,
        this_.TITEL as y3_,
        this_.NAME as y4_,
        this_.VORNAME as y5_,
        this_.ORT as y6_,
        this_.ANREDE1 as y7_ 
    from
        PERSONEN this_ 
    left outer join
        ANREDEN anrede1_ 
            on this_.ANREDE1=anrede1_.ANREDE 
    order by
        y4_ asc

As you can see, the generated SQL for the "anrede1" association is not correct. My question is, how can i set a projection for an association inside my criteria? ((Projections.property("anrede1"),anrede1) doesn't work...)?

Thanks in advance.

Best regards,

peter