Hibernate Criteria Query - MySQL Command Syntax Dictionary

Indonesian Version

For some viewer already know about Java Platform may already familiar with Hibernate, and so for viewer already familiar with Hibernate probably already know with “Criteria Query” term. In this time I’ll try to discuss equality function between Criteria Query and MySQL, with purpose to help other in order to learn about Hibernate and how the function similarity between Criteria Query and basic standard command in MySQL query syntax.

And now we can start to discuss those thing, first we take a table for example in MySQL and going to convert into a class model with java platform and follow by term of Hibernate. In this case we going to use Hibernate Annotation so we not need xml file for field class mapping to field table in a database. For simple case we try to create a table and called it with name “karyawan” and so we create java class model with a same name.

Nama Field		Type Field		Primary Key
id		Integer / Numeric		Yes
nama		Varchar(30)
tgl_masuk		Date
upah		Decimal(20,10)

With this command we can create this table on MySQL:

CREATE TABLE `karyawan` (                                  
  `id` int(11) NOT NULL auto_increment,                    
  `nama` varchar(30) default NULL,                         
  `tgl_masuk` date NOT NULL default '1970-01-01',          
  `upah` decimal(20,10) default NULL,                      
  PRIMARY KEY (`id`)                                      
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC  

Then create model class “Karyawan” made by these syntax show below:

@Entity
@Table(name="karyawan")
public class Karyawan implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    
    @Column(name="nama")
    private String nama;
    
    @Temporal(TemporalType.DATE)
    @Column(name="tgl_masuk",nullable=false,columnDefinition="date")
    private Date tglMasuk;
    
    @Column(name="upah")
    @Type(type="big_decimal")
    private BigDecimal upah;
    
    public Karyawan() {}

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getNama() {
        return nama;
    }

    public void setNama(String nama) {
        this.nama = nama;
    }

    public Date getTglMasuk() {
        return tglMasuk;
    }

    public void setTglMasuk(Date tglMasuk) {
        this.tglMasuk = tglMasuk;
    }

    public BigDecimal getUpah() {
        return upah;
    }

    public void setUpah(BigDecimal upah) {
        this.upah = upah;
    }
}  

By assuming that you already know about Hibernate by this reference article “Proses CRUD Dengan Hibernate Annotations Menggunakan Netbean 6.0”, then we can strait to discuss how to use Criteria Query, we can use these class “Criteria” from “org.hibernate.Criteria” or “DetachedCriteria” from “org.hibernate.criterion.DetachedCriteria” and followed by these class “Expression” from “org.hibernate.criterion.Expression” or “Restriction” from “org.hibernate.criterion.Restriction”, and than with assumption we going to call all data in the table “Karyawan” then we can use this SQL command like shown below:

select * from Karyawan;

whereas used Criteria Query even though “Criteria” or “DetachedCriteria” will produce command line syntax like shown below:

getSession().createCriteria(Karyawan.class).list();

The table shown below are equality command between Criteria Query and MySQL command:

MySQL	Hibernate Criteria Query
select * from Karyawan where id = 1;	getSession().createCriteria(Karyawan.class) .add(Expression.eq(“id”, Long.valueOf(1))) .list();
select * from Karyawan where id != 1;	getSession().createCriteria(Karyawan.class) .add(Expression.ne(“id”, Long.valueOf(1))) .list();
select * from Karyawan where id <> 1;	getSession().createCriteria(Karyawan.class) .add(Expression.ne(“id”, Long.valueOf(1))) .list();
select * from Karyawan where nama = “sesuatu”;	getSession().createCriteria(Karyawan.class) .add(Expression.eq(“nama”, “sesuatu”)) .list();
select * from Karyawan where tgl_masuk = “1900-01-01”;	getSession().createCriteria(Karyawan.class) .add(Expression.eq(“tglMasuk”, new java .util.Date(java.sql.Date .valueOf("1900-01-01")))) .list();
select * from Karyawan where upah = 10000;	getSession().createCriteria(Karyawan.class) .add(Expression.eq(“upah”, BigDecimal .valueOf(10000))) .list();
select * from Karyawan where id > 10;	getSession().createCriteria(Karyawan.class) .add(Expression.gt(“id”, Long.valueOf(10))) .list();
select * from Karyawan where id < 10 and id > 30;	getSession().createCriteria(Karyawan.class) .add(Expression.lt(“id”, Long.valueOf(10))) .add(Expression.gt(“id”, Long.valueOf(30))) .list();
select * from Karyawan where id < 10 or id > 30;	getSession().createCriteria(Karyawan.class) .add(Expression.or(Expression.lt(“id”, Long.valueOf(10)), Expression.gt(“id”, Long.valueOf(30)))) .list();
select * from Karyawan where id <= 10;	getSession().createCriteria(Karyawan.class) .add(Expression.le(“id”, Long.valueOf(10))) .list();
select * from Karyawan where id >= 10;	getSession().createCriteria(Karyawan.class) .add(Expression.ge(“id”, Long.valueOf(10))) .list();
select * from Karyawan where nama like ‘sesu%’;	getSession().createCriteria(Karyawan.class) .add(Expression.like(“nama”, “sesu”, MatchMode.END)) .list();
select * from Karyawan where nama like ‘%sua%’;	getSession().createCriteria(Karyawan.class) .add(Expression.like(“nama”, “sua”, MatchMode.ANYWHERE)) .list();
select * from Karyawan where nama like ‘%atu’;	getSession().createCriteria(Karyawan.class) .add(Expression.like(“nama”, “atu”, MatchMode.START)) .list();
select * from Karyawan where nama like ‘sesuatu’;	getSession().createCriteria(Karyawan.class) .add(Expression.like(“nama”, “sesuatu”, MatchMode.EXACT)) .list();
select * from Karyawan where id between 0 and 100;	getSession().createCriteria(Karyawan.class) .add(Expression.between(“id”, Long.valueOf(0), Long.valueOf(100))) .list();
select * from Karyawan where nama = ‘’;	getSession().createCriteria(Karyawan.class) .add(Expression.isEmpty(“nama”)) .list();
select * from Karyawan where nama <> ‘’;	getSession().createCriteria(Karyawan.class) .add(Expression.isNotEmpty(“nama”)) .list();
select * from Karyawan where nama is null;	getSession().createCriteria(Karyawan.class) .add(Expression.isNull(“nama”)) .list();
select * from Karyawan where nama is not null;	getSession().createCriteria(Karyawan.class) .add(Expression.isNotNull(“nama”)) .list();
select * from Karyawan where id in (1,3,5,7);	List<Long> idlist = new ArrayList<Long>(); idlist.add(1); idlist.add(3); idlist.add(5); idlist.add(7); getSession().createCriteria(Karyawan.class) .add(Expression.in(“id”, idlist)) .list();
select * from Karyawan where id not in (1,3,5,7);	List<Long> idlist = new ArrayList<Long>(); idlist.add(1); idlist.add(3); idlist.add(5); idlist.add(7); getSession().createCriteria(Karyawan.class) .add(Expression.not(Expression.in(“id”, idlist))) .list();
select * from Karyawan where id > 1 order by id ASC;	getSession().createCriteria(Karyawan.class) .addOrder(Order.asc(“id”)) .add(Expression.ge(“id”, Long.valueOf(1))) .list();
select * from Karyawan where id > 1 order by id DESC;	getSession().createCriteria(Karyawan.class) .addOrder(Order.desc(“id”)) .add(Expression.ge(“id”, Long.valueOf(1))) .list();

For additional note, these “Expression.not” or “Restriction.not” command cannot used for MySQL query command like this “Select * from Karyawan where id <> 1” or “Select * from Karyawan where id != 1” though in syntax show where condition “not equal” cause by this command in Criteria Query already represent by “Expression.ne” or “Restriction.ne”. As reference for correct syntax, if Hibernate command we created as part of web-app using Spring Framework, then all example show above must written in Class DAO Implementation, but if isn’t than it necessary to write in Object Class. Like sample below:

@SuppressWarnings("unchecked")
public List<Karyawan> loadContohSatu() {
    List<Long> idlist = new ArrayList<Long>();
    idlist.add(1);
    idlist.add(3);
    idlist.add(5);
    idlist.add(7);
      
    return getSession().createCriteria(Karyawan.class)
    .add(Expression.not(Expression.in(“id”, idlist))).list();
}

@SuppressWarnings("unchecked")
public List<Karyawan> loadContohDua() {
    return getSession getSession().createCriteria(Karyawan.class)
    .addOrder(Order.asc(“id”))
    .add(Expression.ge(“id”, Long.valueOf(1))).list();
}

Perhaps for this moment this knowledge that I can share to all reader, if in this article contain some mistake, please don’t mind to correct me if I done some wrong or give me some advise and comment.

Read More...