1

I have a LinkedHahMap map1 whcih holds key as my beam_current which is of double type and value as my logtime which is of string type. Now I want to use this map1.keySet() in my sql query as-

Set<Double> arr=    map1.keySet();
String vs2="select b.beam_current, b.beam_energy where
          b.logtime between '"+first+"' and '"+last+"' and b.beam_current in('"+arr+"')";

But when I use arr which holds value of map1 key ,**nothing is being displayed.**Can't we use map1.KeySet() method in sql query or I'm implemting it in wrong way??

MES
  • 132
  • 1
  • 14
  • '"+arr+"' == arr.toString() == [1,2,3] == .beam_current in('[1,2,3]')"; – degr Feb 11 '15 at 10:38
  • You should produce a proper string list of values or override the toString , but please don't use this kind of queries build without parameters, they are prone to SQL injection... https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java – Paolof76 Feb 11 '15 at 10:41

2 Answers2

2

First convert your map keys into comma separated string and then use it in your query.

  List<Double> slist = new ArrayList<Double>(map1.keySet());
  String s = StringUtils.join(slist, ',');


      String vs2="select b.beam_current, b.beam_energy where
                  b.logtime between '"+first+"' and '"+last+"' and    
                  b.beam_current in('"+s+"')";
atish shimpi
  • 4,873
  • 2
  • 32
  • 50
  • what is slist??Is it d?? – MES Feb 11 '15 at 10:43
  • @atsih ,I can't see your edit and when I implement thsi i get an exception **Type mismatch: cannot convert from HashSet to List** and moreover another Exception **StringUtils cannot be resolved** occurs – MES Feb 11 '15 at 10:46
  • I have edited my answer changed `HashSet` to `ArrayList` and slist is instance of `List`, please let me know if you have any issue. check line number 1 in answer – atish shimpi Feb 11 '15 at 10:48
  • what do to about StringUtils,as It can't be resolved. – MES Feb 11 '15 at 10:50
  • 1
    It is form `org.apache.commons.lang3.StringUtils` you have to add `commons-lang3-3.3.2.jar` jar file – atish shimpi Feb 11 '15 at 10:50
  • I did but getting an exception **com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type varchar to real** – MES Feb 11 '15 at 11:00
  • print `s` in your project and check that, is it coming comma separated string? – atish shimpi Feb 11 '15 at 11:02
  • its coming comma separated. But why no values are being printed?What is this conversion error?? – MES Feb 11 '15 at 11:05
  • actually your main issue is resolved it's another issue, ask it as new issue and close this question by accepting my answer if it helped you. – atish shimpi Feb 11 '15 at 11:14
0

If you don't want to depend on external libs (like StringUtils.join), you can do it manually:

public static void main(String[] args) {
    HashMap<Double, String> map1 = new HashMap<Double, String>();
    map1.put(1.5, "");
    map1.put(2.5, "");
    String first = "first";
    String last = "last";

    String query = buildQuery(map1, first, last);
    System.out.println(query);
}

private static String buildQuery(HashMap<Double, String> map1, String first, String last) {
    StringBuilder sb = new StringBuilder();
    sb.append("select b.beam_current, b.beam_energy where b.logtime between '");
    sb.append(first);
    sb.append("' and '");
    sb.append(last);
    sb.append("' and b.beam_current in (");
    String separator = "";
    for (Double val : map1.keySet()) {
        sb.append(separator);
        sb.append(val);
        separator = ", ";
    }
    sb.append(")");
    return sb.toString();
}
Pedro Pedruzzi
  • 799
  • 7
  • 12