querydaobean.java

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

    }
    /**
     * 关联查询inner join
     */
    private String QueryInnerJoin(){
        // 获取26岁人的订单，Orders中必须有OrderItem
        Query query = em.createQuery("select o from Orders 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 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();        
    }
    /**
     * 关联查询inner join延迟查询
     */
    private String QueryInnerJoinLazyLoad(){
        // 默认EJB3 QL编译后不关联集合属性变量(orderitems)对应的表
        Query query = em.createQuery("select o from Orders 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){
                // 这时获得Orders实体中的orderItems为空
                Orders order = (Orders)result.get(0);
                // 当应用需要时，EJB3 Runtime才会执行一条SQL语句来加载属于当前Orders的OrderItems(会执行N条获得OrderItems信息的语句)
                Set<OrderItem> list = order.getOrderItems();
                Iterator<OrderItem> it = list.iterator();
                if(it.hasNext()){
                    OrderItem orderItem = it.next();
                    out.append("订购产品名："+orderItem.getProductname()+"<BR>");
                }
            }
        }
        return out.toString();
    }
    /**
     * 关联查询inner fetch查询(只需一条SQL就能把Orders中的属性信息全部取出来)
     */
    private String QueryJoinFetch(){
        // 获取26岁人的订单，Orders中必须有OrderItem
        Query query = em.createQuery("select o from Orders 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 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();
    }
    /**
     * 带条件的Group By 查询
     */
    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 it = result.iterator();
            while(it.hasNext()){
                // 取每一行
                Object[]row=(Object[])it.next();
                // 数组中第一个值是sex
                boolean sex = Boolean.parseBoolean(row[0].toString());
                String sextotal = row[1].toString();
                out.append((sex?"男生":"女生")+"总共有"+sextotal+"人<BR>");
            }
        }
        return out.toString();
    }
    /**
     * Group By 查询
     */
    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 it = result.iterator();
            while(it.hasNext()){
                // 取每一行
                Object[]row=(Object[])it.next();
                // 数组中第一个值是sex
                boolean sex = Boolean.parseBoolean(row[0].toString());
                String sextotal = row[1].toString();
                out.append((sex?"男生":"女生")+"总共有"+sextotal+"人<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(*) 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("***************QueryAggregation结果打印*************<BR>");
        out.append("最大年龄："+maxAge+"<BR>");
        out.append("平均年龄："+avgAge+"<BR>");
        out.append("最小年龄："+minAge+"<BR>");
        out.append("年龄总和："+sumage+"<BR>");
        out.append("总  人数："+countperson+"<BR>");
        return out.toString();
    }
    /**
     * 查询中使用构造子
     */
    private String QueryConstructor(){
        // 我们把需要的两个属性作为SimplePerson的构造器参数，并使用new函数
        Query query = em.createQuery("select new com.myejb.entity.SimplePerson(p.name,p.sex) from Person p order by p.personid desc");
        // 集合中的元素是simplePerson对象
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("***************QueryConstructor结果打印*************<BR>");
        if(result!=null){
            Iterator it = result.iterator();
            while(it.hasNext()){
                SimplePerson person = (SimplePerson)it.next();
                out.append("人员介绍："+person.getDescription()+"<BR>");
            }
        }
        return out.toString();
    }
    /**
     * 查询部分属性
     */
    private String QueryPartAtrribute(){
        // 直接查询我们感兴趣的属性列
        Query query = em.createQuery("select p.personid,p.name from Person p order by p.personid desc");
        // 集合中的元素不再是Person,而是一个Object[]对象数组
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("***************QueryPartAtrribute结果打印*************<BR>");
        if(result!=null){
            Iterator it = result.iterator();
            while(it.hasNext()){
                // 取每一行
                Object[]row = (Object[])it.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 QueryOrderBy(){
        // 先按年龄降序排序，然后按出生日期升序排序
        Query query = em.createQuery("select p from Person p order by p.age desc,birthday asc");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("***************QueryOrderBy结果打印*************<BR>");
        if(result!=null){
            Iterator it = result.iterator();
            while(it.hasNext()){
                Person person = (Person)it.next();
                out.append(person.getName()+"<BR>");
            }
        }
        return out.toString();
    }

    /* （非 Javadoc）
     * @see com.myejb.QL3.QueryDAO#initdate()
     */
    public void initdate(){
        try{
            Query query = em.createQuery("select count(*) from Person p");
            Object result = query.getSingleResult();
            if(result == null || Integer.parseInt(result.toString()) == 0){
                // 没有数据时，插入几条数据作测试
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                Person person = new Person("刘君",true,new Short("29"),formatter.parse("1980-09-30"));
                Set<Orders> orders = new HashSet<Orders>();
                Orders order1 = new Orders(new Float("105.5"),person,new Date());
                order1.addOrderItem(new OrderItem("U盘",new Float("105.5")));
                
                Orders order2 = new Orders(new Float("780"),person,new Date());
                order2.addOrderItem(new OrderItem("MP4",new Float("778")));
                order2.addOrderItem(new OrderItem("矿泉水",new Float("2")));
                orders.add(order1);
                orders.add(order2);
                person.setOrders(orders);
                
                Person person1 = new Person("yunxiaoyi",false,new Short("26"),formatter.parse("1983-10-20"));
                orders = new HashSet<Orders>();
                order1 = new Orders(new Float("360"),person1,new Date());
                order1.addOrderItem(new OrderItem("香水",new Float("360")));
                order2 = new Orders(new Float("1806"),person1,new Date());
                order2.addOrderItem(new OrderItem("照相机",new Float("1800")));
                order2.addOrderItem(new OrderItem("5号电池",new Float("6")));
                orders.add(order1);
                orders.add(order2);
                person1.setOrders(orders);
                //========================================
                Person person2 = new Person("zhangming",false,new Short("24"),formatter.parse("1985-11-25"));
                orders = new HashSet<Orders>();
                order1 = new Orders(new Float("620"),person2,new Date());
                order1.addOrderItem(new OrderItem("棉被",new Float("620")));
                order2 = new Orders(new Float("3"),person2,new Date());
                order2.addOrderItem(new OrderItem("可乐",new Float("3")));

                orders.add(order1);
                orders.add(order2);
                person2.setOrders(orders);
                
                em.persist(person2);
                em.persist(person1);
                em.persist(person);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
    }

}