JSF selectItems value from sql query

Question

I want to populate selectOneMenu component with values extracted from database by sql query. Query returns only store names which I want to enter as values to selectOneMenu

I get java.lang.IllegalArgumentException with stack starting with :

java.lang.IllegalArgumentException  at         com.sun.faces.renderkit.SelectItemsIterator.initializeItems(SelectItemsIterator.java:216)
at com.sun.faces.renderkit.SelectItemsIterator.hasNext(SelectItemsIterator.java:135)
at com.sun.faces.renderkit.html_basic.MenuRenderer.renderOptions(MenuRenderer.java:762)

This is my xhtml code (This is the only use of selectItems):

<h:selectOneMenu id="storeName"   value="#{shoplist.store}">  
   <f:selectItems value="#{buyHistory.stores}" />
</h:selectOneMenu>

This is query from buyHistory bean:

public ResultSet getStores() throws SQLException {
...
PreparedStatement getStores = connection.prepareStatement( 
                "SELECT distinct STORE_NAME  "
                + "FROM BuyingHistory ORDER BY STORE_NAME");
CachedRowSet rowSet = new com.sun.rowset.CachedRowSetImpl();
rowSet.populate(getStores.executeQuery());
return rowSet;
}

What am I doing wrong? Should I convert somehow from resultSet to SelectItem array/list?

score 1 · Answer 1 · edited May 23 '17 at 10:25

1

Should I convert somehow from resultSet to SelectItem array/list?

Yes, that's one of the solutions. See also our h:selectOneMenu wiki page. The IllegalArgumentException will be thrown when the value is not an instance of SelectItem, or an array, or Iterable or Map.

Ultimately, your JSF backing beans should be completely free of java(x).sql dependencies. I.e. you should have no single line of import java(x).sql.Something; in your JSF code. Otherwise, it's bad design anyway (tight-coupling). Learn how to create proper DAO classes.

edited May 23 '17 at 10:25

Community

1
1

answered Aug 27 '13 at 12:31

BalusC

1,082,665
372
3,610
3,555

Thank you! What would be a good design? Is it OK to create separate classes to perform SQL queries, and make JSF managed beans call their methods? Where should these queries be located, in what type of classes? Also, is it a good design to put all queries to same database in one class? – qwerty Aug 27 '13 at 13:03
This article may be helpful in designing a "DAO layer" if you're still fiddling with "plain JDBC": http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html But ultimately, you should really start looking at JPA and EJB. See also http://stackoverflow.com/questions/13011392/jsf-service-layer – BalusC Aug 27 '13 at 13:04

score 1 · Answer 2 · answered Aug 27 '13 at 12:31

Why do you think that JSF would know how to transform a ResultSet from the persistence layer? JSF is a presentation layer framework :)

Yes you need to convert it to a SelectItem-List like this:

private List<SelectItem> transformToSelectItems(ResultSet resultSet) {
    List<SelectItem> selectItems = new ArrayList<SelectItem>();

    while(resultSet.next()) {
        String storeName = resultSet.getString("STORE_NAME");
        SelectItem item = new SelectItem(storeName, storeName);
        selectItems.add(item);
    }

    return selectItems;
}

Be sure to notice BalusC's answer. This is just an example of how to construct a dynamic SelectItem-List. But you should definetely not have a ResultSet in your JSF-ManagedBeans.

JSF selectItems value from sql query

2 Answers2