Hi,

I am designing a "multi tenant" web application which uses Hibernate to access to a high number of MySQL databases (or catalog : managed by a single database server) (> 500 identical databases for a single web application). I plan to use a single data source, in order to avoid saturating the db server with connections and to switch database according to the connected user tenant id.

I have been searching a good solution to do so for a few days, and I would like to ask 2 questions :

1) I have seen it is possible to switch database overriding the onPrepareStatement() method of EmptyInterceptor (like described here http://java.dzone.com/articles/hibernate-dynamic-table-routin). Do you think it is a good solution. Is there any better choice ?

2) I need to insure primary key uniqueness across databases for read-write entities, to avoid entity collisions in Hibernate. I think using a counter table in a shared database. But it may create a bottleneck for insert operations. How do you think I should implement this ?

Best regards,
Eric