ComboBoxSQLContainerFilteredValueChange.java

package com.vaadin.tests.components.combobox;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import com.vaadin.tests.components.TestBase;
import com.vaadin.ui.Label;
import com.vaadin.ui.VerticalLayout;
import com.vaadin.v7.data.Property;
import com.vaadin.v7.data.Property.ValueChangeEvent;
import com.vaadin.v7.data.util.sqlcontainer.SQLContainer;
import com.vaadin.v7.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.v7.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.v7.data.util.sqlcontainer.query.TableQuery;
import com.vaadin.v7.ui.AbstractSelect.Filtering;
import com.vaadin.v7.ui.ComboBox;

public class ComboBoxSQLContainerFilteredValueChange extends TestBase {

    @Override
    protected void setup() {
        VerticalLayout layout = new VerticalLayout();
        addComponent(layout);

        final ComboBox myCombo = new ComboBox("MyCaption");
        layout.addComponent(myCombo);

        final Label selectedLabel = new Label("Selected: null");
        layout.addComponent(selectedLabel);

        try {
            JDBCConnectionPool connectionPool = new SimpleJDBCConnectionPool(
                    "org.hsqldb.jdbc.JDBCDriver",
                    "jdbc:hsqldb:mem:sqlcontainer", "SA", "", 2, 20);

            createTestTable(connectionPool);
            insertTestData(connectionPool);

            TableQuery q = new TableQuery("mytable", connectionPool);
            q.setVersionColumn("version");
            SQLContainer myContainer = new SQLContainer(q);

            myCombo.setContainerDataSource(myContainer);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        myCombo.setItemCaptionPropertyId("MYFIELD");
        myCombo.setFilteringMode(Filtering.FILTERINGMODE_CONTAINS);
        myCombo.setImmediate(true);
        myCombo.setWidth("100.0%");
        myCombo.setHeight("-1px");
        myCombo.addListener(new Property.ValueChangeListener() {
            @Override
            public void valueChange(ValueChangeEvent event) {
                selectedLabel.setValue(
                        "Selected: " + event.getProperty().getValue());
            }
        });
    }

    @Override
    protected String getDescription() {
        return "Selecting the first filtered item should change the value of the label under the ComboBox to 'Selected: 1'.";
    }

    @Override
    protected Integer getTicketNumber() {
        return 10471;
    }

    /**
     * (Re)creates the test table
     *
     * @param connectionPool
     */
    private void createTestTable(JDBCConnectionPool connectionPool) {
        Connection conn = null;
        try {
            conn = connectionPool.reserveConnection();
            Statement statement = conn.createStatement();
            try {
                statement.executeUpdate("DROP TABLE mytable");
            } catch (SQLException e) {
            }
            statement.execute("CREATE TABLE mytable "
                    + "(id INTEGER GENERATED BY DEFAULT AS IDENTITY, "
                    + "MYFIELD VARCHAR(45), " + "PRIMARY KEY(ID))");
            statement.close();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            connectionPool.releaseConnection(conn);
        }
    }

    /**
     * Adds test data to the test table
     *
     * @param connectionPool
     * @throws SQLException
     */
    private void insertTestData(JDBCConnectionPool connectionPool)
            throws SQLException {
        Connection conn = null;
        try {
            conn = connectionPool.reserveConnection();
            Statement statement = conn.createStatement();

            statement.executeUpdate("INSERT INTO mytable VALUES(1, 'A0')");
            statement.executeUpdate("INSERT INTO mytable VALUES(2, 'A1')");
            statement.executeUpdate("INSERT INTO mytable VALUES(3, 'B0')");
            statement.executeUpdate("INSERT INTO mytable VALUES(4, 'B1')");

            statement.close();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            connectionPool.releaseConnection(conn);
        }
    }
}