Setup Spring transactions for MySQL Replication

This post describes how to setup Spring transaction to connect to MySQL database with Replication to direct all write operations to the master and read operations to both master and slaves.

Database setup

The easiest way to setup MySQL database with replication for testing is via Amazon AWS. Create a RDS instance with MySQL as a master and then create a Read Replica from the master. Note AWS uses native MySQL replication to propagate database changes from  the master to the slaves.

JDBC connection

To connect to MySQL Replication using JDBC, made the following 2 changes to your spring config:

  1. Replace the jdbc driver class, e.g. com.mysql.jdbc.Driver, with com.mysql.jdbc.ReplicationDriver.
  2. Modify jdbc connection string to format

             jdbc:mysql:replication://<master db url>,<slave 1 db url>/<dbname>

See MySQL documentation here for list of configuration properties that can be appended to the jdbc URL.

Note the ReplicationDriver wraps one read and one write jdbc connection and can be used transparently with jdbc connection pools such as cp30.

Spring transaction

Use the readOnly attribute of the @Transaction annotation of Spring to direct a transaction to either the master or slave.

For write operations, use @Transactional(readOnly = false) and the database operations will go to the master only

For read only operations, use @Transactional(readOnly = true) and the database operations can go to the slave.

Note:

  1. There are a few old articles on the web indicating the readOnly attribute is ignored. This seems to be outdated and the attribute is working as expected in Spring 3. I am using Spring 3.2.

To verify the setup, I have the following Spring test class:

@RunWith(SpringJUnit4ClassRunner.class)
@TransactionConfiguration(transactionManager="transactionManager", defaultRollback=true)
@ContextConfiguration("classpath:spring/app-config-test.xml")
@Transactional(readOnly = true)
@ActiveProfiles(profiles={"aws"})
public class ProductRepositoryImplTest {
@Autowired
 private SessionFactory sessionFactory;
@Autowired
 @Qualifier("productRepository")
 private IProductRepository repository;
// ... setup details omitted here
@Transactional(readOnly = false)
 @Test
 @Repeat(value = 100)
 public void testReplicationWrite() {
      repository.create(createEntity());
 }
 @Transactional(readOnly = true)
 @Test(expected = GenericJDBCException.class)
 public void testReplicationWriteFail() {
      repository.create(createEntity());
 }
 @Transactional(readOnly = true)
 @Test
 @Repeat(value = 100)
 public void testReplicationRead() {
      repository.findProductByName(RandomStringUtils.randomAlphabetic(10));
 }
}

Note:

  1. The test class above tests the productRepository bean which implements standard CRUD operations (details omitted here) for the entity Product.
  2. The first test method testReplicationWrite() will pass as the readOnly attribute of the @Transactional annotation is set to false.
  3. The second test method testReplicationWriteFail() will throw a GenericJDBCException, as expected by the test method. This confirms that the readOnly attribute works by sending the database operation to the slave and hence the exception. If you remove the “expected = GenericJDBCException.class”, the test will fail with the following error: “org.hibernate.exception.GenericJDBCException: Connection is read-only. Queries leading to data modification are not allowed”
  4. You can also verify on the MySQL master and slave by using the “show processlist” command while the tests are running. The @Repeat annotation runs the tests multiple (100) times to keep the connection process running. Of course, you can also enable the query log for this.
Advertisements

A configurable framework for implementing full text search using Hibernate Search

In this blog, I will describe a database driven framework that I implement to perform full text search of Hibernate entities using the Hibernate Search project.

Setup Hibernate Search

Including Hibernate Search in your project is straight forward. Follow the instruction in the documentation here. I add the following  Maven dependencies:

 <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-search</artifactId>
      <version>4.1.1.Final</version>
 </dependency>
 <!-- Additional Analyzers: -->
 <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-search-analyzers</artifactId>
      <version>4.1.1.Final</version>
 </dependency>
 <!-- Infinispan integration: -->
 <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-search-infinispan</artifactId>
      <version>4.1.1.Final</version>
 </dependency>

The framework

Domain layer

The “configurable” part of the framework is built around a SearchPreference class as shown below

@Entity
@Indexed
public class SearchPreference extends AbstractEntity {

@Field(index=Index.YES, analyze=Analyze.NO, store=Store.NO)
private String entityName;

private String propertyName;

@Enumerated(EnumType.STRING)
@Field(index=Index.YES, analyze=Analyze.NO, store=Store.NO)
private SearchType searchType;

private BoolType boolType;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;

@Override
public long getId() {
return id;
}

// rest of codes omitted here

Note:

(1) The class SearchPreference is used to store the full text search config of an indexed property of the target Hibernate entity. It is itself a Hibernate entity for persistence to the database and has the following properties:

  • entityName – fully qualified class name of the target Hibernate entity
  • propertyName – name of an indexed property of the target Hibernate entity
  • searchType – an enum to define the type of search for this field, i.e. match, wildcard, phase
  • boolType – an enum to define the mode of aggregation of the subquery (must, should)

(2) The class AbstractEntity is an abstract entity class to be extended by all Hibernate entity classes.

(3)  A Hibernate entity is associated with multiple SearchPreference objects for full text search across multiple fields.

Repository layer

The repo layer consists of the following interfaces:

public interface ISearchPreferenceRepository extends IEntityRepository<SearchPreference> {

List<SearchPreference> getSearchFieldsFor(Class<? extends AbstractEntity> entityClass);
}

and

public interface IEntityRepository<T extends AbstractEntity> {

List<T> search(String keyword, int firstResult, int fetchSize, SearchPreference… fields);

}

The interface ISearchPreferenceRepository provides the method getSearchFieldsFor() to load the search field configs as a list of SearchPreference objects using HQL, with one for each field to be queried for. The implementation is shown below.

@Transactional
@Repository(“searchPreferenceRepository”)
public class SearchPreferenceRepository extends AbstractEntityRepository<SearchPreference> implements ISearchPreferenceRepository {

 private static final String hql = “FROM ” + SearchPreference.class.getName() + ” WHERE entityName=?”;

@Autowired(required=true)
public SearchPreferenceRepository(SessionFactory sessionFactory) {
super(sessionFactory);
}

public List<SearchPreference> getSearchFieldsFor(Class<? extends AbstractEntity> entityClass) {
Query hqlQuery = sessionFactory.getCurrentSession().createQuery(hql);
hqlQuery.setParameter(0, ClassUtils.getShortClassName(entityClass));
return hqlQuery.list();
}

}

The actual full text search is done via the search() method of the IEntityRepository interface. Below is the implementation of the method:

public List<ENTITY> search(String keyword, int firstResult, int fetchSize, SearchPreference… fields) {

Session session = sessionFactory.getCurrentSession();

FullTextSession fullTextSession = Search.getFullTextSession(session);

QueryBuilder qb = fullTextSession.getSearchFactory().buildQueryBuilder().forEntity(getType()).get(); // [1]

BooleanJunction boolJn = qb.bool();

List<Query> subqueries = new ArrayList<Query>();

for (int i = 0; i < fields.length; i++) {

SearchPreference field = fields[i];

Query fieldQuery = null;

if (SearchType.MATCH.equals(field.getSearchType())) { // [2]

fieldQuery = qb.keyword().onField(field.getPropertyName()).matching(keyword).createQuery();

} else if (SearchType.WILDCARD.equals(field.getSearchType())) {

fieldQuery = qb.keyword().wildcard().onField(field.getPropertyName()).matching(keyword).createQuery();

}

subqueries.add(fieldQuery);

if (BoolType.MUST == field.getBoolType()) { //[3]

boolJn = boolJn.must(fieldQuery);

}

if (BoolType.SHOULD == field.getBoolType()) {

boolJn = boolJn.should(fieldQuery);

}

}

Query query = boolJn.createQuery(); //[4]

// wrap Lucene query in a org.hibernate.Query

org.hibernate.Query hibQuery = fullTextSession.createFullTextQuery(query, getType()); // [5]

hibQuery.setFirstResult(firstResult);

hibQuery.setFetchSize(fetchSize);

// execute search

List result = hibQuery.list(); // [6]

return result;

}

Its a rather long method but basically what it does is to create a lucene search query via Hibernate Search API using the SearchPreference objects in the input arguments. A Hibernate query object is then created and executed to generate the search results.

Note:

[1] – Create a DSL query builder of type QueryBuilder for the entity type.

[2] – For each field of type SearchPreference, a sub query is created for matching the input search string keyword with the query type (i.e. match, wildcard) matching the searchType property of that in the SearchPreference field.

[3] – The subqueries created in [2] are aggregated here. Currently 2 aggregation operations are supported:

  • SHOULD: the query should contain the matching elements of the subquery
  • MUST: the query must contain the matching elements of the subquery

[4] – The final query object is created.

[5] – The query object in [4] of type org.apache.lucene.search.Query is converted into a core Hibernate query object by the full text session.

[6] – Finally, the query is executed to return search results.

Service layer

The 2 repositories ISearchPreferenceRepository and IEntityRepository are used together to implement a generic full text search service. The implementation of this layer is straight forward is not included here.

Example

Let say we have an entity Product:

@Entity
@Indexed
public class Product extends AbstractEntity {

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private long id;

@Field(index=Index.YES, analyze=Analyze.YES, store=Store.NO)
private String sku;

@Field(index=Index.YES, analyze=Analyze.YES, store=Store.NO)
private String name;

@Field(index=Index.YES, analyze=Analyze.YES, store=Store.NO)
private String description;

… // getters and setters

Note the properties (sku, description, name) are indexed using Hibernate Search annotations. They are required for the entity to be queried via full text search. Let say we have the following data in the product data (mapped to by Hibernate by default):

sku name description
sku_1 name_1 product desc 1
sku_2 name_2 product desc 2
sku_3 name_3 product desc 3

The following codelet of an unit test class demonstrates how the repository classes can be used to perform various full text search of the product entity:

public class ProductRepositoryTest {
@Autowired
 @Qualifier("productRepository")
 private IEntityRepository<Product> repository;
@Test
 public void testSearchAndMatch() throws Exception {
      SearchPreference[] fieldPref = new SearchPreference[2];
      fieldPref[0] = createPref(Product.class.getName(), "sku", SearchType.MATCH, BoolType.MUST);
      fieldPref[1] = createPref(Product.class.getName(), "description", SearchType.MATCH, BoolType.MUST);
      List<Product> results = repository.search("sku_1", 0, 100, fieldPref);
      assertEquals(0, results.size()); // search returns no result as description MUST match keyword "sku_1"
 }
@Test
 public void testSearchOrMatch() throws Exception {
      SearchPreference[] fieldPref = new SearchPreference[2];
      fieldPref[0] = createPref(Product.class.getName(), "sku", SearchType.MATCH, BoolType.SHOULD);
      fieldPref[1] = createPref(Product.class.getName(), "description", SearchType.MATCH, BoolType.SHOULD);
      List<Product> results = repository.search("sku_1*", 0, 100, fieldPref);
      assertTrue(results.size() > 0); // search returns results where sku matches keyword "sku_1", no result from matching description field
 }
@Test
 public void testSearchAndWildcard() throws Exception {
      SearchPreference[] fieldPref = new SearchPreference[2];
      fieldPref[0] = createPref(Product.class.getName(), "sku", SearchType.WILDCARD, BoolType.MUST);
      fieldPref[1] = createPref(Product.class.getName(), "description", SearchType.WILDCARD, BoolType.MUST);
      List<Product> results = repository.search("*1*", 0, 100, fieldPref);
      assertTrue(results.size() > 0); // search return results where both sku and description matches wildcard "*1*", i.e. "sku_1" and "product desc 1"
 }
@Test
 public void testSearchOrWildcard() throws Exception {
      SearchPreference[] fieldPref = new SearchPreference[2];
      fieldPref[0] = createPref(Product.class.getName(), "sku", SearchType.WILDCARD, BoolType.SHOULD);
      fieldPref[1] = createPref(Product.class.getName(), "description", SearchType.WILDCARD, BoolType.SHOULD);
      List<Product> results = repository.search("sku_*", 0, 100, fieldPref);
      assertTrue(results.size() > 0); // search return results where sku matches wildcard "sku_*", no result from matching description field
 }
         // ...

Note the method createPref() returns an instance of SearchPreference object constructed using the method’s input argument. The codes are omitted above. Refer to the highlighted comments of each test for explanation.

That’s it for now.

Using @SQLInsert to insert entity in Hibernate with custom SQL

Summary

I have to work with legacy database with tables having literally 10s of columns that are not needed by the system I am developing. It is fine when all I have to do is to read the data. I just map the entity with the columns I need with Hibernate and ignore the rest. Inserting an entity on the other hand posts a problem if the column is declared non nullable and a default is not set in the database schema. As the non-nullable column is not known to Hibernate, the insert SQL statement generated when saving an entity will not assign any value to the column and a SQL error will result.

Solutions

There are 3 solutions I can think of to handle the above:

(1) Modify the database scheme to either remove unwanted columns or change column  definition to set a default value if it is null

(2) Add Hibernate mapping to the non nullable columns and set default values in entity.

(3) Modify SQL insert statement in Hibernate.

Solution (1) is not available to me as the database schema cannot be modified. Solution (2) may work for others if only a handful of columns need to be mapped. In my case I have too many unmapped columns needed to be added to make this solution practical. I will explain in rest of this blog on how to solve this problem with solution (3).

@SQLInsert

Hibernate supports using customer SQL for create, update and delete operations. See the documentation for more details. For example, I have the following entity

@Entity
@Table(name=”share”)
public class Share {

@Id
@Column(name=”code”)
private String code;

@Column(name=”name”)
private String name;

@Column(name=”price”)
private BigDecimal price;

By default, Hibernate will generate the following insert statement:

insert into share (name, price, code) values (?, ?, ?)

If the underlying table share has another column, e.g.  dummy, which is non nullable, the above SQL will not work and you will not be able to insert a new object into the database. Hibernate will throw the following exception:

Hibernate operation: Could not execute JDBC batch update; uncategorized SQLException for SQL [insert into share (name, price, code) values (?, ?, ?)]; SQL state [HY000]; error code [1364]; Field ‘dummy’ doesn’t have a default value; nested exception is java.sql.BatchUpdateException: Field ‘dummy’ doesn’t have a default value

To fix the above error, add the custom SQL using @SQLInsert:

@Entity
@Table(name=”share”)
@SQLInsert(sql=”Insert into share (name, price, code, dummy) value (?, ?, ?, ”)”, check=ResultCheckStyle.COUNT)
public class Share {

@Id
@Column(name=”code”)
private String code;

Now Hibernate will use the custom SQL and will insert a default value (empty string) into the dummy column.

Note the order of the fields in the SQL is important. To find the correct order used by Hibernate, turn on log4j for the persister, e.g.

// log4j.properties file

log4j.logger.org.hibernate.persister.entity=DEBUG

to get the following messages:

DEBUG [main] entity.AbstractEntityPersister (AbstractEntityPersister.java:2952)     – Static SQL for entity: com.rlee.myapp.domain.Share
DEBUG [main] entity.AbstractEntityPersister (AbstractEntityPersister.java:2957)     –  Version select: select code from share where code =?
DEBUG [main] entity.AbstractEntityPersister (AbstractEntityPersister.java:2960)     –  Snapshot select: select share_.code, share_.name as name3_, share_.price as price3_ from share share_ where share_.code=?
DEBUG [main] entity.AbstractEntityPersister (AbstractEntityPersister.java:2963)     –  Insert 0: insert into share (name, price, code) values (?, ?, ?)
DEBUG [main] entity.AbstractEntityPersister (AbstractEntityPersister.java:2964)     –  Update 0: update share set name=?, price=? where code=?
2012-01-07 11:41:33,361 DEBUG [main] entity.AbstractEntityPersister (AbstractEntityPersister.java:2965)     –  Delete 0: delete from share where code=?

Make sure the field orders in the @SQLInsert is the same as that in the log message.

Hibernate also supports customer SQL for update and delete with @SQLUpdate and @SQLDelete respectively.

Using outer join in HQL to search for entities in Hibernate

Hibernate uses cross join when generating the SQL query from HQL for entity with associations. This could cause problem when searching for entities when the association is optional. For example, let’s consider following 2 entities:

Parent:

@Entity
@Table(name=”Parent”)
public class Parent {

@Id
@Column(name=”id”)
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name=”prop”)
private String prop;

@OneToOne(fetch=FetchType.EAGER)
@JoinColumn(name=”childFK”)
@NotFound(action=NotFoundAction.IGNORE)
private Child child;

Child:

@Entity
@Table(name=”Child”)
public class Child implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@Column(name=”id”)
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

private String childProp;

Note the use of Hibernate @NotFound annotation for the Parent->Child association to indicate that the association is optional. If you want to search for Parent entities using its Child property, be aware that you won’t get any Parent entity that has no child association even if the search criteria is part of a OR statement. For example, let say the Parent table has the following data:

id prop childFK
1 john 1 (NULL)
2 sam (NULL)
3 john 2 1

and Child table has the data

id childProp
1 green
2 red
3 blue

The following HQL will return only Parent “john 2”, not “john 1”:

select from Parent where prop like ‘%john%’ OR chid.childProp like  ‘%green%’

This is because Hibernate generates the SQL query using cross join:

select parent0_.id as id2_, parent0_.childFK as childFK2_, parent0_.prop as prop2_ from Parent parent0_ cross join Child child1_ where parent0_.childFK=child1_.id and (parent0_.prop like ‘%joh%’ or child1_.childProp like ‘%green%’)

Note the highlighted part of the WHERE statement which filters out any Parent with no Child. To get the Parent “john 1” in the result, the following HQL should be used:

select from Parent as p left outer join p.child  as c where p.prop like ‘%john%’ OR c.childProp like ‘%green%’

The corresponding SQL query generated by Hibernate is:

 select parent0_.id as id2_0_, child1_.id as id0_1_, parent0_.childFK as childFK2_0_, parent0_.prop as prop2_0_, child1_.childProp as childProp0_1_ from Parent parent0_ left outer join Child child1_ on parent0_.childFK=child1_.id where parent0_.prop like ‘%joh%’ or child1_.childProp like ‘%green%’

So it would seem Hibernate ignores the @NotFound annotation when generating the SQL from HQL and if you intend to include entities with null association in the search results, you have to use left join in your HQL statements.

Searchable dropdown for Spring MVC/Hibernate web applications – Part I

Introduction

One common feature in a J2EE web application is to provide a dropdown box for user to select from a number of options. Typically, the selected option represents a key to the underlying domain object in the system. For example, a user may select a share code and the application will then display the detailed information of the chosen share by loading the share domain object using the value selected from the database.

In this  blog, I will demonstrate how to implement an end-to-end searchable dropdown solution for Spring MVC/Hibernate web applications. In this article, I will focus on building the backend codes required to provide a generic solution for generating option lists for any entities defined in the system. I will demonstrate how to build the searchable dropdown functions at the front-end in another blog.

Back-end solution design

1. Domain model

The domain model for the dropdown option is simple. It has 2 properties: name and value which correspond to the name and value attributes used in option elements in HTML:

public class DropdownOption {

private String name;

private String value;

public DropdownOption(String name, String value) {
this.name = name;
this.value = value;
}

2. Repository layer

The repository layer is where most of the implementation happens. There is only one method in the interface :

public interface IDropdownRepository {

List<DropdownOption> getOptions(String entityName, String nameProp, String valueProp, String filter);
}

The getOptions() method has 4 arguments:

  1. entityName – name of the entity to get values from
  2. nameProp – name of the entity’s property to use as the name of the dropdown option
  3. valueProp – name of the entity’s property to use as the value of the dropdown option
  4. filter – optional HQL string to filter search result

The implementation of IDropdownRepository using Spring Hibernate support:

@Repository
public class DropdownRepositoryImpl extends HibernateDaoSupport implements
IDropdownRepository {

@Autowired(required=true)
public DropdownRepositoryImpl(SessionFactory sessionFactory) {
super.setSessionFactory(sessionFactory);
}

public List<DropdownOption> getOptions(String entityName, String nameProp,
String valueProp, String filter) {
String where = StringUtils.isBlank(filter) ? “” : ” ” + filter;
 final String hql = “SELECT new com.rlee.myapp.domain.DropdownOption(” + nameProp + “,” + valueProp  + “) FROM ” + entityName + where;
List<DropdownOption> options = getHibernateTemplate().find(hql);
return options;
}

}

As shown in the codes above, the implementation constructs the proper HQL using the input arguments and then execute the query to get the result list. Note

  • the use of constructor of the DropdownOption(name,value) in the HQL SELECT statement to create the DropdownOption domain objects.
  • HQL functions can be used in either or both nameProp and valueProp values. E.g. it is possible to use concat() function to use values of multiple properties (i.e. multiple database columns) of the entity as the display option names.

3. Service layer

Typically, you would access repository via the service layer. In our case, the service just delegates the method to its repository.

Interface:

public interface IDropdownService {
List<DropdownOption> getOptions(String entityName, String nameProp, String valueProp, String filter);
}

Implementing class:

@Service
public class DropdownServiceImpl implements IDropdownService {

@Autowired(required=true)
private IDropdownRepository repository;

public List<DropdownOption> getOptions(String entityName, String nameProp,
String valueProp, String filter) {
return repository.getOptions(entityName, nameProp, valueProp, filter);
}

}

4. Controller

The dropdown option list is obtained via AJAX with requests with  address /dropdown/<entity>/get.action where <entity> is the name of the entity required:

@Controller
@RequestMapping(value = “/dropdown”)
public class DropdownController {

@Autowired(required = true)
private IDropdownService service;

@RequestMapping(value = “/{entity}/get.action”, method = RequestMethod.GET)
public @ResponseBody List<DropdownOption> getDropdownList(
@PathVariable(“entity”) String entity, HttpServletRequest request)
throws ServletRequestBindingException {
// Get name and value property from request
String nameProp = ServletRequestUtils.getRequiredStringParameter(
request, “nameProp”);
String valueProp = ServletRequestUtils.getRequiredStringParameter(
request, “valueProp”);
String filter = ServletRequestUtils.getStringParameter(request,
“filter”);
return service.getOptions(entity, nameProp, valueProp, filter);
}

}

Using Spring MVC JSON support, the method getDropdownList() will return the result as the JSON object representing the underlying list of DropdownOption objects, which can then be easily converted into the corresponding HTML option elements.

That’s it for now. I will show how to use the codes above to implement AJAX based searchable dropdown in the front-end.

Handling empty string foreign key in database in Spring/Hibernate ORM

I recently came across an issue when using Hibernate to map entity classes to an existing database. The database does not define foreign key constrain and use empty string (“”) in the FK column instead of NULL when a data row has no relation to the second table. When I mapped the relationship in my entity class, Hibernate attempts to find the associated entity using “” as the key. For example, the following code will throw an ObjectNotFoundException

@Entity

@Table(…)

@Repository(“myLoadListener”)

public class MyEntity {

@OneToOne

@JoinColumn(name=”fkCol”)

private MyAssocEntity assocEntity;

}

To work around the above problem, I implement a custom hibernate load event listener by extending the standard DefaultLoadEventListener:

@Component
public class MyLoadEventListener extends DefaultLoadEventListener {

@Override
protected Object doLoad(
final LoadEvent event,
final EntityPersister persister,
final EntityKey keyToLoad,
final LoadEventListener.LoadType options) {

Object entity = handleEmptyStringFK(keyToLoad);
return entity == null ? super.doLoad(event, persister, keyToLoad,   options) : entity;
}

@Override
protected Object proxyOrLoad(
final LoadEvent event,
final EntityPersister persister,
final EntityKey keyToLoad,
final LoadEventListener.LoadType options) {
Object entity = handleEmptyStringFK(keyToLoad);
return entity == null ? super.proxyOrLoad(event, persister, keyToLoad, options) : entity;
}

I override both doLoad() and proxyOrLoad() methods by adding a new method handleEmptyStringFK() to check for empty string in the identifier property in the input argument ketToLoad and instantiate an empty object to bypass the exception to be thrown by the parent’s methods. proxyOrLoad() is required here as it is called when using lazy loading.

Now, the listener has to be registered to the session factory. I use Spring and have to add the following in the xml configuration file:

<eventListeners refid=”myloadListener”></eventListeners>

Admittedly, this is a database issue but I cannot modify the data or database schema and the above solution works fine for me.