01-04-02【Nhibernate (版本3.3.1.4000) 出入江湖】HQL查询
2024-08-30 05:01:35
public IList<Customer> GetAllHql()
{
IList<Customer> result = null;
ISession session = _sessionManager.GetSession(); try
{ result = session.CreateQuery("from Customer").List<Customer>();
}
catch (Exception)
{
throw;
}
finally
{
session.Close();
} return result;
}
注意:
result = session.CreateQuery("from Customer").List<Customer>();
Customer是类名,而不是数据库表名。 这正是Nhibernate的好处:屏蔽对数据库的底层操作,直接面向对象(实体类)编程。 实例:
public IList<Customer> GetAllCustomers_HQL(){
return session.CreateQuery("from Customer c").List<Customer>();
} public IList<int> GetCustomerId_HQL() {
return session.CreateQuery("select c.CustomerId from Customer c").List<int>();
} public IList<Object[]> GetCustomerInfo_HQL() {
return session.CreateQuery("select c.FirstName,count(c.FirstName) from Customer c group by c.FirstName").List<Object[]>();
} public IList<Object[]> AggregateFunction()
{
return session.CreateQuery("select avg(c.CustomerId),sum(c.CustomerId),count(c) from Customer c")
.List<Object[]>();
} public IList<String> Distinct_HQL() {
return session.CreateQuery("select distinct c.FirstName from Customer c").List<String>();
} public IList<Customer> Where_HQL() {
return session.CreateQuery("from Customer c where c.FirstName='aaaa'").List<Customer>();
} public IList<Customer> Where_HQL(String firstname)
{
String hql = String.Format("from Customer c where c.FirstName='{0}'",firstname);
return session.CreateQuery(hql).List<Customer>();
} public IList<Customer> WhereLike_HQL(String firstname)
{
String hql = String.Format("from Customer c where c.FirstName like'%{0}%'", firstname);
return session.CreateQuery(hql).List<Customer>();
} public IList<Customer> WhereLike_HQL2(String firstname)
{
String hql = "from Customer c where c.FirstName like ?";
return session.CreateQuery(hql).SetString(,"%"+firstname+"%").List<Customer>();
} public IList<Customer> WhereLike_HQL3(String firstname)
{
String hql = "from Customer c where c.FirstName like :fname";
return session.CreateQuery(hql).SetString("fname", "%" + firstname + "%").List<Customer>();
} public IList<Customer> OrderBy_HQL() {
return session.CreateQuery("from Customer c order by c.FirstName asc,c.LastName desc").List<Customer>();
} //查询下定单的客户信息
public IList<Customer> InnerJoin_HQL()
{
string hql = "select distinct a from Customer a inner join a.Orders b ";
return session.CreateQuery(hql).List<Customer>();
} //public IList<Customer> UseCriteriaAPI_GetCustomersWithOrders(DateTime orderDate)(){
// return null;
//} //查询下定单的客户信息()
public IList<Customer> InnerJoin_HQL(String orderDate)
{
string hql = "select distinct a from Customer a inner join a.Orders b where b.OrderDate>:orderDate";
return session.CreateQuery(hql).SetString("orderDate",orderDate).List<Customer>();
}
------------------------------------------------------------------------------
HQL查询返回不规则对象:
/// <summary>
/// HQL返回不规则对象
/// </summary>
/// <param name="orderDate"></param>
/// <returns></returns>
public IList<Object[]> GetObjectByHQL(String orderDate)
{
//Object的成员依次是Customer,OrderDate
string hql = "select distinct a,b.OrderDate from Customer a " +
"inner join a.Orders b where b.OrderDate=:orderDate";
return _session.CreateQuery(hql)
.SetString("orderDate", orderDate)
.List<Object[]>();
}
Object的成员依次是Customer,OrderDate,
IList<Object[]> objects = customerService.GetObjectByHQL(order1.OrderDate.ToString()); foreach (var objectse in objects)
{
Console.WriteLine("{0}---{1}",
((Customer)objectse[]).CustomerId,
objectse[objectse.Length-].ToString());
}
调用者必须知道Object的成员及其类型,才能正确使用查询结果,这样很不好。
改进的做法是:
创建一个类Object的成员类型,将HQL返回不规则对象转化为通用对象,减低调用着使用的的复杂度
public class HQLQueryResult
{
public Customer Customer { get; set; }
public DateTime OrderDate { get; set; }
}
/// <summary>
/// 将HQL返回不规则对象转化为通用对象,减低调用着使用的的复杂度
/// </summary>
/// <param name="orderDate"></param>
/// <returns></returns>
public IList<HQLQueryResult> GetObjectByHQLAndTransferToEasyObject(String orderDate)
{
List<HQLQueryResult> results = new List<HQLQueryResult>();
//Object的成员依次是Customer,OrderDate
string hql = "select distinct a,b.OrderDate from Customer a " +
"inner join a.Orders b where b.OrderDate=:orderDate";
IList<Object[]> objects =_session.CreateQuery(hql).SetString("orderDate", orderDate).List<Object[]>();
foreach (var objectse in objects)
{
HQLQueryResult tmp = new HQLQueryResult()
{
Customer= ((Customer)objectse[]),
OrderDate = (DateTime)objectse[objectse.Length - ]
}; results.Add(tmp);
}
return results;
}
调用者可以这要调用:
IList<HQLQueryResult> objects = customerService.GetObjectByHQLAndTransferToEasyObject(order1.OrderDate.ToString()); foreach (var objectse in objects)
{
Console.WriteLine("{0}---{1}",
objectse.Customer.CustomerId,
objectse.OrderDate.ToString());
}
最新文章
- Session 知识点再整理(一)基本概念和原理
- WCF初探-20:WCF错误协定
- 字符串链接strcat
- 2015腾讯暑期实习生 Web前端开发 面试经历
- Hibernate,JPA注解@DynamicInsert和@DynamicUpdate,Hibernate如何插入sysdate
- 在ubuntu上配置apue的运行环境
- ArcGIS10中matplotlib画图时的中文设置
- Wzplayer C++ 版本,WzplayerPro
- ProcessStartInfo.UseShellExecute 属性
- webkit report
- 追踪CM_CONTROLCHANGE消息的产生和执行过程,可以较好的领会VCL的思想(就是到处通知,但耦合性很弱)
- php 编译安装curl 时候出现问题
- 数据库范式(1NF 2NF 3NF BCNF)详解
- 【Python】回文
- Azure Messaging-ServiceBus Messaging消息队列技术系列8-服务总线配额
- 暑假OI规划
- vue2.0父子组件之间通信
- istio入门(00)istio的学习资源
- sssp-springmvc+spring+spring-data-jpa增删改查
- 【二代示波器教程】第14章 uCOS-III操作系统版本二代示波器实现