CODEWITHSUNDEEP
javamysqlarrays
user2221016
user2221016

Reputation: 95

How to Store MySQL query into Java array?

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

Upvotes: 3

Views: 11269

Answers (4)

IndoKnight
IndoKnight

Reputation: 1864

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

Upvotes: 0

Adil Shaikh
Adil Shaikh

Reputation: 44740

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

Upvotes: 0

duffymo
duffymo

Reputation: 309028

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.

Upvotes: 0

vptheron
vptheron

Reputation: 7476

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.

Upvotes: 3

Related Questions