Package jslx.dbutilities.dbutil

Interface DatabaseIfc

  • All Known Implementing Classes:
    Database
    public interface DatabaseIfc
    Many databases define the terms database, user, schema in a variety of ways. This abstraction defines this concept as the userSchema. It is the name of the organizational construct for which the user defined database object are contained. These are not the system abstractions. The database name provided to the construct is for labeling and may or may not have any relationship to the actual file name or database name of the database. The supplied connection has all the information that it needs to access the database.

    • Nested Class Summary

      Nested Classes 
      Modifier and Type Interface Description
      static class  DatabaseIfc.LineOption  

    • Method Summary

      All Methods Static Methods Instance Methods Abstract Methods Default Methods 
      Modifier and Type Method Description
      default boolean areAllTablesEmpty​(java.lang.String schemaName)  
      default boolean containsSchema​(java.lang.String schemaName)  
      default boolean containsSchema​(org.jooq.Schema schema)  
      default boolean containsTable​(java.lang.String tableName)  
      default boolean containsTable​(java.lang.String schemaName, java.lang.String table)
      Checks if the supplied table exists in the schema
      default boolean containsTable​(org.jooq.Table<?> table)  
      default DbCreateTask.DbCreateTaskFirstStepIfc create()  
      default org.jooq.ResultQuery<org.jooq.Record> createResultQuery​(java.lang.String sql)
      A simple wrapper to ease the use of jooq for novices.
      default void dropSchema​(java.lang.String schemaName, java.util.List<java.lang.String> tableNames, java.util.List<java.lang.String> viewNames)
      Drops the named schema from the database.
      default boolean executeCommand​(java.lang.String cmd)
      Executes a single command on an database connection
      default boolean executeCommand​(java.sql.Connection con, java.lang.String cmd)
      Executes the SQL provided in the string.
      default boolean executeCommands​(java.util.List<java.lang.String> cmds)
      Consecutively executes the list of SQL queries supplied as a list of strings The strings must not have ";" semi-colon at the end.
      default boolean executeScript​(java.nio.file.Path path)
      Executes the commands in the script on the database
      default java.sql.ResultSet fetchJDBCResultSet​(java.lang.String sql)
      A simple wrapper to ease the use of JDBC for novices.
      default org.jooq.Result<org.jooq.Record> fetchResults​(java.lang.String sql)
      A simple wrapper to ease the use of jooq for novices.
      default java.util.List<java.lang.String> getAllTableNames()  
      default java.sql.Connection getConnection()
      It is best to use this method within a try-with-resource construct This method calls the DataSource for a connection.
      default java.sql.DatabaseMetaData getDatabaseMetaData()  
      javax.sql.DataSource getDataSource()  
      default org.jooq.Schema getDefaultSchema()  
      java.lang.String getDefaultSchemaName()  
      org.jooq.DSLContext getDSLContext()  
      default java.lang.String getInsertQueries​(java.lang.String tableName)  
      default java.lang.String getInsertQueries​(org.jooq.Table<? extends org.jooq.Record> table)  
      java.lang.String getLabel()  
      default org.jooq.Schema getSchema​(java.lang.String schemaName)
      The name of the schema is first checked for an exact lexicographical match.
      org.jooq.SQLDialect getSQLDialect()  
      static java.util.Optional<org.jooq.SQLDialect> getSQLDialect​(javax.sql.DataSource dataSource)
      Attempts to determine the SQLDialect for the data source Reference to JDBCUtils
      default org.jooq.Table<?> getTable​(java.lang.String tableName)
      The name of the table is first checked for an exact lexicographical match.
      default org.jooq.Table<?> getTable​(java.lang.String schemaName, java.lang.String tableName)  
      default org.jooq.Table<?> getTable​(org.jooq.Schema schema, java.lang.String tableName)
      The name of the table is first checked for an exact lexicographical match.
      default java.util.List<java.lang.String> getTableNames​(java.lang.String schemaName)  
      default java.util.List<org.jooq.Table<?>> getTables​(java.lang.String schemaName)  
      default boolean hasData​(java.lang.String schemaName)  
      default boolean hasTables​(java.lang.String schemaName)
      Checks if tables exist in the specified schema
      default boolean isJooQDefaultExecutionLoggingOn()  
      default boolean isTableEmpty​(java.lang.String table)  
      default boolean isTableEmpty​(org.jooq.Table<? extends org.jooq.Record> table)  
      static void jooqCodeGeneration​(java.nio.file.Path pathToCreationScript, java.lang.String schemaName, java.lang.String pkgDirName, java.lang.String packageName)
      Runs jooq code generation on the database at the supplied creation script.
      static void jooqCodeGenerationDerbyDatabase​(javax.sql.DataSource dataSource, java.lang.String schemaName, java.lang.String pkgDirName, java.lang.String packageName)
      Runs jooq code generation on the database at the supplied path.
      static void logWarnings​(java.sql.Connection conn)
      Writes SQLWarnings to log file
      static boolean parseCommandString​(java.lang.String input, java.lang.StringBuilder command)
      Takes the input string and builds a string to represent the command from the string.
      static DatabaseIfc.LineOption parseLine​(java.lang.String line, java.lang.StringBuilder command)
      Takes the input string and builds a string to represent the SQL command from the string.
      static DatabaseIfc.LineOption parseLine​(java.lang.String line, java.lang.String delimiter, java.lang.StringBuilder command)
      Takes the input string and builds a string to represent the SQL command from the string.
      static java.util.List<java.lang.String> parseQueriesInSQLScript​(java.nio.file.Path filePath)
      Method to parse a SQL script for the database.
      static java.util.List<java.lang.String> parseQueriesInString​(java.lang.String str)
      Parses the supplied string and breaks it up into a list of strings The string needs to honor SQL comments and separates each SQL command into a list of strings, 1 string for each command.
      default void printAllTablesAsInsertQueries​(java.lang.String schemaName)
      Prints all table data as insert queries to the console
      default void printAllTablesAsText​(java.lang.String schemaName)
      Prints all tables as text to the console
      default void printInsertQueries​(java.lang.String tableName)
      Prints the insert queries associated with the supplied table to the console
      default void printTableAsCSV​(java.lang.String tableName)
      Prints the table as comma separated values to the console
      default void printTableAsText​(java.lang.String tableName)
      Prints the table as prettified text to the console
      static void runJooQCodeGenerationDerbyDatabase​(javax.sql.DataSource dataSource, java.lang.String schemaName, java.lang.String pkgDirName, java.lang.String packageName)
      Runs jooq code generation on the database at the supplied path.
      default org.jooq.Result<org.jooq.Record> selectAll​(java.lang.String tableName)  
      default org.jooq.Result<org.jooq.Record> selectAll​(org.jooq.Table<? extends org.jooq.Record> table)  
      void setDefaultSchemaName​(java.lang.String name)
      Sets the name of the default schema
      default void setJooQDefaultExecutionLoggingOption​(boolean option)  
      default void writeAllTablesAsCSV​(java.lang.String schemaName, java.nio.file.Path pathToOutPutDirectory)
      Writes all tables as separate comma separated value files into the supplied directory.
      default void writeAllTablesAsInsertQueries​(java.lang.String schemaName, java.io.PrintWriter out)
      Writes all table data as insert queries to the PrintWriter
      default void writeAllTablesAsText​(java.lang.String schemaName, java.io.PrintWriter out)
      Writes all tables as text
      default void writeDbToExcelWorkbook​(java.lang.String schemaName)
      Writes all the tables to an Excel workbook, uses name of schema, uses the working directory
      default void writeDbToExcelWorkbook​(java.lang.String schemaName, java.lang.String wbName)
      Writes all the tables to an Excel workbook uses the working directory
      default void writeDbToExcelWorkbook​(java.lang.String schemaName, java.lang.String wbName, java.nio.file.Path wbDirectory)
      Writes all the tables in the supplied schema to an Excel workbook
      default void writeDbToExcelWorkbook​(java.lang.String schemaName, java.nio.file.Path wbDirectory)
      Writes all the tables to an Excel workbook, uses name of database
      default void writeDbToExcelWorkbook​(java.util.List<java.lang.String> tableNames, java.lang.String wbName, java.nio.file.Path wbDirectory)
      Writes the tables in the supplied list to an Excel workbook, if they exist in the database.
      default void writeInsertQueries​(java.lang.String tableName, java.io.PrintWriter out)
      Writes the insert queries associated with the supplied table to the PrintWriter
      default void writeInsertQueries​(org.jooq.Table<? extends org.jooq.Record> table, java.io.PrintWriter out)
      Writes the insert statements for the table in the file
      default void writeTableAsCSV​(java.lang.String tableName, java.io.PrintWriter out)
      Writes the table as comma separated values
      default void writeTableAsText​(java.lang.String tableName, java.io.PrintWriter out)
      Writes the table as prettified text

    • Field Detail

      • LOGGER

        static final org.slf4j.Logger LOGGER

      • NEW_DELIMITER_PATTERN

        static final java.util.regex.Pattern NEW_DELIMITER_PATTERN

      • COMMENT_PATTERN

        static final java.util.regex.Pattern COMMENT_PATTERN

    • Method Detail

      • getDataSource

        javax.sql.DataSource getDataSource()
        Returns:
        the DataSource backing the database

      • getLabel

        java.lang.String getLabel()
        Returns:
        an identifying string representing the database. This has no relation to the name of the database on disk or in the dbms. The sole purpose is for labeling of output

      • getSQLDialect

        org.jooq.SQLDialect getSQLDialect()
        Returns:
        the jooq SQL dialect for the database

      • getDSLContext

        org.jooq.DSLContext getDSLContext()
        Returns:
        the jooq DSLContext for manipulating this database

      • getDefaultSchemaName

        java.lang.String getDefaultSchemaName()
        Returns:
        a String that represents the name of the default schema for the database. This is the schema that contains the database objects such as the tables. This may be null if no default schema is specified.

      • setDefaultSchemaName

        void setDefaultSchemaName​(java.lang.String name)
        Sets the name of the default schema
        Parameters:
        name - the name for the default schema, may be null

      • getDefaultSchema

        default org.jooq.Schema getDefaultSchema()
        Returns:
        a jooq Schema representing the default schema that holds the user defined tables that are in the database, or null if no default schema is specified

      • setJooQDefaultExecutionLoggingOption

        default void setJooQDefaultExecutionLoggingOption​(boolean option)
        Parameters:
        option - true means the default jooq execution logging is on, false means that it is not

      • isJooQDefaultExecutionLoggingOn

        default boolean isJooQDefaultExecutionLoggingOn()
        Returns:
        true if jooq default execution logging is on

      • getConnection

        default java.sql.Connection getConnection()
                                   throws java.sql.SQLException
        It is best to use this method within a try-with-resource construct This method calls the DataSource for a connection. You are responsible for closing the connection.
        Returns:
        a connection to the database
        Throws:
        java.sql.SQLException - if there is a problem with the connection

      • getDatabaseMetaData

        default java.sql.DatabaseMetaData getDatabaseMetaData()
        Returns:
        the meta data about the database if available, or null

      • getTableNames

        default java.util.List<java.lang.String> getTableNames​(java.lang.String schemaName)
        Parameters:
        schemaName - the name of the schema that should contain the tables
        Returns:
        a list of table names within the schema

      • getAllTableNames

        default java.util.List<java.lang.String> getAllTableNames()
        Returns:
        a list of all table names within the database

      • containsSchema

        default boolean containsSchema​(java.lang.String schemaName)
        Parameters:
        schemaName - the schema name to check
        Returns:
        true if the database contains a schema with the provided name

      • containsSchema

        default boolean containsSchema​(org.jooq.Schema schema)
        Parameters:
        schema - the schema to check
        Returns:
        true if the schema is in this database

      • getSchema

        default org.jooq.Schema getSchema​(java.lang.String schemaName)
        The name of the schema is first checked for an exact lexicographical match. If a match occurs, the schema is returned. If a lexicographical match fails, then a check for a match ignoring the case of the string is performed. This is done because SQL identifier names should be case insensitive. If neither matches then null is returned.
        Parameters:
        schemaName - the schema name to find
        Returns:
        the jooq schema for the name or null

      • containsTable

        default boolean containsTable​(org.jooq.Table<?> table)
        Parameters:
        table - a jooq table for a potential table in the database
        Returns:
        true if the table is in this database

      • containsTable

        default boolean containsTable​(java.lang.String tableName)
        Parameters:
        tableName - the unqualified table name to find as a string
        Returns:
        true if the database contains the table

      • getTable

        default org.jooq.Table<?> getTable​(java.lang.String tableName)
        The name of the table is first checked for an exact lexicographical match. If a match occurs, the table is returned. If a lexicographical match fails, then a check for a match ignoring the case of the string is performed. This is done because SQL identifier names should be case insensitive. If neither matches then null is returned.
        Parameters:
        tableName - the unqualified table name to find as a string
        Returns:
        the jooq Table representation or null if not found

      • getTable

        default org.jooq.Table<?> getTable​(org.jooq.Schema schema,
                                   java.lang.String tableName)
        The name of the table is first checked for an exact lexicographical match. If a match occurs, the table is returned. If a lexicographical match fails, then a check for a match ignoring the case of the string is performed. This is done because SQL identifier names should be case insensitive. If neither matches then null is returned.
        Parameters:
        schema - the schema to check, must not be null
        tableName - the unqualified table name to find as a string
        Returns:
        the jooq Table representation or null if not found

      • getTables

        default java.util.List<org.jooq.Table<?>> getTables​(java.lang.String schemaName)
        Parameters:
        schemaName - the name of the schema that should contain the tables
        Returns:
        a list of jooq Tables that are in the specified schema of the database

      • hasTables

        default boolean hasTables​(java.lang.String schemaName)
        Checks if tables exist in the specified schema
        Parameters:
        schemaName - the name of the schema that should contain the tables
        Returns:
        true if at least one table exists in the schema

      • containsTable

        default boolean containsTable​(java.lang.String schemaName,
                              java.lang.String table)
        Checks if the supplied table exists in the schema
        Parameters:
        schemaName - the name of the schema that should contain the table
        table - a string representing the unqualified name of the table
        Returns:
        true if it exists

      • getTable

        default org.jooq.Table<?> getTable​(java.lang.String schemaName,
                                   java.lang.String tableName)
        Parameters:
        schemaName - the name of the schema that should contain the table
        tableName - a string representation of the unqualified table name as recognized by valid SQL table name
        Returns:
        a jooq Table, or null if no table with that name exists

      • writeTableAsCSV

        default void writeTableAsCSV​(java.lang.String tableName,
                             java.io.PrintWriter out)
        Writes the table as comma separated values
        Parameters:
        tableName - the unqualified name of the table to write
        out - the PrintWriter to write to

      • printTableAsCSV

        default void printTableAsCSV​(java.lang.String tableName)
        Prints the table as comma separated values to the console
        Parameters:
        tableName - the unqualified name of the table to print

      • writeTableAsText

        default void writeTableAsText​(java.lang.String tableName,
                              java.io.PrintWriter out)
        Writes the table as prettified text
        Parameters:
        tableName - the unqualified name of the table to write
        out - the PrintWriter to write to

      • printTableAsText

        default void printTableAsText​(java.lang.String tableName)
        Prints the table as prettified text to the console
        Parameters:
        tableName - the unqualified name of the table to write

      • writeAllTablesAsText

        default void writeAllTablesAsText​(java.lang.String schemaName,
                                  java.io.PrintWriter out)
        Writes all tables as text
        Parameters:
        schemaName - the name of the schema that should contain the tables
        out - the PrintWriter to write to

      • selectAll

        default org.jooq.Result<org.jooq.Record> selectAll​(org.jooq.Table<? extends org.jooq.Record> table)
        Parameters:
        table - the Table to get all records from
        Returns:
        the records as a jooq Result or null

      • printAllTablesAsText

        default void printAllTablesAsText​(java.lang.String schemaName)
        Prints all tables as text to the console
        Parameters:
        schemaName - the name of the schema that should contain the tables

      • writeAllTablesAsCSV

        default void writeAllTablesAsCSV​(java.lang.String schemaName,
                                 java.nio.file.Path pathToOutPutDirectory)
                          throws java.io.IOException
        Writes all tables as separate comma separated value files into the supplied directory. The files are written to text files using the same name as the tables in the database
        Parameters:
        schemaName - the name of the schema that should contain the tables
        pathToOutPutDirectory - the path to the output directory to hold the csv files
        Throws:
        java.io.IOException - a checked exception

      • selectAll

        default org.jooq.Result<org.jooq.Record> selectAll​(java.lang.String tableName)
        Parameters:
        tableName - the unqualified name of the table to get all records from
        Returns:
        a jooq result holding all of the records from the table or null

      • isTableEmpty

        default boolean isTableEmpty​(java.lang.String table)
        Parameters:
        table - the unqualified name of the table
        Returns:
        true if the table contains no records (rows)

      • isTableEmpty

        default boolean isTableEmpty​(org.jooq.Table<? extends org.jooq.Record> table)
        Parameters:
        table - the table to check
        Returns:
        true if the table has no data in the result

      • hasData

        default boolean hasData​(java.lang.String schemaName)
        Parameters:
        schemaName - the name of the schema that should contain the tables
        Returns:
        true if at least one user defined table in the schema has data

      • areAllTablesEmpty

        default boolean areAllTablesEmpty​(java.lang.String schemaName)
        Parameters:
        schemaName - the name of the schema that should contain the tables
        Returns:
        true if all user defined tables are empty in the schema

      • getInsertQueries

        default java.lang.String getInsertQueries​(java.lang.String tableName)
        Parameters:
        tableName - the unqualified name of the table
        Returns:
        a string that represents all of the insert queries for the data that is currently in the supplied table or null

      • getInsertQueries

        default java.lang.String getInsertQueries​(org.jooq.Table<? extends org.jooq.Record> table)
        Parameters:
        table - the table to generate the insert statements for, must not be null
        Returns:
        the insert statements as a string or null

      • printInsertQueries

        default void printInsertQueries​(java.lang.String tableName)
        Prints the insert queries associated with the supplied table to the console
        Parameters:
        tableName - the unqualified name of the table

      • writeInsertQueries

        default void writeInsertQueries​(java.lang.String tableName,
                                java.io.PrintWriter out)
        Writes the insert queries associated with the supplied table to the PrintWriter
        Parameters:
        tableName - the unqualified name of the table
        out - the PrintWriter to write to

      • writeInsertQueries

        default void writeInsertQueries​(org.jooq.Table<? extends org.jooq.Record> table,
                                java.io.PrintWriter out)
        Writes the insert statements for the table in the file
        Parameters:
        table - the the table for the insert statements
        out - the file to write to

      • printAllTablesAsInsertQueries

        default void printAllTablesAsInsertQueries​(java.lang.String schemaName)
        Prints all table data as insert queries to the console
        Parameters:
        schemaName - the name of the schema that should contain the tables

      • writeAllTablesAsInsertQueries

        default void writeAllTablesAsInsertQueries​(java.lang.String schemaName,
                                           java.io.PrintWriter out)
        Writes all table data as insert queries to the PrintWriter
        Parameters:
        schemaName - the name of the schema that should contain the tables
        out - the PrintWriter to write to

      • writeDbToExcelWorkbook

        default void writeDbToExcelWorkbook​(java.lang.String schemaName)
                             throws java.io.IOException
        Writes all the tables to an Excel workbook, uses name of schema, uses the working directory
        Parameters:
        schemaName - the name of the schema containing the tables
        Throws:
        java.io.IOException - if there is a problem

      • writeDbToExcelWorkbook

        default void writeDbToExcelWorkbook​(java.lang.String schemaName,
                                    java.nio.file.Path wbDirectory)
                             throws java.io.IOException
        Writes all the tables to an Excel workbook, uses name of database
        Parameters:
        schemaName - the name of the schema that should contain the tables
        wbDirectory - directory of the workbook, if null uses the working directory
        Throws:
        java.io.IOException - if there is a problem

      • writeDbToExcelWorkbook

        default void writeDbToExcelWorkbook​(java.lang.String schemaName,
                                    java.lang.String wbName)
                             throws java.io.IOException
        Writes all the tables to an Excel workbook uses the working directory
        Parameters:
        schemaName - the name of the schema that should contain the tables
        wbName - name of the workbook, if null uses name of database
        Throws:
        java.io.IOException - if there is a problem

      • writeDbToExcelWorkbook

        default void writeDbToExcelWorkbook​(java.lang.String schemaName,
                                    java.lang.String wbName,
                                    java.nio.file.Path wbDirectory)
                             throws java.io.IOException
        Writes all the tables in the supplied schema to an Excel workbook
        Parameters:
        schemaName - the name of the schema that should contain the tables, must not be null
        wbName - name of the workbook, if null uses name of database
        wbDirectory - directory of the workbook, if null uses the working directory
        Throws:
        java.io.IOException - if there is a problem

      • writeDbToExcelWorkbook

        default void writeDbToExcelWorkbook​(java.util.List<java.lang.String> tableNames,
                                    java.lang.String wbName,
                                    java.nio.file.Path wbDirectory)
                             throws java.io.IOException
        Writes the tables in the supplied list to an Excel workbook, if they exist in the database.
        Parameters:
        tableNames - a list of table names that should be written to Excel, must not be null
        wbName - name of the workbook, if null uses name of database
        wbDirectory - directory of the workbook, if null uses the working directory
        Throws:
        java.io.IOException - if there is a problem

      • executeCommand

        default boolean executeCommand​(java.lang.String cmd)
        Executes a single command on an database connection
        Parameters:
        cmd - a valid SQL command
        Returns:
        true if the command executed without an SQLException

      • executeCommand

        default boolean executeCommand​(java.sql.Connection con,
                               java.lang.String cmd)
        Executes the SQL provided in the string. Squelches exceptions The string must not have ";" semi-colon at the end. The caller is responsible for closing the connection
        Parameters:
        con - a connection for preparing the statement
        cmd - the command
        Returns:
        true if the command executed without an exception

      • executeCommands

        default boolean executeCommands​(java.util.List<java.lang.String> cmds)
        Consecutively executes the list of SQL queries supplied as a list of strings The strings must not have ";" semi-colon at the end.
        Parameters:
        cmds - the commands
        Returns:
        true if all commands were executed

      • executeScript

        default boolean executeScript​(java.nio.file.Path path)
                       throws java.io.IOException
        Executes the commands in the script on the database
        Parameters:
        path - the path
        Returns:
        true if all commands are executed
        Throws:
        java.io.IOException - if there is a problem

      • createResultQuery

        default org.jooq.ResultQuery<org.jooq.Record> createResultQuery​(java.lang.String sql)
        A simple wrapper to ease the use of jooq for novices. Returns a jooq query that can be executed to return results. Errors in the SQL are the user's responsibility. With the query, the user has multiple paths to execution.
        Parameters:
        sql - an SQL text string that is valid
        Returns:
        the query, ready to execute

      • fetchResults

        default org.jooq.Result<org.jooq.Record> fetchResults​(java.lang.String sql)
        A simple wrapper to ease the use of jooq for novices. Returns the results of a jooq query that can be executed to return results. Errors in the SQL are the user's responsibility
        Parameters:
        sql - an SQL text string that is valid
        Returns:
        the results of the query, basically uses fetch() on createResultQuery(String sql)

      • fetchJDBCResultSet

        default java.sql.ResultSet fetchJDBCResultSet​(java.lang.String sql)
        A simple wrapper to ease the use of JDBC for novices. Returns the results of a query in the form of a JDBC ResultSet. Errors in the SQL are the user's responsibility
        Parameters:
        sql - an SQL text string that is valid
        Returns:
        the results of the query

      • parseQueriesInSQLScript

        static java.util.List<java.lang.String> parseQueriesInSQLScript​(java.nio.file.Path filePath)
                                                         throws java.io.IOException
        Method to parse a SQL script for the database. The script honors SQL comments and separates each SQL command into a list of strings, 1 string for each command. The list of queries is returned.

        The script should have each command end in a semi-colon, ; The best comment to use is #. All characters on a line after # will be stripped. Best to put # as the first character of a line with no further SQL on the line

        Based on the work described here:

        https://blog.heckel.xyz/2014/06/22/run-sql-scripts-from-java-on-hsqldb-derby-mysql/

        Parameters:
        filePath - a path to the file for parsing
        Returns:
        the list of strings of the commands
        Throws:
        java.io.IOException - if there is a problem

      • parseLine

        static DatabaseIfc.LineOption parseLine​(java.lang.String line,
                                        java.lang.StringBuilder command)
        Takes the input string and builds a string to represent the SQL command from the string. Uses EmbeddedDerbyDatabase.DEFAULT_DELIMITER as the delimiter, i.e. ";" Checks for "--", "//" and "#" as start of line comments
        Parameters:
        line - the input to parse
        command - the parsed output
        Returns:
        the LineOption COMMENT means line was a comment, CONTINUED means that command continues on next line, END means that command was ended with the delimiter

      • parseLine

        static DatabaseIfc.LineOption parseLine​(java.lang.String line,
                                        java.lang.String delimiter,
                                        java.lang.StringBuilder command)
        Takes the input string and builds a string to represent the SQL command from the string. Checks for "--", "//" and "#" as start of line comments
        Parameters:
        line - the input to parse
        delimiter - the end of comand indicator
        command - the parsed output
        Returns:
        the LineOption COMMENT means line was a comment, CONTINUED means that command continues on next line, END means that command was ended with the delimiter

      • logWarnings

        static void logWarnings​(java.sql.Connection conn)
                 throws java.sql.SQLException
        Writes SQLWarnings to log file
        Parameters:
        conn - the connection
        Throws:
        java.sql.SQLException - the exception

      • parseQueriesInString

        static java.util.List<java.lang.String> parseQueriesInString​(java.lang.String str)
                                                      throws java.io.IOException
        Parses the supplied string and breaks it up into a list of strings The string needs to honor SQL comments and separates each SQL command into a list of strings, 1 string for each command. The list of queries is returned.

        The script should have each command end in a semi-colon, ; The best comment to use is #. All characters on a line after # will be stripped. Best to put # as the first character of a line with no further SQL on the line

        Parameters:
        str - A big string that has SQL queries
        Returns:
        a list of strings representing each SQL command
        Throws:
        java.io.IOException - the exception

      • parseCommandString

        static boolean parseCommandString​(java.lang.String input,
                                  java.lang.StringBuilder command)
        Takes the input string and builds a string to represent the command from the string.
        Parameters:
        input - the input to parse
        command - the parsed output
        Returns:
        true if the parse was successful

      • jooqCodeGenerationDerbyDatabase

        static void jooqCodeGenerationDerbyDatabase​(javax.sql.DataSource dataSource,
                                            java.lang.String schemaName,
                                            java.lang.String pkgDirName,
                                            java.lang.String packageName)
                                     throws java.lang.Exception
        Runs jooq code generation on the database at the supplied path. Assumes that the database exists and has well defined structure. Places generated source files in named package with the main java source
        Parameters:
        dataSource - a DataSource that can provide a connection to the database, must not be null
        schemaName - the name of the schema for which tables need to be generated, must not be null
        pkgDirName - the directory that holds the target package, must not be null
        packageName - name of package to be created to hold generated code, must not be null
        Throws:
        java.lang.Exception - if there is a problem

      • jooqCodeGeneration

        static void jooqCodeGeneration​(java.nio.file.Path pathToCreationScript,
                               java.lang.String schemaName,
                               java.lang.String pkgDirName,
                               java.lang.String packageName)
                        throws java.lang.Exception
        Runs jooq code generation on the database at the supplied creation script. Places generated source files in named package with the main java source
        Parameters:
        pathToCreationScript - a path to a valid database creation script, must not be null
        schemaName - the name of the schema for which tables need to be generated, must not be null
        pkgDirName - the directory that holds the target package, must not be null
        packageName - name of package to be created to hold generated code, must not be null
        Throws:
        java.lang.Exception - if there is a problem

      • runJooQCodeGenerationDerbyDatabase

        static void runJooQCodeGenerationDerbyDatabase​(javax.sql.DataSource dataSource,
                                               java.lang.String schemaName,
                                               java.lang.String pkgDirName,
                                               java.lang.String packageName)
        Runs jooq code generation on the database at the supplied path. Assumes that the database exists and has well defined structure. Places generated source files in package gensrc with the main java source. Squelches all exceptions.
        Parameters:
        dataSource - a DataSource that can provide a connection to the database, must not be null
        schemaName - the name of the schema for which tables need to be generated, must not be null
        pkgDirName - the directory that holds the target package, must not be null
        packageName - name of package to be created to hold generated code, must not be null

      • dropSchema

        default void dropSchema​(java.lang.String schemaName,
                        java.util.List<java.lang.String> tableNames,
                        java.util.List<java.lang.String> viewNames)
        Drops the named schema from the database. If no such schema exist with the name, then nothing is done.
        Parameters:
        schemaName - the name of the schema to drop, must not be null
        tableNames - the table names in the order that they must be dropped, must not be null
        viewNames - the view names in the order that they must be dropped, must not be null

      • getSQLDialect

        static java.util.Optional<org.jooq.SQLDialect> getSQLDialect​(javax.sql.DataSource dataSource)
        Attempts to determine the SQLDialect for the data source Reference to JDBCUtils
        Parameters:
        dataSource - the data source, must not null
        Returns:
        the SQLDialect wrapped in an Optional, may be null if no connection could be established to determine the dialect.