Prepared statement returns false but row is inserted?

Question

So this is my table:

mysql> DESCRIBE app_user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(40) | NO   | PRI | NULL    |                |
| password | varchar(40) | NO   |     | NULL    |                |
| email    | varchar(40) | NO   | PRI | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set

mysql>

What I am expecting is that, I should not be able to add 2 rows with same username because username is Primary Key..

And this is my Java Code:

@Test
public void shouldNotInsertWithSameUserName() throws IOException, SQLException {
    AppUserAccessObject appUserAccessObject = new AppUserAccessObject(new DatabaseConnectionImpl());
    Assert.assertFalse(appUserAccessObject.insertUser("koray", "email", "password"));
}

So this test passes, which means insertUser returns false.. However when I check the database, I see a new row insterted. What am I doing wrong? And the insertuser:

public boolean insertUser(String username,String email,String password) throws SQLException {
    PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO "+ tableName + "(username,email,password) VALUES(?,?,?)");
    preparedStatement.setString(1,username);
    preparedStatement.setString(2,email);
    preparedStatement.setString(3,password);
    boolean execute = preparedStatement.execute();
    return execute;
}

You misunderstood the return value of PreparedStatement#execute(). Please read carefully http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#execute%28%29 — user3470953, Apr 15 '14 at 16:01

Luiggi Mendoza · Accepted Answer · 2014-04-15T16:14:53.180

Your primary key is based on 3 fields:

id (which has auto increment value)
username
email

So, the primary key restricts a new row with these 3 fields having the same value. But since id is marked on auto increment and you're always sending null to it, it will generate a new value, thus never having these three fields with the same value.

A better design for your table would be having id as primary key only, and an Unique Key for your username and email fields. In fact, it would be even better having two unique keys: one for username and another for email.

This will do it:

CREATE TABLE app_user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username varchar(40),
    password varchar(40),
    email varchar(40),
    UNIQUE KEY (username),
    UNIQUE KEY (email)
);

Apart from the design problem in your database:

Use PreparedStatement#executeUpdate to execute DML statemens like INSERT, UPDATE and DELETE.
Use PreparedStatement#executeQuery when retrieving data from database through a SELECT.
Use PreparedStatement#execute when performing any kind of SQL statement like CREATE TABLE or ALTER TABLE.

Prepared statement returns false but row is inserted?

From PreparedStatement#execute javadoc:

Returns: true if the first result is a ResultSet object; false if the first result is an update count or there is no result.

In this case, you got a false result since you executed an update operation (INSERT is understood as an update operation).

Prepared statement returns false but row is inserted?

1 Answers1