mysql - Segregating the read-only and read-write in Spring/J2EE Apps -

we using spring, spring-data , jpa in our project.

for production servers, setup database cluster such read queries directed 1 server , write queries directed server.

this require changes in way daos built.

does know how achieve if 1 has, far, been following cook-book style dao creations using spring-data/jpa dao implementation responsible both reads , writes? kind of changes in architecture needed segregate 2 types of calls?

when using mysql, common java developers use connector/j jdbc driver (since official jdbc driver mysql). developers typically use com.mysql.jdbc.driver class driver, url such jdbc:mysql://host[:port]/database.

connector/j offers driver called replicationdriver allows application load-balance between multiple mysql hosts. when using replicationdriver, jdbc url changes jdbc:mysql:replication://master-host[:master-port][,slave-1-host[:slave-1-port]][,slave-2-host[:slave-2-port]]/database. allows application connect 1 of multiple servers depending on 1 available @ given point in time.

when using replicationdriver, if jdbc connection set read-only, driver treats first host declared in url read-write host , others read-only hosts. developers can take advantage of in spring application structuring code follows:

@service @transactional(readonly = true) public class someserviceimpl implements someservice {    public somedatatype readsomething(...) { ... }     @transactional(readonly = false)    public void writesomething(...) { ... } } 

with code this, whenever method readsomething called, spring transaction management code obtain jdbc connection , call setreadonly(true) on because service methods annotated @transactional(readonly = true) default. make database queries readsomething method go 1 of non-master mysql hosts, load-balanced in round-robin fashion. similarly, whenever writesomething called, spring call setreadonly(false) on underlying jdbc connection, forcing database queries go master server.

this strategy allows application direct read-only traffic 1 set of mysql servers , read-write traffic different server, without changing application's logical architecture or developers having worry different database hosts , roles.