How to force a query to use default(write) connection pool?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

How to force a query to use default(write) connection pool?

Kohei Nozaki
Hello,

I'm looking for a way to force a query to use default(write) connection  
pool.

Let's say we have following properties in persistence.xml:

...
<property name="javax.persistence.jdbc.driver"  
value="org.apache.derby.jdbc.EmbeddedDriver"/>
<property name="javax.persistence.jdbc.url"  
value="jdbc:derby:memory:masterDB;create=true"/>
<property name="eclipselink.connection-pool.read.url"  
value="jdbc:derby:memory:slaveDB;create=true" />
...

This makes read queries to be executed in "slaveDB" but I need to  
forcibly execute some read queries on "masterDB" to avoid replication  
lag (I use a MySQL cluster for my real usecase).

What's the best way to achieve this requirement? it would be great if  
there is a query hint to set the destination of a query to "master"  
forcibly, something like this:

   String mycol = em.createQuery("select m from MyTable m", MyTable.class)
     .setHint("eclipselink.forceMaster", true) // this hint does not exist
     .getSingleResult().getMycol();

(taken from
https://github.com/lbtc-xxx/eclipselink-force-write/blob/master/src/test/java/main/ConnectionDestinationTest.java#L68-L75 
)

I have found a solution which is beginning a transaction then issue a  
native query (e.g. SELECT 1 FROM SYSIBM.SYSDUMMY1) finally issue a JPQL  
query, but looks kludgy.

(complete testcase:
https://github.com/lbtc-xxx/eclipselink-force-write/blob/master/src/test/java/main/ConnectionDestinationTest.java#L111-L120 
)

Thanks,
Kohei
_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
Reply | Threaded
Open this post in threaded view
|

Re: How to force a query to use default(write) connection pool?

Kohei Nozaki
Hi there,

We ended up created two distinct persistence units, one with  
"eclipselink.connection-pool.read.url" and another one without it.

Thanks.

On 6/10/16 1:56 PM, Kohei Nozaki wrote:

> Hello,
>
> I'm looking for a way to force a query to use default(write) connection
> pool.
>
> Let's say we have following properties in persistence.xml:
>
> ...
> <property name="javax.persistence.jdbc.driver"
> value="org.apache.derby.jdbc.EmbeddedDriver"/>
> <property name="javax.persistence.jdbc.url"
> value="jdbc:derby:memory:masterDB;create=true"/>
> <property name="eclipselink.connection-pool.read.url"
> value="jdbc:derby:memory:slaveDB;create=true" />
> ...
>
> This makes read queries to be executed in "slaveDB" but I need to
> forcibly execute some read queries on "masterDB" to avoid replication
> lag (I use a MySQL cluster for my real usecase).
>
> What's the best way to achieve this requirement? it would be great if
> there is a query hint to set the destination of a query to "master"
> forcibly, something like this:
>
>   String mycol = em.createQuery("select m from MyTable m", MyTable.class)
>     .setHint("eclipselink.forceMaster", true) // this hint does not exist
>     .getSingleResult().getMycol();
>
> (taken from
> https://github.com/lbtc-xxx/eclipselink-force-write/blob/master/src/test/java/main/ConnectionDestinationTest.java#L68-L75
> )
>
> I have found a solution which is beginning a transaction then issue a
> native query (e.g. SELECT 1 FROM SYSIBM.SYSDUMMY1) finally issue a JPQL
> query, but looks kludgy.
>
> (complete testcase:
> https://github.com/lbtc-xxx/eclipselink-force-write/blob/master/src/test/java/main/ConnectionDestinationTest.java#L111-L120
> )
>
> Thanks,
> Kohei
_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users