Java/MySql - getting a row and convert it to a arraylist<String>

Question

I am trying to get a row and convert it to a array I alredy tried to convert the resultset.getArray() to a ArrayList but for some reason it returned nothing somebody help me with this

code now:

public ArrayList<String> getReport(String name) {
    try {
        Statement sql =  mySql.getConnection().createStatement();
        ResultSet resultSet = sql.executeQuery("SELECT * FROM `reports` WHERE `reportedplayer`='" + name + "';");
        if(!resultSet.next()) {
            sql.close();
            resultSet.close();
            return null;
        }
        ArrayList<String> rowValues = new ArrayList<String>();
        while (resultSet.next()) {
            rowValues.add(resultSet.getString("reason"));
        }
        sql.close();
        resultSet.close();
        return rowValues;
    }catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}

How many rows do you have in the reports table? If you only have one row, you will never enter the while loop. — Chetan Kinger
– Chetan Kinger, Commented May 20, 2015 at 16:02
I wonder what would happen if name is "'; drop table reports --". — Peter Tillemans
– Peter Tillemans, Commented May 20, 2015 at 16:04
@Chetan Kinger well sometimes there are more but sometimes 1 — Daan Meijer
– Daan Meijer, Commented May 20, 2015 at 16:05
@DaanSander Does your program fail when there is more than one row? I believe it only fails when you have one row. — Chetan Kinger
– Chetan Kinger, Commented May 20, 2015 at 16:06
The complete block of checking for no results is superfluous and causing problems. If you get no data, you will fall through the while loop reading the data and close the resultset and statement just as well. — Peter Tillemans
– Peter Tillemans, Commented May 20, 2015 at 16:10

Krzysztof Cichocki · Accepted Answer · 2015-05-20 16:17:09Z

You should read column names from resultSet metadata and then iterate over the names for each row. Maybe better idea will be to store the parameters as ArrayList<HashMap<String,String>>than intoArrayList<String>, since select * doesn't say anything about order of the fileds you read. But if you know for sure you have just one row, and you know the order, or maybe the order doesn't matter, you could use something like this:

public ArrayList<String> getReport(String name) {
    try {
        Statement sql =  mySql.getConnection().createStatement();
        ResultSet resultSet = sql.executeQuery("SELECT * FROM `reports` WHERE `reportedplayer`='" + name + "';");
        if(!resultSet.next()) {
            sql.close();
            resultSet.close();
            return null;
        }
        ArrayList<String> rowValues = new ArrayList<String>();
        int columnCount = resultSet.getMetaData().getColumnCount();
        String[] columnNames = new String[columnCount];
        for (int idx=0; idx<columnCount; idx++) {
            columnNames[idx] = resultSet.getMetaData().getColumnName(idx);
        }

        while (resultSet.next()) {
            for (String columnName: columnNames) {
                rowValues.add(resultSet.getString(columnName));
            }
        }
        sql.close();
        resultSet.close();
        return rowValues;
    }catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}

Oli · Accepted Answer · 2015-05-20 16:06:30Z

1

Your line:

if (!resultSet.next())

Is moving to the second row without even looking at the first row. You should also have a finally block to handle closing to ensure that it is always carried out properly.

try {
    Statement sql =  mySql.getConnection().createStatement();
    ResultSet resultSet = sql.executeQuery("SELECT * FROM `reports` WHERE `reportedplayer`='" + name + "';");
    List<String> rowValues = new ArrayList<String>();
    while (resultSet.next()) {
        rowValues.add(resultSet.getString("reason"));
    }
    return rowValues.isEmpty() ? null : rowValues;
} catch (SQLException e) {
    e.printStackTrace();
    return null;
} finally {
    sql.close();
    resultSet.close();
}

answered May 20, 2015 at 16:06

Oli

9198 silver badges16 bronze badges

4 Comments

Chetan Kinger Over a year ago

The if statement will only create a problem if there is only one row in the table. Unless the OP confirms this, your answer is a guess and not actually an answer.

Oli Over a year ago

It's a potential solution to the problem - therefore an answer.

Chetan Kinger Over a year ago

A potential solution is not really the same as a solution. Stackoverflow allows comments for potential solutions.

Oli Over a year ago

Unfortunately comments don't allow code blocks and as well as my potential solution I provided another improvement for the OP which would have been harder to demonstrate without them.

Aradhya · Accepted Answer · 2015-05-20 16:17:57Z

1

resultSet.next() will move the cursor to the next row. 1st time when you call resultSet.next() in the if condition it will point to 1st row and when you again call resultSet.next() in the while condition it will point to the 2nd row.

since you are using the resultSet.next() twice you will be getting the 2nd row onwards into the list. If you have only one row in the table you will not get anything in the list.

You can close the connection in the finally block as shown below.

    finally
    {
        try {              
        sql.close();
        resultSet.close();
        } catch (SQLException e) {
        e.printStackTrace();
        }
    }

answered May 20, 2015 at 16:17

Aradhya

1822 gold badges6 silver badges17 bronze badges

Collectives™ on Stack Overflow

Java/MySql - getting a row and convert it to a arraylist<String>

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related