Vaadin Web应用开发教程(50): SQLContainer-使用FreeformQuery

在大部分情况下使用TableQuery就可以满足应用要求，如果需要使用复杂查询，比如多表查询，则可以使用FreeformQuery。缺省情况下FreeformQuery为只读，如果需要支持写操作，可以自行实现FreeformQueryDelegate接口。

[java]
// Read-only queries
public StatementHelper getCountStatement()
public StatementHelper getQueryStatement(int offset, int limit)
public StatementHelper getContainsRowQueryStatement(Object... keys)

// Filtering and sorting
public void setFilters(List<Filter> filters)
public void setFilters(List<Filter> filters,
                       FilteringMode filteringMode)
public void setOrderBy(List<OrderBy> orderBys)

// Write support
public int storeRow(Connection conn, RowItem row)
public boolean removeRow(Connection conn, RowItem row)

// Read-only queries
public StatementHelper getCountStatement()
public StatementHelper getQueryStatement(int offset, int limit)
public StatementHelper getContainsRowQueryStatement(Object... keys)

// Filtering and sorting
public void setFilters(List<Filter> filters)
public void setFilters(List<Filter> filters,
FilteringMode filteringMode)
public void setOrderBy(List<OrderBy> orderBys)

// Write support
public int storeRow(Connection conn, RowItem row)
public boolean removeRow(Connection conn, RowItem row)

本例使用多表查询，查询语句如下：

[sql]
SELECT C.FIRSTNAME , C.LASTNAME , INV.ID,INV.TOTAL
FROM CUSTOMER C
INNER JOIN INVOICE INV
ON C.ID=INV.CUSTOMERID;

SELECT C.FIRSTNAME , C.LASTNAME , INV.ID,INV.TOTAL
FROM CUSTOMER C
INNER JOIN INVOICE INV
ON C.ID=INV.CUSTOMERID;

Java 代码

[java]
void openTable(VerticalLayout layout){
    try {
        JDBCConnectionPool pool = new SimpleJDBCConnectionPool(
                "org.hsqldb.jdbc.JDBCDriver",
                "jdbc:hsqldb:file:/hsqldb/data/sample", "SA", "", 2, 5);
        FreeformQuery query = new FreeformQuery(
                "SELECT C.FIRSTNAME , " +
                "C.LASTNAME , INV.ID,INV.TOTAL " +
                "FROM CUSTOMER C " +
                "INNER JOIN INVOICE INV " +
                "ON C.ID=INV.CUSTOMERID;", pool, "ID");
        SQLContainer container = new SQLContainer(query);
        Table table = new Table("All Invoices", container);
        table.setSelectable(true);

        layout.addComponent(table);

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    }

void openTable(VerticalLayout layout){
try {
  JDBCConnectionPool pool = new SimpleJDBCConnectionPool(
    "org.hsqldb.jdbc.JDBCDriver",
    "jdbc:hsqldb:file:/hsqldb/data/sample", "SA", "", 2, 5);
  FreeformQuery query = new FreeformQuery(
    "SELECT C.FIRSTNAME , " +
    "C.LASTNAME , INV.ID,INV.TOTAL " +
    "FROM CUSTOMER C " +
    "INNER JOIN INVOICE INV " +
    "ON C.ID=INV.CUSTOMERID;", pool, "ID");
  SQLContainer container = new SQLContainer(query);
  Table table = new Table("All Invoices", container);
  table.setSelectable(true);

  layout.addComponent(table);

} catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
}
}

到目前为止，基本介绍完SQLContainer的用法，不过整体来说SQLContainer虽然使用简单方便，当功能有限，对应复杂的数据库应用还是可以直接使用JDBC和JPA等。

补充：Web开发 , 其他 ,