Java SQL 唯一用户名

Question

空白数据库。

如果我输入用户名 example1，它将添加到数据库中。然后，如果我再次尝试重新添加它，它将被我的代码停止，因为它已经在数据库中。然后，如果我输入 example2 并添加它，它将进入数据库，然后如果我尝试重新添加它，我的代码将让它通过，唯一阻止它的是数据库主键:

我认为这一行是问题所在:

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

这是整个方法:

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 1

应该这样比较好:

    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
    }

或者由于您的方法是 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;
}