Class SQLParser


  • class SQLParser
    extends java.lang.Object
    Process JDBC escape strings and parameter markers in the SQL string.

    This code recognizes the following escapes:

    1. Date {d 'yyyy-mm-dd'}
    2. Time {t 'hh:mm:ss'}
    3. Timestamp {ts 'yyyy-mm-dd hh:mm:ss.nnn'}
    4. ESCAPE {escape 'x'}
    5. Function {fn xxxx([arg,arg...])} NB The concat(arg, arg) operator is converted to (arg + arg)
    6. OuterJoin {oj .....}
    7. Call {?=call proc [arg, arg...]} or {call proc [arg, arg...]}
    Notes:
    1. This code is designed to be as efficient as possible and as result the validation done here is limited.
    2. SQL comments are parsed correctly thanks to code supplied by Joel Fouse.
    Version:
    $Id: SQLParser.java,v 1.29 2007/07/08 17:28:23 bheineman Exp $
    Author:
    Mike Hutchinson
    • Nested Class Summary

      Nested Classes 
      Modifier and Type Class Description
      private static class  SQLParser.CachedSQLQuery
      Serialized version of a parsed SQL query (the value stored in the cache for a parsed SQL).
    • Field Summary

      Fields 
      Modifier and Type Field Description
      private static SimpleLRUCache cache
      LRU cache of previously parsed SQL
      private ConnectionJDBC2 connection
      Connection object for server specific parsing.
      private static java.util.HashMap cvMap
      Map of jdbc to server data types for convert
      private int d
      Current position in output buffer.
      private static byte[] dateMask
      Syntax mask for date escape.
      private static java.util.HashMap fnMap
      Map of jdbc to sybase function names.
      private static boolean[] identifierChar
      Lookup table to test if character is part of an identifier.
      private char[] in
      Input buffer with SQL statement.
      private java.lang.String keyWord
      First SQL keyword or identifier in statement.
      private int len
      Length of input buffer.
      private static java.util.HashMap msFnMap
      Map of jdbc to sql server function names.
      private char[] out
      Output buffer to contain parsed SQL.
      private java.util.ArrayList params
      Parameter list to be populated or null if no parameters are expected.
      private java.lang.String procName
      Procedure name in call escape.
      private int s
      Current position in input buffer.
      private java.lang.String sql
      Original SQL string
      private java.lang.String tableName
      First table name in from clause
      private char terminator
      Current expected terminator character.
      private static byte[] timeMask
      Syntax mask for time escape.
      (package private) static byte[] timestampMask
      Syntax mask for timestamp escape.
    • Constructor Summary

      Constructors 
      Modifier Constructor Description
      private SQLParser​(java.lang.String sqlIn, java.util.ArrayList paramList, ConnectionJDBC2 connection)
      Constructs a new parser object to process the supplied SQL.
    • Method Summary

      All Methods Static Methods Instance Methods Concrete Methods 
      Modifier and Type Method Description
      private void callEscape()
      Processes the JDBC {call procedure [(?,?,?)]} type escape.
      private java.lang.String copyKeyWord()
      Copies over possible SQL keyword eg 'SELECT'
      private void copyLiteral​(java.lang.String txt)
      Inserts a String literal in the output buffer.
      private void copyParam​(java.lang.String name, int pos)
      Builds a new parameter item.
      private java.lang.String copyParamName()
      Copies an embedded parameter name to the output buffer.
      private java.lang.String copyProcName()
      Copies an embedded stored procedure identifier over to the output buffer.
      private void copyString()
      Copies over an embedded string literal unchanged.
      private void copyWhiteSpace()
      Copies over white space.
      private void escape()
      Processes the JDBC escape sequences.
      private void functionEscape()
      Processes the JDBC escape {fn function()}.
      private static SimpleLRUCache getCache​(ConnectionJDBC2 connection)
      Retrieves the statement cache, creating it if required.
      private boolean getDateTimeField​(byte[] mask)
      Utility routine to validate date and time escapes.
      private java.lang.String getTableName()
      Extracts the first table name following the keyword FROM.
      private static boolean isIdentifier​(int ch)
      Determines if character could be part of an SQL identifier.
      private void likeEscape()
      Processes the JDBC escape {escape 'X'}.
      private void mustbe​(char c, boolean copy)
      Checks that the next character is as expected.
      private void outerJoinEscape()
      Processes the JDBC escape {oj left outer join etc}.
      (package private) java.lang.String[] parse​(boolean extractTable)
      Parses the SQL statement processing JDBC escapes and parameter markers.
      (package private) static java.lang.String[] parse​(java.lang.String sql, java.util.ArrayList paramList, ConnectionJDBC2 connection, boolean extractTable)
      Parse the SQL statement processing JDBC escapes and parameter markers.
      private void skipMultiComments()
      Skips multi-line comments
      private void skipSingleComments()
      Skips single-line comments.
      private void skipWhiteSpace()
      Skips embedded white space.
      • Methods inherited from class java.lang.Object

        clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
    • Field Detail

      • cache

        private static SimpleLRUCache cache
        LRU cache of previously parsed SQL
      • sql

        private final java.lang.String sql
        Original SQL string
      • in

        private final char[] in
        Input buffer with SQL statement.
      • s

        private int s
        Current position in input buffer.
      • len

        private final int len
        Length of input buffer.
      • out

        private final char[] out
        Output buffer to contain parsed SQL.
      • d

        private int d
        Current position in output buffer.
      • params

        private final java.util.ArrayList params
        Parameter list to be populated or null if no parameters are expected.
      • terminator

        private char terminator
        Current expected terminator character.
      • procName

        private java.lang.String procName
        Procedure name in call escape.
      • keyWord

        private java.lang.String keyWord
        First SQL keyword or identifier in statement.
      • tableName

        private java.lang.String tableName
        First table name in from clause
      • connection

        private final ConnectionJDBC2 connection
        Connection object for server specific parsing.
      • identifierChar

        private static boolean[] identifierChar
        Lookup table to test if character is part of an identifier.
      • timeMask

        private static final byte[] timeMask
        Syntax mask for time escape.
      • dateMask

        private static final byte[] dateMask
        Syntax mask for date escape.
      • timestampMask

        static final byte[] timestampMask
        Syntax mask for timestamp escape.
      • fnMap

        private static java.util.HashMap fnMap
        Map of jdbc to sybase function names.
      • msFnMap

        private static java.util.HashMap msFnMap
        Map of jdbc to sql server function names.
      • cvMap

        private static java.util.HashMap cvMap
        Map of jdbc to server data types for convert
    • Constructor Detail

      • SQLParser

        private SQLParser​(java.lang.String sqlIn,
                          java.util.ArrayList paramList,
                          ConnectionJDBC2 connection)
        Constructs a new parser object to process the supplied SQL.
        Parameters:
        sqlIn - the SQL statement to parse
        paramList - the parameter list array to populate or null if no parameters are expected
        connection - the parent Connection object
    • Method Detail

      • parse

        static java.lang.String[] parse​(java.lang.String sql,
                                        java.util.ArrayList paramList,
                                        ConnectionJDBC2 connection,
                                        boolean extractTable)
                                 throws java.sql.SQLException
        Parse the SQL statement processing JDBC escapes and parameter markers.
        Parameters:
        extractTable - true to return the first table name in the FROM clause of a select
        Returns:
        The processed SQL statement, any procedure name, the first SQL keyword and (optionally) the first table name as elements 0 1, 2 and 3 of the returned String[].
        Throws:
        java.sql.SQLException - if a parse error occurs
      • getCache

        private static SimpleLRUCache getCache​(ConnectionJDBC2 connection)
        Retrieves the statement cache, creating it if required.
        Returns:
        the cache as a SimpleLRUCache
      • isIdentifier

        private static boolean isIdentifier​(int ch)
        Determines if character could be part of an SQL identifier.

        Characters > 127 are assumed to be unicode letters in other languages than english which is reasonable in this application.

        Parameters:
        ch - the character to test.
        Returns:
        boolean true if ch in A-Z a-z 0-9 @ $ # _.
      • copyLiteral

        private void copyLiteral​(java.lang.String txt)
                          throws java.sql.SQLException
        Inserts a String literal in the output buffer.
        Parameters:
        txt - The text to insert.
        Throws:
        java.sql.SQLException
      • copyString

        private void copyString()
        Copies over an embedded string literal unchanged.
      • copyKeyWord

        private java.lang.String copyKeyWord()
        Copies over possible SQL keyword eg 'SELECT'
      • copyParam

        private void copyParam​(java.lang.String name,
                               int pos)
                        throws java.sql.SQLException
        Builds a new parameter item.
        Parameters:
        name - Optional parameter name or null.
        pos - The parameter marker position in the output buffer.
        Throws:
        java.sql.SQLException
      • copyProcName

        private java.lang.String copyProcName()
                                       throws java.sql.SQLException
        Copies an embedded stored procedure identifier over to the output buffer.
        Returns:
        The identifier as a String.
        Throws:
        java.sql.SQLException
      • copyParamName

        private java.lang.String copyParamName()
        Copies an embedded parameter name to the output buffer.
        Returns:
        The identifier as a String.
      • copyWhiteSpace

        private void copyWhiteSpace()
        Copies over white space.
      • mustbe

        private void mustbe​(char c,
                            boolean copy)
                     throws java.sql.SQLException
        Checks that the next character is as expected.
        Parameters:
        c - The expected character.
        copy - True if found character should be copied.
        Throws:
        java.sql.SQLException - if expected characeter not found.
      • skipWhiteSpace

        private void skipWhiteSpace()
        Skips embedded white space.
      • skipSingleComments

        private void skipSingleComments()
        Skips single-line comments.
      • skipMultiComments

        private void skipMultiComments()
                                throws java.sql.SQLException
        Skips multi-line comments
        Throws:
        java.sql.SQLException
      • callEscape

        private void callEscape()
                         throws java.sql.SQLException
        Processes the JDBC {call procedure [(?,?,?)]} type escape.
        Throws:
        java.sql.SQLException - if an error occurs
      • getDateTimeField

        private boolean getDateTimeField​(byte[] mask)
                                  throws java.sql.SQLException
        Utility routine to validate date and time escapes.
        Parameters:
        mask - The validation mask
        Returns:
        True if the escape was valid and processed OK.
        Throws:
        java.sql.SQLException
      • outerJoinEscape

        private void outerJoinEscape()
                              throws java.sql.SQLException
        Processes the JDBC escape {oj left outer join etc}.
        Throws:
        java.sql.SQLException
      • functionEscape

        private void functionEscape()
                             throws java.sql.SQLException
        Processes the JDBC escape {fn function()}.
        Throws:
        java.sql.SQLException
      • likeEscape

        private void likeEscape()
                         throws java.sql.SQLException
        Processes the JDBC escape {escape 'X'}.
        Throws:
        java.sql.SQLException
      • escape

        private void escape()
                     throws java.sql.SQLException
        Processes the JDBC escape sequences.
        Throws:
        java.sql.SQLException
      • getTableName

        private java.lang.String getTableName()
                                       throws java.sql.SQLException
        Extracts the first table name following the keyword FROM.
        Returns:
        the table name as a String
        Throws:
        java.sql.SQLException
      • parse

        java.lang.String[] parse​(boolean extractTable)
                          throws java.sql.SQLException
        Parses the SQL statement processing JDBC escapes and parameter markers.
        Parameters:
        extractTable - true to return the first table name in the FROM clause of a select
        Returns:
        The processed SQL statement, any procedure name, the first SQL keyword and (optionally) the first table name as elements 0 1, 2 and 3 of the returned String[].
        Throws:
        java.sql.SQLException