Prepared statement returns false but row is inserted?
java, mysql, prepared-statement, sql
Solution
Your primary key is based on 3 fields:
- id (which has auto increment value)
- username
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).
Problem
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; } ```