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 statementstatic 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 integerstatic 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 queryDBQuery(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'sDBQuery.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.
-
-
-
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:
- setCurrentTableAlias("a")
- addReturnColumn(...)
- setCurrentTableAlias("b")
- addReturnColumn(...)
-
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'sDBQuery.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
-
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()
-
-