Select multiple column values with Java ResultSet

Question

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

Syscall · Accepted Answer · 2021-04-20 07:16:20Z

4

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) + " ";
    } 
}

edited Apr 20, 2021 at 7:16

Syscall

19.8k10 gold badges44 silver badges60 bronze badges

answered Aug 6, 2018 at 2:57

Shivang Agarwal

1,9331 gold badge14 silver badges22 bronze badges

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

1 Comment

Mike Zacierka Over a year ago

Yes this works, I didn't realize that the ResultSet returns the complete row and not individual queries. That's why I was incrementing the getString() method which wasn't working as planned. Thanks for the help!

John Bollinger · Accepted Answer · 2018-08-06 03:06:47Z

3

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.

answered Aug 6, 2018 at 3:06

John Bollinger

190k11 gold badges102 silver badges206 bronze badges

1 Comment

Mike Zacierka Over a year ago

Thanks for your response. Your answer clears up all my current questions about the Rows vs Columns. I was confused with the way that the ResultSet works. I am just starting out with Java and SQL. Thanks again

Collectives™ on Stack Overflow

Select multiple column values with Java ResultSet

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related