Generates a table based on a given SELECT query, not necessarily from any particular table.
Generates a table based on a given SELECT query, not necessarily from any particular table. The entire SELECT query must be supplied to generate something like "CREATE TABLE destinationTable AS selectQuery". This method is useful for calls to UDFs or stored procedures that might be database-specific and don't conform to selecting columns from a source table.
NOTE: Currently, this works only on certain databases like PostgreSQL, Greenplum, Oracle, MySQL, Teradata, but not MSSQL, which does not support CREATE TABLE ... AS SELECT ....
Table name should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename").
selectQuery is not necessarily a SELECT query, but any query that can feed the CREATE TABLE statement.
query (not necessary SELECT) to be used for CREATE TABLE
name of destination table to be created from SELECT query
Generates a table based on a SELECT query from the specified table, column list, and where clause.
Generates a table based on a SELECT query from the specified table, column list, and where clause.
Table names should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename"). It is acceptable to specify a table join as the sourceTable (i.e. table1 INNER JOIN table2 ...), but in such cases, column names might not be unique and full qualification and aliasing may be required (i.e. "schemaname"."tablename"."columnname" AS "aliasname")
The specified WHERE clause should include the "WHERE" keyword. Note that this is simply SQL that is appended to the query as "SELECT columns FROM sourceTable whereClause" and can include other SQL outside of just a WHERE clause, such as GROUP BY, ORDER BY, etc.
String of comma-separated columns to be SELECTed
name of source table from which we SELECT
name of destination table to be created from SELECT query
where clause of the SELECT query, including the literal "WHERE" keyword
Generates a table based on a SELECT query from the specified table and column list.
Generates a table based on a SELECT query from the specified table and column list.
Table names should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename"). It is acceptable to specify a table join as the sourceTable (i.e. table1 INNER JOIN table2 ...), but in such cases, column names might not be unique and full qualification and aliasing may be required (i.e. "schemaname"."tablename"."columnname" AS "aliasname")
String of comma-separated columns to be SELECTed
name of source table from which we SELECT
name of destination table to be created from SELECT query
Generates a table or view based on a given SELECT query, not necessarily from any particular table.
Generates a table or view based on a given SELECT query, not necessarily from any particular table. The entire SELECT query must be supplied to generate something like "CREATE TABLE destinationTable AS selectQuery" or "CREATE VIEW destinationView AS selectQuery. This method is useful for calls to UDFs or stored procedures that might be database-specific and don't conform to selecting columns from a source table.
NOTE: Currently, table creation does not work on MSSQL, which does not support CREATE TABLE ... AS SELECT ....
Table or view name should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename" or "schemaname"."viewname").
selectQuery is not necessarily a SELECT query, but any query that can feed the CREATE TABLE statement.
query (not necessary SELECT) to be used for CREATE TABLE
name of destination table to be created from SELECT query
true if we are generating a view, false if we are generating a table
Generates a table or view based on a SELECT query from an existing table, column list, and where clause.
Generates a table or view based on a SELECT query from an existing table, column list, and where clause.
Table and view names should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename" or "schemaname"."viewname"). It is acceptable to specify a table join as the sourceTable (i.e. table1 INNER JOIN table2 ...), but in such cases, column names might not be unique and full qualification and aliasing may be required (i.e. "schemaname"."tablename"."columnname" AS "aliasname")
The specified WHERE clause should include the "WHERE" keyword. Note that this is simply SQL that is appended to the query as "SELECT columns FROM sourceTable whereClause" and can include other SQL outside of just a WHERE clause, such as GROUP BY, ORDER BY, etc.
String of comma-separated columns to be SELECTed
name of source table from which we SELECT
name of destination table or view to be created from SELECT query
where clause of the SELECT query, including the literal "WHERE" keyword
true if we are generating a view, false if we are generating a table
Generates a table or view based on a SELECT query from an existing table and column list.
Generates a table or view based on a SELECT query from an existing table and column list.
Table and view names should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename" or "schemaname"."viewname"). It is acceptable to specify a table join as the sourceTable (i.e. table1 INNER JOIN table2 ...), but in such cases, column names might not be unique and full qualification and aliasing may be required (i.e. "schemaname"."tablename"."columnname" AS "aliasname")
String of comma-separated columns to be SELECTed
name of source table from which we SELECT
name of destination table or view to be created from SELECT query
true if we are generating a view, false if we are generating a table
Generates a view based on a given SELECT query, not necessarily from any particular table.
Generates a view based on a given SELECT query, not necessarily from any particular table. The entire SELECT query must be supplied to generate something like "CREATE VIEW destinationTable AS selectQuery". This method is useful for calls to UDFs or stored procedures that might be database-specific and don't conform to selecting columns from a source table.
Table name should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename").
selectQuery is not necessarily a SELECT query, but any query that can feed the CREATE TABLE statement.
query (not necessary SELECT) to be used for CREATE VIEW
name of destination table to be created from SELECT query
Generates a view based on a SELECT query from the specified table, column list, and where clause.
Generates a view based on a SELECT query from the specified table, column list, and where clause.
Table and view names should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename" or "schemaname"."viewname"). It is acceptable to specify a table join as the sourceTable (i.e. table1 INNER JOIN table2 ...), but in such cases, column names might not be unique and full qualification and aliasing may be required (i.e. "schemaname"."tablename"."columnname" AS "aliasname")
The specified WHERE clause should include the "WHERE" keyword. Note that this is simply SQL that is appended to the query as "SELECT columns FROM sourceTable whereClause" and can include other SQL outside of just a WHERE clause, such as GROUP BY, ORDER BY, etc.
String of comma-separated columns to be SELECTed
name of source table from which we SELECT
name of destination view to be created from SELECT query
where clause of the SELECT query, including the literal "WHERE" keyword
Generates a view based on a SELECT query from the specified table and column list.
Generates a view based on a SELECT query from the specified table and column list.
Table and view names should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename" or "schemaname"."viewname"). It is acceptable to specify a table join as the sourceTable (i.e. table1 INNER JOIN table2 ...), but in such cases, column names might not be unique and full qualification and aliasing may be required (i.e. "schemaname"."tablename"."columnname" AS "aliasname")
String of comma-separated columns to be SELECTed
name of source table from which we SELECT
name of destination view to be created from SELECT query
Drops table if it exists, optionally cascade drop all dependent objects
Drops table if it exists, optionally cascade drop all dependent objects
Note: Table name should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename").
Note: Not all databases support cascade drop. In such cases, a DROP TABLE without a CASCADE is attempted.
name of the table to be dropped
if true, drop any dependent objects as well (not supported on all databases)
Drops table if it exists
Drops table if it exists
Note: Table name should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename").
name of the table to be dropped
Drops table or view if it exists, optionally cascade drop all dependent objects.
Drops table or view if it exists, optionally cascade drop all dependent objects. SQLExecutor attempts to drop a table with the given name, and then a view with the given name
Note: Table or view name should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename" or "schemaname"."viewname").
Note: Not all databases support cascade drop. In such cases, a DROP TABLE or DROP VIEW without a CASCADE is attempted.
name of the table or view to be dropped
if true, drop any dependent objects as well (not supported on all databases)
Drops table or view if it exists.
Drops table or view if it exists. SQLExecutor attempts to drop a table with the given name, and then a view with the given name
Note: Table or view name should be fully qualified and delimited, if necessary (i.e. "schemaname"."tablename" or "schemaname"."viewname").
name of the table or view to be dropped
Drops view if it exists, optionally cascade drop all dependent objects
Drops view if it exists, optionally cascade drop all dependent objects
Note: View name should be fully qualified and delimited, if necessary (i.e. "schemaname"."viewname").
Note: Not all databases support cascade drop. In such cases, a DROP VIEW without a CASCADE is attempted.
name of the view to be dropped
if true, drop any dependent objects as well (not supported on all databases)
Drops view if it exists
Drops view if it exists
Note: View name should be fully qualified and delimited, if necessary (i.e. "schemaname"."viewname").
name of the view to be dropped
Executes a query that returns a ResultSet, but transforms it into an array of arrays of Objects.
Executes a query that returns a ResultSet, but transforms it into an array of arrays of Objects.
query to execute, assumes a single ResultSet will be generated from it.
Array of Array of Objects, transformed from ResultSet
Executes a query that returns a ResultSet and applies the ResultSetParser object to it.
Executes a query that returns a ResultSet and applies the ResultSetParser object to it.
Type parameter returned by SQLExecutorResultSetParser
query to execute, assumes a single ResultSet will be generated from it.
SQLExecutorResultSetParser object that takes a ResultSet and parses it to return something
Invocation of resultSetParser on ResultSet, returns object of data type R
Executes a query that returns a ResultSet and applies the function to it.
Executes a query that returns a ResultSet and applies the function to it.
Type parameter returned by resultSetParser
query to execute, assumes a single ResultSet will be generated from it.
function that takes a ResultSet and returns something
Invocation of resultSetParser on ResultSet, returns object of data type R
Executes a DML-like query that INSERTs, UPDATEs, or DELETEs rows.
Executes a DML-like query that INSERTs, UPDATEs, or DELETEs rows.
DML query to execute
number of rows affected or 0
Generates the ColumnDef representations for columns of the given object.
Generates the ColumnDef representations for columns of the given object.
The fully quoted object name.
The ColumnDefs of the object.
Throws exception if the object does not exist.
Returns JDBC Connection object embedded within this SQLExecutor.
Returns JDBC Connection object embedded within this SQLExecutor. This is also the same Connection that this SQLExecutor uses to execute SQL.
the JDBC Connection object embedded within this SQLExecutor.
Returns number of rows from a table or view.
Returns number of rows from a table or view.
name of table or view for which to count rows
number of rows found
Returns the SQLGenerator object used by this SQLExecutor to generate SQL strings.
Returns the SQLGenerator object used by this SQLExecutor to generate SQL strings. SQLGenerator objects are database-type specific, so different SQLGenerator objects are instantiated for different database types.
the SQLGenerator object that is embedded within this SQLExecutor.
Returns the JDBC URL that was used to create the JDBC Connection object that is embedded within this SQLExecutor.
Returns the JDBC URL that was used to create the JDBC Connection object that is embedded within this SQLExecutor. The URL is useful in determining the type of the database and other connection information, but does not include security credentials.
the URL that was used to create the JDBC Connection object embedded within this SQLExecutor.
Checks if table or view exists, and if so, returns true.
Checks if table or view exists, and if so, returns true.
name of table or view to check
true if the table or view does exist, false otherwise
Created by Paul Chang 2016-07-12 (6.1 release)
The purpose of SQLExecutor is to provide an object to facilitate the execution of commonly used SQL queries. All DDL (data definition language) methods are prefixed with "ddl...". These are methods that create, modify, or drop database objects (tables, views, etc.).
Each SQLExecutor is instantiated with a SQLGenerator, URL, and JDBC Connection. The SQLGenerator is used to generate SQL, and the Connection is used to execute SQL. The URL is informational. Several of the methods in SQLGenerator are called by corresponding methods in SQLExecutor and have similar signatures, but unlike SQLGenerator, SQLExecutor will actually execute SQL. SQLGenerator objects are database-type specific, so different SQLGenerator objects are instantiated for different database types.
Once instantiated, the SQLGenerator, URL, and Connection for a SQLExecutor do not change. Aside from database changes, this object has no side-effects once instantiated.
For certain methods, when specifying table names, fully qualify and delimit as necessary (i.e. "schemaname"."tablename"). Delimiting is required for table names with non-standard characters ([A-Za-z][A-Za-z0-9]*). Not all databases support delimiters or non-standard characters (such as Teradata). For those databases that support schemas, specifying the schema name is recommended.
For certain methods, when specifying column names, these must be a comma-separated string of columns that one would find in a SELECT query. This can include expressions that are aliased (i.e. sqlexpression AS aliasname). Any columns with non-standard characters should be delimited (i.e. "columnname"). When SELECTing from more than one table, if column names are not unique, then columns should be fully qualified (i.e. "schemaname"."tablename"."columnname" AS "columnalias").
For certain methods, when specifying a source table, it is permissible to specify more than one table as a join (i.e. table1 INNER JOIN table2 ON ...).
For certain methods, when specifying a whereClause, the literal "WHERE" should be included. Note that we can include anything that follows a FROM clause here, such as GROUP BY, ORDER BY, etc.