About query container

Vaadin 6 / 7 / 8 Framework Data Binding
1

I have used the example from demo to implement a select button that is populated by a query container. but the problem is, on a page, the list shows numbers, but not items.

How do i get the select to show items, and not their ids.

here is my implementation

package database;
import com.itmill.toolkit.data.util.QueryContainer;
import com.mysql.jdbc.Connection;

  1. a class that manages database transactions
public class ListQueryControl
{
	private static Connection conn;

	/**
	 * Fetch all regions from database to a query container
	 */
	public static QueryContainer getRegionList()
	{
		QueryContainer qc = null ;		
		try 
		{	
			conn = (Connection) webprojectDAO.connection();
			qc = new QueryContainer(
                    "SELECT region FROM `tanzaniaregions`", conn);
			
			webprojectDAO.closeConnection();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return qc;
	}
	
}
  1. the method that is used to populate the select component
/**
     * Populates select component with  values from regions table.
     * 
     */
    public void initHomeRegions() throws SQLException {
        // init select
    
    	selHomeRegionTz.setItemCaptionPropertyId("region");
        final QueryContainer qc = ListQueryControl.getRegionList();
		
		selHomeRegionTz.setContainerDataSource(qc);
    }
2

By default, the Select component displays the item IDs of the items in the container, and they have nothing to do with the actual items. In a QueryContainer, the item IDs are query result table row numbers, that is, integers beginning from 1.

You need to set the item caption mode to ITEM_CAPTION_MODE_PROPERTY and then set the name of the property to display in the Select list. The name is the SQL query column name.


select.setItemCaptionMode(Select.ITEM_CAPTION_MODE_PROPERTY);
select.setItemCaptionPropertyId("mytablecolumnname");

Below is a complete example:


try {
    // Create a database connection
    Class.forName("org.hsqldb.jdbcDriver");
    final Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:qcexample", "sa", "");
    
    try {
        // Create an example table and put some data in it.
        Statement st = c.createStatement();
        st.executeQuery("CREATE TABLE People (id INTEGER, name VARCHAR)");
        st.close();
        for (int i=0; i<100; i++) {
            st = c.createStatement();
            st.executeQuery("INSERT INTO People (id, name) VALUES ("+i+",'Person "+(i+1)+"')");
            st.close();
        }
    } catch (SQLException e) {
    }
    
    // Query the database
    final QueryContainer qc = new QueryContainer("SELECT id,name FROM People", c);
    
    // Create a component for selecting a query result item.
    Select select = new Select("Select an item");
    
    // The items shown in the selection component are obtained from the query.
    select.setContainerDataSource(qc);
    
    // The item captions are obtained from a field in the query result. 
    select.setItemCaptionMode(Select.ITEM_CAPTION_MODE_PROPERTY);
    
    // Set the name of the field from which the item captions are obtained.
    select.setItemCaptionPropertyId("name");

    // When selection changes, display the selected item.
    select.setImmediate(true);
    final Label selection = new Label("Currently selected: -");
    select.addListener(new ValueChangeListener() {
        public void valueChange(ValueChangeEvent event) {
            // Get the item id of the currently selected item
            Integer sel = (Integer) event.getProperty().getValue();
            
            // Use the item ID to get the actual row from the query result.
            Item qrItem = qc.getItem(sel);
            
            // Display the item ID
            selection.setValue("Currently selected: result row "+sel.intValue() +
                               " (id="+qrItem.getItemProperty("id")+", " +
                               "name="+qrItem.getItemProperty("name")+")");
        }
    });
    
    main.addComponent(select);
    main.addComponent(selection);
} catch (SQLException e) {
    e.printStackTrace();
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}
3

Hmm… I remembered that it would be enough to use:

selHomeRegionTz.setItemCaptionPropertyId("region");

… and the “caption mode” would change automatically.

Maybe the problem here is the order: In your code the data source is assigned after the caption property. However, in Marko’s example you first assign the ds and then the caption property id.