querydaobean.java

精通EJB3源码

            int personid = Integer.parseInt(row[0].toString());
            String PersonName = row[1].toString(); 
            out.append("personid="+ personid+ "; PersonName="+PersonName+ "<BR>");    
        }
        return out.toString();
    }
    
    private String QueryConstructor(){
        //我们把需要的两个属性作为SimplePerson的构造器参数，并使用new函数。
        Query query = em.createQuery("select new com.foshanshop.ejb3.bean.SimplePerson(p.name,p.sex) from Person p order by p.personid desc");
        //集合中的元素是SimplePerson对象
        List<SimplePerson> result = (List<SimplePerson>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryConstructor 结果打印 ****************<BR>");
        for(SimplePerson simpleperson : result){
            out.append("人员介绍："+ simpleperson.getDescription()+ "<BR>");    
        }
        return out.toString();     
    }
    
    private String QueryAggregation(){
        //获取最大年龄
        Query query = em.createQuery("select max(p.age) from Person p");
        Object result = query.getSingleResult();        
        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(p) 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(p) from Person p group by p.sex");
        //集合中的元素不再是Person,而是一个Object[]对象数组
        List<Object[]> result = (List<Object[]>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryGroupBy 结果打印 ****************<BR>");
        for(Object[] row : result){//取每一行
            //数组中的第一个值是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(p) from Person p group by p.sex having count(p)>?1");
        //设置查询中的参数
        query.setParameter(1, new Long(1));
        //集合中的元素不再是Person,而是一个Object[]对象数组
        List<Object[]> result = (List<Object[]>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryGroupByHaving 结果打印 ****************<BR>");
        for(Object[] row : result){//取每一行
            //数组中的第一个值是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<Order> result = (List<Order>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryLeftJoin 结果打印 ****************<BR>");
        Integer orderid = null;
        for(Order order : result){
            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<Order> result = (List<Order>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryInnerJoin 结果打印 ****************<BR>");
        Integer orderid = null;
        for(Order order : result){
            if (orderid==null || !orderid.equals(order.getOrderid())){
                orderid = order.getOrderid();
                out.append("订单号："+ orderid+ "<BR>");
            }
        }
        return out.toString(); 
    }   
 
    private String QueryInnerJoinLazyLoad(){
        Query query = em.createQuery("select o from Order o inner join o.orderItems where o.ower.age=26 order by o.orderid");
        List<Order> result = (List<Order>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryInnerJoinLazyLoad 结果打印 ****************<BR>");
        if( result.size()>0){
            //这时获得的Order实体中orderItems为空
            Order order = result.get(0);
            //当应用需要时，EJB3 Runtime才会执行一条SQL语句来加载属于当前Order的OrderItems
            for(OrderItem orderItem : order.getOrderItems()){
                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<Order> result = (List<Order>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryJoinFetch 结果打印 ****************<BR>");
        Integer orderid = null;
        for(Order order : result){
            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<Order> result = (List<Order>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryEntityParameter 结果打印 ****************<BR>");
        for(Order order : result){
        	out.append("订单号："+ order.getOrderid()+ "<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 OrderItem item where item.order in(select o from Order as o where o.amount<100)");
        query.executeUpdate();        
        query = em.createQuery("delete from Order as o where o.amount<100");
        int result = query.executeUpdate();//delete的记录数
        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<Order> result = (List<Order>) query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryNOTOperate 结果打印 ****************<BR>");
        for(Order order : result){
        	out.append("订单号："+ order.getOrderid()+ "<BR>");
        }
        return out.toString();       
    }
 
    private String QueryBETWEENOperate(){
    	StringBuffer out = new StringBuffer("*************** QueryBETWEENOperate 结果打印 ****************<BR>");
        //查询金额在300到1000之间的订单
        Query query = em.createQuery("select o from Order as o where o.amount between 300 and 1000");
        List<Order> result = (List<Order>) query.getResultList();
        for(Order order : result){
             out.append("订单号："+ order.getOrderid()+ "<BR>");
        }
        return out.toString();       
    }
    
    private String QueryINOperate(){
    	StringBuffer out = new StringBuffer("*************** QueryINOperate 结果打印 ****************<BR>");
        //查找年龄为26,21的Person
        Query query = em.createQuery("select p from Person as p where p.age in(26,21)");
        List<Person> result = (List<Person>) query.getResultList();        
        for(Person person : result){          
            out.append(person.getName()+ "<BR>");            
        }
        return out.toString();
    }
    
    private String QueryLIKEOperate(){
    	StringBuffer out = new StringBuffer("*************** QueryLIKEOperate 结果打印 ****************<BR>");
    	out.append("---------- 查找以字符串\"li\"开头的Person ----------<BR>");
        Query query = em.createQuery("select p from Person as p where p.name like 'li%'");
        List<Person> result = (List<Person>) query.getResultList();
        for(Person person : result){
            out.append(person.getName()+ "<BR>");            
        }
        out.append("---------- 查询所有name不以字符串\"ming\"结尾的Person ----------<BR>");
        query = em.createQuery("select p from Person as p where p.name not like '%ming'");
        result = (List<Person>) query.getResultList();
    	for(Person person : result){