Examples with ConnectionCallback org.springframework.jdbc.core.ConnectionCallback used on opensource projects

Example 1 with ConnectionCallback

use of org.springframework.jdbc.core.ConnectionCallback in project uPortal by Jasig.

the class RDBMUserIdentityStore method addNewUser.

protected int addNewUser(final int newUID, final IPerson person, final TemplateUser templateUser) throws Exception {
    // Copy template user's groups memberships
    IGroupMember me = GroupService.getGroupMember(person.getEntityIdentifier());
    IGroupMember template = GroupService.getEntity(templateUser.getUserName(), Class.forName("org.apereo.portal.security.IPerson"));
    for (IEntityGroup eg : template.getParentGroups()) {
        ILockableEntityGroup leg = getSafeLockableGroup(eg, me);
        if (leg != null) {
            addPersonToGroup(person, me, leg);
        }
    }
    return this.transactionOperations.execute(new TransactionCallback<Integer>() {

        @Override
        public Integer doInTransaction(TransactionStatus status) {
            return jdbcOperations.execute(new ConnectionCallback<Integer>() {

                @Override
                public Integer doInConnection(Connection con) throws SQLException, DataAccessException {
                    int uPortalUID = -1;
                    PreparedStatement queryStmt = null;
                    PreparedStatement insertStmt = null;
                    try {
                        // Add to UP_USER
                        String insert = "INSERT INTO UP_USER (USER_ID, USER_NAME, USER_DFLT_USR_ID, USER_DFLT_LAY_ID, NEXT_STRUCT_ID, LST_CHAN_UPDT_DT)" + "VALUES (?, ?, ?, ?, null, null)";
                        String userName = person.getUserName();
                        insertStmt = con.prepareStatement(insert);
                        insertStmt.setInt(1, newUID);
                        insertStmt.setString(2, userName);
                        insertStmt.setInt(3, templateUser.getUserId());
                        insertStmt.setInt(4, templateUser.getDefaultLayoutId());
                        if (log.isDebugEnabled())
                            log.debug("RDBMUserIdentityStore::addNewUser(USER_ID=" + newUID + ", USER_NAME=" + userName + ", USER_DFLT_USR_ID=" + templateUser.getUserId() + ", USER_DFLT_LAY_ID=" + templateUser.getDefaultLayoutId() + "): " + insert);
                        insertStmt.executeUpdate();
                        insertStmt.close();
                        insertStmt = null;
                        // Start copying...
                        ResultSet rs = null;
                        String query = null;
                        try {
                            // Add to UP_USER_PROFILE
                            query = "SELECT USER_ID, PROFILE_FNAME, PROFILE_NAME, DESCRIPTION, " + "STRUCTURE_SS_ID, THEME_SS_ID " + "FROM UP_USER_PROFILE " + "WHERE USER_ID=?";
                            queryStmt = con.prepareStatement(query);
                            queryStmt.setInt(1, templateUser.getUserId());
                            if (log.isDebugEnabled())
                                log.debug("RDBMUserIdentityStore::addNewUser(USER_ID=" + templateUser.getUserId() + "): " + query);
                            rs = queryStmt.executeQuery();
                            insert = "INSERT INTO UP_USER_PROFILE (USER_ID, PROFILE_ID, PROFILE_FNAME, PROFILE_NAME, DESCRIPTION, LAYOUT_ID, STRUCTURE_SS_ID, THEME_SS_ID) " + "VALUES(?, ?, ?, ?, ?, NULL, ?, ?)";
                            insertStmt = con.prepareStatement(insert);
                            while (rs.next()) {
                                int id = getNextKey();
                                String profileFname = rs.getString("PROFILE_FNAME");
                                String profileName = rs.getString("PROFILE_NAME");
                                String description = rs.getString("DESCRIPTION");
                                int structure = rs.getInt("STRUCTURE_SS_ID");
                                int theme = rs.getInt("THEME_SS_ID");
                                insertStmt.setInt(1, newUID);
                                insertStmt.setInt(2, id);
                                insertStmt.setString(3, profileFname);
                                insertStmt.setString(4, profileName);
                                insertStmt.setString(5, description);
                                insertStmt.setInt(6, structure);
                                insertStmt.setInt(7, theme);
                                if (log.isDebugEnabled())
                                    log.debug("RDBMUserIdentityStore::addNewUser(USER_ID=" + newUID + ", PROFILE_FNAME=" + profileFname + ", PROFILE_NAME=" + profileName + ", DESCRIPTION=" + description + "): " + insert);
                                insertStmt.executeUpdate();
                            }
                            rs.close();
                            queryStmt.close();
                            if (insertStmt != null) {
                                insertStmt.close();
                                insertStmt = null;
                            }
                            // transaction
                            if (RDBMServices.getDbMetaData().supportsTransactions())
                                con.commit();
                            uPortalUID = newUID;
                        } finally {
                            try {
                                if (rs != null)
                                    rs.close();
                            } catch (Exception e) {
                            }
                        }
                    } finally {
                        try {
                            if (queryStmt != null)
                                queryStmt.close();
                        } catch (Exception e) {
                        }
                        try {
                            if (insertStmt != null)
                                insertStmt.close();
                        } catch (Exception e) {
                        }
                    }
                    return uPortalUID;
                }
            });
        }
    });
}

Example 2 with ConnectionCallback

use of org.springframework.jdbc.core.ConnectionCallback in project uPortal by Jasig.

the class RDBMUserIdentityStore method updateUser.

protected void updateUser(final int userId, final IPerson person, final TemplateUser templateUser) throws Exception {
    // Remove my existing group memberships
    IGroupMember me = GroupService.getGroupMember(person.getEntityIdentifier());
    for (IEntityGroup eg : me.getParentGroups()) {
        ILockableEntityGroup leg = getSafeLockableGroup(eg, me);
        if (leg != null) {
            removePersonFromGroup(person, me, leg);
        }
    }
    // Copy template user's groups memberships
    IGroupMember template = GroupService.getEntity(templateUser.getUserName(), IPerson.class);
    for (IEntityGroup eg : template.getParentGroups()) {
        ILockableEntityGroup leg = getSafeLockableGroup(eg, me);
        if (leg != null) {
            addPersonToGroup(person, me, leg);
        }
    }
    this.transactionOperations.execute(new TransactionCallback<Object>() {

        @Override
        public Object doInTransaction(TransactionStatus status) {
            return jdbcOperations.execute(new ConnectionCallback<Object>() {

                @Override
                public Object doInConnection(Connection con) throws SQLException, DataAccessException {
                    PreparedStatement deleteStmt = null;
                    PreparedStatement queryStmt = null;
                    PreparedStatement insertStmt = null;
                    try {
                        // Update UP_USER
                        String update = "UPDATE UP_USER " + "SET USER_DFLT_USR_ID=?, " + "USER_DFLT_LAY_ID=?, " + "NEXT_STRUCT_ID=null " + "WHERE USER_ID=?";
                        insertStmt = con.prepareStatement(update);
                        insertStmt.setInt(1, templateUser.getUserId());
                        insertStmt.setInt(2, templateUser.getDefaultLayoutId());
                        insertStmt.setInt(3, userId);
                        if (log.isDebugEnabled())
                            log.debug("RDBMUserIdentityStore::addNewUser(): " + update);
                        insertStmt.executeUpdate();
                        insertStmt.close();
                        // Start copying...
                        ResultSet rs = null;
                        String delete = null;
                        String query = null;
                        String insert = null;
                        try {
                            // Update UP_USER_PROFILE
                            delete = "DELETE FROM UP_USER_PROFILE " + "WHERE USER_ID=?";
                            deleteStmt = con.prepareStatement(delete);
                            deleteStmt.setInt(1, userId);
                            if (log.isDebugEnabled())
                                log.debug("RDBMUserIdentityStore::updateUser(USER_ID=" + userId + "): " + delete);
                            deleteStmt.executeUpdate();
                            deleteStmt.close();
                            query = "SELECT USER_ID, PROFILE_FNAME, PROFILE_NAME, DESCRIPTION, " + "STRUCTURE_SS_ID, THEME_SS_ID " + "FROM UP_USER_PROFILE " + "WHERE USER_ID=?";
                            queryStmt = con.prepareStatement(query);
                            queryStmt.setInt(1, templateUser.getUserId());
                            if (log.isDebugEnabled())
                                log.debug("RDBMUserIdentityStore::updateUser(USER_ID=" + templateUser.getUserId() + "): " + query);
                            rs = queryStmt.executeQuery();
                            insert = "INSERT INTO UP_USER_PROFILE (USER_ID, PROFILE_ID, PROFILE_FNAME, PROFILE_NAME, DESCRIPTION, LAYOUT_ID, STRUCTURE_SS_ID, THEME_SS_ID) " + "VALUES(?, ?, ?, ?, ?, NULL, ?, ?)";
                            insertStmt = con.prepareStatement(insert);
                            while (rs.next()) {
                                int id = getNextKey();
                                String profileFname = rs.getString("PROFILE_FNAME");
                                String profileName = rs.getString("PROFILE_NAME");
                                String description = rs.getString("DESCRIPTION");
                                int structure = rs.getInt("STRUCTURE_SS_ID");
                                int theme = rs.getInt("THEME_SS_ID");
                                insertStmt.setInt(1, userId);
                                insertStmt.setInt(2, id);
                                insertStmt.setString(3, profileFname);
                                insertStmt.setString(4, profileName);
                                insertStmt.setString(5, description);
                                insertStmt.setInt(6, structure);
                                insertStmt.setInt(7, theme);
                                if (log.isDebugEnabled())
                                    log.debug("RDBMUserIdentityStore::updateUser(USER_ID=" + userId + ", PROFILE_FNAME=" + profileFname + ", PROFILE_NAME=" + profileName + ", DESCRIPTION=" + description + "): " + insert);
                                insertStmt.executeUpdate();
                            }
                            rs.close();
                            queryStmt.close();
                            insertStmt.close();
                            // transaction
                            if (RDBMServices.getDbMetaData().supportsTransactions())
                                con.commit();
                        } finally {
                            try {
                                rs.close();
                            } catch (Exception e) {
                            }
                        }
                    } finally {
                        try {
                            deleteStmt.close();
                        } catch (Exception e) {
                        }
                        try {
                            queryStmt.close();
                        } catch (Exception e) {
                        }
                        try {
                            insertStmt.close();
                        } catch (Exception e) {
                        }
                    }
                    return null;
                }
            });
        }
    });
}

Example 3 with ConnectionCallback

use of org.springframework.jdbc.core.ConnectionCallback in project otter by alibaba.

the class DdlUtils method findTable.

public static Table findTable(final JdbcTemplate jdbcTemplate, final String catalogName, final String schemaName, final String tableName, final DdlUtilsFilter filter) throws Exception {
    return (Table) jdbcTemplate.execute(new ConnectionCallback() {

        public Object doInConnection(Connection con) throws SQLException, DataAccessException {
            Table table = null;
            DatabaseMetaDataWrapper metaData = new DatabaseMetaDataWrapper();
            boolean isDRDS = false;
            try {
                if (filter != null) {
                    con = filter.filterConnection(con);
                    Assert.notNull(con);
                }
                DatabaseMetaData databaseMetaData = con.getMetaData();
                if (filter != null) {
                    databaseMetaData = filter.filterDataBaseMetaData(jdbcTemplate, con, databaseMetaData);
                    Assert.notNull(databaseMetaData);
                }
                String databaseName = databaseMetaData.getDatabaseProductName();
                String version = databaseMetaData.getDatabaseProductVersion();
                if (StringUtils.startsWithIgnoreCase(databaseName, "mysql") && StringUtils.contains(version, "-TDDL-")) {
                    isDRDS = true;
                }
                metaData.setMetaData(databaseMetaData);
                metaData.setTableTypes(TableType.toStrings(SUPPORTED_TABLE_TYPES));
                metaData.setCatalog(catalogName);
                metaData.setSchemaPattern(schemaName);
                String convertTableName = tableName;
                if (databaseMetaData.storesUpperCaseIdentifiers()) {
                    metaData.setCatalog(catalogName.toUpperCase());
                    metaData.setSchemaPattern(schemaName.toUpperCase());
                    convertTableName = tableName.toUpperCase();
                }
                if (databaseMetaData.storesLowerCaseIdentifiers()) {
                    metaData.setCatalog(catalogName.toLowerCase());
                    metaData.setSchemaPattern(schemaName.toLowerCase());
                    convertTableName = tableName.toLowerCase();
                }
                ResultSet tableData = null;
                try {
                    tableData = metaData.getTables(convertTableName);
                    while ((tableData != null) && tableData.next()) {
                        Map<String, Object> values = readColumns(tableData, initColumnsForTable());
                        table = readTable(metaData, values);
                        if (table.getName().equalsIgnoreCase(tableName)) {
                            break;
                        }
                    }
                } finally {
                    JdbcUtils.closeResultSet(tableData);
                }
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            }
            makeAllColumnsPrimaryKeysIfNoPrimaryKeysFound(table);
            if (isDRDS) {
                makeDRDSShardColumnsAsPrimaryKeys(table, jdbcTemplate, catalogName, schemaName, tableName);
            }
            return table;
        }
    });
}

Example 4 with ConnectionCallback

use of org.springframework.jdbc.core.ConnectionCallback in project otter by alibaba.

the class DdlUtils method findTables.

@SuppressWarnings("unchecked")
public static List<Table> findTables(final JdbcTemplate jdbcTemplate, final String catalogName, final String schemaName, final String tableNamePattern, final DdlUtilsFilter filter, final DdlTableNameFilter tableNameFilter) throws Exception {
    return (List<Table>) jdbcTemplate.execute(new ConnectionCallback() {

        public Object doInConnection(Connection con) throws SQLException, DataAccessException {
            List<Table> tables = new ArrayList<Table>();
            DatabaseMetaDataWrapper metaData = new DatabaseMetaDataWrapper();
            boolean isDRDS = false;
            try {
                if (filter != null) {
                    con = filter.filterConnection(con);
                    Assert.notNull(con);
                }
                DatabaseMetaData databaseMetaData = con.getMetaData();
                if (filter != null) {
                    databaseMetaData = filter.filterDataBaseMetaData(jdbcTemplate, con, databaseMetaData);
                    Assert.notNull(databaseMetaData);
                }
                String databaseName = databaseMetaData.getDatabaseProductName();
                String version = databaseMetaData.getDatabaseProductVersion();
                if (StringUtils.startsWithIgnoreCase(databaseName, "mysql") && StringUtils.contains(version, "-TDDL-")) {
                    isDRDS = true;
                }
                metaData.setMetaData(databaseMetaData);
                metaData.setTableTypes(TableType.toStrings(SUPPORTED_TABLE_TYPES));
                metaData.setCatalog(catalogName);
                metaData.setSchemaPattern(schemaName);
                String convertTableName = tableNamePattern;
                if (databaseMetaData.storesUpperCaseIdentifiers()) {
                    metaData.setCatalog(catalogName.toUpperCase());
                    metaData.setSchemaPattern(schemaName.toUpperCase());
                    convertTableName = tableNamePattern.toUpperCase();
                }
                if (databaseMetaData.storesLowerCaseIdentifiers()) {
                    metaData.setCatalog(catalogName.toLowerCase());
                    metaData.setSchemaPattern(schemaName.toLowerCase());
                    convertTableName = tableNamePattern.toLowerCase();
                }
                ResultSet tableData = null;
                try {
                    tableData = metaData.getTables(convertTableName);
                    while ((tableData != null) && tableData.next()) {
                        Map<String, Object> values = readColumns(tableData, initColumnsForTable());
                        Table table = readTable(metaData, values);
                        if ((tableNameFilter == null) || tableNameFilter.accept(catalogName, schemaName, table.getName())) {
                            tables.add(table);
                        }
                    }
                } finally {
                    JdbcUtils.closeResultSet(tableData);
                }
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            }
            for (Table table : tables) {
                makeAllColumnsPrimaryKeysIfNoPrimaryKeysFound(table);
                if (isDRDS) {
                    makeDRDSShardColumnsAsPrimaryKeys(table, jdbcTemplate, catalogName, schemaName, table.getName());
                }
            }
            return tables;
        }
    });
}

Example 5 with ConnectionCallback

use of org.springframework.jdbc.core.ConnectionCallback in project uPortal by Jasig.

the class RDBMUserIdentityStore method addNewUser.

private int addNewUser(final int newUID, final IPerson person) {
    return this.transactionOperations.execute(status -> jdbcOperations.execute(new ConnectionCallback<Integer>() {

        @Override
        public Integer doInConnection(Connection con) throws SQLException, DataAccessException {
            int uPortalUID;
            PreparedStatement queryStmt = null;
            PreparedStatement insertStmt = null;
            try {
                // Add to UP_USER
                String insert = "INSERT INTO UP_USER (USER_ID, USER_NAME, USER_DFLT_LAY_ID, NEXT_STRUCT_ID, LST_CHAN_UPDT_DT)" + "VALUES (?, ?, ?, null, null)";
                String userName = person.getUserName();
                insertStmt = con.prepareStatement(insert);
                insertStmt.setInt(1, newUID);
                insertStmt.setString(2, userName);
                insertStmt.setInt(3, USER_DFLT_LAY_ID);
                if (log.isDebugEnabled())
                    log.debug("RDBMUserIdentityStore::addNewUser(USER_ID=" + newUID + ", USER_NAME=" + userName + ", USER_DFLT_LAY_ID=" + USER_DFLT_LAY_ID + "): " + insert);
                insertStmt.executeUpdate();
                insertStmt.close();
                insertStmt = null;
                // Start copying...
                ResultSet rs = null;
                String query;
                try {
                    /*
                                                 * NOTE:  in former times, we used a "template user" for this
                                                 * purpose;  going forward we will use the system profile(s).
                                                 */
                    final IPerson system = PersonFactory.createSystemPerson();
                    query = "SELECT upup.USER_ID, upup.PROFILE_FNAME, upup.PROFILE_NAME, upup.DESCRIPTION, " + "upup.STRUCTURE_SS_ID, upup.THEME_SS_ID " + "FROM UP_USER upu, UP_USER_PROFILE upup " + "WHERE upup.USER_ID = upu.USER_ID " + "AND upu.USER_NAME = ?";
                    queryStmt = con.prepareStatement(query);
                    queryStmt.setString(1, system.getUserName());
                    if (log.isDebugEnabled())
                        log.debug("RDBMUserIdentityStore::addNewUser(USER_NAME=" + system.getUserName() + "): " + query);
                    rs = queryStmt.executeQuery();
                    insert = "INSERT INTO UP_USER_PROFILE (USER_ID, PROFILE_ID, PROFILE_FNAME, PROFILE_NAME, DESCRIPTION, LAYOUT_ID, STRUCTURE_SS_ID, THEME_SS_ID) " + "VALUES(?, ?, ?, ?, ?, NULL, ?, ?)";
                    insertStmt = con.prepareStatement(insert);
                    while (rs.next()) {
                        int id = getNextKey();
                        String profileFname = rs.getString("PROFILE_FNAME");
                        String profileName = rs.getString("PROFILE_NAME");
                        String description = rs.getString("DESCRIPTION");
                        int structure = rs.getInt("STRUCTURE_SS_ID");
                        int theme = rs.getInt("THEME_SS_ID");
                        insertStmt.setInt(1, newUID);
                        insertStmt.setInt(2, id);
                        insertStmt.setString(3, profileFname);
                        insertStmt.setString(4, profileName);
                        insertStmt.setString(5, description);
                        insertStmt.setInt(6, structure);
                        insertStmt.setInt(7, theme);
                        if (log.isDebugEnabled())
                            log.debug("RDBMUserIdentityStore::addNewUser(USER_ID=" + newUID + ", PROFILE_FNAME=" + profileFname + ", PROFILE_NAME=" + profileName + ", DESCRIPTION=" + description + "): " + insert);
                        insertStmt.executeUpdate();
                    }
                    rs.close();
                    queryStmt.close();
                    if (insertStmt != null) {
                        insertStmt.close();
                        insertStmt = null;
                    }
                    // transaction
                    if (RDBMServices.getDbMetaData().supportsTransactions())
                        con.commit();
                    uPortalUID = newUID;
                } finally {
                    try {
                        if (rs != null)
                            rs.close();
                    } catch (Exception e) {
                    }
                }
            } finally {
                try {
                    if (queryStmt != null)
                        queryStmt.close();
                } catch (Exception e) {
                }
                try {
                    if (insertStmt != null)
                        insertStmt.close();
                } catch (Exception e) {
                }
            }
            return uPortalUID;
        }
    }));
}