Package com.inductiveautomation.ignition.gateway.datasource.query

Class DBQuery

java.lang.Object
com.inductiveautomation.ignition.gateway.datasource.query.DBQuery
public class DBQuery extends 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 Details

  • Constructor Details

    • DBQuery

      public DBQuery(@Nullable String q)
      Create a new query
      Parameters:
      q - The column quote character

    • DBQuery

      public DBQuery(String q, DatabaseVendor vendor)

  • Method Details

    • getQuoteChar

      public String getQuoteChar()

    • setCurrentTableAlias

      public DBQuery setCurrentTableAlias(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 List<List<Object>> execute(Connection con) throws SQLException
      Execute the query represented by this DBQuery, returning a list of rows. Individual columns will be read per the SQLType specified in addReturnColumn(String, SQLType), which can prevent some auto-coercion issues if JDBC drivers implement ResultSet.getObject(int) in an unusual way. Closes the connection provided automatically after executing the query.
      Parameters:
      con - The connection to use.
      Returns:
      A list of lists of rows. Each inner list will have as many elements as columns have been specified on the query.
      Throws:
      SQLException

    • execute

      public List<List<Object>> execute(Connection con, boolean closeConnectionAfter) throws SQLException
      Execute the query represented by this DBQuery, returning a list of rows. Individual columns will be read per the SQLType specified in addReturnColumn(String, SQLType), which can prevent some auto-coercion issues if JDBC drivers implement ResultSet.getObject(int) in an unusual way.
      Parameters:
      con - The connection to use.
      closeConnectionAfter - Whether to close the connection after the query has been executed.
      Returns:
      A list of lists of rows. Each inner list will have as many elements as columns have been specified on the query.
      Throws:
      SQLException

    • execute

      public void execute(Connection con, DBQuery.StreamingHandler handler) throws 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:
      SQLException

    • execute

      public void execute(Connection con, boolean closeConnectionAfter, DBQuery.StreamingHandler handler) throws 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.
      Parameters:
      closeConnectionAfter - Whether to close the provided connection after executing the query.
      Throws:
      SQLException

    • execute

      public <T> List<T> execute(Connection con, boolean closeConnectionAfter, DBQuery.RowToObject<T> handler) throws SQLException
      Throws:
      SQLException

    • addReturnColumn

      public DBQuery addReturnColumn(String column, SQLType type)

    • addTable

      public DBQuery addTable(String tableName)

    • addTableJoin

      public DBQuery addTableJoin(String table1, String table1Alias, String table2, String table2Alias, String expr)

    • addWhereClause

      public DBQuery addWhereClause(DBQuery.Clause c)

    • getWhereClauses

      public List<DBQuery.Clause> getWhereClauses()

    • addOrWhereClause

      public DBQuery addOrWhereClause(DBQuery.Clause... clauses)

    • addGroupBy

      public DBQuery addGroupBy(String column)

    • addOrderBy

      public DBQuery addOrderBy(String column, boolean ascending)

    • equal

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

    • equal

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

    • notequal

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

    • notequal

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

    • gt

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

    • gt

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

    • ge

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

    • ge

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

    • lt

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

    • lt

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

    • le

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

    • le

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

    • hasFlag

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

    • hasFlag

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

    • notFlag

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

    • notFlag

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

    • like

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

    • like

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

    • isnull

      public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator isnull(String column)

    • isnotnull

      public com.inductiveautomation.ignition.gateway.datasource.query.DBQuery.Operator isnotnull(String column)

    • getSql

      public String getSql()

    • getParams

      public DBQuery.Param[] getParams()