How to set a boolean CMP field?

[Rashmi Tambe]

Hi ,
I have an entity bean that maps to table having a column with datatype as varchar2(1). for e.g. the column name is empty.
This column is treated as a boolean flag. The enetity bean has methods ,
Boolan isEmtpy()
setEmpty(Boolean b)
The problem is when i set it as setEmpty(Boolean.TRUE). value 1 is inserted in database. How can i insert 'Y'/'N' instead os 1/0 using CMP?
I am using oracle8i.
Thanks in advance.

[Karthik Guru]

Originally posted by Rashmi Tambe:
Hi ,
I have an entity bean that maps to table having a column with datatype as varchar2(1). for e.g. the column name is empty.
This column is treated as a boolean flag. The enetity bean has methods ,
Boolan isEmtpy()
setEmpty(Boolean b)
The problem is when i set it as setEmpty(Boolean.TRUE). value 1 is inserted in database. How can i insert 'Y'/'N' instead os 1/0 using CMP?
I am using oracle8i.
Thanks in advance.

so isEmpty and setEmpty are abstract methods for setting / getting the "empty" CMP field? Does weblogic do this varchar --> boolean conversion for us? W'd be great if you c'd confirm this for me.
I thought weblogic expects us to use BIT data type / SMALLINT (cant recollect actually!) for the column type in the DB to map it to a boolean field in the bean.
Either way, you still can persist "Y" / "N" (which i dont personally prefer when you have a bit ( 1 / 0) to indicate false / true) by converting the abstract setters and getters to return/accept a String instead.
Then you can ofcourse do a setEmpty("Y").
or you can provide a different public getter/ setter in the bean that actually interpret the String and return / set a boolean value.

public boolean isEmpty(){
 return ("Y".equals(getEmpty())?true:false);
} 
public void setEmptyField(boolean val){
 setEmpty(val == true?"Y":"N");
}

[Pradeep bhatt]

I suggest you to use String in place of boolean. Oracle database does not allow boolean fields in table so why are you using boolean in EJB.

[Lasse Koskela]

The question here, in my opinion, is whether we are more concerned of our application design or our database schema. The mapping between Java types and SQL types is defined in the JDBC documentation.
Personally, I'd say the combination of boolean and BIT is the best choice for both worlds (developer and DBA); boolean is the logically correct choice for your Java class and BIT is the best performing way to represent a 0/1 value in the database. Unless you're *really* worried about the 'human-readability' of your database data, I can't see why should you use CHAR/VARCHAR instead of BIT? I'd say the concept of "1 = yes, 0 = no" is pretty well known...

[Rashmi Tambe]

Hi Karthik,
Thanks for the solution of putting up a wrapper.

Does weblogic do this varchar --> boolean conversion for us?

yes, Weblogic does that. (Boolean to varchar is done. So Boolean.TRUE-> 0 and Boolean.FALSE ->1)Or may the driver, i am using , oracle.jdbc.driver.OracleDriver does that.

I thought weblogic expects us to use BIT data type / SMALLINT (cant recollect actually!) for the column type in the DB to map it to a boolean field in the bean.

I dont have the choice of db design. client team decides that ! so i have to use varchar2(1).
Thanks too Lasse and Pradeep
[ August 13, 2003: Message edited by: Rashmi Tambe ]

[Rashmi Tambe]

Hi Lasse,

Personally, I'd say the combination of boolean and BIT is the best choice for both worlds (developer and DBA);

I did not find BIT datatype in oracle8i; does it correspond to something different in oracle? it seems it is a basic SQL type.
link u provided says that,
The SQL BIT type can be mapped directly to the Java boolean type.
The then how is it not supported in oracle?

[Pradeep bhatt]

There is no BIT in Oracle but it does exist in MS SQL db.

[Pradeep bhatt]

For Oracle SQL datatypes
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45443

[Rashmi Tambe]

If oracle does not have BIT batatype, then how do u map Boolean datatype to oracle? Using varchar2(1) and having set/get String type is a crude way, i feel. Is there any other way?

[Lasse Koskela]

They claim in Oracle8i JDBC Developer's Guide and Reference, Release 3 (8.1.7) that the Java boolean would map to Oracle's NUMBER data type. That's why the "1" in the database.
[ August 13, 2003: Message edited by: Lasse Koskela ]

[Pradeep bhatt]

You can write a "BEFORE INSERT" trigger if you want to have Y/N in place od 1/0.
Hope this helps.