How can I fetch multiple rows from db in Hibernate , Spring, JSP?

Question

I'm designing a hospitality app. and having some problem with fetching multiple rows from database. I'm using Hibernate, Spring Web MVC, mySQL and JSP. I have layers as Controller, Service, Dao, Model. I've designed a search page to see the user profiles according to their city. For example when I write 'NewYork' to the city field on the search screen it will show a list of user profiles who live in NewYork and mark isHosting value as true.

Here is my User class:

public class User{


@Column(unique = true, nullable = false)
private String username;
...
private String city;
private String isHosting;

public boolean isHosting() {
    return hosting;
}


public void setHosting(boolean hosting) {
    this.hosting = hosting;
}

    ...
 }

Search class:

public class Search {

    private String city;
    private String sdate;
    private String fdate;
    private String numOfvisitor;

 ...

}

This my Dao class:

@Repository
public class SearchDao extends GenericDao<User> {

public User findByUserCity(final String city){

    final Criteria c = createCriteria(User.class).add(Restrictions.eq("city", city));

    return (User) c.uniqueResult();
}

}

Service class:

@Service
@Transactional
public class SearchService extends GenericService<User>{

@Autowired
public SearchService(SearchDao dao) {
    super(dao);

}

...

public User findByUserCity(final String city) {
    return ((SearchSurferDao) this.dao).findByUserCity(city);
}

}

And Controller class:

@RequestMapping(value = "/search", method = RequestMethod.GET)
public ModelAndView search(@ModelAttribute Search search) {


    User user = SearchService.findByUserCity(search.getCity());

    ModelAndView result = new ModelAndView("hello");

    ...

    result.addObject("username", user.getUsername());
    return result;
}

I know that I need to write a database query which returns a list and the list is needed to be send to JSP file and with foreach tag I can see profiles on the screen. But how can write a database query to get such a list from db, actually put it in which class? What I need to do in Controller? Where can I check isHosting value?

JB Nizet · Accepted Answer · 2014-04-12 13:01:45Z

4

Criteria c = createCriteria(User.class).add(Restrictions.eq("city", city));
return (User) c.uniqueResult();

The above code does create a query which finds the users with the given city. But it assumes that only one user exists in the given city, which is probably not the case. The method should be

public List<User> findByUserCity(final String city) {
    Criteria c = createCriteria(User.class).add(Restrictions.eq("city", city));
    return c.list();
}

Also, The Criteria API leads to hard to read code, and is suitable when you have to dynamically compose a query based on several search criteria. For such a static query, you should use HQL:

public List<User> findByUserCity(String city) {
    return session.createQuery("select u from User u where u.city = :city")
                  .setString("city", city)
                  .list();
}

answered Apr 12, 2014 at 13:01

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bisorniyesordum Over a year ago

Thank you so much. it works! But I still wonder where can I check isHosting value?

P R · Accepted Answer · 2017-03-20 13:56:03Z

To see only isHosting=true user you have two ways:

Fetch only isHosting=true users
For this, your query will change to :

session.createQuery("select u from User u where u.city = :city and u.isHosting is true")
          .setString("city", city)
          .list();

Fetch all users with matching city, then filter them in your java code.

ArrayList<User> fetchedList=session.createQuery("select u from User u where u.city = :city")
          .setString("city", city)
          .list();
for(User u: fetchedList){
 if(u.isHosting()){
  display(u);
 }
}

Here, I would recommend using first option, as db queries are generally faster than iterating through fetched data on the client side.

However, if you want to have info on all of your users and want to filter isHosting=true users, the 2nd option is better than asking DB again and again.

Collectives™ on Stack Overflow

How can I fetch multiple rows from db in Hibernate , Spring, JSP?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related