Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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

0
Micheal_Male
Asked:
Micheal_Male
  • 4
  • 4
  • 2
  • +1
2 Solutions
 
CEHJCommented:
You can't parameterize (use bind variables in) a set unfortunately
0
 
mrjoltcolaCommented:
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#
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree.
however, if you have for example sql server, you could implement a function like I did (search for dbo.ParmsToList), and change the query like this:
private final static String VALIDATE_USER_SQL = " select count(*) from user_table where id = ? and user in ( select value from dbo.ParmsTolist(?, ',') ) ";

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CEHJCommented:
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 ...
0
 
Micheal_MaleAuthor Commented:
I am suing postgres DB
0
 
CEHJCommented:
>>I am suing postgres DB

What, for not allowing you to use a List as a bin variable..? ;-)
0
 
CEHJCommented:
(bind variable)
0
 
Micheal_MaleAuthor 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.
0
 
Micheal_MaleAuthor 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.
0
 
mrjoltcolaCommented:
>>I am suing postgres DB

LOL, thanks for the laugh... :)

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I am also sure the function I mentioned can be translated into postgreSql ... :)
0
 
Micheal_MaleAuthor Commented:
Thanks All. Angel idea is the perfect and easy one. Worked perfect,.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now