Hibernate框架簡介⑤

Milky-way發表於2018-08-11

HQL查詢

hql語句中沒有*的寫法:

測試程式碼:

package com.rl.hiber.test.hql;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import com.rl.hiber.model.User;
import com.rl.hiber.utils.HibernateUtils;


public class TestHibernate {
	
	@Test
	public void testAdd(){
		Session session = HibernateUtils.getSession();
		Transaction tx = session.beginTransaction();
		try {
			for(int i = 0; i < 10; i++){
				User user = new User();
				user.setUname("任亮"+i);
				if(i%2 == 0){
					user.setGender(1);
				}else{
					user.setGender(2);
				}
				user.setSalary(1000+ i*100);
				user.setBirthday(new Date());
				session.save(user);
			}
			tx.commit();
		} catch (Exception e) {
			e.printStackTrace();
			tx.rollback();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
	
	@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		
		try {
			//查詢所有的user類的物件
			//String hql = "from User";//from 後面跟的是類名, 而非表名
			String hql = "select u from User u";//hql中沒有*的寫法
			
			//根據hql語句建立查詢物件
			Query query = session.createQuery(hql);
			//查詢列表
			List<User> userList = query.list();
			for(User user: userList){
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
}

單列查詢:

package com.rl.hiber.test.hql;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import com.rl.hiber.model.User;
import com.rl.hiber.utils.HibernateUtils;


public class TestHibernate {
	
		
	/**
	 * 查詢物件的屬性
	 */
	@Test
	public void testQuery2(){
		Session session = HibernateUtils.getSession();
		
		try {
			//查詢所有的user類的物件
			//String hql = "from User";
			String hql = "select u.uname from User u";//hql中沒有*的寫法
			//根據hql語句建立查詢物件
			Query query = session.createQuery(hql);
			//查詢列表
			List<String> nameList = query.list();
			for(String name: nameList){
				System.out.println(name);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
	
	
}

多個屬性查詢:

package com.rl.hiber.test.hql;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import com.rl.hiber.model.User;
import com.rl.hiber.utils.HibernateUtils;


public class TestHibernate {
	
	
	/**
	 * 查詢物件的多個屬性
	 */
	@Test
	public void testQuery3(){
		Session session = HibernateUtils.getSession();
		try {
			//查詢所有的user類的物件
			//String hql = "from User";
			String hql = "select u.uname, u.gender from User u";//hql中沒有*的寫法
			//根據hql語句建立查詢物件
			Query query = session.createQuery(hql);
			//查詢列表,多個屬性的查詢要使用Object[]來做接收
			List<Object[]> objArrList = query.list();
			for(Object[] objArr: objArrList){
				System.out.println("姓名："+objArr[0]+"   性別："+objArr[1]);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
	
	
}

分頁查詢:

package com.rl.hiber.test.hql;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import com.rl.hiber.model.User;
import com.rl.hiber.utils.HibernateUtils;


public class TestHibernate {
	
	
	
	/**
	 * 分頁查詢
	 */
	@Test
	public void testQuery4(){
		Session session = HibernateUtils.getSession();
		
		try {
			//查詢所有的user類的物件
			//String hql = "from User";
			String hql = "select u from User u";//hql中沒有*的寫法
			
			//根據hql語句建立查詢物件
			Query query = session.createQuery(hql);
			//從前臺傳遞過來的是頁碼PageNo，  startNum = (pageNo - 1)*pageSize
			//設定開始行號
			query.setFirstResult(5);
			//設定每頁記錄數pageSize
			query.setMaxResults(5);
			//查詢列表
			List<User> userList = query.list();
			for(User user: userList){
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
	
	/**
	 * 分頁查詢
	 */
	@Test
	public void testQuery5(){
		Session session = HibernateUtils.getSession();
		try {
			//查詢所有的user類的物件
			//String hql = "from User";
			String hql = "select u from User u";//hql中沒有*的寫法
			//根據hql語句建立查詢物件
			Query query = session.createQuery(hql);
			//從前臺傳遞過來的是頁碼PageNo，  startNum = (pageNo - 1)*pageSize
			
			//查詢列表
			List<User> userList = query.setFirstResult(5)
										.setMaxResults(5)
										.list();
			for(User user: userList){
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
}

限定查詢:

package com.rl.hiber.test.hql;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import com.rl.hiber.model.User;
import com.rl.hiber.utils.HibernateUtils;


public class TestHibernate1 {
	
	
	/**
	 * 限定查詢
	 */
	@Test
	public void testQuery5(){
		Session session = HibernateUtils.getSession();
		try {
		
			String hql = "select u from User u where u.gender = ? and u.uname = ?";//hql中沒有*的寫法
			Query query = session.createQuery(hql);
			//設定第一個引數的值,和JDBC不同，預編譯的索引從0開始
			query.setParameter(0, 2);
			query.setParameter(1, "任亮5");
			List<User> userList = query.list();
			for(User user : userList){
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
	/**
	 * 限定查詢
	 */
	@Test
	public void testQuery6(){
		Session session = HibernateUtils.getSession();
		try {
			//傳參的語法		：[自定義名稱]
			String hql = "select u from User u where u.gender = :gender and u.uname = :uname";//hql中沒有*的寫法
			Query query = session.createQuery(hql);
			//給自定義的引數賦值
			query.setParameter("gender", 1);
			query.setParameter("uname", "任亮8");
			List<User> userList = query.list();
			for(User user : userList){
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
}

統計查詢:

package com.rl.hiber.test.hql;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.Test;

import com.rl.hiber.model.EmpBean;
import com.rl.hiber.utils.HibernateUtils;


public class TestHibernate2 {
	
	
	/**
	 * 統計查詢
	 */
	@Test
	public void testQuery5(){
		Session session = HibernateUtils.getSession();
		try {
			//查詢記錄數
			//String hql = "select count(u.userId) from User u";
			//查詢最大值
			//String hql = "select max(u.userId) from User u";
			//查詢最小值
			//String hql = "select min(u.userId) from User u";
			//查詢平均值
			//String hql = "select avg(u.userId) from User u";
			//統計求和
			String hql = "select sum(u.userId) from User u";
			Query query = session.createQuery(hql);
			//查詢唯一的結果
			Object obj = query.uniqueResult();
			System.out.println(obj);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
	
	@Test
	public void testQuery6(){
		Session session = HibernateUtils.getSession();
		try {
			
			//統計求和
			String hql = "select avg(u.salary), u.gender from User u group by u.gender having avg(u.salary) > 1400";
			Query query = session.createQuery(hql);
			//查詢唯一的結果
			List<Object[]> objArrList = query.list();
			for(Object[] objArr : objArrList){
				System.out.println("平均工資：" + objArr[0]+ "  性別："+objArr[1]);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
	
}

投影查詢:

package com.rl.hiber.test.hql;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.Test;

import com.rl.hiber.model.EmpBean;
import com.rl.hiber.utils.HibernateUtils;


public class TestHibernate2 {

	/**
	 * 投影查詢
	 */
	@Test
	public void testQuery7(){
		Session session = HibernateUtils.getSession();
		try {
			
			//我們可以建立一個業務Bean，在Bean提供有引數的構造器來接收sql返回的值，建立出物件語法 在select後面new com.rl.hiber.model.EmpBean(max(u.salary), u.gender)
			String hql = "select new com.rl.hiber.model.EmpBean(max(u.salary), u.gender) from User u group by u.gender having avg(u.salary) > 1400";
			Query query = session.createQuery(hql);
			//查詢唯一的結果
			List<EmpBean> objArrList = query.list();
			for(EmpBean objArr : objArrList){
				System.out.println(objArr);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
	
}

排序查詢:

package com.rl.hiber.test.hql;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import com.rl.hiber.model.User;
import com.rl.hiber.utils.HibernateUtils;


public class TestHibernate4 {
	
	@Test
	public void testAdd(){
		Session session = HibernateUtils.getSession();
		Transaction tx = session.beginTransaction();
		
		try {
			for(int i = 0; i < 10; i++){
				User user = new User();
				user.setUname("任亮"+i);
				if(i%2 == 0){
					user.setGender(1);
				}else{
					user.setGender(2);
				}
				user.setSalary(1000+ i*100);
				user.setBirthday(new Date());
				session.save(user);
			}
			
			tx.commit();
		} catch (Exception e) {
			e.printStackTrace();
			tx.rollback();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
	
	
	/**
	 * 按著工資排序
	 */
	@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//查詢所有的user類的物件
			//String hql = "from User";
			String hql = "select u from User u order by u.salary desc";//hql中沒有*的寫法
			//根據hql語句建立查詢物件
			Query query = session.createQuery(hql);
			//查詢列表
			List<User> userList = query.list();
			for(User user: userList){
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
		
}

模糊查詢:

package com.rl.hiber.test.hql;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import com.rl.hiber.model.User;
import com.rl.hiber.utils.HibernateUtils;


public class TestHibernate4 {
	
	
	/**
	 * 模糊查詢
	 */
	@Test
	public void testQuery2(){
		Session session = HibernateUtils.getSession();
		try {
			//String hql = "select u from User u where u.uname like '%亮%'";//hql中沒有*的寫法
			//模糊查詢和sql中的語法一致
			String hql = "select u from User u where u.uname like '%亮_'";//hql中沒有*的寫法
			//根據hql語句建立查詢物件
			Query query = session.createQuery(hql);
			//查詢列表
			List<User> userList = query.list();
			for(User user: userList){
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}	
}

提取查詢hql查詢語句到配置檔案中:

User.hbm.xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- 
package：指定當前對映檔案的實體類model所在的包
 -->
<hibernate-mapping package="com.rl.hiber.model">
	<!-- 
		lazy：當前類是否使用延遲載入，預設是true使用，false不使用
	 -->
	<class name="User" table="t_user">
		<!-- id
			是主鍵對映配置
		 -->
		<id name="userId" column="user_id">
			<!-- 
				generator：主鍵的對映策略
			 -->
			<generator class="native"></generator>
		</id>
		<property name="uname"></property>
		<property name="salary"></property>
		<property name="gender"></property>
		<property name="birthday"></property>
	</class>
	<!-- 
		query：定義hql語句
		name:查詢的名字，唯一
	 -->
	<query name="getUserAll">
		<![CDATA[
			from User u where u.salary > :salary
		]]>
	</query>
</hibernate-mapping>

測試程式碼:

package com.rl.hiber.test.hql;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.Test;

import com.rl.hiber.model.User;
import com.rl.hiber.utils.HibernateUtils;


public class TestHibernate5 {
	/**
	 * 限定查詢
	 */
	@Test
	public void testQuery2(){
		Session session = HibernateUtils.getSession();
		try {
			//從對映檔案中獲得hql語句建立query物件
			Query query = session.getNamedQuery("getUserAll");
			query.setParameter("salary", 1500);
			//查詢列表
			List<User> userList = query.list();
			for(User user: userList){
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}
}

Hibernate框架簡介④
2018-08-11
框架
Hibernate框架簡介③
2018-08-10
框架
Hibernate框架簡介②
2018-08-10
框架
Hibernate框架簡介①
2018-08-09
框架
Hibernate資料校驗簡介
2021-11-25
Scrapy框架簡介
2019-01-06
框架
HTML 框架簡介
2022-09-26
HTML框架
Flask 框架簡介
2017-09-23
Flask框架
Django框架簡介
2018-01-07
Django框架
graphicsview框架簡介
2013-07-24
View框架
Spring+Struts+Hibernate 簡介(轉)
2015-11-23
Spring
Spring框架簡介⑩
2018-08-09
Spring框架
Spring框架簡介⑨
2018-08-08
Spring框架
Spring框架簡介⑧
2018-08-08
Spring框架
SpringMVC框架簡介②
2018-08-12
SpringMVC框架
SpringMVC框架簡介①
2018-08-12
SpringMVC框架
[轉]SSH框架簡介
2018-09-12
框架
Spring框架簡介⑦
2018-08-07
Spring框架
Spring框架簡介⑥
2018-08-07
Spring框架
Spring框架簡介⑤
2018-08-07
Spring框架
Spring框架簡介④
2018-08-07
Spring框架
Spring框架簡介③
2018-08-07
Spring框架
Spring框架簡介②
2018-08-07
Spring框架
Spring框架簡介①
2018-08-06
Spring框架
[原]ZolltyMVC框架簡介
2013-11-29
MVC框架
【SSH】--SSH框架簡介
2015-10-23
框架
wsgiref模組、web框架、django框架簡介
2022-05-10
Web框架Django
spring框架——Spring框架簡介
2017-06-28
Spring框架
hibernate一二級快取簡單介紹
2014-04-01
快取
Hibernate框架
2020-04-20
框架
Flutter路由框架Fluro簡介
2020-01-17
Flutter路由框架
uni-app 框架簡介
2022-06-13
APP框架
關於Struts框架簡介
2014-07-07
框架
Hibernate 框架理解
2017-10-31
框架
Java集合框架系列教程一：集合框架簡介
2013-01-22
Java框架
OSX 攻擊框架Empyre簡介
2020-08-19
框架
大資料框架原理簡介
2020-12-30
大資料框架
前端框架 Quasar-Framework 簡介
2018-03-18
前端框架Framework

Hibernate框架簡介⑤

相關文章