basesql.java

信息发布 发布系统 动态的菜单 和 信息统计

package com.xuntian.material.sql;

import java.util.Iterator;
import java.util.Map;

public class BaseSQL {
    private static final Object LIKE = " LIKE ";

    protected static String WHERE_1_EQUALS_1 = " WHERE 1=1 ";

    protected static String WHERE_ONE_EQUALS_MINUS_ONE = " WHERE 1=-1 ";

    protected static String SELECT = " SELECT ";

    protected static String INSERT_INTO = " INSERT INTO ";

    protected static String UPDATE = " UPDATE ";

    protected static String DELETE = " DELETE ";

    protected static String FROM = " FROM ";

    protected static String AND = " AND ";

    protected static String OR = " OR ";

    protected static String VALUES = " VALUES ";

    protected static String SET = " SET ";

    protected static String COMMA = ", ";

    protected static String INTERROGATION = " ? ";

    protected static String UNION = " UNION ";

    protected static String NULL = "null";

    protected static String ORDER_BY = " ORDER BY ";

    protected static String DESC = " DESC ";

    protected static String ASC = " ASC ";

    protected static String BETWEEN = " BETWEEN ";

    protected static String IS_NULL = " IS NULL ";

    // class_id
    public static final String COLUMN_CLASS_ID = "class_id";

    protected static String getQuerySQL(String table) {
        return getQuerySQL(table, null);
    }

    protected static String getQuerySQL(String table, String[] fields) {
        return getQuerySQL((String[]) null, table, fields);
    }

    protected static String getQuerySQL(String[] columns, String table) {
        return getQuerySQL(columns, table, (String[]) null);
    }

    protected static String getQuerySQL(String[] columns, String table,
            String[] fields) {
        StringBuffer sql = new StringBuffer(SELECT);
        if (columns == null) {
            sql.append("*");
        } else {
            for (int i = 0; i < columns.length; i++) {
                if (i > 0) {
                    sql.append(COMMA);
                }
                sql.append(columns[i]);
            }
        }
        sql.append(FROM);
        sql.append(table);
        if (fields != null) {
            sql.append(WHERE_1_EQUALS_1);
            for (String field : fields) {
                sql.append(AND);
                sql.append(field);
                sql.append("=?");
            }
        }
        return sql.toString();
    }

    protected static String getQuerySQL(String[] columns, String table,
            Map<String, String> params) {
        StringBuffer sql = new StringBuffer(SELECT);
        if (columns == null) {
            sql.append("*");
        } else {
            for (int i = 0; i < columns.length; i++) {
                if (i > 0) {
                    sql.append(COMMA);
                }
                sql.append(columns[i]);
            }
        }
        sql.append(FROM);
        sql.append(table);
        if (params != null) {
            Iterator<String> keys = params.keySet().iterator();
            String key;
            sql.append(WHERE_1_EQUALS_1);
            while (keys.hasNext()) {
                key = keys.next();
                prepareLikeCondition(sql, key, params.get(key));
            }
        }
        return sql.toString();
    }

    protected static String getInsertSQL(String table, String[] columns) {
        StringBuffer sql = new StringBuffer(INSERT_INTO);
        sql.append(table);
        sql.append("(");
        for (int i = 0; i < columns.length; i++) {
            if (i > 0) {
                sql.append(", ");
            }
            sql.append(columns[i]);
        }
        sql.append(")");
        sql.append(VALUES);
        sql.append("(");
        for (int i = 0; i < columns.length; i++) {
            if (i > 0) {
                sql.append(COMMA);
            }
            sql.append("?");
        }
        sql.append(")");
        return sql.toString();
    }

    protected static String getInsertFromTempSQL(String table, String temp,
            String[] columns, String[] fields) {
        StringBuffer sql = new StringBuffer(INSERT_INTO);
        sql.append(table);
        sql.append("(");
        for (int i = 0; i < columns.length; i++) {
            if (i > 0) {
                sql.append(", ");
            }
            sql.append(columns[i]);
        }
        sql.append(")");
        sql.append(SELECT);
        for (int i = 0; i < columns.length; i++) {
            if (i > 0) {
                sql.append(", ");
            }
            sql.append(columns[i]);
        }
        sql.append(FROM);
        sql.append(temp);
        if (fields != null) {
            sql.append(WHERE_1_EQUALS_1);
            for (String field : fields) {
                sql.append(AND);
                sql.append(field);
                sql.append("=?");
            }
        }
        return sql.toString();
    }

    protected static String getInsertFromTempSQL(String table, String temp,
            String[] columnsTable, String[] columnsTemp, String[] fields) {
        StringBuffer sql = new StringBuffer(INSERT_INTO);
        sql.append(table);
        sql.append("(");
        for (int i = 0; i < columnsTable.length; i++) {
            if (i > 0) {
                sql.append(", ");
            }
            sql.append(columnsTable[i]);
        }
        sql.append(")");
        sql.append(SELECT);
        for (int i = 0; i < columnsTemp.length; i++) {
            if (i > 0) {
                sql.append(", ");
            }
            sql.append(columnsTemp[i]);
        }
        sql.append(FROM);
        sql.append(temp);
        if (fields != null) {
            sql.append(WHERE_1_EQUALS_1);
            for (String field : fields) {
                sql.append(AND);
                sql.append(field);
                sql.append("=?");
            }
        }
        return sql.toString();
    }

    protected static String getUpdateSQL(String table, String[] columns) {
        return getUpdateSQL(table, columns, null);
    }

    protected static String getUpdateSQL(String table, String[] columns,
            String[] fields) {
        StringBuffer sql = new StringBuffer(UPDATE);
        sql.append(table);
        sql.append(SET);
        for (int i = 0; i < columns.length; i++) {
            if (i > 0) {
                sql.append(COMMA);
            }
            sql.append(columns[i]);
            sql.append("=?");
        }
        if (fields != null) {
            sql.append(WHERE_1_EQUALS_1);
            for (String field : fields) {
                sql.append(AND);
                sql.append(field);
                sql.append("=?");
            }
        }
        return sql.toString();
    }

    protected static String getDeleteSQL(String table) {
        return getDeleteSQL(table, null);
    }

    protected static String getDeleteSQL(String table, String[] fields) {
        StringBuffer sql = new StringBuffer(DELETE);
        sql.append(FROM);
        sql.append(table);
        if (fields != null) {
            sql.append(WHERE_1_EQUALS_1);
            for (String field : fields) {
                sql.append(AND);
                sql.append(field);
                sql.append("=?");
            }
        }
        return sql.toString();
    }

    protected static String getDeleteSQL(String table, String field,
            String[] values) {
        StringBuffer sql = new StringBuffer(DELETE);
        sql.append(FROM);
        sql.append(table);
        if (field != null && !field.equals("")) {
            sql.append(WHERE_1_EQUALS_1);
            prepareInCondition(sql, field, values);
        }
        return sql.toString();
    }

    protected static String getDeleteSQL(String table, String field,
            int[] values) {
        StringBuffer sql = new StringBuffer(DELETE);
        sql.append(FROM);
        sql.append(table);
        if (field != null && !field.equals("")) {
            sql.append(WHERE_1_EQUALS_1);
            prepareInCondition(sql, field, values);
        }
        return sql.toString();
    }

    protected static String getAndCondition(String column, String value) {
        if (value == null) {
            value = "";
        }
        StringBuffer handlers = new StringBuffer();
        handlers.append(AND);