CODEWITHSUNDEEP
javasqlarraysarraylist
Daan Meijer
Daan Meijer

Reputation: 143

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

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

Upvotes: 2

Views: 1509

Answers (3)

Aradhya
Aradhya

Reputation: 182

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

Upvotes: 1

Krzysztof Cichocki
Krzysztof Cichocki

Reputation: 6414

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

Upvotes: 1

Oli
Oli

Reputation: 919

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

Upvotes: 1

Related Questions