aspose file tools
The moose likes JDBC and Relational Databases and the fly likes pass array to query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA Java SE 8 Programmer I Study Guide 1Z0-808 this week in the OCAJP forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "pass array to query" Watch "pass array to query" New topic
Author

pass array to query

phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
Hi,

How would I pass an array to a query?

for ex:

public Hashtable getMonth(String cat,int[] month, int year) throws

{

need to pass month as a array to the below query

SELECT SUM(aaa), SUM(bbb), SUM(ddd) FROM emp_tbl WHERE AND YEAR(DATE)=2008 AND MONTH(DATE) =").append(month)

}
Can anyone help me in this case please?

Thanks a lot
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31415
    
184

Lakshmi,
You can build the where clause dynamically.

If you had two months, you would want it to look like:
AND MONTH(DATE) in ( ?, ? )
If you had five months, you would want it to look like:
AND MONTH(DATE) in ( ?, ?, ?, ?, ? )

A loop builds this up well at runtime. My example uses the question mark binding variables used in prepared statements. Prepared Statements are generally recommended, but you can use the same technique with your array elements directly.


[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
Now I am passing an monthstring (1,2, 3) to the query

StringBuffer query = new StringBuffer("SELECT SUM(AAAAA), SUM(DDDDDDD) ");
query.append(" FROM "+Constants.emp+" WHERE CAT
='").append(category).append("' AND YEAR(DATE)=").append(year);
query.append(" AND MONTH(DATE)IN").append("(").append(monthStr).append(")");

query.append(" AND MONTH(DATE)IN").append("(").append(monthStr).append(")");// I got the mql syntax error at for monthstr
java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1,2,3)' at line 1

what was the wrong in the above query. Please let me know
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31415
    
184

Lakshmi,
Try outputting the value of the query variable to see what it built. Then run it through the command line mySql to get the actual error message. Or post it here if you don't have access.
 
wood burning stoves
 
subject: pass array to query