Home > ejb > MySQL Master/Slave in JEE enviroment

MySQL Master/Slave in JEE enviroment

With a MySQL Master/Slave database we have to  send write operations to the Master server e read only operations to Slave servers with load balancing.

With the MySQL Connector/J we can use this driver “com.mysql.jdbc.ReplicationDriver”: it , automatically, send queries to Master or to Slaves based on the read-only attribute of the connection (Connection.getReadOnly()).

At first we set the connection pool on our favorite JEE server:

 Url: jdbc:mysql:replication://master,slave1,slave2,slave3/test

The driver send the queries to Slaves only if auto-commit is set to false and the connection is read-only, otherwise it use the Master.

If in the EJB transaction, as opposed to Spring, you could set the property read-only, probably the JPA provider would set the same property on the connection. So, always assuming, would be enough: @TransactionAttribute(TransactionAttributeType.XXXXXX,readOnly=true)

Unfortunately at the moment it is not possible, so we have to create our interceptor and our annotation @ReadOnly.

import javax.interceptor.Interceptor;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

public class JpaReadOnlyInterceptor implements Serializable {

private EntityManager entityManager;

 protected void setEntityManagerInjection(EntityManager entityManager) {
 this.entityManager = entityManager;

 public Object setReadOnlyOperation(InvocationContext context) throws Exception {

Connection connection = entityManager.unwrap(java.sql.Connection.class);

// Only in the case where the autocommit is enabled
 //boolean autoCommit = connection.getAutoCommit();
 boolean readOnly = connection.isReadOnly();

try {
 // Only in the case where the autocommit is enabled

return context.proceed();
 } finally {
 // restore state
 // Only in the case where the autocommit is enabled


@Target({ ElementType.TYPE, ElementType.METHOD })
public @interface ReadOnly {}

If your favourite JEE server have a current WELD version, you should be able to use the new interceptor CDI without problems:

public DataResult readData(String id) {

Otherwise you have to use the “legacy” Ejb interceptor:

public DataResult readData(String id) {


  1. Non c'è ancora nessun commento.
  1. No trackbacks yet.


Effettua il login con uno di questi metodi per inviare il tuo commento:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )


Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: