querydaobean.java

ejb3实例教程全部配书源码

            while( iterator.hasNext() ){
                Person person= (Person)iterator.next();            
                out.append(person.getName()+ "<BR>");            
            }
        }           
        return out.toString();
    }
    
    private String QueryISNULLOperate(){
        //查询含有购买者的所有Order
        Query query = em.createQuery("select o from Order as o where o.ower is not null order by o.orderid");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryISNULLOperate 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            out.append("--------------- 查询含有购买者的所有Order -------------<BR>");
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                out.append("订单号："+ order.getOrderid()+ "<BR>");
            }
        }
        //查询没有购买者的所有Order
        query = em.createQuery("select o from Order as o where o.ower is null order by o.orderid");
        result = query.getResultList();
        if (result!=null){
            Iterator iterator = result.iterator();
            out.append("--------------- 查询没有购买者的所有Order -------------<BR>");
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                out.append("订单号："+ order.getOrderid()+ "<BR>");
            }
        }
        
        return out.toString();       
    }
    
    private String QueryISEMPTYOperate(){
        //查询含有订单项的所有Order
        Query query = em.createQuery("select o from Order as o where o.orderItems is not empty order by o.orderid");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryISEMPTYOperate 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            out.append("---------------  查询含有订单项的所有Order -------------<BR>");
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                out.append("订单号："+ order.getOrderid()+ "<BR>");
            }
        }
        //查询没有订单项的所有Order
        query = em.createQuery("select o from Order as o where o.orderItems is empty order by o.orderid");
        result = query.getResultList();
        if (result!=null){
            Iterator iterator = result.iterator();
            out.append("--------------- 查询没有订单项的所有Order -------------<BR>");
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                out.append("订单号："+ order.getOrderid()+ "<BR>");
            }
        }
        
        return out.toString();       
    }  
    
    private String QueryEXISTSOperate(){
        //如果存在订单号1，就获取所有OrderItem
        Query query = em.createQuery("select oi from OrderItem as oi where exists (select o from Order o where o.orderid=1)");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryEXISTSOperate 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            out.append("--------------- 如果存在订单号1，就获取所有OrderItem -------------<BR>");
            while( iterator.hasNext() ){
                OrderItem item = (OrderItem) iterator.next();
                out.append("所有订购的产品名："+ item.getProductname()+ "<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)");
        result = query.getResultList();
        if (result!=null){
            Iterator iterator = result.iterator();
            out.append("--------------- 如果不存在订单号10，就获取id为1的OrderItem -------------<BR>");
            if( iterator.hasNext() ){
                OrderItem item = (OrderItem) iterator.next();
                out.append("订单项ID为1的订购产品名："+ item.getProductname()+ "<BR>");
            }
        }
        
        return out.toString();       
    }
    
    private String QueryStringOperate(){
        //查询所有人员，并在姓名后面加上字符串"_foshan"
        Query query = em.createQuery("select p.personid, concat(p.name, '_foshan') from Person as p");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryStringOperate 结果打印 ****************<BR>");
        if (result!=null){
            out.append("---------- 查询所有人员，并在姓名后面加上字符串\"_foshan\" ----------<BR>");
            Iterator iterator = result.iterator();            
            while( iterator.hasNext() ){
                //取每一行
                Object[] row = ( Object[]) iterator.next();
                //数组中的第一个值是personid
                int personid = Integer.parseInt(row[0].toString());
                String PersonName = row[1].toString(); 
                out.append("personid="+ personid+ "; Person Name="+PersonName+ "<BR>");    
            }
        }  
        
        //查询所有人员,只取姓名的前三个字符
        query = em.createQuery("select p.personid, substring(p.name,1,3) from Person as p");
        result = query.getResultList();
        if (result!=null){
            out.append("---------- 查询所有人员,只取姓名的前三个字符 ----------<BR>");
            Iterator iterator = result.iterator();            
            while( iterator.hasNext() ){
                //取每一行
                Object[] row = ( Object[]) iterator.next();
                //数组中的第一个值是personid
                int personid = Integer.parseInt(row[0].toString());
                String PersonName = row[1].toString(); 
                out.append("personid="+ personid+ "; Person Name="+PersonName+ "<BR>");    
            }
        } 
        return out.toString();
    }  
    
    private String QueryMathLOperate(){
        //查询所有Order的订单号及其订单项的数量
        Query query = em.createQuery("select o.orderid, size(o.orderItems) from Order as o group by o.orderid");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryMathLOperate 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            out.append("--------------- 查询所有Order的订单号及其订单项的数量 -------------<BR>");
            while( iterator.hasNext() ){
                //取每一行
                Object[] row = ( Object[]) iterator.next(); 
                out.append("订单号："+ row[0].toString()+ "; 订单项共"+row[1].toString()+ "项<BR>");  
            }
        }
        //查询所有Order的订单号及其总金额/10的余数
        query = em.createQuery("select o.orderid, mod(o.amount, 10) from Order as o");
        result = query.getResultList();
        if (result!=null){
            Iterator iterator = result.iterator();
            out.append("--------------- 查询所有Order的订单号及其总金额/10的余数 -------------<BR>");
            while( iterator.hasNext() ){
                //取每一行
                Object[] row = ( Object[]) iterator.next(); 
                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 result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QuerySubQueryOperate 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            out.append("--------------- 查询年龄为26岁的购买者的所有Order -------------<BR>");
            while( iterator.hasNext() ){
                Order order = (Order) iterator.next();
                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 result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryStoreProcedure 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            while( iterator.hasNext() ){
                Person person= (Person)iterator.next();            
                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 result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryPartColumnStoreProcedure 结果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();            
            while( iterator.hasNext() ){
                //取每一行
                Object[] row = ( Object[]) iterator.next();
                //数组中的第一个值是personid
                int personid = Integer.parseInt(row[0].toString());
                String PersonName = row[1].toString(); 
                out.append("人员ID="+ personid+ "; 姓名="+PersonName+ "<BR>");    
            }
        } 
        return out.toString();       
    }

}