How can I pull multiple values from sql database with java.sql?

Question

I have a table of products containing an item's product number and other details, and a ReviewTable with the product number, rating and review. Because an item can have multiple ratings and reviews I need to retrieve all ratings and reviews for that item.

 "drop table ProductTable", // The product table


"create table ProductTable ("+
      "productNo      Char(4)," + // Unique product number
      "description    Varchar(40)," +
      "picture        Varchar(80)," +
      "price          Float)",

  "insert into ProductTable values " +
     "('0001', '40 inch LED HD TV', 'images/pic0001.jpg', 269.00)",

 "drop table ReviewTable",
   "create table ReviewTable ("+ 
   "productNo       Char(4)," +
   "ratingScore     Integer,"  +
   "review          Varchar(200))",
   
   "insert into ReviewTable values ( '0001',  2, 'Very high quality, but I had to replace it after 1 year' )",
   "insert into ReviewTable values ( '0001',  3, 'Very good' )", // Two reviews for the same product

   "select * from ReviewTable, ProductTable " +
              " where ReviewTable.productNo = ProductTable.productNo",

I have a Product object that takes as arguments the product number, an array of all its ratings and an array of its reviews:

 public Product(String aProductNum, double[] aRating, String[] aReviews) {
      theProductNum  = aProductNum;     // Product number
        theRating      = aRating;         // ratings of product
        theReviews     = aReviews;        // All the reviews for the product
}

Finally, I have a getDetails method that retrieves the data about a product, and this is where I need to add multiple values to an array..

  public synchronized Product getDetails( String pNum )
         throws StockException
  {
    try
    {
        String [] reviews = new String[0]; // Initialise reviews
        double [] ratings = new double[0]; // Initialise ratings
      Product   dt = new Product( "0", ratings, reviews); // Initialise product
      ResultSet rs = getStatementObject().executeQuery(
        "select ratingScore, review " +
        "  from ProductTable, ReviewTable " +
        "  where  ProductTable.productNo = '" + pNum + "' " +
        "  and    ReviewTable.productNo  = '" + pNum + "'"
      );
      if ( rs.next() )
      {
        dt.setProductNum( pNum );            
        dt.setRating(rs.getDouble("ratingScore") ); // CURRENTLY PULLING ONLY ONE RATING
        dt.setReviews(rs.getString("review"));  // CURRENTLY PULLING ONLY ONE REVIEW

      }
      rs.close();
      return dt;
    } catch ( SQLException e )
    {
      throw new StockException( "SQL getDetails: " + e.getMessage() );
    }
  }

Any help please? Thanks a lot in advance

`dt.setRating()` etc. seem to set only one value of the `Product` instance you're reusing for the entire query. If you want to retrieve multiple reviews you'd need to provide code for that, e.g. create a `Review` class and a `addReview(Review)` method in `Product` and then call it like `dt.addReview(new Review(rating, review))` with the values extracted from the resultset. Later on you might want to use a proper ORM framework for this but that's how you could start. — Thomas, May 25 '21 at 10:25
Never build queries like this, used prepared statements with placeholders. This code is ripe for SQL injection attacks. — Goibniu, May 25 '21 at 10:27
@Abra I need to pass as arguments an array of all the ratings and one for all the reviews that share the same `productNo` to the `Product` class , sorry if that was not clear. I also changed the comment, my mistake - I don't need the average of the ratings but an array with each individual rating. — andreadhelpra, May 25 '21 at 11:02
@Abra I excluded all unnecessary information from my original code for this question and forgot to delete that bit, apologies. Now it is updated and it should be fine. — andreadhelpra, May 25 '21 at 11:40

score 1 · Accepted Answer · answered May 25 '21 at 12:00

This is a rewrite of method getDetails. Explanations after the code.

Note that this is based entirely and solely on the details in your question.

public synchronized Product getDetails( String pNum )
         throws StockException
{
    String sql = "select ratingScore, review from ReviewTable where productNo = ?";
    try (java.sql.Connection conn = java.sql.DriverManager.getConnection("URL");
         java.sql.PreparedStatement ps = conn.prepareStatement(sql))
    {
        ps.setString(1, pNum);
        ResultSet rs = ps.executeQuery();
        java.util.List<Double> ratings = new java.util.ArrayList<>();
        java.util.List<String> reviews = new java.util.ArrayList<>();
        while ( rs.next() )
        {
            ratings.add(rs.getDouble(1);
            reviews.add(rs.getString(2));
        }
        double[] ratingsArray = ratings.stream().mapToDouble(Double::doubleValue).toArray();
        String[] reviewsArray = reviews.toArray(new String[]{});
        return new Product(pNum, ratingsArray, reviewsArray);
    } catch ( SQLException e )
    {
        throw new StockException( "SQL getDetails: " + e.getMessage() );
    }
}

You should use java.sql.PreparedStatement rather than Statement.
The above code uses try-with-resources since you need to close the PreparedStatement after you finish using it.
Since you only retrieve columns from database table ReviewTable, I changed the SQL query.
Rather than call if (rs.next()), you can use a while loop. That way you can iterate over all the rows in the Resultset.
Since you don't know how many rows there are in the ResultSet, use a List to store details from all the rows.
Since your class Product stores an array and not a List, I convert the Lists to arrays. Note that the code for converting List<Double> to double[] came from this SO question: How to cast from List to double[] in Java?
Now I have the three arguments I need in order to call the constructor of class Product. So I call the constructor and return the object that the constructor returned.

How can I pull multiple values from sql database with java.sql?

1 Answers1