How to write JPA query with boolean condition
Asked Answered
A

9

33

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

Avram answered 6/6, 2011 at 11:2 Comment(3)
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?Lockard
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 <property name="jpaVendorAdapter"> <bean class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter"/> </property> query with TRUE condition works.Avram
Have the same here with Derby/Hibernate. But it works for MySQL though. :-)Kyleekylen
O
45

Use the following form:

SELECT e FROM Employee e WHERE e.active = TRUE

See the docs for more info.

Orff answered 7/12, 2015 at 9:17 Comment(0)
H
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);
}
Horan answered 20/3, 2019 at 15:46 Comment(0)
P
1

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;

    .......
}
Plebe answered 2/12, 2015 at 16:7 Comment(0)
U
0

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

Unplaced answered 30/4, 2012 at 14:57 Comment(0)
I
0

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

Ignace answered 1/5, 2012 at 13:34 Comment(0)
E
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");

Exhilarant answered 10/8, 2017 at 6:53 Comment(0)
Q
0

It's working in my scenerio:

findByTitleAndDocumentIdAndIsDeleted(String title, UUID docId, Boolean isDeleted);
Quadrivium answered 9/8, 2019 at 11:59 Comment(0)
P
0

Two ways in the example, first one with the flag already set on method, the other is parametric/parametrized:

@Entity
@Table(name = "tbl_metadata")
@Data 
public class TdMetadata {

    @Column
    private Boolean isCalculated;
}


@Repository
public interface TdMetadataRepository extends PagingAndSortingRepository<TdMetadata, Integer> {

    List<TdMetadata> findByCalculatedTrue();
    List<TdMetadata> findByCalculated(Boolean calculated);
}
Pily answered 5/12, 2023 at 13:28 Comment(0)
I
-4

TRUE is a special keyword. try

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

Invest answered 19/6, 2014 at 11:57 Comment(1)
"u.active IS TRUE" did not work for me either, but "u.active = TRUE" did. Working with EclipseLink 2.5Workhouse

© 2022 - 2024 — McMap. All rights reserved.