fc.jdbc
Class QueryReader

java.lang.Object
  fc.jdbc.QueryReader

public 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).

  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.*;

  Constructor Summary

  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

  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 java.lang.Object

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

  Constructor Detail

  QueryReader

  public QueryReader(File f)
              throws IOException

  Creates a query reader that reads queries from the specified file, using the UTF-8 encoding and a default logger.

  Throws:

  IOException - on error reading from the file

  QueryReader

  public QueryReader(File f,
                     Log logger)
              throws IOException

  Creates a query reader that reads queries from the specified file, using the UTF-8 encoding and the specified logger.

  Throws:

  IOException - on error reading from the file

  Method Detail

  getQuery

  public String getQuery(String name)

  returns the query with the specified name or null if the query does not exist.

  getQueries

  public Map getQueries()

  returns the entire query map containing all successfully read queries.

  main

  public static void main(String[] args)
                   throws IOException

  Throws:

  IOException