CODEWITHSUNDEEP
javapostgresqlhibernateload-balancingpgpool
computeiro
computeiro

Reputation: 603

No load balance comment in Java Hibernate Query

We are using pgPool in our Java project to do load balancing in our Postgres database.

pgPool sends read only queries to slave servers and write queries to the master. That is ok.

But there are very specific cases in our application when we need the query to be executed in master server only.

pgPool provides the following mechanism:

/*NO LOAD BALANCE*/ SELECT * FROM user;

The query above will always be executed in the master server.

I searched a lot but while it is possible to set a comment in a manual query (there is a setComment() in Query class), I couldn't find a way to do that using a Hibernate repository with queries created from method names.

Example:

public interface UserRepository extends Repository<User, Long> {
    List<User> findByEmailAddressAndLastname(String emailAddress, String lastname);
}

How to put the comment in this query?

Upvotes: 3

Views: 1160

Answers (2)

Eager
Eager

Reputation: 1691

I had to do the same thing in my project where I use Spring Data and Hibernate. I overrided the org.hibernate.dialect.Dialect#addSqlHintOrComment method and added custom wrapper for Hibernate's @QueryHint. The full implementation and related details can be found here

Upvotes: 0

computeiro
computeiro

Reputation: 603

Well, this not answers the original question, but I've solved the problem using a different approach.

I've found the solution by reading this section of pgPool documentation: http://www.pgpool.net/docs/latest/pgpool-en.html#condition_for_load_balance

So, annotating a Java method or class with @Transactional achieves the desired result of routing the queries to master server.

When using this annotation, pgPool will route all queries made after a WRITE query (including) to the Postgres master server.

If you don't have a WRITE query, you can achieve the result by annotating with @Transactional(isolation=Isolation.SERIALIZABLE) - if using Spring. But be aware that this isolation level is the strictest available.

Upvotes: 0

Related Questions