Hibernate query using joins and like operator

Question

I have two model classes:

Class 1 looks like this:

@Entity
@Table(name = "cdm_location_charge_class", catalog = "emscribedx")
public class Cdm_location_charge_class {
    private static Logger LOG = Logger.getLogger(Cdm_location_charge_class.class);
    private int indx;
    private String location;
    private String charge_Class;
    private List <Cdm_trans> cdm_Trans;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "indx")
    public int getIndx() {
        return indx;
    }
    public void setIndx(int indx) {
        this.indx = indx;
    }   
    @Column(name = "location")
    public String getLocation() {
        return location;
    }
    public void setLocation(String location) {
        this.location = location;
    }
    @Column(name = "charge_Class")
    public String getCharge_Class() {
        return charge_Class;
    }
    public void setCharge_Class(String charge_Class) {
        this.charge_Class = charge_Class;
    }
    @OneToMany (mappedBy = "cdm_location_charge_class", targetEntity = Cdm_trans.class, cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    public List<Cdm_trans> getCdm_Trans() {
        return cdm_Trans;
    }
    public void setCdm_Trans(List<Cdm_trans> cdm_Trans) {
        this.cdm_Trans = cdm_Trans;
    }

---

And class 2 looks like this:

@Entity
@Table(name = "cdm_Trans", catalog = "emscribedx")
public class Cdm_trans {
private static Logger LOG = Logger.getLogger(Cdm_trans.class);

private int indx;
private String charge_Class;
private String charge_Code;
private String charge_Description;
private String charge_Eligibility;
private String exp_Date;
private BigDecimal rate_Charge;
private String cpt_Code;
private int rev_Code;
private String charge_Type;
private Cdm_location_charge_class cdm_location_charge_class;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "indx")
public int getIndx() {
    return indx;
}

public void setIndx(int indx) {
    this.indx = indx;
}

@Column(name = "charge_Class")
public String getCharge_Class() {
    return charge_Class;
}

public void setCharge_Class(String charge_Class) {
    this.charge_Class = charge_Class;
}

@Column(name = "charge_Code")
public String getCharge_Code() {
    return charge_Code;
}

public void setCharge_Code(String charge_Code) {
    this.charge_Code = charge_Code;
}

@Column(name = "charge_Description")
public String getCharge_Description() {
    return charge_Description;
}

public void setCharge_Description(String charge_Description) {
    this.charge_Description = charge_Description;
}

@Column(name = "charge_Eligibility")
public String getCharge_Eligibility() {
    return charge_Eligibility;
}

public void setCharge_Eligibility(String charge_Eligibility) {
    this.charge_Eligibility = charge_Eligibility;
}

@Column(name = "exp_Date")
public String getExp_Date() {
    return exp_Date;
}

public void setExp_Date(String exp_Date) {
    this.exp_Date = exp_Date;
}

@Column(name = "rate_Charge")
public BigDecimal getRate_Charge() {
    return rate_Charge;
}

public void setRate_Charge(BigDecimal rate_Charge) {
    this.rate_Charge = rate_Charge;
}

@Column(name = "cpt_Code")
public String getCpt_Code() {
    return cpt_Code;
}

public void setCpt_Code(String cpt_Code) {
    this.cpt_Code = cpt_Code;
}

@Column(name = "rev_Code")
public int getRev_Code() {
    return rev_Code;
}

public void setRev_Code(int rev_Code) {
    this.rev_Code = rev_Code;
}

@Column(name = "charge_Type")
public String getCharge_Type() {
    return charge_Type;
}

public void setCharge_Type(String charge_Type) {
    this.charge_Type = charge_Type;
}

@ManyToOne
@JoinColumn(name = "charge_Class", insertable = false, updatable = false)
public Cdm_location_charge_class getCdm_location_charge_class() {
    return cdm_location_charge_class;
}

public void setCdm_location_charge_class(
        Cdm_location_charge_class cdm_location_charge_class) {
    this.cdm_location_charge_class = cdm_location_charge_class;
}
}

---

The two classes correspond to two underlying tables:

Cdm_trans and Cdm_location_charge_class.

I want to create a Hibernate Service that will give me the same result as the following sql query:

select cdm_trans.* from Cdm_trans, Cdm_location_charge_class where 
cdm_location_charge_class.charge_Class = cdm_trans.charge_Class and 
cdm_location_charge_class.location = <Some location passed to the method> and
Cdm_trans.charge_Description like '%<Some search term passed into the method>%;

My preference would be to do this using the Hibernate criteria API. But I will go with HQL if that is the only way. Can someone show me how to do this?

I've tried this HQL query:

Query query = session.createQuery("FROM cdm_location_charge_class as cl  INNER JOIN   cl.cdm_Trans where cl.location = :location and  cdm_Trans.charge_Description like :search");

But I I get an error: cdm_location_charge_class is not mapped

Answer 1

If you want to use Criteria, you just add a restriction.

 List cats = session.createCriteria(Cat.class)
     .createCriteria("kittens")
         .add( Restrictions.like("name", "Iz%") )
     .list();

HQL example:

Query query = session.createQuery("FROM Cdm_location_charge_class cl " + 
   "INNER JOIN cl.cdm_Trans trans " +
   "where cl.location = :location and trans.charge_Description like :search")
   .setParameter("location", locationValue)
   .setParameter("search", "%" + searchValue + "%");

Answer 2

Here is the documentation on Criteria queries in Hibernate: http://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/querycriteria.html

Specifically, you want to look at the Restrictions class (http://docs.jboss.org/hibernate/orm/3.3/api/org/hibernate/criterion/Restrictions.html) and static methods like() and ilike(), where the latter handles case-insensitive comparison. I also tend towards HQL for static queries, but I have seen benefit at times to make static elements configurable, especially for debugging. Therefore, I may use the dynamic query approach with the query stored in a configuration file, so I can alter it easily for testing even though the application runs the same query typically.

Respectfully yours,

Kevin

Answer 3

Can you be more specific about the issue, or are you just asking for a very specific tutorial?

Anyway why is HQL second choice. IMHO the Criteria Helper should only be used when constructing dynamic queries (add conditions based on user input for example). If you have a static query i would suggest HQL. It is also supposed to be faster. Also look into NamedQuery.