user3211740 user3211740 - 6 months ago 48
Java Question

Java SQL Unique username

Blank database.

If I enter a username example1 it will add to the database. Then if I try and re-add it again it will be stopped by my code as it already in the database. Then if I enter example2 and add it it will go into the database and then if I try and re-add it my code lets it go through the only thing that stops it is the database primary key:

I believe this line to be the issue:

PreparedStatement st = connection.prepareStatement("select * from Members order by username asc");


Here is the whole method:

public static boolean CheckUsernameExists(String username){

boolean usernameExists = false;

try{
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(urlDB, usernameDB, passwordDB);

PreparedStatement st = connection.prepareStatement("select * from Members order by username asc");
ResultSet r1=st.executeQuery();
String usernameCounter;
if(r1.next()){
usernameCounter = r1.getString("username");
if(usernameCounter.equals(username)){
System.out.println(username);
usernameExists = true;
}
}
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
}catch (ClassNotFoundException cE){
System.out.println("Class Not Found Exception: "+ cE.toString());
}

return usernameExists;
}

Answer

Should be better like this:

    PreparedStatement st = connection.prepareStatement("select * from Members where username = ? ");
    st.setString(1, username);
    ResultSet r1=st.executeQuery();
    if(r1.next()){
         System.out.println('user already exist in database!");
    }
    else{
        //Add to database
    }

Or since your method is CheckUsernameExists:

public static boolean CheckUsernameExists(String username){

    boolean usernameExists = false;
    try{
        PreparedStatement st = connection.prepareStatement("select * from Members where username = ? ");
        st.setString(1, username);
        ResultSet r1=st.executeQuery();
        if(r1.next()){
           usernameExists = true;
        }
    catch (Exception e) {
        System.out.println("SQL Exception: "+ e.toString());
    }
    return usernameExists;
}