Simple queries to get started

In this example we will demonstrate a few simple queries that should get you started with the fluent Querying API in MetaModel.

We will assume you have a CSV file with the two colums first_name and last_name.

File myFile = new File("persons.csv");

DataContext dataContext = DataContextFactory.createCsvDataContext(myFile);
Schema schema = dataContext.getDefaultSchema();
Table[] tables = schema.getTables();

// CSV files only has a single table in the default schema
assert tables.length == 1;

Table table = tables[0];

// there are several ways to get columns - here we simply get them by name
Column firstNameColumn = table.getColumnByName("first_name");
Column lastNameColumn = table.getColumnByName("last_name");

// use the table and column types in the query
Query q = dataContext.query().from(table).select(firstNameColumn).toQuery();
System.out.println(q.toString());

This will print out:

SELECT first_name FROM persons.csv.persons

Now let's say we want to add a where clause for the lastname. We simple do like this in stead:

Query q = dataContext.query().from(table).select(firstNameColumn).where(lastNameColumn).equals("Doe").toQuery();
System.out.println(q.toString());

This will print out:

SELECT first_name FROM persons.csv.persons WHERE last_name = 'Doe';

Finally let's do some aggregation by adding a GROUP BY clause and COUNT(*) to the select clause:

Query q = dataContext.query().from(table).select(lastNameColumn).selectCount().groupBy(lastNameColumn).orderBy(lastNameColumn).asc().toQuery();
System.out.println(q.toString());

This will print out:

SELECT last_name, COUNT(*) FROM persons.csv.persons GROUP BY last_name ORDER BY last_name ASC;

And the beauty of it is that it will work even on a flat datastore like a CSV file. Here's the query execution code:

DataSet ds = dataContext.executeQuery(q)
while (ds.next()) {
  Row row = ds.getRow();
  String lastName = (String) row.getValue(lastNameColumn);
  Number count = (Number) row.getValue(1);
  System.out.println("row: " + lastName + " occurs " + count + " times");
}

Given an example CSV file this will print out:

row: Elison occurs 2 times
row: Gates occurs 5 times
row: Jobs occurs 3 times