src/site/apt/example_simple_queries.apt

 ------
 Simple queries to get started
 ------
 
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
+-------------------------------+