检查数据库是否具有相同的值

Good evening. Below is my code on adding items in my database

晚上好。下面是我在数据库中添加项目的代码

String sql = "Insert into userinfo(firstname,lastname,contactNumber,email,address,username,password,accountType) value (?,?,?,?,?,?,?,?)";
String accountType = (String) jComboBoxAccType.getSelectedItem();
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, jTextFieldFistName.getText());
        ps.setString(2, jTextFieldLastName.getText());
        ps.setString(3, jTextFieldContactNumber.getText());
        ps.setString(4, jTextFieldEmail.getText());
        ps.setString(5, jTextFieldAddress.getText());
        ps.setString(6, jTextFieldUsername.getText());
        ps.setString(7, jTextFieldPassword.getText());
        ps.setString(8, accountType);
        ps.execute();

How would I be able to check if there is already an existing username and password before adding?

在添加之前,我如何能够检查是否已存在用户名和密码?

4 个解决方案

#1

Normally, you want the database to enforce such data integrity rules itself. This ensures that the data is correct. You don't want to check at the application-level, because that introduces race conditions (two inserts happening at essentially the same time, where both validate that the table has not duplicates and then both insert the same values).

通常,您希望数据库本身强制执行此类数据完整性规则。这可确保数据正确无误。您不希望在应用程序级别进行检查,因为这会引入竞争条件(两个插入基本上同时发生,其中两个插入都验证表没有重复,然后都插入相同的值)。

You can guarantee uniqueness using a unique constraint or unique index (the former is implemented using the latter). This will generate an error when a duplicate value is inserted. It is easy to create:

您可以使用唯一约束或唯一索引来保证唯一性(前者使用后者实现)。插入重复值时,这将生成错误。它很容易创建:

alter table userinfo add constraint unq_username_password unique (username, password);

That said, normally a user would have only one password, so the constraint would be only on the user name:

也就是说,通常用户只有一个密码,因此约束只能在用户名上:

alter table userinfo add constraint unq_username unique (username);

#2

You need something like this code

你需要像这样的代码

PreparedStatement ps=connection.prepareStatement("select 1 from userinfo where username=? and password=?");
ps.setString(1, jTextFieldUsername.getText());
ps.setString(2, jTextFieldPassword.getText());
ps.execute();
if (ps.getResultSet.next()) {
// So, user and password already in database
}
else {
// Insert value...
}

But, as for me, check the existing same pair of (username,password) during adding new user to database is not a good idea.

但是,对我来说,在将新用户添加到数据库期间检查现有的同一对(用户名,密码)并不是一个好主意。

#3

There are 3 different ways to perform the checking:

有三种不同的方法来执行检查:

1) Create a query that search if a record with the same username and password exists, something like:

1)创建一个查询,搜索具有相同用户名和密码的记录是否存在,如下所示:

SELECT COUNT(*) as exists FROM userinfo WHERE username = :username AND password = :password

SELECT username as exists FROM userinfo WHERE username = :username AND password = :password

SELECT username as exists FROM userinfo WHERE username =:username AND password =:password

So if a duplicate record is found the "exists" field is going to return a value different than 0 or NULL.

因此,如果找到重复记录,则“exists”字段将返回不同于0或NULL的值。

2) Just create an unique index for both username and password and when you are trying to insert a duplicated record an error is going to be raised, so you have to use a a "try...catch" statement between the "ps.execute".

2)只需为用户名和密码创建一个唯一索引,当您尝试插入重复记录时,将引发错误,因此您必须在“ps.execute”之间使用“try ... catch”语句”。

3) Adding a subquery that checks if duplicate values exists (See: https://*.com/a/3025332/1641558)

3)添加一个子查询,检查是否存在重复值(参见:https://*.com/a/3025332/1641558)

#4

You should only change the query as,

您应该只将更改更改为,

Insert ignore into userinfo(firstname,lastname,contactNumber,email,address,username,password,accountType) value (?,?,?,?,?,?,?,?)

#1