三套查询之HQL查询
hql语句(面向)：类 对象 属性
1 package com.rong.entity.hql;
2
3 public class User {
4
5 public User(int id, String name) {
6 super();
7 this.id = id;
8 this.name = name;
9 }
10 public User() {
11 super();
12 }
13 private int id;
14 private String name;
15 public int getId() {
16 return id;
17 }
18 public void setId(int id) {
19 this.id = id;
20 }
21 public String getName() {
22 return name;
23 }
24 public void setName(String name) {
25 this.name = name;
26 }
27 }
1 package com.rong.entity.hql;
2
3 import javax.persistence.Entity;
4 import javax.persistence.GeneratedValue;
5 import javax.persistence.GenerationType;
6 import javax.persistence.Id;
7
8 @Entity
9 public class Student {
10 @Id
11 @GeneratedValue(strategy=GenerationType.AUTO)
12 private int id;
13 private String name;
14 private int age;
15 public int getId() {
16 return id;
17 }
18 public void setId(int id) {
19 this.id = id;
20 }
21 public String getName() {
22 return name;
23 }
24 public void setName(String name) {
25 this.name = name;
26 }
27 public int getAge() {
28 return age;
29 }
30 public void setAge(int age) {
31 this.age = age;
32 }
33 }
1 package com.rong.entity.hql;
2
3 import java.util.Iterator;
4 import java.util.List;
5 import java.util.Map;
6 import java.util.Map.Entry;
7 import java.util.Set;
8
9 import org.hibernate.Query;
10 import org.hibernate.Session;
11 import org.hibernate.SessionFactory;
12 import org.hibernate.Transaction;
13 import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
14 import org.hibernate.cfg.Configuration;
15 import org.hibernate.service.ServiceRegistry;
16 import org.junit.After;
17 import org.junit.Before;
18 import org.junit.Test;
19 //hql语句(面向)：类 对象 属性
20 public class TestHQL {
21 SessionFactory sessionFactory;
22 Session session;
23 Transaction transaction;
24 @Before
25 public void init() {
26 Configuration configuration = new Configuration().configure();
27 ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
28 .applySettings(configuration.getProperties()).build();
29 sessionFactory=configuration.buildSessionFactory(serviceRegistry);
30 session = sessionFactory.openSession();
31 transaction = session.beginTransaction();
32
33 }
34 //插入数据
35 @Test
36 public void addData(){
37 Student student=null;
38 for(int i=1;i<=100;i++){
39 student=new Student();
40 student.setAge(i);
41 student.setName("先生"+i);
42 session.save(student);
43 }
44 }
45 //查询所有的学生 (所有列)
46 @Test
47 public void test1(){
48 Query query = session.createQuery("from Student");//Student是类名
49 List<Student> list = query.list();
50 System.out.println(list.size());
51 for (Student student : list) {
52 System.out.println(student.getAge()+student.getName());
53 }
54 }
55 //查询所有的学生当中的某一列
56 @Test
57 public void test2(){
58 //Student是类名，name是Student类的成员变量名
59 Query query = session.createQuery("select s.name from Student s");
60 List<Student> list = query.list();
61 System.out.println(list);
62 }
63 //查询所有学生中的多列
64 @Test
65 public void test3(){
66 Query query = session.createQuery("select s.name,s.age from Student s");
67 //Query query = session.createQuery("select name,age from Student s");
68 List<Object[]> list = query.list();
69 //list集合的每一个元素都是数组，而每一个数组都是由name和age构成
70 for (Object[] objects : list) {
71 for (Object object : objects) {
72 System.out.print(object);
73 }
74 System.out.println();
75 }
76 }
77 //分页查询
78 @Test
79 public void test4(){
80 //Query query = session.createQuery("select s from Student s");
81 Query query = session.createQuery("from Student");
82 query.setFirstResult(0);//设置从哪里开始
83 query.setMaxResults(3);//分页的条目
84 List<Student> list = query.list();
85 for (Student student : list) {
86 System.out.println(student.getAge()+student.getName());
87 }
88 }
89 //查询所有的学生的某些字段 返回值是map类型,即获取List<Map<String,Object>>结果
90 @Test
91 public void test5(){
92 //这个是没有别名的
93 //Query query = session.createQuery("select new map(s.name,s.age) from Student s");
94 //这个有别名的(修改key的值 就需要增加别名)
95 Query query = session.createQuery("select new map(s.name as name,s.age as age) from Student s");
96 List<Map<String,Object>> list = query.list();
97 //结果 key是 从0 开始的
98 //System.out.println(list);//[{0=先生1, 1=1}, {0=先生2, 1=2}, {0=先生3, 1=3}......]
99 System.out.println(list);//[{name=先生1, age=1}, {name=先生2, age=2}......]
100 for (Map<String, Object> map : list) {
101 Set<Entry<String, Object>> set = map.entrySet();
102 Iterator<Entry<String, Object>> iterator = set.iterator();
103 while(iterator.hasNext()){
104 Entry<String, Object> entry = iterator.next();
105 System.out.println(entry.getKey()+entry.getValue());
106 }
107 }
108 }
109 //查询所有的学生的某些字段，返回值是list类型,即获取List<List<Object>>结果
110 @Test
111 public void test6(){
112 Query query = session.createQuery("select new list(s.name,s.age) from Student s");
113 List<List<Object>> list = query.list();
114 for (List<Object> li : list) {
115 for (Object object : li) {
116 System.out.print(object);
117 }
118 System.out.println();
119 }
120 }
121 //查询student表，返回值是User,User类必须要有public User(int id, String name)的构造方法！！！
122 @Test
123 public void test7(){
124 Query query = session.createQuery("select new com.rong.entity.hql.User(s.id,s.name) from Student s");
125 List<User> list = query.list();
126 for (User user : list) {
127 System.out.println(user.getId()+user.getName());
128 }
129 }
130
131
132 @After
133 public void destroy(){
134 transaction.commit();
135 session.close();
136 sessionFactory.close();
137 }
138 }
关联(持久化类)与连接(数据库表)
1 package com.rong.entity.myhql;
2
3 import javax.persistence.Entity;
4 import javax.persistence.FetchType;
5 import javax.persistence.GeneratedValue;
6 import javax.persistence.GenerationType;
7 import javax.persistence.Id;
8 import javax.persistence.OneToOne;
9
10 @Entity
11 public class Teacher {
12 @Id
13 @GeneratedValue(strategy=GenerationType.AUTO)
14 private int tid;
15 private String tname;
16 @OneToOne(targetEntity=Student.class,fetch=FetchType.LAZY,mappedBy="teacher")
17 private Student student;
18 public int getTid() {
19 return tid;
20 }
21 public void setTid(int tid) {
22 this.tid = tid;
23 }
24 public String getTname() {
25 return tname;
26 }
27 public void setTname(String tname) {
28 this.tname = tname;
29 }
30 }
1 package com.rong.entity.myhql;
2
3 import javax.persistence.Entity;
4 import javax.persistence.FetchType;
5 import javax.persistence.GeneratedValue;
6 import javax.persistence.GenerationType;
7 import javax.persistence.Id;
8 import javax.persistence.JoinColumn;
9 import javax.persistence.OneToOne;
10
11 @Entity
12 public class Student {
13 @Id
14 @GeneratedValue(strategy=GenerationType.AUTO)
15 private int id;
16 private String name;
17 private int age;
18 @OneToOne(targetEntity=Teacher.class,fetch=FetchType.LAZY)
19 @JoinColumn(name="t_id",referencedColumnName="tid",unique=true)
20 private Teacher teacher;
21 public int getId() {
22 return id;
23 }
24 public void setId(int id) {
25 this.id = id;
26 }
27 public String getName() {
28 return name;
29 }
30 public void setName(String name) {
31 this.name = name;
32 }
33 public int getAge() {
34 return age;
35 }
36 public void setAge(int age) {
37 this.age = age;
38 }
39 public Teacher getTeacher() {
40 return teacher;
41 }
42 public void setTeacher(Teacher teacher) {
43 this.teacher = teacher;
44 }
45 }
1 package com.rong.entity.myhql;
2
3 import java.util.List;
4
5 import org.hibernate.Query;
6 import org.hibernate.Session;
7 import org.hibernate.SessionFactory;
8 import org.hibernate.Transaction;
9 import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
10 import org.hibernate.cfg.Configuration;
11 import org.hibernate.service.ServiceRegistry;
12 import org.junit.After;
13 import org.junit.Before;
14 import org.junit.Test;
15 //hql语句(面向)：类 对象 属性
16 public class TestHQL {
17 SessionFactory sessionFactory;
18 Session session;
19 Transaction transaction;
20 @Before
21 public void init() {
22 Configuration configuration = new Configuration().configure();
23 ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
24 .applySettings(configuration.getProperties()).build();
25 sessionFactory=configuration.buildSessionFactory(serviceRegistry);
26 session = sessionFactory.openSession();
27 transaction = session.beginTransaction();
28
29 }
30 //插入数据
31 @Test
32 public void addData(){
33 Student student=null;
34 Teacher teacher=null;
35 for(int i=1;i<=20;i++){
36 teacher=new Teacher();
37 teacher.setTname("老师"+i);
38 student=new Student();
39 student.setAge(i);
40 student.setName("学生"+i);
41 student.setTeacher(teacher);
42 session.save(teacher);
43 session.save(student);
44 }
45 }
46 //1.隐式关联(不需要写join语句)
47 //查询时，关联的属性是一个持久化类. @ManyToOne、@OneToOne
48 @Test
49 public void test1(){
50 Query query = session.createQuery("select s from Student s where s.teacher.tid=?");
51 query.setParameter(0, 3);
52 List<Student> list = query.list();
53 for(int i=0;i<list.size();i++){
54 Student student = list.get(i);
55 //3学生333老师3
56 System.out.println(student.getId()+student.getName()+student.getAge()
57 +student.getTeacher().getTid()+student.getTeacher().getTname());
58 }
59
60 }
61 //2.显示关联(需要写join语句)
62 //注意：若使用@OneToMany、@ManyToMany查询时，关联的属性是一个Set集合. 这里使用@OneToOne
63 @Test
64 public void test2(){
65 //注意： inner join 后边的类 要写 属性 inner join s.teacher
66 Query query = session.createQuery("select s from Student s inner join s.teacher t where t.tid=?");
67 query.setParameter(0, 18);
68 Object uniqueResult = query.uniqueResult();
69 Student student=(Student)uniqueResult;
70 //18学生1818老师18
71 System.out.println(student.getId()+student.getName()+student.getAge()
72 +student.getTeacher().getTname());
73 }
74 //3.抓取连接(查询延迟的属性)
75 //查询时，关联的属性配置了延迟加载的，但本次查询要查询出来.join fetch 关联的属性
76 @Test
77 public void test3(){
78 //查两次，要配置延迟加载
79 //这里肯定是把Student查出来了。但是Student中的teacher属性还没有查数据库
80 //因为Student的teacher属性配置了懒加载fetch=FetchType.LAZY
81 Query query = session.createQuery("select s from Student s");
82 List<Student> list = query.list();
83 Student student = list.get(0);
84 //要真正使用Teacher了。所以要去查数据库。
85 System.out.println(student.getTeacher().getTname());
86
87 System.out.println("=======================================");
88 //查一次，要配置延迟加载
89 //就是teacher属性虽然是懒加载的，但是我们通过join fetch直接把懒加载的属性全部查出来。
90 Query query2 = session.createQuery("select s from Student s join fetch s.teacher");
91 List<Student> list2 = query2.list();
92 Student student2 = list.get(0);
93 System.out.println(student2.getTeacher().getTname());//这里不会再发sql了。
94 }
95 @After
96 public void destroy(){
97 transaction.commit();
98 session.close();
99 sessionFactory.close();
100 }
101 }
排序order by
分组group by
1 package com.rong.entity.group;
2
3 import java.util.HashSet;
4 import java.util.Set;
5
6 import javax.persistence.Entity;
7 import javax.persistence.FetchType;
8 import javax.persistence.GeneratedValue;
9 import javax.persistence.GenerationType;
10 import javax.persistence.Id;
11 import javax.persistence.OneToMany;
12
13 @Entity
14 public class Teacher {
15 @Id
16 @GeneratedValue(strategy=GenerationType.AUTO)
17 private int tid;
18 private String tname;
19 @OneToMany(targetEntity=Student.class,fetch=FetchType.LAZY,mappedBy="teacher")
20 private Set<Student> students=new HashSet<Student>();
21 public int getTid() {
22 return tid;
23 }
24 public void setTid(int tid) {
25 this.tid = tid;
26 }
27 public String getTname() {
28 return tname;
29 }
30 public void setTname(String tname) {
31 this.tname = tname;
32 }
33 public Set<Student> getStudents() {
34 return students;
35 }
36 public void setStudents(Set<Student> students) {
37 this.students = students;
38 }
39 }
1 package com.rong.entity.group;
2
3 import javax.persistence.Entity;
4 import javax.persistence.FetchType;
5 import javax.persistence.GeneratedValue;
6 import javax.persistence.GenerationType;
7 import javax.persistence.Id;
8 import javax.persistence.JoinColumn;
9 import javax.persistence.ManyToOne;
10
11 @Entity
12 public class Student {
13 @Id
14 @GeneratedValue(strategy=GenerationType.AUTO)
15 private int id;
16 private String name;
17 private int math;
18 private int java;
19 @ManyToOne(targetEntity=Teacher.class,fetch=FetchType.LAZY)
20 @JoinColumn(name="t_id",referencedColumnName="tid")
21 private Teacher teacher;
22 public int getId() {
23 return id;
24 }
25 public void setId(int id) {
26 this.id = id;
27 }
28 public String getName() {
29 return name;
30 }
31 public void setName(String name) {
32 this.name = name;
33 }
34 public int getMath() {
35 return math;
36 }
37 public void setMath(int math) {
38 this.math = math;
39 }
40 public int getJava() {
41 return java;
42 }
43 public void setJava(int java) {
44 this.java = java;
45 }
46 public Teacher getTeacher() {
47 return teacher;
48 }
49 public void setTeacher(Teacher teacher) {
50 this.teacher = teacher;
51 }
52 }
1 package com.rong.entity.group;
2
3 import java.util.List;
4 import java.util.Random;
5
6 import org.hibernate.Query;
7 import org.hibernate.Session;
8 import org.hibernate.SessionFactory;
9 import org.hibernate.Transaction;
10 import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
11 import org.hibernate.cfg.Configuration;
12 import org.hibernate.service.ServiceRegistry;
13 import org.junit.After;
14 import org.junit.Before;
15 import org.junit.Test;
16 //hql语句(面向)：类 对象 属性
17 public class TestHQL {
18 SessionFactory sessionFactory;
19 Session session;
20 Transaction transaction;
21 @Before
22 public void init() {
23 Configuration configuration = new Configuration().configure();
24 ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
25 .applySettings(configuration.getProperties()).build();
26 sessionFactory=configuration.buildSessionFactory(serviceRegistry);
27 session = sessionFactory.openSession();
28 transaction = session.beginTransaction();
29 }
30 //随机生成名字
31 public String randomName(){
32 String string="abcdefghijklmnopqrstuvwxyz";
33 StringBuilder stringBuilder=new StringBuilder();
34 Random random=new Random();
35 for(int i=0;i<4;i++){
36 int index = random.nextInt(string.length());
37 char charAt = string.charAt(index);
38 stringBuilder.append(charAt);
39 }
40 return stringBuilder.toString();
41 }
42 //插入数据
43 @Test
44 public void addData(){
45 Teacher teacher1=new Teacher();
46 teacher1.setTname("龙老师");
47 session.save(teacher1);
48 Teacher teacher2=new Teacher();
49 teacher2.setTname("高老师");
50 session.save(teacher2);
51 Random random=new Random();
52 for(int i=1;i<=100;i++){
53 Student student=new Student();
54 student.setName(randomName());
55 student.setJava(random.nextInt(100)+1);
56 student.setMath(random.nextInt(100)+1);
57 if(random.nextInt(2)==0){
58 student.setTeacher(teacher1);
59 }else{
60 student.setTeacher(teacher2);
61 }
62 session.save(student);
63 }
64 }
65 //1.根据老师分组统计学生数量(统计每位老师的学生数量)
66 @Test
67 public void test1(){
68 Query query = session.createQuery("select count(s),s.teacher.tname from Student s group by s.teacher.tname");
69 List<Object[]> list = query.list();
70 //46高老师
71 //54龙老师
72 for (Object[] objects : list) {
73 for (Object object : objects) {
74 System.out.print(object);
75 }
76 System.out.println();
77 }
78 }
79 //2.根据老师分组统计学生平均分,总分数
80 @Test
81 public void test2(){
82 Query query = session.createQuery("select sum(s.java+s.math),avg(s.java+s.math),s.teacher.tname from Student s group by s.teacher.tname");
83 List<Object[]> list = query.list();
84 //4659 101.2826 高老师
85 //5256 97.3333 龙老师
86 for (Object[] objects : list) {
87 for (Object object : objects) {
88 System.out.print(object+" ");
89 }
90 System.out.println();
91 }
92 }
93
94 @After
95 public void destroy(){
96 transaction.commit();
97 session.close();
98 sessionFactory.close();
99 }
100 }
分组过滤having
1 //根据老师分组统计学生平均分,总分数，并且把老师为1的过滤出来
2 @Test
3 public void test3(){
4 Query query = session.createQuery("select avg(s.java+s.math),sum(s.java+s.math),s.teacher.tname,s.teacher.tid from Student s group by s.teacher.tname having s.teacher.tid=?");
5 query.setParameter(0, 1);
6 List<Object[]> list = query.list();
7 //97.3333 5256 龙老师 1
8 for (Object[] objects : list) {
9 for (Object object : objects) {
10 System.out.print(object+" ");
11 }
12 System.out.println();
13 }
14 }
聚集函数(统计函数)
1 //聚集函数(统计函数) count max min avg sum
2 @Test
3 public void test4(){
4 Long count=(Long) session.createQuery("select count(*) from Student").uniqueResult();
5 System.out.println("学生总人数："+count);
6 int max=(int) session.createQuery("select max(s.java) from Student s").uniqueResult();
7 System.out.println("java最高成绩："+max);
8 int min=(int) session.createQuery("select min(s.math) from Student s").uniqueResult();
9 System.out.println("math最低成绩："+min);
10 double avg = (double) session.createQuery("select avg(s.java+s.math) from Student s").uniqueResult();
11 System.out.println("每位学生的平均总成绩："+avg);
12 Long sum = (Long) session.createQuery("select sum(s.java+s.math) from Student s").uniqueResult();
13 System.out.println("所有学生的总成绩之和："+sum);
14 }