在Java的Hibernate框架中对数据库数据进行查询操作
hibernate查询语言(hql)是一种面向对象的查询语言,类似于sql,但不是对表和列操作,hql适用于持久对象和它们的属性。 hql查询由hibernate转换成传统的sql查询,这在圈上的数据库执行操作。
虽然可以直接使用sql语句和hibernate使用原生sql,但建议使用hql尽可能避免数据库可移植性的麻烦,并采取hibernate的sql生成和缓存策略的优势。
都像select,from和where等关键字不区分大小写,但如表名和列名的属性是区分在hql敏感。
from 语句
使用from子句,如果要加载一个完整的持久化对象到内存中。下面是一个使用from子句的简单的语法:
string hql = "from employee"; query query = session.createquery(hql); list results = query.list();
string hql = "from com.hibernatebook.criteria.employee"; query query = session.createquery(hql); list results = query.list();
as 语句
as子句可以用来别名分配给类中的hql查询,特别是当有很长的查询。例如,我们前面简单的例子是以下几点:
string hql = "from employee as e"; query query = session.createquery(hql); list results = query.list();
as关键字是可选的,也可以直接在之后的类名指定别名,如下所示:
string hql = "from employee e"; query query = session.createquery(hql); list results = query.list();
select 子句
select子句提供了更多的控制权比from子句的结果集。如果想获得对象而不是整个对象的几个属性,使用select子句。下面是一个使用select语句来获取employee对象只是first_name字段的简单的语法:
string hql = "select e.firstname from employee e"; query query = session.createquery(hql); list results = query.list();
值得注意的是在这里,employee.firstname是employee对象的一个属性,而不是employee表的一个字段。
where 子句
如果想缩小了从存储返回的特定对象,可以使用where子句。下面是一个使用where子句的简单的语法:
string hql = "from employee e where e.id = 10"; query query = session.createquery(hql); list results = query.list();
order by 子句
若要排序hql查询的结果,将需要使用order by子句。您可以在结果集按升序(asc)或降序(desc)通过在对象的任何属性排序结果。下面是一个使用order by子句的简单的语法:
string hql = "from employee e where e.id > 10 order by e.salary desc"; query query = session.createquery(hql); list results = query.list();
如果想通过一个以上的属性进行排序,你会仅仅是额外的属性添加到由子句用逗号隔开,如下所示的命令的结尾:
string hql = "from employee e where e.id > 10 " + "order by e.firstname desc, e.salary desc "; query query = session.createquery(hql); list results = query.list();
group by 子句
该子句允许从hibernate的它基于属性的值的数据库和组提取信息,并且通常使用结果包括总值。下面是一个使用group by子句的语法很简单:
string hql = "select sum(e.salary), e.firtname from employee e " + "group by e.firstname"; query query = session.createquery(hql); list results = query.list();
使用命名参数
hibernate命名在其hql查询参数支持。这使得编写接受来自用户的输入容易,不必对sql注入攻击防御hql查询。下面是一个使用命名参数的简单的语法:
string hql = "from employee e where e.id = :employee_id"; query query = session.createquery(hql); query.setparameter("employee_id",10); list results = query.list();
update 子句
批量更新是新的hql与hibernate3,以及不同的删除工作,在hibernate 3和hibernate2一样。 query接口现在包含一个名为executeupdate()方法用于执行hql update或delete语句。
在update子句可以用于更新一个或多个对象中的一个或多个属性。下面是一个使用update子句的简单的语法:
string hql = "update employee set salary = :salary " + "where id = :employee_id"; query query = session.createquery(hql); query.setparameter("salary", 1000); query.setparameter("employee_id", 10); int result = query.executeupdate(); system.out.println("rows affected: " + result);
delete 子句
delete子句可以用来删除一个或多个对象。下面是一个使用delete子句的简单的语法:
string hql = "delete from employee " + "where id = :employee_id"; query query = session.createquery(hql); query.setparameter("employee_id", 10); int result = query.executeupdate(); system.out.println("rows affected: " + result);
insert 子句
hql支持insert into子句中只记录在那里可以插入从一个对象到另一个对象。以下是使用insert into子句的简单的语法:
string hql = "insert into employee(firstname, lastname, salary)" + "select firstname, lastname, salary from old_employee"; query query = session.createquery(hql); int result = query.executeupdate(); system.out.println("rows affected: " + result);
聚合方法
hql支持多种聚合方法,类似于sql。他们工作在hql同样的方式在sql和下面的可用功能列表:
distinct关键字只计算在该行设定的唯一值。下面的查询将只返回唯一的计数:
string hql = "select count(distinct e.firstname) from employee e"; query query = session.createquery(hql); list results = query.list();
使用查询分页
有用于分页查询接口的两个方法。
- query setfirstresult(int startposition)
- query setmaxresults(int maxresult)
采用上述两种方法一起,可以在网站或swing应用程序构建一个分页组件。下面是例子,可以扩展来获取10行:
string hql = "from employee"; query query = session.createquery(hql); query.setfirstresult(1); query.setmaxresults(10); list results = query.list();
查询条件
hibernate提供了操作对象,并依次数据在rdbms表可用的备用方式。其中一个方法是标准的api,它允许你建立一个标准的查询对象编程,可以套用过滤规则和逻辑条件。
hibernate的session接口提供了可用于创建一个返回的持久化对象的类的实例时,应用程序执行一个条件查询一个criteria对象createcriteria()方法。
以下是最简单的一个条件查询的例子是将简单地返回对应于employee类的每个对象。
criteria cr = session.createcriteria(employee.class); list results = cr.list();
限制与标准:
可以使用add()方法可用于criteria对象添加限制条件查询。下面是例子增加一个限制与薪水返回的记录是等于2000:
criteria cr = session.createcriteria(employee.class); cr.add(restrictions.eq("salary", 2000)); list results = cr.list();
以下是几个例子覆盖不同的场景,并且可以根据要求使用:
criteria cr = session.createcriteria(employee.class); // to get records having salary more than 2000 cr.add(restrictions.gt("salary", 2000)); // to get records having salary less than 2000 cr.add(restrictions.lt("salary", 2000)); // to get records having fistname starting with zara cr.add(restrictions.like("firstname", "zara%")); // case sensitive form of the above restriction. cr.add(restrictions.ilike("firstname", "zara%")); // to get records having salary in between 1000 and 2000 cr.add(restrictions.between("salary", 1000, 2000)); // to check if the given property is null cr.add(restrictions.isnull("salary")); // to check if the given property is not null cr.add(restrictions.isnotnull("salary")); // to check if the given property is empty cr.add(restrictions.isempty("salary")); // to check if the given property is not empty cr.add(restrictions.isnotempty("salary")); 可以创建and或or使用logicalexpression限制如下条件: criteria cr = session.createcriteria(employee.class); criterion salary = restrictions.gt("salary", 2000); criterion name = restrictions.ilike("firstnname","zara%"); // to get records matching with or condistions logicalexpression orexp = restrictions.or(salary, name); cr.add( orexp ); // to get records matching with and condistions logicalexpression andexp = restrictions.and(salary, name); cr.add( andexp ); list results = cr.list();
虽然上述所有条件,可以直接使用hql在前面的教程中介绍。
分页使用标准:
还有的标准接口,用于分页的两种方法。
- public criteria setfirstresult(int firstresult)
- public criteria setmaxresults(int maxresults)
采用上述两种方法一起,我们可以在我们的网站或swing应用程序构建一个分页组件。下面是例子,可以扩展来每次获取10行:
criteria cr = session.createcriteria(employee.class); cr.setfirstresult(1); cr.setmaxresults(10); list results = cr.list();
排序的结果:
标准的api提供了org.hibernate.criterion.order类排序按升序或降序排列你的结果集,根据对象的属性。这个例子演示了如何使用order类的结果集进行排序:
criteria cr = session.createcriteria(employee.class); // to get records having salary more than 2000 cr.add(restrictions.gt("salary", 2000)); // to sort records in descening order crit.addorder(order.desc("salary")); // to sort records in ascending order crit.addorder(order.asc("salary")); list results = cr.list();
预测与聚合:
该criteria api提供了一个org.hibernate.criterion.projections类可用于获取平均值,最大值或最小值的属性值。projections类是类似于类限制,因为它提供了几个静态工厂方法用于获得projection 实例。 provides the
以下是涉及不同的方案的一些例子,可按规定使用:
criteria cr = session.createcriteria(employee.class); // to get total row count. cr.setprojection(projections.rowcount()); // to get average of a property. cr.setprojection(projections.avg("salary")); // to get distinct count of a property. cr.setprojection(projections.countdistinct("firstname")); // to get maximum of a property. cr.setprojection(projections.max("salary")); // to get minimum of a property. cr.setprojection(projections.min("salary")); // to get sum of a property. cr.setprojection(projections.sum("salary"));
criteria queries 例子:
考虑下面的pojo类:
public class employee { private int id; private string firstname; private string lastname; private int salary; public employee() {} public employee(string fname, string lname, int salary) { this.firstname = fname; this.lastname = lname; this.salary = salary; } public int getid() { return id; } public void setid( int id ) { this.id = id; } public string getfirstname() { return firstname; } public void setfirstname( string first_name ) { this.firstname = first_name; } public string getlastname() { return lastname; } public void setlastname( string last_name ) { this.lastname = last_name; } public int getsalary() { return salary; } public void setsalary( int salary ) { this.salary = salary; } }
让我们创建下面的employee表来存储employee对象:
create table employee ( id int not null auto_increment, first_name varchar(20) default null, last_name varchar(20) default null, salary int default null, primary key (id) );
以下将被映射文件。
<?xml version="1.0" encoding="utf-8"?> <!doctype hibernate-mapping public "-//hibernate/hibernate mapping dtd//en" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="employee" table="employee"> <meta attribute="class-description"> this class contains the employee detail. </meta> <id name="id" type="int" column="id"> <generator class="native"/> </id> <property name="firstname" column="first_name" type="string"/> <property name="lastname" column="last_name" type="string"/> <property name="salary" column="salary" type="int"/> </class> </hibernate-mapping>
最后,我们将创建应用程序类的main()方法来运行,我们将使用criteria查询的应用程序:
import java.util.list; import java.util.date; import java.util.iterator; import org.hibernate.hibernateexception; import org.hibernate.session; import org.hibernate.transaction; import org.hibernate.sessionfactory; import org.hibernate.criteria; import org.hibernate.criterion.restrictions; import org.hibernate.criterion.projections; import org.hibernate.cfg.configuration; public class manageemployee { private static sessionfactory factory; public static void main(string[] args) { try{ factory = new configuration().configure().buildsessionfactory(); }catch (throwable ex) { system.err.println("failed to create sessionfactory object." + ex); throw new exceptionininitializererror(ex); } manageemployee me = new manageemployee(); /* add few employee records in database */ integer empid1 = me.addemployee("zara", "ali", 2000); integer empid2 = me.addemployee("daisy", "das", 5000); integer empid3 = me.addemployee("john", "paul", 5000); integer empid4 = me.addemployee("mohd", "yasee", 3000); /* list down all the employees */ me.listemployees(); /* print total employee's count */ me.countemployee(); /* print toatl salary */ me.totalsalary(); } /* method to create an employee in the database */ public integer addemployee(string fname, string lname, int salary){ session session = factory.opensession(); transaction tx = null; integer employeeid = null; try{ tx = session.begintransaction(); employee employee = new employee(fname, lname, salary); employeeid = (integer) session.save(employee); tx.commit(); }catch (hibernateexception e) { if (tx!=null) tx.rollback(); e.printstacktrace(); }finally { session.close(); } return employeeid; } /* method to read all the employees having salary more than 2000 */ public void listemployees( ){ session session = factory.opensession(); transaction tx = null; try{ tx = session.begintransaction(); criteria cr = session.createcriteria(employee.class); // add restriction. cr.add(restrictions.gt("salary", 2000)); list employees = cr.list(); for (iterator iterator = employees.iterator(); iterator.hasnext();){ employee employee = (employee) iterator.next(); system.out.print("first name: " + employee.getfirstname()); system.out.print(" last name: " + employee.getlastname()); system.out.println(" salary: " + employee.getsalary()); } tx.commit(); }catch (hibernateexception e) { if (tx!=null) tx.rollback(); e.printstacktrace(); }finally { session.close(); } } /* method to print total number of records */ public void countemployee(){ session session = factory.opensession(); transaction tx = null; try{ tx = session.begintransaction(); criteria cr = session.createcriteria(employee.class); // to get total row count. cr.setprojection(projections.rowcount()); list rowcount = cr.list(); system.out.println("total coint: " + rowcount.get(0) ); tx.commit(); }catch (hibernateexception e) { if (tx!=null) tx.rollback(); e.printstacktrace(); }finally { session.close(); } } /* method to print sum of salaries */ public void totalsalary(){ session session = factory.opensession(); transaction tx = null; try{ tx = session.begintransaction(); criteria cr = session.createcriteria(employee.class); // to get total salary. cr.setprojection(projections.sum("salary")); list totalsalary = cr.list(); system.out.println("total salary: " + totalsalary.get(0) ); tx.commit(); }catch (hibernateexception e) { if (tx!=null) tx.rollback(); e.printstacktrace(); }finally { session.close(); } } }
编译和执行:
下面是步骤来编译并运行上述应用程序。请确保您已在进行的编译和执行之前,适当地设置path和classpath。
- 创建hibernate.cfg.xml配置文件中配置章节解释。
- 创建employee.hbm.xml映射文件,如上图所示。
- 创建employee.java源文件,如上图所示,并编译它。
- 创建manageemployee.java源文件,如上图所示,并编译它。
- 执行manageemployee二进制运行程序.
会得到以下结果,并记录将创建在employee表中。
$java manageemployee
.......various log messages will display here........ first name: daisy last name: das salary: 5000 first name: john last name: paul salary: 5000 first name: mohd last name: yasee salary: 3000 total coint: 4 total salary: 15000
如果检查employee表,它应该记录如下:
mysql> select * from employee;
+----+------------+-----------+--------+ | id | first_name | last_name | salary | +----+------------+-----------+--------+ | 14 | zara | ali | 2000 | | 15 | daisy | das | 5000 | | 16 | john | paul | 5000 | | 17 | mohd | yasee | 3000 | +----+------------+-----------+--------+ 4 rows in set (0.00 sec)
上一篇: asp.net(C#)生成无限级别菜单
下一篇: Spring入门实战之Profile详解
推荐阅读
-
在Java的Hibernate框架中对数据库数据进行查询操作
-
在Java的Spring框架的程序中使用JDBC API操作数据库
-
使用Java对数据库进行基本的查询和更新操作
-
使用Java对数据库进行基本的查询和更新操作
-
如何在Java程序中访问mysql数据库中的数据并进行简单的操作
-
如何在Java程序中访问mysql数据库中的数据并进行简单的操作
-
使用C#对MongoDB中的数据进行查询,修改等操作
-
关于php的tp框架中怎么对选中元素进行数据库操作有关问题
-
C++对Mysql数据库的访问查询(基于Mysql5.0的API,vs2010中操作
-
利用SQL Server 2008中的SSIS进行大规模的数据库查询操作