Hibernate查询方式
这里使用了两个实体类,客户类和联系人类;
关系是客户类为一,联系人类为多
这里补充下要用到的资料。
//客户类:
public class Customer {
private Integer cid;
private String custName;
private String custLevel;
private String custSource;
private String custPhone;
private String custMobile;
private Set<LinkMan> linkMans=new HashSet<LinkMan>();
public Set<LinkMan> getLinkMans() {
return linkMans;
}
public void setLinkMans(Set<LinkMan> linkMans) {
this.linkMans = linkMans;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCustName() {
return custName;
}
public void setCustName(String custName) {
this.custName = custName;
}
public String getCustLevel() {
return custLevel;
}
public void setCustLevel(String custLevel) {
this.custLevel = custLevel;
}
public String getCustSource() {
return custSource;
}
public void setCustSource(String custSource) {
this.custSource = custSource;
}
public String getCustPhone() {
return custPhone;
}
public void setCustPhone(String custPhone) {
this.custPhone = custPhone;
}
public String getCustMobile() {
return custMobile;
}
public void setCustMobile(String custMobile) {
this.custMobile = custMobile;
}
public Customer(Integer cid, String custName, String custLevel, String custSource, String custPhone,
String custMobile) {
super();
this.cid = cid;
this.custName = custName;
this.custLevel = custLevel;
this.custSource = custSource;
this.custPhone = custPhone;
this.custMobile = custMobile;
}
public Customer() {
super();
}
@Override
public String toString() {
return "Customer [cid=" + cid + ", custName=" + custName + ", custLevel=" + custLevel + ", custSource="
+ custSource + ", custPhone=" + custPhone + ", custMobile=" + custMobile + "]";
}
}
//联系人类:
public class LinkMan {
private Integer lid;
private String lname;
private String lgender;
private String lphone;
private Customer customer;
public Customer getCustomer() {
return customer;
}
public void setCustomer(Customer customer) {
this.customer = customer;
}
public Integer getLid() {
return lid;
}
public void setLid(Integer lid) {
this.lid = lid;
}
public String getLname() {
return lname;
}
public void setLname(String lname) {
this.lname = lname;
}
public String getLgender() {
return lgender;
}
public void setLgender(String lgender) {
this.lgender = lgender;
}
public String getLphone() {
return lphone;
}
public void setLphone(String lphone) {
this.lphone = lphone;
}
public LinkMan() {
super();
// TODO Auto-generated constructor stub
}
public LinkMan(Integer lid, String lname, String lgender, String lphone) {
super();
this.lid = lid;
this.lname = lname;
this.lgender = lgender;
this.lphone = lphone;
}
@Override
public String toString() {
return "LinkMan [lid=" + lid + ", lname=" + lname + ", lgender=" + lgender + ", lphone=" + lphone + "]";
}
}
<!-- 客户类的配置 -->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="cn.domarvel.entity.Customer" table="customer">
<id name="cid">
<generator class="native"></generator>
</id>
<property name="custName" length="20"/>
<property name="custLevel" length="10"/>
<property name="custSource" length="20"/>
<property name="custPhone" length="11"/>
<property name="custMobile" length="11"/>
<set name="linkMans" cascade="save-update,delete" inverse="true"><!-- cascade是为了简化操作,inverse是为了不让Hibernate双向
维护多的那方的外键,为了提高效率,双向维护是在hibernate内部实现的,并不是在表里面实现的。 -->
<key column="clid"></key>
<one-to-many class="cn.domarvel.entity.LinkMan"/>
</set>
</class>
</hibernate-mapping>
<!-- 联系人的配置 -->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="cn.domarvel.entity.LinkMan" table="linkman">
<id name="lid">
<generator class="native"></generator>
</id>
<property name="lname"/>
<property name="lgender"/>
<property name="lphone"/>
<many-to-one name="customer" class="cn.domarvel.entity.Customer" column="clid"></many-to-one>
</class>
</hibernate-mapping>
//这是Hibernate操作的工具类
public class HibernateUtils {
private static SessionFactory sessionFactory=null;
static{
Configuration configuration=new Configuration();
configuration.configure();
sessionFactory=configuration.buildSessionFactory();
}
public static SessionFactory getSessionFactory(){
return sessionFactory;
}
public static Session getCurrentSession(){
return sessionFactory.getCurrentSession();
}
public static void main(String[] args) {
}
}
对象导航查询
- 根据id查询某个客户,在查询这个客户里面所有的联系人。
- 通过对象引导查询,要写该对象的get方法
OID查询
- 根据id查询某一条记录,返回对象。
hql查询
- Query对象,写hql语句实现查询。
QBC查询
- Criteria对象。
本地sql查询
- SQLQuery对象,使用普通sql实现查询。
对象导航查询
- 查询某个客户里面所有联系人过程,使用对象导航实现。
/**
* 简单对象导航查询
*/
@Test
public void showDeepAll(){
Session session=HibernateUtils.getCurrentSession();
Transaction tanTransaction=session.beginTransaction();
Customer customer=session.get(Customer.class, 1);
System.out.println(customer);
System.out.println(customer.getLinkMans());//通过直接过去customer里面的联系人,也就是在一的那方直接获取多的那方就可以输出,
Hibernate会帮你自动查询
//这里的查询过程是,如果你没有调用customer.getLinkMans()你就不会从数据库里面查找,也就是说,用的时候Hibernate才帮你查找。
tanTransaction.commit();
}
//注意这里不开启事务是没办法成功进行操作的。
//导航查询
@Test
public void showSingle(){
Session session=HibernateUtils.getCurrentSession();
Transaction transaction=session.beginTransaction();
Query query=session.createQuery("select lname from LinkMan s where s.customer.cid = ?");//注意这里必须要给类名重命名,不然
会报错。
query.setInteger(0, 1);
List<String> customers=query.list();
for(String i:customers){
System.out.println(i);
}
transaction.commit();
}
OID查询
- 根据id查询某一条记录,返回对象。
@Test
public void showDeepAll(){
Session session=HibernateUtils.getCurrentSession();
Transaction tanTransaction=session.beginTransaction();
Customer customer=session.get(Customer.class, 1);
System.out.println(customer);
tanTransaction.commit();
}
HQL查询
- hql:hibernate query language ,hibernate 提供一种查询语言,hql语言和普通sql很相似。
- 区别:普通sql操作数据表和字段,hql操作实体类和属性。
使用hql查询操作的时候,使用Query对象
- 创建Query对象,写hql语句
- 调用Query对象里面的方法得到结果
常用的hql语句
- 查询所有
Query query=session.createQuery("from Customer");
List<Customer> list=query.list();
for(Customer customer:list){
System.out.println(customer);
}
2.条件查询
简介: 通过id查询客户名:
/*
第一种方式:通过问号设置值(?)
*/
@Test
public void showSingle(){
Session session=HibernateUtils.getCurrentSession();
Transaction transaction=session.beginTransaction();
Query query=session.createQuery("select custName from Customer where id = ?");
query.setParameter(0, 1);//设置问号,从0开始
List<String> customers=query.list();
for(String i:customers){
System.out.println(i);
}
transaction.commit();
}
/*
第二种方式:通过起别名来赋值
格式:":别名"
@Test
public void showSingle(){
Session session=HibernateUtils.getCurrentSession();
Transaction transaction=session.beginTransaction();
Query query=session.createQuery("select custName from Customer where id = :idid");
query.setParameter("idid", 1);
List<String> customers=query.list();
for(String i:customers){
System.out.println(i);
}
transaction.commit();
}
*/
//上面两个的sql输出结果
Hibernate:
select
customer0_.custName as col_0_0_
from
customer customer0_
where
customer0_.cid=?
百度
//很明显,Hibernate这是要什么给什么
通过id查询客户所有信息
@Test
public void showSingle(){
Session session=HibernateUtils.getCurrentSession();
Transaction transaction=session.beginTransaction();
Query query=session.createQuery("from Customer where id = ?");
query.setInteger(0, 1);
List<Customer> customers=query.list();
for(Customer i:customers){
System.out.println(i);
}
transaction.commit();
}
//输出结果:
Hibernate:
select
customer0_.cid as cid1_0_,
customer0_.custName as custName2_0_,
customer0_.custLevel as custLeve3_0_,
customer0_.custSource as custSour4_0_,
customer0_.custPhone as custPhon5_0_,
customer0_.custMobile as custMobi6_0_
from
customer customer0_
where
customer0_.cid=?
Customer [cid=1, custName=百度, custLevel=vip, custSource=网络, custPhone=110, custMobile=120]
基本用法:
- distinct 过滤重复的值
hql="select distinct ename from Emp ";
- delete 删除
//删除年龄大于25的员工
hql="delete Emp where eage>25"; //删除年龄大于25岁的用户
Query query=session.createQuery(hql);
query.executeUpdate(); //执行 executeUpdate 方法返回int类型。
tx.commit(); //成功,则提交,对数据库操作
- update 更新
//更新员工编号11的年龄为22
String hql="update Emp s set s.eage='22' where s.eid=11"; //更新语句
Query query=session.createQuery(hql);
query.executeUpdate(); //执行
tx.commit(); //成功,则提交
- between…and…和not between… and…确定查询范围
//查找员工表中年龄在20到30之间的员工姓名
hql="select ename from Emp where eage between 20 and 30"
- in和not in确定查询集合
//查询员工属于低1,2部门的员工姓名
hql="select ename from Emp where did in(1,2)"
- like进行模糊查询
//用like进行模糊查询时有两个可用的通配符:“%”和“_”。“%”代表长度大于等于0的字符,“_”代表长度为1的单个字符。
//查询员工表中姓名中带有刘的员工
hql="select ename from Emp where ename like '%刘%'"
- 逻辑与 and 逻辑或 or
//查询员工中薪水大于2000 同时年龄小于30的员工姓名
hql="select ename from Emp where esal>2000 and eage<30";
//查询员工中,年龄大于40或者年龄小于30的员工
hql="select ename from Emp where eage>40 or eage<30";
- order by对结果进行排序
//对薪水进行排序,从小到大
hql="select esal from Emp order by esal asc";
//对年龄进行排序,降序,
hql="select ename from Emp order by eage desc";
- group by对记录进行分组
//根据部门分组,求出各组的平均薪水
hql=" select avg(esal) from Emp group by did";
- having 对分组进行筛选
//根据部门分组,查出员工所在组平均薪水大于10000的员工姓名
hql=" select ename from Emp group by did having avg(esal)>10000";
- 聚集函数
//查询emp员工表中有多少个员工。
sql:select count(ename) from emp;
hql: select count(ename) from Emp;
聚集函数及含义:
hql: select avg(esal) from Emp; 薪水平均值
hql: select max(esal) from Emp; 薪水最大值
hql: select sum(esal) from Emp; 薪水最小值
public void Test91() throws Exception{
Configuration config=new Configuration().configure();
SessionFactory sessionFactory= config.buildSessionFactory();
Session session=null;
Transaction tr=null;
try{
session= sessionFactory.openSession();
tr=session.beginTransaction();
String hql="select count(ename) from Emp";
Object count=session.createQuery(hql).uniqueResult(); //uniqueResult()方法返回Object类型
System.out.println(count);
tr.commit();
}catch(Exception e){
tr.rollback();
}finally{
if(session!=null){
session.close();
}
if(sessionFactory!=null){
sessionFactory.close();
}
}
}
- 子查询
Hibernate 支持子查询,所谓子查询就是,要查询的字段及信息在A表,条件在B表。语法与sql语句相似。
public void Test9() throws Exception{
Configuration config=new Configuration().configure();
SessionFactory sessionFactory= config.buildSessionFactory();
Session session=null;
Transaction tr=null;
try{
session= sessionFactory.openSession();
tr=session.beginTransaction();
String hql="select ename from Emp where did in (select did from Dept where daddress = 302)";
Query query= session.createQuery(hql);
List<String> list = query.list();
for(String message:list){
System.out.println(message);
}
tr.commit();
}catch(Exception e){
tr.rollback();
}finally{
if(session!=null){
session.close();
}
if(sessionFactory!=null){
sessionFactory.close();
}
}
}