Pagination with Hibernate and MySQL

When you have a large of records in the table database, you need to paging it to avoid load all data records with slow speed. Someone's mistake about pagination in Hibernate with MySQL by using MySQL query normally.

Example below is used to paging by MySQl Query. this query will select first record and limit select to 5 records:

SELECT * FROM table LIMIT 0,5 

And in hibernate if we use this query by normal like below..

public List paging() {
    try {
       String queryString = "from table LIMIT 0,5";
       Query queryObject = getSession().createQuery(queryString);
       return queryObject.list();
    } catch (RuntimeException re) {
       throw re;
    }
}

It's wrong. And it must be:

public List paging() {
    try {
       String queryString = "from table";
       Query queryObject = getSession().createQuery(queryString);
       queryObject.setFirstResult(0);
       queryObject.setMaxResults(5);
       return queryObject.list();
    } catch (RuntimeException re) {
       throw re;
    }
}

setFirstResult(...) is used to select the record number, and setMaxResult(...) is used to select number of records.
Dont be mistake about sql query and Query Object when hibernating with MySQL.

Common Hibernate Errors and Causes

This entry deals with some problems when do a work with Hibernate, you will get some errors which you can't understand or dont know causes of that errors. Along with the exception or error messages themselves, potential causes of these errors are often listed along with links to additional resources.
To find the error you're looking for, use your browser's Find or Search capability and input a few words that you are seeing in your error message.



1. org.hibernate.hql.ast.QuerySyntaxException: unexpected end of subtree HQL query...
A complete exception would look like:

2007-06-06 10:14:02,953 [DefaultQuartzScheduler_Worker-4] 
  ERROR PARSER::reportError - <AST>:0:0: unexpected end of subtree
  org.hibernate.hql.ast.QuerySyntaxException: unexpected end of subtree [QUERY]
    at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
    at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:225)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
...

Probable cause: In the HQL query you have used a collection for a binding (maybe something like an in (:variable), and you have given a collection by using the setParameterList() method. But the collection that was binded is empty.

2. org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list...

A complete exception would look like:

Exception in thread "main" org.hibernate.QueryException: 
query specified join fetching, but the owner of the fetched
association was not present in the select list 
[FromElement{explicit,not a collection join,fetch join,fetch non-lazy
properties,classAlias=j_addressParent3,role=null,tableName=t_address,
tableAlias=address4_,origin=t_person_insurance
insurance3_,colums={insurance3_.ADDRESS_ID ,className=nl.sodeso.demo.impl.Address}}] [QUERY]

Probable cause: You have a HQL query that contains a join with a fetch option but that join has an owner that does not specify the fetch option, make sure that the owner also has the fetch option.

The following is an example of the problem:

01: select person from Person as person
02: left join person.address as address
03: left join fetch address.country as country

Here you can see that the join on line 03 is a child join of the join on line 02,
the join on line 03 specifies the fetch option but the join on line 02 doesn't.
In this case the join on line 02 should be changed to:

02: left join fetch person.address as address

3. exception: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query

Something in your code will be:

String del = "DELETE VotableNode vn WHERE vn.votingSetBucketId = " + Integer.toString(bucketId); 

session.createSQLQuery(del).executeUpdate();

Probable cause: It's saying that you can't create a bulk delete on a sql query by using hibernate.

The error in this code is that you were executing a HQL script using createSQLQuery method which is completely wrong. You should use createQuery:

String del = "DELETE VotableNode vn WHERE vn.votingSetBucketId = " + Integer.toString(bucketId); 

session.createQuery(del).executeUpdate();

So there's nothing much to say, If you want to make a bulk task like an update of multiple records or a deletion of multiple rows using a simple query, you MUST use a HQL query.



4. Initial SessionFactory creation failed.org.hibernate.MappingException: Error reading resource:app1/contact.hbm.xml

Probable causes: Your file xml config is missing in your classpath.

5. org.hibernate.HibernateException: Illegal attempt to associate a collection with two open sessions. 

from imma


Probable causes: It showed up when I attempt to update an element in a collection; adding to the collection was not a problem, hibernate mapping files are ok and everything else seemed to be in order but the error keeps turning up.

Solution: it turned out that I was preserving a value across requests whose presence cause a select statement to be executed before the update hence the exception.

A little context about the exception mentioned: I was using Hibernate, Spring and JSF. I was trying to update the collection using a JSF managed bean that has a request scope. Note that the hibernate data access object is managed by Spring hence is not (in my setup) part of JSF life cycle.

So with the above scenario, the first request attaches the collection to a hibernate session, subsequent attempts to modify the collection threw up the mentioned exception.

Changing the scope of the JSF managed bean seems to solve the issue.

Updated at 10/28/08 by Nguyen, Lam D

What is Hibernate?

Hibernate 

Hibernate is a free, open source Java package that makes it easy to work with relational databases. Hibernate makes it seem as if your database contains plain Java objects like you use every day, without having to worry about how to get them out of (or back into) mysterious database tables. It liberates you to focus on the objects and features of your application, without having to worry about how to store them or find them later.

History and Background

Most applications have some need to work with data. Java applications, when running, tend to encapsulate data as networks of interconnected objects, but those objects vanish in a puff of logic when the program ends, so there needs to be some way to store them. And sometimes the data is already "out there" before the application is even written, so there needs to be a way to read it in and represent it as objects. Writing code by hand to perform these tasks is tedious and error-prone, and can represent a major portion of the effort involved in the overall application.
As good object-oriented developers got tired of this repetitive work, their typical tendency towards enlightened laziness started to manifest itself in the creation of tools to help automate the process. When working with relational databases, the culmination of such efforts were object/relational mapping tools.
There have been a variety of such tools, ranging from expensive commercial offerings to the EJB standards built into J2EE. In many cases, however, the tools introduce their own complexity, force the developer to learn detailed rules for using them, and to modify the classes making up their applications to conform to the needs of the mapping system. As the tools evolved to handle ever more rigorous and complex enterprise requirements, the intricacies required to use them started to overwhelm the savings you could obtain by doing so in simpler, common cases. This has led to something of a revolution favoring more lightweight solutions, of which Hibernate is an example.

How Hibernate Works

Hibernate doesn't get in your way; nor does it force you to change the way your objects behave. They don't need to implement any magical interfaces in order to be blessed with the ability to persist. All you need to do is create an XML "mapping document" telling Hibernate the classes you want to be able to store in a database, and how they relate to the tables and columns in that database, and then you can ask it to fetch data as objects, or store objects as data for you. Compared to most of the alternatives, it's almost magical.
There isn't room in an introductory article like this to work through a concrete example of building and using a Hibernate mapping document (that's what the first couple of chapters in my Hibernate: A Developer's Notebook are for, after all). And there are some good examples already on the Web and in the Hibernate online documentation. It really is straightforward, though. Properties in the application objects are associated with appropriate database structures in a simple, natural way.
At runtime, Hibernate reads the mapping document and dynamically builds Java classes to manage the translation between the database and Java worlds. There is a simple, intuitive API in Hibernate to perform queries against the objects represented by the database. To change those objects you just interact with them normally in the program, and then tell Hibernate to save the changes. Creating new objects is similarly simple; you just create them in the normal way and tell Hibernate about them so they can get stored to the database.
The Hibernate API is simple to learn and interacts quite naturally with the flow of your program. You invoke it in sensible places, and it does what you'd like it to. The benefits it brings in terms of automation and code savings greatly outweigh the short time it takes to learn. And you get an additional bonus in that your code doesn't care (or even have to know) what kind of database you're using. My company has had projects forced to change database vendors late in the development process. This can be a horrible mess, but with Hibernate it has required nothing more than a single change to the Hibernate configuration file.
This discussion has assumed that you already had a relational database set up, as well as Java classes to map, through the creation of the Hibernate mapping document. There is a Hibernate "toolset" that works at build time to support different workflows. For example, if you've got the Java classes and mapping document, Hibernate can create (or update) the necessary database tables for you. Or, starting with just the mapping document, Hibernate can generate the data classes for you, too. Or it can reverse engineer your database and classes to sketch out a mapping document for you. There are also some alpha plugins for Eclipse to provide intelligent editing support and graphical access to these tools right from within the IDE.
If you're in a Hibernate 2 environment, fewer of these tools are provided, but there are third-party options available.

When to Use Hibernate

Given how cool and flexible Hibernate seems, why would you ever use anything else? Here are a some scenarios to help you make that judgment (and perhaps, by providing some contrast and context, they'll help identify situations where Hibernate is the perfect fit).
If your application has very simple data storage needs--for example, you just want to manage a group of user preference choices--you don't need a database at all, let alone a fancy object-relational mapping system (even one as wonderfully easy to use as Hibernate)! Starting with Java 1.4, there is a standard Java Preferences API that fills this role very nicely.
For people who are already comfortable working with relational databases and know how to craft the perfect SQL query to interact with an enterprise database, Hibernate might seem to get in the way, much like a land yacht with power everything and automatic transmission can irritate a performance-oriented race car driver. If this describes you, if you're on a project team that includes a strong DBA, or are given stored procedures to work with, you might want to investigate iBATIS. The authors of Hibernate themselves identify iBATIS as an interesting alternative. I found it intriguing because we developed a similar (though much less powerful) system in-house for an e-commerce site and we've been using it in other contexts ever since, although after discovering Hibernate we usually favor that for new projects. You might consider SQL-centric solutions like iBATIS to be "inverted" object/relational mapping tools, whereas Hibernate is a more traditional ORM.
Of course, there may be other external factors that mandate a different approach. You might have to use a full-blown EJB architecture (or some other non-plain-objects mapping system) to fit in with an enterprise environment. You may be tailoring code for a platform that provides its own data storage tools, like Mac OS X's Core Data. You may have been handed a storage specification like an XML DTD, which doesn't involve relational databases at all.
But if you're working with a rich object model, and want to be able to store it flexibly, easily, and efficiently (whether or not you're starting with or have decided on using a relational database, as long as that's an option--and with good free databases like MySQL or the Java embeddable HSQLDB available, it ought to always be an option), then Hibernate is probably the way to go. You may be amazed at how much time you save, and how much you love working with it.

Learning More

The Hibernate project has extensive online documentation that can help orient you and get you started quickly.
The definitive reference is Hibernate in Action, by Christian Bauer and Gavin King. Written by the creators of Hibernate, it gives you a thorough grounding in what you can do with the package, and how to do it right.
My own book, Hibernate: A Developer's Notebook, is another great way to get started quickly. It provides a direct but detailed walkthrough of how to set up Hibernate in a Java project, and how to use some of its most important features. The code examples are currently based on earlier releases of Hibernate and HSQLDB, so if you want them to work without any tweaks (which is the nicest way to get started), you'll need to work with those versions of the software. The basic concepts remain on target, regardless, and I'm hoping to be able to update the book for Hibernate 3 soon.
Another interesting book is Better Faster Lighter Java, by Bruce Tate and Justin Gehtland. This book's pragmatic approach to getting real projects finished in a sane way is one of the reasons Hibernate spiked in popularity. It gives sound advice on how to evaluate and use (or reject) available Java technologies, and mentions both Hibernate and Spring as examples of the right approach.
Finally, "Working with Hibernate in Eclipse" (though it predates the new alpha Hibernate 3 tools, which are shaping up to be far, far cooler) walks through how to use an Eclipse plugin called Hibernate Synchronizer with Hibernate.

by James Elliott, author of Hibernate: A Developer's Notebook