asked on

IN Statement Query

I have a count query which i am using to determine if the user exist or not. I am having an IN Statement in the query. Unfortunately everytime i run spring throws an error that the column value is greater than 2. If someone can point what i am doing wrong that would be great.

private final static String VALIDATE_USER_SQL = " select count(*) from user_table where id = ? and user in (?) ";
 
public boolean validateUser(final UserModel userModel, final List user) {
 
		if (LOG.isInfoEnabled()) {
			LOG
					.info("inside validateUser()");
		}
 
		Object[] parameters = new Object[] { new Integer(userModel.getUserId(), user };
		int[] paramTypes = { Types.INTEGER, Types.VARCHAR };
		int count = 0;
		try {
 
			count = getJdbcTemplate().queryForInt(VALIDATE_USER_SQL,
					parameters, paramTypes);
 
			if (count > 0) {
 
				if (LOG.isDebugEnabled()) {
					LOG.debug("Count is [ " + count + " ]");
				}
 
				return true;
			}
 
		}
 
		catch (final DataAccessException ex) {
 
			if (LOG.isErrorEnabled()) {
				LOG.error("DataAccessException [ " + ex.getMessage() + " ]");
			}
		}
 
		return false;
	}

Open in new window

SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mrjoltcola

You did not say which database; if you are using Oracle you can pass Java collections in as bind variables. You cannot use a direct IN(:var) but you can pass it as a collection and cast it.

http://knol.google.com/k/franck-pachot/oracle-passing-a-list-as-bind-variable/17uabcrki6uux/2#

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CEHJ

Otherwise, if you can't use that or don't want to narrow the portability of your code, just concatenate the List as part of the sql string and use the other parameter as the only bind variable. Although i must say i'm a little surprised that 'getUserId' can have any relevance to more than one user ...

Micheal_Male

ASKER

I am suing postgres DB

CEHJ

>>I am suing postgres DB

What, for not allowing you to use a List as a bin variable..? ;-)

CEHJ

(bind variable)

Micheal_Male

ASKER

LOL i am sorry i meant using heheh. I will search on bind variable and will implement the solution to see if it works. Will update.

Micheal_Male

ASKER

Or i can use a loop to see what the user list size is and pass the id and the user in a loop. If count is > 0 then break and return true. But i was trying to find an efficient way like Bind variables. Let me see if i can implement it.

mrjoltcola

>>I am suing postgres DB

LOL, thanks for the laugh... :)

Guy Hengel [angelIII / a3]

I am also sure the function I mentioned can be translated into postgreSql ... :)

Micheal_Male

ASKER

Thanks All. Angel idea is the perfect and easy one. Worked perfect,.