Category Archives: Hibernate

MySql connections autodrop after a certain hours

MySql is configured to drop any connection which has been Idle for more than 8 hours. What is the implication of this? After you return to your deployed app after a gap of 8 hours (If default SQL parameters have not been changed), you will be greeted with an exception.

How to solve this issue?

  1. Increase the wait_time parameter -Not a good Idea, it might unnecessarily hold on to the resources and not might be a sure shot way. Apart from that, being dependent on an “external” configuration for failover is not a very good idea -what if the server itself crashes, what if this configuration is missed in one of the instnaces, and many such issues will pop up against this approach.
  2. Use the parameter autoReconnect=true with JDBC URL -My SQl itself does not recommend this, have a look at link and people have reported that this does not work as well, refer link.
  3. Custom handling -have your code identify that connection has been lost and then recover it and try to reconnect, but then it would be a lot of fail over mechanism in code.
  4. The best way I found was to configure pooling mechanism as c3p0. See this post how to configure c3p0 in JPA for hibernate, it’s simple, easy and reliable.

So how do you test that issue is solved?

  1. Change wait_timeout in MySql to just 2 minutes, this is how it can be done from MySql workbench admin console mysql_timeout
  2. Keep value of idleTestPeriod less than wait_timeout -A quick recap what idleTestPeriod signifies
  3. idleTestPeriod:  default value=0; If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds
  4. Login after wait_timeout has passed -it should not throw a exception

Solving classloading issue while adding pooling support using c3p0 in JPA with hibernate underneath

I added c3p0 for pooling in JPA, but encountered an exception

Unable to load class [org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider]

My configuration looked like

<property name="hibernate.connection.provider_class"
          value="org.hibernate.connection.C3P0ConnectionProvider" />
<property name="hibernate.c3p0.max_size" value="10" />
<property name="hibernate.c3p0.min_size" value="0" />
<property name="hibernate.c3p0.acquire_increment" value="5" />
<property name="hibernate.c3p0.idle_test_period" value="60" />
<property name="hibernate.c3p0.max_statements" value="0" />
<property name="hibernate.c3p0.timeout" value="100" />	    

Details about these properties and other defined at link

Looking at log trace it’s not difficult to figure out that jar is not correct, so first change, upgrade to latest c3p0 artifact.





After changing this, it worked but printed an Warning

WARN - HHH000208: org.hibernate.connection.C3P0ConnectionProvider has been deprecated in favor of org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider; that provider will be used instead.

This indicates that provider_class should be changed to remove the depricated class, so it should be

<property name="hibernate.connection.provider_class"
          value="org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider" />

This cleanly integrated the c3p0 implementation.

How to log query parameters passed to JPA queries

I am using JPA with hibernate underneath. Using the property

<property name="hibernate.show_sql" value="true"/>

Shows just the SQL and not the values of parameters passed -it displays ? marks for query parameter as incomeexpe0_.transactiondate)>=?
Want to know what was the exact parameters passed -This is real helpful for debugging.
This can be achieved using hibernate logging -Add following two lines in your file

If this is used “” -No need to use property hibernate.show_sql, it wil take care of dumping queries.
The secondstatement, “”, logs the JDBC parameters passed to a query

TRACE [main] ( - binding parameter [1] as [TIMESTAMP] - Thu Jan 03 01:00:18 IST 2013
TRACE [main] ( - binding parameter [2] as [TIMESTAMP] - Thu Jan 03 23:00:19 IST 2013

For even more advanced analysis and precise JDBC formatted queries (Non in an ORM form, but REAL sql), jdbc proxy driver like P6Spy can be used.

Data truncation exception while joining one to one in JPA

Lets say a class is having one to one relation ship with other entity, it looks like

public class StudentDetails implements Serializable{
    @OneToOne(cascade={CascadeType.REFRESH,CascadeType.MERGE},fetch = FetchType.EAGER) 
    private ClassDetails classId;

When I tried to save rows in the table, it threw an exception

javax.persistence.PersistenceException: org.hibernate.exception.DataException: Data truncation: Data too long for column 'classid' at row 1
	at org.hibernate.ejb.AbstractEntityManagerImpl.convert(
	at org.hibernate.ejb.AbstractEntityManagerImpl.convert(
	at org.hibernate.ejb.AbstractEntityManagerImpl.convert(

What went wrong? When I looked at generated table the filed type for classid was LOB while I expected it to be an int (It just an ID from foreign key). The culprit is -using column instead of joincolumn

public class StudentDetails implements Serializable{
    @OneToOne(cascade={CascadeType.REFRESH,CascadeType.MERGE},fetch = FetchType.EAGER) 
    private ClassDetails classId;

Instead of column, it should be join column

    @OneToOne(cascade={CascadeType.REFRESH,CascadeType.MERGE},fetch = FetchType.EAGER) 
    @JoinColumn(name="classid",nullable=false) <strong> See the joincolumn </strong> 
	private ClassDetails classId;}

If join column is not specified, JPA assumes that ClassDetails object will be saved (along with its all nested graph) to DB and hence it creates a LOB type -telling it that its a joined foreign key resulted in a int data type for the field.

Persisting a detached entity in JPA

In a JPA many to many relationship, if cascade type has been set at CascadeType.PERSIST (or CascadeType.ALL, which includes CascadeType.PERSIST), then while saving the parent and updating it with references of the child, it will try to save the child again.

	@ManyToMany(cascade={CascadeType.PERSIST,CascadeType.MERGE,CascadeType.REFRESH},fetch = FetchType.EAGER)
	joinColumns={@JoinColumn(name="questionpaperid")}, inverseJoinColumns=@JoinColumn(name="questionid"))

Following issues can appear

  1. Child is already in persistence store (A detached instance has been passed) -in this case it will throw an exception “org.hibernate.PersistentObjectException: detached entity passed to persist”
  2. Child is a new entry, then it will be added in its table and operation will complete successfully

Following are some solutions

  1. If requirement is that Parent should add a existing but detached child(creating an instance with same ID and passing on from the client) -it needs to be reattached to session first. Hmm -for this there is NO way in JPA -Nope, merge does not reattach and entity. If you are using hibernate, you are lucky, merge can do the trick
    For JPA, best option would be to query for entity on server side before trying to save it.
  2. If its sure that only new child will be added, and not an detached instance from DB, CascadeType.PERSIST will take care of
  3. On the other hand, if requirement is never to add a new child if its not alredy in DB then CascadeType.PERSIST should be removed and cascade={CascadeType.MERGE,CascadeType.REFRESH} should be used
	@ManyToMany(cascade={CascadeType.MERGE,CascadeType.REFRESH},fetch = FetchType.EAGER)
	joinColumns={@JoinColumn(name="questionpaperid")}, inverseJoinColumns=@JoinColumn(name="questionid"))

Fetching Nested collections in JPA

While coding in JPA with hibernate underneath I got an exception org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags. I was using “LIST” for the association and fetching eagerly

	@ManyToMany(cascade={CascadeType.MERGE,CascadeType.REFRESH},fetch = FetchType.EAGER)
	joinColumns={@JoinColumn(name="classid")}, inverseJoinColumns=@JoinColumn(name="subjectid"))
	private List<SubjectDetails> subjects;

The problem seems to be two fold:

  1. JPA allows only two level of nesting for collection, Parententity->Childentity is fine but it does not allow Parententity->childentity->SubChildEntity
  2. Using List for collection instead of SET -remember a SET guarantees unique elements while list does not

What could be solution:

  1. Use SET instead of List and thats what I have done for the moment
	private SET<SubjectDetails> subjects;

  • Hibernate does allow, so we can use specific annotation but it will defy the whole purpose of using JPA
  • Review the model, instead of depending on Object graph to be fetched “Automagically” by ORM, can we move the logic in service layer and do the population as on need basis for required UI? And if you say it would throw lazy initialization because you have used session at DAO -Red Flag -its an anti pattern
  • Using in hibernate

    What does this tag do :

    It tells hibernate to automatically sync up the table structure –Pojo to DB layer schema. There are couple of options which can go here:

    validate: validate the schema, makes no changes to the database.
    update: update the schema.
    create: creates the schema, destroying previous data.
    create-drop: drop the schema at the end of the session.

    I suggest not to use this tag even for development, using it in production is any way nothing less than sin.

    In a stressed environment (as it happened with me today), one might forget that this small tag is doing some magic and “automatically” changing the DB structure/deleting/ records et all.

    1. I was running a test (DB layer test) for validating a schema change –and it was supposed to fail, because SQL has not been run.
    2. Instead all the tests were passing, see even great news sometime smell bad.
    3. A couple of tests later, When I verified the DB schema I saw all the DB “Automatically” updated with new schema.
    4. As I had earlier worked on hibernate, It clicked and I verified the config file and got the answer to mystery.
    5. Same would not have happened if I did not had experience in ORM, or at least it would have taken some time/help.