CODEWITHSUNDEEP
javamysqlresultset
tsusanka
tsusanka

Reputation: 4841

Assign ResultSet to variables in class

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?

Upvotes: 3

Views: 10425

Answers (5)

user1055201
user1055201

Reputation: 159

I think this is possible if you use e.g hibernate. But like my precursor write: Don't set ResultSet in constructor of Product, because you will work on another copy of ResultSet (ResultSet is heavy) and if you have many data, that shrunk performance.

So just do:

... 

pArr.put(rs.getInt("id"), new Product(rs.getint("Price") ....));

...

public Product(int price ....) {

Upvotes: 0

Kai
Kai

Reputation: 39632

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.

Upvotes: 3

Thilo
Thilo

Reputation: 262474

Commons DbUtils, which is a thin layer on top JDBC (not as involved as something like Hibernate) can set bean properties from a ResultSet.

Upvotes: 0

kant
kant

Reputation: 157

I guess that using some or mapping library (such as Hibernate) will make code more pleasant. As i heard it makes things common with what you want. However, using it involves replacing all database interaction code in your project. I think that your code is not so ugly, probably doing this makes it a little more pleasant:

 while (rs.next()) {
   pArr.put(rs.getInt("id"), new Product(rs));
   Integer id = rs.getInt("id");
   Integer price = rs.getInt("price");
   Integer content = rs.getString("content");
   Integer intro = rs.getString("intro");
   Product product = new Product(price, content, intro);
   pArr.put(id, product);
 }

Huh, it seems that while i was typing the answer three same answers appeared. Sorry guys :)

Upvotes: 0

mprabhat
mprabhat

Reputation: 20323

You can achieve this with with the help of Reflection, ResultSetMapper does the same.

One tutorial.

Apache BeanUtils has something similar but it returns you DynaBean.

If you can move to more sophisticated and full fleged system you should consider using an ORM like hibernate, JPA.

For normal JDBC either you can come up with your own reflection util or can you use ResultMapper.

Upvotes: 3

Related Questions