Disjuction (OR operator)

Writing OR clause using criteria API.

In this case operation parameter can be passed as optional / varargs (arbitrary number of values ).

private void addOperationToCriteria(Criteria criteria, SomeOperation ... operation)
{
if (operation == null)
return;
if (operation.length == 1)
criteria.add(Restrictions.eq("operation", operation[0]));
else
{
Disjunction dis = Restrictions.disjunction(); // OR
for (SomeOperation o : operation)
{
dis.add(Restrictions.eq("operation", o));
}
criteria.add(dis);
}
}


SELECT FOR UPDATE

We have 2 machines with active active configuration and there is table with column for counters.
Db is on 3rd machine.
How to update these columns?


Example from code:

public InitialStateEntity findByIdWithLock(Long id)
{
DetachedCriteria criteria = DetachedCriteria.forClass(InitialStateEntity.class);
criteria.add(Restrictions.idEq(id));
criteria.setLockMode(LockMode.PESSIMISTIC_WRITE);
return (InitialStateEntity) criteria.getExecutableCriteria(getSession()).uniqueResult();
}


Ref:
https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/transactions.html

JPA Composite Key

Create class that will represent composite key and has implemented hashCode and equals methods.

public class MsisdnCompositeKey implements Serializable
{
private static final long serialVersionUID = 1L;

private String msisdn;
private Long providerId;
.....


Class that uses this composite key needs to be annotated with @IdClass and have same fields (msisdn and providedId as in example).

@Entity
@Table(name = "MSISDN")
@IdClass(value = MsisdnCompositeKey.class)
public class Msisdn
{
private static final long serialVersionUID = 1L;

@Id
private String msisdn;
@Id
private Long providerId;

.....

Ref:
https://stackoverflow.com/questions/13032948/how-to-create-and-handle-composite-primary-key-in-jpa

Count

Using criteria API:

session.createCriteria(Book.class).setProjection(Projections.rowCount()).uniqueResult();

or native SQL:

session.createQuery("select count(*) from Book").uniqueResult()

Ref:

https://stackoverflow.com/questions/1372317/how-do-we-count-rows-using-hibernate

Hibernate/persistence annotations

Some useful annotations when using
hibernate.hbm2ddl.auto to update | create | create-drop
If hbm2ddl.auto is set to update it will not change type of field!
     postgres:  ALTER TABLE users ALTER COLUMN role TYPE varchar(16);

javax.persistence.Lob with/without org.hibernate.annotations.Type

Using postgres and omitting Type for text lobs will cause numbers in database tables. 

Like operator will not work!

Solution:

@Lob

@Type(type = "org.hibernate.type.TextType")

If there was version without @Type and you want to update/migrate, please share your expirience.
(If you add @Type, old records will be read as plain strings.)
// This was tested with hibernate 3.6.10. and 5.1.0

org.hibernate.annotations.ForeignKey

Very useful for not having generated names as foreign keys.

In JPA 2.1 you can use javax.persistence.ForeignKey
@JoinColumn(foreignKey = @ForeignKey(name = "FK_ORDER_CUSTOMER"))

javax.persistence.Enumerated(javax.persistence.EnumType.STRING)

When using enum, store it in db as string, not a number

javax.persistence.Id with javax.persistence.Column

This combination will cause a lot of problems with hsqldb (column annotation is same like Id constraint). Postgres will not complain.

@Id

@Column(name = "ID", nullable = false, unique = true)

Solution:

remove nullable and unique

Tested on hsqldb 2.3.2 (as in memory) and postgres 9.2/9.3

Ref:

http://www.shredzone.de/cilla/page/299/string-lobs-on-postgresql-with-hibernate-36.html
http://java.dzone.com/articles/postgres-and-oracle

Exists via Criteria API

Parent class has List of Sons. (1:n)
        @OneToMany(mappedBy = "parent", fetch = FetchType.LAZY)
        @Cascade( { CascadeType.SAVE_UPDATE, CascadeType.LOCK })
        @OrderBy("changeDate DESC")
Son has 1 Parent. (n:1)
        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "parentoid", nullable = false)
FK is set on Son table to Parent and it is called PARENTOID.
How to get Parents that have at least 1 son?

select *
  from PARENT p
   where
       exists
            (select h.oid from SON h where p.oid = H.PARENTOID)

2 ways:
1) via Restrictions.isNotEmpty (easy)
DetachedCriteria crit= DetachedCriteria.forClass(Parent.class);
crit.add(Restrictions.isNotEmpty("sons"));

2) via Subqueries.exists - you can add more restrictions in subquery's WHERE clause
DetachedCriteria crit= DetachedCriteria.forClass(Parent.class);

DetachedCriteria subquery = DetachedCriteria.forClass(Son.class, "h");
subquery.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
subquery.setProjection(Projections.id());
subquery.add(Property.forName("parent").eqProperty(crit.getAlias() + ".oid"));
crit.add(Subqueries.exists(subquery));



Hibernate LazyInitializationException handling

In toString() method you want to output objects that are lazy fetched.
Solution that I use is via reflection with exception catching.
This will work only for fields declared in this class not super!

@Override:
public String toString()
{
   StringBuilder sb = new StringBuilder();
   sb.append(......);
   sb.append(ExceptionHandler.ignoreLazy(this, "zone"));
   return sb.toString();
}

public class ExceptionHandler:
public static String ignoreLazy(Object whom, String property)
{
  try
  {
    Field field = whom.getClass().getDeclaredField(property);
    field.setAccessible(true);
    Object print = field.get(tkoMeSadrzi);
    return print.toString();
 }
 catch (NoSuchFieldException e)
 {
   log.warn("DEVELOPER EXC ", e);
   return "NoSuchFieldException: " + e.getMessage();
  }
  catch (Exception lazy)
  { //ignore
    //log.debug("******* IGNORE LAZY : "+property);
    return "lazy loaded!";
  }
}


Hibernate show sql with parameters

#log hibernate prepared statements/SQL queries 
#(equivalent to setting 'hibernate.show_sql' to 'true')
log4j.logger.org.hibernate.SQL=debug

# log JDBC bind parameter runtime arguments
log4j.logger.org.hibernate.type=trace

To format sql output put this line in hibernate configuration file:
hibernate.format_sql

Calling procedure using SQL

In my case procedure does not need to return anything.
Calling it from DAO layer:

String queryString = "call myProcedure()";
Query query = getSession().createSQLQuery(queryString);
query.executeUpdate();

Associations via criteria

Fetch data from one table by criteria on linked table

Person has Address. (1:1)
Address has field street.
I want to fetch all Persons that has Address with with specific street.
DetachedCriteria criteria= DetachedCriteria.forClass(Person.class);
criteria.createCriteria("address").add(Restrictions.eq("street", "WANTED"));

SELECT *
    FROM
        PERSON p
    inner join
        ADDRESS a
            on p.addressID=a.ID 
  WHERE
a.street=?

Fetch data from one table by having foreign key of linked table

Object A has 1:1 relation to object B.
Table A has foreign key to table B.
Class A has B.
I want to fetch class A and I only have ID of class B.
How to construct criteria?
SELECT * FROM a WHERE b_ID=?
//createCriteria on class A
criteria.add(Restrictions.eq("b.id", b.getId()));
criteria.list();


Fetch data from 3 linked tables

DPT has 1:n relation to OFFCOURTPAYMENT,
OFFCOURTPAYMENT has 1:n relation to SUBJECTASSEMBLY
How to fetch DPTs that belong to specific SUBJECTASSEMBLY?
select d.* from DPT d
INNER JOIN OFFCOURTPAYMENT o ON D.ACTIVEOCPOID = O.OID
INNER JOIN SUBJECTASSEMBLY sa ON SA.OID = O.SUBJECTASSEMBLYOID
where d.COMPANYOID= coid (4)
and D.CURRENTSTATE = 'MIROVANJE'
and SA.RECORDNUMBER = '1'
and SA.SUBJECTABLETYPE = 'OCS_DPT'

SubjectAssembly sa is passed in method
DetachedCriteria crit = DetachedCriteria.forClass(DailyParkingTicket.class);
crit.add(Restrictions.eq("currentDPTState", DPTState.StateName.MIROVANJE));
DetachedCriteria saCrit = crit.createCriteria("activeOcp").createCriteria("subjectAssembly"); // 2 inner joins will be generated
saCrit.add(Restrictions.eq("recordNumber", sa.getRecordNumber()));
saCrit.add(Restrictions.eq("subjectableType", sa.getSubjectableType()));
criteria.getExecutableCriteria(getSession()).list();

Notes

I want to fetch (via join) objects associated with my source object A:
A has list of Bs and B has C.
DetachedCriteria criteria= DetachedCriteria.forClass(A.class);
criteria.setFetchMode("B", FetchMode.JOIN); //must be present!
criteria.setFetchMode("B.C", FetchMode.JOIN);


// without DISTINCT_ROOT_ENTITY @ManyToMany returns full cartesian join!
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

@Column annotation and constraints in db

@Column(unique = true, nullable = false)
private Date startDate;

This annotation is description on startDate column when reading java file.
It is not used to stop inserting or updating database with false values, if you do not have constraints on table!
It can be used for generating table from entity class.

1 exception to the above rule:
If you try insert null into not null field declared in annotation it will throw Exception!

Hibernate -distinct (with disjunction) via criteria

How to write distinct using Hibernate criteria API?

DetachedCriteria crit = DetachedCriteria.forClass(clazz);

Disjunction dis = Restrictions.disjunction(); // OR
dis.add(Restrictions.eq("billType", BillType.RACUN));
dis.add(Restrictions.eq("billType", BillType.STORNO));
crit.add(dis);

crit.setProjection(Projections.distinct(Projections.property("billSubtype")));

Criteria criteria = crit.getExecutableCriteria(getSession());
return crit.list();

select distinct B.BILLSUBTYPE from bill b where (B.BILLTYPE = 'RACUN' or B.BILLTYPE = 'STORNO')


http://www.jairrillo.com/blog/2009/01/29/how-to-use-left-join-in-hibernate-criteria/

Hibernate - SQL Query

This post is written while using Hibernate 3.x version

String sqlQuery =
"select max(to_number(POLJE)) from TABLICA where COMPANYID=:company ";
SQLQuery query = getSession().createSQLQuery(sqlQuery);
query.setParameter("company", 1008);
query.addScalar("max(to_number(POLJE))", Hibernate.LONG); //in db it is varchar
// you need to use StandardBasicTypes.LONG) in Hibernate v 4.3.x
Long l = (Long) query.uniqueResult();
if (l == null)
return 0L;
else return lL;
//if result is empty result set.. returned value will be null

Generated SQL:

select
    max(to_number(POLJE))
from
    TABLICA
WHERE
    COMPANYID=?


When using helper entity (that is not persisted) when data is fetched from many tables you can use ResultTransformer:
select B.BILLNUMBERPREFIX || B.BILLNUMBER AS billNumber, T.CREDIT, T.EVENTDATE ....
query.addScalar("billNumber").addScalar("credit", LongType.INSTANCE).addScalar("eventDate");
//necessary because property CREDIT is not the same as credit in Java (case sensitivity!) query.setResultTransformer(Transformers.aliasToBean(MyHelperClass.class));
log.info("\n\nSQL: " + query.getQueryString() + "\n\n");

How to map enum field?
import org.hibernate.impl.TypeLocatorImpl;
import org.hibernate.type.Type;
import org.hibernate.type.TypeResolver;
import org.hibernate.type.EnumType;

import org.hibernate.type.LongType;

Properties pmParams = new Properties();
pmParams.put("enumClass", "hr.samara.model.PaymentMethod");
pmParams.put("type", "12"); /* EnumType.STRING type = 12 */
Type pmType = new TypeLocatorImpl(new TypeResolver()).custom(EnumType.class, pmParams);

I removed default setter and made custom - bad :(
public void setSmsState(String smsState)
{
   this.smsState = SmsState.valueOf(smsState);
}


Ref:
http://www.journaldev.com/3422/hibernate-native-sql-example-addscalar-addentity-addjoin-parameter-example
https://stackoverflow.com/questions/9633337/hibernate-sql-transformation-fails-for-enum-field-type

Spring with Hibernate configuration

Loads object from the data source

<bean id="someRepository">

    <property name="sessionFactory" ref="sessionFactory"/>

</bean>

Instructs the container to look for beans with @Transactional and decorate them

<tx:annotation-driven transaction-manager="transactionManager"/>

A Hibernate SessionFactory for mapping entities from object to relation tables

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">

   <property name="dataSource" ref="dataSource"/>

   <property name="annotatedClasses">

      <list>

         <value>package.Entity1</value>

         <value>package.Entity2</value>

      </list>

   </property>

   <property name="hibernateProperties">

      <value>

         hibernate.format_sql=true

         hibernate.show_sql=true

      </value>

   </property>

</bean>

A transaction manager for working with Hibernate SessionFactories

<bean id="transactionManager"  

    class="org.springframework.orm.hibernate3.HibernateTransactionManager">

    <property name="sessionFactory" ref="sessionFactory" />

</bean>

Transparent Exception Handling

Annotate classes with @Repository

Define a Spring-provided BeanPostProcessor (eg. <context:annotation-config/> or context:component-scan)


Ref:
https://spring.io/blog/2012/04/06/migrating-to-spring-3-1-and-hibernate-4-1
http://sethuramanmurali.wordpress.com/2013/09/15/difference-between-and-annotation-config-and-component-scan/