六、hql语句

一、选课系统

1.建表

 

 

2.填充数据

INSERT INTO `student` VALUES ('20040001', '林青霞', 'F', '计算机系', '22', '上海');

INSERT INTO `student` VALUES ('20040002', '刘德华', 'M', '外语系', '23', '南京');

INSERT INTO `student` VALUES ('20050006', '周润发', 'M', '数学家', '20', '湖北');

INSERT INTO `student` VALUES ('200050003', '成龙', 'M', '化学系', '21', '山东');

INSERT INTO `student` VALUES ('200050005', '周华健', 'M', '生物系', '24', '山东');

INSERT INTO `student` VALUES ('200500004', '林可欣', 'F', '计算机系', '22', '北京');

 

INSERT INTO `course` VALUES ('11', 'java编程', '6');

INSERT INTO `course` VALUES ('21', 'c++课程', '4');

INSERT INTO `course` VALUES ('31', 'oracle', '3');

INSERT INTO `course` VALUES ('41', 'javaEE', '100');

INSERT INTO `course` VALUES ('51', 'linux', '1');

 

INSERT INTO `studcourse` VALUES ('1', '20040001', '11', '90');

INSERT INTO `studcourse` VALUES ('2', '20040001', '21', '19');

INSERT INTO `studcourse` VALUES ('3', '20050003', '21', '45');

INSERT INTO `studcourse` VALUES ('4', '20050004', '41', '99');

INSERT INTO `studcourse` VALUES ('5', '20050006', '11', '39');

 

3.逆向工程(优先生成主表，即student表和course表，再生成studcourse表)

 

// Course表

 

private Integer cid;

private String cname;

private Integer ccredit;

private Set studcourses = new HashSet(0);

 

// Studcourse表

 

private Integer stuCourseId;

private Student student;

private Course course;

private Integer grade;

 

//Student表

 

private Long sid;

private String sname;

private String ssex;

private String sdept;

private Integer sage;

private String saddress;

private Set studcourses = new HashSet(0);

 

这里我们可以看出，由于表之间的主外键关系，一个学生可以选多门课，一门课可以被多个人选，于是就生成了如上的domain对象。而且，在hbm.xml文件中还有<set>标签和<one-to-many>等标签。

 

二、hql查询

//查询学生的所有属性
 

Session session=HibernateUtil.getCurrentSession();

Transaction ts=null;

try {

session=HibernateUtil.getCurrentSession();

ts=session.beginTransaction();

//查询所有属性可以不用select * from

List<Student> list = session.createQuery("from Student").list();

for(Student s:list){

System.out.println(s.getSname()+" "+s.getSaddress());

}

ts.commit();

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

if(ts!=null) ts.rollback();

}finally{

if(session!=null&&session.isOpen()){

session.close();

}

}

//当然list还有另一种遍历方式

Iterator<Student> iterator = list.iterator();

while(iterator.hasNext()){

Student s=iterator.next();

System.out.println(s.getSname()+" "+s.getSage());

}

//查询部分属性

//在jdbc里，建议需要什么字段就查询什么字段，但在hibernate里，建议把整个对象都查出来

//但是作为了解，我们还是需要知道怎么查询部分字段

List list = session.createQuery("select sname,sage from Student").list();

for(int i=0;i<list.size();i++){

Object[] obj = (Object[]) list.get(i);

System.out.println(obj[0]+" "+obj[1]);

}

 

List<Object[]> list = session.createQuery("select sname,sage from Student").list();

for(Object[] obj:list){

System.out.println(obj[0]+" "+obj[1]);

}

//注意，查询两个字段以上就是对象数组，查询一个字段就是对象

List list = session.createQuery("select sage from Student").list();

for(int i=0;i<list.size();i++){

System.out.println(list.get(i));

}

//查询每个人选择的课程数

List<Student> list = session.createQuery("from Student").list();

for(Student s:list){

System.out.println(s.getSname()+"选择了"+s.getStudcourses().size()+"门课");

}

//查询每个人选择的课程

List<Student> list = session.createQuery("from Student").list();

for(Student s:list){

if(s.getStudcourses().size()==0) System.out.println(s.getSname()+"没有选课");

else{

Set<Studcourse> set = s.getStudcourses();

for(Studcourse sc:set){

System.out.println(s.getSname()+"选择了"+sc.getCourse().getCname());

}

}

}

//uniqueResult(当你确定查询结果至多只有一组时使用,如果有多条会报错)

 

Student s= (Student) session.createQuery("from Student where sid='20050003'").uniqueResult();

 

System.out.println(s.getSname());

//distinct关键字取消重复值

List list = session.createQuery("select distinct ssex from Student").list();

for(int i=0;i<list.size();i++){

System.out.println(list.get(i));

}

//between...and

List list = session.createQuery("select sname,sage from Student where sage between 20 and 22").list();

for(int i=0;i<list.size();i++){

Object[] obj=(Object[]) list.get(i);

System.out.println(obj[0]+" "+obj[1]);

}

//in和not in

List list = session.createQuery("select sname,sdept from Student where sdept in('计算机系','外语系')").list();

for(int i=0;i<list.size();i++){

Object[] obj=(Object[]) list.get(i);

System.out.println(obj[0]+" "+obj[1]);

}

//group by显示各个系的平均年龄

List list = session.createQuery("select avg(sage),sdept from Student group by sdept").list();

for(int i=0;i<list.size();i++){

Object[] obj=(Object[]) list.get(i);

System.out.println(obj[0]+" "+obj[1]);

}

 

//having对分组查询后的结果再进行筛选

//人数大于1的系名称

List<Object[]> list = session.createQuery("select count(*),sdept from Student group by sdept having count(*)>1").list();

for(Object[] obj:list){

System.out.println(obj[0]+" "+obj[1]);

}

//聚集函数--count(),avg(),max(),min(),sum()

List<Object[]> list = session.createQuery("select max(grade),min(grade) from Studcourse where cid=11").list();

for(Object[] obj:list){

System.out.println("最高分:"+obj[0]+" 最低分："+obj[1]);

}

 

//查询所有分数低于60分的学生名、课程名、分数

List<Object[]> list = session.createQuery("select student.sname,course.cname,grade from Studcourse where grade<60").list();

for(Object[] obj:list){

System.out.println(obj[0]+" "+obj[1]+" "+obj[2]);

}

--在Studcourse表里有属性student和属性course，所以能够这样访问

//查询每个系不及格的人数

List<Object[]> list = session.createQuery("select student.sdept,count(*) from Studcourse where grade<60 group by student.sdept").list();

for(Object[] obj:list){

System.out.println(obj[0]+" "+obj[1]);

}

//按年龄从小到大排序，取出前三个学生名字、年龄

List<Object[]> list = session.createQuery("select sname,sage from Student order by sage")

.setFirstResult(0) //表示从第一个开始取

.setMaxResults(3) //最多取出3个

.list();

 

for(Object[] obj:list){

System.out.println(obj[0]+" "+obj[1]);

}

//分页显示所有数据

public static void showResultByPage(int pageSize){

 

int pageNow=1;//当前是第几页

int pageCount=1;//总共需要分几页

int rowCount=1;//总共有多少条数据

 

Session session=HibernateUtil.getCurrentSession();

Transaction ts=null;

try {

session=HibernateUtil.getCurrentSession();

ts=session.beginTransaction();

rowCount = Integer.parseInt(session.createQuery("select count(*) from Student").uniqueResult().toString());

pageCount=(rowCount-1)/pageSize+1;

//循环显示每页

for(int i=1;i<=pageCount;i++){

System.out.println("************第"+i+"页************");

List<Student> list = session.createQuery("from Student")

.setFirstResult((i-1)*pageSize)

.setMaxResults(pageSize)

.list();

 

for(Student s:list){

System.out.println(s.getSname()+" "+s.getSdept());

}

}

 

 

ts.commit();

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

if(ts!=null) ts.rollback();

}finally{

if(session!=null&&session.isOpen()){

session.close();

}

}

}

//参数绑定

//使用setParameter()的方法来确定变量的值，使sql语句可读性好，程序性能提高，防止sql注入

①替代问号

Query query = session.createQuery("select sname,sage from Student where sid=?");

query.setString(0, "20040001");

 

List<Object[]> list = query.list();

for(Object[] obj:list){

System.out.println(obj[0]+" "+obj[1]);

}

②替代冒号加字符串

Query query = session.createQuery("select sname,sage from Student where sid=:ss");

query.setString("ss", "20040001");

 

List<Object[]> list = query.list();

for(Object[] obj:list){

System.out.println(obj[0]+" "+obj[1]);

}

//根据参数绑定的方法提供同一的查询方法

public static List executeQuery(String hql,String[] parameters){

Session session=null;

Transaction ts=null;

List list=null;

try {

session=HibernateUtil.getCurrentSession();

ts=session.beginTransaction();

Query query = session.createQuery(hql);

if(parameters!=null&¶meters.length>0){

for(int i=0;i<parameters.length;i++){

query.setString(i, parameters[i]);

}

}

list = query.list();

ts.commit();

} catch (Exception e) {

// TODO: handle exception

if(ts!=null) ts.rollback();

e.printStackTrace();

}finally{

if(session!=null&&session.isOpen()){

session.close();

}

}

return list;

}

//这里由于sessionFactory、session等都已经在HibernateUtil.executeQuery处执行了

public static void main(String[] args) {

// TODO Auto-generated method stub

String hql="select sname,sage from Student where sid=?";

String[] parameters={"20040001"};

List<Object[]> list = HibernateUtil.executeQuery(hql,parameters);

for(Object[] obj:list){

System.out.println(obj[0]+" "+obj[1]);

}

}

//统一的分页查询方法

public static List excuteQueryByPage(String hql,String[] parameters,int pageSize,int pageNow){

Session session=null;

Transaction ts=null;

List list=null;

try {

session=HibernateUtil.getCurrentSession();

ts=session.beginTransaction();

Query query = session.createQuery(hql);

if(parameters!=null&¶meters.length>0){

for(int i=0;i<parameters.length;i++){

query.setString(i, parameters[i]);

}

}

query.setFirstResult((pageNow-1)*pageSize);

query.setMaxResults(pageSize);

list = query.list();

ts.commit();

} catch (Exception e) {

// TODO: handle exception

if(ts!=null) ts.rollback();

e.printStackTrace();

}finally{

if(session!=null&&session.isOpen()){

session.close();

}

}

return list;

}

//统一的添加方法

public static void save(Object obj){

Session session=null;

Transaction ts=null;

try {

session=HibernateUtil.getCurrentSession();

ts=session.beginTransaction();

session.save(obj);

ts.commit();

} catch (Exception e) {

// TODO: handle exception

if(ts!=null) ts.rollback();

e.printStackTrace();

}finally{

if(session!=null&&session.isOpen()){

session.close();

}

}

}

//统一的修改和删除

public static void executeUpdate(String hql,String[] parameters){

Session session=null;

Transaction ts=null;

try {

session=HibernateUtil.getCurrentSession();

ts=session.beginTransaction();

Query query = session.createQuery(hql);

if(parameters!=null&¶meters.length>0){

for(int i=0;i<parameters.length;i++){

query.setString(i, parameters[i]);

}

}

query.executeUpdate();

ts.commit();

} catch (Exception e) {

// TODO: handle exception

if(ts!=null) ts.rollback();

e.printStackTrace();

}finally{

if(session!=null&&session.isOpen()){

session.close();

}

}

}

//复杂查询

//显示所有选择了21号课程的学生信息

String hql="select student.sname,student.sdept from Studcourse where course.cid=21";

List<Object[]> list = HibernateUtil.executeQuery(hql,null);

for(Object[] obj:list){

System.out.println(obj[0]+" "+obj[1]);

}

//hql不能直接查询对象，查询的必须是能够得到的字段

//例如下面hql，执行都将会报错

String hql="select student from Studcourse where course.cid=21";

List<Student> list = HibernateUtil.executeQuery(hql,null);

for(Student s:list){

System.out.println(s.getSname()+" "+s.getSdept());

}

 

String hql="select student.* from Studcourse where course.cid=21";

List<Object[]> list = HibernateUtil.executeQuery(hql,null);

for(Object[] s:list){

System.out.println(s[0]+" "+s[1]);

}

//子查询

String hql="from Studcourse where course.cid=21";

List<Studcourse> list = HibernateUtil.executeQuery(hql,null);

for(Studcourse sc:list){

System.out.println(sc.getStuCourseId()+" "+sc.getGrade());

}

//session关闭后，依然能够得到list中直接属性的值

 

//如果想得到学生名等，将会报错could not initialize proxy - no Session

for(Studcourse sc:list){

System.out.println(sc.getStudent().getSname());

}

//因为sc.getStudent()是需要向SQL发送sql语句的，而此时session已经关闭了，懒加载机制就不会再打开session去查询。

 

//禁用懒加载即可再查询到

三、在映射文件中得到hql语句

在Student.hbm.xml中配置如下hql语句:

<hibernate-mapping>

     <query name="query1">

        from Student where sage>22

     </query>

</hibernate-mapping>

执行时候执行如下代码：

List<Student> list = session.getNamedQuery("query1").list();

for(Student s:list){

System.out.println(s.getSname()+" "+s.getSage());

}