DB4O : SQL Queries

DataNucleus provides a useful extension to db4o, known as sql4o . This is a project written by Travis Reeder. DataNucleus Access Platform provides an updated version licensed under Apache 2, under agreement with the original author, and utilised by the db4o plugin for SQL queries. The documentation below is taken from the original, but with some enhancements to better clarify some features, and add on any DataNucleus extensions.

Supported SQL Syntax

Clearly to support the full range of ANSI SQL would be a significant task. This plugin supports a reasonable subset of the most useful parts of SQL. 

SELECT select_expr, ...
FROM object_type
[WHERE where_condition]
[GROUP BY {group_field_name }]
[ORDER BY {order_field_name } [ASC | DESC], ...]
[LIMIT {[offset,] row_count }]

where

  • select_expr reference fields in an object that you would like returned. If used, it will return an array of objects. If SELECT is given and is anything other than *, then the results will be returned as an array of values, NOT the object.
  • object_type must consist of the fully qualified class name of a single object. (Joins are not yet supported). An alias can be used for example: FROM ObjectName ob
  • where_condition Just as in normal SQL.
  • group_field_name Just as in normal SQL.
  • order_field_name Just as in normal SQL.
  • offset, rowcount To limit the number of records returned
In addition you could select "db4o_id" which is a special keyword supported, to get hold of the internal DB4O object id.





Results

The return values depend on the "select_expr" specified. If select_expr is not present or select_expr equals '*', then all fields of the candidate object will be accessible. If select_expr is present and is not '*', only the fields specified will be accessible.

Queries return a List of org.datanucleus.sql4o.Result objects (List<Result>). This List is an instance of org.datanucleus.sql4o.ObjectSetWrapper which allows access to some information about number of rows, number of columns in each row and the field names for each column. The Result object allows you to access the return values by using one of the getObject() methods

  • Result.getObject(int fieldIndex)
  • Result.getObject(String fieldName)
For simple queries where selecting all fields of the candidate class, Result.getBaseObject() will return the underlying object that the field values come from. This will return null for aggregate queries.





Direct SQL Query Execution

The primary way of executing an SQL query is where you have a db4o ObjectContainer. Here we provide the class Sql4o with an execute method, allowing direct execution of SQL. Obviously all SQL syntax is not supported, but the principal constraints are. Here's an example 

    ObjectContainer cont = {obtain object container}
    ...
    List results = Sql4o.execute(cont, "SELECT * FROM Contact");
    Iterator iter = results.iterator();
    while (iter.hasNext())
    {
        Object obj = iter.next();
        ...
    }


SQL Query Execution via JDBC

An alternative to direct execution is where you want to use the familiar JDBC interface. Here is a code sample, obtaining a Connection and execuying a query. The JDBC URL string follows the following styles

  • Server-based jdbc:db4o://{HOSTNAME}:{PORT}
  • File-based jdbc:db4o:file:{FILENAME} 

    // Load Driver
    Class.forName("org.datanucleus.sql4o.jdbc.Db4oDriver");

    // Get java.sql.Connection
    Connection conn = DriverManager.getConnection("jdbc:db4o://localhost:" + port,
                         username, password);

    // Create java.sql.Statement from Connection
    Statement statement = conn.createStatement();

    // Execute query
    ResultSet rs = stmt.executeQuery("select * from Contact");

    // Iterate over results:
    while(rs.next())
    {
        String name = rs.getString("name");
        int age = rs.getInt("age");
        System.out.println("Got contact: " + name + " age: " + age);
    }

    // Clean up
    rs.close();
    statement.close();
    conn.close(); // only conn.close is required