彩世界开奖app官网-彩世界平台官方网址(彩票平台)
做最好的网站
来自 计算机编程 2019-12-07 01:32 的文章
当前位置: 彩世界开奖app官网 > 计算机编程 > 正文

Hibernate框架之Criteria查询【彩世界开奖app官网】

 

1 使用原生SQL语句更新--Database.ExecuteSqlCommand

假设你有一张如图9-1所示的Payment数据库表。

彩世界开奖app官网 1

图9-1

1.1 实体类型:

彩世界开奖app官网 2彩世界开奖app官网 3

1  public class Payment
2     {
3         public int PaymentId { get; set; }
4         public decimal Amount { get; set; }
5 
6         public string Vendor { get; set; }
7     }

View Code

1.2 数据访问类:

彩世界开奖app官网 4彩世界开奖app官网 5

 1 public class EF6RecipesContext: DbContext
 2     {
 3         public DbSet<Payment> Payments { get; set; }
 4         public EF6RecipesContext()
 5             : base("name=EF6CodeFirstRecipesContext")
 6         {
 7         }
 8         protected override void OnModelCreating(DbModelBuilder modelBuilder)
 9         {
10             base.OnModelCreating(modelBuilder);
11 
12             modelBuilder.Entity<Payment>().ToTable("Payments", "example9");
13         }
14     }

View Code

1.3 代码演示:

彩世界开奖app官网 6彩世界开奖app官网 7

 1 // 删除之前的测试数据
 2             using (var context = new EFRecipesEntities())
 3             {
 4                 context.Database.ExecuteSqlCommand("delete from chapter3.payment");
 5             }
 6             //插入两行数据
 7             using (var context = new EFRecipesEntities())
 8             {
 9                 var sql = @"insert into Chapter3.Payment(Amount, Vendor)
10                    values (@Amount, @Vendor)";    //这里可以使用@p0这样的参数占位符,ado.net为自动为我们创建参数对象
11                 var parameters = new DbParameter[]
12                     {
13                         new SqlParameter {ParameterName = "Amount", Value = 99.97M},
14                         new SqlParameter {ParameterName = "Vendor", Value = "Ace Plumbing"}
15                     };
16 
17                 var rowCount = context.Database.ExecuteSqlCommand(sql, parameters);
18 
19                 parameters = new DbParameter[]
20                     {
21                         new SqlParameter {ParameterName = "Amount", Value = 43.83M},
22                         new SqlParameter
23                             {
24                                 ParameterName = "Vendor",
25                                 Value = "Joe's Trash Service"
26                             }
27                     };
28 
29                 rowCount  = context.Database.ExecuteSqlCommand(sql, parameters);
30                 Console.WriteLine("{0} rows inserted", rowCount.ToString());
31             }
32 
33             // 获取并显示数据
34             using (var context = new EFRecipesEntities())
35             {
36                 Console.WriteLine("Payments");
37                 Console.WriteLine("========");
38                 foreach (var payment in context.Payments)
39                 {
40                     Console.WriteLine("Paid {0} to {1}", payment.Amount.ToString(),
41                                       payment.Vendor);
42                 }
43             }
44 
45             Console.WriteLine("nPress <enter> to continue...");
46             Console.ReadLine();

View Code

输出:

1 2 rows inserted
2 Payments
3 ========
4 Paid $99.97 to Ace Plumbing
5 Paid $43.83 to Joe's Trash Service

彩世界开奖app官网 8彩世界开奖app官网 9

/// <summary>
        /// 自定义SQL分页查询_WebSite库_LINQ用于自定义分页SQL和非INT类型变量值传输(防止非INT类型值SQL注入)
        /// </summary>
        /// <typeparam name="T">返回类型</typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="fromTableSql">select * from {0} where {1} order by {2}:填写{0}</param>
        /// <param name="orderByTableFieldSql">select * from {0} where {1} order by {2}:填写{2}</param>
        /// <param name="totalCount">总条数</param>
        /// <param name="whereLinq">关于T的linq语句==>生成可DbParameter[]防SQL注入参数数组</param>
        /// <returns></returns>
        public static List<T> Select_WebSiteBase<T>(int pageIndex, int pageSize, string fromTableSql, string orderByTableFieldSql, out int totalCount, Expression<Func<T, bool>> whereLinq)
        {
            DB.MySql.WebSite.Entity.WherePart wherePart = DB.MySql.WebSite.Entity.WhereBuilder.Instance_MySql.ToSql<T>(whereLinq);
            List<DbParameter> dbParameterList = new List<DbParameter>(0);
            if (wherePart.Parameters != null && wherePart.Parameters.Count > 0)
            {
                foreach (var paramter in wherePart.Parameters)
                {
                    dbParameterList.Add(new MySqlParameter(paramter.Key, paramter.Value));
                }
            }
            string pageSql = string.Format(@"SELECT * FROM {0} WHERE {1} ORDER BY {2} LIMIT {3},{4};", fromTableSql, wherePart.Sql, orderByTableFieldSql, (pageIndex - 1) * pageSize, pageSize);
            string totalCountSql = string.Format(@"SELECT COUNT(*) FROM {0} WHERE {1};", fromTableSql, wherePart.Sql);
            List<T> tList = DB.MySql.WebSite.BLL.BLLGeneric.Select<T>(CommandType.Text, pageSql   totalCountSql, out totalCount, dbParameterList.ToArray());
            dbParameterList.Clear();
            dbParameterList = null;
            return tList;
        }

2 使用原生SQL语句获取对象--Database.SqlQuery()

假设你有如图所示的一个拥有Student实体类型的模型。

彩世界开奖app官网 10

2.1 实体类型

彩世界开奖app官网 11彩世界开奖app官网 12

public class Student
    {
        public int StudentId { get; set; }
        public string Degree { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

View Code

2.2 上下文对象DbContext

彩世界开奖app官网 13彩世界开奖app官网 14

 1 public class EFRecipesEntities : DbContext
 2     {
 3         public EFRecipesEntities()
 4             : base("ConnectionString")
 5         {
 6         }
 7 
 8         public DbSet<Student> Students { get; set; }
 9 
10         protected override void OnModelCreating(DbModelBuilder modelBuilder)
11         {
12             modelBuilder.Entity<Student>().ToTable("example9.Students");
13             base.OnModelCreating(modelBuilder);
14         }
15     }

View Code

2.3 代码演示:

彩世界开奖app官网 15彩世界开奖app官网 16

 1 using (var context = new EFRecipesEntities())
 2             {
 3                 // 删除出测试数据
 4                 context.Database.ExecuteSqlCommand("delete from example9.students");
 5 
 6                 // 插入数据
 7                 context.Students.Add(new Student
 8                     {
 9                         FirstName = "Robert",
10                         LastName = "Smith",
11                         Degree = "Masters"
12                     });
13                 context.Students.Add(new Student
14                     {
15                         FirstName = "Julia",
16                         LastName = "Kerns",
17                         Degree = "Masters"
18                     });
19                 context.Students.Add(new Student
20                     {
21                         FirstName = "Nancy",
22                         LastName = "Stiles",
23                         Degree = "Doctorate"
24                     });
25                 context.SaveChanges();
26             }
27 
28             using (var context = new EFRecipesEntities())
29             {
30                 var sql = "select * from example9.Students where Degree = @Major";
31                 var parameters = new DbParameter[]
32                     {
33                         new SqlParameter {ParameterName = "Major", Value = "Masters"}
34                     };
35                 var students = context.Database.SqlQuery<Student>(sql, parameters);
36                 Console.WriteLine("Students...");
37                 foreach (var student in students)
38                 {
39                     Console.WriteLine("{0} {1} is working on a {2} degree",
40                                       student.FirstName, student.LastName, student.Degree);
41                 }
42             }
43 
44             Console.WriteLine("nPress <enter> to continue...");
45             Console.ReadLine();
46         }

View Code

输出:

 Students...
 Robert Smith is working on a Masters degree
 Julia Kerns is working on a Masters degree


这里在查询语句中使用“*”表示所有的列名,实体框架会将返回的列匹配到合适的属性上。一般情况下,这会工作得很好。但是,查询中只有部分列返回时,实体框架会在实例化对象时抛出一个异常。一个更好的方法和最佳实践是,在你的查询语句中显式枚举所有列(也就是说,指定所有的列名)。

  如果你的SQL语句返回的列多于实例化实体所需数量(也就是说,列值数量多于实体对象属性数量),实体框架会忽略掉多于的列。如果你仔细想想,这不是一个令人满意的行为。再一次重申,在SQL语句中显式枚举你所期望返回的列名,确保它们与实体类型匹配

  SqlQuery()方法有很多限制,如果你在使用TPH继承映射,你的SQL语句返回的行要映射到不同的派生类型上,实体框架不能使用鉴别列来将行映射到正确的派生类型。你可能会得到一个运行时异常,因为行中可能不包含正在实例化类型所需的值。

  有趣的是,我们可以使用SqlQuery()方法实例化根本就不是实体的类型。例如,我们创建一个StudentName类,它只包含姓,和名两个属性民。如果我们的SQL语句也只返回这两个列,我们可以使用SqlQuery<StudentName>()方法和指定的SQL语句获取类型StudentName的实例集合。

  我们很小心地使用短语,SQL语句,而不是查询语句,是因为SqlQuery()方法可以接受任何返回行集合的SQL语句。这当然包含查询语句,但也包含执行存储过程的SQL语句。

 
@Test
    // 查询Emp表总记录数
    public void TestThree2() {    
        Integer result =(Integer) session.createCriteria(Emp.class)
        .setProjection(Projections.count("empno")).uniqueResult();
        System.out.println(result);
    }
WhereBuilder whereBuilder = new WhereBuilder('`');
            Expression<Func<Order_detail, bool>> expression = null;
            expression = orderDetail => orderDetail.OrderDetailState == state;
            WherePart columnValue = whereBuilder.ToSql<Order_detail>(expression);
            expression = orderDetail => orderDetail.OrderID == orderId;
            WherePart where = whereBuilder.ToSql<Order_detail>(expression);

            List<MySqlParameter> paramList = new List<MySqlParameter>(columnValue.Parameters.Count   where.Parameters.Count);
            foreach (KeyValuePair<string, object> pair in columnValue.Parameters)
            {
                paramList.Add(new MySqlParameter("@"   pair.Key, pair.Value));
            }
            foreach (KeyValuePair<string, object> pair in where.Parameters)
            {
                paramList.Add(new MySqlParameter("@"   pair.Key, pair.Value));
            }

            result = UpdateWhere(columnValue.Sql, where.Sql, paramList.ToArray()) == 0;
            Console.WriteLine(result);

View Code

彩世界开奖app官网 17彩世界开奖app官网 18

彩世界开奖app官网 19彩世界开奖app官网 20

 

五:DetachedCriteria

View Code

3.城市为"北京"

彩世界开奖app官网 21彩世界开奖app官网 22

彩世界开奖app官网 23彩世界开奖app官网 24

 

MatchMode.START:字符串在最前面的位置.相当于"like 'key%'"
MatchMode.END:字符串在最后面的位置.相当于"like '%key'"
MatchMode.ANYWHERE:字符串在中间匹配.相当于"like '%key%'"
MatchMode.EXACT:字符串精确匹配.相当于"like 'key'"

彩世界开奖app官网 25彩世界开奖app官网 26

     5.本地SQL检索方式:使用本地数据库的SQL查询语句

View Code

DetachedCriteria和Criteria功能类似,它实现了CriteriaSpecification接口
Criteria是由Session对象创建的
DetachedCriteria创建时不需要Session对象
使用DetachedCriteria来构造查询条件
可以把DetachedCriteria作为方法参数传递给业务层

View Code

    @Test //查询Empinfo的ename在中有 总 
        public void TestSix(){ 
           //like 字符串模式匹配 
           //ilike 字符串模式匹配 同时忽略大小写
            List<Empinfo> list=session.createCriteria(Empinfo.class).add(Restrictions.like("ename", "%总%")).list();
            for (Empinfo empinfo : list) {
                System.out.println(empinfo.getEname());
            }                            
        }

 实例2-使用实例-多个语句:

public void TestThree(){                    
        List<Dept> list=session.createCriteria(Dept.class).add(Restrictions.eq("deptname", "财务部2")).list();
        for (Dept dept : list) {
            System.out.println(dept.getDeptname());
        }        
    }
int totalCount = 0;
            List<int> alist = new List<int> { 5001536, 2, 3 };
            List<User_info> userInfoList = UserCenterBus.Select_WebSiteBase<User_info>(1, 10, User_info._USER_INFO_, User_info._ID_   " DESC", out totalCount, m => alist.Contains(m.ID));
            base.Response.Write(JsonHelper.ConvertJsonToStr(userInfoList));
@Test
    // 查询Empinfo按工资升序
    public void TestOne() {        
        List<Empinfo> list = session.createCriteria(Empinfo.class)
                .addOrder(Order.asc("ehomezipcode")).list();
        for (Empinfo empinfo : list) {
            System.out.println(empinfo.getEhomezipcode());
        }    
    }

 

View Code

http://ryanohs.com/2016/04/generating-sql-from-expression-trees-part-2/#more-394

http://stackoverflow.com/a/2616980/291955

主代码:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text;

namespace ConsoleApplication2
{
    /// <summary>
    /// Generating SQL from expression trees, Part 2
    /// http://ryanohs.com/2016/04/generating-sql-from-expression-trees-part-2/#more-394
    /// </summary>
    public class WhereBuilder
    {
        private readonly char _columnBeginChar = '[';
        private readonly char _columnEndChar = ']';
        private System.Collections.ObjectModel.ReadOnlyCollection<ParameterExpression> expressParameterNameCollection;

        public int i = 1;

        public WhereBuilder(char columnChar = '`')
        {
            this._columnBeginChar = this._columnEndChar = columnChar;
        }

        //public WhereBuilder(char columnBeginChar = '[', char columnEndChar = ']')
        //{
        //    this._columnBeginChar = columnBeginChar;
        //    this._columnEndChar = columnEndChar;
        //}

        public WherePart ToSql<T>(Expression<Func<T, bool>> expression)
        {
            if (expression.Parameters.Count > 0)
            {
                this.expressParameterNameCollection = expression.Parameters;
            }
            return Recurse(expression.Body, isUnary: true);
        }

        private WherePart Recurse(Expression expression, bool isUnary = false, string prefix = null, string postfix = null)
        {
            if (expression is UnaryExpression)
            {
                var unary = (UnaryExpression)expression;
                return WherePart.Concat(NodeTypeToString(unary.NodeType), Recurse(unary.Operand, true));
            }
            if (expression is BinaryExpression)
            {
                var body = (BinaryExpression)expression;
                return WherePart.Concat(Recurse(body.Left), NodeTypeToString(body.NodeType), Recurse(body.Right));
            }
            if (expression is ConstantExpression)
            {
                var constant = (ConstantExpression)expression;
                var value = constant.Value;
                if (value is int)
                {
                    return WherePart.IsSql(value.ToString());
                }
                if (value is string)
                {
                    value = prefix   (string)value   postfix;
                }
                if (value is bool && isUnary)
                {
                    return WherePart.Concat(WherePart.IsParameter(i  , value), "=", WherePart.IsSql("1"));
                }
                return WherePart.IsParameter(i  , value);
            }
            if (expression is MemberExpression)
            {
                var member = (MemberExpression)expression;
                var memberExpress = member.Expression;
                if (member.Member is PropertyInfo && this.IsContainsParameterExpress(member))
                {
                    var property = (PropertyInfo)member.Member;
                    //var colName = _tableDef.GetColumnNameFor(property.Name);
                    var colName = property.Name;
                    if (isUnary && member.Type == typeof(bool))
                    {
                        return WherePart.Concat(Recurse(expression), "=", WherePart.IsParameter(i  , true));
                    }
                    return WherePart.IsSql(string.Format("{0}{1}{2}", this._columnBeginChar, colName, this._columnEndChar));
                }
                if (member.Member is FieldInfo || !this.IsContainsParameterExpress(member))
                {
                    var value = GetValue(member);
                    if (value is string)
                    {
                        value = prefix   (string)value   postfix;
                    }
                    return WherePart.IsParameter(i  , value);
                }
                throw new Exception($"Expression does not refer to a property or field: {expression}");
            }
            if (expression is MethodCallExpression)
            {
                var methodCall = (MethodCallExpression)expression;
                //方法表达式需要验证调用对象是否是属性表达式&&属性表达式中的参数表达式是否是表达式参数集合中的实例(或者表达式中包含的其他表达式中的参数表达式)
                if (methodCall.Object is MemberExpression && this.IsContainsParameterExpress(methodCall))
                {
                    // LIKE queries:
                    if (methodCall.Method == typeof(string).GetMethod("Contains", new[] { typeof(string) }))
                    {
                        return WherePart.Concat(Recurse(methodCall.Object), "LIKE", Recurse(methodCall.Arguments[0], prefix: "%", postfix: "%"));
                    }
                    if (methodCall.Method == typeof(string).GetMethod("StartsWith", new[] { typeof(string) }))
                    {
                        return WherePart.Concat(Recurse(methodCall.Object), "LIKE", Recurse( methodCall.Arguments[0], postfix: "%"));
                    }
                    if (methodCall.Method == typeof(string).GetMethod("EndsWith", new[] { typeof(string) }))
                    {
                        return WherePart.Concat(Recurse(methodCall.Object), "LIKE", Recurse(methodCall.Arguments[0], prefix: "%"));
                    }
                    // IN queries:
                    if (methodCall.Method.Name == "Contains")
                    {
                        Expression collection;
                        Expression property;
                        if (methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 2)
                        {
                            collection = methodCall.Arguments[0];
                            property = methodCall.Arguments[1];
                        }
                        else if (!methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 1)
                        {
                            collection = methodCall.Object;
                            property = methodCall.Arguments[0];
                        }
                        else
                        {
                            throw new Exception("Unsupported method call: "   methodCall.Method.Name);
                        }
                        var values = (IEnumerable)GetValue(collection);
                        return WherePart.Concat(Recurse(property), "IN", WherePart.IsCollection(ref i, values));
                    }
                }
                else
                {
                    var value = GetValue(expression);
                    if (value is string)
                    {
                        value = prefix   (string)value   postfix;
                    }
                    return WherePart.IsParameter(i  , value);
                }

                throw new Exception("Unsupported method call: "   methodCall.Method.Name);
            }
            if (expression is NewExpression)
            {
                var member = (NewExpression)expression;
                var value = GetValue(member);
                if (value is string)
                {
                    value = prefix   (string)value   postfix;
                }
                return WherePart.IsParameter(i  , value);
            }
            throw new Exception("Unsupported expression: "   expression.GetType().Name);
        }

        private bool IsContainsParameterExpress(Expression expression)
        {
            bool result = false;
            if (this.expressParameterNameCollection != null && this.expressParameterNameCollection.Count > 0 && expression != null)
            {
                if (expression is MemberExpression)
                {
                    if (this.expressParameterNameCollection.Contains(((MemberExpression)expression).Expression))
                    {
                        result = true;
                    }
                }
                else if (expression is MethodCallExpression)
                {
                    MethodCallExpression methodCallExpression = (MethodCallExpression)expression;
                    if (methodCallExpression.Object != null && methodCallExpression.Object is MemberExpression)
                    {
                        MemberExpression MemberExpression = (MemberExpression)methodCallExpression.Object;
                        if (MemberExpression.Expression != null && this.expressParameterNameCollection.Contains(MemberExpression.Expression))
                        {
                            result = true;
                        }
                    }
                    if (methodCallExpression.Arguments != null && methodCallExpression.Arguments.Count > 0 && methodCallExpression.Arguments[0] is MemberExpression)
                    {
                        MemberExpression memberExpression = (MemberExpression)methodCallExpression.Arguments[0];
                        if (memberExpression.Expression != null && this.expressParameterNameCollection.Contains(memberExpression.Expression))
                        {
                            result = true;
                        }
                    }
                }
            }
            return result;
        }

        private static object GetValue(Expression member)
        {
            // source: http://stackoverflow.com/a/2616980/291955
            var objectMember = Expression.Convert(member, typeof(object));
            var getterLambda = Expression.Lambda<Func<object>>(objectMember);
            var getter = getterLambda.Compile();
            return getter();
        }

        private static string NodeTypeToString(ExpressionType nodeType)
        {
            switch (nodeType)
            {
                case ExpressionType.Add:
                    return " ";
                case ExpressionType.And:
                    return "&";
                case ExpressionType.AndAlso:
                    return "AND";
                case ExpressionType.Divide:
                    return "/";
                case ExpressionType.Equal:
                    return "=";
                case ExpressionType.ExclusiveOr:
                    return "^";
                case ExpressionType.GreaterThan:
                    return ">";
                case ExpressionType.GreaterThanOrEqual:
                    return ">=";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.LessThanOrEqual:
                    return "<=";
                case ExpressionType.Modulo:
                    return "%";
                case ExpressionType.Multiply:
                    return "*";
                case ExpressionType.Negate:
                    return "-";
                case ExpressionType.Not:
                    return "NOT";
                case ExpressionType.NotEqual:
                    return "<>";
                case ExpressionType.Or:
                    return "|";
                case ExpressionType.OrElse:
                    return "OR";
                case ExpressionType.Subtract:
                    return "-";
            }
            throw new Exception($"Unsupported node type: {nodeType}");
        }
    }

    public class WherePart
    {
        public string Sql { get; set; }
        public Dictionary<string, object> Parameters { get; set; } = new Dictionary<string, object>();

        public static WherePart IsSql(string sql)
        {
            return new WherePart()
            {
                Parameters = new Dictionary<string, object>(),
                Sql = sql
            };
        }

        public static WherePart IsParameter(int count, object value)
        {
            return new WherePart()
            {
                Parameters = { { count.ToString(), value } },
                Sql = $"@{count}"
            };
        }

        public static WherePart IsCollection(ref int countStart, IEnumerable values)
        {
            var parameters = new Dictionary<string, object>();
            var sql = new StringBuilder("(");
            foreach (var value in values)
            {
                parameters.Add((countStart).ToString(), value);
                sql.Append($"@{countStart},");
                countStart  ;
            }
            if (sql.Length == 1)
            {
                sql.Append("null,");
            }
            sql[sql.Length - 1] = ')';
            return new WherePart()
            {
                Parameters = parameters,
                Sql = sql.ToString()
            };
        }

        public static WherePart Concat(string @operator, WherePart operand)
        {
            return new WherePart()
            {
                Parameters = operand.Parameters,
                Sql = $"({@operator} {operand.Sql})"
            };
        }

        public static WherePart Concat(WherePart left, string @operator, WherePart right)
        {
            return new WherePart()
            {
                Parameters = left.Parameters.Union(right.Parameters).ToDictionary(kvp => kvp.Key, kvp => kvp.Value),
                Sql = $"({left.Sql} {@operator} {right.Sql})"
            };
        }
    }
}

二:动态查询   --根据判断用户给的条件查询

 

彩世界开奖app官网 27彩世界开奖app官网 28

参考资料:

View Code

 实例1-使用实例-单个语句:

setFirstResult(int firstResult):设置从哪一个对象开始查询,参数firstResult是个对象在查询结果中的位置,位置的起始值是0

使用LINQ生成Where的SQL语句:

实例五:查询部门表中名字有"总"子的员工 --like() ilike()

一:条件查询

实例一:分页查询按工资升序后的Empinfo表

     4.QBC检索方式:使用QBC(Query By Criteria)API来检索对象,这种API封装了基于字符串形式的查询语句,提供了更加面向对象的查询接口

彩世界开奖app官网 29彩世界开奖app官网 30

实例一:查询财务部的姓名中包括“总”的员工

彩世界开奖app官网 31彩世界开奖app官网 32

实例一:查询Empinfo表按工资升序  --addOrder(Order.asc())

Criteria接口提供了设置分页的方法

1.内连接

MatchMode类的四种模式:

实例四:查询Empinfo表的ehomezipcode(薪资)在500到100000  --Restrictions.between()

彩世界开奖app官网 33彩世界开奖app官网 34

Session session;
    Transaction tx;

    //单元测试前走
    @Before
    public void Before(){
        session= HibernateUtil.currentSession();        
         tx= session.beginTransaction();        
    }

    //单元测试后走
    @After
    public void After(){
        tx.commit();        
        HibernateUtil.closeSession();    
    }

    @SuppressWarnings("unchecked")
    @Test //使用Criteria查询所有的部门名称
    public void TestOne(){                
        Criteria criteria = session.createCriteria(Dept.class);
        List<Dept> list = criteria.list();
        for (Dept dept : list) {
            System.out.println(dept.getDeptname());
        }                            
    }

彩世界开奖app官网 35彩世界开奖app官网 36

@Test //查询没有员工的部门
        public void TestEight(){                     
            List<Dept> list=session.createCriteria(Dept.class)
                    .add(Restrictions.isEmpty("emps"))
                    .list();
            for (Dept dept : list) {
                System.out.println(dept.getDeptname());
            }
        }

  02.使用createAlias()

2.迫切左连接  --setFetchMode()

3.使用Criteria接口的list()方法执行查询语句,list()方法返回java.util.List类型的结果,List集合中的每个元素都是持久化对象。

四:分页查询

使用Criteria查询包括以下步骤:

实例一:使用Criteria查询所有的部门

@Test
    // 查询财务部的姓名中包括“总”的员工
    public void TestFour() {    
        List<Emp> list = session.createCriteria(Emp.class)
                .add(Restrictions.ilike("empname", "总", MatchMode.ANYWHERE))
                .createCriteria("dept")
                .add(Restrictions.eq("deptname", "财务部").ignoreCase()).list();
        for (Emp emp : list) {
            System.out.println(emp.getEmpname());
        }

    }

彩世界开奖app官网 37彩世界开奖app官网 38

 

首先给大家说说Hibernate检索方式

Criteria查询是Hibernate提供的另一种查询方式,与HQL基于字符串的查询形式完全不同。Hibernate提供了org.hibernate.Criteria接口,org.hibernate.criterion.Critereion接口和org.hibernate.criterion.Restrictions类等Criteria API,用于支持在运行时动态生成查询语句。

setMaxResult(int maxResult):设置一次最多查询出的对象个数

View Code

View Code

1.使用Session接口的createCriteria()方法创建Criteria对象

     3.HQL检索方式:使用面向对象的HQL查询语言

View Code

2.使用Restrictions类提供的静态方法设置查询条件,这些静态方法返回Criterion对象,一个Criterion对象代表一个查询条件。Criteria借口的add()方法用来添加查询条件。

     2.OID检索方式:按照对象的OID来检索对象

uniqueResult()方法用于查询唯一对象。需要注意,必须明确知道查询结果中只有一个对象,才可以直接使用uniqueResult()方法。

View Code

实例一:查询Emp表总记录数

HQL使用order by 字句对查询结果排序,Criteria查询使用org.hibernate.criterion.Order类对查询结果排序

 七:连接查询

View Code

    @Test //查询Empinfo的ename在中张总或王总
        public void TestSeven(){                     
            List<Empinfo> list=session.createCriteria(Empinfo.class).add(Restrictions.or(Restrictions.eq("ename", "张总"), Restrictions.eq("ename", "王总"))).list();
            for (Empinfo empinfo : list) {
                System.out.println(empinfo.getEname());
            }
        }

     1.导航对象图检索方式:根据已经加载的对象导航到其他对象

实例七:查询没有员工的部门  --Restrictions.isEmpty()

彩世界开奖app官网 39彩世界开奖app官网 40

View Code

  01.使用createCriteria()

彩世界开奖app官网 41彩世界开奖app官网 42

六:查询唯一对象  --uniqueResult()

@SuppressWarnings("unchecked")
    @Test
    public void TestSenven() {    
        DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Emp.class,"e")
                .createAlias("e.dept", "d")
                .add(Restrictions.eq("d.deptname", "财务部"));    
        List<Emp> list = detachedCriteria.getExecutableCriteria(session).list();
        for (Emp emp : list) {
            System.out.println(emp.getEmpname());
        }

    }
    @Test //查询Empinfo的ehomezipcode在500到100000
    public void TestFive(){     
        List<Empinfo> list=session.createCriteria(Empinfo.class).add(Restrictions.between("ehomezipcode", 500, 100000)).list();
        for (Empinfo empinfo : list) {
            System.out.println(empinfo.getEname());
        }            
    }

View Code

实例六:查询Empinfo的ename在中"张总"或"王总" --Restrictions.or()

View Code

实例一:查询财务部的姓名中包括“总”的员工

彩世界开奖app官网 43彩世界开奖app官网 44

实例一:查询财务部的员工

 

实例二:查询部门表为"财务部2"的部门  --Restrictions.between()

彩世界开奖app官网 45彩世界开奖app官网 46

View Code

@Test
    // 查询财务部的姓名中包括“总”的员工
    public void TestFive() {


        List<Emp> list = session.createCriteria(Emp.class, "e")
                .createAlias("dept", "d")
                .add(Restrictions.ilike("e.empname", "总", MatchMode.ANYWHERE))
                .add(Restrictions.eq("d.deptname", "财务部").ignoreCase()).list();
        for (Emp emp : list) {
            System.out.println(emp.getEmpname());
        }

    }

三:排序查询

View Code

@Test 
        public void TestNice(){                     
         Criteria criteria = session.createCriteria(Empinfo.class); 
           //00.构建出一个和page对应的条件类,依次判定条件是否为空
           Empinfo empinfo=new Empinfo();
           empinfo.setEhomecity("北京");
           empinfo.setEhomestreet("北京大街");
           empinfo.setEhomezipcode(8000);

           //1.2判定之后,动态拼接检索条件
           if (empinfo.getEhomecity()!=null) {
               //用户填写了城市作为检索条件
               criteria.add(Restrictions.eq("ehomecity", empinfo.getEhomecity()));
           }
           if(empinfo.getEhomeprovince()!=null){
               //用户填写薪资作为筛选条件
               criteria.add(Restrictions.gt("ehomeprovince", empinfo.getEhomeprovince()));
           }
           if (empinfo.getEhomestreet()!=null) {
               //用户填写街道作为筛选条件
               criteria.add(Restrictions.eq("ehomestreet", empinfo.getEhomestreet()));
            }
            List<Empinfo> list = criteria.list();
            for (Empinfo emp : list) {
                System.out.println(emp.getEname());
            }

        }

实例三:查询部门表的deptname为"财务部2"和"财务部"的名称(链式编程风格)  --Restrictions.in()

@Test //查询Dept的deptname为财务部2和财务部的名称(链式编程风格)
    public void TestFour(){        
        List jobList=new ArrayList<String>();//定义一个数组
        jobList.add("财务部");
        jobList.add("财务部2");
        List<Dept> list=session.createCriteria(Dept.class).add(Restrictions.in("deptname", jobList)).list();
        for (Dept dept : list) {
            System.out.println(dept.getDeptname());
        }                    
    }

彩世界开奖app官网 47彩世界开奖app官网 48

@Test
    // 查询部门为“财务部”的员工个数
    public void TestSix() {
        List<Dept> list = session.createCriteria(Dept.class, "d")
                .setFetchMode("emps", FetchMode.JOIN)
                .add(Restrictions.eq("deptname", "财务部"))
                .list();
        //使用Set接口的实现类HashSet来过滤重复的数据(HashSet中不会存储相同的值)
        HashSet<Dept> set=new HashSet<Dept>(list);
        for (Dept dept : set) {
            System.out.println(dept.getDeptname() "," dept.getEmps().size());
        }    
    }
    @Test
    // 分页查询Empinfo表
    public void TestTwo() {    
        List<Empinfo> list = session.createCriteria(Empinfo.class)
                .addOrder(Order.asc("ehomezipcode"))
                .setFirstResult(0)
                .setMaxResults(2).list();
        for (Empinfo empinfo : list) {
            System.out.println(empinfo.getEhomezipcode());
        }
    }

View Code

  Hibernate提供了5种检索对象的方式

彩世界开奖app官网 49彩世界开奖app官网 50

View Code

升序:addOrder(Order.asc())

1.薪资大于8000

2.街道是"北京大街"

实例一:Empinfo表中查询条件可为:

降序:addOrder(Order.desc())

实例一:查询部门为“财务部”的员工个数

本文由彩世界开奖app官网发布于计算机编程,转载请注明出处:Hibernate框架之Criteria查询【彩世界开奖app官网】

关键词: entityframew