Welcome to the new Gigaspaces XAP forum. To recover your account, please follow these instructions.

Ask Your Question
0

SQL Query using "IN"

I'm trying to figure out how to use an "IN" clause in a SQL query.

My User POJO has a String userName property, and I'm passing in a String[] of user names I want to load the User objects for.

I tried this:

String[] names = ... SQLQuery<user> referencedUserQuery = new SQLQuery<user>(User.class, "userName in (?)"); referencedUserQuery.setParameter(1,names);

This didn't work, and seems to return an empty Object[] instead of a User[].

I also tried building the query string dynamically, adding a "?" for each name and binding each one using setParameter for each, also with no success.

Are there any examples using an "IN" clause in a query?

{quote}This thread was imported from the previous forum. For your reference, the original is [available here|http://forum.openspaces.org/thread.jspa?threadID=2340]{quote}

asked 2008-05-18 20:09:00 -0500

jcarreira's avatar

updated 2013-08-08 09:52:00 -0500

jaissefsfex's avatar
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

IN SQL should work. You should not pass an array to the setParameter , but a single value:
SQLQuery<User> referencedUserQuery = new SQLQuery<User>(User.class, "userName in (?,?,?)");
referencedUserQuery.setParameter(1,names0);
referencedUserQuery.setParameter(2,names1);
referencedUserQuery.setParameter(3,names2);

Still , it would not have huge performance boost compared to separate read with SQLQuery using setParameter.
Please note you should not create SQLQuery for each space query operation. Create it once and reuse it using the setParameter method.

Shay

answered 2008-05-19 09:28:30 -0500

shay hassidim's avatar
edit flag offensive delete link more

Comments

If this is not much more efficient than loading each one separately, what's the most efficient way to load these items?

I tried this once, creating a dynamic string with the right number of "?"s and setting them separately, and it didn't work. I'll try it again and see what the error is. Unfortunately, doing this means I can't reuse the SQLQuery because I don't know how many usernames there are going to be (probably less than 20) .

As a side note, are SQLQuery instances thread safe? Can I re-use them in a concurrent environment like an event listener being called from a polling / notification container?

Being as I can't re-use the SQLQuery, is it more efficient to just do separate queries for each User using a template User with the userName set?

jcarreira's avatar jcarreira  ( 2008-05-19 09:53:43 -0500 )edit

If you are using ExternalDataSource the recommended way is to have separate read calls. This will make sure the space would not perform unnecessary database call.

We will optimize the IN implementation in future versions to have better performance.

SQLQuery instances are thread safe , but if you are using the SQLQuery.setParamter you will need to have separate SQLQuery instances per thread.

When using the notify listener don't have heavy lifting business logic implemented at the listener since this might consume all notify threads (relevant only with system with high TP). In such cases , delegate the actual work to another worker thread to allow the space to release the notification invocation thread once the listener method returns.

Shay

shay hassidim's avatar shay hassidim  ( 2008-05-19 10:03:46 -0500 )edit

Ok, I'll stick with the multiple queries for now. I'll post another design question in a bit about that.

For the work tying up notify threads, I implemented this being delegated to a thread pool using the adapter discussed [here|http://www.gigaspaces.com/wiki/display/GS6/OpenSpaces+Events+Component+-+Data+Event+Listener#OpenSpacesEventsComponent-DataEventListener-TaskExecutorEventListenerAdapter].

Thanks!

jcarreira's avatar jcarreira  ( 2008-05-19 10:09:04 -0500 )edit

Please note you can also use SQLQuery.setParameters(array) and pass the names array.

Shay

shay hassidim's avatar shay hassidim  ( 2008-05-20 02:32:57 -0500 )edit

Answars:

  1. The issue here is that readMultiple call might access the database for each query condition execution when using LRU cache policy mode.

For example: Having your query as ”A or B or C“ with 10 as the max objects for the readMultiple call , might result 3 calls to the database in case A , B , C does not have 10 matching entries within the space. We do not query the space for A , B and C and summing the result and based on the result deciding if the database should be queried to fetch the remaining objects. We will optimize this behavior in future versions.

  1. There is no difference between POJO with/out annotations in terms of queries handling. Full list of supported key words located at: http://www.gigaspaces.com/wiki/displa...

For AND based queries use regular template. These are translated to one Query when ExternalDataSource is used.

3, 4 ExternalDataSource considerations and limitations listed here (6.5): http://www.gigaspaces.com/wiki/displa...

Full details about POJO’s located here: http://www.gigaspaces.com/wiki/displa...

  1. Here are list of changes with the XAP 6.5 ExternalDataSource interface:
  • The ExternalDataSource has been changed to use the ExternalDataSource.Iterator(SQLQuery) instead of Iterator(Object).
  • The following has been removed: , DataProvider.count , SQLDataProvider.count , DataPersister.updateBatch , DataPersister.removeBatch
  • ExternalDataSource.Iterator(SQLQuery) will be called with like based queries.
  • ExternalDataSource..writeBatch is deprecated (called for transactional writeMuletiple). ExternalDataSource.executeBulk will be called instead.
  • The HibernateExternalDataSource implementation has been enhanced dramatically to support fast data load and statles session. It has been moved to open spaces (it is now open source). See the org.openspaces.persistency.hibernate.DefaultHibernateExternalDataSource and org.openspaces.persistency.hibernate.StatelessHibernateExternalDataSource classes.

  • The com.gigaspaces.datasource.hibernate.HibernateDataSource is deprecated.

More about 6.5: http://www.gigaspaces.com/wiki/displa...http://www.gigaspaces.com/wiki/displa...

Shay

shay hassidim's avatar shay hassidim  ( 2008-06-11 17:02:00 -0500 )edit

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower

Stats

Asked: 2008-05-18 20:09:00 -0500

Seen: 299 times

Last updated: May 19 '08