Class QueryReader

java.lang.Object
fc.jdbc.QueryReader
public final class QueryReader extends Object
Loads sql queries from a file. Storing queries in a file (when the number of queries is greater than 10 or so, is a lot more manageable than embeddding these as strings in the program).

In the query file:

  • comments start with hash or //
  • // comments can also end a line and anything after a // on a line is ignored.
  • empty lines (just whitespaces) are ignored
  • queries are of format: 
    queryname = querycontent
  ...querycontent continued...
  ;
  • a ';' character by itself (on a separate line) ends a name = content section. The query is stored and can be retrieved via it's name.

  • a special section within the content is enclosed within $...$ and is processed when this file is read. This section (if specified) refers to a corresponding molly DBO class. For example: 

    userDetailQuery = 
  select 
    $package.foo$, $x.y.bar$ from
  ...rest of query...
;
    In this example, package.foo will be replaced by the call to package.fooMgr.columns() and $x.y.bar$ will be replaced by a call to x.y.barMgr.columns(). Note, the names must be fully qualified (that is, including the package name) since otherwise we won't be able to locate the corresponding Mgr class at runtime.

    The $...$ can end with an optional prefix after the ending $, for example, $...$xxx

    If present, the package.fooMgr.columns(prefix) is invoked to get the column names. A prefix other than the default tablename.colname formulation, (such as xxx_colname) is necessary, if the queryname uses a tablename AS xxx abbreviation anywhere in the query (if an abbreviation is used via the SQL AS clause, then that abbreviation must be used everywhere, not the tablename).

  • to access a constant static final class attribute, use $$...$$. This is useful when constant values (declared in code) are to be used in a query. 
    userDetailQuery = 
  select 
    $$x.y.SOME_VAL$$ 
  ...rest of query...
;
    This will replace the value of the java field/attribute x.y.XYZ (in declaring class y located in package x). This method does a straight value interpolation and does not add SQL stringify quotes if they are needed. For String or character types, quotes should be manually placed around the replaced value, such as: 
    userDetailQuery = 
  select 
    "$$x.y.SOME_STRING_VALUE$$"
;

    Note: the fields accessed this way must be delared as final and static. If they are not, then a error will be thrown when processing the query.

    • Here is a real world example of this file in action:

    1. The following file is called my.queries (the name is arbitrary) and stored under WEB-INF/foo/bar 
      # Get all friends of X
#   - get list of friends for X (friend uids from friends table)
# - get info about those friend uids from the users table
userFriends = 
  SELECT 
  u.name, u.status, u.gender, u.likes, u.pictime, u.hbtime,
  f.friend_uid, f.is_liked, f.is_unliked,
  is_friend(u.uid, ?) as twoway
  FROM
  users as u, friends as f
  WHERE
  f.uid = ? 
  and u.uid = f.friend_uid
;

# Get unread messages sent to user X
# Unread messages are grouped by the the sender id and message count per sender
#
unreadMessages = 
  SELECT
  from_uid, count(is_retrieved) as count
  FROM 
  messages 
  WHERE 
  to_uid = ? and is_retrieved = false 
  GROUP BY 
  from_uid
;

# Gets detailed information about place X
placeDetail = 
  SELECT
      $my.dbobj.location$
  FROM 
    location 
  WHERE
  location_id = ?
;
    2. This is then initialized/accessed from a servlet via the following code snippet: 
      try 
  {
  //queryMgr is an instance variable in the servlet (can be later accessed
  //from other methods). webroot is a file pointing to the root directory
  //of this context.

  queryMgr = new QueryReader(
    new File(webroot, "WEB-INF/foo/bar/my.queries"), log); 
  log.info("Available queries: ", queryMgr.getQueries().keySet());
  }
catch (IOException e) {
  throw new ServletException(IOUtil.throwableToString(e));
  }
    3. Note: the placeDetail query in the example file contains the $..$ replaceable text $my.dbobj.location$. This means, that the my.dbobj.* classes should be included in the invoking servlet's classpath, such that the corresponding my.dbobj.locationMgr class is found.

      This would be ensured by putting the following at the top of the servlet code above:

      import my.dbobj.*;

  • Field Summary

    Fields
    Modifier and Type
    Field
    Description
    static final int
    TEST_FIELD_1
     
    static final String
    TEST_FIELD_2
     

  • Constructor Summary

    Constructors
    Constructor
    Description
    QueryReader(File f)
    Creates a query reader that reads queries from the specified file, using the UTF-8 encoding and a default logger.
    QueryReader(File f, Log logger)
    Creates a query reader that reads queries from the specified file, using the UTF-8 encoding and the specified logger.

  • Method Summary

    Modifier and Type
    Method
    Description
    Map
    getQueries()
    returns the entire query map containing all successfully read queries.
    String
    getQuery(String name)
    returns the query with the specified name or null if the query does not exist.
    static void
    main(String[] args)
     

    Methods inherited from class Object

    equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait