代码
在项目中应用NHibernate架构时,会经常遇到多表查询,使用select
new
ObjectEntity这种语法,根据查询的内容自己构建相应的实体类.
这是一个小型家庭办公中的实例。有员工表Employee,姓名表Project,工作记录表EmpWorkRecord。一个员工有多条工作记录,工作记录与项目有关。
Employee实体类与映射文件如下。
public class Employee
{
private IList emprecord;
public Employee()
{
emprecord = new ArrayList();
}
private int id;
public virtual int Id
{
get { return id; }
set { id = value; }
}
/// <summary>
/// 员工姓名
/// </summary>
private string empname = "" ;
public virtual string EmpName
{
get { return empname; }
set { empname = value; }
}
/// <summary>
/// 性别
/// </summary>
private string sex = "" ;
public virtual string Sex
{
get { return sex; }
set { sex = value; }
}
/// <summary>
/// 地址
/// </summary>
private string address = "" ;
public virtual string Address
{
get { return address; }
set { address = value; }
}
/// <summary>
/// 家庭电话
/// </summary>
private string housephone = "" ;
public virtual string Housephone
{
get { return housephone; }
set { housephone = value; }
}
/// <summary>
/// 手机号码
/// </summary>
private string telephone = "" ;
public virtual string Telephone
{
get { return telephone; }
set { telephone = value; }
}
/// <summary>
/// 电子邮件
/// </summary>
private string email = "" ;
public virtual string Email
{
get { return email; }
set { email = value; }
}
public virtual IList EmpRecord
{
get { return emprecord; }
set { emprecord = value; }
}
private IList < EmpGetMonyRecord > empgetmonyrecord;
public virtual IList < EmpGetMonyRecord > EmpGetMonyRecord
{
get { return empgetmonyrecord; }
set { empgetmonyrecord = value; }
}
}
Employee.hbm.xml文件:
----------------------------
<? xml version = " 1.0 " encoding = " utf-8 " ?>
< hibernate - mapping xmlns = " urn:nhibernate-mapping-2.2 " >
< class name = " Finance.Model.Employee,Finance.Model " table = " Employee " >
< id name = " Id " type = " Int32 " >
< generator class = " identity " />
</ id >
< property name = " EmpName " type = " String " length = " 16 " />
< property name = " Address " type = " String " length = " 100 " />
< property name = " Sex " type = " String " length = " 2 " />
< property name = " Housephone " type = " String " length = " 13 " />
< property name = " Telephone " type = " String " length = " 12 " />
< property name = " Email " type = " String " length = " 50 " />
< bag name = " EmpRecord " table = " EmpWorkRecord " >
< key column = " EmpId " ></ key >
< one - to - many class = " Finance.Model.EmpWorkRecord,Finance.Model " />
</ bag >
< bag name = " EmpGetMonyRecord " table = " EmpGetMonyRecord " >
< key column = " EmpId " ></ key >
< one - to - many class = " Finance.Model.EmpGetMonyRecord,Finance.Model " />
</ bag >
</ class >
</ hibernate - mapping >
--------------------------------
工作记录实体类EmpWorkRecord以及hbm.xml文件如下:
public class EmpWorkRecord
{
private 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 Id
{
get { return id; }
set { id = value; }
}
public virtual int EmpId
{
get { return empid; }
set { empid = value; }
}
public virtual decimal Wage
{
get { return wage; }
set { wage = value; }
}
public virtual DateTime WorkDate
{
get { return workdate; }
set { workdate = value; }
}
public virtual string WorkType
{
get { return worktype; }
set { worktype = value; }
}
public virtual Project Project
{
get { return project; }
set { project = value; }
}
public virtual Employee Emp
{
get { return emp; }
set { emp = value; }
}
}
---EmpWorkRecord.hbm.xml文件----------------------
<? xml version = " 1.0 " encoding = " utf-8 " ?>
< hibernate - mapping xmlns = " urn:nhibernate-mapping-2.2 " >
< import class = " Finance.Model.EmpWorkJoinEmpProject,Finance.Model " /> // 引入构造实体类EmpWorkJoinEmpProject
< class name = " Finance.Model.EmpWorkRecord,Finance.Model " table = " EmpWorkRecord " >
< id name = " Id " type = " Int32 " >
< generator class = " identity " />
</ id >
< property name = " Wage " type = " Decimal " />
< property name = " WorkDate " type = " DateTime " />
< property name = " WorkType " type = " String " length = " 10 " ></ property >
< many - to - one name = " Emp " column = " EmpId " class = " Finance.Model.Employee,Finance.Model " lazy = " false " ></ many - to - one >
< many - to - one name = " Project " column = " ProjectId " class = " Finance.Model.Project,Finance.Model " lazy = " false " ></ many - to - one >
</ class >
</ hibernate - mapping >
现在要获取员工的工作记录信息以表的形式显示出来或者绑定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 " );
}
// 绑定GridView
private 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.QueryString[ " Page " ], 1 );
IList list = materalcostbll.GetPage(pageIndex, 15 , where , " cost.Id desc " );
List < MaterialJoinCost > materialjoincost = new List < MaterialJoinCost > ();
MaterialJoinCost m = null ;
IEnumerator e = list.GetEnumerator();
while (e.MoveNext())
{ // 给查询出来的每行赋值
m = new MaterialJoinCost();
object [] obj = ( object [])e.Current;
m.Name = obj[ 0 ].ToString();
m.Price = decimal .Parse(obj[ 2 ].ToString());
m.Num = Convert.ToInt32(obj[ 3 ].ToString());
m.PurchaseTime = Convert.ToDateTime(obj[ 4 ].ToString());
m.PurchasePeople = obj[ 5 ].ToString();
m.Id = int .Parse(obj[ 6 ].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中加上
< import class = " Finance.Model.EmpWorkJoinEmpProject,Finance.Model " /> 语句,为了是构造实体类时找到该类。
public class EmpWorkJoinEmpProject
{
private int id;
private int empId;
private string empName;
private decimal wage;
private DateTime workDate;
private string pname;
private string worktype;
public string WorkType
{
get { 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 Id
{
get { return id; }
set { id = value; }
}
public int EmpId
{
get { return empId; }
set { empId = value; }
}
public string EmpName
{
get { return empName; }
set { empName = value; }
}
public decimal Wage
{
get { return wage; }
set { wage = value; }
}
public DateTime WorkDate
{
get { return workDate; }
set { workDate = value; }
}
public string PName
{
get { return pname; }
set { pname = value; }
}
}
1 )获取数据DAL
public IList GetDataByQuery( string where )
{
string hql = " select new EmpWorkJoinEmpProject(work.Id, emp.Id,emp.EmpName,work.Wage,work.WorkType,work.WorkDate,p.ProjectName) from EmpWorkRecord work join work.Emp emp,work.Project p " ;
if ( ! string .IsNullOrEmpty( where ) && where != "" )
{
hql += " where " + where ;
}
return control.GetDataByQuery(hql);
}
2 )EmpWorkRecordBLL代码:
public IList GetDataByQuery( string where )
{
return dal.GetDataByQuery( where );
}
3 )绑定GridView
private void BindGv()
{
string where = null ;
if ( this .txtGemoneyPerson.Text.Trim().Length > 0 )
{
where = " EmpName=' " + this .txtGemoneyPerson.Text.Trim() + " ' " ;
}
int pageIndex = Finance.StringHandling.Integer.GetInteger(Request.QueryString[ " Page " ], 1 );
IList list = workbll.GetPage(pageIndex, 15 , where , " work.Id desc " );
GridView1.DataSource = list;
GridView1.DataBind();
pageset.Text = workbll.GetPageSet(pageIndex, 15 , where , " EmpWorkRecordInfo.aspx?Page=$ " , 2 );
}
这是一个小型家庭办公中的实例。有员工表Employee,姓名表Project,工作记录表EmpWorkRecord。一个员工有多条工作记录,工作记录与项目有关。
Employee实体类与映射文件如下。
public class Employee
{
private IList emprecord;
public Employee()
{
emprecord = new ArrayList();
}
private int id;
public virtual int Id
{
get { return id; }
set { id = value; }
}
/// <summary>
/// 员工姓名
/// </summary>
private string empname = "" ;
public virtual string EmpName
{
get { return empname; }
set { empname = value; }
}
/// <summary>
/// 性别
/// </summary>
private string sex = "" ;
public virtual string Sex
{
get { return sex; }
set { sex = value; }
}
/// <summary>
/// 地址
/// </summary>
private string address = "" ;
public virtual string Address
{
get { return address; }
set { address = value; }
}
/// <summary>
/// 家庭电话
/// </summary>
private string housephone = "" ;
public virtual string Housephone
{
get { return housephone; }
set { housephone = value; }
}
/// <summary>
/// 手机号码
/// </summary>
private string telephone = "" ;
public virtual string Telephone
{
get { return telephone; }
set { telephone = value; }
}
/// <summary>
/// 电子邮件
/// </summary>
private string email = "" ;
public virtual string Email
{
get { return email; }
set { email = value; }
}
public virtual IList EmpRecord
{
get { return emprecord; }
set { emprecord = value; }
}
private IList < EmpGetMonyRecord > empgetmonyrecord;
public virtual IList < EmpGetMonyRecord > EmpGetMonyRecord
{
get { return empgetmonyrecord; }
set { empgetmonyrecord = value; }
}
}
Employee.hbm.xml文件:
----------------------------
<? xml version = " 1.0 " encoding = " utf-8 " ?>
< hibernate - mapping xmlns = " urn:nhibernate-mapping-2.2 " >
< class name = " Finance.Model.Employee,Finance.Model " table = " Employee " >
< id name = " Id " type = " Int32 " >
< generator class = " identity " />
</ id >
< property name = " EmpName " type = " String " length = " 16 " />
< property name = " Address " type = " String " length = " 100 " />
< property name = " Sex " type = " String " length = " 2 " />
< property name = " Housephone " type = " String " length = " 13 " />
< property name = " Telephone " type = " String " length = " 12 " />
< property name = " Email " type = " String " length = " 50 " />
< bag name = " EmpRecord " table = " EmpWorkRecord " >
< key column = " EmpId " ></ key >
< one - to - many class = " Finance.Model.EmpWorkRecord,Finance.Model " />
</ bag >
< bag name = " EmpGetMonyRecord " table = " EmpGetMonyRecord " >
< key column = " EmpId " ></ key >
< one - to - many class = " Finance.Model.EmpGetMonyRecord,Finance.Model " />
</ bag >
</ class >
</ hibernate - mapping >
--------------------------------
工作记录实体类EmpWorkRecord以及hbm.xml文件如下:
public class EmpWorkRecord
{
private 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 Id
{
get { return id; }
set { id = value; }
}
public virtual int EmpId
{
get { return empid; }
set { empid = value; }
}
public virtual decimal Wage
{
get { return wage; }
set { wage = value; }
}
public virtual DateTime WorkDate
{
get { return workdate; }
set { workdate = value; }
}
public virtual string WorkType
{
get { return worktype; }
set { worktype = value; }
}
public virtual Project Project
{
get { return project; }
set { project = value; }
}
public virtual Employee Emp
{
get { return emp; }
set { emp = value; }
}
}
---EmpWorkRecord.hbm.xml文件----------------------
<? xml version = " 1.0 " encoding = " utf-8 " ?>
< hibernate - mapping xmlns = " urn:nhibernate-mapping-2.2 " >
< import class = " Finance.Model.EmpWorkJoinEmpProject,Finance.Model " /> // 引入构造实体类EmpWorkJoinEmpProject
< class name = " Finance.Model.EmpWorkRecord,Finance.Model " table = " EmpWorkRecord " >
< id name = " Id " type = " Int32 " >
< generator class = " identity " />
</ id >
< property name = " Wage " type = " Decimal " />
< property name = " WorkDate " type = " DateTime " />
< property name = " WorkType " type = " String " length = " 10 " ></ property >
< many - to - one name = " Emp " column = " EmpId " class = " Finance.Model.Employee,Finance.Model " lazy = " false " ></ many - to - one >
< many - to - one name = " Project " column = " ProjectId " class = " Finance.Model.Project,Finance.Model " lazy = " false " ></ many - to - one >
</ class >
</ hibernate - mapping >
现在要获取员工的工作记录信息以表的形式显示出来或者绑定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 " );
}
// 绑定GridView
private 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.QueryString[ " Page " ], 1 );
IList list = materalcostbll.GetPage(pageIndex, 15 , where , " cost.Id desc " );
List < MaterialJoinCost > materialjoincost = new List < MaterialJoinCost > ();
MaterialJoinCost m = null ;
IEnumerator e = list.GetEnumerator();
while (e.MoveNext())
{ // 给查询出来的每行赋值
m = new MaterialJoinCost();
object [] obj = ( object [])e.Current;
m.Name = obj[ 0 ].ToString();
m.Price = decimal .Parse(obj[ 2 ].ToString());
m.Num = Convert.ToInt32(obj[ 3 ].ToString());
m.PurchaseTime = Convert.ToDateTime(obj[ 4 ].ToString());
m.PurchasePeople = obj[ 5 ].ToString();
m.Id = int .Parse(obj[ 6 ].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中加上
< import class = " Finance.Model.EmpWorkJoinEmpProject,Finance.Model " /> 语句,为了是构造实体类时找到该类。
public class EmpWorkJoinEmpProject
{
private int id;
private int empId;
private string empName;
private decimal wage;
private DateTime workDate;
private string pname;
private string worktype;
public string WorkType
{
get { 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 Id
{
get { return id; }
set { id = value; }
}
public int EmpId
{
get { return empId; }
set { empId = value; }
}
public string EmpName
{
get { return empName; }
set { empName = value; }
}
public decimal Wage
{
get { return wage; }
set { wage = value; }
}
public DateTime WorkDate
{
get { return workDate; }
set { workDate = value; }
}
public string PName
{
get { return pname; }
set { pname = value; }
}
}
1 )获取数据DAL
public IList GetDataByQuery( string where )
{
string hql = " select new EmpWorkJoinEmpProject(work.Id, emp.Id,emp.EmpName,work.Wage,work.WorkType,work.WorkDate,p.ProjectName) from EmpWorkRecord work join work.Emp emp,work.Project p " ;
if ( ! string .IsNullOrEmpty( where ) && where != "" )
{
hql += " where " + where ;
}
return control.GetDataByQuery(hql);
}
2 )EmpWorkRecordBLL代码:
public IList GetDataByQuery( string where )
{
return dal.GetDataByQuery( where );
}
3 )绑定GridView
private void BindGv()
{
string where = null ;
if ( this .txtGemoneyPerson.Text.Trim().Length > 0 )
{
where = " EmpName=' " + this .txtGemoneyPerson.Text.Trim() + " ' " ;
}
int pageIndex = Finance.StringHandling.Integer.GetInteger(Request.QueryString[ " Page " ], 1 );
IList list = workbll.GetPage(pageIndex, 15 , where , " work.Id desc " );
GridView1.DataSource = list;
GridView1.DataBind();
pageset.Text = workbll.GetPageSet(pageIndex, 15 , where , " EmpWorkRecordInfo.aspx?Page=$ " , 2 );
}