How to write JPA query with boolean condition

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

Use the following form:

SELECT e FROM Employee e WHERE e.active = TRUE

See the docs for more info.

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);
}

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;

    .......
}

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

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

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");

It's working in my scenerio: findByTitleAndDocumentIdAndIsDeleted(String title, UUID docId, Boolean isDeleted);

TRUE is a special keyword. try

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