(15)hql初始化及常用的查询

客官°小女子只卖身不卖艺 2022-05-03 14:00 135阅读 0赞

@Entity
    public class Classroom {
    	private int id;
    	private String name;
    	private int grade;
    	private Set<Student> stus;
    	private Special special;
    	
    	public Classroom() {
    	}
    	public Classroom(int id) {
    		// TODO 自动生成的构造函数存根
    		this.id=id;
    	}
    	
    	public Classroom(String name, int grade, Special special) {
    		// TODO 自动生成的构造函数存根
    		this.grade=grade;
    		this.special=special;
    		this.name=name;
    	}
    	@Id
    	@GeneratedValue(strategy = GenerationType.IDENTITY)
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public int getGrade() {
    		return grade;
    	}
    	public void setGrade(int grade) {
    		this.grade = grade;
    	}
    	@OneToMany(mappedBy="classroom")
    	@LazyCollection(LazyCollectionOption.EXTRA)
    	public Set<Student> getStus() {
    		return stus;
    	}
    	public void setStus(Set<Student> stus) {
    		this.stus = stus;
    	}
    	@ManyToOne
    	@JoinColumn(name="spe_id")
    	public Special getSpecial() {
    		return special;
    	}
    	public void setSpecial(Special special) {
    		this.special = special;
    	}
    	
    	
    }

@Entity
    public class Special {
    private int id;
    private String name;
    private String type;
    private Set<Classroom> clas;
    
    
    
    public Special() {
    }
    public Special(String name, String type) {
    	this.name=name;
    	this.type=type;
    }
    public Special(int id) {
    	// TODO 自动生成的构造函数存根
    	this.id=id;
    }
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public int getId() {
    	return id;
    }
    public void setId(int id) {
    	this.id = id;
    }
    public String getName() {
    	return name;
    }
    public void setName(String name) {
    	this.name = name;
    }
    public String getType() {
    	return type;
    }
    public void setType(String type) {
    	this.type = type;
    }
    @OneToMany(mappedBy="special")
    @LazyCollection(LazyCollectionOption.EXTRA)
    public Set<Classroom> getClas() {
    	return clas;
    }
    public void setClas(Set<Classroom> clas) {
    	this.clas = clas;
    }
    
    
    }

@Entity
    public class Student {
    	private int id;//属性
    	private String name;
    	private String sex;
    	private Classroom classroom;
    	
    	public Student() {}
    	@Id
    	@GeneratedValue(strategy = GenerationType.IDENTITY)
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public String getSex() {
    		return sex;
    	}
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    	@ManyToOne
    	@JoinColumn(name="c_id")
    	public Classroom getClassroom() {
    		return classroom;
    	}
    	public void setClassroom(Classroom classroom) {
    		this.classroom = classroom;
    	}
    	public Student(String name, String sex, Classroom classroom) {
    		this.name = name;
    		this.sex = sex;
    		this.classroom = classroom;
    	}
    	
    	
    }

**1.@GeneratedValue(strategy = GenerationType.IDENTITY)，这样hibernate就不会创建***[hibernate\_sequence][hibernate_sequence]*

**数据初始化:**

class TestAdd {
    	Random ran=new Random();
    	@Test
    	public void testAddSpecial() {
    		Session session = null;
    		session = util.HibernateUtil.getSession();
    		try {
    			session.beginTransaction();
    			
    			session.save(new Special("计算机教育","教育类"));
    			session.save(new Special("计算机应用技术","高职类"));
    			session.save(new Special("计算机网络技术","高职类"));
    			session.save(new Special("计算机信息管理","高职类"));
    			session.save(new Special("数学教育","教育类"));
    			session.save(new Special("物理教育","教育类"));
    			session.save(new Special("化学教育","教育类"));
    			session.save(new Special("会计","高职类"));
    			session.save(new Special("英语教育","教育类"));
    			
    			session.getTransaction().commit();
    		} catch (HibernateException e) {
    			e.printStackTrace();
    			session.getTransaction().rollback();
    		} finally {
    			if(session!=null) session.close();
    		}
    	}
    	
    	@Test
    	public void testAddClassroom() {
    		Session session = null;
    		session =HibernateUtil.getSession();
    		try {
    			session.beginTransaction();
    			
    			session.save(new Classroom("计算机教育1班",2009,new Special(1)));
    			session.save(new Classroom("计算机教育2班",2009,new Special(1)));
    			session.save(new Classroom("计算机教育班",2010,new Special(1)));
    			session.save(new Classroom("计算机教育班",2011,new Special(1)));
    			session.save(new Classroom("计算机应用技术",2009,new Special(2)));
    			session.save(new Classroom("计算机应用技术",2010,new Special(2)));
    			session.save(new Classroom("计算机应用技术",2011,new Special(2)));
    			session.save(new Classroom("计算机网络技术",2009,new Special(3)));
    			session.save(new Classroom("计算机网络技术",2010,new Special(3)));
    			session.save(new Classroom("计算机网络技术",2011,new Special(3)));
    			session.save(new Classroom("计算机信息管理",2009,new Special(4)));
    			session.save(new Classroom("计算机信息管理",2010,new Special(4)));
    			session.save(new Classroom("计算机信息管理",2011,new Special(4)));
    			session.save(new Classroom("数学教育1班",2009,new Special(5)));
    			session.save(new Classroom("数学教育2班",2009,new Special(5)));
    			session.save(new Classroom("数学教育3班",2009,new Special(5)));
    			session.save(new Classroom("数学教育1班",2010,new Special(5)));
    			session.save(new Classroom("数学教育2班",2010,new Special(5)));
    			session.save(new Classroom("数学教育1班",2011,new Special(5)));
    			session.save(new Classroom("数学教育2班",2011,new Special(5)));
    			session.save(new Classroom("物理教育",2009,new Special(6)));
    			session.save(new Classroom("物理教育",2010,new Special(6)));
    			session.save(new Classroom("物理教育",2011,new Special(6)));
    			session.save(new Special("化学教育","教育类"));
    			session.save(new Classroom("化学教育",2009,new Special(7)));
    			session.save(new Classroom("化学教育",2010,new Special(7)));
    			session.save(new Classroom("化学教育",2011,new Special(7)));
    			session.save(new Classroom("会计",2009,new Special(8)));
    			session.save(new Classroom("会计",2010,new Special(8)));
    			session.save(new Classroom("会计",2011,new Special(8)));
    			session.save(new Classroom("英语教育A班",2009,new Special(9)));
    			session.save(new Classroom("英语教育B班",2009,new Special(9)));
    			session.save(new Classroom("英语教育A班",2010,new Special(9)));
    			session.save(new Classroom("英语教育B班",2010,new Special(9)));
    			session.save(new Classroom("英语教育A班",2011,new Special(9)));
    			session.save(new Classroom("英语教育B班",2011,new Special(9)));
    			session.save(new Classroom("选修课班A",2011,null));
    			session.save(new Classroom("选修课班B",2011,null));
    			session.getTransaction().commit();
    		} catch (HibernateException e) {
    			e.printStackTrace();
    			session.getTransaction().rollback();
    		} finally {
    			if(session!=null) session.close();
    		}
    	}
    	
    	@Test
    	public void testgetNames() {
    		for(int i=0;i<100;i++) {
    			System.out.println(testGetName());
    		}
    	}
    	
    	
    	
    	@Test
    	public String testGetName() {
    		String[] name1 = new String[]{"孔","张","叶","李","叶","孔",
    				"张","陈","刘","牛","夏","侯","令","令","赵","母","穆","倪",
    				"称","程","王","刘","冬","吴","马","沈"};
    		
    		String[] name2 = new String[]{"凡","课","颖","页","源","都",
    				"浩","皓","西","东","北","南","冲","昊","力","量","妮",
    				"敏","捷","杰","坚","名","生","华","鸣","蓝","春","虎","刚","诚"};
    		
    		String[] name3 = new String[]{"吞","明","敦","刀","备","伟",
    				"唯","楚","勇","诠","佺","河","正","震","点","贝","侠",
    				"伟","大","凡","琴","青","林","星","集","财"};
    		boolean three;
    		if(ran.nextInt(50)<=45) {
    			three=true;
    		}else {
    			three=false;
    		}
    		if(three) {
    			String n1=name1[ran.nextInt(name1.length)];
    			String n2=name2[ran.nextInt(name2.length)];
    			String n3=name3[ran.nextInt(name3.length)];
    			return n1+n2+n3;
    		}else {
    			String n1=name1[ran.nextInt(name1.length)];
    			int n=ran.nextInt(10);
    			String n2;
    			if(n>=5) {
    				n2=name2[ran.nextInt(name2.length)];
    			}else {
    				n2=name3[ran.nextInt(name3.length)];
    			}
    			return n1+n2;
    		}
    	}
    	@Test
    	public void testAddStu() {
    		Session session = null;
    		session = util.HibernateUtil.getSession();
    		try {
    			session.beginTransaction();
    			String[] sexs= new String[]{"男","女"};
    			for(int i=1;i<=32;i++) {
    				for(int j=1;j<=40;j++) {
    					session.save(new Student(testGetName(), sexs[ran.nextInt(2)], new Classroom(i)));
    				}
    			}
    			session.getTransaction().commit();
    		} catch (HibernateException e) {
    			e.printStackTrace();
    			session.getTransaction().rollback();
    		} finally {
    			if(session!=null) session.close();
    		}
    	}
    	
    	@Test
    	public void testAddStuNoCla() {
    		Session session = null;
    		session = util.HibernateUtil.getSession();
    		try {
    			session.beginTransaction();
    			String[] sexs= new String[]{"男","女"};
    			
    				for(int j=1;j<=100;j++) {
    					session.save(new Student(testGetName(), sexs[ran.nextInt(2)], null));
    				}
    				
    			session.getTransaction().commit();
    		} catch (HibernateException e) {
    			e.printStackTrace();
    			session.getTransaction().rollback();
    		} finally {
    			if(session!=null) session.close();
    		}
    	}
    }

**数据初始化，有时只需要一个对象具有id即可，比如为cla设置spe\_id，只需要传入一个special对象，里面有id值即可**

**test01：**

@Test
    	void test01() {
    		Session s=null;
    		try {
    			s=HibernateUtil.getSession();
    		    
    			Query query=s.createQuery("from Special");
    			List<Special> spes=query.list();
    			for(Special spe:spes) {
    				System.out.println(spe.getName());
    			}
    		} catch (Exception e) {
    			// TODO 自动生成的 catch 块
    			e.printStackTrace();
    			s.getTransaction().rollback();
    		}finally {
    			HibernateUtil.closeSession(s);
    		}
    	}

hql是基于**对象**查询的

**test02：**

void test02() {
    		Session s=null;
    		try {
    			s=HibernateUtil.getSession();
    		    
    			Query query=s.createQuery("select spe from Special spe");
    			List<Special> spes=query.list();
    			for(Special spe:spes) {
    				System.out.println(spe.getName());
    			}
    		} catch (Exception e) {
    			// TODO 自动生成的 catch 块
    			e.printStackTrace();
    			s.getTransaction().rollback();
    		}finally {
    			HibernateUtil.closeSession(s);
    		}
    	}

**select spe from Special spe，注意语句都要在对象Special后加一个别名spe，然后select xx，要去什么对象使用别名即可**

**test03：**

void test03() {
    		Session s=null;
    		try {
    			s=HibernateUtil.getSession();
    		    
    			Query query=s.createQuery("select stu from Student stu where name like '%张%'");
    			List<Student> stus=query.list();
    			for(Student stu:stus) {
    				System.out.println(stu.getName());
    			}
    		} catch (Exception e) {
    			// TODO 自动生成的 catch 块
    			e.printStackTrace();
    			s.getTransaction().rollback();
    		}finally {
    			HibernateUtil.closeSession(s);
    		}
    	}

**test04:**

void test04() {
    		Session s=null;
    		try {
    			s=HibernateUtil.getSession();
    		    
    			Query query=s.createQuery("select stu from Student stu where name like ?0").setParameter(0, "%李%");
    			List<Student> stus=query.list();
    			for(Student stu:stus) {
    				System.out.println(stu.getName()+","+stu.getSex());
    			}
    		} catch (Exception e) {
    			// TODO 自动生成的 catch 块
    			e.printStackTrace();
    			s.getTransaction().rollback();
    		}finally {
    			HibernateUtil.closeSession(s);
    		}
    	}

**注意:（1）.避免sql注入攻击**

**（2）.新版的hibernate like ？0，然后在setParameter(0,xx),记得在？后面加索引，什么数都可以，前后对应即可**

**test05:**

void test05() {
       		Session s=null;
       		try {
       			s=HibernateUtil.getSession();
       		    
       			Query query=s.createQuery("select stu from Student stu where name like ?0 and sex=:sex").setParameter(0, "%李%").setParameter("sex", "男");
       			List<Student> stus=query.list();
       			for(Student stu:stus) {
       				System.out.println(stu.getName()+","+stu.getSex());
       			}
       		} catch (Exception e) {
       			// TODO 自动生成的 catch 块
       			e.printStackTrace();
       			s.getTransaction().rollback();
       		}finally {
       			HibernateUtil.closeSession(s);
       		}
       	}

**注意:（1）.基于别名查询**

**test06：**

void test06() {
       		Session s=null;
       		try {
       			s=HibernateUtil.getSession();
       		    
       			long count=(long) s.createQuery("select count(*) from Student where name like ?0").setParameter(0, "%李%").uniqueResult();
       			System.out.println(count);
       		} catch (Exception e) {
       			// TODO 自动生成的 catch 块
       			e.printStackTrace();
       			s.getTransaction().rollback();
       		}finally {
       			HibernateUtil.closeSession(s);
       		}
       	}

**注意:（1）.返回的count是long对象**

**test07：**

void test07() {
       		Session s=null;
       		try {
       			s=HibernateUtil.getSession();
       		    
       			Student student=(Student) s.createQuery("select stu from Student stu where id=:id").setParameter("id", 1).uniqueResult();
       			System.out.println(student.getName());
       		} catch (Exception e) {
       			// TODO 自动生成的 catch 块
       			e.printStackTrace();
       			s.getTransaction().rollback();
       		}finally {
       			HibernateUtil.closeSession(s);
       		}
       	}

**注意：(1).使用uniqueResult();就是仅仅只返回一个对象，使用list返回的就是list数组**

**test08：**

void test08() {
       		Session s=null;
       		try {
       			s=HibernateUtil.getSession();
       		    
       			List<Object[]> objs=s.createQuery("select stu.sex,count(*) from Student stu group by stu.sex").list();
       			for(Object[] obj:objs) {
       				System.out.println(obj[0]+":"+obj[1]);
       			}
       		} catch (Exception e) {
       			// TODO 自动生成的 catch 块
       			e.printStackTrace();
       			s.getTransaction().rollback();
       		}finally {
       			HibernateUtil.closeSession(s);
       		}
       	}

**注意：(1).该sql语句返回的是List<Object\[\]>**

**test09:**

void test09() {
       		Session s=null;
       		try {
       			s=HibernateUtil.getSession();
       		    
       			List<Student> objs=s.createQuery("select stu from Student stu where stu.classroom.id = :cla_id").setParameter("cla_id", 1).list();
       			for(Student obj:objs) {
       				System.out.println(obj.getId()+":"+obj.getName());
       			}
       		} catch (Exception e) {
       			// TODO 自动生成的 catch 块
       			e.printStackTrace();
       			s.getTransaction().rollback();
       		}finally {
       			HibernateUtil.closeSession(s);
       		}
       	}

**注意：(1).where stu.classroom.id=xx，此时会自动完成连接，不过该连接效率较低**

**test10:**

void test10() {
       		Session s=null;
       		try {
       			s=HibernateUtil.getSession();
       		    
       			List<Student> objs=s.createQuery("select stu from Student stu where stu.classroom.id in (:cla_id) and stu.name like ?0").setParameter(0, "%张%").setParameterList("cla_id", new Integer[] {1,2}).list();
       			for(Student obj:objs) {
       				System.out.println(obj.getId()+":"+obj.getName());
       			}
       		} catch (Exception e) {
       			// TODO 自动生成的 catch 块
       			e.printStackTrace();
       			s.getTransaction().rollback();
       		}finally {
       			HibernateUtil.closeSession(s);
       		}
       	}

**注意:（1）：先setParameter再setParameterList，然后使用in必须基于别名，即in （：cla\_id），然后再setParameterList("cla\_id",new integer\[\]\{1,2\})**

**test11:**

void test11() {
       		Session s=null;
       		try {
       			s=HibernateUtil.getSession();
       		    
       			List<Student> objs=s.createQuery("select stu from Student stu where stu.classroom.id in (:cla_id)").setParameterList("cla_id", new Integer[] {1,2}).setFirstResult(0).setMaxResults(20).list();
       			for(Student obj:objs) {
       				System.out.println(obj.getId()+":"+obj.getName());
       			}
       		} catch (Exception e) {
       			// TODO 自动生成的 catch 块
       			e.printStackTrace();
       			s.getTransaction().rollback();
       		}finally {
       			HibernateUtil.closeSession(s);
       		}
       	}

**注意:（1）：分页可以设置setFirstResult（0）及setMaxResults(20),前者表示取索引0开始的数据，MaxResult表示一页最大容量**

**test12：**

@Test
       	void test12() {
       		Session s=null;
       		try {
       			s=HibernateUtil.getSession();
       		    
       			List<Student> objs=s.createQuery("select stu from Student stu where stu.classroom is null").list();
       			for(Student obj:objs) {
       				System.out.println(obj.getId()+":"+obj.getName());
       			}
       		} catch (Exception e) {
       			// TODO 自动生成的 catch 块
       			e.printStackTrace();
       			s.getTransaction().rollback();
       		}finally {
       			HibernateUtil.closeSession(s);
       		}
       	}

**注意:（1）：可以使用stu.classroom is null，不能使用stu.classroom = null。**

[hibernate_sequence]: http://www.baidu.com/link?url=x8Xbv-sTsaE_ttMhMAc1Dq1rWQnW7XKPA82a_4308vuyKaogS0uaGQctocwUB133EoqhdLJVuG503rdmKK8emnmIYotsdNbUf49eXdBQncu