• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 127
  • Last Modified:

Java array passed to SQL where clause

I have something like this:

ResultSet rs = stmt.executeQuery("SELECT lid, basistime, value FROM fcstheight WHERE lid = 'ASTF1' ORDER BY basistime");

I need to put pass in a Java Array (or, ArrayList?) for the WHERE clause.  That array would have many lid's

Can something help me an with an example?  Would I use a "IN" clause somehow?
0
MarkLoveExEx
Asked:
MarkLoveExEx
  • 11
  • 10
  • 2
1 Solution
 
CEHJCommented:
Would I use a "IN" clause somehow?
You could, yes. Just write a helper method that quotes all the values
0
 
MarkLoveExExAuthor Commented:
Too many solutions out there regarding syntax. Got frustrated...
0
 
CEHJCommented:
    private static String toSet(List<?extends Object> list) {
        String tmp = list.toString();
        tmp = tmp.replace("[", "(").replace("]", ")");
        tmp = tmp.replaceAll("\\b(\\w+)\\b", "'$1'");

        return tmp;
    }

Open in new window


String inClause = "IN " + toSet(theList);

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
MarkLoveExExAuthor Commented:
Excellent solution Charles.  You just saved me a lot of time.  Mark
0
 
CEHJCommented:
:) It shouldn't be regarded as production quality code though - for one thing, it's assumptive about the initial implementation of toString (which weakens it)
0
 
MarkLoveExExAuthor Commented:
What assumptions is it making?  I should be able to tell if what I'm feeding it will cause a problem or not...
0
 
CEHJCommented:
It assumes it's an implementation of List.toString from the JDK.
private static String toSet(java.util.ArrayList<?extends Object> list) 

Open in new window

would remove doubt, but even then, you could argue that one day they might change the implementation ...

A stronger implementation would require more coding
0
 
MarkLoveExExAuthor Commented:
Ok. Thanks.  Since I am NOT a professional programmer, I cringe every time I am forced to update the Java run-time environment.  I update, and then fix anything that's broken.  Maybe if I was paid full time to do this stuff, and got really good at it, I would feel more in control...
0
 
gurpsbassiCommented:
I would also be careful using an IN clause if the number of Id's is huge.
Not sure what equates to 'huge', each database vendor will have its limits. I recall a query blowing up because the IN clause was too big. Not sure which RDBMS it was...maybe sqlserver.
0
 
CEHJCommented:
I cringe every time I am forced to update the Java run-time environment.  I update, and then fix anything that's broken.
In theory, if thing's are done right, updating Java shouldn't be a problem owing to backward compatibility
0
 
MarkLoveExExAuthor Commented:
gurpsbassi -- At most I would have about 30 ids. Is that a lot?
0
 
CEHJCommented:
An alternative would be to use
WHERE x = 'foo' OR x ='bar'

Open in new window

etc.
but you might find the RDBMS optimizes it anyway. Which one is it btw?
0
 
MarkLoveExExAuthor Commented:
Postgres
0
 
CEHJCommented:
0
 
gurpsbassiCommented:
30 id's doesn't sound like it would cause a problem.
0
 
MarkLoveExExAuthor Commented:
CEHJ, May I ask a followup question?  If not, I can open up another question...  Here's my question:

Your supplied code (toSet) needs a List.  Makes sense.  But when I am putting together my "SELECT" statement, I need to piece together the name of that List using strings.  But, when executing, the program thinks that I am trying to send a String, instead of a List.  It is actually the contents of the String, interpreted as the name of a List, that I want to pass. How can I do that?
0
 
CEHJCommented:
Sorry - have read that several times and don't understand a word i'm afraid ;)
0
 
MarkLoveExExAuthor Commented:
This is what you gave me (or, close to it):

public class HelperCode {

         public static String toSet(List<?extends Object> list) {
              String tmp = list.toString();
              tmp = tmp.replace("[", "(").replace("]", ")");
              tmp = tmp.replaceAll("\\b(\\w+)\\b", "'$1'");

              return tmp;
          }
}


This is how I can call it, and this WORKS:

ResultSet rs = stmt.executeQuery("SELECT lid,pe,dur,ts,extremum,validtime,basistime,value FROM fcstheight where lid IN"
+ HelperCode.toSet(StartHere.fgDB_Chowan) + " ORDER BY lid, basistime, validtime");

The StartHere.fgDB_Chowan is declared like this (in the StartHere class):
      static List<String> fgDB_Chowan = new ArrayList<String>();

But,what I WANT to do is call "toSet" using the following:
I have a String with "Chowan" in it in the variable "selectedFG"
I modify that String:
                  myTOSETstring = "fbDB_" + selectedFG;

I then try to run the following, but it fails:
ResultSet rs = stmt.executeQuery("SELECT lid,pe,dur,ts,extremum,validtime,basistime,value FROM fcstheight where lid IN"
+ HelperCode.toSet("StartHere." + myTOSETstring) + " ORDER BY lid, basistime, validtime");

I get a compile error:
The method toSet(List<? extends Object>) in the type HelperCode is not applicable for the arguments (String)

I understand this error, but I don't know how to fix it.
0
 
CEHJCommented:
I don't get it. The method i gave you was intended to work with your spec of a series of IDs. I don't see where 'StartHere.' comes in ...
0
 
MarkLoveExExAuthor Commented:
The array of IDs is located in the StartHere class. These arrays are static, and i have lots of arrays with different names.  When I am calling the database, I need to piece together (using Strings) the name of the correct Array to pass to the code you gave me.  Ultimately, the name of the array to pass is dependent upon what the user is doing in a GUI.
Although I can piece together the name of the Array to pass, the program thinks that I am trying to pass the actual String, rather than the Array that it represents...
0
 
MarkLoveExExAuthor Commented:
It's kinda like shell scripting, where you use different types of quotes to change how the shell interprets things.
0
 
CEHJCommented:
I'm confused. A set (as in IN) is a list of values - why are you using the term 'name'
Do you mean each value is prefaced with another token that is a kind of name, or what?
0
 
MarkLoveExExAuthor Commented:
I can fix this with a gaudy set of if, then statements.  I will pass the list of values like I'm supposed to, without trying to get fancy...  I'm good. Thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now