zwwcn

Just another WordPress.com site

Category Archives: Hibernate

hibernate criteria on composite id

@Embeddable
public class TeacherStudentPK  implements java.io.Serializable{
    @ManyToOne
    @JoinColumn(name = "STUDENT_ID")
	private Student student;
    @ManyToOne
    @JoinColumn(name = "TEACHER_ID")
public class TeacherStudent implements java.io.Serializable {

	/* primay key*/
	@EmbeddedId 
	private TeacherStudentPK teacherStudentPK;
      public class Student{
	@OneToMany(mappedBy="teacherStudentPK.student")
	private Collection teachers = new ArrayList();

The crieteria is shown below:

criteria = em.createCriteria(Student.class, "student")
		   .createAlias("student.teachers", "teacherStudent")
.add(Restrictions.eq("teacherStudent.teacherStudentPK.teacher", selectedTeacher))
									

JPA native query with parameter

I tried ?name but couldn’t get it working. I end up with using number (?1 ,?2) to build up my dynamic query and it works properly.
Query query = em.createNativeQuery(“SELECT * FROM OP_CHECKLIST c WHERE c.DATE BETWEEN ?1 AND ?2 “);

Query query = em.createNativeQuery("SELECT * FROM OP_CHECKLIST c WHERE c.DATE BETWEEN ?1 AND ?2 ");
query.setParameter("1", sdf.format(fromDate));
query.setParameter("2", sdf.format(endDate));

getting distinct row count in hibernate criteria

Projections.rowCount could return a wrong result when joining is used in the criteria. I used “setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)” in the criteria to get the distinct items but it  doesn’t work with Projections.rowCount.

 

 

 
This won’t work, it still contain duplicates:

	public Criteria countDefaultCriteria(){
		return getIncidentsDefaultCriteria().setProjection(Projections.rowCount());
	}

This give the correct row count:

	public Criteria countDefaultCriteria(){
		return getIncidentsDefaultCriteria().setProjection(Projections.countDistinct("objID"));
	}

Concat in JPA native query

I am writing some native query to record customized info.

SELECT COUNT(DISTINCT i.OBJECT_ID),CONCAT(YEAR(i.DATE) ,'-',MONTH(i.DATE)) as yearDate FROM OP_INCIDENT i GROUP BY YEAR(i.DATE),MONTH(i.DATE)

The above query is supposed to generate data in format like [2,’2015-09′], but what I got is [2,[54,43,41,49,50]]. Instead giving a string year-month value, it gives me a data array.

An explicit type cast sovle the problem:

SELECT COUNT(DISTINCT i.OBJECT_ID),CONCAT(CAST(YEAR(i.DATE) AS CHAR),'-',CAST(MONTH(i.DATE) AS CHAR)) as yearDate FROM OP_INCIDENT i GROUP BY YEAR(i.DATE),MONTH(i.DATE)

order by before group by in mysql

We have two entities: Activity 1:n Conversation. I need to get the latest conversation for each of the activity. The MySql doesn’t allow me to put order by before the group by.
I found a workaround from this :

So I get something like:
SELECT * FROM ( SELECT * FROM OP_TICKET_CONVERSATION WHERE ACTIVITY_ID IN (ticketsIds) ORDER BY OBJECT_ID DESC) AS tc GROUP BY tc.ACTIVITY_ID

This works but hibernate returns Object type, we need to specify the root entity:

this could be done by adding “addEntity”.

List conversationsList = em.createSQLQuery(sqlString).addEntity(TicketConversation.class).list();

fetch join collection break firstResult/MaxResult in hibernate

If a fetch join is used along with order by, hibernate will load all records into memory to do the sorting. It will ignore the firstResult/MaxResult which mean no real database pagination.

em.createQuery("SELECT a FROM TicketTracker tt
LEFT JOIN tt.ticket a
LEFT JOIN FETCH a.conversation tc
WHERE a.status!=400 GROUP BY tt.ticket ORDER BY a.dueDate", TicketActivity.class)

.setFirstResult(0).setMaxResults(10).list();

In this example, even we didn’t order records by the join entity, the join fetch still break the setFirstResult/SetMaxResult . I have to write a seperate query to fetch the conversations records.

cannot simultaneously fetch multiple bags

ref from http://stackoverflow.com/questions/17566304/multiple-fetches-with-eager-type-in-hibernate-with-jpa

The root cause of the problem is that when Hibernate fetches SQL query results there is no simple way to tell which child element belongs to which collection. See this blog entry for more detailed explanation with an example. To summarize you have following workarounds:

  • Load each collection separately using subselect @Fetch(FetchMode.SELECT)
  • Force usage of list instead of bag by adding index column @IndexColumn(name="LIST_INDEX")
  • Use unordered collection like Set.

Hibernate one to many filtering

Recently I need to do an hiberate one to many filtering, but it’s not as easy as I thought.

Firstly, hibernate criteria doesn’t allow you join the same association twice, which mean you can’t have code like this:


finalCriteria.createAlias("a.b","b1", JoinType.LEFT_OUTER_JOIN)

.createAlias("a.b","b2", JoinType.LEFT_OUTER_JOIN);

Two workarounds are mentioned on https://hibernate.atlassian.net/browse/HHH-879, I tried use detachedCriteria but got the same error.

The second solution works for me:


Criterion  criterion  = Restrictions.sqlRestriction(" ((exists (select tc.OBJECT_ID from OP_TICKET_CATEGORY_OPERATOR tc where tc.TICKET_CATEGORY_ID = this_.OBJECT_ID AND tc.OPERATOR_ID ="+selectedResolver.getObjID()+" AND tc.OPERATOR_TYPE='RE')) AND (exists (select tc.OBJECT_ID from OP_TICKET_CATEGORY_OPERATOR tc where tc.TICKET_CATEGORY_ID = this_.OBJECT_ID AND tc.OPERATOR_ID ="+selectedOpener.getObjID()+" AND tc.OPERATOR_TYPE='OP')))" );
finalCriteria.add(criterion);

 

Entities:


OP_TICKET_CATEGORY

@OneToMany(mappedBy="ticketCategory",fetch=FetchType.LAZY,orphanRemoval=true)
private List<TicketCategoryOperator> ticketCategoryOperators;

------------------------------------------------

OP_TICKET_CATEGORY_OPERATOR

@ManyToOne
@JoinColumn(name = "TICKET_CATEGORY_ID",nullable = true)
private TicketCategory ticketCategory;

@ManyToOne
@JoinColumn(name = "OPERATOR_ID",nullable = true)
private Operator operator;

TicketCategoryOperatorType type = TicketCategoryOperatorType.OPENER;

 

reset criteria between get rowcount and the execution of the criteria

If we use the same criteria for returning the rowCount and also the data list, we need to reset it after getting rowCount

 


Long rowCount = (Long) searchCriteria.setProjection(Projections.rowCount()).uniqueResult();
<strong>  searchCriteria.setProjection(null);</strong>
searchCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

searchCriteria.list();

org.hibernate.validator.InvalidStateException: validation failed for

for this validation error, use below code to find the wrong property:

catch (InvalidStateException e) {
    for (InvalidValue invalidValue : e.getInvalidValues()) {
        log.info("Instance of bean class: " + invalidValue.getBeanClass().getSimpleName() +
                 " has an invalid property: " + invalidValue.getPropertyName() +
                 " with message: " + invalidValue.getMessage());
    }
}