Hibernate hql

HQL与sql相似语法

[select/delete/update…][from…][where…][group by…][having…][order by…]

 

 

代码结构和使用的jar包

 

 

Man.java

package com.orange.demo;

public class Man {
	private Integer id;
	private String name;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

}

Test.java

package com.orange.demo;

import java.util.Arrays;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;


public class Test {
	
	private static SessionFactory sessionFactory = null;
	static{
		Configuration cfg = new Configuration();
		cfg.configure("hibernate.cfg.xml");		//在src目录下
		sessionFactory = cfg.buildSessionFactory();
	}
	
	public static void main(String[] args) {
		try {
//			save();
//			query1();
//			query2();
//			query3(10);
//			query4();
//			query5();
//			query6();
//			query7();
//			delete();
			update();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 增加测试数据
	 */
	public static void save() throws Exception {
		Session session = sessionFactory.openSession();
		Transaction tx =  session.beginTransaction();
		Man man = null;
		for(int i = 1;i<=10;i++){
			man = new Man();
			man.setName("大橙子_"+i);
			session.save(man);
		}

		tx.commit();
		session.close();
	
	}
	

	/**
	 * 查询表的所有字段(查询对象的所有属性)
	 */

	public static void query1() throws Exception {
		Session session = sessionFactory.openSession();
		//hql 查询的都是类名和类的属性
		String hql = "from Man where id > 5 order by id desc";
		List<Man> list = session.createQuery(hql).list();
		for(Man m : list){
			System.out.println(m.getId()+"---"+m.getName());
		}
		session.close();
	}
	/**
	 * 返回表的多个列
	 */
	public static void query2() throws Exception {
		Session session = sessionFactory.openSession();
		//hql 查询的都是类名和类的属性
		String hql = "select id,name from Man where id < 5 ";
		
		List list = session.createQuery(hql).list();
		for(Object obj : list){
			//返回多个列时要将返回的结构转成数组，数组的顺序和hql查询的顺序一致
			System.out.println(Arrays.toString((Object[])obj) );
		}
		session.close();
	}
	
	/**
	 * 返回一个一条数据，并且使用占位符
	 */
	public static void query3(int id) throws Exception {
		Session session = sessionFactory.openSession();
		String hql = "from Man where id = ? ";
		//uniqueResult方法返回一条数据，返回多条会报错
		Man man = (Man) session.createQuery(hql).setParameter(0, id).uniqueResult();
		System.out.println(man.getName());
		session.close();
	}
	
	/**
	 * 指定hql参数的名称
	 */
	public static void query4() throws Exception {
		Session session = sessionFactory.openSession();
		String hql = "from Man  where id between :idMin and :idMax ";
		List<Man> list = session.createQuery(hql).setParameter("idMin", 3).setParameter("idMax", 5).list();
		for(Man m : list){
			System.out.println(m.getId()+"---"+m.getName());
		}
		session.close();
	}
	
	/**
	 *分页查询
	 */
	
	public static void query5() throws Exception {
		Session session = sessionFactory.openSession();
		String hql = "FROM Man";
		Query query = session.createQuery(hql);
		query.setFirstResult(5);	//开始记录数
		query.setMaxResults(3);		//查询的条数	
		List<Man> list = query.list();
		for(Man m : list){
			System.out.println(m.getId()+"---"+m.getName());
		}
		session.close();
	}
	/**
	 * 从配置文件Man.hbm.xml获取hql，
	 * 聚合查询 count
	 */
	public static void query6() throws Exception {
		Session session = sessionFactory.openSession();
		//uniqueResult方法返回一条数据，返回多条会报错
		Long count =  (Long)session.getNamedQuery("countquery").uniqueResult();
		System.out.println(count);
		session.close();
	}
	
	/**
	 *  in 查询
	 */
	
	public static void query7() throws Exception {
		Session session = sessionFactory.openSession();
		//uniqueResult方法返回一条数据，返回多条会报错
		Query query = session.getNamedQuery("inquery").setParameterList("id", new Object[] { 3,6,9});
		List<Man> list = query.list();
		for(Man m : list){
			System.out.println(m.getId()+"---"+m.getName());
		}
		session.close();
	}

	public static void delete() throws Exception {
		Session session = sessionFactory.openSession();
		Transaction tx = session.beginTransaction();
		String hql = "DELETE FROM Man t WHERE t.id = 3 or t.id >8";
		int result = session.createQuery(hql).executeUpdate();
		System.out.println("删除 " + result + "行数据");
		tx.commit();
		session.close();
	}
	
	
	public static void update() throws Exception {
		
		Session session = sessionFactory.openSession();
		Transaction tx = session.beginTransaction();
		String hql_select = "from Man where id = ? ";
		String hql_update = "update Man t set name = ? WHERE t.id =2";
		Man man = null;
		
		//查询id=2的
		man = (Man) session.createQuery(hql_select).setParameter(0, 2).uniqueResult();
		System.out.println(man.getName());
		
		//修改id=2的name
		int result = session.createQuery(hql_update).setParameter(0, "orange33444").executeUpdate();
		System.out.println("更新 " + result + "行数据");
		
		session.refresh(man);	//刷新session缓存
		//再次查询id=2的
		man = (Man) session.createQuery(hql_select).setParameter(0, 2).uniqueResult();
		System.out.println(man.getName());
		
		tx.commit();
		session.close();
	}
}

update方法中如果没有session.refresh(man)这句，更新前和更新后的name值是一样的，这是因为session缓存的缘故，要想查看更新后的值，要在第二次查询前加session.refresh(man)来刷新session缓存

Man.hbm.xml

<?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="com.orange.demo" auto-import="true">
	<class name="Man" table="t_man">
		<id name="id" column="t_id">
			<generator class="native"></generator>
		</id>
		<property name="name" column="t_name" type="string"></property>
	</class>
	
	
	<query name="countquery">
			SELECT COUNT(*) FROM Man
	</query>
	<query name="inquery">
		
		<![CDATA[
		 FROM Man t where t.id in (:id) 
		]]>
	</query>
</hibernate-mapping>

设置了auto-import="true"后在写hql时直接from 类名即可，如果设置auto-import="false"，写hql时from后要写类的包名加类名，如com.orange.demo.Man

hql中可能会写 '<' 或'>' ，这样会被认为是xml的左或右尖括号，所以需要转义，这种情况将hql写在CDATA中就可以了

hibernate.cfg.xml

<!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 name="foo">
			<!-- 数据库方言 --> 
		<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
		<property name="connection.url">jdbc:mysql://localhost:3306/t1?characterEncoding=UTF-8</property>
		<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
		<property name="connection.username">root</property>
		<property name="hibernate.connection.password">123456</property>
		<!-- 在控制台打印SQL，但不能打印建表语句 -->
		<property name="hibernate.show_sql">true</property>
		<!-- 打印SQL是否格式化 false：不格式化 -->
		<property name="hibernate.format_sql">false</property>
		<!--自动更新数据库结构 -->
		<property name="hbm2ddl.auto">update</property>
		
		<mapping resource="com/orange/demo/Man.hbm.xml"/>
	
	</session-factory>
</hibernate-configuration>

hql还支持多表连接和group by查询这里就不演示了。