Solved

Java array passed to SQL where clause

Posted on 2016-10-29
23
41 Views
Last Modified: 2016-10-30
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
Comment
Question by:MarkLoveExEx
  • 11
  • 10
  • 2
23 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 41865164
Would I use a "IN" clause somehow?
You could, yes. Just write a helper method that quotes all the values
0
 

Author Comment

by:MarkLoveExEx
ID: 41865166
Too many solutions out there regarding syntax. Got frustrated...
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 500 total points
ID: 41865174
    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
 

Author Closing Comment

by:MarkLoveExEx
ID: 41865214
Excellent solution Charles.  You just saved me a lot of time.  Mark
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 41865220
:) 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
 

Author Comment

by:MarkLoveExEx
ID: 41865221
What assumptions is it making?  I should be able to tell if what I'm feeding it will cause a problem or not...
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 41865235
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
 

Author Comment

by:MarkLoveExEx
ID: 41865246
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
 
LVL 15

Expert Comment

by:gurpsbassi
ID: 41865298
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 41865310
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
 

Author Comment

by:MarkLoveExEx
ID: 41865365
gurpsbassi -- At most I would have about 30 ids. Is that a lot?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 86

Expert Comment

by:CEHJ
ID: 41865371
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
 

Author Comment

by:MarkLoveExEx
ID: 41865377
Postgres
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 41865379
0
 
LVL 15

Expert Comment

by:gurpsbassi
ID: 41865436
30 id's doesn't sound like it would cause a problem.
0
 

Author Comment

by:MarkLoveExEx
ID: 41865818
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 41865844
Sorry - have read that several times and don't understand a word i'm afraid ;)
0
 

Author Comment

by:MarkLoveExEx
ID: 41865850
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 41865852
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
 

Author Comment

by:MarkLoveExEx
ID: 41865865
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
 

Author Comment

by:MarkLoveExEx
ID: 41865868
It's kinda like shell scripting, where you use different types of quotes to change how the shell interprets things.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 41865871
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
 

Author Comment

by:MarkLoveExEx
ID: 41865876
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 34
JAVA part two 5 41
Java and GPO 11 47
Strange loading of website behaviour 3 23
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now