Examples with MapSqlParameterSource org.springframework.jdbc.core.namedparam.MapSqlParameterSource used on opensource projects

Example 16 with MapSqlParameterSource

use of org.springframework.jdbc.core.namedparam.MapSqlParameterSource in project perun by CESNET.

the class UsersManagerImpl method getUsersByIds.

public List<User> getUsersByIds(PerunSession sess, List<Integer> usersIds) throws InternalErrorException {
    // If usersIds is empty, we can immediatelly return empty results
    if (usersIds.size() == 0) {
        return new ArrayList<User>();
    }
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("ids", usersIds);
    try {
        return namedParameterJdbcTemplate.query("select " + userMappingSelectQuery + "  from users where users.id in ( :ids )", parameters, USER_MAPPER);
    } catch (EmptyResultDataAccessException ex) {
        return new ArrayList<User>();
    } catch (RuntimeException ex) {
        throw new InternalErrorException(ex);
    }
}

Example 17 with MapSqlParameterSource

use of org.springframework.jdbc.core.namedparam.MapSqlParameterSource in project perun by CESNET.

the class SearcherImpl method getUsers.

public List<User> getUsers(PerunSession sess, Map<Attribute, String> attributesWithSearchingValues) throws InternalErrorException {
    StringBuilder query = new StringBuilder();
    query.append("select distinct " + UsersManagerImpl.userMappingSelectQuery + " from users ");
    List<String> whereClauses = new ArrayList<String>();
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    int counter = 0;
    for (Attribute key : attributesWithSearchingValues.keySet()) {
        counter++;
        String value = attributesWithSearchingValues.get(key);
        query.append("left join user_attr_values val" + counter + " ");
        query.append("on val" + counter + ".user_id=users.id and val" + counter + ".attr_id=" + key.getId() + " ");
        query.append("left join attr_names nam" + counter + " on val" + counter + ".attr_id=nam" + counter + ".id ");
        if (value == null || value.isEmpty()) {
            if (key.getType().equals(LinkedHashMap.class.getName()) || key.getType().equals(BeansUtils.largeStringClassName) || key.getType().equals(BeansUtils.largeArrayListClassName)) {
                whereClauses.add("val" + counter + ".attr_value_text IS NULL ");
            } else {
                whereClauses.add("val" + counter + ".attr_value IS NULL ");
            }
        } else {
            if (key.getType().equals(Integer.class.getName())) {
                key.setValue(Integer.valueOf(value));
                whereClauses.add("val" + counter + ".attr_value=:v" + counter + " ");
                whereClauses.add("nam" + counter + ".type=:n" + counter + " ");
                parameters.addValue("n" + counter, Integer.class.getName().toString());
                parameters.addValue("v" + counter, BeansUtils.attributeValueToString(key));
            } else if (key.getType().equals(String.class.getName())) {
                key.setValue(value);
                whereClauses.add("lower(" + Compatibility.convertToAscii("val" + counter + ".attr_value") + ")=lower(" + Compatibility.convertToAscii(":v" + counter) + ") ");
                whereClauses.add("nam" + counter + ".type=:n" + counter + " ");
                parameters.addValue("n" + counter, String.class.getName().toString());
                parameters.addValue("v" + counter, BeansUtils.attributeValueToString(key));
            } else if (key.getType().equals(BeansUtils.largeStringClassName)) {
                key.setValue(value);
                whereClauses.add("lower(" + Compatibility.convertToAscii("val" + counter + ".attr_value_text") + ") LIKE lower(" + Compatibility.convertToAscii(":v" + counter) + ") ");
                whereClauses.add("nam" + counter + ".type=:n" + counter + " ");
                parameters.addValue("n" + counter, BeansUtils.largeStringClassName);
                parameters.addValue("v" + counter, BeansUtils.attributeValueToString(key));
            } else if (key.getType().equals(Boolean.class.getName())) {
                key.setValue(value);
                whereClauses.add("lower(" + Compatibility.convertToAscii("val" + counter + ".attr_value") + ")=lower(" + Compatibility.convertToAscii(":v" + counter) + ") ");
                whereClauses.add("nam" + counter + ".type=:n" + counter + " ");
                parameters.addValue("n" + counter, Boolean.class.getName().toString());
                parameters.addValue("v" + counter, BeansUtils.attributeValueToString(key));
            } else if (key.getType().equals(ArrayList.class.getName())) {
                List<String> list = new ArrayList<String>();
                list.add(value);
                key.setValue(list);
                whereClauses.add("val" + counter + ".attr_value LIKE :v" + counter + " ");
                whereClauses.add("nam" + counter + ".type=:n" + counter + " ");
                parameters.addValue("n" + counter, ArrayList.class.getName().toString());
                parameters.addValue("v" + counter, '%' + BeansUtils.attributeValueToString(key).substring(0, BeansUtils.attributeValueToString(key).length() - 1) + '%');
            } else if (key.getType().equals(BeansUtils.largeArrayListClassName)) {
                List<String> list = new ArrayList<String>();
                list.add(value);
                key.setValue(list);
                whereClauses.add("val" + counter + ".attr_value_text LIKE :v" + counter + " ");
                whereClauses.add("nam" + counter + ".type=:n" + counter + " ");
                parameters.addValue("n" + counter, BeansUtils.largeArrayListClassName);
                parameters.addValue("v" + counter, '%' + BeansUtils.attributeValueToString(key).substring(0, BeansUtils.attributeValueToString(key).length() - 1) + '%');
            } else if (key.getType().equals(LinkedHashMap.class.getName())) {
                String[] splitMapItem = value.split("=");
                if (splitMapItem.length == 0)
                    throw new InternalErrorException("Value can't be split by char '='.");
                String splitKey = splitMapItem[0];
                StringBuilder splitValue = new StringBuilder();
                if (splitMapItem.length > 1) {
                    for (int i = 1; i < splitMapItem.length; i++) {
                        if (i != 1)
                            splitValue.append('=');
                        splitValue.append(splitMapItem[i]);
                    }
                }
                Map<String, String> map = new LinkedHashMap<String, String>();
                map.put(splitKey, splitValue.length() == 0 ? null : splitValue.toString());
                key.setValue(map);
                whereClauses.add("val" + counter + ".attr_value_text LIKE :v" + counter + " or val" + counter + ".attr_value_text LIKE :vv" + counter + " ");
                whereClauses.add("nam" + counter + ".type=:n" + counter + " ");
                parameters.addValue("n" + counter, LinkedHashMap.class.getName().toString());
                parameters.addValue("v" + counter, BeansUtils.attributeValueToString(key) + '%');
                parameters.addValue("vv" + counter, "%," + BeansUtils.attributeValueToString(key) + '%');
            } else {
                throw new InternalErrorException(key + " is not type of integer, string, boolean, array or hashmap.");
            }
        }
    }
    //Add Where clauses at end of sql query
    boolean first = true;
    for (String whereClause : whereClauses) {
        if (first) {
            query.append("where ");
            query.append(whereClause);
            first = false;
        } else {
            query.append("and ");
            query.append(whereClause);
        }
    }
    try {
        return jdbc.query(query.toString(), parameters, UsersManagerImpl.USER_MAPPER);
    } catch (EmptyResultDataAccessException e) {
        return new ArrayList<User>();
    } catch (RuntimeException e) {
        throw new InternalErrorException(e);
    }
}

Example 18 with MapSqlParameterSource

use of org.springframework.jdbc.core.namedparam.MapSqlParameterSource in project perun by CESNET.

the class GroupsManagerImpl method getGroupsByIds.

public List<Group> getGroupsByIds(PerunSession sess, List<Integer> groupsIds) throws InternalErrorException {
    // If groupsIds are empty, we can immediately return empty result
    if (groupsIds.size() == 0) {
        return new ArrayList<Group>();
    }
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("ids", groupsIds);
    try {
        return this.namedParameterJdbcTemplate.query("select " + groupMappingSelectQuery + " from groups where groups.id in ( :ids )", parameters, GROUP_MAPPER);
    } catch (EmptyResultDataAccessException ex) {
        return new ArrayList<Group>();
    } catch (RuntimeException ex) {
        throw new InternalErrorException(ex);
    }
}

Example 19 with MapSqlParameterSource

use of org.springframework.jdbc.core.namedparam.MapSqlParameterSource in project perun by CESNET.

the class MembersManagerImpl method getMembersByUsersIds.

public List<Member> getMembersByUsersIds(PerunSession sess, List<Integer> usersIds, Vo vo) throws InternalErrorException {
    // If usersIds is empty, we can immediatelly return empty results
    if (usersIds.size() == 0) {
        return new ArrayList<Member>();
    }
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("ids", usersIds);
    parameters.addValue("vo", vo.getId());
    try {
        return this.namedParameterJdbcTemplate.query("select " + memberMappingSelectQuery + " from members join users on members.user_id=users.id where members.user_id in ( :ids ) and members.vo_id=:vo " + "order by users.last_name, users.first_name", parameters, MEMBER_MAPPER);
    } catch (EmptyResultDataAccessException ex) {
        return new ArrayList<Member>();
    } catch (RuntimeException ex) {
        throw new InternalErrorException(ex);
    }
}

Example 20 with MapSqlParameterSource

use of org.springframework.jdbc.core.namedparam.MapSqlParameterSource in project perun by CESNET.

the class TaskResultDaoJdbc method getTaskResultsForDestinations.

public List<TaskResult> getTaskResultsForDestinations(List<String> destinationsNames) throws InternalErrorException {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("destinations", destinationsNames);
    try {
        return getNamedParameterJdbcTemplate().query("select " + taskResultMappingSelectQuery + ", " + ServicesManagerImpl.destinationMappingSelectQuery + ", " + ServicesManagerImpl.serviceMappingSelectQuery + " from tasks_results left join destinations on tasks_results.destination_id = destinations.id" + " left join tasks on tasks.id = tasks_results.task_id " + " left join exec_services on exec_services.id = tasks.exec_service_id" + " left join services on services.id = exec_services.service_id where destinations.destination in ( :destinations )", parameters, TASKRESULT_ROWMAPPER);
    } catch (RuntimeException e) {
        throw new InternalErrorException(e);
    }
}