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().
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?
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.
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?
Joined: Aug 07, 2003
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.
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 ]
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 ]
Joined: Aug 07, 2003
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.