querydaobean.java

jboss4 + Mysql +ejb3.0 EJB3 简单例子

        // 查询所有人员，并在姓名后面加上字符串"_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结果打印*******CONCAT 字符拼接******<BR>");
        if(result!=null){
            Iterator it = result.iterator();
            out.append("-----------查询所有人员，并在姓名后面加上字符串\"_foshan\"--------<BR>");
            while(it.hasNext()){
                // 取每一行
                Object[]row = (Object[])it.next();
                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){
            Iterator it = result.iterator();
            out.append("-----------查询所有人员，只取姓名的前三个字符。 SUBSTRING 字符串截取--------<BR>");
            while(it.hasNext()){
                // 取每一行
                Object[]row = (Object[])it.next();
                int personid = Integer.parseInt(row[0].toString());
                String personName = row[1].toString();
                out.append("personid="+personid+";Person Name="+personName+"<BR>");
            }
        }
        return out.toString();
    }
    /**
     * 使用操作符Exists
     */
    private String QueryExistsOperate(){
        // 如果存在订单号为1的订单，就获取所有OrderItem
        Query query = em.createQuery("select oi from OrderItem as oi where exists(select o from Orders o where o.orderid = 1)");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("***************QueryExistsOperate结果打印*************<BR>");
        if(result!=null){
            Iterator it = result.iterator();
            out.append("-----------如果存在订单号1，就获取所有OrderItem--------<BR>");
            while(it.hasNext()){
                OrderItem item = (OrderItem)it.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 Orders o where o.orderid = 10)");
        result = query.getResultList();
        if(result!=null){
            Iterator it = result.iterator();
            out.append("-----------如果不存在订单号为10的订单，就获取id为1的OrderItem--------<BR>");
            while(it.hasNext()){
                OrderItem item = (OrderItem)it.next();
                out.append("订单项ID为1的订购的产品名："+item.getProductname()+"<BR>");
            }
        }
        return out.toString();
    }
    /**
     * 使用操作符IS Empty
     */
    private String QueryISEmptyOperate(){
        // 查询含有订单项的所有Orders
        Query query = em.createQuery("select o from Orders 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 it = result.iterator();
            out.append("-----------查询含有订单项的所有Orders--------<BR>");
            while(it.hasNext()){
                Orders order = (Orders)it.next();
                out.append("订单号："+order.getOrderid()+"<BR>");
            }
        }
        query = em.createQuery("select o from Orders as o where o.orderItems is empty order by o.orderid");
        result = query.getResultList();
        if(result!=null){
            Iterator it = result.iterator();
            out.append("-----------查询没有订单项的所有Orders--------<BR>");
            while(it.hasNext()){
                Orders order = (Orders)it.next();
                out.append("订单号："+order.getOrderid()+"<BR>");
            }
        }
        return out.toString();
    }
    /**
     * 使用操作符IS NULL
     */
    private String QueryISNULLOperate(){
        // 查询含有购买者的所有Orders
        Query query = em.createQuery("select o from Orders 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 it = result.iterator();
            out.append("-----------查找含有购买者的所有Order--------<BR>");
            while(it.hasNext()){
                Orders order = (Orders)it.next();
                out.append("订单号："+order.getOrderid()+"<BR>");
            }
        }
        // 查询没有购买者的所Order
        query = em.createQuery("select o from Orders as o where o.ower is null order by o.orderid");
        result = query.getResultList();

        if(result!=null){
            Iterator it = result.iterator();
            out.append("-----------查找没有购买者的所有Order--------<BR>");
            while(it.hasNext()){
                Orders order = (Orders)it.next();
                out.append("订单号："+order.getOrderid()+"<BR>");
            }
        }
        return out.toString();
    }

    /**
     * 使用操作符Like
     */
    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 it = result.iterator();
            while(it.hasNext()){
                Person person = (Person)it.next();
                out.append(person.getName()+"<BR>");                
            }
        }
        // 可以结合一起使用，比如查询所有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 it = result.iterator();
            while(it.hasNext()){
                Person person = (Person)it.next();
                out.append(person.getName()+"<BR>");                
            }
        }
        return out.toString();
    }
    /**
     * 使用操作符IN
     */
    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 it = result.iterator();
            while(it.hasNext()){
                Person person = (Person)it.next();
                out.append(person.getName()+"<BR>");
            }
        }
        return out.toString();
    }
    
    /**
     * 使用操作符Between
     */
    private String QueryBetweenOperate(){
        // 查询金额在300到1000之间的订单
        Query query = em.createQuery("select o from Orders o where o.amount between 300 and 1000");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("***************QueryBetweenOperate结果打印*************<BR>");
        if(result!=null){
            Iterator it = result.iterator();
            Integer orderid = null;
            while(it.hasNext()){
                Orders order = (Orders)it.next();
                if(orderid==null||orderid.equals(order.getOrderid())){
                    orderid = order.getOrderid();
                    out.append("订单号："+orderid+"<BR>");                    
                }
            }
        }
        return out.toString();
    }
    /**
     * 使用操作符NOT
     */
    private String QueryNotOperate(){
        // 查询除了指定的人之外的所有订单
        Query query = em.createQuery("select o from Orders 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 it = result.iterator();
            Integer orderid = null;
            while(it.hasNext()){
                Orders order = (Orders)it.next();
                if(orderid==null||orderid.equals(order.getOrderid())){
                    orderid = order.getOrderid();
                    out.append("订单号："+orderid+"<BR>");
                }
            }
        }
        return out.toString();
    }
    /**
     * 批量更新
     */
    private String QueryBatchUpadate(){
         // 把的有订单的金额加10
        Query query = em.createQuery("update Orders as o set o.amount = o.amount+10");
         // update的记录数
        int result = query.executeUpdate();
        StringBuffer out = new StringBuffer("***************QueryBatchUpadate结果打印*************<BR>");
        out.append("更新操作影响的记录数："+result+"条<BR>");
        return out.toString();
    }
    /**
     * 批量删除
     */
    private String QueryBatchRemove(){
         // 把的有订单的金额加10
        Query query = em.createQuery("delete from Orders as o where o.amount < 100");
         // update的记录数
        int result = query.executeUpdate();
        StringBuffer out = new StringBuffer("***************QueryBatchRemove结果打印*************<BR>");
        out.append("删除操作影响的记录数："+result+"条<BR>");
        return out.toString();
    }    /**
     * 关联查询Left join
     */
    private String QueryLeftJoin(){
        // 获取26岁人的订单，不管Orders中是否有OrderItem
        Query query = em.createQuery("select o from Orders 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 it = result.iterator();
            Integer orderid = null;
            while(it.hasNext()){
                Orders order = (Orders)it.next();
                if(orderid==null||!orderid.equals(order.getOrderid())){
                    orderid = order.getOrderid();
                    out.append("订单号："+orderid+"<BR>");
                }
                
            }
        }
        return out.toString();