Cfrgtkky

I am try to get data from a database but it's returning nothing when I just inserted something. I don't know what I am doing wrong. I have followed many tutorials but found nothing working.

    sql = "INSERT INTO optionsetvalue (optionsetid, value, text) VALUES ("+id+", 10000, 'Monday')";

    executeInsert(sql);



    sql = "SELECT * FROM optionsetvalue";

    ResultSet rs = execute(sql, true);

    try {

        while(rs.next())

        {

            System.out.println(rs.getInt("value"));

        }

    } catch (SQLException e) {

        // TODO Auto-generated catch block

        e.printStackTrace();

    }

and I have this method

protected ResultSet execute(String sql, boolean returnResults)

{

    ResultSet results = null;

    if(databaseExists)

    {

        try (Connection conn = DriverManager.getConnection(url);

                Statement stmt = conn.createStatement()) {



            if(returnResults) results = stmt.executeQuery(sql);

            else stmt.execute(sql);



        } catch (SQLException e) {

            System.out.println(e.getMessage());

        }

    }

    return results;

}

What is the mistake I am making? It is definately making it into the stmt.executeQuery method as I have stepped through the code. When I run the rs.Next() it returns false so never gets to the System.out.println.

protected long executeInsert(String sql)

{

    try(

        Connection conn = DriverManager.getConnection(url);

        PreparedStatement statement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

    ) {



        int affectedRows = statement.executeUpdate();



        if (affectedRows == 0) {

            throw new SQLException("Creating user failed, no rows affected.");

        }



        try (ResultSet generatedKeys = statement.getGeneratedKeys()) {

            if (generatedKeys.next()) {

                return generatedKeys.getLong(1);

            }

            else {

                throw new SQLException("Creating user failed, no ID obtained.");

            }

        }

    } catch(SQLException e)

    {

        System.out.println(e.getMessage());

    }

    return -1;

}

You’re using try-with-resource in your execute method which means your connection and result set are closed when you exit the try {}, you need to extract your data directly from the result set and return that data from the method as a custom class or some collection class.

A very simplified example

if(returnResults) {

    results = stmt.executeQuery(sql);

    if (results.next()) {

        value = results.getInt(“value”);

        System.out.println(value);

    }

}