Assign ResultSet to variables in class

java, mysql, resultset

Solution

First: You really shouldn't pass the `ResultSet` to the constructor of `Product`! You should cleanly divide your database access code from your business logic.

So typically I would expect to see this in your code:

private static final String TABLE_NAME = "product";
private static final String ID_COLUMN = "id";
private static final String INTRO_COLUMN = "intro";
private static final String CONTENT_COLUMN = "content";
private static final String PRICE_COLUMN = "price";

private static final String FETCHALLPRODUCTS_QUERY = String.format("SELECT %s, %s, %s, %s FROM %s", ID_COLUMN, INTRO_COLUMN, CONTENT_COLUMN, PRICE_COLUMN, TABLE_NAME);

public Map<Integer, Product> fetchAllProducts() {

  Map<Integer, Product> pArr = new HashMap();
  try {
     Statement st = conn.createStatement();
     ResultSet rs = st.executeQuery(FETCHALLPRODUCTS_QUERY);
     while (rs.next()) {
        Integer price = rs.getInt(PRICE_COLUMN);
        String content = rs.getString(CONTENT_COLUMN);
        String intro = rs.getString(INTRO_COLUMN);

        Product product = new Product(price, content, intro);
        Integer id = rs.getInt(ID_COLUMN);
        pArr.put(id, product);
     }
     st.close();
  } catch (SQLException ex) {
   //...
  }
  return pArr;
}

But to answer your question: Doing this is a very common practise when dealing with plain JDBC. What you are looking for is an ORM framework like Hibernate.

One thing I'm doing when using JDBC connection is declaring constants for the column names and table names. That way it is a bit cleaner in my opinion.

Problem

I have a following function: ``` public Map<Integer, Product> fetchAllProducts() { Map<Integer, Product> pArr = new HashMap(); try { Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT id, intro, content, price FROM Product"); while (rs.next()) { pArr.put(rs.getInt("id"), new Product(rs)); } st.close(); } catch (SQLException ex) { //... } return pArr; } ``` which gets all the rows from mySQL table `Product` and for each row creates a new `Product` class. Product constructor: ``` public Product(ResultSet rs) { try { price = rs.getInt("price"); content = rs.getString("content"); intro = rs.getString("intro"); } catch (SQLException ex) { //... } } ``` My question is: is there a better way to assign the result columns to variables in `Product`? The code `price = rs.getInt("price");` and so on seems redundant, doesn't it? Perfect would be, if I change the query statement to `SELECT intro, content, tax, delivery FROM ...` the constructuor would automaticlly assign it to the appropriate variables (i.e. intro, content, tax, delivery) in the constructor. Can this be done in Java or am I just dreaming?

Original source