Hibernate查询的几种总结。

野性酷女 2024-04-17 05:56 71阅读 0赞

### 一，HQL查询： ###

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

链接：[https://blog.csdn.net/chlei\_/article/details/100042012][https_blog.csdn.net_chlei_article_details_100042012]

### 1.基本索引 ###

数据库表格：

![20190825101721112.png][]

主要代码：

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

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70][]

### 2.排序索引 ###

主要代码：

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

测试输出结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 1][]

### 3.条件索引 ###

主要代码：

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

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 2][]

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

主要代码：

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);

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 3][]

### 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);

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 4][]

![201908251232219.png][]

### 6.投影检索 ###

主要代码：

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

结果：

![20190825153620604.png][]

### 二，QBC查询 ###

### 1.基本检索 ###

主要代码：

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

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 5][]

### 2.排序查询 ###

主要代码：

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

输出结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 6][]

### 3.条件检索 ###

主要代码：

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

输出结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 7][]

### 4.条件查询 ###

主要代码：

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

输出结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 8][]

### 5.统计分页查询 ###

主要代码：

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

输出结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 9][]

### 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);

输出结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 10][]

### 三，SQL查询 ###

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

主要代码：

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

输出结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 11][]

[https_blog.csdn.net_chlei_article_details_100042012]: https://blog.csdn.net/chlei_/article/details/100042012
[20190825101721112.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/aefae55bf6c145a79f2e31b4fb124101.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/541fe1fe6c4149e894ab045b37eb35a2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/546de2e8b0f8424aa0b84139db04de0b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/8c4f8ec582e5465c871a5e9d1aedd76e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/67532983d6f44c87acf9a477aa120291.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/77c3eb00a20c4c949cb6b84230e9f4f1.png
[201908251232219.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/513a604abfad4651a0b6af4d9bf1036f.png
[20190825153620604.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/0d92937e8aa245848a0bbf98470896c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/cde24e81747a46de8aff341a04f7e703.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/dfca6fecb7c04f4cbe99f7f17abe6554.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/68c4ac0540a54c76b713ab458e23ca12.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/9e7f9c219b934faa8ca105d830b2b39b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/b8db9e64d4a84d1abbe00340646d4feb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/3b26fb87cec8412baba79dff90c6dd0e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/658688706f2944e3a9f03591a90ff6d7.png