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;  
}

vptheron · Accepted Answer · 2013-04-09 20:42:23Z

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 9, 2013 at 20:42

vptheron

7,4761 gold badge29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2221016 Over a year ago

Thanks! How do I secure my "finally" block? I'm still a beginner at this.

vptheron Over a year ago

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 : stackoverflow.com/questions/4507440/…

user2221016 Over a year ago

Thank you, that link was a great explanation.

duffymo · Accepted Answer · 2013-04-09 20:42:42Z

0

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.

answered Apr 9, 2013 at 20:42

duffymo

310k46 gold badges376 silver badges570 bronze badges

Comments

Adil Shaikh · Accepted Answer · 2013-04-09 20:43:29Z

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 9, 2013 at 20:43

Adil Shaikh

44.8k17 gold badges95 silver badges112 bronze badges

1 Comment

user2221016 Over a year ago

was I just missing "}" so that everything was iterating in the "while" loop? I'm still a beginner, thanks!

IndoKnight · Accepted Answer · 2013-04-09 20:43:36Z

0

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]);
        }

answered Apr 9, 2013 at 20:43

IndoKnight

1,8641 gold badge21 silver badges31 bronze badges

Collectives™ on Stack Overflow

How to Store MySQL query into Java array?

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related