zwwcn

Just another WordPress.com site

Category Archives: JPA

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));

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.

find object can’t be serialized

I encountered a problem today :one of the object can't be serialized, then I found
this useful code on from http://www.theserverside.com/discussions/thread.tss?thread_id=17336
       public static final Object clone(Serializable in) {
            try {
                ByteArrayOutputStream byteOutStream = new ByteArrayOutputStream();
                ObjectOutputStream outStream = new ObjectOutputStream(byteOutStream);
                outStream.writeObject(in);
                ByteArrayInputStream byteInStream =
                    new ByteArrayInputStream(byteOutStream.toByteArray());
                ObjectInputStream inStream = new ObjectInputStream(byteInStream);
                return inStream.readObject();
            } catch (OptionalDataException e) {
             throw new RuntimeException("Optional data found. " + e.getMessage()); 
            } catch (StreamCorruptedException e) {
             throw new RuntimeException("Serialized object got corrupted. " + e.getMessage());
            } catch (ClassNotFoundException e) {
             throw new RuntimeException("A class could not be found during deserialization. " + e.getMessage()); 
            } catch (NotSerializableException ex) {
                ex.printStackTrace();
             throw new IllegalArgumentException("Object is not serializable: " + ex.getMessage());
            } catch (IOException e) {
             throw new RuntimeException("IO operation failed during serialization. " + e.getMessage()); 
            }
        }

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();

value is not valid in h:selectonemenu convertor Validation

The equals method of the entity should return true. What happened in my case is that the entity is lazy loaded, which got a class type of lazyinitializer.  overwrite the equals method solve the problem:

remove this code:

if (getClass() != obj.getClass())
return false;