CODEWITHSUNDEEP
javamysqlsql
Mike Zacierka
Mike Zacierka

Reputation: 53

Select multiple column values with Java ResultSet

I am trying to select values from a row in my MySQL table. 

SELECT fortnite,psn,steam,twitch,xbox,youtube 
  FROM `social_media` 
  WHERE id = '16483378715464928'

When I try to convert the result into a string, the ResultSet only receives the first "fortnite" row. My question is, how do I retrieve the following columns and put them all into one string to return.

Here is my example code:

    public static String getSocialMedia(String id) {
    String ret = "";
    int i = 1;
    try {
        Statement stmt = null;
        ResultSet resultSet = null;
        getConnection();
        stmt = con.createStatement();
        resultSet = stmt.executeQuery("SELECT fortnite,psn,steam,twitch,xbox,youtube FROM `social_media` WHERE id ='" + id + "'");
        while(resultSet.next()) {
            ret += resultSet.getString(i) + " ";
            i++;
        }
        if(resultSet != null) {
            resultSet.close();
        }
        if(stmt != null) {
            stmt.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return ret;
}

Upvotes: 2

Views: 13854

Answers (2)

Shivang Agarwal
Shivang Agarwal

Reputation: 1923

This is happening due to this.

while(resultSet.next()) {
     ret += resultSet.getString(i) + " ";
     i++; 
}

In the above code inside while you need to fetch all the values either by name or index. next() function gives you the complete row not a single column.

You should change it to:

while(resultSet.next()) {
    for(i = 1, i <=6, i++){           
        ret += resultSet.getString(i) + " ";
    } 
}

Upvotes: 4

John Bollinger
John Bollinger

Reputation: 180103

When i try to convert the result into a string, the ResultSet only receives the first "fortnite" row. My question is, how do i retrieve the following columns and put them all into one string to return.

Terminology is important here, because misunderstanding terminology may lead you to misinterpret documentation. In this case, the important terminology distinction is "row" vs. "column".

Your query SELECTs fortnite,psn,steam,twitch,xbox,youtube. Those six identifiers define six columns that each row in your result set will have. It looks like your particular query is selecting by the table's primary key, so you'll only ever have zero or one row, but other queries can return multiple rows.

You want to extract the values of all six columns of one row, but you iterate while(resultSet.next()), and ResultSet.next() moves the result set to the next row, if any, not the next column. Since you have only one row, the loop terminates after only one iteration.

It looks like you want something more like this:

        if (resultSet.next()) {
            for (i = 1; i <= 6; i++) {
                ret += resultSet.getString(i) + " ";
            }
        }

The if (resultSet.next()) is necessary to move the result set to the first row, and to detect when there isn't any. Then you iterate over the columns of the result, whose number you know statically, based on the query.

Upvotes: 3

Related Questions