CODEWITHSUNDEEP
javaoracle-databasehibernatejpa
softwareplay
softwareplay

Reputation: 1409

Boolean column type (nullable true) in hibernate

the problem is the following, i have an entity with a column.. say:

@Getter     
@Setter
@Column(name = "COLUMN_NAME", nullable = true)
private Boolean flag;

As you can see the type is Boolean.. On the database it is a numeric column (NUMBER(1,0)). I'm trying to to perform a select and an insert in the table, through hibernate jpa so the select is a repository find and the insert is a saveAndFlush. This is the select:

public static final String query = "SELECT mgb FROM Entity mgb WHERE "
        + "( "
        +   "("
        +       "mgb.flag= :flag"
        +   ") OR "
        +   "(:flag IS NULL)"
        + ") AND "
        + "mgb.otherColumn IS NULL";
@Query(query)
public List<Entity>find(@Param("flag") Boolean flag);

This is the insert:

    Entity mgbi = new Entity();
    mgbi.setFlag(null);
    EntityRepository.saveAndFlush(mgbi);

Both these operation throw me the same exception;

2014-11-26 17:50:25,279 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - <SQL Error: 17004, SQLState: 99999>
2014-11-26 17:50:25,280 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - <Invalid column type: 16>

I've done some attempt and i found out that if i change this line: mgbi.setFlag(null); to mgbi.setFlag(true); the error is not thrown any more and the insert is rightly done.

I've found out on the web that the boolean type has this problem in java, but i've used the Boolean which should not act this weird.. AND, last but not least, i've an other entity, with almost the same column (even the name is the same), on the same database, with the same data types involved both from the java and from the db (oracle) point of view, it doesn't give me ANY error..

So i'm quite confused..

If someone could point me in the right direction i would be glad..

Upvotes: 0

Views: 3540

Answers (2)

C&#225;ssio
C&#225;ssio

Reputation: 329

I had the same exception messages this week when performing an update that always worked. What changed is that I upgraded my JBoss from 6.0.1.Final to EAP-6.3, which also comes with a new version of hibernate.

To solve the problem, I simply changed the value of the property "hibernate.dialect" from "org.hibernate.dialect.OracleDialect" to "org.hibernate.dialect.Oracle9iDialect" on persistence.xml.

Upvotes: 4

Multisync
Multisync

Reputation: 8787

I'd recommend to use SQL coalesce function which is supported by Hibernate:

public static final String query = "SELECT mgb FROM Entity mgb WHERE "
        + "mgb.flag = coalesce(:flag, mgb.flag) "
        + "AND mgb.otherColumn IS NULL";

It returns first not NULL value among the arguments.

Upvotes: 0

Related Questions