How to Store MySQL query into Java array?

Question

I've found suggestions online to store the queried data into an ArrayList and then to convert the ArrayList into an Array. Below is the code that I have and it appears that I'm not doing this correctly. The SQL syntax is correct (I tested in my MySQL). Any suggestions on how to correct my code would be helpful, thanks!

public static void dxNameExerciseID(){
    //String dxName = name;
    //String result = null;

    try{
        con = DriverManager.getConnection(url, user, password);  
        pst = con.prepareStatement("SELECT * FROM exercise,condition_exercise,diagnosis WHERE exercise.exercise_id = condition_exercise.exercise_id_fk AND condition_exercise.diagnosis_id_fk = diagnosis.diagnosis_id AND diagnosis.diagnosis_name = 'Adductor Strain';");
        rs = pst.executeQuery();  

        ArrayList<String> list= new ArrayList<String>();
        while (rs.next()) {
            list.add(rs.getString("exercise_id"));

            String[] result = new String[list.size()];
            result = list.toArray(result);

            for(int i =0; i<result.length; i++){
                System.out.println(result[i]);
            }   
        }   

    }catch(SQLException ex){
    }finally {
        try {
            if (rs != null){
                rs.close();
            }
            if (pst != null){
                pst.close();
            }
            if (con != null){
                con.close();
            }
        }catch(SQLException ex){
        }
    }

//return result;

}

score 3 · Answer 1 · answered Apr 09 '13 at 20:42

3

This should work better. See how you first create your ArrayList of String by iterating over your ResultSet, and once your list is complete you can create the Array of Strings.

ArrayList<String> list= new ArrayList<String>();
while (rs.next()) {
    list.add(rs.getString("exercise_id"));   
} 

String[] result = new String[list.size()];
result = list.toArray(result);

for(int i =0; i<result.length; i++){
    System.out.println(result[i]);
}

BTW: your finally block is unsafe. If rs.close() fails you won't close your connection.

answered Apr 09 '13 at 20:42

vptheron

7,426
25
34

Thanks! How do I secure my "finally" block? I'm still a beginner at this. – user2221016 Apr 09 '13 at 21:07
1

You need to wrap each xxx.close() in its own try/catch to make sure that if one fails it won't stop the execution and let all the other resources open. Have a look at this : http://stackoverflow.com/questions/4507440/must-jdbc-resultsets-and-statements-be-closed-separately-although-the-connection – vptheron Apr 09 '13 at 21:11
Thank you, that link was a great explanation. – user2221016 Apr 09 '13 at 21:21

score 0 · Answer 2 · answered Apr 09 '13 at 20:42

Poor code in every way. Catch blocks should never be empty. You close your resources incorrectly. If something goes wrong, how will you know? Pass the connection in, don't make the method responsible for getting it. Use PreparedStatement. This code should be thrown away so you can start again.

score 0 · Answer 3 · answered Apr 09 '13 at 20:43

0

This should work - You were creating Array on each iteration which was the reason of your problem

while (rs.next()) {
       list.add(rs.getString("exercise_id")); 
}   

String[] result = new String[list.size()];
result = list.toArray(result);
for(int i =0; i<result.length; i++){
      System.out.println(result[i]);
}

answered Apr 09 '13 at 20:43

Adil Shaikh

44,509
17
89
111

was I just missing "}" so that everything was iterating in the "while" loop? I'm still a beginner, thanks! – user2221016 Apr 09 '13 at 21:05

score 0 · Answer 4 · answered Apr 09 '13 at 20:43

Some of your code should be refactored

    ArrayList<String> list= new ArrayList<String>();
    while (rs.next()) {
        list.add(rs.getString("exercise_id"));


    }  
     String[] result = new String[list.size()];
        result = list.toArray(result);

        for(int i =0; i<result.length; i++){
            System.out.println(result[i]);
        }

How to Store MySQL query into Java array?

4 Answers4