Hibernate查询的几种总结。

一，HQL查询：

配置基本信息，不会的可以看我之前的文章

链接：https://blog.csdn.net/chlei_/article/details/100042012

1.基本索引

 数据库表格：

主要代码：

Query query = session.createQuery("from Customer");
List<Customer> list = query.list();
System.out.println(list);

结果：

2.排序索引

主要代码：

            Query query = session.createQuery("from Customer order by id desc ");
            List<Customer> list = query.list();
            System.out.println(list);

测试输出结果：

3.条件索引

主要代码：

            Query query = session.createQuery("from Customer where id = 2");
            List<Customer> list = query.list();
            System.out.println(list);

结果：

这里要注意的一个问题就是，这里之前资料不是这么直接操作的，但是我按照资料来，报错了（希望知道原因的小伙伴可以帮助解答）：

主要代码：

Query query = session.createQuery("from Customer where id = ?");
            query.setParameter(0,2);
            List<Customer> list = query.list();
            System.out.println(list);

结果：

八月 25, 2019 11:02:17 上午 org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl getIsolatedConnection
INFO: HHH10001501: Connection obtained from JdbcConnectionAccess [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess@69e308c6] for (non-JTA) DDL execution was not in auto-commit mode; the Connection 'local transaction' will be committed and the Connection will be set into auto-commit mode.
Exception in thread "main" java.lang.IllegalArgumentException: org.hibernate.QueryException: Legacy-style query parameters (`?`) are no longer supported; use JPA-style ordinal parameters (e.g., `?1`) instead : from customer.Customer where id = ? [from customer.Customer where id = ?]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:729)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:104)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.hibernate.context.internal.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:350)
	at com.sun.proxy.$Proxy39.createQuery(Unknown Source)
	at test.test.main(test.java:41)
Caused by: org.hibernate.QueryException: Legacy-style query parameters (`?`) are no longer supported; use JPA-style ordinal parameters (e.g., `?1`) instead : from customer.Customer where id = ? [from customer.Customer where id = ?]
	at org.hibernate.QueryException.generateQueryException(QueryException.java:120)
	at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:220)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
	at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:158)
	at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:611)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:720)
	... 8 more
Caused by: org.hibernate.QueryException: Legacy-style query parameters (`?`) are no longer supported; use JPA-style ordinal parameters (e.g., `?1`) instead : from customer.Customer where id = ?
	at org.hibernate.hql.internal.ast.HqlSqlWalker.generatePositionalParameter(HqlSqlWalker.java:1099)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.parameter(HqlSqlBaseWalker.java:3463)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1374)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4706)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4177)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2138)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:815)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:609)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:272)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192)
	... 14 more

4.分页检索

主要代码：

Query query = session.createQuery("from Customer");
            //表示从第几个开始
            query.setFirstResult(1);
            //表示需要查询几条数据
            query.setMaxResults(2);
            List<Customer> list = query.list();
            System.out.println(list);

结果：

5.分页统计检索

主要代码：

1.count函数

            Query query = session.createQuery("select count (*) from Customer");
            Object count = query.uniqueResult();
            System.out.println(count);

2.sum函数（这里因为id是可以统计的类型，用作实验）

            Query query = session.createQuery("select sum(id) from Customer");
            List<Customer> list = query.list();
            System.out.println(list);

结果：

6.投影检索

主要代码：

            Query query = session.createQuery("select username from Customer ");
            List<Customer> list = query.list();
            System.out.println(list);

结果：

二，QBC查询

1.基本检索

主要代码：

            Criteria criteria = session.createCriteria(Customer.class);
            List<Customer> list = criteria.list();
            System.out.println(list);

结果：

2.排序查询

主要代码：

            Criteria criteria = session.createCriteria(Customer.class);
            criteria.addOrder(org.hibernate.criterion.Order.desc("id"));
            List<Customer> list = criteria.list();
            System.out.println(list);

输出结果：

3.条件检索

主要代码：

            Criteria criteria = session.createCriteria(Customer.class);
            criteria.add(Restrictions.eq("username","AT&T"));
            Object result = criteria.uniqueResult();
            System.out.println(result);

输出结果：

4.条件查询

主要代码：

            Criteria criteria = session.createCriteria(Customer.class);
            criteria.setFirstResult(2);
            criteria.setMaxResults(1);
            List<Customer> list = criteria.list();
            System.out.println(list);

输出结果：

5.统计分页查询

主要代码：

            Criteria criteria = session.createCriteria(Customer.class);
            criteria.setProjection(Projections.rowCount());
            Object count = criteria.uniqueResult();
            System.out.println(count);

输出结果：

6.离线条件查询

主要代码：

            DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Customer.class);
            detachedCriteria.add(Restrictions.eq("username","AT&T"));
            Criteria criteria = detachedCriteria.getExecutableCriteria(session);
            Customer c = (Customer) criteria.uniqueResult();
            System.out.println(c);

输出结果：

三，SQL查询

SQL查询就是直接输入sql语句查询，这里只举个查询整个顾客列表信息的例子

主要代码：

            SQLQuery query = session.createSQLQuery("select * from customer");
            query.addEntity(Customer.class);
            List<Customer> list = query.list();
            System.out.println(list);

输出结果：