sqlcreator.java

一个Java持久层类库

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ActiveObject.core;

import ActiveObject.common.Flag;
import ActiveObject.exception.ObjectAnalysisException;
import ActiveObject.vo.Accesstor;
import ActiveObject.vo.BasicSqlStatement;
import ActiveObject.vo.ObjectInfo;
import ActiveObject.vo.IntermediaryExpression;
import java.util.Map;

/**
 *
 * @author tanjiazhang
 */
public class SQLCreator {

    private static DataBaseType dbType = DataBaseType.MySQL;

    IntermediaryExpression expression;

    public SQLCreator(IntermediaryExpression expression)
    {
        this.expression = expression;
    }

    String toSQLString() throws ObjectAnalysisException
    {
        String sortSQL = createOrderStatement();
        switch(expression.getOperateType())
        {
            case IntermediaryExpression.Query:
                StringBuffer selectSql = new StringBuffer(200);
                selectSql.append("select * from ");
                selectSql.append(createAliasStatement());
                if(!OQLParser.isContentEmpty(expression.getCondition()))
                {
                    selectSql.append(" where ");
                    selectSql.append(expression.getCondition());
                }
                if(sortSQL != null)
                    selectSql.append(sortSQL);

                //添加记录个数限制
                if(expression.getLimitLength() > -1)
                {
                    selectSql.append(" limit ");
                    if(expression.getLimitStart() > -1)
                    {
                        selectSql.append(expression.getLimitStart());
                        selectSql.append(",");
                    }
                    selectSql.append(expression.getLimitLength());
                }
            return OQLParser.parseLikeSyntax(selectSql, expression.getParamValues());
            
            case IntermediaryExpression.Update:
                //String sortSql = this.createOrderStatement();
                StringBuffer updateSql = new StringBuffer(200);
                updateSql.append("update ");
                updateSql.append(this.createAliasStatement());
                updateSql.append(" set ");
                updateSql.append(expression.getSetting());

                if(!OQLParser.isContentEmpty(expression.getCondition()))
                {
                    updateSql.append(" where ");
                    updateSql.append(expression.getCondition());
                }
                if(sortSQL != null)
                    updateSql.append(sortSQL);
            return OQLParser.parseLikeSyntax(updateSql, expression.getParamValues());
                
            case IntermediaryExpression.Delete:
                //String expandSql = this.createOrderStatement();
                StringBuffer sql = new StringBuffer(200);
                sql.append("delete ");
                int objectIndex = expression.getAliasMap().size()-1;
                int index=0;
                //添加要删除的表的别名
                for(String item : expression.getAliasMap().values())
                {
                    sql.append(item);
                    if(index < objectIndex)
                        sql.append(',');
                    else
                        sql.append(' ');
                }
                sql.append("from ");
                sql.append(this.createAliasStatement());
                if(!OQLParser.isContentEmpty(expression.getCondition()))
                {
                    sql.append(" where ");
                    sql.append(expression.getCondition());
                }
                if(sortSQL != null)
                    sql.append(sortSQL);
            return OQLParser.parseLikeSyntax(sql, expression.getParamValues());
        }
        return "";
    }

    /**
     * 返回排序部分语句，包含前后空格。例如 order by u.id DESC, u.name ASC
     * @return 如果没有添加排序条件则返回空
     */
    String createOrderStatement()
    {
        Map<String, Order> orders = expression.getOrders();
        if(orders == null)
            return null;
        int index = 1;
        StringBuffer expandCondition = new StringBuffer(50);
        //添加排序控制
        expandCondition.append(" order by ");
        int size = orders.size();
        for(String key : orders.keySet())
        {
            expandCondition.append(key);
            if(orders.get(key) == Order.DESC)
                expandCondition.append(" DESC ");
            else
                expandCondition.append(" ASC ");
            if(index == size)
                expandCondition.append(' ');
            else
                expandCondition.append(",");
            index++;
        }
        return expandCondition.toString();
    }

    /**
     * 返回表别名部分语句，包含前后空格。例如 user as u,role as r
     * @return
     */
    final String createAliasStatement() throws ObjectAnalysisException
    {
        Map<String, String> aliasMap = expression.getAliasMap();
        Class[] objectTypes = expression.getObjectTypes();
        StringBuffer sql = new StringBuffer(50);
        int objectIndex = objectTypes.length-1;
        for(int index=0;index<=objectIndex;index++)
        {
            ObjectInfo recordType = ActiveRecordHelper.analizeObjectInfo(objectTypes[index]);
            sql.append(makeTableName(recordType.getTableName()));
            sql.append(" as ");
            sql.append(aliasMap.get(objectTypes[index].getSimpleName()));
            if(index == objectIndex)
            {
                if(!OQLParser.isContentEmpty(expression.getCondition()))//此句可以删除
                    sql.append(" ");
            }
            else
                sql.append(",");
        }
        return sql.toString();
    }
    
    static BasicSqlStatement createBasicSQL(String tableName, Accesstor[] accesstors, Accesstor[] ids)
    {
        //构造SQL语句-------------------------------------------------------------------
        BasicSqlStatement stmts = new BasicSqlStatement();
        StringBuffer bufInsert = new StringBuffer(200);
        StringBuffer bufUpdate = new StringBuffer(200);
        StringBuffer bufSelect = new StringBuffer(200);
        StringBuffer bufDelete = new StringBuffer(100);
        //insert语句
        bufInsert.append("insert into ");
        bufInsert.append(makeTableName(tableName));
        
        bufInsert.append("(");
        //update语句
        bufUpdate.append("update ");
        bufUpdate.append(makeTableName(tableName));
        bufUpdate.append(" set ");
        //select语句
        bufSelect.append("select ");
        //delete语句
        bufDelete.append("delete from ");
        bufDelete.append(makeTableName(tableName));
        int index = 0;
        int idLength = ids.length-1;
        for(index = 0;index<=idLength;index++)
        {
            String columnName = ids[index].getColumnName();
            bufInsert.append(columnName);
            bufSelect.append(columnName);
            bufInsert.append(',');
            bufSelect.append(',');
        }
        int accLength = accesstors.length-1;
        for(index = 0;index<=accLength;index++)
        {
            String columnName = accesstors[index].getColumnName();
            bufInsert.append(columnName);
            bufSelect.append(columnName);
            bufUpdate.append(columnName);
            if(index==accLength)
            {
                bufUpdate.append("=?");
            }
            else
            {
                bufInsert.append(',');
                bufSelect.append(',');
                bufUpdate.append("=?,");
            }
        }
        bufInsert.append(")values(");
        index = idLength+accLength+2;
        for(;index>0;index--)//添加insert语句的占位符
        {
            bufInsert.append('?');
            if(index==1)
                bufInsert.append(')');
            else
                bufInsert.append(',');
        }
        bufSelect.append(" from ");
        bufSelect.append(makeTableName(tableName));
        //where子句
        StringBuffer whereClause = new StringBuffer(50);
        whereClause.append(" where ");
        for(index = 0;index<=idLength;index++)
        {
            whereClause.append(ids[index].getColumnName());
            if(index==idLength)
                whereClause.append("=?");
            else
                whereClause.append("=?,");
        }
        bufUpdate.append(whereClause);
        bufSelect.append(whereClause);
        bufDelete.append(whereClause);
        //-----------------------------------------------------------------------------
        stmts.setInsertStatement(bufInsert.toString());
        stmts.setUpdateStatement(bufUpdate.toString());
        stmts.setSelectStatement(bufSelect.toString());
        stmts.setDeleteStatement(bufDelete.toString());

        return stmts;
    }

    private static String JoinTableWord = null;
    private static final String joinTable(String table1, String table2, String foreignKey, String primaryKey)
    {
        if(JoinTableWord == null)
        {
            switch(dbType)
            {
                case MySQL:
                    JoinTableWord = " left join ";
                case SQLServer:
                    JoinTableWord = " inner join ";
            }
        }
        return table1 + JoinTableWord + table2 + " on " + foreignKey + "=" + primaryKey;
    }

    private static final String makeTableName(String tableName)
    {
        switch(dbType)
        {
            case MySQL:
                return tableName;
            case SQLServer:
                return '['+tableName+']';
        }
        return tableName;
    }

    static
    {
        String dbtype = DataBaseProperty.getProperty(Flag.DataSource.DataBaseType);
        if(dbtype == null)
            dbType = DataBaseType.MySQL;
        else if(DataBaseName.MySql.toUpperCase().equals(dbtype.toUpperCase()))
            dbType = DataBaseType.MySQL;
        else if(DataBaseName.SqlServer.toUpperCase().equals(dbtype.toUpperCase()))
            dbType = DataBaseType.SQLServer;
    }
}

enum DataBaseType
{
    MySQL ,
    SQLServer
}

class DataBaseName
{
    static String MySql = "MySQL";
    static String SqlServer = "SQLServer";
}