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;
@ReadOnly
@Interceptor
public class JpaReadOnlyInterceptor implements Serializable {
private EntityManager entityManager;
@PersistenceContext
protected void setEntityManagerInjection(EntityManager entityManager) {
this.entityManager = entityManager;
}
@AroundInvoke
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 {
connection.setReadOnly(true);
// Only in the case where the autocommit is enabled
//connection.setAutoCommit(false);
return context.proceed();
} finally {
// restore state
// Only in the case where the autocommit is enabled
//connection.setAutoCommit(autoCommit);
connection.setReadOnly(readOnly);
}
}
}
@Inherited
@InterceptorBinding
@Target({ ElementType.TYPE, ElementType.METHOD })
@Retention(RetentionPolicy.RUNTIME)
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:
@ReadOnly
public DataResult readData(String id) {
...
Otherwise you have to use the “legacy” Ejb interceptor:
@Interceptors(JpaReadOnlyInterceptor.class)
public DataResult readData(String id) {
...
References:
