mysql jdbc: inserting true value to a bit column becomes false

Question

mysql jdbc: inserting true value to a bit column becomes false, but command line client works fine. For example,

Employee

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | varchar(8)  | NO   | PRI | NULL    |       |
| name       | varchar(20) | NO   |     | NULL    |       |
| full_time  | bit(1)      | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

command line:

mysql> insert into Employee (id,name,full_time) values ('100', 'John', true);

mysql> select * from Employee;
+--------+------+-----------+
| id     | name | full_time |
+--------+------+-----------+
| 100    | John |  (symbol) |      
+--------+------+-----------+

mysql> select count(*) from Employee where full_time=true;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Works.

But JDBC

String sql = "Insert into Employee (id,name,full_time) values (?, ?, ?)";
PreparedStatement s = connection.prepareStatement(sql);
s.setObject(1, "100");
s.setObject(2, "John");
s.setObject(3, true);
s.executeUpdate();

The row is inserted, but The full_time column is false.

mysql> select * from Employee;
+--------+------+-----------+
| id     | name | full_time |
+--------+------+-----------+
| 100    | John |           |      
+--------+------+-----------+


mysql> select count(*) from Employee where full_time=true;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

What might be wrong?

Answer 1

Use setBoolean to assign the true/false value to the full_time column:

String sql = "Insert into Employee (id, name, full_time) values (?, ?, ?)";
PreparedStatement s = connection.prepareStatement(sql);
s.setString(1, "100");
s.setString(2, "John");
s.setBoolean(3, true);
s.executeUpdate();

Always use the appropriate setter for the type you are trying to insert/update. I don't know how s.setObject(1, '100') was even compiling, but string literals in Java use double quotes, and we should use setString for assigning them in a statement.