MySQL - add and remove username from table

[Giovanni De Stefano]

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:

/**
 * MySQL database: users
 * MySQL table name: users
 * 
 * SQL Query:
 * CREATE TABLE `users`.`users` (
 * `user_ID` INTEGER UNSIGNED AUTO_INCREMENT,
 * `username` VARCHAR(45),
 * `password` VARCHAR(45),
 * `email` VARCHAR(45),
 * PRIMARY KEY(`user_ID`)
 * )
 */

The methods to add and remove a user are:

public void addUser(String username, String password, String email) {
        executeUpdateQuery("" +
                "INSERT INTO USERS(USERNAME, PASSWORD, EMAIL) " +
                "VALUES('" + username + "', '" + password + "', '" + email + "')");
    }
    public void removeUser(String username) {
        executeUpdateQuery("" +
                "DELETE FROM USERS " +
                "WHERE USERNAME='" + username + "'");
    }
 
    private void executeUpdateQuery(String query) {
        try {
            statement.executeUpdate(query);
        } catch (SQLException e) {
            System.err.println("Error! Cannot execute the update query : "
                    + query);
            e.printStackTrace();
        }
    }

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 ]

[Shailesh Chandra]

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

[Jeanne Boyarsky]

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]

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]

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]

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]

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.