Subscribe for updates on posts
Be the first to read the latest news

Spring+JPA with MySQL/Oracle configurations

September 6th, 2010 by Ioan Cocan in Java, General, Spring Framework

Spring Framework

During development for a solution based on Spring+JPA+MySQL/Oracle we came across two settings to watch for.

1. Database isolation level

The default isolation level in MySQL is REPEATABLE READ, different from the default in Oracle (READ_COMMITTED). It seems this is kept for historical reasons related to the replication. Still, the effect is that for example a database query will return the same result, no matter if some other transaction has meanwhile changed the database.

For checking the current MySQL isolation level: SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

For changing the isolation level add: “transaction-isolation = REPEATABLE-READ” to you MySQL configuration.

2. JPA transaction configuration

For those accustomed to Hibernate default configuration, the default JpaTransactionManager comes with a different default for behavior related to transaction configuration. The “globalRollbackOnParticipationFailure” is set to true, that means a failed transaction will trigger a global rollback. If you want to change that and be the decision maker if the global transaction should be rolled back or not, set globalRollbackOnParticipationFailure to false.


How to order by a custom SQL formula/expression when using hibernate Criteria API

June 10th, 2008 by spostelnicu in Java, General

In our current project we are using Spring + Hibernate Annotations.
Today I needed to use something like:

1
2
3
SELECT DISTINCT t.id FROM MyClass t
 WHERE .....
 ORDER BY (a + b) DESC

where a and b are properties of MyClass (columns in the “my_class” table).

The “where …” expression must be generated dynamically based on the user input, so we are using Criteria API to generate the query as such:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Criteria criteria = getSession().createCriteria(MyClass.class);
criteria.setProjection(Projections.distinct(Projections.id()));
 
// Some custom dynamic conditions
criteria.add(Restrictions.gt("createdDate", afterDate));
criteria.add(Restrictions.in("state", approvedStates));
criteria.add(Restrictions.isNull("deletedDate"));
if (includedCategories != null || excludedCategories != null) {
    Criteria categoryCriteria = criteria.createCriteria("category");
    if (includedCategories != null) {
        for (String categoryPrefix : includedCategories) {
            categoryCriteria.add(Restrictions.like("path", categoryPrefix + "%"));
        }
    }
    if (excludedCategories != null) {
        for (String categoryPrefix : excludedCategories) {
            categoryCriteria.add(Restrictions.not(Restrictions.like("path", categoryPrefix + "%")));
        }
    }
}
 
criteria.add(Restrictions.sqlRestriction("(a + b) > 1"));
 
// Custom ordering by some SQL formula/expression
criteria.addOrder(Order.desc("a + b"));
 
return criteria.list();

Now, the problem is that the class Order does not support custom SQL formula/expression…

So my solution was to derive my own class as such:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
package ro.tremend.util.hibernate;
 
import org.hibernate.criterion.Order;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
 
/**
 * Extends {@link org.hibernate.criterion.Order} to allow ordering by an SQL formula passed by the user.
 * Is simply appends the <code>sqlFormula</code> passed by the user to the resulting SQL query, without any verification.
 * @author Sorin Postelnicu
 * @since Jun 10, 2008
 */
public class OrderBySqlFormula extends Order {
    private String sqlFormula;
 
    /**
     * Constructor for Order.
     * @param sqlFormula an SQL formula that will be appended to the resulting SQL query
     */
    protected OrderBySqlFormula(String sqlFormula) {
        super(sqlFormula, true);
        this.sqlFormula = sqlFormula;
    }
 
    public String toString() {
        return sqlFormula;
    }
 
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return sqlFormula;
    }
 
    /**
     * Custom order
     *
     * @param sqlFormula an SQL formula that will be appended to the resulting SQL query
     * @return Order
     */
    public static Order sqlFormula(String sqlFormula) {
        return new OrderBySqlFormula(sqlFormula);
    }
}

Now, to use the custom ordering, I included the following line:

1
        criteria.addOrder(OrderBySqlFormula.sqlFormula("(a + b) desc"));