how to get all rows in one column using jdbc

Question

I have 2 column in my teams table id and teamname

this is my method

public List<String> getallteams() {
    Connection conn = null;
    ArrayList users = new ArrayList();
    List<String> teams = new ArrayList();
    try {
        conn = DBConnection.openConnection();

        String sql = " SELECT * FROM teams  ";


        PreparedStatement stmt = conn.prepareStatement(sql);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            String t1 = rs.getString(1);
            String t2 = rs.getString(2);
            String t3 = rs.getString(3);
            String t4 = rs.getString(4);
            String t5 = rs.getString(5);
            String t6 = rs.getString(6);


            String[] finalresult1 = {t1,t2,t3,t4,t5,t6 };



            teams = Arrays.asList(finalresult1);


                System.out.println(teams.get(1));
        }

    } catch (SQLException ex) {
        Logger.getLogger(UsersDaoImp.class.getName()).log(Level.SEVERE, null, ex);

    } finally {

        DBConnection.closeConnection(conn);
    }

    return teams;

}

I'm new to mysqli, so struggling a bit here

but it give me java.sql.SQLException: Column Index out of range, 2 > 1 error

You need to learn to debug your code, Then you can step through it line by line and see what is going on. You don't have 6 columns in your table. That is for sure. There is a lot wrong with your code. — juergen d, Apr 23 '16 at 22:10
Your table doesn't have 6 columns as your code suggests. In fact, the table seems to have only _one_ column based on the error message. Show the table definition and explain what you're trying to accomplish. — Jim Garrison, Apr 23 '16 at 22:15

score 1 · Answer 1 · answered Apr 23 '16 at 22:19

You just said "I have 2 column" (id and name), so don't try to retrieve columns 3, 4, 5, and 6.

while (rs.next()) {
    String id   = rs.getString(1);
    String name = rs.getString(2);
    System.out.println("id=" + id + ", name=" + name);
}

If the second column is named teamname, and you want a list of all team names, then do this:

List<String> teams = new ArrayList<>();
try (Connection conn = DBConnection.openConnection()) {
    String sql = "SELECT teamname FROM teams";
    try (PreparedStatement stmt = conn.prepareStatement(sql);
         ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            teams.add(rs.getString("teamname"));
        }
    }
}

System.out.println("First team: " + teams.get(1));

how to get all rows in one column using jdbc

1 Answers1