aspose file tools*
The moose likes Object Relational Mapping and the fly likes Mapping ENUM('0', '1') or CHAR(0) for Boolean Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Mapping ENUM( Watch "Mapping ENUM( New topic
Author

Mapping ENUM('0', '1') or CHAR(0) for Boolean

Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

I am needing a field in my MySQL database that represents whether or not the user is currently active. If active, they can log into the system otherwise they cannot.

I have been reading on different ways to do this in MySQL since there is no boolean data type. One suggestion is to use an ENUM('0','1') and another solution is to use CHAR(0) where NULL is non-active and "" is active.

Now, put that aside and I want to know if there is a way to then map that in hibernate to an actual boolean so that in Java code I can just test isActive().


GenRocket - Experts at Building Test Data
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Ok, here is what I did. I added the following to my hibernate.cfg.xml:



I then changed active in my database to a tinyint with a value of 1 or 0. So now in my java code I can just use booleans everywhere when querying/updating the data. Including my POJO and mapping file for User.

Now for the problem. I am wondering how this will affect other fields in my tables where I might happen to store a 1 or a 0 but instead of booleans they represent an ID from another table. Will it only substitute if the mapping calls for a boolean and it sees a 1 or a 0?
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
When I ported from entity beans to Hibernate, I mapped several Boolean and boolean properties to NUMBER(1) columns in Oracle. I did it pretty quick and didn't think about adding query substitutions or value mappings of any sort. When I tested it against the old database as well as created new data, it used the existing 0 and 1 values correctly.

In other words, I don't think you need the query.substitutions property at all. Have you tried it without it?

Now one difference: The boolean properties are not involved in any of my HQL queries as of yet. But, given that it mapped to 0/1 be default, I would hope it would map them in HQL as well. You should probably verify this before you depend on it.
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

I don't think it does automatically. I haven't tried it, but why would it? I mean, as an example:





Now, if my database table is

user
------
active (tinyint(1))

Why would hibernate automatically convert the active value to TRUE/FALSE? I mean, I guess if they had that built in already, but according to other dicussion, you need to use the hibernate.query.substitutions. So far I haven't had a problem but I would really like to know if this is going to affect me in a negative way:


I am wondering how this will affect other fields in my tables where I might happen to store a 1 or a 0 but instead of booleans they represent an ID from another table. Will it only substitute if the mapping calls for a boolean and it sees a 1 or a 0?
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
All I can say is that I have several Boolean and boolean properties mapped to NUMBER(1) columns and no query.substitutions properties and I am able to read and write true and false values into the database as 1 and 0 respectively.

I'd bet that this mapping was added automatically since it's a common pattern. I almost recall reading that if you map it to a CHAR(1) field it will use 'Y' and 'N' or 'T' and 'F'. Again, "almost recall" -- a search should turn up a definitive answer.

This is the closest thing I've found so far:
Chapter 5. Basic O/R Mapping

5.2.2 Basic value types

Type mappings from Java primitives or wrapper classes to appropriate (vendor-specific) SQL column types. boolean, yes_no and true_false are all alternative encodings for a Java boolean or java.lang.Boolean.
When in doubt, go to the source. The net.sf.hibernate.type.BooleanType mapper simply uses the PreparedStatement.setBoolean(int, b) method. Maybe Oracle's driver handles this part of it.

Looking at the source, however, I see that it doesn't consider null. Yet I am getting null values correctly. I suppose the mapper must check wasNull() outside of the type class itself. It works flawlessly here.

Again, I haven't included a boolean/Boolean in any of my queries, so you might want to write up a quick test to do that.
[ February 17, 2005: Message edited by: David Harkness ]
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Well, I'll yank out the substitution property and see what happens. You are probably right.
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Please, let us know if that works and if you can still use booleans in queries. I'm really curious as I know my Hibernate knowledge is quite shallow.

Er, I mean, I eat Hibernate with every meal; of course it will work!
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Well what do you know. I commented out the substitution line and it still worked. That's good to know, I guess. I wonder how many other substitutions are built in like that.

Now on to post questions about caching issues.
[ February 17, 2005: Message edited by: Gregg Bolinger ]
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Glad to hear it works for you, too.

For what it's worth, I haven't set up a second level cache yet, and the ported Tomcat + Hibernate application is still running circles around the old WebLogic + entity beans with caching-between-transactions version.

That being said, when I get some of that "free time" stuff I've read about I will be hooking up Tangosol's Coherence cache as we already use it for other areas of the application.
miguel lisboa
Ranch Hand

Joined: Feb 08, 2004
Posts: 1281
I am needing a field in my MySQL database that represents whether or not the user is currently active.

in this class i've a similar case:

here's MySql table:
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | | PRI | NULL | auto_increment |
| nome | varchar(255) | YES | | NULL | |
| pessoaSingular | tinyint(1) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
and ddl generated with hibernate:
create table entidade (
id bigint not null auto_increment,
nome varchar(255),
pessoaSingular bit,
primary key (id)
)


java amateur
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Mapping ENUM('0', '1') or CHAR(0) for Boolean