BaseService

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import com.autocreate.dbpool.DBUtil;
import com.autocreate.util.JBeanClass;
import com.autocreate.util.Log;
import com.autocreate.util.TimeTools;

public class BaseService<T>
{
    /**
     * 通过反射创建查询语句 
     * @param info
     * @return
     */
    @SuppressWarnings("unchecked")
    public T query(T info)
    {
        Log.getLogger().info("query object: " + info);
        StringBuffer sqlBuffer = new StringBuffer(512);
        String className = info.getClass().getSimpleName();
        sqlBuffer.append("select * from ").append(className);
        
        Object[] whereObj = createWhereByPrimaryKey(info);
        String sql = sqlBuffer.toString() + whereObj[0];
        return (T)DBUtil.getInstance().queryBean(sql, info.getClass(), ((List)whereObj[1]).toArray());
    }
    
    /**
     * 通过反射创建insert语句 
     * @param info
     * @return
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public boolean insert(T info) throws Exception
    {
        Log.getLogger().info("insert object: " + info);
        
        StringBuffer sqlBuffer = new StringBuffer(512);
        StringBuffer bufferName = new StringBuffer(256);
        StringBuffer bufferValue = new StringBuffer(256);
        List paramList = new ArrayList();
        
        String className = info.getClass().getSimpleName();
        sqlBuffer.append("insert into ").append(className).append("(");
        
        Object obj = null;
        Object[] typeAndValue = null;
        Field[] fields = info.getClass().getDeclaredFields();
        for (Field f : fields)
        {
            obj = JBeanClass.getGetterMethodInvokeValue(info, f.getName());
            if (null != obj)
            {
                typeAndValue = getValueByType(f.getType().getName(), obj);
                bufferName.append(f.getName()).append(",");
                bufferValue.append(typeAndValue[0]).append(",");
                paramList.add(typeAndValue[1]);
            }
        }
        
        sqlBuffer.append(StringUtils.removeEnd(bufferName.toString(), ","))
            .append(") values (")
            .append(StringUtils.removeEnd(bufferValue.toString(), ","))
            .append(")");
        
        return DBUtil.getInstance().update(sqlBuffer.toString(), paramList.toArray());
    }
    
    /**
     * 通过反射创建update语句 
     * @param info
     * @return
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public boolean update(T info) throws Exception
    {
        Log.getLogger().info("update object: " + info);
        
        StringBuffer sqlBuffer = new StringBuffer(512);
        StringBuffer bufferName = new StringBuffer(256);
        List paramList = new ArrayList();
        
        // 通过反射构建update语句
        String className = info.getClass().getSimpleName();
        sqlBuffer.append("update ").append(className).append(" set ");
        Object obj = null;
        Object[] typeAndValue = null;
        Field[] fields = info.getClass().getDeclaredFields();
        for (Field f : fields)
        {
            obj = JBeanClass.getGetterMethodInvokeValue(info, f.getName());
            if (null != obj)
            {
                typeAndValue = getValueByType(f.getType().getName(), obj);
                bufferName.append(f.getName()).append(" = ").append(typeAndValue[0]).append(", ");
                paramList.add(typeAndValue[1]);
            }
        }
        sqlBuffer.append(StringUtils.removeEnd(bufferName.toString(), ", "));
        
        Object[] whereObj = createWhereByPrimaryKey(info);
        String sql = sqlBuffer.toString() + whereObj[0];
        paramList.addAll((List)whereObj[1]);
        return DBUtil.getInstance().update(sql, paramList.toArray());
    }
    
    /**
     * 通过反射创建delete语句 
     * @param info
     * @return
     * @throws SQLException
     */
    @SuppressWarnings("unchecked")
    public boolean delete(T info) throws SQLException
    {
        StringBuffer sqlBuffer = new StringBuffer(256);
        String className = info.getClass().getSimpleName();
        sqlBuffer.append("delete from ").append(className);
        Object[] whereObj = createWhereByPrimaryKey(info);
        String sql = sqlBuffer.toString() + whereObj[0];
        return DBUtil.getInstance().update(sql, ((List)whereObj[1]).toArray());
    }
    
    /**
     * 通过对象主键创建WHERE条件
     * @param info
     * @return
     */
    private Object[] createWhereByPrimaryKey(T info)
    {
        StringBuffer whereBuffer = new StringBuffer(256);
        List<Object> paramList = null;
        Object obj = null;
        String tablePrimaryKey = JBeanClass.getMethodInvokeValue(info, "getTablePrimaryKey", null, null).toString();
        //Log.getLogger().info("The \"" + info.getClass().getSimpleName() + "\" table's Primary Key is: " + tablePrimaryKey);
        if (StringUtils.isNotEmpty(tablePrimaryKey))
        {
            whereBuffer.append(" where ");
            String[] primaryKeys = tablePrimaryKey.split(",");
            paramList = new ArrayList<Object>();
            for (String str : primaryKeys)
            {
                obj = JBeanClass.getGetterMethodInvokeValue(info, str);
                if (null != obj)
                {
                    whereBuffer.append(str).append(" = ? and ");
                    paramList.add(obj);
                }
            }
        }
        String where = StringUtils.removeEnd(whereBuffer.toString(), "and ");
        return new Object[] {where, paramList};
    }
    
    /**
     * 根据类型和返回对应的值，insert、update时候用到
     * @param type
     * @param value
     * @return
     */
    private Object[] getValueByType(String type, Object value)
    {
        Object[] obj = new Object[2];
        if (type.equals("java.util.Date"))
        {
            obj[0] = "to_date(?,'YYYY-MM-DD HH24:MI:SS')";
            obj[1] = TimeTools.format2.format(value);
        }
        else
        {
            obj[0] = "?";
            obj[1] = value;
        }
        return obj;
    }
    
    /**
     * 查询所有
     * @param tableName
     * @param clazz
     * @param whereMap
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<T> queryList(String tableName, Class clazz, Map<String, Object> whereMap)
    {
        Object[] obj = getWhereCase(whereMap);
        String sql = "select * from " + tableName + obj[0];
        Object[] param = (Object[])obj[1];
        return DBUtil.getInstance().queryBeanList(sql, clazz, param);
    }
    
    /**
     * 查询一页数据
     * @param tableName
     * @param clazz
     * @param whereMap
     * @param pageNumber
     * @param pageSize
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<T> queryPageList(String tableName, Class clazz, Map<String, Object> whereMap, int pageNumber,
        int pageSize)
    {
        String sql =
            "select * from (select a.*,rownum row_num from (select * from {0}{1})a) b where b.row_num between {2} and {3}";
        int beg = (pageNumber - 1) * pageSize + 1;
        int end = pageNumber * pageSize;
        Object[] obj = getWhereCase(whereMap);
        Object[] messageFormatObj = {tableName, obj[0], beg, end};
        sql = MessageFormat.format(sql, messageFormatObj);
        Object[] param = (Object[])obj[1];
        return DBUtil.getInstance().queryBeanList(sql, clazz, param);
    }
    
    /**
     * 根据表名和参数获取总记录数
     * @param tableName
     * @param whereMap
     * @return
     */
    @SuppressWarnings("unchecked")
    public int getCount(String tableName, Map<String, Object> whereMap)
    {
        Object[] obj = getWhereCase(whereMap);
        String sql = "select count(*) as c from " + tableName + obj[0];
        Object[] param = (Object[])obj[1];
        Map map = (Map)DBUtil.getInstance().queryMap(sql, param);
        if (null != map)
        {
            return Integer.parseInt(map.get("c").toString());
        }
        return 0;
    }
    
    /**
     * 组装SQL WHERE ORDERBY条件
     * @param whereMap
     * @return Object[0]: sql , Object[1]: params
     */
    @SuppressWarnings("unchecked")
    private Object[] getWhereCase(Map<String, Object> whereMap)
    {
        Object[] obj = new Object[2];
        if (null != whereMap && !whereMap.isEmpty())
        {
            StringBuffer buffer = new StringBuffer(128);
            buffer.append(" where 1 = 1 ");
            Iterator<Map.Entry<String, Object>> it = whereMap.entrySet().iterator();
            List list = new ArrayList();
            while (it.hasNext())
            {
                Map.Entry<String, Object> entry = it.next();
                // 如果key或value为null, 或者为orderby, 则continue
                if (null == entry.getKey() || null == entry.getValue() || entry.getValue().toString().equals("")
                    || entry.getKey().trim().equalsIgnoreCase("orderby"))
                {
                    continue;
                }
                else
                {
                    buffer.append("and ").append(entry.getKey()).append(" = ? ");
                    list.add(entry.getValue());
                }
            }
            obj[0] = buffer.append(whereMap.get("orderby") == null ? "" : whereMap.get("orderby"));
            obj[1] = list.toArray();
        }
        else
        {
            obj[0] = "";
            obj[1] = null;
        }
        return obj;
    }
}