MarkLoveExEx
asked on
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?
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?
ASKER
Too many solutions out there regarding syntax. Got frustrated...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent solution Charles. You just saved me a lot of time. Mark
:) 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)
ASKER
What assumptions is it making? I should be able to tell if what I'm feeding it will cause a problem or not...
It assumes it's an implementation of List.toString from the JDK.
A stronger implementation would require more coding
private static String toSet(java.util.ArrayList<?extends Object> list)
would remove doubt, but even then, you could argue that one day they might change the implementation ...A stronger implementation would require more coding
ASKER
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...
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.
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.
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
ASKER
gurpsbassi -- At most I would have about 30 ids. Is that a lot?
An alternative would be to use
but you might find the RDBMS optimizes it anyway. Which one is it btw?
WHERE x = 'foo' OR x ='bar'
etc.but you might find the RDBMS optimizes it anyway. Which one is it btw?
ASKER
Postgres
You could probably use
https://www.postgresql.org/docs/8.1/static/sql-explain.html
https://www.postgresql.org/docs/8.1/static/sql-explain.html
30 id's doesn't sound like it would cause a problem.
ASKER
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?
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?
Sorry - have read that several times and don't understand a word i'm afraid ;)
ASKER
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,val idtime,bas istime,val ue FROM fcstheight where lid IN"
+ HelperCode.toSet(StartHere .fgDB_Chow an) + " 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,val idtime,bas istime,val ue FROM fcstheight where lid IN"
+ HelperCode.toSet("StartHer e." + 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.
public class HelperCode {
public static String toSet(List<?extends Object> list) {
String tmp = list.toString();
tmp = tmp.replace("[", "(").replace("]", ")");
tmp = tmp.replaceAll("\\b(\\w+)\
return tmp;
}
}
This is how I can call it, and this WORKS:
ResultSet rs = stmt.executeQuery("SELECT lid,pe,dur,ts,extremum,val
+ HelperCode.toSet(StartHere
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,val
+ HelperCode.toSet("StartHer
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.
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 ...
ASKER
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...
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...
ASKER
It's kinda like shell scripting, where you use different types of quotes to change how the shell interprets things.
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?
Do you mean each value is prefaced with another token that is a kind of name, or what?
ASKER
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.