fc.jdbc

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 and Description
static int	TEST_FIELD_1
static String	TEST_FIELD_2

Constructor Summary

Constructors

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

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

Field Detail

TEST_FIELD_1

public static final int TEST_FIELD_1

See Also:

Constant Field Values

TEST_FIELD_2

public static final String TEST_FIELD_2

See Also:

Constant Field Values

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