Hibernate JPA, MySQL and TinyInt(1) for Boolean instead of bit or char

Question

Here is my JPA2 / Hibernate definition:

Code:
@Column(nullable = false)
private boolean enabled;

In MySql this column is resolved to a bit(1) datatype - which does not work for me. For legacy issues I need to map the boolean to a tinyint not to a bit. But I do not see a possibility to change the default datatype. Is there any?

Donatello · Answer 1 · 2014-09-25T14:19:10.327

66

@Type annotation is an Hibernate annotation.

In full JPA2 (with Hibernate 3.6+), the way to map a Boolean field to a TINYINT(1) SQL type instead of BIT(1), is to use the columnDefinition attribute.

@Column(nullable = false, columnDefinition = "TINYINT(1)")
private boolean enabled;

nb: length attribute seems to have no effect in this case, then we use (1) syntax.

With Hibernate 4.0+, this kind of syntax can cause an runtime error like this :

Wrong column type Found: bit, expected: TINYINT(1)

It seems that in this case, your only way is to use tinyInt1isBit=false in the MySQL datasource connection string like this :

jdbc:mysql://server_host:3306/database?tinyInt1isBit=false

By the way, you can now use the length attribute like this :

@Column(nullable = false, columnDefinition = "TINYINT", length = 1)
private boolean enabled;

edited Sep 25 '14 at 14:19

answered Apr 19 '12 at 09:06

Donatello

2,597
2
22
28

3

Since MySQL aliases `BOOLEAN` to `TINYINT(1)` one can also use `columnDefinition = "BOOLEAN"`, which might be a little more readable. – eggyal Sep 26 '13 at 09:29
you're right, you can also use the BOOLEAN alias with MySQL as long as the alias is realy set to TINYINT, which is true for now. By the way, BOOLEAN and TINYINT are both not standard SQL data types, so you take a risk of failure if you change your data provider dialect (ex: Oracle). – Donatello Jan 27 '14 at 14:16
3

`columnDefinition = "TINYINT", length = 1` is resulting column with :: `tinyint(4)` . what could be the reason? – gtiwari333 Aug 17 '16 at 12:46

score 44 · Accepted Answer · edited Aug 13 '12 at 17:56

44

Try the NumericBooleanType. For some reason this doesn't have a declared short type name so you'd have to use:

@Column(nullable = false)
@Type(type = "org.hibernate.type.NumericBooleanType")
private boolean enabled;

This does map to an INTEGER type but it will probably work fine with a TINYINT.

UPDATE: org.hibernate.type.NumericBooleanType Does not work with TINYINT in some RDBMS. Switch the database column type to INTEGER. Or use a different Java @Type value, or columnDefinition, as appropriate.

In this example, Dude's answer of @Column(nullable = false, columnDefinition = "TINYINT(1)") would work without any database changes.

edited Aug 13 '12 at 17:56

Jon Adams

22,969
17
78
115

answered Aug 01 '10 at 19:22

Mike Q

21,350
19
80
124

Works fine, but after changing bit(1) to int – zaletniy Mar 22 '12 at 12:48
We can also use `@Type(type = "yes_no")` along with `@Column` annotation. – Paramesh Korrakuti Jul 01 '20 at 08:36

score 5 · Answer 3 · answered Nov 26 '17 at 00:10

I'm using JPA with Spring Data/Hibernate 5.0 on a MySQL database.

In my Entity object, I put the following:

@Column(name = "column_name", columnDefinition = "BOOLEAN")
private Boolean variableName;

My dev environment has hibernate auto-ddl set to update, so when I deployed to dev, it created the table with column_name of type tinyint(1).

My code that uses this column considers null as false, so I'm not worried about nulls, if you are, you could make it a primitive boolean or add ", nullable = false" to the Column annotation.

This solution is fully JPA (doesn't use hibernate Type annotation) and requires no change to the connection string.

score 2 · Answer 4 · edited Aug 19 '16 at 19:15

2

I had this error:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in ServletContext resource [/WEB-INF/config/context-config.xml]: Invocation of init method failed; nested exception is org.hibernate.MappingException: Could not determine type for: org.hibernate.type.NumericBooleanType, at table: bookingItem, for columns: [org.hibernate.mapping.Column(enabled)]

And this worked for me:

@Column(nullable = false, columnDefinition = "TINYINT(1)")
private boolean enabled;

edited Aug 19 '16 at 19:15

dbmitch

4,400
4
21
37

answered Aug 06 '15 at 11:29

marioarranzr

46
2

That ended up working for me too. Not entirely sure why since I'm using the data type byte. I'm assuming because MySQL uses tinyint and not byte? Ref: @Column(name = "totalBeds", nullable = false, columnDefinition = "TINYINT(1)") private byte totalBeds; – coder3 Sep 03 '20 at 16:16

score 2 · Answer 5 · answered Apr 16 '19 at 14:43

2

When using Microsoft sql and some versions of mysql use the following:

@Column(name = "eanbled", columnDefinition = "bit default 0", nullable = false)
private boolean enabled;

For me, tinybit, boolean, and other such definitions failed.

answered Apr 16 '19 at 14:43

Nox

896
8
23

score 0 · Answer 6 · answered May 17 '20 at 09:20

Old question but probably will save someone's time.

I am using Spring Data JPA 2.2.5. I had a similar issue when I work with MySQL and MariadDB parallelly with the same code base. It worked on one and didn't on another.

The issue was, I was creating the field as unsigned.

I moved the below SQL part from

`is_fixed` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',

to the below

`is_fixed` TINYINT(1) NOT NULL DEFAULT '0',

this fixed the issue and was working in both Mysql and MariaDB without any issue...

Hibernate JPA, MySQL and TinyInt(1) for Boolean instead of bit or char

6 Answers6

Linked