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 Apache MetaModel.

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

   1 File myFile = new File("persons.csv");
   2 
   3 DataContext dataContext = DataContextFactory.createCsvDataContext(myFile);
   4 Schema schema = dataContext.getDefaultSchema();
   5 Table[] tables = schema.getTables();
   6 
   7 // CSV files only has a single table in the default schema
   8 assert tables.length == 1;
   9 
  10 Table table = tables[0];
  11 
  12 // there are several ways to get columns - here we simply get them by name
  13 Column firstNameColumn = table.getColumnByName("first_name");
  14 Column lastNameColumn = table.getColumnByName("last_name");
  15 
  16 // use the table and column types in the query
  17 Query q = dataContext.query().from(table).select(firstNameColumn).toQuery();
  18 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:

   1 Query q = dataContext.query().from(table).select(firstNameColumn).where(lastNameColumn).equals("Doe").toQuery();
   2 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:

   1 Query q = dataContext.query().from(table).select(lastNameColumn).selectCount().groupBy(lastNameColumn).orderBy(lastNameColumn).asc().toQuery();
   2 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:

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

Given an example CSV file this will print out:

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


CategoryExamples

examples/SimpleQueriesToGetStarted (last edited 2013-07-17 21:48:21 by KasperSorensen)