CODEWITHSUNDEEP
javasqlhibernateormcriteria
Swaranga Sarma
Swaranga Sarma

Reputation: 13403

Logical OR between a Criterion and Criteria

I have the following model classes:

@Entity
@Table(name = "title")
public final class Title extends ModelData<Title>
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer titleID;

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

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

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "title")
    private Set<Book> books;
}

@Entity
@Table(name = "book")
public final class Book extends ModelData<Book>
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "bookID")
    private int bookID;

    @ManyToOne(cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
    @JoinColumn(name = "titleID")
    private Title title;

    @Column(name = "edition")
    private Integer edition;

    @Column(name = "isbn")
    private String ISBN;
}

I want to write a Criteria query that is equivalent to the following SQL; 

Select 
        t.title, b.edition 
    from 
        books b, title t
    where 
        b.titleID = t.titleID
    and 
        (b.edition=4 OR t.title LIKE '%Java%);

I tried the following:

Criteria c = session.createCriteria(Book.class);

Criteria titleCriteria = c.createCriteria("title");
titleCriteria.add(Restrictions.like("title", "%Java%");

Criterion edition = Restrictions.eq("edition", 4);

LogicalExpression orExp = Restrictions.or(edition, titleCriteria); //cannot do this

How do I achieve the above?

Thanks.

Upvotes: 1

Views: 4115

Answers (4)

Juan Rada
Juan Rada

Reputation: 3766

Another idea

Convert yout criteria to formula field and evaluate as normal criterios

Add formula field to mapping file, or annotations to your classes

<property name="titlename" type="string"
formula="(Select title.title from title 
    where title.titleID= titleID)"/>

then 

Criteria c = session.createCriteria(Book.class)
Criteria titleCriteria = c.createCriteria("title");
titleCriteria.add(Restrictions.like("titlename", "%Java%");
Criterion edition = Restrictions.eq("edition", 4);
LogicalExpression orExp = Restrictions.or(edition, titleCriteria); //CAN< do this!!!

Upvotes: 1

Sebastien Lorber
Sebastien Lorber

Reputation: 92120

public class MyDTO {
  private String dtoTitle;
  private String dtoEdition;
  // + setters/getters
}


Criteria c = session.createCriteria(Book.class,"b");
c.createAlias("title", "t");
c.add(
    Restrictions.disjunction()
    .add( Restrictions.like("t.title", "%Java%") )
    .add( Restrictions.eq("b.edition", 4) )
);
c.setProjection( 
    Projections.projectionList()
    .add( Projections.property("t.title"), "dtoTitle" )
    .add( Projections.property("b.edition"), "dtoEdition" )
);
c.setResultTransformer(Transformers.aliasToBean(MyDTO.class));
List<MyDTO> result = (List<MyDTO>)c.list();

Something like this should work fine.

On dao using many criterias you should consider using static imports.

Upvotes: 1

dcernahoschi
dcernahoschi

Reputation: 15230

For more than 2 OR conditions it's more readable to use:

c.add(
   Restrictions.disjunction()
            .add(Restrictions.eq(...))
            .add(Restrictions.eq(...))
            .add(Restrictions.eq(...))
   )

Upvotes: 0

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79808

I think you want this. I haven't tested it, so there may be some minor errors, but the basic idea is correct.

Criteria c = session.createCriteria(Book.class); 
Criterion titleCriterion = Restrictions.like("title.title", "%Java%"); 
Criterion edition = Restrictions.eq("edition", 4); 
c.add( Restrictions.or( edition, titleCriterion ));

Upvotes: 0

Related Questions