偶然间发现HQL一个非常有意思的用法,拿来分享下。
示例如下:
1. student数据库表(MySQL)
CREATE TABLE `student` (
`ID` int(11) NOT NULL,
`NAMES` varchar(50) NOT NULL,
`AGE` int(3) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表中数据
ID | NAMES | AGE |
1 | 王鹏 | 24 |
2 | 张三 | 12 |
2. student实体类
@Entity
@Table(name = "STUDENT")
public class Student {
private int id;
private String names;
private int age;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID", unique = true, nullable = false)
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Column(name = "NAMES", length = 50, nullable = false)
public String getNames() {
return names;
}
public void setNames(String names) {
this.names = names;
}
@Column(name = "AGE", length = 3, nullable = false)
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
3. HQL语句
public void hql() {
int age = 10;
Session session = sessionFactory.getCurrentSession();
String hql = "from Student where age > ?";
Query query = session.createQuery(hql);
query.setInteger(0, age);
// query.setParameter(0, age);
List<Student> students = query.list();
System.out.println(students.size());
}
当HQL有参数且知道参数类型,我们可以采用类似query.setInteger(0, age)语句来设置,如果不知道参数类型,我们采用query.setParameter(0, age)语句。但是当使用query.setParameter(0, age)时,如果age类型与Student实体类中age类型不一致时,就会报类型转换异常,如:
public void hql() {
String age = "10";
Session session = sessionFactory.getCurrentSession();
String hql = "from Student where age > ?";
Query query = session.createQuery(hql);
// query.setInteger(0, age);
query.setParameter(0, age);
List<Student> students = query.list();
System.out.println(students.size());
}
将报异常java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer。
但是我们知道当我们用原生SQL时,SQL语句其实是不关心参数类型的,例如:
String sql = "select * from student where AGE > " + age;
HQL之所以会关心变量类型,是因为HQL语句是基于对象的查询语句,最终需要将对象查询语句变为SQL语句,那么在解析时必然会考虑变量类型。那有什么解决办法呢?
其实HQL是支持SQL语句的,注意:我说支持不是说把HQL写成sql,而是支持混合写法,如下所示:
public void hql() {
String age = "10";
Session session = sessionFactory.getCurrentSession();
String hql = "from Student where AGE > ?";
Query query = session.createQuery(hql);
query.setParameter(0, age);
List<Student> students = query.list();
System.out.println(students.size());
}
注意String hql = "from Student where AGE > ?"中HQL语句的写法。
from Student是基于HQL的,而AGE却是SQL写法,这样写后就不会再报类型转换错误的异常了,这是因为Hibernagte在解析HQL语句时,最终会将HQL变为SQL,因此如果语句中含有SQL语句部分的话,就不会再解析转换了,
诸如如上情况的,再看个例子:
public void hql() {
int id = 1;
String age = "10";
Session session = sessionFactory.getCurrentSession();
String hql = "from Student where AGE > ? and id = ?";
Query query = session.createQuery(hql);
query.setParameter(0, age);
query.setInteger(1, id);
List<Student> students = query.list();
System.out.println(students.size());
}
可以看到HQL中夹杂着SQL语句。
这算是一个小发现吧,希望会帮助到一些朋友。