NHibernate多表查询解决方法.docx

nhibernate多表查询概述:在项目中应用nhibernate架构时,会经常遇到多表查询.因为项目上要用到,通过多天的查询总结了以下两种解决方案.解决方案:(1)使用hql语句,然后把查询出的结果存入临时表中,然后再进行绑定.(2)使用select new oaxtjoinglxt这种语法,根据查询的内容自己构建相应的实体类.第一种方案实现具体过程:我的需求如下:我需要查询jkptoaxtorganization实体类中的orgid,及jkptglxtorganization实体类中的orgname我需要查询jkptoaxtorganization实体类中的orgid,及jkptglxtorganization实体类中的orgname第一步:构建hql语句如下string sql = select oo.orgid ,go.orgname from jkptoaxtorganization as oo,jkptglxtorganization as go where oo.orgid=+orgid+ and oo.suborgid=go.orgid; iquery query = session.createquery(sql);ilist list = query.list();说明:如果在相应的映射文件中设置好实体的对应关系,就没有必要加where条件了.第二步:建立临时表,把查询出的结果存入临时表datatable dt = new datatable();datacolumn dc = new datacolumn(); dc = new datacolumn(); /增加第1列dc.datatype = system.type.gettype(system.int32);dc.columnname = orgid;dt.columns.add(dc);dc = new datacolumn(); /增加第2列dc.datatype = system.type.gettype(system.string);dc.columnname = orgname;dt.columns.add(dc);dc.dispose();ienumerator enu = list.getenumerator();while (enu.movenext() /给查询出来的每行赋值object obj = (object)enu.current;datarow newrow = dt.newrow();newroworgid = obj0;newroworgname = obj1;dt.rows.add(newrow);ds.tables.add(dt);我个人觉得,这种方式存在很大缺陷,如临时表用完需要删除,如果要查询50个字段,那么写临时表就得累死,所以我还是推荐大家用第二种方案.第二种方案的具体实现过程第一步:构建hql查询语句:string sql = select new oaxtjoinglxt(oo.orgid,gg.orgname) from jkptoaxtorganization as oo , jkptglxtorganization as gg where oo.orgid= + orgid + and oo.suborgid=gg.orgid; iquery query = session.createquery(sql);ilist list = query.list();ds = helpers.converttodataset(list);return ds;第二步:根据查询的内容构造的实体类oaxtjoinglxt.cs如下 public class oaxtjoinglxtpublic oaxtjoinglxt(decimal porgid, string porgname)this._orgid = porgid;this._orgname = porgname;private decimal? _orgid;public decimal? orgidget return _orgid; set _orgid =value;private string _orgname;public string orgnameget return _orgname; set _orgname = value; 第三步:在相应的映射文件中导入oaxtjoinglxt类为了要让nhibernate知道到什么地方去找这个oaxtjoinglxt类，我们需要将oaxtjoinglxt类也导入到hbm.xml 文件定义中，在jkptoaxtweatherforecast.hbm.xml中添加下面的语句就可以了： !-到此,已经ok了,大家看一下运行的结果.运行结果: nhibernate 多表查询解决在项目中应用nhibernate架构时,会经常遇到多表查询,使用select new objectentity这种语法,根据查询的内容自己构建相应的实体类.这是一个小型家庭办公中的实例。有员工表employee，姓名表project，工作记录表empworkrecord。一个员工有多条工作记录，工作记录与项目有关。employee实体类与映射文件如下。public class employeeprivate ilist emprecord;public employee()emprecord = new arraylist();private int id;public virtual int idget return id; set id = value; / / 员工姓名/ private string empname = ;public virtual string empnameget return empname; set empname = value; / / 性别/ private string sex = ;public virtual string sexget return sex; set sex = value; / / 地址/ private string address = ;public virtual string addressget return address; set address = value; / / 家庭电话/ private string housephone = ;public virtual string housephoneget return housephone; set housephone = value; / / 手机号码/ private string telephone = ;public virtual string telephoneget return telephone; set telephone = value; / / 电子邮件/ private string email = ;public virtual string emailget return email; set email = value; public virtual ilist emprecordget return emprecord; set emprecord = value; private ilist empgetmonyrecord;public virtual ilist empgetmonyrecordget return empgetmonyrecord; set empgetmonyrecord = value; employee.hbm.xml文件：工作记录实体类empworkrecord以及hbm.xml文件如下：public class empworkrecordprivate employee emp;private int id;private int empid;private decimal wage;private datetime workdate;private string worktype;private project project;public empworkrecord()emp = new employee();project = new project(); public virtual int idget return id; set id = value; public virtual int empidget return empid; set empid = value; public virtual decimal wageget return wage; set wage = value; public virtual datetime workdateget return workdate; set workdate = value; public virtual string worktypeget return worktype; set worktype = value; public virtual project projectget return project; set project = value; public virtual employee empget return emp; set emp = value; empworkrecord.hbm.xml文件/引入构造实体类empworkjoinempproject现在要获取员工的工作记录信息以表的形式显示出来或者绑定gridview上：员工名（empname），工作日期（workdate），工作类型（worktype），姓名名称（projectname）。如果就做关联查询的结果并不能显示出表的形式，必须把查询出的数据进行处理。（1）一种方法是把查询的结果循环遍历构造出表的形式再绑定到gridview。/获取数据public ilist getpage(int pageindex, int pagesize, string where, string orderby) string hql = select m.name,m.type,m.price,cost.num,cost.purchasetime,cost.purchasepeople,cost.id from material m join m.materialcost cost;return control.getmanytablepage(hql, where, orderby, pageindex, pagesize, finance.model.materialcost);/绑定gridviewprivate void bindgv()string where = null;if (this.txtmaterialname.text.trim().length 0)where = name= + this.txtmaterialname.text.trim() + ;int pageindex = finance.stringhandling.integer.getinteger(request.querystringpage, 1);ilist list = materalcostbll.getpage(pageindex, 15, where, cost.id desc); list materialjoincost = new list();materialjoincost m = null;ienumerator e = list.getenumerator();while (e.movenext() /给查询出来的每行赋值m = new materialjoincost();object obj = (object)e.current;m.name = obj0.tostring();m.price = decimal.parse(obj2.tostring();m.num = convert.toint32(obj3.tostring();m.purchasetime = convert.todatetime(obj4.tostring();m.purchasepeople = obj5.tostring();m.id = int.parse(obj6.tostring();materialjoincost.add(m);gridview1.datasource = materialjoincost; gridview1.databind();pageset.text = materalcostbll.getpageset(pageindex, 15, where, materialcost.aspx?page=$, 2);（2）运用nhibernate查询语法：select new empworkjoinempproject(.)。这里主要介绍方法二，这种方法也是比较常用的方式。首先构造empworkjoinempproject实体。在empworkrecord.hbm.xml中加上语句，为了是构造实体类时找到该类。public class empworkjoinempprojectprivate int id;private int empid;private string empname;private decimal wage;private datetime workdate;private string pname;private string worktype;public string worktypeget return worktype; set worktype = value; public empworkjoinempproject(int id, int empid, string empname, decimal wage,string worktype, datetime workdate, string pname)this.id = id;this.empid = empid;this.empname = empname;this.wage = wage;this.workdate = workdate;this.pname = pname;this.worktype = worktype;public int idget return id; set id = value; public int empidget return empid; set empid = value; public string empnameget return empname; set empname = value; public decimal wageget return wage; set wage = value; public datetime workdateget return workdate; set workdate = value; public string pnameget return pname; set pname = value; 1)获取数据dalpublic ilist getdatabyquery(string where)string hql = select new empworkjoinempproject(work.id, emp.id,emp.empname,work.wage,work.worktype,wor