HQL查询

maven配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.reige</groupId>
    <artifactId>HQLLearn</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!-- 添加Hibernate依赖 -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>3.6.10.Final</version>
        </dependency>
        <!-- mysql数据库的驱动包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-nop</artifactId>
            <version>1.7.5</version>
        </dependency>
        <dependency>
            <groupId>javassist</groupId>
            <artifactId>javassist</artifactId>
            <version>3.12.0.GA</version>
        </dependency>
    </dependencies>
<!--在pom.xml中显式地告诉Maven把什么资源文件复制到target/classes文件夹下。 -->
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
            </resource>
        </resources>
    </build>

</project>

创建Test测试类

初始化session

public class SellerTest {
    private Session session = null;

    @Before
    public void setUp() throws Exception {
        session = HibernateSessionFactory.getCurrentSession();
    }

    @After
    public void tearDown() throws Exception {
        session.close();
    }

}

select

查询某个表

• from Seller s

    @Test
    public void testFromClause(){
        String hql = " from Seller  s ";
        String h = "from Seller s";

        Query query = session.createQuery(hql);

        List<Seller> sellers = query.list();
        for(Seller seller : sellers){
            System.out.println("name:"+seller.getName());
        }
    }

查询某一列

• select s.name from Seller s
• 注： 如查询多列 List<Object[]> list = query.list();

    @Test
    public void testSelectClauseObjectArray(){
        String hql = " select s.name from Seller s ";

        Query query = session.createQuery(hql);

        List<Object> list = query.list();

        for(Object obj : list){
            System.out.println("name:"+obj);
        }
    }

以list返回查询结果

• select new list(s.name,s.tel,s.address) from Seller s

    @Test
    public void testSelectClauseList(){
        String hql = " select new list(s.name,s.tel,s.address) from Seller s ";

        Query query = session.createQuery(hql);
        List<List> lists = query.list();

        for(List list : lists){
            System.out.println("name : "+list.get(0));
            System.out.println("tel:"+list.get(1));
            System.out.println("address:"+list.get(2));
        }
    }

以map返回查询结果

• select new map(s.name,s.tel,s.address) from Seller s

    @Test
    public void testSelectClauseMap(){
        String hql = " select new map(s.name,s.tel,s.address) from Seller s ";
        Query query =session.createQuery(hql);

        List<Map> maps = query.list();
        for(Map map : maps){
            System.out.println("name:"+map.get("0"));
            System.out.println("tel:"+map.get("1"));
            System.out.println("address:"+map.get("2"));
        }
    }

以对象返回查询结果

• select new Seller(s.name,s.tel,s.address) from Seller s

    @Test
    public void testSelectClauseSelf(){
        String hql = " select new Seller(s.name,s.tel,s.address) from Seller s ";
        Query query = session.createQuery(hql);
        List<Seller> sellers = query.list();

        for(Seller seller : sellers){
            System.out.println("name: "+seller.getName());
            System.out.println("tel:"+seller.getTel());
            System.out.println("address:"+seller.getAddress());
        }
    }

where

查询Commodity 表中price小于200

• from Commodity c where c.price<=200

    @Test
    public void testWhere1(){
        String hql = " from Commodity c where c.price<=200 ";
        Query query = session.createQuery(hql);
        List<Commodity> commodities = query.list();

        for(Commodity c : commodities){
            System.out.println("name:"+c.getName());
            System.out.println("price:"+c.getPrice());
        }
    }

between and 范围运算

• from Commodity c where c.price between 1000 and 5000 and c.category like ‘%电脑%’ or name like ‘%电脑%’

    @Test
    public void testWhere2(){
        String hql  = " from Commodity c where c.price between 1000 and 5000 and c.category like '%电脑%' or name like '%电脑%' ";
        Query query = session.createQuery(hql);
        List<Commodity> commodities = query.list();

        for(Commodity c : commodities){
            System.out.println("name:"+c.getName());
            System.out.println("category:"+c.getCategory());
            System.out.println("price:"+c.getPrice());
        }
    }

查询Commodity price 小于两百或者大于3000

• from Commodity c where c.price<200 or c.price>3000

    @Test
    public void testWhere3(){
        String hql = " from Commodity c where c.price<200 or c.price>3000 ";
        Query query = session.createQuery(hql);
        List<Commodity> commodities = query.list();

        for(Commodity c : commodities){
            System.out.println("name:"+c.getName());
            System.out.println("price:"+c.getPrice());
        }
    }

四则运算

• from Commodity c where c.price*5>3000

    @Test
    public void testWhere4(){
        String hql = " from Commodity c where c.price*5>3000 ";
        Query query = session.createQuery(hql);
        List<Commodity> commodities = query.list();

        for(Commodity c : commodities){
            System.out.println("name:"+c.getName());
            System.out.println("price:"+c.getPrice()*5);
        }
    }

order by

• from Commodity order by seller.id asc,price desc,name asc

    @Test
    public void testOrderby(){
        String hql = " from Commodity order by seller.id asc,price desc,name asc  ";
        Query query = session.createQuery(hql);
        List<Commodity> commodities = query.list();

        for(Commodity c : commodities){
            System.out.println("name:"+c.getName());
            System.out.println("price:"+c.getPrice());
        }
    }

is not empty

• from Order o where o.orderItems is not empty

    @Test
    public void testWhere1(){
        String hql = " from Order o where o.orderItems is not empty ";
        Query query = session.createQuery(hql);
        List<Order> orders = query.list();

        for(Order order : orders){
            System.out.println(order.getCustomer().getName());
            System.out.println(order.getAmount());
            System.out.println(order.getTradeDate());
        }
    }