querydaobean.java

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

/**
 * Copyright (c)上海烟草(集团)公司与上海康时信息系统有限公司。
 */
package com.myejb.imp.QL3;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.myejb.QL3.QueryDAO;
import com.myejb.entity.OrderItem;
import com.myejb.entity.Orders;
import com.myejb.entity.Person;
import com.myejb.entity.SimplePerson;

/**
 * EJB3 QL测试远程无状态会话Bean
 * @author xywang
 * @since 2009-2-23
 * @version 1.0
 * 
 */
@Stateless
@Remote(QueryDAO.class)
public class QueryDAOBean implements QueryDAO{
    @PersistenceContext
    protected EntityManager em;

    /* （非 Javadoc）
     * @see com.myejb.QL3.QueryDAO#executeQuery(int)
     */
    public String executeQuery(int index){
        String result = "";
        switch(index){
        case 1:
            //result = this.NameQuery();
            break;
        case 2:
            //result = this.PositionQuery();
            break;
        case 3:
            result = this.QueryOrderBy();
            break;
        case 4:
            result = this.QueryPartAtrribute();
            break;
        case 5:
            result = this.QueryConstructor();
            break;
        case 6:
            result = this.QueryAggregation();
            break;
        case 7:
            result = this.QueryGroupBy();
            break;
        case 8:
            result = this.QueryGroupByHaving();
            break;
        case 9:
            result = this.QueryLeftJoin();
            break;
        case 10:
            result = this.QueryInnerJoin();
            break;
        case 11:
            result = this.QueryInnerJoinLazyLoad();
            break;
        case 12:
            result = this.QueryJoinFetch();
            break;            
        case 13:
            result = this.QueryBatchUpadate();
            break;            
        case 14:
            result = this.QueryBatchRemove();
            break; 

        case 15:
            result = this.QueryNotOperate();
            break; 
        case 16:
            result = this.QueryBetweenOperate();
            break; 
        case 17:
            result = this.QueryINOperate();
            break; 
        case 18:
            result = this.QueryLikeOperate();
            break; 
        case 19:
            result = this.QueryISNULLOperate();
            break; 
        case 20:
            result = this.QueryISEmptyOperate();
            break; 
        case 21:
            result = this.QueryExistsOperate();
            break; 
        case 22:
            result = this.QueryMathLOperate();
            break; 
        case 23:
            result = this.QueryStringOperate();
            break; 
        case 24:
            result = this.QueryNoneReturnValueStoreProcedure();
            break; 
        case 25:
            result = this.QuerySingleObjectStoreProcedure();
            break; 
        case 26:
            result = this.QueryStoreProcedure();
            break; 
        case 27:
            result = this.QueryPartColumnStoreProcedure();
            break; 
        case 28:
            result = this.QuerySubQueryOperate();
            break; 
            
        }
        return result;
    }
    /**
     * 调用无返回参数的存储过程
     */
    private String QueryNoneReturnValueStoreProcedure(){
        // 调用无返回参数的存储过程
        Query query = em.createNativeQuery("{call AddPerson()}");
        query.executeUpdate();
        StringBuffer out = new StringBuffer("***************QueryNoneReturnValueStoreProcedure结果打印*************<BR>");
        return out.toString();
    }
    /**
     * 调用返回单个值的存储过程
     */
    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();
    }
    /**
     * 调用返回全部列的存储过程
     */
    private String QueryStoreProcedure(){
        // 调用返回全部列的存储过程
        Query query = em.createNativeQuery("{call GetPersonList()}",Person.class);
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("***************QueryStoreProcedure结果打印*************<BR>");
        if(result!=null){
            Iterator it = result.iterator();
            while(it.hasNext()){
                Person person = (Person)it.next();
                out.append(person.getName()+"<BR>");
            }
        }
        
        return out.toString();
    }
    /**
     * 调用返回部分列的存储过程
     */
    private String QueryPartColumnStoreProcedure(){
        // 调用返回部分列的存储过程
        Query query = em.createNativeQuery("{call GetPersonPartProperties()}");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("***************QueryPartColumnStoreProcedure结果打印*************<BR>");
        if(result!=null){
            Iterator it = result.iterator();
            while(it.hasNext()){
                Object[] row = (Object[])it.next();
                int personid = Integer.parseInt(row[0].toString());
                String personName = row[1].toString();
                out.append("人员ID："+personid+";姓名："+personName+"<BR>");
            }
        }
        
        return out.toString();
    }
    /**
     * 子查询
     */
    private String QuerySubQueryOperate(){
        // 查询年龄为26岁的购买者的所有Orders
        Query query = em.createQuery("select o from Orders 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 it = result.iterator();
            out.append("-----------查询年龄为26岁的购买者的所有Orders--------<BR>");
            while(it.hasNext()){
                Orders order = (Orders)it.next();
                out.append("订单号："+order.getOrderid()+"<BR>");
            }
        }
        return out.toString();
    }
    /**
     * 计算函数操作
     * ABS     绝对值
     * SQRT    平方根
     * MOD     取余数
     * SIZE    取集合的数量
     */
    private String QueryMathLOperate(){
        // 查询所有Orders的订单号及其订单项的数量
        Query query = em.createQuery("select o.orderid,size(o.orderItems) from Orders as o group by o.orderid");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("***************QueryMathLOperate结果打印*************<BR>");
        if(result!=null){
            Iterator it = result.iterator();
            out.append("-----------查询所有Orders的订单号及其订单项的数量--------<BR>");
            while(it.hasNext()){
                // 取每一行
                Object[]row = (Object[])it.next();
                out.append("订单号："+row[0].toString()+";订单项共"+row[1].toString()+"项<BR>");
            }
        }
        // 查询所有Orders的订单号及其总金额/10的余数
        query = em.createQuery("select o.orderid,mod(o.amount,10) from Orders as o ");
        result = query.getResultList();
        if(result!=null){
            Iterator it = result.iterator();
            out.append("-----------查询所有Orders的订单号及其总金额/10的余数--------<BR>");
            while(it.hasNext()){
                // 取每一行
                Object[]row = (Object[])it.next();
                out.append("订单号："+row[0].toString()+";总金额/10的余数："+row[1].toString()+"<BR>");
            }
        }
        return out.toString();
    }
    /**
     * 字符串函数操作
     * CONCAT       字符串拼接
     * substring    字符串截取
     * trim         去掉空格
     * lower        转换成小写
     * upper        转换成大写
     * length       字符串长度
     * locate       字符串定位
     */
    private String QueryStringOperate(){