sql
Used to generate a series of unique aliases for use in sub-queries.
Created by Paul Chang 2016-07-12 (6.1 release)
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.
Helper interface for Java to parse ResultSet objects.
Helper interface for Java to parse ResultSet objects. For Scala, we simply use executeQuery[R](String, (ResultSet => R)): R
For Java, passing lambda functions is more complicated until Java 8. So instead, we have a helper interface. Create an object that will parse the ResultSet and pass it to SQLExecutor.executeQuery().
The purpose of SQLGenerator is to provide an object to facilitate the construction of commonly used SQL queries.
The purpose of SQLGenerator is to provide an object to facilitate the construction of commonly used SQL queries. Because SQL generation can vary between different database types, each SQLGenerator is instantiated for a particular database type.
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.
Used to generate a series of unique aliases for use in sub-queries. Behaviour is deterministic, for ease of use in tests.
The value of "stem" defaults to "alias". Bear in mind that this should be short to avoid hitting the 30 character limit in Oracle.