setBoolean() method of java.sql.PreparedStatement

Question

As per the oracle documentation -

https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setBoolean(int,%20boolean)

setBoolean(int parameterIndex,boolean x)

method Sets the designated parameter to the given Java boolean value. The driver converts this to an SQL BIT or BOOLEAN value when it sends it to the database.

Here is there any way to know when the value will be stored as SQL BIT? and when will it store as BOOLEAN?

I am using oracle database, I have used this method passing the value as true(Java Boolean) and the value getting stored in database is 1 instead of Y. What could be the reason?

I'm assuming the actual type depends on the type of the column in the relevant DB table. How did you define that column? — Eran, Aug 02 '17 at 10:56
Can you define your table ? Maybe you have that column as a bit. — Arpit, Aug 02 '17 at 10:57
Folks - A quick search suggests that Oracle doesn't have a BIT data type (at the storage level): https://stackoverflow.com/questions/3726758/is-there-a-boolean-type-in-oracle-databases — T.J. Crowder, Aug 02 '17 at 11:01
Whether it's stored as BIT or BOOLEAN is up to you, and how you have defined the column type in the database. — Jesper, Aug 02 '17 at 11:01
@Eran column Data type is CHAR(1 BYTE) however that table has other rows where this column value is stored as Y. — Curious Techie, Aug 02 '17 at 11:02
Simply put, Oracle doesn't have a boolean type. The common way is to declare a small number field and use `1` and `0` for boolean values. And this is probably what the driver does for you - sets the field to `1`. It doesn't know that you think that `Y` is `true` and `N` is `false`. — RealSkeptic, Aug 02 '17 at 11:04
@RealSkeptic Okay, but then why there are other records same column showing value as Y? Is it random? — Curious Techie, Aug 02 '17 at 11:05
I suppose that's information that was inserted by a different procedure somewhere else. Probably in the way that T.J. Crowder suggests in his answer. — RealSkeptic, Aug 02 '17 at 11:06

T.J. Crowder · Accepted Answer · 2017-08-02T11:11:53.150

5

Since it appears Oracle doesn't have either BIT or BOOLEAN as a storage data type, don't use setBoolean. It's defined as being for those kinds of columns.

Instead, if it's a CHAR(1), use setString and whatever values mean "true" and "false" to you. From your question, for instance, you might expect Y and N:

.setString(index, x ? "Y" : "N");

edited Aug 02 '17 at 11:11

answered Aug 02 '17 at 11:04

T.J. Crowder

1,031,962
187
1,923
1,875

Thanks @T.J. Crowder – Curious Techie Aug 02 '17 at 11:07

smilyface · Answer 2 · 2022-02-11T13:58:34.083

T.J. Crowder's answer is correct.

CREATE TABLE TEST_1
    (
    MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT 
    CHECK (MEAL IN ('Y', 'N'))
    );

Output:

Table: TEST_1
Column name: MEAL
Data type: CHAR(1 BYTE) 
Nullable: Yes

Bean:

private boolean meal;
public Boolean getMeal(){
  return meal;
}
public void setMeal(Boolean meal){
  return this.meal = meal;
}

And then use,

preparedStatement.setString(indexOfMealCol, getMeal() ? "Y" : "N");

Note:

Providing any other value than Y or N, it will throw error
Null value accepted

setBoolean() method of java.sql.PreparedStatement

2 Answers2