• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MySQL - add and remove username from table

 
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
     
    Ranch Hand
    Posts: 1087
    Oracle Spring Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    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
     
    author & internet detective
    Posts: 41860
    908
    Eclipse IDE VI Editor Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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.
     
    Jeanne Boyarsky
    author & internet detective
    Posts: 41860
    908
    Eclipse IDE VI Editor Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Posts: 1087
    Oracle Spring Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Posts: 161
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Posts: 41860
    908
    Eclipse IDE VI Editor Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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.
     
    What are you saying? I thought you said that Santa gave you that. And this tiny ad:
    a bit of art, as a gift, that will fit in a stocking
    https://gardener-gift.com
    reply
      Bookmark Topic Watch Topic
    • New Topic