This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes MySQL - add and remove username from table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL - add and remove username from table" Watch "MySQL - add and remove username from table" New topic
Author

MySQL - add and remove username from table

Giovanni De Stefano
Ranch Hand

Joined: Aug 17, 2004
Posts: 144
Hi guys,
I am creating a little web app with database access (MySQL). I have a java object that creates the connection to the database and stores username and password.

I have created the table with this query:


The methods to add and remove a user are:


How can I implement the following:
  • the username should be unique
  • the password should be encrypted
  • the update is thread safe?

  • Thank you all in advance,
    Giovanni
    [ May 14, 2005: Message edited by: Giovanni De Stefano ]

    SCJP 1.4
    Shailesh Chandra
    Ranch Hand

    Joined: Aug 13, 2004
    Posts: 1081

    Originally posted by Giovanni De Stefano:

    How can I implement the following:
  • the username should be unique



  • Why do you want user_name to be unique, however you can implement this by adding unique key to column.
    but my question still remain same because many user can have same name,you should make user_id as unique key or a composite key on user_id and user_name

  • the password should be encrypted



  • encrypt it in java so that this part remain database independant.
    There are plenty of API's on net use any

  • the update is thread safe?




  • What do you want to achieve, database itself provide locking mechanism.
    If there is something else please explain.

    Shailesh


    Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
    Jeanne Boyarsky
    author & internet detective
    Marshal

    Joined: May 26, 2003
    Posts: 30595
        
    154

    Giovanni,
    1) Add an unique index to make the username unique. This protects the database integrity. You will also want to check before adding to prevent exceptions.
    2) Most database have an encrypted datatype. Not sure what MySQL calls it though.
    3) As written, the update is thread safe. By default, JDBC uses atomic operations to access the database. If you add the check as described in #1, you can code a transaction to make sure the check and update are done together.


    [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
    Jeanne Boyarsky
    author & internet detective
    Marshal

    Joined: May 26, 2003
    Posts: 30595
        
    154

    Shailesh,
    I can think of legitimate reasons for user_id and user_name to be independent with each being unique individually. Suppose every user has a numeric id assigned on created the account. Mine could be 5. When I sign up, I say my username should be "boyarsky." This needs to be unique because I use it to sign in. How suppose I get married and want to change my username. If username was part of the key, this would be next to impossible. But if the fields are independent, my new name can map to user id 5 and everything else can continue as it was before.
    Shailesh Chandra
    Ranch Hand

    Joined: Aug 13, 2004
    Posts: 1081

    Jeanne,

    If user_name is used for login I am ok with It and agree with you.

    I was interpreting that user_id would be used for login so I asked why poster want user_name to be unique coz name may repeat

    and In continuation of same I just added option so that there can be same name multiple times.

    example:

    let say I got Id as 6 and name shailesh ...now I start login with 6
    new user having same name will get an Id of 7 and name shailesh and starts login with 7

    so It was just an option of composite primary key that a user can keep on login with id as 6or 7 and composite primary key will further help for tracking like when user logged in or logged out or when password was changed,previous password history

    I didn't think about change of name in future :roll:

    and this would lead me towards your approach to keep Id and name separate and make the Id as Primary key

    Shailesh
    Giovanni De Stefano
    Ranch Hand

    Joined: Aug 17, 2004
    Posts: 144
    Hi guys,
    thank you all for your help.

    I am veeeeery new with database stuff, and I am teaching myself.

    My idea is to create a web app where people can login and get personalized information, otherwise they will get general information available to everybody.

    I am doing this because I am studying Servlet and JSP, but to really understand how things work I need to code something by myself. The database access is a piece of the whole thing where I want to use and manage sessions, cookies, etc.

    Thank to you guys now I know I don't have to worry about concurrent access to the database.
    For the password encryption I am giving a look at the standard library.

    The username issue is not solved though. I don't want to let people share the same username, I want it to be unique. Once a username is set, the user cannot change it. When I was thinking of the user table, I thought it would have been nice to have a user ID, but basically I still don't know how to use this ID to make sure the username I am adding is unique.

    I thought of syncronizing the update, something like this:
    syncronize(the object that communicates with the database) {
    ask the database if the username I am going to add already exists,
    if it does exist tell the user
    if it doesn't exist add the username
    }

    this means that I have to send 2 queries to the database, but I still don't use the user ID. Is this what you guys do in "real life"?

    I don't have industrial experience, that's why I decided to develop this app. I also thought of storing the object that communicates with the database in a context attribute available to every servlet in the web app.
    Is this the right way to do things?

    Thank you very much for your support, any suggestion will be very appreciated!

    Giovanni
    Jeanne Boyarsky
    author & internet detective
    Marshal

    Joined: May 26, 2003
    Posts: 30595
        
    154

    Giovanni,
    If I had a requirement that the username could not changed, I would use it as the primary key (which forces uniqueness.) Then there is no need for userid. This is the scenario Shailesh was describing.

    In real life, I would allow the username to be changed. There are some sites that don't allow this to happen easily which is a bit frustrating. It isn't necessary if you are just practicing though.

    The login/personalization sounds like a great project to learn with since it touches everything.

    -----------------------------------------------------------------------
    Regardless of which approach you take, it is good to think about synchronization/transactions. Even if just conceptually.

    If you are guaranteed that your program is the only one accessing the database, you could synchronize through Java (as in your example.) However, you need to synchronize against all updates (including deletes) which is likely to introduce a bottleneck. You are better off using JDBC transactions (commit/rollback) to let the database manage this.
     
    GeeCON Prague 2014
     
    subject: MySQL - add and remove username from table