HQL数据查询基础（一）

<span style="font-size:18px;">/*# MySQL-Front 5.1  (Build 3.80)*/

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;


/*# Host: 127.0.0.1    Database: webshop
# ------------------------------------------------------
# Server version 5.1.36-community*/

DROP DATABASE IF EXISTS `webshop`;
CREATE DATABASE `webshop` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `webshop`;

/*#
# Source for table commodity
#*/

DROP TABLE IF EXISTS `commodity`;
CREATE TABLE `commodity` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `price` double(11,2) DEFAULT NULL,
  `unit` varchar(50) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `description` varchar(1000) DEFAULT NULL,
  `seller` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

/*#
# Dumping data for table commodity
#*/

LOCK TABLES `commodity` WRITE;
/*!40000 ALTER TABLE `commodity` DISABLE KEYS */;
INSERT INTO `commodity` VALUES (1,'中式童装',120,'套','童装','中式童装',1);
INSERT INTO `commodity` VALUES (2,'女士套装',200,'套','女装','女士职业套装',1);
INSERT INTO `commodity` VALUES (3,'男士西服',200,'套','男装','男士西服套装',1);
INSERT INTO `commodity` VALUES (4,'笔记本电脑',4000,'台','电脑','双核笔记本电脑',2);
INSERT INTO `commodity` VALUES (5,'移动硬盘',400,'块','电脑周边','1t移动硬盘',2);
INSERT INTO `commodity` VALUES (6,'液晶电视',5000,'台','电视','4k高清液晶电视',3);
INSERT INTO `commodity` VALUES (7,'滚筒洗衣机',4000,'台','洗衣机','滚筒洗衣机',3);
INSERT INTO `commodity` VALUES (8,'《hibernate编程》',30,'本','实体书','介绍hibernate编程',4);
INSERT INTO `commodity` VALUES (9,'《Java核心》',50,'本','实体书','介绍Java编程核心',4);
INSERT INTO `commodity` VALUES (10,'《海底两万里》',40,'本','电子书','经典科幻小说',4);
INSERT INTO `commodity` VALUES (11,'优盘',30,'个','电脑周边','16G优盘',2);
/*!40000 ALTER TABLE `commodity` ENABLE KEYS */;
UNLOCK TABLES;

/*#
# Source for table customer
#*/

DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `tel` varchar(50) DEFAULT NULL,
  `address` varchar(1000) DEFAULT NULL,
  `email` varchar(300) DEFAULT NULL,
  `sex` varchar(30) DEFAULT NULL,
  `description` varchar(4000) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*#
# Dumping data for table customer
#*/

LOCK TABLES `customer` WRITE;
/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
INSERT INTO `customer` VALUES (1,'张三','13800000000','中国上海××区××路','13800000000@138.com','男','热爱编程的程序员',25,'1990-01-01');
INSERT INTO `customer` VALUES (2,'李四','13888888888','中国北京××区××路','13888888888@138.com','女','酷爱网购的白领',20,'1995-02-21');
INSERT INTO `customer` VALUES (3,'王五','15888888888','中国深圳××区××路','15888888888@158.com','男','这个家伙很懒，什么也没有留下',35,'1980-04-14');
INSERT INTO `customer` VALUES (4,'赵六','13900000000',NULL,NULL,'男',NULL,40,'1975-01-01');
/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
UNLOCK TABLES;

/*#
# Source for table orderform
#*/

DROP TABLE IF EXISTS `orderform`;
CREATE TABLE `orderform` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `customer` int(11) DEFAULT NULL,
  `tradedate` date DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `amount` double(11,2) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*#
# Dumping data for table orderform
#*/

LOCK TABLES `orderform` WRITE;
/*!40000 ALTER TABLE `orderform` DISABLE KEYS */;
INSERT INTO `orderform` VALUES (1,1,'2015-04-30','已收货',4400);
INSERT INTO `orderform` VALUES (2,2,'2015-05-11','已发货',520);
INSERT INTO `orderform` VALUES (3,3,'2015-05-13','已付款',9120);
/*!40000 ALTER TABLE `orderform` ENABLE KEYS */;
UNLOCK TABLES;

/*#
# Source for table orderitem
#*/

DROP TABLE IF EXISTS `orderitem`;
CREATE TABLE `orderitem` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ORDERID` int(11) DEFAULT NULL,
  `COMMODITY` int(11) DEFAULT NULL,
  `DISCOUNT` double(11,2) DEFAULT NULL,
  `ACTPRICE` double(11,2) DEFAULT NULL,
  `AMOUNT` double(11,2) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

/*#
# Dumping data for table orderitem
#*/

LOCK TABLES `orderitem` WRITE;
/*!40000 ALTER TABLE `orderitem` DISABLE KEYS */;
INSERT INTO `orderitem` VALUES (1,1,4,1,4000,1);
INSERT INTO `orderitem` VALUES (2,1,5,1,400,1);
INSERT INTO `orderitem` VALUES (3,2,1,1,120,1);
INSERT INTO `orderitem` VALUES (4,2,2,1,200,1);
INSERT INTO `orderitem` VALUES (5,2,3,1,200,1);
INSERT INTO `orderitem` VALUES (6,3,6,1,5000,1);
INSERT INTO `orderitem` VALUES (7,3,7,1,4000,1);
INSERT INTO `orderitem` VALUES (8,3,8,1,30,1);
INSERT INTO `orderitem` VALUES (9,3,9,1,50,1);
INSERT INTO `orderitem` VALUES (10,3,10,1,40,1);
/*!40000 ALTER TABLE `orderitem` ENABLE KEYS */;
UNLOCK TABLES;

/*#
# Source for table seller
#*/

DROP TABLE IF EXISTS `seller`;
CREATE TABLE `seller` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `tel` varchar(1000) DEFAULT NULL,
  `address` varchar(2000) DEFAULT NULL,
  `website` varchar(500) DEFAULT NULL,
  `star` int(11) DEFAULT NULL,
  `business` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*#
# Dumping data for table seller
#*/

LOCK TABLES `seller` WRITE;
/*!40000 ALTER TABLE `seller` DISABLE KEYS */;
INSERT INTO `seller` VALUES (1,'A服装店','13000000000','中国北京××区','www.a.com',5,'经营各式服装');
INSERT INTO `seller` VALUES (2,'B数码店','15800000000','中国浙江杭州市××区','www.b.com',4,'经营各类数码电子产品');
INSERT INTO `seller` VALUES (3,'C电器店','13012341234','中国广东深圳市××区','www.c.com',4,'经营各类家电');
INSERT INTO `seller` VALUES (4,'D书店','18600000000','中国陕西西安市××区','www.d.com',5,'经营各类实体书与电子书');
/*!40000 ALTER TABLE `seller` ENABLE KEYS */;
UNLOCK TABLES;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
</span>

<span style="font-size:18px;">package com.imooc.util;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

/**
 * Created by DreamBoy on 2016/5/19.
 */

/**
 * 通过这个Hibernate初始化类，就可以有效地管理Session，避免了Session的多线程共享数据问题。
 */
public class HibernateUtil {
    private static final ThreadLocal<Session> threadLocal = new ThreadLocal<Session>();
    private static SessionFactory sessionFactory = null; //SessionFactory对象

    static {
        try {
            Configuration cfg = new Configuration().configure(); //加载Hibernate配置文件
            //创建服务注册对象
            ServiceRegistry serviceRegistry = new ServiceRegistryBuilder().applySettings(cfg.getProperties()).buildServiceRegistry();
            //创建会话工厂对象
            sessionFactory = cfg.buildSessionFactory(serviceRegistry);
        } catch (Exception e) {
            System.err.println("创建会话工厂失败");
            e.printStackTrace();
        }
    }

    //获取session
    public static Session getSession() throws HibernateException {
        Session session = threadLocal.get();
        if(session == null || !session.isOpen()) {
            if(sessionFactory == null) {
                rebuildSessionFactory();
            }

            session = (sessionFactory != null) ? sessionFactory.openSession() : null;
            threadLocal.set(session);
        }
        return session;
    }

    /**
     * 重建会话工厂
     */
    public static void rebuildSessionFactory() {
        try {
            Configuration cfg = new Configuration().configure(); //加载Hibernate配置文件
            //创建服务注册对象
            ServiceRegistry serviceRegistry = new ServiceRegistryBuilder().applySettings(cfg.getProperties()).buildServiceRegistry();
            //创建会话工厂对象
            sessionFactory = cfg.buildSessionFactory(serviceRegistry);
        } catch (Exception e) {
            System.err.println("创建会话工厂失败");
            e.printStackTrace();
        }
    }

    //获取SessionFactory对象
    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }
    //关闭Session
    public static void closeSession() throws HibernateException {
        Session session = threadLocal.get();
        threadLocal.set(null);
        if(session != null) {
            session.close(); //关闭session
        }
    }
}
</span>

<span style="font-size:18px;">package com.imooc.model;

/**
 * Created by DreamBoy on 2016/5/19.
 */

import java.io.Serializable;

/**
 * 商家信息持久化类
 */
public class Seller implements Serializable {
    private long id; //主键
    private String name; //名称
    private String tel; //电话
    private String address; //地址
    private String website; //网站
    private Integer star; //星级
    private String business; //经营范围

    public long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getWebsite() {
        return website;
    }

    public void setWebsite(String website) {
        this.website = website;
    }

    public Integer getStar() {
        return star;
    }

    public void setStar(Integer star) {
        this.star = star;
    }

    public String getBusiness() {
        return business;
    }

    public void setBusiness(String business) {
        this.business = business;
    }

    @Override
    public String toString() {
        return "主键：" + this.getId() +
                " | 名称：" + this.getName() +
                " | 电话：" + this.getTel() +
                " | 星级：" + this.getStar() +
                " | 网址：" + this.getWebsite() +
                " | 地址：" + this.getAddress() +
                " | 经营范围：" + this.getBusiness();
    }
}
</span>

<span style="font-size:18px;"><?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>

    <class name="com.imooc.model.Seller" table="SELLER">
        <id name="id" type="java.lang.Long">
            <column name="ID"/>
            <generator class="increment"/>
        </id>
        <property name="name" type="java.lang.String">
            <column name="NAME"/>
        </property>
        <property name="tel" type="java.lang.String">
            <column name="TEL"/>
        </property>
        <property name="address" type="java.lang.String">
            <column name="ADDRESS"/>
        </property>
        <property name="website" type="java.lang.String">
            <column name="WEBSITE"/>
        </property>
        <property name="star" type="java.lang.Integer">
            <column name="STAR"/>
        </property>
        <property name="business" type="java.lang.String">
            <column name="BUSINESS"/>
        </property>
    </class>
</hibernate-mapping></span>

<span style="font-size:18px;"><?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD//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"></property>
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql:///webshop?useUnicode=true&characterEncoding=UTF-8</property>
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

        <property name="show_sql">true</property>
        <property name="format_sql">true</property>
        <property name="hbm2ddl.auto">update</property>

        <mapping resource="com/imooc/model/Seller.hbm.xml"/>
        <!-- DB schema will be updated if needed -->
        <!-- <property name="hbm2ddl.auto">update</property> -->
    </session-factory>
</hibernate-configuration></span>

<span style="font-size:18px;">package com.imooc.model;

import com.imooc.util.HibernateUtil;
import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

/**
 * Created by DreamBoy on 2016/5/19.
 */
public class SellerTest {
    private Session session = null;

    @Before
    public void setUp() throws Exception {
        session = HibernateUtil.getSession();
    }

    @After
    public void tearDown() throws Exception {
        HibernateUtil.closeSession();
    }

    @Test
    public void testSeller() {
        String hql = " from Seller ";
        //创建Query实例对象
        Query query = session.createQuery(hql);
        List<Seller> sellers = query.list();

        for(Seller seller : sellers) {
            System.out.println(seller);
        }
    }
}
</span>