This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement-2Sql queries

 
natasa jones
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everybody!
my problem is the following..i have a function so as to register a new User.i would like although first to chech if the username that the user specifies is already taken.the code is as follows:

public int saveUser(User user) throws ServiceException {
PreparedStatement preparedStatement = null;
PreparedStatement preparedStatement2;
int userId = 0;
Connection connection = null;
ResultSet resultSet = null;
ResultSet resultSet2;


try {

connection = dataSource.getConnection();
preparedStatement2 = connection.prepareStatement(SQL_usernameExists);
preparedStatement2.setString(1, user.getUserName());
resultSet2 = preparedStatement.executeQuery();

if (resultSet2 != null) {
userId=-1;
}

else if (resultSet2 == null) { //code for user registration//

what i am trying to do is to have this function returning -1 if this username already exists or registering the new user.but this code doesnt seem to work!it is caught in a ServiceException.
the sql_query i use to check if the username already exists is :
private static final String SQL_usernameExists = "select user_id from account where USERNAME=?";

can anyone help??
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, you should have a unique-ness constraint in the database so that at the very least, the database never allows 2 users to have the same name. In this way, you could just look for an exception to be thrown although since you expect a problem, its better to try to catch it ahead of time.

Second, usually checking if the user name exists and creating it about broken into multiple steps (not usually the same step) but if it must be the same step then you should use a transaction. There's nothing to stop two users both creating the same user name at the same time if both of the first queries get executed before both of the second. A transaction may (but not always) help to overcome this, which is why as I said earlier, a database constraint is needed.

Third, I'm not off hand sure why your query is failling, but if I had to guess I'd say its because you never get a null result set even when no queries match, you just get an empty one. You should be catching "if !rs.next()" instead. Another tip is that since you don't care about the output of the query (only that there is a match) use "select 1 from ..." since it has the same effect and doesn't transmit any real data.
[ August 21, 2006: Message edited by: Scott Selikoff ]
 
natasa jones
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello!
i wonder if you could explain what do you mean by saying:

you should have a unique-ness constraint in the database so that at the very least, the database never allows 2 users to have the same name. In this way, you could just look for an exception to be thrown although since you expect a problem, its better to try to catch it ahead of time.

how can i achieve this?by having "username" as key to the table?or is it something else?i dont really know much about these and its a bit confusing!
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, a key or UNIQUE keyword will accomplish this in most databases although it need not be the primary key.

Usernames fall under the category of 'natural keys'. Although it is convenient to make natural keys the primary keys, I generally avoid it and use integers since this allows for better join capabilities to other tables.
 
natasa jones
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi again!
well i followed your advice and i am catching the SQLException.to do so, i considered that username is the primary key though...
let's hope i wont have join problems!
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Its not that you would have join problems, its that joining might not be as easy or as fast. For example, if you want to track a user throughout the system (user purchase record, user grouping, etc) you would need to put their username in each table throughout the system. This could be a much larger field than an integer in most database systems and be harder to change a username if instead, you had a user id as the primary key and the username as a unique key. Again, though, this is up to you.

I nearly forgot- Natasa p,

You may not have noticed that we have a policy on screen names here at the ranch. It must consist of a first name, a space, and a valid last name.

Unfortunately, your screen name does not seem to conform with this policy. Please take a moment to change it.

Thanks,
Scott
 
natasa jones
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i have changed it,so the primary key is an integer!
thanks for your answer!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic