Examples with Database org.wso2.carbon.humantask.core.db.Database used on opensource projects

Example 31 with Database

use of org.wso2.carbon.humantask.core.db.Database in project carbon-apimgt by wso2.

the class ThreatProtectionDAOImpl method addPolicy.

/**
 * Add a threat protection policy to database
 * @param policy Threat protection policy
 * @param connection SQL Connection
 * @throws APIMgtDAOException If failed to add policy
 */
private void addPolicy(ThreatProtectionPolicy policy, Connection connection) throws APIMgtDAOException {
    final String sqlQuery = "INSERT INTO " + THREAT_PROTECTION_TABLE + " (UUID, NAME, TYPE, POLICY) " + " VALUES (?, ?, ?, ?)";
    try (PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery)) {
        preparedStatement.setString(1, policy.getUuid());
        preparedStatement.setString(2, policy.getName());
        preparedStatement.setString(3, policy.getType());
        preparedStatement.setBytes(4, policy.getPolicy().getBytes("UTF-8"));
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        String errorMsg = "Error adding Threat Protection policy";
        throw new APIMgtDAOException(errorMsg, e);
    } catch (UnsupportedEncodingException e) {
        String errorMsg = "Charset error in threat protection policy";
        throw new APIMgtDAOException(errorMsg, e);
    }
}

Example 32 with Database

use of org.wso2.carbon.humantask.core.db.Database in project carbon-apimgt by wso2.

the class PostgresSQLStatements method prepareAttributeSearchStatementForStore.

/**
 * @see ApiDAOVendorSpecificStatements#prepareAttributeSearchStatementForStore(Connection connection, List, List,
 * Map, int, int)
 */
@Override
@SuppressFBWarnings({ "SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING", "OBL_UNSATISFIED_OBLIGATION_EXCEPTION_EDGE" })
public PreparedStatement prepareAttributeSearchStatementForStore(Connection connection, List<String> roles, List<String> labels, Map<String, String> attributeMap, int offset, int limit) throws APIMgtDAOException {
    StringBuilder roleListBuilder = new StringBuilder();
    roleListBuilder.append("?");
    for (int i = 0; i < roles.size() - 1; i++) {
        roleListBuilder.append(",?");
    }
    StringBuilder searchQuery = new StringBuilder();
    Iterator<Map.Entry<String, String>> entries = attributeMap.entrySet().iterator();
    while (entries.hasNext()) {
        Map.Entry<String, String> entry = entries.next();
        searchQuery.append("LOWER(");
        if (APIMgtConstants.TAG_SEARCH_TYPE_PREFIX.equalsIgnoreCase(entry.getKey())) {
            searchQuery.append(APIMgtConstants.TAG_NAME_COLUMN);
        } else if (APIMgtConstants.SUBCONTEXT_SEARCH_TYPE_PREFIX.equalsIgnoreCase(entry.getKey())) {
            searchQuery.append(APIMgtConstants.URL_PATTERN_COLUMN);
        } else {
            searchQuery.append(entry.getKey());
        }
        searchQuery.append(") LIKE ?");
        if (entries.hasNext()) {
            searchQuery.append(" AND ");
        }
    }
    // retrieve the attribute applicable for the search
    String searchAttribute = attributeMap.entrySet().iterator().next().getKey();
    // get the corresponding implementation based on the attribute to be searched
    String query = searchMap.get(searchAttribute).getStoreAttributeSearchQuery(roleListBuilder, searchQuery, offset, limit);
    query = "Select * from ( " + query + " ) A " + getStoreAPIsByLabelJoinQuery(labels);
    try {
        int queryIndex = 1;
        PreparedStatement statement = connection.prepareStatement(query);
        // include the attribute in the query (for APIs with public visibility)
        for (Map.Entry<String, String> entry : attributeMap.entrySet()) {
            statement.setString(queryIndex, '%' + entry.getValue().toLowerCase(Locale.ENGLISH) + '%');
            queryIndex++;
        }
        // include user roles in the query
        for (String role : roles) {
            statement.setString(queryIndex, role);
            queryIndex++;
        }
        // include the attribute in the query (for APIs with restricted visibility)
        for (Map.Entry<String, String> entry : attributeMap.entrySet()) {
            statement.setString(queryIndex, '%' + entry.getValue().toLowerCase(Locale.ENGLISH) + '%');
            queryIndex++;
        }
        for (String label : labels) {
            statement.setString(queryIndex, label);
            queryIndex++;
        }
        // setting 0 as the default offset based on store-api.yaml and Postgress specifications
        statement.setInt(queryIndex, (offset < 0) ? 0 : offset);
        statement.setInt(++queryIndex, limit);
        return statement;
    } catch (SQLException e) {
        String errorMsg = "Error occurred while searching APIs for attributes in the database.";
        log.error(errorMsg, e);
        throw new APIMgtDAOException(errorMsg, e);
    }
}

Example 33 with Database

use of org.wso2.carbon.humantask.core.db.Database in project carbon-apimgt by wso2.

the class SystemApplicationDaoImpl method removeConsumerKeyForApplication.

@Override
public void removeConsumerKeyForApplication(String appName) throws APIMgtDAOException {
    final String query = "DELETE FROM AM_SYSTEM_APPS WHERE NAME = ?";
    try (Connection connection = DAOUtil.getConnection();
        PreparedStatement statement = connection.prepareStatement(query)) {
        connection.setAutoCommit(false);
        try {
            statement.setString(1, appName);
            log.debug("Executing query: {} ", query);
            statement.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw new APIMgtDAOException("Couldn't Delete System Application", e);
        } finally {
            connection.setAutoCommit(DAOUtil.isAutoCommit());
        }
    } catch (SQLException e) {
        String errorMsg = "Error while creating database connection/prepared-statement";
        throw new APIMgtDAOException(errorMsg, e);
    }
}

Example 34 with Database

use of org.wso2.carbon.humantask.core.db.Database in project carbon-apimgt by wso2.

the class AnalyticsDAOImpl method getAPIInfo.

/**
 * @see AnalyticsDAO#getAPIInfo(Instant, Instant, String)
 */
@Override
@SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
public List<APIInfo> getAPIInfo(Instant fromTimestamp, Instant toTimestamp, String createdBy) throws APIMgtDAOException {
    final String query;
    if (StringUtils.isNotEmpty(createdBy)) {
        query = "SELECT UUID,PROVIDER,NAME,CONTEXT,VERSION,CREATED_TIME,CURRENT_LC_STATUS, LC_WORKFLOW_STATUS  " + "FROM AM_API " + "WHERE CREATED_TIME BETWEEN ? AND ? " + "AND CREATED_BY = ? " + "ORDER BY CREATED_TIME ASC";
    } else {
        query = "SELECT UUID,PROVIDER,NAME,CONTEXT,VERSION,CREATED_TIME,CURRENT_LC_STATUS, LC_WORKFLOW_STATUS  " + "FROM AM_API " + "WHERE CREATED_TIME BETWEEN ? AND ? " + "ORDER BY CREATED_TIME ASC";
    }
    List<APIInfo> apiInfoList = new ArrayList<>();
    try (Connection connection = DAOUtil.getConnection();
        PreparedStatement statement = connection.prepareStatement(query)) {
        statement.setTimestamp(1, Timestamp.from(fromTimestamp));
        statement.setTimestamp(2, Timestamp.from(toTimestamp));
        if (StringUtils.isNotEmpty(createdBy)) {
            statement.setString(3, createdBy);
        }
        log.debug("Executing query: {} ", query);
        statement.execute();
        try (ResultSet rs = statement.getResultSet()) {
            while (rs.next()) {
                APIInfo apiInfo = new APIInfo();
                apiInfo.setId(rs.getString("UUID"));
                apiInfo.setProvider(rs.getString("PROVIDER"));
                apiInfo.setName(rs.getString("NAME"));
                apiInfo.setContext(rs.getString("CONTEXT"));
                apiInfo.setVersion(rs.getString("VERSION"));
                apiInfo.setCreatedTime(rs.getTimestamp("CREATED_TIME").getTime());
                apiInfo.setLifeCycleStatus(rs.getString("CURRENT_LC_STATUS"));
                apiInfo.setWorkflowStatus(rs.getString("LC_WORKFLOW_STATUS"));
                apiInfoList.add(apiInfo);
            }
        }
    } catch (SQLException e) {
        throw new APIMgtDAOException("Error while creating database connection/prepared-statement", e);
    }
    return apiInfoList;
}

Example 35 with Database

use of org.wso2.carbon.humantask.core.db.Database in project carbon-apimgt by wso2.

the class AnalyticsDAOImpl method getAPICount.

/**
 * @see AnalyticsDAO#getAPICount(Instant, Instant, String)
 */
@Override
@SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
public List<APICount> getAPICount(Instant fromTime, Instant toTime, String createdBy) throws APIMgtDAOException {
    final String query;
    if (StringUtils.isNotEmpty(createdBy)) {
        query = "SELECT COUNT(UUID) AS count, CREATED_TIME AS time " + "FROM AM_API " + "WHERE (CREATED_TIME BETWEEN ? AND ?) " + "AND CREATED_BY = ? " + "GROUP BY CREATED_TIME " + "ORDER BY CREATED_TIME ASC";
    } else {
        query = "SELECT COUNT(UUID) AS count, CREATED_TIME AS time " + "FROM AM_API " + "WHERE (CREATED_TIME BETWEEN ? AND ?) " + "GROUP BY CREATED_TIME " + "ORDER BY CREATED_TIME ASC";
    }
    List<APICount> apiInfoList = new ArrayList<>();
    try (Connection connection = DAOUtil.getConnection();
        PreparedStatement statement = connection.prepareStatement(query)) {
        statement.setTimestamp(1, Timestamp.from(fromTime));
        statement.setTimestamp(2, Timestamp.from(toTime));
        if (StringUtils.isNotEmpty(createdBy)) {
            statement.setString(3, createdBy);
        }
        log.debug("Executing query: {} ", query);
        statement.execute();
        try (ResultSet rs = statement.getResultSet()) {
            long count = 0;
            while (rs.next()) {
                APICount apiCount = new APICount();
                count += rs.getLong("count");
                apiCount.setTimestamp(rs.getTimestamp("time").getTime());
                apiCount.setCount(count);
                apiInfoList.add(apiCount);
            }
        }
    } catch (SQLException e) {
        throw new APIMgtDAOException("Error while creating database connection/prepared-statement", e);
    }
    return apiInfoList;
}