We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

IN Statement Query

Micheal_Male
Micheal_Male asked
on
Medium Priority
258 Views
Last Modified: 2012-05-07
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

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Top Expert 2009

Commented:
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#
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2016

Commented:
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 ...

Author

Commented:
I am suing postgres DB
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>I am suing postgres DB

What, for not allowing you to use a List as a bin variable..? ;-)
CERTIFIED EXPERT
Top Expert 2016

Commented:
(bind variable)

Author

Commented:
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.

Author

Commented:
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.
Top Expert 2009

Commented:
>>I am suing postgres DB

LOL, thanks for the laugh... :)

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

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

Author

Commented:
Thanks All. Angel idea is the perfect and easy one. Worked perfect,.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.