package com.gordon.test;

import java.text.DecimalFormat;

import java.util.Arrays;

import java.util.List;

import org.hibernate.Session;

import org.hibernate.Transaction;

import org.junit.Test;

import com.gordon.domain.Book;

import com.gordon.utils.HibernateUtil;

public class TestDemo1 {

	/**

	 * 分组查询

	 * 查询结果：

		Hibernate:

		    select

		        publisher1_.name as col_0_0_,

		        count(*) as col_1_0_

		    from

		        t_book book0_,

		        t_publisher publisher1_

		    where

		        book0_.publisher_id=publisher1_.id

		    group by

		        book0_.publisher_id

		[电子工业出版社, 3]

		[北京大学出版社, 2]

		[人民邮电出版社, 2]

	 */

	@Test

	public void run5() {

		Session session = HibernateUtil.getCurrentSession();

		Transaction transaction = session.beginTransaction();

		List<Object[]> list = session.createQuery("select b.publisher.name, count(*) from Book b group by b.publisher").list();

		for (Object[] book : list) {

			System.out.println(Arrays.toString(book));

		}

		transaction.commit();

	}

	/**

	 * 聚合函数

	 * 查询结果：

		Hibernate:

		    select

		        count(*) as col_0_0_

		    from

		        t_book book0_

		Hibernate:

		    select

		        sum(book0_.price) as col_0_0_

		    from

		        t_book book0_

		Hibernate:

		    select

		        max(book0_.price) as col_0_0_,

		        min(book0_.price) as col_1_0_

		    from

		        t_book book0_

		Hibernate:

		    select

		        avg(book0_.price) as col_0_0_

		    from

		        t_book book0_

		count: 7

		sum: 330.04

		Max:70.00 Min:31.00

		avg: 47.15

	 */

	@Test

	public void run4() {

		Session session = HibernateUtil.getCurrentSession();

		Transaction transaction = session.beginTransaction();

		String count = "select count(*) from Book";

		Long c = (Long) session.createQuery(count).uniqueResult();

		String sum = "select sum(b.price) from Book b";

		Double s = (Double) session.createQuery(sum).uniqueResult();

		String maxmin = "select max(b.price), min(b.price) from Book b";

		Object[] mm = (Object[]) session.createQuery(maxmin).uniqueResult();

		String avg = "select avg(b.price) from Book b";

		Double a = (Double) session.createQuery(avg).uniqueResult();

		DecimalFormat df = new DecimalFormat("00.00");

		System.out.println("count: " + c);

		System.out.println("sum: " + df.format(s));

		System.out.println("Max:" + df.format(mm[0]) + " Min:" + df.format(mm[1]));

		System.out.println("avg: " + df.format(a));

		transaction.commit();

	}

	/**

	 * 检索图书对象的部分属性

	 * 查询结果

		Hibernate:

		    select

		        book0_.name as col_0_0_,

		        book0_.price as col_1_0_

		    from

		        t_book book0_

		    order by

		        book0_.price desc

		[云计算技术及性能优化, 70.0]

		[架构探险：轻量级微服务架构（下册）, 63.2]

		[中国冰雪梦, 54.0]

		[生产微服务 , 44.0]

		[VisualBasic2015实践教程 , 36.0]

		[Photoshop图形图像处理, 31.84]

		[C语言程序设计, 31.0]

	 */

	@Test

	public void run3() {

		Session session = HibernateUtil.getCurrentSession();

		Transaction transaction = session.beginTransaction();

		List<Object[]> list = session.createQuery("select b.name, b.price from Book b order by b.price desc").list();

		for (Object[] book : list) {

			System.out.println(Arrays.toString(book));

		}

		transaction.commit();

	}

	/**

	 * 对查询结果进行排序

	 * 查询结果：

		Hibernate:

		    select

		        book0_.id as id1_0_,

		        book0_.name as name2_0_,

		        book0_.price as price3_0_,

		        book0_.publisher_id as publishe4_0_

		    from

		        t_book book0_

		    order by

		        book0_.price desc

		70.0, 云计算技术及性能优化

		63.2, 架构探险：轻量级微服务架构（下册）

		54.0, 中国冰雪梦

		44.0, 生产微服务

		36.0, VisualBasic2015实践教程

		31.84, Photoshop图形图像处理

		31.0, C语言程序设计

	 */

	@Test

	public void run2() {

		Session session = HibernateUtil.getCurrentSession();

		Transaction transaction = session.beginTransaction();

		List<Book> list = session.createQuery("from Book b order by b.price desc").list();

		for (Book book : list) {

			System.out.println(book.getPrice() + ", " + book.getName());

		}

		transaction.commit();

	}

	/**

	 * 查询所有图书

	 * 查询结果：

		Hibernate:

		    select

		        book0_.id as id1_0_,

		        book0_.name as name2_0_,

		        book0_.price as price3_0_,

		        book0_.publisher_id as publishe4_0_

		    from

		        t_book book0_

		云计算技术及性能优化

		C语言程序设计

		中国冰雪梦

		Photoshop图形图像处理

		VisualBasic2015实践教程

		生产微服务

		架构探险：轻量级微服务架构（下册）

	 */

	@Test

	public void run1() {

		Session session = HibernateUtil.getCurrentSession();

		Transaction transaction = session.beginTransaction();

		List<Book> list = session.createQuery("from Book").list();

		for (Book book : list) {

			System.out.println(book.getName());

		}

		transaction.commit();

	}

}