• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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().
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I'll yank out the substitution property and see what happens. You are probably right.
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1282
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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)
)
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic