How to retrieve values from SQL when ID is in array form

Question

The function below will pick the highest value and it will display value which are in column place1(in table placeseen) as output based on the ID.So far I only can get the highest value but not the value in place1. I don't know what's wrong with my coding because the output is always shows empty.

   private void pick_highest_value_here_and_display(ArrayList<Double> value) throws Exception {
            // TODO Auto-generated method stub
            double aa[]=value.stream().mapToDouble(v -> v.doubleValue()).toArray();
            double highest=aa[0+1];
            for(int i=0;i<aa.length;i++)
            {
                if(aa[i]>highest){
                    highest=aa[i];
                    String sql ="Select* from placeseen where ID =aa[i]";
                    DatabaseConnection db = new DatabaseConnection();
                    Connection  conn =db.getConnection();
                    PreparedStatement  ps = conn.prepareStatement(sql);
                    ResultSet rs = ps.executeQuery();
                    if (rs.next()) 
                    {  
                    String aaa;
                    aaa=rs.getString("place1");
                    System.out.println(aaa);
                    }
                    ps.close();
                    rs.close();
                    conn.close();
                }

            }

            System.out.println(highest);
        }

use try catch you have an sql error `select*` and also `a[i]` should be outside the double inverted commas — singhakash, Jul 31 '15 at 06:12
I have tried with your method but still can't get the aaa value.. — John Joe, Jul 31 '15 at 06:23

score 1 · Answer 1 · edited May 23 '17 at 11:45

1

instead of

  String sql ="Select * from placeseen where ID =aa[i]";//aa[i] taking a value

use

  String sql ="Select place1 from placeseen where ID =?";
  PreparedStatement ps = conn.prepareStatement(sql);
  ps.setDouble(1, aa[i]);

passing aa[i] variable value .

Avoid sql injection

edited May 23 '17 at 11:45

Community

1
1

answered Jul 31 '15 at 06:12

SatyaTNV

4,137
3
15
31

is the given condition is true? @user5156075 – SatyaTNV Jul 31 '15 at 06:24
The placeseen table has 5 column, which are ID, place1,place2,place3 and place4..I want display place1 only – – John Joe Jul 31 '15 at 06:26

Naman Gala · Accepted Answer · 2015-07-31T06:36:11.823

0

You can try this

// as you are using preparedStatement you can use ? and then set value for it to prevent sql injection
String sql = "Select * from placeseen where ID = ?";
DatabaseConnection db = new DatabaseConnection();
Connection conn = db.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ps.setDouble(1, aa[i]);  // 1 represent first attribute represented by ?

System.out.println(ps); // this will print query in console

ResultSet rs = ps.executeQuery();
if (rs.next()) {
    System.out.println("Inside rs.next()");  // for debug purpose
    String aaa;
    aaa=rs.getString("place1");
    System.out.println(aaa);
}
// remaining code

edited Jul 31 '15 at 06:36

answered Jul 31 '15 at 06:18

Naman Gala

4,670
1
21
55

Do one thing, just sysout your preparedStatement after setting values, you will get query and they try to execute in mysql directly. See my updated answer. – Naman Gala Jul 31 '15 at 06:24
I still getting the same output..which is the highest value only – John Joe Jul 31 '15 at 07:11
did you get the query in your console? – Naman Gala Jul 31 '15 at 07:14
I get this "com.mysql.jdbc.JDBC4PreparedStatement@151376dc: Select Place1 from placeseen where ID = 0.9805806756909202"....I have changed * to Place1 – John Joe Jul 31 '15 at 07:18
did you try to execute it in mysql directly? – Naman Gala Jul 31 '15 at 07:21
ya..I think i should use i instead of aa[i]..aa[i] gives me 0.9805806756909202 but [i] give me 1 – John Joe Jul 31 '15 at 07:24
what do you want to use `aa[i]` or only `i`? What is your requirement? – Naman Gala Jul 31 '15 at 07:26
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/84768/discussion-between-user5156075-and-naman-gala). – John Joe Jul 31 '15 at 07:29

How to retrieve values from SQL when ID is in array form

2 Answers2