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

Class DBQuery

java.lang.Object

com.inductiveautomation.ignition.gateway.datasource.query.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.

Nested Class Summary

Nested Classes

Modifier and Type	Class	Description
static class	DBQuery.AndClause
static interface	DBQuery.Clause	A clause that requires parameters to be added to the prepared statement
static interface	DBQuery.Column
static class	DBQuery.OrClause
static class	DBQuery.Param
static class	DBQuery.RawClause
static interface	DBQuery.RowToObject<T>
static interface	DBQuery.SQLClause	An arbitrary part of a SQL query, that knows how to append itself into a larger query.
static interface	DBQuery.StreamingHandler

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

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)	Execute the query represented by this DBQuery, returning a list of rows.
java.util.List<java.util.List<java.lang.Object>>	execute(java.sql.Connection con, boolean closeConnectionAfter)	Execute the query represented by this DBQuery, returning a list of rows.
<T> java.util.List<T>	execute(java.sql.Connection con, boolean closeConnectionAfter, DBQuery.RowToObject<T> handler)
void	execute(java.sql.Connection con, boolean closeConnectionAfter, 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.
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

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:

java.sql.SQLException

execute

public java.util.List<java.util.List<java.lang.Object>> execute(java.sql.Connection con,
                                                                boolean closeConnectionAfter)
                                                         throws java.sql.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:

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 void execute(java.sql.Connection con,
                    boolean closeConnectionAfter,
                    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.

Parameters:

closeConnectionAfter - Whether to close the provided connection after executing the query.

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)

addWhereClause

public DBQuery addWhereClause(DBQuery.Clause c)

getWhereClauses

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

addOrWhereClause

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

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()

getParams

public DBQuery.Param[] getParams()