Hibernate HQL以及相关方法

布满荆棘的人生 2022-08-21 07:59 167阅读 0赞

***纸上得来终觉浅***

**1.首先Hibernate有以下几种检索方式：**

1)导航对象图检索方式：根据已经加载的对象导航到其他对象，比如前面文章讲到的关联方式，A对B，A可以加载B，那么只要获取A对象，就可以获取到B对象；

2)OID检索方式，按照对象的OID来检索对象，比如get和load方法；

3)HQL检索  (Hibernate query language),一种面向对象的查询，查询的结果面向对象。

4)QBC检索，对查询语句进行封装，更加面向对象，同时查询语句也面向对象。

5)本地SQL，就是一般的sql了。

**2.HQL检索：**

**1)先创建表格以及添加数据(采用之前文章讲述的双向一对多的关联方式)：**

Teacher类：

package roadArchitectWeb;
    
    import java.util.HashSet;
    import java.util.Set;
    
    public class Teacher {
    	private Integer Id;
    	private String Name;
    	private String PhoneNum;
    	private Set<Student> Students = new HashSet<>();
    
    	public Set<Student> getStudents() {
    		return Students;
    	}
    	public void setStudents(Set<Student> students) {
    		Students = students;
    	}
    	public Integer getId() {
    		return Id;
    	}
    	public void setId(Integer id) {
    		Id = id;
    	}
    	public String getName() {
    		return Name;
    	}
    	public void setName(String name) {
    		Name = name;
    	}
    	public String getPhoneNum() {
    		return PhoneNum;
    	}
    	public void setPhoneNum(String phoneNum) {
    		PhoneNum = phoneNum;
    	}
    	public  Teacher() {
    	}
    	@Override
    	public String toString() {
    		return "Teacher [Id=" + Id + ", Name=" + Name + ", PhoneNum=" + PhoneNum + ", Students=" + Students + "]";
    	}
    }

Student类：

package roadArchitectWeb;
    
    import java.util.HashSet;
    import java.util.Set;
    
    public class Student {
    	private Integer Id;
    	private String sName;
    	private String Age;
    	private Teacher teacher;
    	public Teacher getTeacher() {
    		return teacher;
    	}
    	public void setTeacher(Teacher teacher) {
    		this.teacher = teacher;
    	}
    	public Integer getId() {
    		return Id;
    	}
    	public void setId(Integer id) {
    		Id = id;
    	}
    	public String getsName() {
    		return sName;
    	}
    	public void setsName(String sName) {
    		this.sName = sName;
    	}
    	public String getAge() {
    		return Age;
    	}
    	public void setAge(String age) {
    		Age = age;
    	}
    	@Override
    	public String toString() {
    		return "Student [Id=" + Id + ", sName=" + sName + ", Age=" + Age + ", teacher=" + teacher + "]";
    	}
    	public Student(){
    	}
    }

Teacher和Student配置文件：

<?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
    
    <hibernate-mapping>
        <class name="roadArchitectWeb.Teacher" table="TEACHER">
            <id name="Id" type="java.lang.Integer">
                <column name="ID" />
                <generator class="native" />
            </id>
            <property name="Name" type="java.lang.String">
                <column name="NAME" />
            </property>
            <property name="PhoneNum" type="java.lang.String">
                <column name="PHONENUM" />
            </property>
            <set name="Students" table="STUDENT">  
      
            <key column="teacherId"></key>  
            <one-to-many class="roadArchitectWeb.Student"/>  
            </set>  
        </class>
    </hibernate-mapping>

<?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
    
    <hibernate-mapping package="roadArchitectWeb">
        <class name="Student" table="STUDENT">
            <id name="Id" type="java.lang.Integer">
                <column name="ID" />
                <generator class="native">
                </generator>
            </id>
            <property name="sName" type="java.lang.String">
                <column name="SNAME" />
            </property>
            <property name="Age" type="java.lang.String">
                <column name="AGE" />
            </property>
           	<many-to-one name="teacher" class="Teacher"  
            	column="teacherId">  
             </many-to-one>
        </class>
    </hibernate-mapping>

hibernate配置文件：

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE hibernate-configuration PUBLIC
    		"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    		"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
    <hibernate-configuration>
        <session-factory>
        <property name="connection.username">root</property>
        <property name="connection.password">123456</property>
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost/roadarchitectweb</property>
        
        <property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>
        <property name="hbm2ddl.auto">update</property>
        
        <property name="hibernate.c3p0.max_size">10</property>
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="c3p0.acquire_increment">2</property>
        
        <property name="c3p0.idle_test_period">2000</property>
        <property name="c3p0.timeout">2000</property>
        
        <property name="c3p0.max_statements">10</property>
        
        <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
        <mapping resource="roadArchitectWeb/Student.hbm.xml"/>
        <mapping resource="roadArchitectWeb/Teacher.hbm.xml"/>
        </session-factory>
    </hibernate-configuration>

测试类：

package roadArchitectWeb;
    import java.util.ArrayList;
    
    import org.hibernate.Query;
    import org.hibernate.Session;
    import org.hibernate.SessionFactory;
    import org.hibernate.Transaction;
    import org.hibernate.cfg.Configuration;
    import org.junit.Test;
    
    public class HibernateTest {
    	@Test
    	public void test() {
    		SessionFactory sessionFactory = null;
    		Configuration configuration = new Configuration().configure();
    		sessionFactory = configuration.buildSessionFactory();
    		Session session = sessionFactory.openSession();
    		Transaction transaction = session.beginTransaction();
    		
    		/*begin*/
    		Teacher teacher = new Teacher();
    		Student student = new Student();
    		teacher.setName("T1");
    		teacher.setPhoneNum("T18788837117");
    		student.setsName("S1");
    		student.setAge("S10");
    		student.setTeacher(teacher);
    		session.save(student);
    		session.save(teacher);
    		
    		Teacher teacher2 = new Teacher();
    		Student student2 = new Student();
    		teacher2.setName("T2");
    		teacher2.setPhoneNum("T28788837117");
    		student2.setsName("S2");
    		student2.setAge("S20");
    		student2.setTeacher(teacher2);
    		session.save(student2);
    		session.save(teacher2);
    		
    		Student student3 = new Student();
    		student3.setsName("S3");
    		student3.setAge("S30");
    		student3.setTeacher(teacher);
    		session.save(student3);
    		
    		Student student4 = new Student();
    		student4.setsName("S4");
    		student4.setAge("S40");
    		student4.setTeacher(teacher);
    		session.save(student4);
    		
    		Student student5 = new Student();
    		student5.setsName("S5");
    		student5.setAge("S50");
    		student5.setTeacher(teacher2);
    		session.save(student5);
    		
    		Student student6 = new Student();
    		student6.setsName("S6");
    		student6.setAge("S60");
    		student6.setTeacher(teacher2);
    		session.save(student6);
    		
    		/*按参数位置绑定(?)或者按参数名称(:Id)绑定*/
    		//1.创建Query对象
    //		String hql = "from Student a where a.sName like ? and a.teacher = ? and a.Id > :Id";
    //		Query query = session.createQuery(hql);
    		
    		//2.绑定参数
    //		Teacher teacher = new Teacher();
    //		teacher.setId(2);
    //		query.setString(0, "%S%")
    //		.setInteger("Id", 1)
    //		.setEntity(1,teacher);
    		
    		//3.执行查询
    //		java.util.List<Student> list = query.list();
    //		System.out.println("HibernateTest.test():"+list.size());
    		
    		transaction.commit();
    		session.close();
    		sessionFactory.close();
    	}
    }

**2)HQL是面向对象的，它的机制就是经过hibernate翻译为sql，写hql时候，只关注类和类的属性：**

在标准sql中应该写表名的地方写类型，列名的地方写属性名，如：Student是类名，sName是属性名：

/*按参数位置绑定(?)或者按参数名称(:Id)绑定*/
    		//1.创建Query对象
    		String hql = "from Student a where a.sName like ? and a.teacher = ? and a.Id > :Id";
    		Query query = session.createQuery(hql);
    		
    		//2.绑定参数
    		Teacher teacher = new Teacher();
    		teacher.setId(2);
    		query.setString(0, "%S%")
    		.setInteger("Id", 1)
    		.setEntity(1,teacher);
    		
    		//3.执行查询
    		java.util.List<Student> list = query.list();
    		System.out.println("HibernateTest.test():"+list.size());

**3)hibernate分页：**

setFirstResult指定查询第几页的数据，setMaxResult指定一页显示多少数据；查询的结果就是first页的max条数据：

//1.创建Query对象
    		String hql = "from Student a where a.sName like ? ";
    		Query query = session.createQuery(hql);
    		
    		//2.绑定参数  setFirstResult指定查询第几页的数据，setMaxResult指定一页显示多少数据；查询的结果就是first页的max条数据
    		query.setString(0, "%S%")
    		.setFirstResult(2)
    		.setMaxResults(2);
    		
    		//3.执行查询
    		java.util.List<Student> list = query.list();
    		System.out.println("HibernateTest.test():"+list.size());

**4)投影查询：查询的结果转换为对象的格式**

查询的结果为List，里面的每条数据都是一条记录，要想将一条数据转化为一个对象，就要使用hibernate的投影查询：

查询的结果要与类映射，首先要在Student类中新增一个构造器：

public Student(Integer id, String sName, String age, Teacher teacher) {
    		super();
    		Id = id;
    		this.sName = sName;
    		Age = age;
    		this.teacher = teacher;
    	}

//1.创建Query对象
    		String hql = "select new Student(a.Id,a.sName,a.Age,teacher) from Student a where a.sName like ? ";
    		Query query = session.createQuery(hql);
    		
    		//2.绑定参数  setFirstResult指定查询第几页的数据，setMaxResult指定一页显示多少数据；查询的结果就是first页的max条数据
    		query.setString(0, "%S%");
    		
    		//3.执行查询
    		java.util.List<Student> list = query.list();
    		/*可以使用getsName方法了*/
    		for(Student studentT:list){
    			System.out.println("HibernateTest.test():"+studentT.getsName());
    		}

**5)Hql还可以使用groupby和having以及其他聚合函数，同标准SQL相同。**