querydaobean.java

ejb3实例教程全部配书源码

        String maxAge = result.toString(); 
        //获取平均年龄
        query = em.createQuery("select avg(p.age) from Person p");
        result = query.getSingleResult();        
        String avgAge = result.toString(); 
        //获取最小年龄
        query = em.createQuery("select min(p.age) from Person p");
        result = query.getSingleResult();        
        String minAge = result.toString(); 
        //获取总人数
        query = em.createQuery("select count(*) from Person p");
        result = query.getSingleResult();        
        String countperson = result.toString(); 
        //获取年龄总和
        query = em.createQuery("select sum(p.age) from Person p");
        result = query.getSingleResult();        
        String sumage = result.toString();         
        
        StringBuffer out = new StringBuffer("*************** QueryConstructor 结果打印 ****************<BR>");
        out.append("最大年龄："+ maxAge+ "<BR>");
        out.append("平均年龄："+ avgAge+ "<BR>");
        out.append("最小年龄："+ minAge+ "<BR>");
        out.append("总人数："+ countperson+ "<BR>");
        out.append("年龄总和："+ sumage+ "<BR>");        
        return out.toString(); 
    }
 
    private String QueryGroupBy(){
        //返回男女生各自的总人数
        Query query = em.createQuery("select p.sex, count(*) from Person p group by p.sex");
        //集合中的元素不再是Person,而是一个Object[]对象数组
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryGroupBy 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            while( iterator.hasNext() ){
                //取每一行
                Object[] row = (Object[]) iterator.next();
                //数组中的第一个值是sex
                boolean sex = Boolean.parseBoolean(row[0].toString());
                //数组中的第二个值是聚合函数COUNT返回值
                String sextotal = row[1].toString();
                out.append((sex ? "男生":"女生")+ "总共有"+ sextotal+ "人<BR>");
            }
        }
        return out.toString(); 
    }
    
    private String QueryGroupByHaving(){
        //返回人数超过1人的性别
        Query query = em.createQuery("select p.sex, count(*) from Person p group by p.sex having count(*) >?1");
        //设置查询中的参数
        query.setParameter(1, new Long(1));
        //集合中的元素不再是Person,而是一个Object[]对象数组
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryGroupByHaving 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            while( iterator.hasNext() ){
                //取每一行
                Object[] row = (Object[]) iterator.next();
                //数组中的第一个值是sex
                boolean sex = Boolean.parseBoolean(row[0].toString());
                //数组中的第二个值是聚合函数COUNT返回值
                String sextotal = row[1].toString();
                out.append((sex ? "男生":"女生")+ "总共有"+ sextotal+ "人<BR>");
            }
        }
        return out.toString(); 
    }
    
    private String QueryLeftJoin(){
        //获取26岁人的订单,不管Order中是否有OrderItem
        Query query = em.createQuery("select o from Order o left join o.orderItems where o.ower.age=26 order by o.orderid");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryLeftJoin 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            Integer orderid = null;
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                if (orderid==null || !orderid.equals(order.getOrderid())){
                    orderid = order.getOrderid();
                    out.append("订单号："+ orderid+ "<BR>");
                }
            }
        }
        return out.toString(); 
    }
 
    private String QueryInnerJoin(){
        //获取26岁人的订单,Order中必须要有OrderItem
        Query query = em.createQuery("select o from Order o inner join o.orderItems where o.ower.age=26 order by o.orderid");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryInnerJoin 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            Integer orderid = null;
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                if (orderid==null || !orderid.equals(order.getOrderid())){
                    orderid = order.getOrderid();
                    out.append("订单号："+ orderid+ "<BR>");
                }
            }
        }
        return out.toString(); 
    }   
 
    private String QueryInnerJoinLazyLoad(){
        // 默认EJB3 QL编译后不关联集合属性变量(orderItems)对应的表
        Query query = em.createQuery("select o from Order o inner join o.orderItems where o.ower.age=26 order by o.orderid");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryInnerJoinLazyLoad 结果打印 ****************<BR>");
        if (result!=null){
            if( result.size()>0){
                //这时获得Order实体中orderItems( 集合属性变量 )为空
                Order order = (Order) result.get(0);
                //当应用需要时，EJB3 Runtime才会执行一条SQL语句来加载属于当前Order的OrderItems
                Set<OrderItem> list = order.getOrderItems();
                Iterator<OrderItem> iterator = list.iterator();
                if (iterator.hasNext()){
                    OrderItem orderItem =iterator.next();
                    out.append("订购产品名："+ orderItem.getProductname()+ "<BR>");
                }
            }
        }
        return out.toString(); 
    }   
    
    private String QueryJoinFetch(){
        //获取26岁人的订单,Order中必须要有OrderItem
        Query query = em.createQuery("select o from Order o inner join fetch o.orderItems where o.ower.age=26 order by o.orderid");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryJoinFetch 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            Integer orderid = null;
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                if (orderid==null || !orderid.equals(order.getOrderid())){
                    orderid = order.getOrderid();
                    out.append("订单号："+ orderid+ "<BR>");
                }
            }
        }
        return out.toString(); 
    }    
 
    private String QueryEntityParameter(){
        //查询某人的所有订单
        Query query = em.createQuery("select o from Order o where o.ower =?1 order by o.orderid");
        Person person = new Person();
        person.setPersonid(new Integer(1));
        //设置查询中的参数
        query.setParameter(1,person);
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryEntityParameter 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            Integer orderid = null;
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                if (orderid==null || !orderid.equals(order.getOrderid())){
                    orderid = order.getOrderid();
                    out.append("订单号："+ orderid+ "<BR>");
                }
            }
        }
        return out.toString(); 
    }   
  
    private String QueryBatchUpdate(){
        //把所有订单的金额加10
        Query query = em.createQuery("update Order as o set o.amount=o.amount+10");
        //update的记录数
        int result = query.executeUpdate();
        StringBuffer out = new StringBuffer("*************** QueryBatchUpdate 结果打印 ****************<BR>");
        out.append("更新操作影响的记录数："+ result+ "条<BR>");
        return out.toString(); 
    } 
 
    private String QueryBatchRemove(){
        //把金额小于100的订单删除
        Query query = em.createQuery("delete from Order as o where o.amount <100");
        //delete的记录数
        int result = query.executeUpdate();
        StringBuffer out = new StringBuffer("*************** QueryBatchRemove 结果打印 ****************<BR>");
        out.append("删除操作影响的记录数："+ result+ "条<BR>");
        return out.toString(); 
    } 
    
    private String QueryNOTOperate(){
        //查询除了指定人之外的所有订单
        Query query = em.createQuery("select o from Order o where not(o.ower =?1) order by o.orderid");
        Person person = new Person();
        person.setPersonid(new Integer(2));
        //设置查询中的参数
        query.setParameter(1,person);
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryNOTOperate 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            Integer orderid = null;
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                if (orderid==null || !orderid.equals(order.getOrderid())){
                    orderid = order.getOrderid();
                    out.append("订单号："+ orderid+ "<BR>");
                }
            }
        }
        return out.toString();       
    }
 
    private String QueryBETWEENOperate(){
        //查询金额在300到1000之间的订单
        Query query = em.createQuery("select o from Order as o where o.amount between 300 and 1000");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryBETWEENOperate 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            Integer orderid = null;
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                if (orderid==null || !orderid.equals(order.getOrderid())){
                    orderid = order.getOrderid();
                    out.append("订单号："+ orderid+ "<BR>");
                }
            }
        }
        return out.toString();       
    }
    
    private String QueryINOperate(){
        //查找年龄为26,21的Person
        Query query = em.createQuery("select p from Person as p where p.age in(26,21)");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryINOperate 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            while( iterator.hasNext() ){
                Person person= (Person)iterator.next();            
                out.append(person.getName()+ "<BR>");            
            }
        }
        return out.toString();
    }
    
    private String QueryLIKEOperate(){
        //查找以字符串"li"开头的Person
        Query query = em.createQuery("select p from Person as p where p.name like 'li%'");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryLIKEOperate 结果打印 ****************<BR>");
        if (result!=null){
            out.append("---------- 查找以字符串\"li\"开头的Person ----------<BR>");
            Iterator iterator = result.iterator();
            while( iterator.hasNext() ){
                Person person= (Person)iterator.next();            
                out.append(person.getName()+ "<BR>");            
            }
        }
        //可以结合NOT一起使用，比如查询所有name不以字符串"ming"结尾的Person
        query = em.createQuery("select p from Person as p where p.name not like '%ming'");
        result = query.getResultList();
        if (result!=null){
            out.append("---------- 查询所有name不以字符串\"ming\"结尾的Person ----------<BR>");
            Iterator iterator = result.iterator();