Package org.teichert.databaseexplorer.controllers

Class DatabaseController

  • public final class DatabaseController
extends Object
    The Database-Controller-Class provides all necessary methods to work with the database.
    This Class is mostly used via the Database-Model DatabaseModel.

    • Field Detail

      • EMPTY_CONNECTION_POOL_ID

        public static final int EMPTY_CONNECTION_POOL_ID
        Default ConnectionPoolId for System Executions (Which is not a user execution and belongs to no Tab)
        This ConnectionPoolId will not be chaced within the HashMap mapOfDatabasePool,
        so the connection will created every time again.
        See Also:
        Constant Field Values

    • Method Detail

      • createNewSQLTable

        public void createNewSQLTable​(SQLTable table)
                       throws SQLException
        Creates a new table in the database with the values from the given SQL table.
        Parameters:
        table -
        Throws:
        SQLException

      • getCreateSQLIndexesString

        public String getCreateSQLIndexesString​(SQLTable table,
                                        boolean withSchema)
                                 throws SQLException
        Returns the string for creating the indexes of the given table.
        It only get index-create-statement for non-primary-indexes!
        Parameters:
        table -
        Throws:
        SQLException

      • getCreateIndexString

        public String getCreateIndexString​(SQLIndex index,
                                   boolean withSchema)
        Returns the string for creating the given index.
        Parameters:
        index -
        Returns:

      • getCreationString

        public String getCreationString​(SQLTable table,
                                boolean withSchema)
        Creates the Create-Table SQL-Script from the given SQLTable
        Parameters:
        table -
        withSchema -
        Returns:

      • createTempSQLTable

        public KeyValue<String,​SQLTable> createTempSQLTable​(int connectionPoolId,
                                                          SQLTextEditor<?> editor,
                                                          SQLDatabase database,
                                                          String sql,
                                                          Map<Object,​Object> mapOfData,
                                                          boolean fillContent)
        Creates a temporary table when executing a "SELECT"-SQL script.
        Parameters:
        connectionPoolId -
        editor -
        database -
        sql -
        fillContent -
        mapOfData -
        Returns:

      • createPreparedTempSQLTable

        public KeyValue<String,​SQLTable> createPreparedTempSQLTable​(int connectionPoolId,
                                                                  SQLDatabase database,
                                                                  String sql,
                                                                  Object... values)
        Creates a temporary table with a prepared statement.
        Parameters:
        database -
        sql -
        values -
        Returns:

      • createTempSQLTableForSQL

        public KeyValue<String,​SQLTable> createTempSQLTableForSQL​(int connectionPoolId,
                                                                SQLDatabase database,
                                                                String sql,
                                                                boolean fillContent)
        Executes the sql and returns a SQLTable.
        Parameters:
        database -
        sql -
        fillContent -
        Returns:

      • createNewSQLIndexes

        public void createNewSQLIndexes​(SQLTable table)
                         throws SQLException
        Creates a new index for the given table.
        Parameters:
        table -
        Throws:
        SQLException

      • insertIntoSQLTableValue

        public void insertIntoSQLTableValue​(int connectionPoolId,
                                    String columnName,
                                    SQLTableValue newValue)
                             throws SQLException
        Inserts a new row for the given table.
        Parameters:
        newValue -
        Throws:
        SQLException

      • copyRows

        public void copyRows​(DatabaseModel model,
                     SQLTable sourceTable,
                     SQLTable targetTable)
              throws Exception
        Iterates through the source-table and copy row by row into the target-table on the other database!
        Parameters:
        model -
        sourceTable -
        targetTable -
        Throws:
        Exception

      • exportSQl

        public String exportSQl​(String sql,
                        SQLDatabase db)
                 throws Exception
        Returns an SQL-script for export for the given input-sql
        Parameters:
        sql - = input sql, which will be exported
        db - = database on which the export sql will be executed
        Returns:
        Throws:
        Exception

      • deleteSQLTableValue

        public void deleteSQLTableValue​(int connectionPoolId,
                                SQLTableValue[] oldValues)
                         throws SQLException
        Deletes the given rows.
        Parameters:
        connectionPoolId -
        oldValues -
        Throws:
        SQLException

      • reloadTable

        public void reloadTable​(int connectionPoolId,
                        SQLDatabase database,
                        Connection conn,
                        ResultSet rs,
                        SQLTable table,
                        int limit,
                        boolean fillTable,
                        boolean closeAfterLoading)
                 throws Exception
        Fills the given SQLTable with all information and if fillTable is true also SQLRows with the given amount restricted by limit.
        Parameters:
        database - = database
        conn - = MAY ALSO BE ZERO, THEN A NEW CONNECTION WILL BE CREATED!
        rs - = MAY ALSO BE ZERO, THEN A NEW RESULTSET WILL BE CREATED! (IN COMBINATION WITH conn !!!
        table - = table to be filled.
        limit - = row limit (SQL statement)
        closeAfterLoading - = Should the new or passed connection be closed after the execution? (Recommended for a new connection, since it is no longer used!)
        Throws:
        SQLException
        Exception

      • executeSQL

        public Exception executeSQL​(int connectionPoolId,
                            SQLDatabase database,
                            String sql,
                            SQLTextEditor<?> editor,
                            IDoingAfterThread d)
        Executes an SQL-Statement without returning a ResultSet.
        For example: (UPDATE, DELETE, INSERT INTO) etc. commands should be executed!
        Parameters:
        connectionPoolId -
        database - = SQLDatabase
        sql - = SQL-Statement
        editor -
        d -
        Returns:

      • executePreparedSQL

        public Exception executePreparedSQL​(int connectionPoolId,
                                    SQLDatabase database,
                                    String sql,
                                    Object[] values)
        Executes an SQL-Statement via a PreparedStatement without returning a ResultSet. So with this, for example: (UPDATE, DELETE, INSERT INTO) etc. commands should be executed!
        Parameters:
        connectionPoolId -
        database -
        sql -
        values -
        Returns:

      • testConnection

        public KeyValue<String,​SQLDatabase> testConnection​(String id,
                                                         String grouping,
                                                         String customName,
                                                         String sshConnId,
                                                         String sshForwardingId,
                                                         String db,
                                                         String ip,
                                                         String port,
                                                         String user,
                                                         String pwd,
                                                         String parameter,
                                                         DatabaseModel model,
                                                         Map<String,​Object> map)
        Tests the Connection.
        Timeout is for default 5 seconds.
        Parameters:
        id - = database identifier
        grouping - = database grouping (for css style only)
        customName - = database customName (for gui only)
        sshConnId - = the identifier for a SSH-Connection (can be null)
        sshForwardingId - = the identifier for a SSH-Port-Forwarding (can be null)
        db - = database name
        ip - = IP-address of the database
        port - = Port of the database
        user - = Username of the database
        pwd - = Password of the user of the database
        parameter - = Parameter of the database.
        Returns:
        returns an array of type Object from a size at least 2.
        Index 0 is a boolean; This means if the connection is correctly done or not.
        Index 1 is the SQLDatabase-Object; this is the object created by the test.
        if the test gone wrong, the database-object is null!

      • createConnection

        public Connection createConnection​(int connectionPoolId,
                                   SQLDatabase database)
                            throws SQLException
        Returns a Database-Connection.
        If no connection exists for the given connectionPoolId, a new one will be created
        otherwise it will return a cached one.
        Parameters:
        connectionPoolId -
        database -
        Returns:
        Throws:
        SQLException

      • createConnection

        public Connection createConnection​(int connectionPoolId,
                                   String id,
                                   String grouping,
                                   String customName,
                                   String sshConnId,
                                   String sshForwardingId,
                                   String db,
                                   String ip,
                                   String port,
                                   String user,
                                   String pwd,
                                   String parameter,
                                   boolean isLazyLoaded,
                                   DatabaseModel model)
                            throws SQLException
        Returns a Database-Connection.
        If no connection exists for the given connectionPoolId, a new one will be created
        otherwise it will return a cached one.
        Parameters:
        connectionPoolId -
        id -
        grouping -
        customName -
        sshConnId -
        sshForwardingId -
        db -
        ip -
        port -
        user -
        pwd -
        parameter -
        isLazyLoaded -
        Returns:
        Throws:
        SQLException

      • createNewConnectionForSubTransaction

        public Connection createNewConnectionForSubTransaction​(int connectionPoolId,
                                                       SQLDatabase database)
                                                throws SQLException
        Creates always a new Database-Connection, because SubTransaction will be handled within a new connection, to split it from the original one.
        (All Database-Connections Connection have it's own execution-context)
        Parameters:
        connectionPoolId -
        database -
        Returns:
        Throws:
        SQLException

      • createNewConnectionForSubTransaction

        public Connection createNewConnectionForSubTransaction​(int connectionPoolId,
                                                       String db,
                                                       String ip,
                                                       String port,
                                                       String user,
                                                       String pwd,
                                                       String parameter,
                                                       DatabaseModel model)
                                                throws SQLException
        Creates always a new Database-Connection, because SubTransaction will be handled within a new connection, to split it from the original one.
        (All Database-Connections Connection have it's own execution-context)
        Parameters:
        connectionPoolId -
        db -
        ip -
        port -
        user -
        pwd -
        parameter -
        Returns:
        Throws:
        SQLException

      • doRollback

        public void doRollback​(int connectionPoolId,
                       Map<Object,​Object> mapOfData)
                throws SQLException
        Do a rollback (if possible) to the cached Database-Connection for the given connectionPoolId.
        Parameters:
        connectionPoolId -
        mapOfData -
        Throws:
        SQLException

      • doCommit

        public void doCommit​(int connectionPoolId,
                     Map<Object,​Object> mapOfData)
              throws SQLException
        Do a commit (if possible) to the cached Database-Connection for the given connectionPoolId.
        Parameters:
        connectionPoolId -
        mapOfData -
        Throws:
        SQLException

      • hasConnection

        public boolean hasConnection​(int connectionPoolId,
                             Map<Object,​Object> mapOfData)
        Checks if a cached Database-Connection exists for the given connectionPoolId.
        Parameters:
        connectionPoolId -
        mapOfData -
        Returns:

      • reconnect

        public void reconnect​(ExecutorService es,
                      int connectionPoolId,
                      Map<Object,​Object> mapOfData)
               throws Exception
        Do a "reconnect" (close and create a new Connection if possible) to the cached Database-Connection for the given connectionPoolId and also rollback not committet executions.
        Parameters:
        es -
        connectionPoolId -
        mapOfData -
        Throws:
        Exception

      • reconnect

        public void reconnect​(ExecutorService es,
                      int connectionPoolId,
                      SQLDatabase database,
                      Map<Object,​Object> mapOfData)
               throws Exception
        Do a "reconnect" (close and create a new Connection if possible) to the cached Database-Connection for the given connectionPoolId.
        Parameters:
        es -
        connectionPoolId -
        database -
        mapOfData -
        Throws:
        Exception

      • abort

        public void abort​(int connectionPoolId)
           throws SQLException
        Aborts the current database-execution represented by the connectionPoolId.
        Parameters:
        connectionPoolId -
        Throws:
        SQLException

      • closeConnection

        public void closeConnection​(ExecutorService es,
                            int connectionPoolId,
                            Map<Object,​Object> mapOfData)
        Close (if possible) the cached Database-Connection for the given connectionPoolId.
        The mode will determine if the connection is a (JDBC) or (Hibernate) Connection.
        Parameters:
        connectionPoolId -
        mapOfData -

      • loadTableInfosWithoutLazyloading

        public void loadTableInfosWithoutLazyloading​(int connectionPoolId,
                                             SQLTable table)
                                      throws SQLException
        Loads all table-informations and ignore lazyloading.
        Parameters:
        connectionPoolId -
        table -
        Throws:
        SQLException

      • loadTableInfos

        public void loadTableInfos​(int connectionPoolId,
                           SQLTable table)
                    throws SQLException
        Loads all table-informations but takes lazyloading into account.
        Parameters:
        connectionPoolId -
        table -
        Throws:
        SQLException

      • fillConstraints

        public void fillConstraints​(SQLDatabase database)
        Adds all SQLForeignInfo into the columns of all tables within the given SQLDatabase.
        Parameters:
        database -

      • fillConstraints

        public void fillConstraints​(SQLTable table)
        Adds all SQLForeignInfo into the columns of all tables within the given SQLDatabase.
        Parameters:
        table -

      • getColumnCount

        public int getColumnCount​(Connection conn,
                          SQLTable table,
                          boolean closeAfterLoading)
                   throws SQLException
        Returns the number of columns of the given (not yet filled) table tableName.
        Parameters:
        conn -
        table -
        closeAfterLoading -
        Returns:
        Throws:
        SQLException

      • loadSQLColumns

        public void loadSQLColumns​(Connection conn,
                           SQLDatabase database,
                           SQLTable table)
                    throws SQLException
        Loads the SQLColumns for the given SQLTable and the given SQLDatabase.
        The SQL database is still included if the table does not yet have a parent!
        Parameters:
        conn -
        database -
        table -
        Throws:
        SQLException

      • createMapFromTableRow

        public Map<String,​Object> createMapFromTableRow​(SQLRow row)
        Creates a HashMap based on the internal structure of the SaveItem table.
        Key = SQLTableColumn-name
        value = value of the row to the column

        The table names are (always in uppercase):
        ID, DBNAME, IP, PORT, USER, USERPWD, DBPARAMETER, CUSTOMNAME, GROUPNAME, SSHCONNID, SSHFORWARDINGID, MODEL, LAZYLOADED
        Parameters:
        row -
        Returns: