如何在番石榴中表示SQL表并选择查询？

Question

考虑一个典型的Customers表：

​

​

和一个典型的SQL模式匹配查询：

SELECT FirstName, LastName, City 

FROM Customers 

WHERE FirstName LIKE '%ab%'

OR LastName LIKE '%an%'

如何使用Google库、表、集合和谓词来表示此表和SELECT查询

编辑

正如mfulton26所提到的，番石榴表可能不是与数据库表等效的最理想的数据结构。

因此，哪种数据结构是内存中数据表最合适的数据结构，能够提供：

1)迭代(可能使用迭代器)

2)过滤器(可能带有谓词)

3)具有快速访问索引的多个数据列。

Answer 1

请注意，SQL表通常不会映射到番石榴表。番石榴的表用于当您需要两个索引时(请参阅NewCollectionTypesExplained·谷歌/番石榴Wiki)。SQL结果集通常表示为简单的Collection或Iterable。

尽管如此，下面是如何“查询”番石榴表：

Java 8

table.rowMap().values().stream().filter(row -> {
    return row.get("FirstName").contains("ab") || row.get("LastName").contains("an");
});

Java 7/6

FluentIterable.from(table.rowMap().values()).filter(new Predicate<Map<String, String>>() {
    @Override
    public boolean apply(Map<String, String> row) {
        return row.get("FirstName").contains("ab") || row.get("LastName").contains("an");
    }
});

还请注意，Guava的表不像数据库表，可以添加额外的索引等等，您只能得到两个索引:行和列。

Answer 2

表

public class CustomerTable {

    public enum Column {
        FIRST_NAME, LAST_NAME, ADDRESS_LINE1, CITY, STATE_PROVINCE_CD, POSTAL_CODE;
    }

    private Table<Integer, Column, String> table = HashBasedTable.create();

    @Override
    public String toString() {
        return table.toString();
    }

    public void createRow(String[] values) {
        if (Column.values().length != values.length) {
            throw new IllegalArgumentException();
        }
        Integer rowNum = table.rowKeySet().size() + 1;
        for(int i=0; i < values.length; i ++) {
            table.put(rowNum, Column.values()[i], values[i]);
        }
    }

    public Table<Integer, Column, String> query(Predicate<Map<Column, String>> query) {
        return query(query, allOf(Column.class));
    }

    public Table<Integer, Column, String> query(Predicate<Map<Column, String>> query, EnumSet<Column> columns) {
        Map<Integer, Map<Column, String>> filtered = Maps.filterValues(table.rowMap(), query);
        return createResultTable(filtered, columns);
    }

    private Table<Integer, Column, String> createResultTable(Map<Integer, Map<Column, String>> resultMap, final EnumSet<Column> columns) {

        int i = 0;
        Table<Integer, Column, String> result = HashBasedTable.create();
        for (Map<Column, String> row : resultMap.values()) {
            i++;
            for (Column column : row.keySet()){
                if (columns.contains(column)) {
                    result.put(i, column, row.get(column));
                }
            }
        }
        return result;
    }

谓词

class LikePredicate implements Predicate<Map<CustomerTable.Column, String>> {

    private Column column;
    private String value;

    public LikePredicate(Column column, String value) {
        this.column = column;
        this.value = value;
    }

    @Override
    public boolean apply(Map<Column, String> input) {
        return input.get(column) != null && input.get(column).contains(value);
    }

    public static LikePredicate like(Column column, String value) {
        return new LikePredicate(column, value);
    }
}

使用示例

public static void main(String[] args) {

    CustomerTable customerTable = new CustomerTable();
    customerTable.createRow(new String[]{"Ben", "Miller", "101 Candy Rd.", "Redmond", "WA", "98052"});
    customerTable.createRow(new String[]{"Garret", "Vargas", "10203 Acorn Avenue", "Calgary", "AB", "T2P 2G8"});
    //Create other rows or read rows from a file


    Table<Integer, Column, String> result;
    /*
    SELECT FirstName, LastName, City 
    FROM Customers 
    WHERE FirstName LIKE '%ab%'
          OR LastName LIKE '%an%'
    */
    result = customerTable.query(or(like(Column.FIRST_NAME, "ab"), like(Column.LAST_NAME, "an")),
            EnumSet.of(Column.FIRST_NAME, Column.LAST_NAME, Column.CITY));

    System.out.println(result);
}