偶然间发现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语句。
这算是一个小发现吧,希望会帮助到一些朋友。