Package com.jgcomptech.tools.databasetools.jdbc.builders

Class QueryBuilder

java.lang.Object

com.jgcomptech.tools.databasetools.jdbc.builders.SQLBuilder

com.jgcomptech.tools.databasetools.jdbc.builders.QueryBuilder

public class QueryBuilder
extends SQLBuilder

A builder class for creating a SELECT sql statement to query the database.

Since:

1.4.0

Constructor Summary

Constructors

Constructor	Description
QueryBuilder()

Method Summary

Modifier and Type	Method	Description
TypedStatement	build(Database db)	Runs buildPreparedStatement and passes this object to a new instance of TypedStatement.
java.sql.ResultSet	buildAndExecute(Database db)	Runs buildPreparedStatement, executes the statement and returns the result-set.
int	buildExecuteAndGetNumRows(Database db)	Runs buildPreparedStatement, executes the statement and returns the number of rows in the result-set.
QueryBuilder	FROM(java.lang.String tableName)	Specifies the name of the table to use for the query.
QueryBuilder	ORDER_BY(java.lang.String columnName)	Sorts the result-set in ascending order by the specified column.
QueryBuilder	ORDER_BY(java.lang.String columnName, boolean byDescending)	Sorts the result-set in ascending or descending order by the specified column.
QueryBuilder	SELECT(java.lang.String... columnNames)	This statement returns all rows with only the specified columns from the table.
QueryBuilder	SELECT_ALL()	This statement returns all rows in all columns from the table.
QueryBuilder	SELECT_AVG(java.lang.String columnName)	This statement returns the average of all the values in the specified column.
QueryBuilder	SELECT_COUNT(java.lang.String columnName)	This statement returns the number of rows in the table.
QueryBuilder	SELECT_COUNT_ALL_FROM(QueryBuilder builder)	This statement counts all rows in all columns from the result of the the specified query.
QueryBuilder	SELECT_DISTINCT(java.lang.String... columnNames)	This statement returns all rows with only the specified columns from the table excluding duplicate rows.
QueryBuilder	SELECT_DISTINCT_AVG(java.lang.String columnName)	This statement returns the average of all the values in the specified column excluding duplicate values.
QueryBuilder	SELECT_DISTINCT_COUNT(java.lang.String columnName)	This statement returns the number of rows in the table excluding duplicate rows.
QueryBuilder	SELECT_DISTINCT_SUM(java.lang.String columnName)	This statement returns the sum of all the values in the specified column excluding duplicate values.
QueryBuilder	SELECT_MAX(java.lang.String columnName)	This statement returns the highest value of all the values in the specified column.
QueryBuilder	SELECT_MIN(java.lang.String columnName)	This statement returns the lowest value of all the values in the specified column.
QueryBuilder	SELECT_SUM(java.lang.String columnName)	This statement returns the sum of all the values in the specified column.
QueryBuilder	WHERE(java.lang.String columnName, java.lang.String value)	Specifies a condition to constrain the SELECT statement and if this is not the first WHERE added it is separated by AND.
QueryBuilder	WHERE_BETWEEN(java.lang.String columnName, java.lang.String start, java.lang.String stop)	Specifies a condition to constrain the SELECT statement that checks if the specified column value equals the start or stop values or falls between those values and if this is not the first WHERE added it is separated by AND.
QueryBuilder	WHERE_BETWEEN_OR(java.lang.String columnName, java.lang.String start, java.lang.String stop)	Specifies a condition to constrain the SELECT statement that checks if the specified column value equals the start or stop values or falls between those values and if this is not the first WHERE added it is separated by OR.
QueryBuilder	WHERE_EXISTS(QueryBuilder builder)	Specifies a condition to constrain the SELECT statement that checks if any record exists in the sub query and if this is not the first WHERE added it is separated by AND.
QueryBuilder	WHERE_EXISTS_OR(QueryBuilder builder)	Specifies a condition to constrain the SELECT statement that checks if any record exists in the sub query and if this is not the first WHERE added it is separated by OR.
QueryBuilder	WHERE_IN(java.lang.String columnName, java.lang.String... values)	Specifies a condition to constrain the SELECT statement that checks if the column matches one of the specified values.
QueryBuilder	WHERE_IN_OR(java.lang.String columnName, java.lang.String... values)	Specifies a condition to constrain the SELECT statement that checks if the column matches one of the specified values.
QueryBuilder	WHERE_IS_NOT_NULL(java.lang.String columnName)	Specifies a condition to constrain the SELECT statement that checks if the specified column is NOT NULL and if this is not the first WHERE added it is separated by AND.
QueryBuilder	WHERE_IS_NOT_NULL_OR(java.lang.String columnName)	Specifies a condition to constrain the SELECT statement that checks if the specified column is NOT NULL and if this is not the first WHERE added it is separated by OR.
QueryBuilder	WHERE_IS_NULL(java.lang.String columnName)	Specifies a condition to constrain the SELECT statement that checks if the specified column is NULL and if this is not the first WHERE added it is separated by AND.
QueryBuilder	WHERE_IS_NULL_OR(java.lang.String columnName)	Specifies a condition to constrain the SELECT statement that checks if the specified column is NULL and if this is not the first WHERE added it is separated by OR.
QueryBuilder	WHERE_NOT(java.lang.String columnName, java.lang.String value)	Specifies an inverse condition using NOT to constrain the SELECT statement and if this is not the first WHERE added it is separated by AND.
QueryBuilder	WHERE_NOT_BETWEEN(java.lang.String columnName, java.lang.String start, java.lang.String stop)	Specifies a condition to constrain the SELECT statement that checks if the specified column value does not equal the start or stop values and does not fall between those values and if this is not the first WHERE added it is separated by AND.
QueryBuilder	WHERE_NOT_BETWEEN_OR(java.lang.String columnName, java.lang.String start, java.lang.String stop)	Specifies a condition to constrain the SELECT statement that checks if the specified column value does not equal the start or stop values and does not fall between those values and if this is not the first WHERE added it is separated by OR.
QueryBuilder	WHERE_NOT_EXISTS(QueryBuilder builder)	Specifies a condition to constrain the SELECT statement that checks if no records exist in the sub query and if this is not the first WHERE added it is separated by AND.
QueryBuilder	WHERE_NOT_EXISTS_OR(QueryBuilder builder)	Specifies a condition to constrain the SELECT statement that checks if no records exist in the sub query and if this is not the first WHERE added it is separated by OR.
QueryBuilder	WHERE_NOT_IN(java.lang.String columnName, java.lang.String... values)	Specifies a condition to constrain the SELECT statement that checks if the column does not match one of the specified values.
QueryBuilder	WHERE_NOT_IN_OR(java.lang.String columnName, java.lang.String... values)	Specifies a condition to constrain the SELECT statement that checks if the column does not match one of the specified values.
QueryBuilder	WHERE_NOT_OR(java.lang.String columnName, java.lang.String value)	Specifies an inverse condition using NOT to constrain the SELECT statement and if this is not the first WHERE added it is separated by OR.
QueryBuilder	WHERE_OR(java.lang.String columnName, java.lang.String value)	Specifies a condition to constrain the SELECT statement and if this is not the first WHERE added it is separated by OR.

Methods inherited from class com.jgcomptech.tools.databasetools.jdbc.builders.SQLBuilder

buildPreparedStatement, getSql, getStatement, setStatement, toString

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Constructor Detail

QueryBuilder

public QueryBuilder()

Method Detail

SELECT_ALL

public QueryBuilder SELECT_ALL()

This statement returns all rows in all columns from the table.

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT_COUNT_ALL_FROM

public QueryBuilder SELECT_COUNT_ALL_FROM(QueryBuilder builder)

This statement counts all rows in all columns from the result of the the specified query. The result table will have the column name "FinalCount".

Parameters:

builder - the QueryBuilder containing the sql statement to retrieve results from

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT

public QueryBuilder SELECT(java.lang.String... columnNames)

This statement returns all rows with only the specified columns from the table.

Parameters:

columnNames - the names of the columns to return data from

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT_DISTINCT

public QueryBuilder SELECT_DISTINCT(java.lang.String... columnNames)

This statement returns all rows with only the specified columns from the table excluding duplicate rows.

Parameters:

columnNames - the names of the columns to return data from

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT_COUNT

public QueryBuilder SELECT_COUNT(java.lang.String columnName)

This statement returns the number of rows in the table.

Parameters:

columnName - the name of the column that will be used as the column in the result table

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT_DISTINCT_COUNT

public QueryBuilder SELECT_DISTINCT_COUNT(java.lang.String columnName)

This statement returns the number of rows in the table excluding duplicate rows.

Parameters:

columnName - the name of the column that will be used as the column in the result table

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT_AVG

public QueryBuilder SELECT_AVG(java.lang.String columnName)

This statement returns the average of all the values in the specified column.

Parameters:

columnName - the column to average the values

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT_DISTINCT_AVG

public QueryBuilder SELECT_DISTINCT_AVG(java.lang.String columnName)

This statement returns the average of all the values in the specified column excluding duplicate values.

Parameters:

columnName - the column to average the values

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT_SUM

public QueryBuilder SELECT_SUM(java.lang.String columnName)

This statement returns the sum of all the values in the specified column.

Parameters:

columnName - the column to average the values

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT_DISTINCT_SUM

public QueryBuilder SELECT_DISTINCT_SUM(java.lang.String columnName)

This statement returns the sum of all the values in the specified column excluding duplicate values.

Parameters:

columnName - the column to average the values

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT_MIN

public QueryBuilder SELECT_MIN(java.lang.String columnName)

This statement returns the lowest value of all the values in the specified column.

Parameters:

columnName - the column to average the values

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

SELECT_MAX

public QueryBuilder SELECT_MAX(java.lang.String columnName)

This statement returns the highest value of all the values in the specified column.

Parameters:

columnName - the column to average the values

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a select statement has already been specified

FROM

public QueryBuilder FROM(java.lang.String tableName)

Specifies the name of the table to use for the query.

Parameters:

tableName - the name of the table

Returns:

the instance of the builder to continue building

Throws:

java.lang.IllegalStateException - if a SELECT statement is not called first

WHERE

public QueryBuilder WHERE(java.lang.String columnName,
                          java.lang.String value)

Specifies a condition to constrain the SELECT statement and if this is not the first WHERE added it is separated by AND.

Parameters:

columnName - the column name to check against

value - the expected value

Returns:

the instance of the builder to continue building

WHERE_OR

public QueryBuilder WHERE_OR(java.lang.String columnName,
                             java.lang.String value)

Specifies a condition to constrain the SELECT statement and if this is not the first WHERE added it is separated by OR.

Parameters:

columnName - the column name to check against

value - the expected value

Returns:

the instance of the builder to continue building

WHERE_NOT

public QueryBuilder WHERE_NOT(java.lang.String columnName,
                              java.lang.String value)

Specifies an inverse condition using NOT to constrain the SELECT statement and if this is not the first WHERE added it is separated by AND.

Parameters:

columnName - the column name to check against

value - the expected value

Returns:

the instance of the builder to continue building

WHERE_NOT_OR

public QueryBuilder WHERE_NOT_OR(java.lang.String columnName,
                                 java.lang.String value)

Specifies an inverse condition using NOT to constrain the SELECT statement and if this is not the first WHERE added it is separated by OR.

Parameters:

columnName - the column name to check against

value - the expected value

Returns:

the instance of the builder to continue building

WHERE_IS_NULL

public QueryBuilder WHERE_IS_NULL(java.lang.String columnName)

Specifies a condition to constrain the SELECT statement that checks if the specified column is NULL and if this is not the first WHERE added it is separated by AND.

Parameters:

columnName - the column name to check against

Returns:

the instance of the builder to continue building

WHERE_IS_NOT_NULL

public QueryBuilder WHERE_IS_NOT_NULL(java.lang.String columnName)

Specifies a condition to constrain the SELECT statement that checks if the specified column is NOT NULL and if this is not the first WHERE added it is separated by AND.

Parameters:

columnName - the column name to check against

Returns:

the instance of the builder to continue building

WHERE_IS_NULL_OR

public QueryBuilder WHERE_IS_NULL_OR(java.lang.String columnName)

Specifies a condition to constrain the SELECT statement that checks if the specified column is NULL and if this is not the first WHERE added it is separated by OR.

Parameters:

columnName - the column name to check against

Returns:

the instance of the builder to continue building

WHERE_IS_NOT_NULL_OR

public QueryBuilder WHERE_IS_NOT_NULL_OR(java.lang.String columnName)

Specifies a condition to constrain the SELECT statement that checks if the specified column is NOT NULL and if this is not the first WHERE added it is separated by OR.

Parameters:

columnName - the column name to check against

Returns:

the instance of the builder to continue building

WHERE_IN

public QueryBuilder WHERE_IN(java.lang.String columnName,
                             java.lang.String... values)

Specifies a condition to constrain the SELECT statement that checks if the column matches one of the specified values. and if this is not the first WHERE added it is separated by AND.

Parameters:

columnName - the column name to check against

values - a list of expected values

Returns:

the instance of the builder to continue building

WHERE_NOT_IN

public QueryBuilder WHERE_NOT_IN(java.lang.String columnName,
                                 java.lang.String... values)

Specifies a condition to constrain the SELECT statement that checks if the column does not match one of the specified values. and if this is not the first WHERE added it is separated by AND.

Parameters:

columnName - the column name to check against

values - a list of expected values

Returns:

the instance of the builder to continue building

WHERE_IN_OR

public QueryBuilder WHERE_IN_OR(java.lang.String columnName,
                                java.lang.String... values)

Specifies a condition to constrain the SELECT statement that checks if the column matches one of the specified values. and if this is not the first WHERE added it is separated by OR.

Parameters:

columnName - the column name to check against

values - a list of expected values

Returns:

the instance of the builder to continue building

WHERE_NOT_IN_OR

public QueryBuilder WHERE_NOT_IN_OR(java.lang.String columnName,
                                    java.lang.String... values)

Specifies a condition to constrain the SELECT statement that checks if the column does not match one of the specified values. and if this is not the first WHERE added it is separated by AND.

Parameters:

columnName - the column name to check against

values - a list of expected values

Returns:

the instance of the builder to continue building

WHERE_BETWEEN

public QueryBuilder WHERE_BETWEEN(java.lang.String columnName,
                                  java.lang.String start,
                                  java.lang.String stop)

Specifies a condition to constrain the SELECT statement that checks if the specified column value equals the start or stop values or falls between those values and if this is not the first WHERE added it is separated by AND.

Parameters:

columnName - the column name to check against

start - the inclusive beginning value

stop - the inclusive ending value

Returns:

the instance of the builder to continue building

WHERE_NOT_BETWEEN

public QueryBuilder WHERE_NOT_BETWEEN(java.lang.String columnName,
                                      java.lang.String start,
                                      java.lang.String stop)

Specifies a condition to constrain the SELECT statement that checks if the specified column value does not equal the start or stop values and does not fall between those values and if this is not the first WHERE added it is separated by AND.

Parameters:

columnName - the column name to check against

start - the inclusive beginning value

stop - the inclusive ending value

Returns:

the instance of the builder to continue building

WHERE_BETWEEN_OR

public QueryBuilder WHERE_BETWEEN_OR(java.lang.String columnName,
                                     java.lang.String start,
                                     java.lang.String stop)

Specifies a condition to constrain the SELECT statement that checks if the specified column value equals the start or stop values or falls between those values and if this is not the first WHERE added it is separated by OR.

Parameters:

columnName - the column name to check against

start - the inclusive beginning value

stop - the inclusive ending value

Returns:

the instance of the builder to continue building

WHERE_NOT_BETWEEN_OR

public QueryBuilder WHERE_NOT_BETWEEN_OR(java.lang.String columnName,
                                         java.lang.String start,
                                         java.lang.String stop)

Specifies a condition to constrain the SELECT statement that checks if the specified column value does not equal the start or stop values and does not fall between those values and if this is not the first WHERE added it is separated by OR.

Parameters:

columnName - the column name to check against

start - the inclusive beginning value

stop - the inclusive ending value

Returns:

the instance of the builder to continue building

WHERE_EXISTS

public QueryBuilder WHERE_EXISTS(QueryBuilder builder)

Specifies a condition to constrain the SELECT statement that checks if any record exists in the sub query and if this is not the first WHERE added it is separated by AND.

Parameters:

builder - the QueryBuilder containing the sql statement to retrieve results from

Returns:

the instance of the builder to continue building

WHERE_EXISTS_OR

public QueryBuilder WHERE_EXISTS_OR(QueryBuilder builder)

Specifies a condition to constrain the SELECT statement that checks if any record exists in the sub query and if this is not the first WHERE added it is separated by OR.

Parameters:

builder - the QueryBuilder containing the sql statement to retrieve results from

Returns:

the instance of the builder to continue building

WHERE_NOT_EXISTS

public QueryBuilder WHERE_NOT_EXISTS(QueryBuilder builder)

Specifies a condition to constrain the SELECT statement that checks if no records exist in the sub query and if this is not the first WHERE added it is separated by AND.

Parameters:

builder - the QueryBuilder containing the sql statement to retrieve results from

Returns:

the instance of the builder to continue building

WHERE_NOT_EXISTS_OR

public QueryBuilder WHERE_NOT_EXISTS_OR(QueryBuilder builder)

Specifies a condition to constrain the SELECT statement that checks if no records exist in the sub query and if this is not the first WHERE added it is separated by OR.

Parameters:

builder - the QueryBuilder containing the sql statement to retrieve results from

Returns:

the instance of the builder to continue building

ORDER_BY

public QueryBuilder ORDER_BY(java.lang.String columnName)

Sorts the result-set in ascending order by the specified column.

Parameters:

columnName - the name of the column to sort

Returns:

the instance of the builder to continue building

ORDER_BY

public QueryBuilder ORDER_BY(java.lang.String columnName,
                             boolean byDescending)

Sorts the result-set in ascending or descending order by the specified column.

Parameters:

columnName - the name of the column to sort

byDescending - if true sorts descending otherwise sorts ascending

Returns:

the instance of the builder to continue building

build

public TypedStatement build(Database db)
                     throws java.sql.SQLException

Runs buildPreparedStatement and passes this object to a new instance of TypedStatement.

Specified by:

build in class SQLBuilder

Parameters:

db - the database to request the PreparedStatement object from

Returns:

a new instance of TypedStatement

Throws:

java.sql.SQLException - if a database access error occurs

buildAndExecute

public java.sql.ResultSet buildAndExecute(Database db)
                                   throws java.sql.SQLException

Runs buildPreparedStatement, executes the statement and returns the result-set.

Parameters:

db - the database to request the PreparedStatement object from

Returns:

the result of the statement as a ResultSet

Throws:

java.sql.SQLException - if a database access error occurs

buildExecuteAndGetNumRows

public int buildExecuteAndGetNumRows(Database db)
                              throws java.sql.SQLException

Runs buildPreparedStatement, executes the statement and returns the number of rows in the result-set.

Parameters:

db - the database to request the PreparedStatement object from

Returns:

the result of the statement as the number of rows updated

Throws:

java.sql.SQLException - if a database access error occurs