Class DBQuery


  • public class DBQuery
    extends java.lang.Object
    A class that helps construct a dynamic SQL SELECT query, using proper quoting syntax and prepared-statement style parameters. Use by creating a DBQuery, adding return columns, where clauses, etc. When finished, you can either retrieve the SQL and params and execute manually, or use one of the execute functions.
    • Field Summary

      Fields 
      Modifier and Type Field Description
      static DBQuery.RowToObject<java.lang.Integer> TO_INT
      Pulls the first column out of each row as an integer
      static DBQuery.RowToObject<java.lang.String> TO_STRING
      Pulls the first column out of each row as a string
    • Constructor Summary

      Constructors 
      Constructor Description
      DBQuery​(java.lang.String q)
      Create a new query
      DBQuery​(java.lang.String q, DatabaseVendor vendor)  
    • Method Summary

      All Methods Instance Methods Concrete Methods 
      Modifier and Type Method Description
      DBQuery addGroupBy​(java.lang.String column)  
      DBQuery addOrderBy​(java.lang.String column, boolean ascending)  
      DBQuery addOrWhereClause​(DBQuery.Clause... clauses)  
      DBQuery addReturnColumn​(java.lang.String column, SQLType type)  
      DBQuery addTable​(java.lang.String tableName)  
      DBQuery addTableJoin​(java.lang.String table1, java.lang.String table1Alias, java.lang.String table2, java.lang.String table2Alias, java.lang.String expr)  
      DBQuery addWhereClause​(DBQuery.Clause c)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator equal​(java.lang.String column, java.lang.Object value)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator equal​(java.lang.String column, java.lang.Object value, SQLType type)  
      java.util.List<java.util.List<java.lang.Object>> execute​(java.sql.Connection con)  
      <T> java.util.List<T> execute​(java.sql.Connection con, boolean closeConnectionAfter, DBQuery.RowToObject<T> handler)  
      void execute​(java.sql.Connection con, DBQuery.StreamingHandler handler)
      Executes the query represented by this DBQuery, calling the streaming handler's DBQuery.StreamingHandler.onRow(int, ResultSet) function on every row returned be the ResultSet.
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator ge​(java.lang.String column, java.lang.Object value)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator ge​(java.lang.String column, java.lang.Object value, SQLType type)  
      DBQuery.Param[] getParams()  
      java.lang.String getQuoteChar()  
      java.lang.String getSql()  
      java.util.List<DBQuery.Clause> getWhereClauses()  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator gt​(java.lang.String column, java.lang.Object value)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator gt​(java.lang.String column, java.lang.Object value, SQLType type)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator hasFlag​(java.lang.String column, java.lang.Object value)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator hasFlag​(java.lang.String column, java.lang.Object value, SQLType type)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator isnotnull​(java.lang.String column)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator isnull​(java.lang.String column)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator le​(java.lang.String column, java.lang.Object value)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator le​(java.lang.String column, java.lang.Object value, SQLType type)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator like​(java.lang.String column, java.lang.Object value)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator like​(java.lang.String column, java.lang.Object value, SQLType type)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator lt​(java.lang.String column, java.lang.Object value)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator lt​(java.lang.String column, java.lang.Object value, SQLType type)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator notequal​(java.lang.String column, java.lang.Object value)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator notequal​(java.lang.String column, java.lang.Object value, SQLType type)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator notFlag​(java.lang.String column, java.lang.Object value)  
      com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator notFlag​(java.lang.String column, java.lang.Object value, SQLType type)  
      DBQuery setCurrentTableAlias​(java.lang.String value)
      Sets the alias that will be used by subsequent calls to functions that add column based elements.
      • Methods inherited from class java.lang.Object

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

      • TO_STRING

        public static final DBQuery.RowToObject<java.lang.String> TO_STRING
        Pulls the first column out of each row as a string
      • TO_INT

        public static final DBQuery.RowToObject<java.lang.Integer> TO_INT
        Pulls the first column out of each row as an integer
    • Constructor Detail

      • DBQuery

        public DBQuery​(@Nullable
                       java.lang.String q)
        Create a new query
        Parameters:
        q - The column quote character
      • DBQuery

        public DBQuery​(java.lang.String q,
                       DatabaseVendor vendor)
    • Method Detail

      • getQuoteChar

        public java.lang.String getQuoteChar()
      • setCurrentTableAlias

        public DBQuery setCurrentTableAlias​(java.lang.String value)
        Sets the alias that will be used by subsequent calls to functions that add column based elements. For example, if you wanted to select columns from multiple tables, you would do:
        1. setCurrentTableAlias("a")
        2. addReturnColumn(...)
        3. setCurrentTableAlias("b")
        4. addReturnColumn(...)
        Setting the value to "null" means "no alias used".
      • execute

        public java.util.List<java.util.List<java.lang.Object>> execute​(java.sql.Connection con)
                                                                 throws java.sql.SQLException
        Throws:
        java.sql.SQLException
      • execute

        public void execute​(java.sql.Connection con,
                            DBQuery.StreamingHandler handler)
                     throws java.sql.SQLException
        Executes the query represented by this DBQuery, calling the streaming handler's DBQuery.StreamingHandler.onRow(int, ResultSet) function on every row returned be the ResultSet. Closes the connection afterwards.
        Throws:
        java.sql.SQLException
      • execute

        public <T> java.util.List<T> execute​(java.sql.Connection con,
                                             boolean closeConnectionAfter,
                                             DBQuery.RowToObject<T> handler)
                                      throws java.sql.SQLException
        Throws:
        java.sql.SQLException
      • addReturnColumn

        public DBQuery addReturnColumn​(java.lang.String column,
                                       SQLType type)
      • addTable

        public DBQuery addTable​(java.lang.String tableName)
      • addTableJoin

        public DBQuery addTableJoin​(java.lang.String table1,
                                    java.lang.String table1Alias,
                                    java.lang.String table2,
                                    java.lang.String table2Alias,
                                    java.lang.String expr)
      • getWhereClauses

        public java.util.List<DBQuery.Clause> getWhereClauses()
      • addGroupBy

        public DBQuery addGroupBy​(java.lang.String column)
      • addOrderBy

        public DBQuery addOrderBy​(java.lang.String column,
                                  boolean ascending)
      • equal

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator equal​(java.lang.String column,
                                                                                                java.lang.Object value)
      • equal

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator equal​(java.lang.String column,
                                                                                                java.lang.Object value,
                                                                                                SQLType type)
      • notequal

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator notequal​(java.lang.String column,
                                                                                                   java.lang.Object value)
      • notequal

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator notequal​(java.lang.String column,
                                                                                                   java.lang.Object value,
                                                                                                   SQLType type)
      • gt

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator gt​(java.lang.String column,
                                                                                             java.lang.Object value)
      • gt

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator gt​(java.lang.String column,
                                                                                             java.lang.Object value,
                                                                                             SQLType type)
      • ge

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator ge​(java.lang.String column,
                                                                                             java.lang.Object value)
      • ge

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator ge​(java.lang.String column,
                                                                                             java.lang.Object value,
                                                                                             SQLType type)
      • lt

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator lt​(java.lang.String column,
                                                                                             java.lang.Object value)
      • lt

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator lt​(java.lang.String column,
                                                                                             java.lang.Object value,
                                                                                             SQLType type)
      • le

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator le​(java.lang.String column,
                                                                                             java.lang.Object value)
      • le

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator le​(java.lang.String column,
                                                                                             java.lang.Object value,
                                                                                             SQLType type)
      • hasFlag

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator hasFlag​(java.lang.String column,
                                                                                                  java.lang.Object value)
      • hasFlag

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator hasFlag​(java.lang.String column,
                                                                                                  java.lang.Object value,
                                                                                                  SQLType type)
      • notFlag

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator notFlag​(java.lang.String column,
                                                                                                  java.lang.Object value)
      • notFlag

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator notFlag​(java.lang.String column,
                                                                                                  java.lang.Object value,
                                                                                                  SQLType type)
      • like

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator like​(java.lang.String column,
                                                                                               java.lang.Object value)
      • like

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator like​(java.lang.String column,
                                                                                               java.lang.Object value,
                                                                                               SQLType type)
      • isnull

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator isnull​(java.lang.String column)
      • isnotnull

        public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator isnotnull​(java.lang.String column)
      • getSql

        public java.lang.String getSql()