SQL Select statement in java

Question

I have a HashSet of strings, the size of the hashset is variable it can contain 10 strings or 10.000 it depends on the user that fills the list. Now I have to make a sql statement with the strings from the list where I get some information back from the database this is how I do it right now:

        for (Iterator<String> iter = currentStrings.iterator(); iter
                .hasNext();) {
            ResultSet rs = entPermission.sql
                    .executeQuery("select name from table join table_access "
                            + "on table_access.access_granted_to=table.id"
                            + " join table on table.id=table_access.name_id"
                            + " where table.name='"
                            + iter.next() + "'");
            while (rs.next()) {
                String informations = rs.getString("name");
                 informationList.add(informations);

            }
        }

So what I do now is send a request for the information for every single string in the HashSet so as you can understand it can take a while till I get the results of 10.000 strings. Is there a better (faster) way to go through the 10.000 strings without making a select statement for every single one of them?

you could consider using `select ... where name in (name1, name2, name3 ...)` ... that way you have to request the DB one time only — A4L, Feb 07 '12 at 13:59
See also: http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives — fgb, Feb 07 '12 at 14:09
How does the user fill the HashSet with names? If the names could be stored in a table as well, the select could be performed in one go. That would be much faster, but doesn't help much if you'd have to wait for 10.000 inserts first ... so depends on your apps logic, which you do not mention. Also, if the in-statement has restrictions, you could break up into several queries and union them together to get around that. — Jon Martin Solaas, Feb 07 '12 at 19:12
@Jon Martin Solaas the user fills the HashSet by clicking on a tree Item .depending if the item is a Parent the HashSet will get all the children, he can select multiple parents so the HashSet gets pretty large. — Kiesa, Feb 08 '12 at 07:09
@Kiesa - next question is, how is the tree item populated? If the tree item relationships are stored in the database in the first place, you can base your query on that. No need to read all id's into a tree structure, and then put a bunch of id's in a "... where blabla in (id1,2....)"- clause, if all you really need to build the query criteria are the parent id, or id's. You really only need the id's of the nodes the user actually clicks, the rest of the ids you can join in somehow in the query. — Jon Martin Solaas, Feb 09 '12 at 09:36
@Jon Martin Solaasi aktualy I have 3 trees from 3 different sources , one source is xml another GML and the third as you said datadase. — Kiesa, Feb 09 '12 at 09:40
@Kiesa From somewhere far back in my memory I think I remember that PostgreSQL has some plugin that mimicks the Oracle CONNECT BY hierarchical query feature. Maybe that will help you in constructing a query that can deduce all the child-node-ids from the nodes that the user actually clicks. From the manuals it seems WITH RECURSIVE is the way to do hierarchical/recursive queries in postgresql. http://wiki.postgresql.org/wiki/CTEReadme — Jon Martin Solaas, Feb 09 '12 at 09:42
@Kiesa Ah, then you can't just do all the fancy stuff in sql .... unless replicating the XML and GML structures in the database is an option ... — Jon Martin Solaas, Feb 09 '12 at 09:45

score 4 · Accepted Answer · answered Feb 07 '12 at 13:54

4

You have to use PreparedStatement to construct pre-compiled sql statement.

answered Feb 07 '12 at 13:54

KV Prajapati

93,659
19
148
186

Unai Vivi · Answer 2 · 2012-02-07T15:32:26.813

2

Change your SELECT so that you query your DB only once, by expanding your strings into a IN:

String strIN="";
for(Iterator<String> iter = currentStrings.iterator(); iter.hasNext();)
{
    if(strIN!="")
        strIn+=",";
    strIN+="'"+iter.next()+"'";
}
ResultSet rs = entPermission.sql
    .executeQuery("select name from table join table_access "
                + "on table_access.access_granted_to=table.id"
                + " join table on table.id=table_access.name_id"
                + " where table.name IN ("+strIN+")");
//...

Note that, for the sake of clarity, I have used String for strIN; if your currentStrings is very big, you might want to use a StringBuilder instead.

edited Feb 07 '12 at 15:32

answered Feb 07 '12 at 14:04

Unai Vivi

3,073
3
30
46

2

Note that using the "IN" can be an issue since different databases have a limit on the list you can provide. I believe Oracle is about 1000 items. – bsimic Feb 07 '12 at 15:18
OP uses PostgreSQL: do you know how high its limits are? – Unai Vivi Feb 09 '12 at 12:47
I don't know what the limit for PostgreSQL is but I know that it has one. Sorry. – bsimic Feb 09 '12 at 23:39

score 1 · Answer 3 · answered Feb 07 '12 at 13:57

1

I would use a stored procedure in the database. I would avoid plain SQL queries in the code, if possible.

answered Feb 07 '12 at 13:57

vulkanino

9,074
7
44
71

score 0 · Answer 4 · answered Feb 07 '12 at 14:04

How about using UNION to combine the result sets - you can build up your statement programatically and send it once.

StringBuilder str = new StringBuilder("select name from table join table_access ");
str.append("on table_access.access_granted_to=table.id");
str.append(" join table on table.id=table_access.name_id");
str.append(" where table.name='");

StringBuilder query = new StringBuilder();


for (Iterator<String> iter = currentStrings.iterator(); iter
            .hasNext();) {
    query.append(str.toString());
    query.append(iter.next());
    query.append("'");

    if (iter.hasNext()) {
        query.append(" UNION ");
    }       
}

ResultSet rs = entPermission.sql.executeQuery(query);
while (rs.next()) {
    String informations = rs.getString("name");
    informationList.add(informations);
}

This is still constructed. A size limitation will kick in, eventually! — mgaert, Feb 07 '12 at 15:44
Are you guys sure a query without any limitations at all is really, really needed here? And, btw, If you know how to implement one, please let me know ;-) — Jon Martin Solaas, Feb 09 '12 at 09:28

score 0 · Answer 5 · answered Feb 07 '12 at 15:43

0

Do not attempt to put 10000 items into a constructed IN list. This will not work (SQL size limitation) and does not scale.

Instead, have a temp table with some integer column, and a colum for your 10000 values. greate a common random integer RR (or use a SEQUENCE, AUTOKEY etc.) and put all your 10000 values (names) into that table, in batched mode. Have a non-unique index on the integer column.

Then, JOIN against that table, limited by that integer RR. This way, all your SQL statements will be short, and constant (!). And the database is perfect at joining things.

Eventually (right after the operation, or once a day), remove outdated items from your temp table.

answered Feb 07 '12 at 15:43

mgaert

2,338
21
27

i have to get the information on the fly , the user just have to click on an Item in a Tree to fill the HashSet so the HashSet changes very frequently – Kiesa Feb 08 '12 at 10:09
Well, sure. But you need to consider whether you want to hit the database for this at all, or not. If not, consider some kind of caching. Assume you still want to go for the database. It does not matter whether you send all items in one SQL statement, or in 1000 set-up + 1 query + 1 teardown. Key point is that your statements are fixed length and constant. – mgaert Feb 08 '12 at 13:14
@mgaert we already know that the 1000 statement approach is too slow, so it matters. – Jon Martin Solaas Feb 09 '12 at 09:31

SQL Select statement in java

5 Answers5