Java boolean function that returns if data exists in database

Question

This is the function that I have currently. However, it is not returning anything.

public boolean authenticateDisabledRegions(int analystId, List<Integer> regionIds) {
    Integer query = jdbcTemplate.queryForObject(
        "select count(*) from ANALYST_REGION_MAPPING WHERE STATUS = 0 AND ANALYST_ID = ? AND REGION_ID = ?", 
        Integer.class, analystId, regionIds);

    return query != null && query > 0;
}

I tried to do this but it does not seem to be working, I have been looking it up and there are a lot of suggestions that say fo the ResultSet rs with rs.next() I am just wondering what the best approach is for this type of function.

The purpose of this is to check whether it exists, if it does, it will call update (&change status), if not, it will call insert.

Well, it'll return *something*. What is it returning and how does it differ from what you want? Are there any errors? — Carcigenicate, Feb 10 '17 at 20:24
Please provide correct source code. For the current one the method parameters are different than to the ones creating the query. — René Scheibe, Feb 10 '17 at 20:28
No need to check query != null because If there is no record to count, zero will return. — LHA, Feb 10 '17 at 20:52

score 1 · Answer 1 · edited May 23 '17 at 11:53

1

If your input is a list of ids you should use SQL's "IN" clause and Spring's NamedParameterJdbcTemplate.

public boolean doesRegionExist(int analystId, List<Integer> regionIds) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("analystId", analystId);
    params.addValue("regionIds", regionIds);

    Integer result = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM USER_REGION_TABLE WHERE STATUS = 1 AND USER_ID = :analystId AND REGION_ID IN (:regionIds)", params, Integer.class);

    return result != null && result > 0;
}

Also see How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?

edited May 23 '17 at 11:53

Community

1
1

answered Feb 10 '17 at 20:44

René Scheibe

1,970
2
14
20

I'm getting this error: HTTP Status 500 - Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException USER_REGION_TABLE WHERE STATUS = 1 AND USER_ID = (:analystId) AND REGION_ID IN (:regionIds)]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type – user7470849 Feb 10 '17 at 21:58
Maybe you have to define the parameter type when using `addValue` - http://docs.spring.io/spring-framework/docs/2.5.x/api/org/springframework/jdbc/core/namedparam/MapSqlParameterSource.html#addValue(java.lang.String,%20java.lang.Object,%20int,%20java.lang.String) and http://stackoverflow.com/questions/22444447/mapsqlparametersource-does-not-map-properly-with-namedparameterjdbctemplate-for – René Scheibe Feb 10 '17 at 22:07

score 1 · Accepted Answer · answered Feb 13 '17 at 21:27

This was what worked for me in the end:

@Override
public boolean doesRegionExist(int userId, List<Integer> regionIds) {
    HashMap<String, Object> paramMap = new HashMap<String, Object>();
    paramMap.put("userId", analystId);
    paramMap.put("regionIds", regionIds);

    SqlRowSet result = namedParameterJdbcTemplate.queryForRowSet("SELECT * FROM USER_REGION_TABLE WHERE STATUS = 0 AND USER_ID = (:userId) AND REGION_ID IN (:regionIds)", paramMap);

        if (result.next())
            return true;
        else
            return false;

    }

score 0 · Answer 3 · answered Feb 10 '17 at 20:31

0

Try this one:

public boolean doesRegionExist(int userId, List<Integer> regionIds) {
    Integer result = jdbcTemplate.queryForObject("select count(*) from USER_REGION_TABLE WHERE STATUS = 1 AND USER_ID = ? AND REGION_ID = ?", Integer.class, analystId, region);

            return result != null & result > 0;
    }

answered Feb 10 '17 at 20:31

Volodymyr Grynda

41
3

That's not a solution as the `&` operator is just a binary AND. The SQL query itself is the problem. – René Scheibe Feb 10 '17 at 20:50

score 0 · Answer 4 · answered Feb 10 '17 at 20:35

Something wrong with your code

 public boolean doesRegionExist(int userId, List<Integer> regionIds) {
Integer query = jdbcTemplate.queryForObject("select count(*) from USER_REGION_TABLE WHERE STATUS = 1 AND USER_ID = ? AND REGION_ID = ?", Integer.class, analystId, region);

        return query != null && query > 0;
}

The parameter are userId and regionIds, but you passing analystId and region into queryForObject() method.

Java boolean function that returns if data exists in database

4 Answers4