package com.vaadin.tests.components.table;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Locale;
import com.vaadin.data.Property;
import com.vaadin.data.Property.ValueChangeEvent;
import com.vaadin.data.Property.ValueChangeListener;
import com.vaadin.data.util.sqlcontainer.SQLContainer;
import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.query.TableQuery;
import com.vaadin.server.VaadinRequest;
import com.vaadin.tests.components.AbstractTestUI;
import com.vaadin.ui.CheckBox;
import com.vaadin.ui.Label;
import com.vaadin.ui.Table;
import com.vaadin.ui.VerticalLayout;
public class TableSqlContainer extends AbstractTestUI {
@Override
protected void setup(VaadinRequest request) {
setLocale(Locale.ENGLISH);
VerticalLayout layout = new VerticalLayout();
addComponent(layout);
final Table table = new Table("Table with SQLContainer");
layout.addComponent(table);
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);
table.setContainerDataSource(myContainer);
} catch (SQLException e) {
e.printStackTrace();
}
table.setImmediate(true);
table.setSizeFull();
table.setSelectable(true);
table.addValueChangeListener(new Property.ValueChangeListener() {
@Override
public void valueChange(ValueChangeEvent event) {
selectedLabel.setValue("Selected: "
+ event.getProperty().getValue());
}
});
final CheckBox editMode = new CheckBox("Edit mode");
editMode.addValueChangeListener(new ValueChangeListener() {
@Override
public void valueChange(ValueChangeEvent event) {
table.setEditable(editMode.getValue());
}
});
addComponent(editMode);
}
/**
* (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, D DATE,"
+ "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, '2013-05-24', 'A0')");
statement
.executeUpdate("INSERT INTO mytable VALUES(2, '2013-04-26', 'A1')");
statement
.executeUpdate("INSERT INTO mytable VALUES(3, '2013-05-27', 'B0')");
statement
.executeUpdate("INSERT INTO mytable VALUES(4, '2013-04-28', 'B1')");
statement.close();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
connectionPool.releaseConnection(conn);
}
}
@Override
protected String getTestDescription() {
return "A test with Table connected to a SQLContainer using TableQuery";
}
@Override
protected Integer getTicketNumber() {
return 11224;
}
}