Check database if it has same values

Question

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?

You could, for example, simply create select query. Or you could add a UNIQUE index to your table, then that code will throw a SqlException, if it already exists. — Florian Schaetz, Mar 28 '16 at 11:04

score 1 · Answer 1 · answered Mar 28 '16 at 11:06

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);

This is the second way that I recommended. – Juan Lago Mar 28 '16 at 11:21 — Juan Lago, Mar 28 '16 at 11:21

score 0 · Answer 2 · answered Mar 28 '16 at 11:07

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.

score 0 · Answer 3 · edited May 23 '17 at 11:50

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:

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

or

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.

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".

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

score 0 · Answer 4 · answered Mar 28 '16 at 12:01

0

You should only change the query as,

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

answered Mar 28 '16 at 12:01

Dibakar Paul

165
11

Check database if it has same values

4 Answers4