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
– Thomas, Commented May 25, 2021 at 10:25
Never build queries like this, used prepared statements with placeholders. This code is ripe for SQL injection attacks. — Goibniu
– Goibniu, Commented May 25, 2021 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
– andreadhelpra, Commented May 25, 2021 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
– andreadhelpra, Commented May 25, 2021 at 11:40

Abra · Accepted Answer · 2021-05-25 12:00:13Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related