querydaobean.java

精通EJB3源码

            out.append(person.getName()+ "<BR>");            
        }          
        return out.toString();
    }
    
    private String QueryISNULLOperate(){
    	StringBuffer out = new StringBuffer("*************** QueryISNULLOperate 结果打印 ****************<BR>");
    	out.append("--------------- 查询含有购买者的所有Order -------------<BR>");
        Query query = em.createQuery("select o from Order as o where o.ower is not null order by o.orderid");
        List<Order> result = (List<Order>) query.getResultList();
        for(Order order : result){
            out.append("订单号："+ order.getOrderid()+ "<BR>");
        }
        out.append("--------------- 查询没有购买者的所有Order -------------<BR>");
        query = em.createQuery("select o from Order as o where o.ower is null order by o.orderid");
        result = (List<Order>) query.getResultList();
        for(Order order : result){
            out.append("订单号："+ order.getOrderid()+ "<BR>");
        }       
        return out.toString();       
    }
    
    private String QueryISEMPTYOperate(){
    	StringBuffer out = new StringBuffer("*************** QueryISEMPTYOperate 结果打印 ****************<BR>");
    	out.append("--------------- 查询含有订单项的所有Order -------------<BR>");
        Query query = em.createQuery("select o from Order as o where o.orderItems is not empty order by o.orderid");
        List<Order> result = (List<Order>) query.getResultList();        
        for(Order order : result){
            out.append("订单号："+ order.getOrderid()+ "<BR>");
        }
        out.append("--------------- 查询没有订单项的所有Order -------------<BR>");
        query = em.createQuery("select o from Order as o where o.orderItems is empty order by o.orderid");
        result = (List<Order>) query.getResultList();
        for(Order order : result){
            out.append("订单号："+ order.getOrderid()+ "<BR>");
        }       
        return out.toString();       
    }  
    
    private String QueryEXISTSOperate(){
        StringBuffer out = new StringBuffer("*************** QueryEXISTSOperate 结果打印 ****************<BR>");
        out.append("--------------- 如果存在订单号1，就获取所有OrderItem -------------<BR>");
        //如果存在订单号1，就获取所有OrderItem
        Query query = em.createQuery("select oi from OrderItem as oi where exists (select o from Order o where o.orderid=1)");
        List<OrderItem> result = (List<OrderItem>) query.getResultList();
        for(OrderItem item : result){
            out.append("所有订购的产品名："+ item.getProductname()+ "<BR>");
        }
        out.append("--------------- 如果不存在订单号10，就获取id为1的OrderItem -------------<BR>");
        //如果不存在订单号10，就获取id为1的OrderItem
        query = em.createQuery("select oi from OrderItem as oi where oi.id=1 and not exists (select o from Order o where o.orderid=10)");
        OrderItem item = (OrderItem) query.getSingleResult();
        if(item!=null){
            out.append("订单项ID为1的订购产品名："+ item.getProductname()+ "<BR>");
        }        
        return out.toString();       
    }
    
    private String QueryStringOperate(){
    	StringBuffer out = new StringBuffer("*************** QueryStringOperate 结果打印 ****************<BR>");
    	out.append("---------- 查询所有人员，并在姓名后面加上字符串\"_foshan\",另外从字符常量ABC中去除C ----------<BR>");
        Query query = em.createQuery("select p.personid, concat(p.name, '_foshan'),trim(TRAILING 'C' FROM 'ABC') from Person as p");
        List<Object[]> result = (List<Object[]>) query.getResultList();
        for(Object[] row : result){//取每一行
            out.append("personid="+ row[0]+ "; PersonName="+ row[1]+ "; ABC去除后面的C="+ row[2]+ "<BR>");    
        }
        out.append("---------- 查询所有人员,只取姓名的前三个字符 ----------<BR>");
        query = em.createQuery("select p.personid, substring(p.name,1,3) from Person as p");
        result = (List<Object[]>) query.getResultList();
        for(Object[] row : result){//取每一行
            out.append("personid="+ row[0]+ "; PersonName="+ row[1]+ "<BR>");    
        }
        return out.toString();
    }  
    
    private String QueryMathLOperate(){
        StringBuffer out = new StringBuffer("*************** QueryMathLOperate 结果打印 ****************<BR>");
        out.append("--------------- 查询所有Order的订单号及其订单项的数量 -------------<BR>");
        Query query = em.createQuery("select o.orderid, size(o.orderItems) from Order as o group by o.orderid");
        List<Object[]> result = (List<Object[]>)query.getResultList();
        for(Object[] row : result){//取每一行
            out.append("订单号："+ row[0].toString()+ "; 订单项共"+row[1].toString()+ "项<BR>");  
        }
        out.append("--------------- 查询所有Order的订单号及其总金额/10的余数 -------------<BR>");
        query = em.createQuery("select o.orderid, mod(o.amount, 10) from Order as o");
        result = (List<Object[]>)query.getResultList();
        for(Object[] row : result){//取每一行
            out.append("订单号："+ row[0].toString()+ "; 总金额/10的余数:"+row[1].toString()+ "<BR>");  
        }
        return out.toString();
    }
  
    private String QuerySubQueryOperate(){
        //查询年龄为26岁的购买者的所有Order
        Query query = em.createQuery("select o from Order as o where o.ower in(select p from Person as p where p.age =26) order by o.orderid");
        List<Order> result = (List<Order>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QuerySubQueryOperate 结果打印 ****************<BR>");
        out.append("--------------- 查询年龄为26岁的购买者的所有Order -------------<BR>");
        for(Order order : result){
            out.append("订单号："+ order.getOrderid()+ "<BR>");
        }     
        return out.toString();       
    }
 
    /* Mysql数据库AddPerson存储过程的DDL:
     * 
        CREATE PROCEDURE `AddPerson`()
            NOT DETERMINISTIC
            SQL SECURITY DEFINER
            COMMENT ''
        BEGIN
             INSERT into person(`PersonName`,`sex`,`age`) values('存储过程',1,25);
        END;
     */
    private String QueryNoneReturnValueStoreProcedure(){
        //调用无返回参数的存储过程
        Query query = em.createNativeQuery("{call AddPerson()}");
        query.executeUpdate();
        StringBuffer out = new StringBuffer("*************** QueryNoneReturnValueStoreProcedure 结果打印 ****************<BR>");
        return out.toString();       
    }
 
    /* Mysql数据库GetPersonName存储过程的DDL:
     * 
        CREATE PROCEDURE `GetPersonName`(IN Pid INTEGER(11))
            NOT DETERMINISTIC
            SQL SECURITY DEFINER
            COMMENT ''
        BEGIN
            select personname from person where `personid`=Pid;
        END;
     */
    private String QuerySingleObjectStoreProcedure(){
        //调用返回单个值的存储过程
        Query query = em.createNativeQuery("{call GetPersonName(?)}");
        query.setParameter(1, new Integer(1));
        String result = query.getSingleResult().toString();
        StringBuffer out = new StringBuffer("*************** QuerySingleObjectStoreProcedure 结果打印 ****************<BR>");
        out.append("返回值(人员姓名)为："+ result+ "<BR>");
        return out.toString();       
    }
    
    /* Mysql数据库GetPersonList存储过程的DDL:
     * 
        CREATE PROCEDURE `GetPersonList`()
            NOT DETERMINISTIC
            SQL SECURITY DEFINER
            COMMENT ''
        BEGIN
             select * from person;
        END;
     */
    private String QueryStoreProcedure(){
        //调用返回Person全部属性的存储过程
        Query query = em.createNativeQuery("{call GetPersonList()}", Person.class);
        List<Person> result = (List<Person>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryStoreProcedure 结果打印 ****************<BR>");
        for(Person person : result){           
            out.append(person.getName()+ "<BR>");            
        }
        return out.toString();       
    }
 
    /* Mysql数据库GetPersonPartProperties存储过程的DDL:
     * 
        CREATE PROCEDURE `GetPersonPartProperties`()
            NOT DETERMINISTIC
            SQL SECURITY DEFINER
            COMMENT ''
        BEGIN
             SELECT personid, personname from person;
        END;
     */
    private String QueryPartColumnStoreProcedure(){
        //调用返回记录集部分列的存储过程
        Query query = em.createNativeQuery("{call GetPersonPartProperties()}");
        List<Object[]> result = (List<Object[]>) query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryPartColumnStoreProcedure 结果打印 ****************<BR>");
        for(Object[] row : result){//取每一行
            out.append("人员ID="+ row[0]+ "; 姓名="+ row[1]+ "<BR>");    
        }
        return out.toString();       
    }

    private String QueryAllAnySomeOperate(){
        //查询订单中每个子项都大于100元的所有Order
        Query query = em.createQuery("select o from Order o where 100<ALL(select item.price from o.orderItems item)");
        List<Order> result = (List<Order>)query.getResultList();
        StringBuffer out = new StringBuffer("*************** QuerySubQueryOperate 结果打印 ****************<BR>");
        out.append("--------------- 查询订单中,每个子项都大于100元的所有Order -------------<BR>");
        for(Order order : result){
            out.append("订单号："+ order.getOrderid()+ "<BR>");
        }
        
        //查询订单中有一个子项大于700元的所有Order
        query = em.createQuery("select o from Order o where 700<ANY(select item.price from o.orderItems item)");
        result = (List<Order>)query.getResultList();
        out.append("--------------- 查询订单中，只要有一个子项大于700元的所有Order -------------<BR>");
        for(Order order : result){
            out.append("订单号："+ order.getOrderid()+ "<BR>");
        }
        
        //查询订单中有一个子项大于700元的所有Order
        query = em.createQuery("select o from Order o where 700<SOME(select item.price from o.orderItems item)");
        result = (List<Order>)query.getResultList();
        out.append("--------------- 查询订单中,只有一个或多个子项大于700元的所有Order -------------<BR>");
        for(Order order : result){
            out.append("订单号："+ order.getOrderid()+ "<BR>");
        }
        return out.toString();       
    }
    
    private String QueryMemberOf(){
        //查询含有某个订单项的订单
        Query query = em.createQuery("select o from Order o where ?1 member of o.orderItems");
        OrderItem item = new OrderItem();
        item.setId(1);
        query.setParameter(1, item);
        List<Order> result = (List<Order>) query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryMemberOf 结果打印 ****************<BR>");
        for(Order order : result){
            out.append(order.getOrderid()+ "<BR>");            
        }
        return out.toString();       
    }
}