CODEWITHSUNDEEP
springspring-jdbc
Mike Croteau
Mike Croteau

Reputation: 1132

Spring JDBC : Inconsistent results when performing Order By

Any help would be greatly appreciated. I am working on a project using Spring JDBC for data access and am performing a simple query with an order by column expression, I am currently getting inconsistent results meaning the order by doesn't seem to be working. I have tried more than one database still no avail.

String sql = "select * from account where upper(name) like upper(:query) order by name asc";
MapSqlParameterSource params = new MapSqlParameterSource().addValue("query", "%" + query + "%");

List<Account> accountsSearched = namedParameterJdbcTemplate.query(sql, params, new BeanPropertyRowMapper<Account>(Account.class));

Any ideas what could be the issue?

Upvotes: 0

Views: 472

Answers (1)

Nitishkumar Singh
Nitishkumar Singh

Reputation: 1839

So the problem is not within your SQL code, but problem exist in search method implementation

Existing Code

public List<Account> search(String uncleanedQuery, int offset) {
    String query = uncleanedQuery.replaceAll("([-+.^:,])","");
    String sql = "select * from account where upper(name) like upper(:query) order by name asc";
    MapSqlParameterSource params = new MapSqlParameterSource().addValue("query", "%" + query + "%");
    List<Account> accountsSearched = namedParameterJdbcTemplate.query(sql, params, new BeanPropertyRowMapper<Account>(Account.class));
    Set<Account> accountSearchSet = new HashSet<Account>(accountsSearched);
    List<Account> accounts = new ArrayList<Account>(accountSearchSet);
    return accounts;
}

In the above code, we are fetching data correctly but assigning it to HashSet. HashSet does not respect ordering by name and generates random order for Account, due to which you are getting random order every time.

Solution 1: There is no reason, you actually need Set. Using set just making your program slow. If you want to get DISTINCT data then modify SQL query.

public List<Account> search(String uncleanedQuery, int offset) {
    String query = uncleanedQuery.replaceAll("([-+.^:,])","");
    String sql = "select * from account where upper(name) like upper(:query) order by name asc";
    MapSqlParameterSource params = new MapSqlParameterSource().addValue("query", "%" + query + "%");
    List<Account> accountsSearched = namedParameterJdbcTemplate.query(sql, params, new BeanPropertyRowMapper<Account>(Account.class));
    return accountsSearched;
}

Solution 2:

Still, you want to go with your approach then change code to use TreeSet and order based on the name

public List<Account> search(String uncleanedQuery, int offset) {
    String query = uncleanedQuery.replaceAll("([-+.^:,])", "");
    System.out.println("Search Query Called");
    String sql = "select * from account where upper(name) like upper(:query) order by name";

    MapSqlParameterSource params = new MapSqlParameterSource().addValue("query", "%" + query + "%");

    List<Account> accountsSearched = namedParameterJdbcTemplate.query(sql, params,
            new BeanPropertyRowMapper<Account>(Account.class));

    Comparator<Account> comp = new Comparator<Account>() {

        @Override
        public int compare(Account a1, Account a2) {
            return a1.getName().compareTo(a2.getName());
        }
    };

    SortedSet<Account> accountSearchSet = new TreeSet<Account>(comp);
    accountSearchSet.addAll(accountsSearched);

    List<Account> accounts = new ArrayList<Account>(accountSearchSet);

    return accounts;
}

Upvotes: 1

Related Questions