How to write JPA query with boolean condition

Question

In my project i am using JPA 2.0 with eclipselink inplementation, an I have following problem:

I have defined entity with boolean column:

@Entity
public User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="USR_ID")
    private Short id;

    @Column(name="USR_NAME")
    private String name;

    @Column(name="USR_ACTIVE")
    private boolean active;

    .......

}

I want to create query which will return all active users, something like this:

select u from User u where u.active = TRUE;

But if I use that query I got exception that boolean can't be cast to Short (column in database is stored as smallint). Is there any correct way how to write this query?

Thanks

What JPA provider are you using (i.e. Hibernate, EclipseLink, OpenJPA, etc)? What database vendor are you using (MySQL, SQL Server, Oracle)? What JDBC driver are you using? — Edwin Dalorzo, Jun 08 '11 at 19:56
I am using Eclipselink as JPA provider, Derby as database and Derby client jdbc driver, but also Spring for creation of EntityManager, transaction managment... And there was also a problem, i have forget to specified JPA vendor. After adding query with TRUE condition works. — Rado Skrib, Jun 09 '11 at 10:29
Have the same here with Derby/Hibernate. But it works for MySQL though. :-) — Jan Goyvaerts, Apr 03 '12 at 13:02

score 44 · Answer 1 · edited Jun 01 '21 at 07:51

44

Use the following form:

SELECT e FROM Employee e WHERE e.active = TRUE

See the docs for more info.

edited Jun 01 '21 at 07:51

Jens Piegsa

7,399
5
58
106

answered Dec 07 '15 at 09:17

YUIOP QWERT

493
4
6

3

the answer is "SELECT e FROM Employee e WHERE e.active = TRUE",you can ignore the link. So It is not a " Link-only answers"! Furthermore ，link can be invalid so does any answer! So the link is a reference for anybody who is interesting to read more. Last but not least, the link is a wiki link ,so it's no less than an answer but a method to get what you want to know!!! – YUIOP QWERT Dec 08 '15 at 03:02
Wow,if the answer is wrong ,you just can add your comment. And then it will be useful to recognize that the link had been out-of-date. Don't know what the downvotes means really. – YUIOP QWERT Dec 09 '15 at 05:16

score 4 · Answer 2 · answered Mar 20 '19 at 15:46

4

For MySql works this:

public interface UserRepository extends JpaRepository<User, Long> {    
    @Query("SELECT e FROM  Employee e WHERE e.active=?1")
    User findByStatus(Boolean active);
}

answered Mar 20 '19 at 15:46

Kirill_code

129
1
9

score 1 · Answer 3 · answered Dec 02 '15 at 16:07

I had this problem too (PostgreSQL + Eclipselink) and got it working with the @TypeConverter and @Convert annotations.

@Entity
@TypeConverter(name = "booleanToInteger", dataType = Integer.class)
public User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="USR_ID")
    private Short id;

    @Column(name="USR_NAME")
    private String name;

    @Column(name="USR_ACTIVE")
    @Convert("booleanToInteger")
    private boolean active;

    .......
}

score 0 · Answer 4 · answered Apr 30 '12 at 14:57

0

I am using EclipseLink as JPA provider and a MySQL database. With this configuration, using 1 for true and 0 for false works.

answered Apr 30 '12 at 14:57

punkyduck

669
2
9
17

score 0 · Answer 5 · answered May 01 '12 at 13:34

0

You can use @TypeConverter(dataType=Integer.class)

answered May 01 '12 at 13:34

James

17,965
11
91
146

score 0 · Answer 6 · answered Aug 10 '17 at 06:53

0

You can write query like :

@Query("SELECT u from User u order by u.active");

It will display inactive users first. If you want to see active user first use like :

@Query("SELECT u from User u order by u.active desc");

answered Aug 10 '17 at 06:53

Rahul Chauhan

1,487
1
11
13

score 0 · Answer 7 · edited Jul 22 '23 at 20:16

0

It's working in my scenerio:

findByTitleAndDocumentIdAndIsDeleted(String title, UUID docId, Boolean isDeleted);

edited Jul 22 '23 at 20:16

Ahmed Ashour

5,179
10
35
56

answered Aug 09 '19 at 11:59

Purushottam Sadh

1,087
10
5

score -4 · Answer 8 · answered Jun 19 '14 at 11:57

-4

TRUE is a special keyword. try

select u from User u where u.active IS TRUE;

answered Jun 19 '14 at 11:57

minibi

50
7

3

"u.active IS TRUE" did not work for me either, but "u.active = TRUE" did. Working with EclipseLink 2.5 – ctron Mar 05 '15 at 12:02

How to write JPA query with boolean condition

8 Answers8

Linked