hibernate查询方式:HQL、SQL、Criteria方法、命名、动态分离查询、例子查询

query database first!

mysql> select * from product;+----+--------+----------+| id | name   | qq       |+----+--------+----------+|  1 | apple  | 20121212 ||  2 | orange | 20111111 ||  3 | banana | 20122222 ||  4 | apple  | 20122222 ||  5 | apple  | 20122222 ||  6 | apple  | 201222qq |+----+--------+----------+6 rows in set (0.00 sec)mysql>

【1、HQL查询】：

hibernate管理类！

package com.bubble.util;import org.hibernate.HibernateException;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.cfg.AnnotationConfiguration;/** * @author bubble  * */public class HibernateUtil {// single private static final SessionFactory sessionFactory;static{try{//class AnnotationConfiguration:读取关于Annotation的配置sessionFactory=new AnnotationConfiguration().configure().buildSessionFactory();}catch (Throwable e) {// TODO: handle exceptionthrow new ExceptionInInitializerError(e);}}// static method to get sessionpublic static Session getSession() throws HibernateException{return sessionFactory.openSession();}// close session factorypublic static void closeSessionFactory(){sessionFactory.close();}}

测试HQL查询方式的代码！

package com.bubble.test;import java.util.List;import org.hibernate.Query;import org.hibernate.Session;import com.bubble.entity.Product;import com.bubble.util.HibernateUtil;public class HqlTest { /** * @author bubble 11 / 12 / 07 * HQL具有跨数据库的优点。 * 适用情况：常用方法，比较传统，类似jdbc。 * 缺点：新的查询语言，适用面有限，仅适用于Hibernate框架。 */public static void main(String[] args) {// TODO Auto-generated method stub Session session=HibernateUtil.getSession();// get session //String hql="from Product as product where product.name= ? ";// hql  // same to the ? method  String hql="from Product as product where product.name=:name";// 命名参数   Query  query = session.createQuery(hql); // create query object  // query.setString(0, "apple");// set the value of first ? query.setString("name","apple");  List<Product> list=query.list();// get product if product's name=apple  for (Product product : list) {// print product's info System.out.println("name is:"+product.getName()+"\tQQ shenma is:"+product.getQq()); } // closesession.close();HibernateUtil.closeSessionFactory();}}

查询结果！

2011-12-7 1:46:54 org.hibernate.cfg.annotations.Version <clinit>信息: Hibernate Annotations 3.3.0.GA2011-12-7 1:46:54 org.hibernate.cfg.Environment <clinit>信息: Hibernate 3.2.52011-12-7 1:46:54 org.hibernate.cfg.Environment <clinit>信息: hibernate.properties not found2011-12-7 1:46:54 org.hibernate.cfg.Environment buildBytecodeProvider信息: Bytecode provider name : cglib2011-12-7 1:46:54 org.hibernate.cfg.Environment <clinit>信息: using JDK 1.4 java.sql.Timestamp handling2011-12-7 1:46:54 org.hibernate.cfg.Configuration configure信息: configuring from resource: /hibernate.cfg.xml2011-12-7 1:46:54 org.hibernate.cfg.Configuration getConfigurationInputStream信息: Configuration resource: /hibernate.cfg.xml2011-12-7 1:46:54 org.hibernate.cfg.Configuration doConfigure信息: Configured SessionFactory: null2011-12-7 1:46:54 org.hibernate.cfg.AnnotationBinder bindClass信息: Binding entity from annotated class: com.bubble.entity.Product2011-12-7 1:46:54 org.hibernate.cfg.annotations.EntityBinder bindTable信息: Bind entity com.bubble.entity.Product on table Product2011-12-7 1:46:54 org.hibernate.validator.Version <clinit>信息: Hibernate Validator 3.0.0.GA2011-12-7 1:46:55 org.hibernate.connection.DriverManagerConnectionProvider configure信息: Using Hibernate built-in connection pool (not for production use!)2011-12-7 1:46:55 org.hibernate.connection.DriverManagerConnectionProvider configure信息: Hibernate connection pool size: 202011-12-7 1:46:55 org.hibernate.connection.DriverManagerConnectionProvider configure信息: autocommit mode: false2011-12-7 1:46:55 org.hibernate.connection.DriverManagerConnectionProvider configure信息: using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost:3306/hiber0012011-12-7 1:46:55 org.hibernate.connection.DriverManagerConnectionProvider configure信息: connection properties: {user=root, password=****}2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: RDBMS: MySQL, version: 5.1.45-community-log2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.1.13 ( Revision: ${bzr.revision-id} )2011-12-7 1:46:55 org.hibernate.dialect.Dialect <init>信息: Using dialect: org.hibernate.dialect.MySQLDialect2011-12-7 1:46:55 org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory信息: Using default transaction strategy (direct JDBC transactions)2011-12-7 1:46:55 org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup信息: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Automatic flush during beforeCompletion(): disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Automatic session close at end of transaction: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: JDBC batch size: 152011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: JDBC batch updates for versioned data: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Scrollable result sets: enabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: JDBC3 getGeneratedKeys(): enabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Connection release mode: auto2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Maximum outer join fetch depth: 22011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Default batch fetch size: 12011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Generate SQL with comments: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Order SQL updates by primary key: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Order SQL inserts for batching: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory信息: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory2011-12-7 1:46:55 org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>信息: Using ASTQueryTranslatorFactory2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Query language substitutions: {}2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: JPA-QL strict compliance: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Second-level cache: enabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Query cache: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory createCacheProvider信息: Cache provider: org.hibernate.cache.NoCacheProvider2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Optimize cache for minimal puts: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Structured second-level cache entries: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Echoing all SQL to stdout2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Statistics: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Deleted entity synthetic identifier rollback: disabled2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Default entity-mode: pojo2011-12-7 1:46:55 org.hibernate.cfg.SettingsFactory buildSettings信息: Named query checking : enabled2011-12-7 1:46:55 org.hibernate.impl.SessionFactoryImpl <init>信息: building session factory2011-12-7 1:46:55 org.hibernate.impl.SessionFactoryObjectFactory addInstance信息: Not binding factory to JNDI, no JNDI name configuredHibernate: select product0_.id as id0_, product0_.name as name0_, product0_.qq as qq0_ from Product product0_ where product0_.name=?name is:appleQQ shenma is:20121212name is:appleQQ shenma is:20122222name is:appleQQ shenma is:20122222name is:appleQQ shenma is:201222qq2011-12-7 1:46:55 org.hibernate.impl.SessionFactoryImpl close信息: closing2011-12-7 1:46:55 org.hibernate.connection.DriverManagerConnectionProvider close信息: cleaning up connection pool: jdbc:mysql://localhost:3306/hiber001

-------------------------------------------------------------------------------------------------------------------------------

【2、对象化查询Criteria方法】：

Criteria查询代码！ 

package com.bubble.test;import java.util.List;import org.hibernate.Criteria;import org.hibernate.Query;import org.hibernate.Session;import org.hibernate.criterion.Restrictions;import com.bubble.entity.Product;import com.bubble.util.HibernateUtil;public class CriteriaTest { /** * @author bubble 11 / 12 / 07 * 适用情况：面向对象操作，革新了以前的数据库操作方式，易读。 * 缺点：适用面较HQL有限。 *  */public static void main(String[] args) {// TODO Auto-generated method stub Session session=HibernateUtil.getSession();// get session Criteria criteria = session.createCriteria(Product.class);  criteria.add(Restrictions.eq("name", "apple"));// Restrictions:get product's name which eq apple ,like the where Restrictions in hql //eq是等于，gt是大于，lt是小于,or是或 criteria.add(Restrictions.gt("id", 3)); // when id > 3   List<Product> list=criteria.list();// get product if product's name=apple and id>3  for (Product product : list) {// print product's info System.out.println("name is:"+product.getName()+"\tQQ shenma is:"+product.getQq()); } // closesession.close();HibernateUtil.closeSessionFactory();}}

查询结果！

Hibernate: select this_.id as id0_0_, this_.name as name0_0_, this_.qq as qq0_0_ from Product this_ where this_.name=? and this_.id>?name is:appleQQ shenma is:20122222name is:appleQQ shenma is:20122222name is:appleQQ shenma is:201222qq

-------------------------------------------------------------------------------------------
【3、动态分离查询DetachedCriteria】

detachedcriteria查询方式代码！

package com.bubble.test;import java.util.List;import org.hibernate.Criteria;import org.hibernate.Session;import org.hibernate.criterion.DetachedCriteria;import org.hibernate.criterion.Restrictions;import com.bubble.entity.Product;import com.bubble.util.HibernateUtil;/** * @author bubble 11 / 12 / 07  * 适用情况：面向对象操作，分离业务与底层，不需要字段属性摄入到Dao实现层。 * 缺点：适用面较HQL有限。 */public class DetachedCriteriaTest {// get result from database ,return listpublic static List dc(DetachedCriteria dc) {Session session = HibernateUtil.getSession();Criteria c = dc.getExecutableCriteria(session);List<Criteria> list = c.list();session.close();return list;}// main methodpublic static void main(String[] args) {DetachedCriteria dc = DetachedCriteria.forClass(Product.class);int id = 1;String name = "apple";if (id != 0)dc.add(Restrictions.gt("id", id));if (name != null)dc.add(Restrictions.eq("name", name));List<Product> list = dc(dc);System.out.println("离线查询返回结果：--------------------");for (Product product : list) {// print product's infoSystem.out.println("name is:" + product.getName()+ "\tQQ shenma is:" + product.getQq());}}}

结果！

Hibernate: select this_.id as id0_0_, this_.name as name0_0_, this_.qq as qq0_0_ from Product this_ where this_.id>? and this_.name=?离线查询返回结果：--------------------name is:appleQQ shenma is:20122222name is:appleQQ shenma is:20122222name is:appleQQ shenma is:201222qq

-------------------------------------------------------------------------------------------------------------------------------------------------------
【4、例子查询】

例子查询代码！

package com.bubble.test;import java.util.List;import org.hibernate.Session;import org.hibernate.criterion.Example;import com.bubble.entity.Product;import com.bubble.util.HibernateUtil;/** * @author bubble 11 / 12 / 07 * 适用情况：面向对象操作。    * 缺点：适用面较HQL有限，不推荐。 */public class ExampleTest { public static void main(String[] args) {// TODO Auto-generated method stub Session session=HibernateUtil.getSession();// get session List<Product> products = session.createCriteria(Product.class).add(Example.create(new Product())).list();  for (Product product : products) {// print product's infoSystem.out.println("name is:" + product.getName()+ "\tQQ shenma is:" + product.getQq());} // closesession.close();HibernateUtil.closeSessionFactory();}}

查询结果！

Hibernate: select this_.id as id0_0_, this_.name as name0_0_, this_.qq as qq0_0_ from Product this_ where (1=1)name is:appleQQ shenma is:20121212name is:orangeQQ shenma is:20111111name is:bananaQQ shenma is:20122222name is:appleQQ shenma is:20122222name is:appleQQ shenma is:20122222name is:appleQQ shenma is:201222qq

【5、sql查询】

sql查询代码！

package com.bubble.test;import java.util.List;import org.hibernate.Query;import org.hibernate.Session;import com.bubble.entity.Product;import com.bubble.util.HibernateUtil;/** * @author bubble 11 / 12 / 07 * 适用情况：不熟悉HQL的朋友，又不打算转数据库平台的朋友，万能方法  * 缺点：破坏跨平台，不易维护，不面向对象。 */public class SqlTest { public static void main(String[] args) {// TODO Auto-generated method stub Session session=HibernateUtil.getSession();// get session Query query = session.createSQLQuery("select * from product").addEntity(Product.class);// create sql statement  List<Product> list=query.list();// get all products  for (Product product : list) {// print product's info System.out.println("name is:"+product.getName()+"\tid is:"+product.getId()); } // closesession.close();HibernateUtil.closeSessionFactory();}}

结果！

Hibernate: select * from productname is:appleid is:1name is:orangeid is:2name is:bananaid is:3name is:appleid is:4name is:appleid is:5name is:appleid is:6

-----------------------------------------------------------------------------------------------------------

【6、命名查询】

之前是使用hibernate注解来进行映射的，现在注释掉注解映射

hibernate.cfg.xml

<?xml version='1.0' encoding='UTF-8'?><!DOCTYPE hibernate-configuration PUBLIC          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"><!-- Generated by MyEclipse Hibernate Tools.                   --><hibernate-configuration><session-factory><property name="dialect">org.hibernate.dialect.MySQLDialect</property><property name="connection.url">jdbc:mysql://localhost:3306/hiber001</property><property name="connection.username">root</property><property name="connection.password">123456</property><property name="connection.driver_class">com.mysql.jdbc.Driver</property><property name="myeclipse.connection.profile">mysql5</property><property name="show_sql">true</property><!-- annotation part --><!-- <mapping class="com.bubble.entity.Product" /> --> <mapping resource="com/bubble/entity/Product.hbm.xml" /> </session-factory></hibernate-configuration>

命名查询代码！

package com.bubble.test;/** * @author bubble 11 / 12 / 07 * 适用情况：万能方法，有点像ibatis轻量级框架的操作，方便维护。 * 缺点：不面向对象。基于hql和sql，有一定缺陷 */import java.util.List;import org.hibernate.Query;import org.hibernate.Session;import com.bubble.entity.Product;import com.bubble.util.HibernateUtil; public class NamedTest {public static void main(String[] args) {// TODO Auto-generated method stub Session session=HibernateUtil.getSession();// get session Query query = session.getNamedQuery("getProductByName");  query.setString("name","apple");  List<Product> list=query.list();// get all products named apple  for (Product product : list) {// print product's info System.out.println("name is:"+product.getName()+"\tid is:"+product.getId()); } // closesession.close();HibernateUtil.closeSessionFactory();}}

查询结果！

Hibernate: select product0_.id as id0_, product0_.name as name0_, product0_.qq as qq0_ from hiber001.product product0_ where product0_.name=?name is:appleid is:1name is:appleid is:4name is:appleid is:5name is:appleid is:6

okay！that's all!

----------------------------------------------------------------------------------------------------------------------------------------------------

谢谢~~欢迎交流！

本文转自：http://www.kaifaer.com/code-17-1-1.html 小伙伴开发网