三套查询之SQL查询
Native Sql Query原生的sql查询.
要求写sql语句.
SQLQuery 是 Query的子类
1.查询所有的学生
1 //1.查询所有的学生
2 @Test
3 public void test1(){
4 SQLQuery sqlQuery = session.createSQLQuery("select * from student");//student是表名，不是类名
5 sqlQuery.addEntity(Student.class);
6 List<Student> list = sqlQuery.list();
7 for (Student student : list) {
8 System.out.println(student.getId()+student.getName()+student.getTeacher().getTname());
9 }
10 System.out.println("========================================");
11 SQLQuery sqlQuery2 = session.createSQLQuery("select s.* from student s where s.id%2=0");//student是表名，不是类名
12 sqlQuery2.addEntity("com.rong.entity.sql.Student");
13 List<Student> list2 = sqlQuery2.list();
14 for (Student student : list2) {
15 System.out.println(student.getId()+student.getName()+student.getTeacher().getTname());
16 }
17
18 }
2.标量查询
1 //2.标量查询
2 @Test
3 public void test2(){
4 SQLQuery sqlQuery = session.createSQLQuery("select s.java,s.math,s.name from student s where s.name like ?");
5 sqlQuery.setParameter(0, "%a%");
6 List<Object[]> list = sqlQuery.list();
7 for (Object[] objects : list) {
8 for (Object object : objects) {
9 System.out.print(object+" ");
10 }
11 System.out.println();
12 }
13 System.out.println("=========================================");
14 SQLQuery sqlQuery2 = session.createSQLQuery("select * from student s");
15 sqlQuery2.addScalar("s.id");
16 sqlQuery2.addScalar("s.name");
17 List<Object[]> list2 = sqlQuery2.list();
18 for (Object[] objects : list2) {
19 for (Object object : objects) {
20 System.out.print(object+" ");
21 }
22 System.out.println();
23 }
24 }
3.关联查询
1 //3.关联查询
2 @Test
3 public void test3(){
4 SQLQuery sqlQuery = session.createSQLQuery("select * from student s,teacher t where s.t_id=t.tid");
5 sqlQuery.addEntity("s",Student.class);// 实体查询
6 sqlQuery.addEntity("t",Teacher.class);// 实体查询
7 sqlQuery.addJoin("t", "s.teacher");// 关联查询 (s.学生持久类中的关联属性)
8 /*sqlQuery.addScalar("s.name");// 标量查询
9 sqlQuery.addScalar("t.tname");// 标量查询
10 List<Object[]> list = sqlQuery.list();
11 for (Object[] objects : list) {
12 for (Object object : objects) {
13 System.out.print(object);
14 }
15 System.out.println();
16 }*/
17 List<Object[]> list = sqlQuery.list();
18 for (Object[] objects : list) {
19 for (Object object : objects) {
20 if(object instanceof Student){
21 Student student=(Student) object;
22 System.out.println(student.getId()+student.getName()+student.getTeacher().getTname());
23 }else if(object instanceof Teacher){
24 Teacher teacher=(Teacher) object;
25 System.out.println(teacher.getTid()+teacher.getTname());
26 }
27 }
28 System.out.println();
29 }
30 }
4.命名查询
A.第一种方式*hbm.xml
1.提供一个配置文件. (xxx.hbm.xml).
2.在hibernate.cfg.xml文件中配置xxx.hbm.xml.
3.定义sql
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<sql-query name="sql_query">
select s.*,t.*
from student s,teacher t
where s.t_id=t.tid
<!-- addEntity: 实体查询 -->
<return alias="s" class="com.rong.entity.sql.Student"></return>
<return alias="t" class="com.rong.entity.sql.Teacher"></return>
<!-- addJoin : 关联查询 -->
<return-join alias="t" property="s.teacher"></return-join>
<!-- addScalar : 标量查询 -->
<return-scalar column="s.name"/>
<return-scalar column="t.tname"/>
</sql-query>
</hibernate-mapping>
4.代码
1 @Test
2 public void test4(){
3 Query query = session.getNamedQuery("sql_query");
4 List<Object[]> list = query.list();
5 for (Object[] objects : list) {
6 for (Object object : objects) {
7 System.out.println(object);
8 }
9 System.out.println();
10 }
11 }
B.第二种方式: (在持久化类上加注解).
1 package com.rong.entity.sql;
2
3 import javax.persistence.ColumnResult;
4 import javax.persistence.Entity;
5 import javax.persistence.EntityResult;
6 import javax.persistence.FetchType;
7 import javax.persistence.GeneratedValue;
8 import javax.persistence.GenerationType;
9 import javax.persistence.Id;
10 import javax.persistence.JoinColumn;
11 import javax.persistence.ManyToOne;
12 import javax.persistence.NamedNativeQueries;
13 import javax.persistence.NamedNativeQuery;
14 import javax.persistence.SqlResultSetMapping;
15
16 @Entity
17 //单个SQL语句，一个实体类中该注解只能使用一次
18 @NamedNativeQuery(name = "sql1", query = "select * from student s,teacher t "
19 + "where s.t_id=t.tid",resultClass=Student.class)
20 //多个SQL语句
21 @NamedNativeQueries(value = { @NamedNativeQuery(name = "sql2", query = "select * from student s,teacher t where s.t_id=t.tid and s.name like ?"
22 ,resultSetMapping="rs")
23 ,@NamedNativeQuery(name = "sql3", query = "select s.java from student s where s.id>90")})
24 @SqlResultSetMapping(name = "rs",entities={@EntityResult(entityClass = Student.class)
25 ,@EntityResult(entityClass = Teacher.class)},columns=@ColumnResult(name = "s.name"))
26 public class Student {
27 @Id
28 @GeneratedValue(strategy=GenerationType.AUTO)
29 private int id;
30 private String name;
31 private int math;
32 private int java;
33 @ManyToOne(targetEntity=Teacher.class,fetch=FetchType.LAZY)
34 @JoinColumn(name="t_id",referencedColumnName="tid")
35 private Teacher teacher;
36 public int getId() {
37 return id;
38 }
39 public void setId(int id) {
40 this.id = id;
41 }
42 public String getName() {
43 return name;
44 }
45 public void setName(String name) {
46 this.name = name;
47 }
48 public int getMath() {
49 return math;
50 }
51 public void setMath(int math) {
52 this.math = math;
53 }
54 public int getJava() {
55 return java;
56 }
57 public void setJava(int java) {
58 this.java = java;
59 }
60 public Teacher getTeacher() {
61 return teacher;
62 }
63 public void setTeacher(Teacher teacher) {
64 this.teacher = teacher;
65 }
66 }
1 @Test
2 public void test5(){
3 Query query = session.getNamedQuery("sql1");
4 List<Student> list = query.list();
5 for (Student student : list) {
6 System.out.println(student.getName()+student.getTeacher().getTname());
7 }
8 System.out.println("==============================");
9 Query query2 = session.getNamedQuery("sql2");
10 query2.setParameter(0, "%a%");
11 List<Object[]> list2 = query2.list();
12 for (Object[] objects : list2) {
13 for (Object object : objects) {
14 System.out.println(object);
15 }
16 System.out.println();
17 }
18 System.out.println("================================");
19 Query query3 = session.getNamedQuery("sql3");
20 List<Object> list3 = query3.list();
21 for (Object object : list3) {
22 System.out.println(object);
23 }
24 }