I'm trying to my a very simple webapplication, webshop, for cupcakes.

From the webApp you can choose a cupcake form the dropdown with three attributes (top, bottom, quantity). These are stored in an ArrayList on my sessionScope but all in numbers e.g. Chokolate as 1 and Vanilla as 2. I want to use these topId numbers to ask my DB (MySQL) for what is in 1 and then have it return Chokolate.

I think I am almost there with my code, but can't get it to return my String, as my topId is an Int.

    public static Top getTopById(int topId) {
        readFromArrayPutInSQL();

        String sql = "INSERT INTO cupcaketopping (toppingType, toppingPrice) VALUES (?, ?)";

        try {
            ConnectionPool connectionPool = new ConnectionPool();
            String query = "SELECT toppingType FROM cupcaketopping";
            Statement statement = connectionPool.getConnection().createStatement();
            ResultSet rs = statement.executeQuery(query);
            rs.getString(topId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return topId; //Here is the problem - I GUESS?
    }

Code after changes due to input in comments, seem to be working!

public static Top getTopById(int topId) {
        readFromArrayPutInSQL();

        String query = "SELECT toppingType FROM cupcaketopping WHERE toppingID = " topId "";

        try {
            ConnectionPool connectionPool = new ConnectionPool();
            PreparedStatement preparedStatement = connectionPool.getConnection().prepareStatement(query);
            ResultSet rs = preparedStatement.executeQuery(query);
            rs.next();
            return new Top(rs.getString(1));

            //connectionPool.close(); //NOTE! Won't run, IntelliJ is asking me to delete! 
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

CodePudding user response：

There are a few problems:

1. You're selecting all rows from the cupcaketopping table, regardless of the topId. You should probably be using a PreparedStatement, and then use topId as part of your query.

2. You never call ResultSet#next(). The result set always starts "before" the first row. You have to call next() for each row in the result set (it returns true if there is a row to read).

3. The ResultSet#getString(int) method gets the String value of the column at the given index of the result. You only select one column, so the argument should probably be 1 (not topId).

4. You never close the Statement when done with it.

   • Depending on how your connection pool class works, you might actually need to close the Connection instead.

5. You never try to use the String returned by rs.getString(topId).

6. You never try to convert the query result to a Top instance.

7. Given it's possible the query will return no result, you might want to consider making the return type Optional<Top>.

8. The sql string seems to have no purpose.

Your code should look more like this:

public Optional<Top> getTopById(int topId) {
    Connection conn = ...;

    String query = "SELECT toppingType FROM cupcaketopping WHERE id = ?";
    // closes the statement via try-with-resources
    try (PreparedStatement stat = conn.prepareStatement(query)) {
        stat.setInt(1, topId);

        ResultSet rs = stat.executeQuery();
        // assume unique result (as it's assumed the ID is the primary key)
        if (rs.next()) {
            // assumes 'Top' has a constructor that takes a 'String'
            return Optional.of(new Top(rs.getString(1)));
        } else {
            return Optional.empty();
        }
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    }
}

Your actual implementation may vary, depending on how the rest of your code is designed.