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());
}