How to fetch total record count using Hibernate Criteria
How to fetch total record count using Hibernate Criteria
Hibernate: Case Insensitive Query
public List
fetchByCountry(String country) {
DetachedCriteria criteria = DetachedCriteria.forClass(MobileCarrier.class);
criteria.add(Restrictions.ilike("country", country, MatchMode.EXACT));
return getHibernateTemplate().findByCriteria(criteria);
}
Hibernate: Case Insensitive Query
public List
fetchByCountry(String country) {
DetachedCriteria criteria = DetachedCriteria.forClass(MobileCarrier.class);
criteria.add(Restrictions.ilike("country", country, MatchMode.EXACT));
return getHibernateTemplate().findByCriteria(criteria);
}
Using TINY_INT for Java Boolean types with Hibernate
>>We are using Spring 2.0 and Hibernate 3.2 with a MySQL 5 database.
THE PROBLEM: By default Hibernate persists properties that are of Java Boolean or boolean type as CHAR type with values 0 and 1 in our MySQL database. This is efficient, but when we want to look at the database using our GUI tools, these values both display as the same meaningless box character. This is because the ASCII values 0 and 1, both represent non-printable characters.
THE SOLUTION: If we force Hibernate to make these columns TINY_INT type, our tools will display the values correctly as “0” and “1”. Hibernate allows us to do this by creating a custom type and assigning it to all of our boolean properties. Here’s how:
1. Create a custom Hibernate type. We extend Hibernate’s abstract BooleanType and override a few methods to customize it for our needs.
OneZeroBoolean.java
package com.example.model;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.hibernate.dialect.Dialect;
import org.hibernate.type.BooleanType;
public class OneZeroBoolean extends BooleanType
{
private static final long serialVersionUID = 1L;
public Object get(ResultSet rs, String name) throws SQLException
{
if (rs.getObject(name) == null)
return null;
int code = rs.getInt(name);
return code != 0;
}
public void set(PreparedStatement st, Object value, int index) throws SQLException
{
if (value == null)
st.setObject(index, null);
else
st.setInt(index, Boolean.TRUE.equals(value) ? 1 : 0);
}
public int sqlType()
{
return Types.TINYINT;
}
public String objectToSQLString(Object value, Dialect dialect) throws Exception
{
return ((Boolean)value).booleanValue() ? "1" : "0";
}
public Object stringToObject(String xml) throws Exception
{
if ("0".equals(xml))
{
return Boolean.FALSE;
} else
{
return Boolean.TRUE;
}
}
}
2. Register this new type with Hibernate. We are using annotations for our hibernate configuration, so this is done in the package-info.java file in the package where our entity classes are defined. Here register our new type under the name “onezero-boolean”. You can use any name you like (unless it’s already used by hibernate). We will reference this name later when we declare the boolean properties themselves.
package-info.java
@TypeDefs( { @TypeDef(name = "onezero-boolean", typeClass = OneZeroBoolean.class) })
package com.example.model;
import org.hibernate.annotations.TypeDefs;
import org.hibernate.annotations.TypeDef;
NOTE: In order to use these package-level annotations we need to tell the Hibernate configuration to look for them. Your configuration may be in hibernate.cfg.xml or you may be doing it in Spring. Here are examples from both…
Hibernate.cfg.xml: Add a mapping within <session-factory> telling hibernate to look for package-level annotations in the specified package.
hibernate.cfg.xml
<hibernate-configuration>
<session-factory>
...
<mapping package="com.example.model" />
...
Spring: Spring provides some helpers to configure a hibernate session factory as an alternative to specifying everything in hibernate.cfg.xml. In Spring 2.0 it’s the org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean class. Here is a snippet of the configuration including the extra piece needed for package annotations:
applicationContext.xml
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
</props>
</property>
<!-- This property tells it to look at our package-level annotations -->
<property name="annotatedPackages">
<list>
<value>com.example.model</value>
</list>
</property>
<property name="annotatedClasses">
<list>
<value>com.example.model.Entity1</value>
<value>com.example.model.Entity2</value>
...
</list>
</property>
</bean>
3. Where ever boolean or Boolean type properties are declared in the entity classes, tell hibernate to use our new custom type. This is done with the hibernate @Type annotation; just specify the name that we used to register our custom type above.
Entity1.java
package com.example.model;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import org.hibernate.annotations.Type;
@Entity
public class Entity1
{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
@Type(type="onezero-boolean")
private boolean active;
//Getters and Setters
...
}
That’s all there is to it. This same type can be applied to any and/or all boolean properties in our entities. Hibernate schema generation will generate TINY_INT columns for us and hibernate persistence will store boolean values as 1(true) and 0(false) in those columns.
Using TINY_INT for Java Boolean types with Hibernate
>>We are using Spring 2.0 and Hibernate 3.2 with a MySQL 5 database.
THE PROBLEM: By default Hibernate persists properties that are of Java Boolean or boolean type as CHAR type with values 0 and 1 in our MySQL database. This is efficient, but when we want to look at the database using our GUI tools, these values both display as the same meaningless box character. This is because the ASCII values 0 and 1, both represent non-printable characters.
THE SOLUTION: If we force Hibernate to make these columns TINY_INT type, our tools will display the values correctly as “0” and “1”. Hibernate allows us to do this by creating a custom type and assigning it to all of our boolean properties. Here’s how:
1. Create a custom Hibernate type. We extend Hibernate’s abstract BooleanType and override a few methods to customize it for our needs.
OneZeroBoolean.java
package com.example.model;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.hibernate.dialect.Dialect;
import org.hibernate.type.BooleanType;
public class OneZeroBoolean extends BooleanType
{
private static final long serialVersionUID = 1L;
public Object get(ResultSet rs, String name) throws SQLException
{
if (rs.getObject(name) == null)
return null;
int code = rs.getInt(name);
return code != 0;
}
public void set(PreparedStatement st, Object value, int index) throws SQLException
{
if (value == null)
st.setObject(index, null);
else
st.setInt(index, Boolean.TRUE.equals(value) ? 1 : 0);
}
public int sqlType()
{
return Types.TINYINT;
}
public String objectToSQLString(Object value, Dialect dialect) throws Exception
{
return ((Boolean)value).booleanValue() ? "1" : "0";
}
public Object stringToObject(String xml) throws Exception
{
if ("0".equals(xml))
{
return Boolean.FALSE;
} else
{
return Boolean.TRUE;
}
}
}
2. Register this new type with Hibernate. We are using annotations for our hibernate configuration, so this is done in the package-info.java file in the package where our entity classes are defined. Here register our new type under the name “onezero-boolean”. You can use any name you like (unless it’s already used by hibernate). We will reference this name later when we declare the boolean properties themselves.
package-info.java
@TypeDefs( { @TypeDef(name = "onezero-boolean", typeClass = OneZeroBoolean.class) })
package com.example.model;
import org.hibernate.annotations.TypeDefs;
import org.hibernate.annotations.TypeDef;
NOTE: In order to use these package-level annotations we need to tell the Hibernate configuration to look for them. Your configuration may be in hibernate.cfg.xml or you may be doing it in Spring. Here are examples from both…
Hibernate.cfg.xml: Add a mapping within <session-factory> telling hibernate to look for package-level annotations in the specified package.
hibernate.cfg.xml
<hibernate-configuration>
<session-factory>
...
<mapping package="com.example.model" />
...
Spring: Spring provides some helpers to configure a hibernate session factory as an alternative to specifying everything in hibernate.cfg.xml. In Spring 2.0 it’s the org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean class. Here is a snippet of the configuration including the extra piece needed for package annotations:
applicationContext.xml
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
</props>
</property>
<!-- This property tells it to look at our package-level annotations -->
<property name="annotatedPackages">
<list>
<value>com.example.model</value>
</list>
</property>
<property name="annotatedClasses">
<list>
<value>com.example.model.Entity1</value>
<value>com.example.model.Entity2</value>
...
</list>
</property>
</bean>
3. Where ever boolean or Boolean type properties are declared in the entity classes, tell hibernate to use our new custom type. This is done with the hibernate @Type annotation; just specify the name that we used to register our custom type above.
Entity1.java
package com.example.model;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import org.hibernate.annotations.Type;
@Entity
public class Entity1
{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
@Type(type="onezero-boolean")
private boolean active;
//Getters and Setters
...
}
That’s all there is to it. This same type can be applied to any and/or all boolean properties in our entities. Hibernate schema generation will generate TINY_INT columns for us and hibernate persistence will store boolean values as 1(true) and 0(false) in those columns.
Hibernate: limit result number of using Criteria
To limit the number of records/results returned by the search criteria(notice we are not using DetachedCriteria):
Criteria criteria = getSession().createCriteria(MyClass.class);
criteria.setMaxResults(100);
List< MyClass > list = criteria.list();
if (list != null && list.size() > 0)
{
log.warn("Found " + list.size() + " Impressions");
}
return list;
Hibernate: limit result number of using Criteria
To limit the number of records/results returned by the search criteria(notice we are not using DetachedCriteria):
Criteria criteria = getSession().createCriteria(MyClass.class);
criteria.setMaxResults(100);
List< MyClass > list = criteria.list();
if (list != null && list.size() > 0)
{
log.warn("Found " + list.size() + " Impressions");
}
return list;
Gilead: PersistentRemoteService
[WARN] StandardContext[]Exception while dispatching incoming RPC call
java.lang.NullPointerException: null
at net.sf.gilead.core.PersistentBeanManager.mergePojo(PersistentBeanManager.java:423)
at net.sf.gilead.core.PersistentBeanManager.merge(PersistentBeanManager.java:289)
at net.sf.gilead.gwt.GileadRPCHelper.parseInputParameters(GileadRPCHelper.java:89)
at net.sf.gilead.gwt.PersistentRemoteService.processCall(PersistentRemoteService.java:147)
at com.google.gwt.user.server.rpc.RemoteServiceServlet.doPost(RemoteServiceServlet.java:86)
So if you don't use spring to create your PersistentRemoteServices, then you have to include these 2 lines in the constructor of all your services.
XmlBeanFactory application = ApplicationContextFactory.getXmlBeanFactoryInstance();
setBeanManager((PersistentBeanManager) application.getBean("hibernateBeanManager"));
Gilead: PersistentRemoteService
[WARN] StandardContext[]Exception while dispatching incoming RPC call
java.lang.NullPointerException: null
at net.sf.gilead.core.PersistentBeanManager.mergePojo(PersistentBeanManager.java:423)
at net.sf.gilead.core.PersistentBeanManager.merge(PersistentBeanManager.java:289)
at net.sf.gilead.gwt.GileadRPCHelper.parseInputParameters(GileadRPCHelper.java:89)
at net.sf.gilead.gwt.PersistentRemoteService.processCall(PersistentRemoteService.java:147)
at com.google.gwt.user.server.rpc.RemoteServiceServlet.doPost(RemoteServiceServlet.java:86)
So if you don't use spring to create your PersistentRemoteServices, then you have to include these 2 lines in the constructor of all your services.
XmlBeanFactory application = ApplicationContextFactory.getXmlBeanFactoryInstance();
setBeanManager((PersistentBeanManager) application.getBean("hibernateBeanManager"));
GILEAD replaces Hibernate4GWT
http://noon.gilead.free.fr/gilead/index.php?page=f-a-q
1) Download ZIP:
http://sourceforge.net/project/showfiles.php?group_id=239931&package_id=291834&release_id=639455
Import jar files into Maven2 repo:
mvn install:install-file -DgroupId=net.sf.gilead -DartifactId=adapter-core -Dversion=1.2.0.29 -Dpackaging=jar -Dfile=*download location*/gilead-1.2.0.29/dist/adapter-core-1.2.0.29.jar
mvn install:install-file -DgroupId=net.sf.gilead -DartifactId=adapter4gwt -Dversion=1.2.0.29 -Dpackaging=jar -Dfile=*download location*/gilead-1.2.0.29/dist/adapter4gwt-1.2.0.29.jar
mvn install:install-file -DgroupId=net.sf.gilead -DartifactId=hibernate-util -Dversion=1.2.0.29 -Dpackaging=jar -Dfile=*download location*/gilead-1.2.0.29/dist/hibernate-util-1.2.0.29.jar
mvn install:install-file -DgroupId=net.sf.beanlib -DartifactId=beanlib -Dversion=3.3.0beta21 -Dpackaging=jar -Dfile=*download location*/gilead-1.2.0.29/adapter-core/lib/beanlib-3.3.0beta21b.jar
mvn install:install-file -DgroupId=net.sf.beanlib -DartifactId=beanlib-hibernate -Dversion=3.3.0beta21 -Dpackaging=jar -Dfile=*download location*/gilead-1.2.0.29/adapter-core/lib/beanlib-hibernate-3.3.0beta21b.jar
2) replace in XYZ.gwt.xml
<inherits name="net.sf.hibernate4gwt.Hibernate4Gwt15" />
with
<inherits name="net.sf.gilead.Adapter4Gwt15" />
3) replace LazyPojo with LightEntity
import net.sf.hibernate4gwt.pojo.java5.LazyPojo;
public class BaseDTO extends LazyPojo
with
import net.sf.gilead.pojo.java5.LightEntity;
public class BaseDTO extends LightEntity
4) replace
import net.sf.hibernate4gwt.core.HibernateBeanManager;
import net.sf.hibernate4gwt.gwt.HibernateRemoteService;
public class SomeClassImpl extends HibernateRemoteService
with
import net.sf.gilead.core.PersistentBeanManager;
import net.sf.gilead.gwt.PersistentRemoteService;
public class SomeClassImpl extends PersistentRemoteService
5) change how you get the beans:
ApplicationContextFactory application = ApplicationContextFactory.getInstance();
setBeanManager((PersistentBeanManager) application.getBean("hibernateBeanManager"));
addressDao = (AddressDao) application.getBean("addressDao");
It also seems like there were some issues with merging arrays that was
fixed in the 1.1.1 version of hibernate4gwt http://hibernate4gwt.sourceforge.net/news.html
We have had some issues with merging objects that contain Lists or
Sets of other objects and getting classcastexception errors. hopefully
going to the new version will fix these issues.
GILEAD replaces Hibernate4GWT
http://noon.gilead.free.fr/gilead/index.php?page=f-a-q
1) Download ZIP:
http://sourceforge.net/project/showfiles.php?group_id=239931&package_id=291834&release_id=639455
Import jar files into Maven2 repo:
mvn install:install-file -DgroupId=net.sf.gilead -DartifactId=adapter-core -Dversion=1.2.0.29 -Dpackaging=jar -Dfile=*download location*/gilead-1.2.0.29/dist/adapter-core-1.2.0.29.jar
mvn install:install-file -DgroupId=net.sf.gilead -DartifactId=adapter4gwt -Dversion=1.2.0.29 -Dpackaging=jar -Dfile=*download location*/gilead-1.2.0.29/dist/adapter4gwt-1.2.0.29.jar
mvn install:install-file -DgroupId=net.sf.gilead -DartifactId=hibernate-util -Dversion=1.2.0.29 -Dpackaging=jar -Dfile=*download location*/gilead-1.2.0.29/dist/hibernate-util-1.2.0.29.jar
mvn install:install-file -DgroupId=net.sf.beanlib -DartifactId=beanlib -Dversion=3.3.0beta21 -Dpackaging=jar -Dfile=*download location*/gilead-1.2.0.29/adapter-core/lib/beanlib-3.3.0beta21b.jar
mvn install:install-file -DgroupId=net.sf.beanlib -DartifactId=beanlib-hibernate -Dversion=3.3.0beta21 -Dpackaging=jar -Dfile=*download location*/gilead-1.2.0.29/adapter-core/lib/beanlib-hibernate-3.3.0beta21b.jar
2) replace in XYZ.gwt.xml
<inherits name="net.sf.hibernate4gwt.Hibernate4Gwt15" />
with
<inherits name="net.sf.gilead.Adapter4Gwt15" />
3) replace LazyPojo with LightEntity
import net.sf.hibernate4gwt.pojo.java5.LazyPojo;
public class BaseDTO extends LazyPojo
with
import net.sf.gilead.pojo.java5.LightEntity;
public class BaseDTO extends LightEntity
4) replace
import net.sf.hibernate4gwt.core.HibernateBeanManager;
import net.sf.hibernate4gwt.gwt.HibernateRemoteService;
public class SomeClassImpl extends HibernateRemoteService
with
import net.sf.gilead.core.PersistentBeanManager;
import net.sf.gilead.gwt.PersistentRemoteService;
public class SomeClassImpl extends PersistentRemoteService
5) change how you get the beans:
ApplicationContextFactory application = ApplicationContextFactory.getInstance();
setBeanManager((PersistentBeanManager) application.getBean("hibernateBeanManager"));
addressDao = (AddressDao) application.getBean("addressDao");
It also seems like there were some issues with merging arrays that was
fixed in the 1.1.1 version of hibernate4gwt http://hibernate4gwt.sourceforge.net/news.html
We have had some issues with merging objects that contain Lists or
Sets of other objects and getting classcastexception errors. hopefully
going to the new version will fix these issues.
Hibernate Detached criteria with projections (GROUP BY)
public List
fetchDetailMetrics(Date dateFrom, Date dateTo, String navPage, String navOption, OrganizationDTO org) {
List
pageViews = new ArrayList (); log.warn("Date from " + dateFrom + " to " + dateTo);
DetachedCriteria metrics = DetachedCriteria.forClass(MetricsForUserSession.class);
metrics.add(Expression.between("dateTime", dateFrom, dateTo));
metrics.add(Expression.eq("navOption", navOption));
metrics.add(Expression.eq("navPage", navPage));
if (org != null)
{
log.warn(" org " + org.getName());
metrics.add(Expression.eq("organization.id", org.getId()));
}
ProjectionList projectList = Projections.projectionList();
// group by
projectList.add(Projections.groupProperty("entityId"));
// alias of the column head
projectList.add(Projections.alias(Projections.rowCount(), "count"));
metrics.setProjection(projectList);
// order by, sorting
metrics.addOrder(Order.desc("count"));
List
if (results == null || results.size() <>
log.warn("fetched nothing");
else
log.warn("fetched " + results.size());
log.warn("fetched navPages " + results.size());
for (Object[] column : results)
{
log.warn(column[0] + " " + column[1]);
PageView pageView = new PageView();
determineDescription(pageView, column, navPage);
pageView.setViewCount(new Integer(column[1].toString()));
pageViews.add(pageView);
}
return pageViews;
}
Hibernate Detached criteria with projections (GROUP BY)
public List
fetchDetailMetrics(Date dateFrom, Date dateTo, String navPage, String navOption, OrganizationDTO org) {
List
pageViews = new ArrayList (); log.warn("Date from " + dateFrom + " to " + dateTo);
DetachedCriteria metrics = DetachedCriteria.forClass(MetricsForUserSession.class);
metrics.add(Expression.between("dateTime", dateFrom, dateTo));
metrics.add(Expression.eq("navOption", navOption));
metrics.add(Expression.eq("navPage", navPage));
if (org != null)
{
log.warn(" org " + org.getName());
metrics.add(Expression.eq("organization.id", org.getId()));
}
ProjectionList projectList = Projections.projectionList();
// group by
projectList.add(Projections.groupProperty("entityId"));
// alias of the column head
projectList.add(Projections.alias(Projections.rowCount(), "count"));
metrics.setProjection(projectList);
// order by, sorting
metrics.addOrder(Order.desc("count"));
List
if (results == null || results.size() <>
log.warn("fetched nothing");
else
log.warn("fetched " + results.size());
log.warn("fetched navPages " + results.size());
for (Object[] column : results)
{
log.warn(column[0] + " " + column[1]);
PageView pageView = new PageView();
determineDescription(pageView, column, navPage);
pageView.setViewCount(new Integer(column[1].toString()));
pageViews.add(pageView);
}
return pageViews;
}
Hibernate: disjunction
Junction junction = Expression.disjunction();
junction.add(Expression.or(Expression.eq("plurality", phrase.getPlurality()), Expression.eq("plurality", Constants.PHRASE_NEUTRAL)));
dcPhrase.add(junction);
Hibernate: disjunction
Junction junction = Expression.disjunction();
junction.add(Expression.or(Expression.eq("plurality", phrase.getPlurality()), Expression.eq("plurality", Constants.PHRASE_NEUTRAL)));
dcPhrase.add(junction);
Hibernate: detached criteria
DetachedCriteria dcSport = DetachedCriteria.forClass(PhraseSport.class);
dcSport.add(Expression.eq("sport.id", phrase.getSportId()));
List
sports = hibernateList(dcSport);
Hibernate: detached criteria
DetachedCriteria dcSport = DetachedCriteria.forClass(PhraseSport.class);
dcSport.add(Expression.eq("sport.id", phrase.getSportId()));
List
sports = hibernateList(dcSport);
Hibernate equals(); hashCode(); toString() methods
Soul searching
For the last 4 months, I've been creating a social network application CommunitySportsDesk.com in GWT with very good results.
Still, the question remains, what's next?
The rest of the company is looking at Flex3.
I am missing rich application functionality in HTML/CSS/JS GWT, but I cherish Java tools.
Then, again I realize I have been writing this and reading the Web the whole morning on iPhone, with my Mac book pro stuffed in the backpack next to me - laptops become simply obsolete for everyday users.
Sun is writing the JVM for iPhone and if Apple allows it, we can write apps in Java.
Objective-C may be powerful but is verbose, ugly, and does not have as much community support as Java, I am talking about frameworks, not the language itself.
I will wait for iPhone Java patiently while trying to warm myself to Objective-C.
Like me, people will want to use the fully-featured (Web) applications on their mobile devices.