Hibernate之三种查询方式和批处理

hibernate的三种查询方式、原生sql、hql、Criteria(标准查询)

三种查询方式各有特点、个人对三种方式的一个理解

原生sql：需要手动书写sql语句，在sql优化上比其他两种方式要灵活，毕竟自己写sql

hql：hibernate面向对象的一种查询方式，跟数据库中表和字段不直接挂钩，跟实体类和其属性相关，省去了书写sql语句。操作更方便简单

Criteria：该方式个人理解是在hql方式上的进一步包装，将hql中需要手动写的条件查询，进行了进一步封装成各种方法，需要进行条件查询时，可直接调用其相应方法即可、更加方便

package com.dao;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import com.bean.Emplyoee;

public class TestAnnotations {
	static SessionFactory sessionFactory;

	static {
		Configuration configuration = new Configuration();
		configuration.configure();
		sessionFactory = configuration.buildSessionFactory();
	}

	public static void main(String[] args) {
		TestAnnotations testAnnotations = new TestAnnotations();
		/*
		 * 向表中循环添加数据 ：for (int i = 1; i < 19; i++) { Emplyoee work = new
		 * Emplyoee(); work.setMoney(i * 1000 + i * 100 + i * 10 + i);
		 * work.setName("张半仙" + i); testAnnotations.add(work); }
		 */
		/*
		 * List<Emplyoee> list = testAnnotations.select(); for (Emplyoee e :
		 * list) { System.out.println(e);
		 * 
		 * }
		 */
		// testAnnotations.select(0);
		// int update = testAnnotations.update(5, "老子");
		// testAnnotations.criteriaSelect();
		// testAnnotations.batchAdd();
		// testAnnotations.sqlSelect();
		testAnnotations.sqlDelete(50);
	}

	/*
	 * 批处理添加
	 */
	public void batchAdd() {
		Session session = sessionFactory.openSession();
		Transaction transaction = session.beginTransaction();
		try {
			for (int i = 0; i < 100; i++) {
				Emplyoee e = new Emplyoee();
				e.setName("我是" + i + "号");
				e.setMoney(i * 10);
				session.save(e);
				if (i % 30 == 0) {
					session.flush();
					session.clear();
				}
			}
			transaction.commit();
		} catch (Exception e) {
			if (transaction != null)
				transaction.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
	}

	/*
	 * 第一种查询方式：标准查询
	 */
	public void criteriaSelect() {
		Session openSession = sessionFactory.openSession();
		Transaction ts = openSession.beginTransaction();
		Criteria criteria = openSession.createCriteria(Emplyoee.class);
		// criteria.add(Restrictions.eq("money", 6666));
		// criteria.add(Restrictions.gt("money", 6666));
		// criteria.add(Restrictions.between("money", 6666, 9999));
		Criterion c1 = Restrictions.lt("money", 3333);
		Criterion c2 = Restrictions.gt("money", 13333);
		// criteria.add(Restrictions.or(c1, c2));
		// criteria.setProjection(Projections.rowCount());
		criteria.setProjection(Projections.avg("money"));
		List list = criteria.list();
		System.out.println(list);
	}

	/*
	 * 第三种查询方式：原生sql查询
	 */
	public void sqlSelect() {
		Session openSession = sessionFactory.openSession();
		Transaction ts = openSession.beginTransaction();
		// sql语句
		String sql = "select e.id,e.name,e.money from emplyoee e";
		SQLQuery sqlQuery = openSession.createSQLQuery(sql);
		// 设置对象类型
		sqlQuery.addEntity(Emplyoee.class);
		List list = sqlQuery.list();
		System.out.println(list);
	}

	/*
	 * 原生sql删除语句>>delete
	 */
	public void sqlDelete(int id) {
		Session openSession = sessionFactory.openSession();
		Transaction ts = openSession.beginTransaction();
		// sql语句
		try {
			String sql = "delete from emplyoee where id>" + id;
			SQLQuery sqlQuery = openSession.createSQLQuery(sql);
			int update = sqlQuery.executeUpdate();
			ts.commit();
			System.out.println(update);
		} catch (Exception e) {
			if (ts != null)
				ts.rollback();
			e.printStackTrace();
		} finally {
			openSession.close();
		}

	}

	/*
	 * 第二种查询方式：hql查询
	 */
	public int add(Emplyoee work) {
		Session openSession = sessionFactory.openSession();
		Transaction ts = openSession.beginTransaction();
		int result = -2;
		try {
			result = (int) openSession.save(work);
			ts.commit();
		} catch (Exception e) {
			ts.rollback();
			e.printStackTrace();
		} finally {
			openSession.close();
		}
		return result;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List select() {
		Session session = sessionFactory.openSession();
		Transaction transaction = session.beginTransaction();
		// Query createQuery = session.createQuery("FROM Emplyoee e where e.id
		// =:id");
		// createQuery.setParameter("id", id);
		Query createQuery = session.createQuery("From Emplyoee e ORDER BY e.money desc");

		createQuery.setFirstResult(0);
		createQuery.setMaxResults(10);
		List list = createQuery.list();
		session.close();
		return list;
	}

	/*
	 * 更新方法
	 */
	public int update(int id, String name) {
		Session session = sessionFactory.openSession();
		Transaction transaction = session.beginTransaction();
		int executeUpdate = -1;
		try {
			/*
			 * 数据更新操作
			 */
			String hql = "update Emplyoee e set e.name=:name where e.id=:id";
			Query query = session.createQuery(hql);
			query.setParameter("name", name);
			query.setParameter("id", id);
			executeUpdate = query.executeUpdate();
			transaction.commit();
		} catch (Exception e) {
			// TODO: handle exception
			transaction.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		return executeUpdate;

	}

	/*
	 * 根据条件查询单条记录
	 */
	public Object select(int id) {
		Session session = sessionFactory.openSession();
		Transaction transaction = session.beginTransaction();
		String hql = "select sum(e.money) from Emplyoee e group by e.name";
		// String hql = "FROM Emplyoee e where e.id=:id";
		// Query k = createQuery.setParameter("id", id);
		Query createQuery = session.createQuery(hql);

		List list = createQuery.list();
		session.close();
		System.out.println(list);
		return list.get(0);
	}
}