• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Unique Columns

 
Drew Lane
Ranch Hand
Posts: 296
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am going to specify the 'username' column in my database (mySQL) as unique.
When I try to insert a duplicate username into that column, what kind of error / exception should I expect in Java?
How should it be handled?
The ultimate goal here is to notify the user that the username already exists and that they should choose another.
Thanks!
Drew
 
Dave Vick
Ranch Hand
Posts: 3244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Drew
The exception would be thrown when you use the excuteUpdate method of the statement - the only exception that it can throw is a SQLException. After you catch the exception you would just have to examine the vendor specific error code returned to determine the specific error. OF course if you tailor it by vendor then it doesn't make it very portable. You could, instead, check for an existing entry before trying to insert a new one.
 
Graham Thorpe
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First u retreve the usernames from database then u can compare two usernames i mean databse username and inserted username.So if two are same means u can print message "username already exists choose another name ".I hope so u can understood...
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I agree with Dave to query before the insert to find out if the username exists. The error number and message can change at any time without notice ( a patch, or upgrade to newer version of DB ), that's when all hell breaks loose and everyone starts pointing fingers...
by the way, I'm currently working on an automated username/password generation program and I do a "select count(*) from xxx where username = 'xxxxx'" for all the reasons stated above.
Jamie
 
Drew Lane
Ranch Hand
Posts: 296
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the tips!
I got this working as was suggested by checking the database to see if the username exists before inserting it in the database.
I used "select * from xxx where username = 'xxxx'", and then "while(resultSet.next()){...}", etc.
This works, but I'd rather use "select count(*) from xxx where username = 'xxx'" if this is more efficient (as Jamie suggested).
The thing is, I'm not really sure how to retrieve the value returned by this count(*) function. It must return an int, right? Which method should I use for this?
Thanks again,
Drew
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Drew your assumption was correct, rs.getInt() is the method to use.
Something like that should work for you.
Jamie
 
Drew Lane
Ranch Hand
Posts: 296
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK that makes sense, Jamie.
I wonder how much faster this will be than the simple method I mentioned (especially using MySQL).
I suppose since the result set only contains an int that will help, too.
Drew
 
Paulo Salgado
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Drew,
I have the impression SELECT'ing a column would be slightly faster than adding a function to the statement, unless the DB optimizer is smart enough to recognize you have defined the column you use in your predicate (WHERE clause) as unique.
Since you asked about performance, you may want to consider:
- Do not use 'SELECT *' in your query. This will force object allocations for all column values retrieved. For your current case you don't even need to specify a column, you just want to make sure the row exists. Try 'SELECT 1 FROM <your table> WHERE <your predicate>. If rs.next() is true you have a row, if not you can insert your new user
- Think about using PreparedStatement
- I don't know MySQL but if this column is not the Primary Key, try to have an index created on it (you may have done this already by creating an unique index to force it unique).
Good luck.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic