com.glaf.core.util.DBUtils.java Source code

Java tutorial

Introduction

Here is the source code for com.glaf.core.util.DBUtils.java

Source

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.glaf.core.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.util.Assert;

import com.glaf.core.base.FieldDefinition;
import com.glaf.core.domain.ColumnDefinition;
import com.glaf.core.domain.TableDefinition;
import com.glaf.core.el.ExpressionTools;
import com.glaf.core.entity.SqlExecutor;
import com.glaf.core.jdbc.DBConnectionFactory;

public class DBUtils {

    protected final static Log logger = LogFactory.getLog(DBUtils.class);

    public final static String newline = System.getProperty("line.separator");

    public static final String POSTGRESQL = "postgresql";

    public static final String ORACLE = "oracle";

    public static void alterTable(Connection connection, TableDefinition tableDefinition) {
        List<String> cloumns = new java.util.ArrayList<String>();
        Statement statement = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            stmt = connection.createStatement();
            rs = stmt.executeQuery("select * from " + tableDefinition.getTableName() + " where 1=0 ");
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String column = rsmd.getColumnName(i);
                cloumns.add(column.toUpperCase());
            }

            logger.debug(tableDefinition.getTableName() + " cloumns:" + cloumns);

            JdbcUtils.close(stmt);
            JdbcUtils.close(rs);

            Collection<ColumnDefinition> fields = tableDefinition.getColumns();
            for (ColumnDefinition field : fields) {
                if (field.getColumnName() != null && !cloumns.contains(field.getColumnName().toUpperCase())) {
                    String sql = getAddColumnSql(dbType, tableDefinition.getTableName(), field);
                    if (sql != null && sql.length() > 0) {
                        statement = connection.createStatement();
                        logger.info("alter table " + tableDefinition.getTableName() + ":\n" + sql);
                        statement.execute(sql);
                        JdbcUtils.close(statement);
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
            JdbcUtils.close(statement);
        }
    }

    public static void alterTable(String systemName, String tableName, List<ColumnDefinition> columns) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSetMetaData rsmd = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<String> columnNames = new java.util.ArrayList<String>();
        try {
            conn = DBConnectionFactory.getConnection();
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(" select * from " + tableName + " where 1=0 ");
            rs = pstmt.executeQuery();
            rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            for (int i = 1; i <= count; i++) {
                columnNames.add(rsmd.getColumnName(i).toLowerCase());
            }

            if (columns != null && !columns.isEmpty()) {
                String dbType = DBConnectionFactory.getDatabaseType(conn);
                for (ColumnDefinition column : columns) {
                    if (columnNames.contains(column.getColumnName().toLowerCase())) {
                        continue;
                    }
                    String javaType = column.getJavaType();
                    String sql = " alter table " + tableName + " add " + column.getColumnName();
                    if ("db2".equalsIgnoreCase(dbType)) {
                        if ("String".equals(javaType)) {
                            if (column.getLength() > 0) {
                                sql += " varchar(" + column.getLength() + ")";
                            } else {
                                sql += " varchar(50) ";
                            }
                        } else if ("Integer".equals(javaType)) {
                            sql += " integer ";
                        } else if ("Long".equals(javaType)) {
                            sql += " bigint ";
                        } else if ("Double".equals(javaType)) {
                            sql += " double precision ";
                        } else if ("Date".equals(javaType)) {
                            sql += " timestamp ";
                        } else if ("Clob".equals(javaType)) {
                            sql += " clob(10240000) ";
                        } else if ("Blob".equals(javaType)) {
                            sql += " blob ";
                        } else if ("Boolean".equals(javaType)) {
                            sql += " smallint ";
                        }
                    } else if ("oracle".equalsIgnoreCase(dbType)) {
                        if ("String".equals(javaType)) {
                            if (column.getLength() > 0) {
                                sql += " NVARCHAR2(" + column.getLength() + ")";
                            } else {
                                sql += " NVARCHAR2(50)";
                            }
                        } else if ("Integer".equals(javaType)) {
                            sql += " INTEGER ";
                        } else if ("Long".equals(javaType)) {
                            sql += " NUMBER(19,0) ";
                        } else if ("Double".equals(javaType)) {
                            sql += " NUMBER(*,10) ";
                        } else if ("Date".equals(javaType)) {
                            sql += " TIMESTAMP(6) ";
                        } else if ("Clob".equals(javaType)) {
                            sql += " CLOB ";
                        } else if ("Blob".equals(javaType)) {
                            sql += " BLOB ";
                        } else if ("Boolean".equals(javaType)) {
                            sql += " NUMBER(1,0) ";
                        }
                    } else if ("mysql".equalsIgnoreCase(dbType)) {
                        if ("String".equals(javaType)) {
                            if (column.getLength() > 0) {
                                sql += " varchar(" + column.getLength() + ")";
                            } else {
                                sql += " varchar(50)";
                            }
                        } else if ("Integer".equals(javaType)) {
                            sql += " int  ";
                        } else if ("Long".equals(javaType)) {
                            sql += " bigint ";
                        } else if ("Double".equals(javaType)) {
                            sql += " double ";
                        } else if ("Date".equals(javaType)) {
                            sql += " datetime ";
                        } else if ("Clob".equals(javaType)) {
                            sql += " longtext ";
                        } else if ("Blob".equals(javaType)) {
                            sql += " longblob ";
                        } else if ("Boolean".equals(javaType)) {
                            sql += " tinyint ";
                        }
                    } else if ("postgresql".equalsIgnoreCase(dbType)) {
                        if ("String".equals(javaType)) {
                            if (column.getLength() > 0) {
                                sql += " varchar(" + column.getLength() + ")";
                            } else {
                                sql += " varchar(50)";
                            }
                        } else if ("Integer".equals(javaType)) {
                            sql += " integer ";
                        } else if ("Long".equals(javaType)) {
                            sql += " bigint ";
                        } else if ("Double".equals(javaType)) {
                            sql += " double precision ";
                        } else if ("Date".equals(javaType)) {
                            sql += " timestamp ";
                        } else if ("Clob".equals(javaType)) {
                            sql += " text ";
                        } else if ("Blob".equals(javaType)) {
                            sql += " bytea ";
                        } else if ("Boolean".equals(javaType)) {
                            sql += " boolean ";
                        }
                    } else if ("sqlserver".equalsIgnoreCase(dbType)) {
                        if ("String".equals(javaType)) {
                            if (column.getLength() > 0) {
                                sql += " nvarchar(" + column.getLength() + ")";
                            } else {
                                sql += " nvarchar(50)";
                            }
                        } else if ("Integer".equals(javaType)) {
                            sql += " int ";
                        } else if ("Long".equals(javaType)) {
                            sql += " numeric(19,0) ";
                        } else if ("Double".equals(javaType)) {
                            sql += " double precision ";
                        } else if ("Date".equals(javaType)) {
                            sql += " datetime ";
                        } else if ("Clob".equals(javaType)) {
                            sql += " text ";
                        } else if ("Blob".equals(javaType)) {
                            sql += " image ";
                        } else if ("Boolean".equals(javaType)) {
                            sql += " tinyint ";
                        }
                    } else if ("h2".equalsIgnoreCase(dbType)) {
                        if ("String".equals(javaType)) {
                            if (column.getLength() > 0) {
                                sql += " varchar(" + column.getLength() + ")";
                            } else {
                                sql += " varchar(50)";
                            }
                        } else if ("Integer".equals(javaType)) {
                            sql += " int  ";
                        } else if ("Long".equals(javaType)) {
                            sql += " bigint ";
                        } else if ("Double".equals(javaType)) {
                            sql += " double ";
                        } else if ("Date".equals(javaType)) {
                            sql += " timestamp ";
                        } else if ("Clob".equals(javaType)) {
                            sql += " clob ";
                        } else if ("Blob".equals(javaType)) {
                            sql += " longvarbinary ";
                        } else if ("Boolean".equals(javaType)) {
                            sql += " boolean ";
                        }
                    } else if ("sqlite".equalsIgnoreCase(dbType)) {
                        if ("String".equals(javaType)) {
                            if (column.getLength() > 0) {
                                sql += " TEXT(" + column.getLength() + ")";
                            } else {
                                sql += " TEXT(50)";
                            }
                        } else if ("Integer".equals(javaType)) {
                            sql += " INTEGER  ";
                        } else if ("Long".equals(javaType)) {
                            sql += " INTEGER ";
                        } else if ("Double".equals(javaType)) {
                            sql += " REAL ";
                        } else if ("Date".equals(javaType)) {
                            sql += " TEXT ";
                        } else if ("Clob".equals(javaType)) {
                            sql += " TEXT ";
                        } else if ("Blob".equals(javaType)) {
                            sql += " BLOB ";
                        }
                    } else {
                        if ("String".equals(javaType)) {
                            if (column.getLength() > 0) {
                                sql += " varchar(" + column.getLength() + ")";
                            } else {
                                sql += " varchar(50)";
                            }
                        } else if ("Integer".equals(javaType)) {
                            sql += " int ";
                        } else if ("Long".equals(javaType)) {
                            sql += " bigint ";
                        } else if ("Double".equals(javaType)) {
                            sql += " double ";
                        } else if ("Date".equals(javaType)) {
                            sql += " timestamp ";
                        } else if ("Clob".equals(javaType)) {
                            sql += " clob ";
                        } else if ("Blob".equals(javaType)) {
                            sql += " blob ";
                        } else if ("Boolean".equals(javaType)) {
                            sql += " boolean ";
                        }
                    }
                    logger.info("execute alter:" + sql);
                    stmt = conn.createStatement();
                    stmt.executeUpdate(sql);
                    JdbcUtils.close(stmt);
                }
            }
            JdbcUtils.close(pstmt);
            conn.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
            JdbcUtils.close(pstmt);
            JdbcUtils.close(conn);
        }
    }

    public static void alterTable(String systemName, TableDefinition tableDefinition) {
        List<String> cloumns = new java.util.ArrayList<String>();
        Connection connection = null;
        Statement statement = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            connection = DBConnectionFactory.getConnection(systemName);
            connection.setAutoCommit(false);
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            stmt = connection.createStatement();
            rs = stmt.executeQuery("select * from " + tableDefinition.getTableName() + " where 1=0 ");
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String column = rsmd.getColumnName(i);
                cloumns.add(column.toUpperCase());
            }

            logger.debug(tableDefinition.getTableName() + " cloumns:" + cloumns);

            JdbcUtils.close(stmt);
            JdbcUtils.close(rs);

            Collection<ColumnDefinition> fields = tableDefinition.getColumns();
            for (ColumnDefinition field : fields) {
                if (field.getColumnName() != null && !cloumns.contains(field.getColumnName().toUpperCase())) {
                    String sql = getAddColumnSql(dbType, tableDefinition.getTableName(), field);
                    if (sql != null && sql.length() > 0) {
                        statement = connection.createStatement();
                        logger.info("alter table " + tableDefinition.getTableName() + ":\n" + sql);
                        statement.execute(sql);
                        JdbcUtils.close(statement);
                    }
                }
            }
            connection.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(stmt);
            JdbcUtils.close(rs);
            JdbcUtils.close(statement);
            JdbcUtils.close(connection);
        }
    }

    public static void alterTable(TableDefinition tableDefinition) {
        List<String> cloumns = new java.util.ArrayList<String>();
        Connection connection = null;
        Statement statement = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            connection = DBConnectionFactory.getConnection();
            connection.setAutoCommit(false);
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            stmt = connection.createStatement();
            rs = stmt.executeQuery("select * from " + tableDefinition.getTableName() + " where 1=0 ");
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String column = rsmd.getColumnName(i);
                cloumns.add(column.toUpperCase());
            }

            logger.debug(tableDefinition.getTableName() + " cloumns:" + cloumns);

            Collection<ColumnDefinition> fields = tableDefinition.getColumns();
            for (ColumnDefinition field : fields) {
                if (field.getColumnName() != null && !cloumns.contains(field.getColumnName().toUpperCase())) {
                    String sql = getAddColumnSql(dbType, tableDefinition.getTableName(), field);
                    if (sql != null && sql.length() > 0) {
                        statement = connection.createStatement();
                        logger.info("alter table " + tableDefinition.getTableName() + ":\n" + sql);
                        statement.execute(sql);
                        JdbcUtils.close(statement);
                    }
                }
            }
            connection.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(stmt);
            JdbcUtils.close(rs);
            JdbcUtils.close(statement);
            JdbcUtils.close(connection);
        }
    }

    public static void createIndex(Connection connection, String tableName, String columnName, String indexName) {
        DatabaseMetaData dbmd = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean hasIndex = false;
        boolean autoCommit = false;
        try {
            autoCommit = connection.getAutoCommit();
            dbmd = connection.getMetaData();
            rs = dbmd.getIndexInfo(null, null, tableName, false, false);
            while (rs.next()) {
                String col = rs.getString("COLUMN_NAME");
                if (StringUtils.equalsIgnoreCase(columnName, col)) {
                    hasIndex = true;
                    break;
                }
            }
            JdbcUtils.close(rs);

            if (!hasIndex) {
                String sql = " create index " + indexName.toUpperCase() + " on " + tableName + " (" + columnName
                        + ") ";
                connection.setAutoCommit(false);
                stmt = connection.createStatement();
                stmt.executeUpdate(sql);
                JdbcUtils.close(stmt);
                connection.commit();
                connection.setAutoCommit(autoCommit);
            }
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
        }
    }

    public static void createIndex(String systemName, String tableName, String columnName, String indexName) {
        Connection connection = null;
        DatabaseMetaData dbmd = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean hasIndex = false;
        try {
            connection = DBConnectionFactory.getConnection(systemName);
            dbmd = connection.getMetaData();
            rs = dbmd.getIndexInfo(null, null, tableName, false, false);
            while (rs.next()) {
                String col = rs.getString("COLUMN_NAME");
                if (StringUtils.equalsIgnoreCase(columnName, col)) {
                    hasIndex = true;
                    break;
                }
            }
            JdbcUtils.close(rs);

            if (!hasIndex) {
                String sql = " create index " + indexName.toUpperCase() + " on " + tableName + " (" + columnName
                        + ") ";
                connection.setAutoCommit(false);
                stmt = connection.createStatement();
                stmt.executeUpdate(sql);
                JdbcUtils.close(stmt);
                connection.commit();
            }
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
            JdbcUtils.close(connection);
        }
    }

    /**
     * ????
     * 
     * @param connection
     *            JDBC
     * @param tableDefinition
     *            
     */
    public static String createTable(Connection connection, TableDefinition tableDefinition) {
        Statement statement = null;
        try {
            String tableName = tableDefinition.getTableName();
            if (tableExists(connection, tableName)) {
                return null;
            }
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            logger.info("dbType:" + dbType);
            String sql = getCreateTableScript(dbType, tableDefinition);
            if (sql != null && sql.length() > 0) {
                connection.setAutoCommit(false);
                statement = connection.createStatement();
                logger.info("create table " + tableDefinition.getTableName() + ":\n" + sql);
                statement.execute(sql);
                JdbcUtils.close(statement);
                connection.commit();
                return sql;
            }
            return null;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(statement);
        }
    }

    public static String createTable(String systemName, TableDefinition classDefinition) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DBConnectionFactory.getConnection(systemName);
            connection.setAutoCommit(false);
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            String sql = getCreateTableScript(dbType, classDefinition);
            if (sql != null && sql.length() > 0) {
                statement = connection.createStatement();
                logger.info("create table " + classDefinition.getTableName() + ":\n" + sql);
                statement.execute(sql);
                JdbcUtils.close(statement);
                connection.commit();
                return sql;
            }
            return null;
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(statement);
            JdbcUtils.close(connection);
        }
    }

    public static String createTable(TableDefinition classDefinition) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DBConnectionFactory.getConnection();
            connection.setAutoCommit(false);
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            String sql = getCreateTableScript(dbType, classDefinition);
            if (sql != null && sql.length() > 0) {
                statement = connection.createStatement();
                logger.info("create table " + classDefinition.getTableName() + ":\n" + sql);
                statement.execute(sql);
                JdbcUtils.close(statement);
                connection.commit();
                return sql;
            }
            return null;
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(statement);
            JdbcUtils.close(connection);
        }
    }

    /**
     * ???
     * 
     * @param connection
     *            JDBC
     * @param tableDefinition
     *            
     */
    public static void dropAndCreateTable(Connection connection, TableDefinition tableDefinition) {
        String tableName = tableDefinition.getTableName();
        logger.info("check table:" + tableName);
        if (tableExists(connection, tableName)) {
            dropTable(connection, tableName);
        }
        if (!tableExists(connection, tableName)) {
            createTable(connection, tableDefinition);
        }
    }

    /**
     * ?
     * 
     * @param connection
     *            JDBC
     * @param tableDefinition
     *            
     */
    public static void dropTable(Connection connection, String tableName) {
        logger.info("check table:" + tableName);
        if (tableExists(connection, tableName)) {
            /**
             * ????
             */
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            if (System.getProperty("devMode") != null || StringUtils.equalsIgnoreCase(dbType, "sqlite")
                    || StringUtils.equalsIgnoreCase(dbType, "h2")) {
                Statement statement = null;
                try {
                    statement = connection.createStatement();
                    logger.info("drop table " + tableName);
                    statement.executeUpdate(" drop table " + tableName);
                    JdbcUtils.close(statement);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    throw new RuntimeException(ex);
                } finally {
                    JdbcUtils.close(statement);
                }
            }
        }
    }

    public static void executeSchemaResource(Connection conn, String ddlStatements) {
        Exception exception = null;
        Statement statement = null;
        String sqlStatement = null;
        try {
            StringTokenizer tokenizer = new StringTokenizer(ddlStatements, ";");
            while (tokenizer.hasMoreTokens()) {
                sqlStatement = tokenizer.nextToken();
                if (StringUtils.isNotEmpty(sqlStatement) && !sqlStatement.startsWith("#")) {
                    logger.debug(sqlStatement);
                    try {
                        statement = conn.createStatement();
                        statement.executeUpdate(sqlStatement);
                        JdbcUtils.close(statement);
                    } catch (Exception ex) {
                        if (exception == null) {
                            exception = ex;
                        }
                        logger.debug(" execute statement error: " + sqlStatement, ex);
                    } finally {
                        JdbcUtils.close(statement);
                    }
                }
            }

            if (exception != null) {
                exception.printStackTrace();
                throw exception;
            }

            logger.info("execute db schema successful");

        } catch (Exception ex) {
            throw new RuntimeException("couldn't execute db schema: " + sqlStatement, ex);
        } finally {
            JdbcUtils.close(statement);
        }
    }

    public static String executeSchemaResource(String systemName, String ddlStatements) {
        StringBuilder buffer = new StringBuilder();
        Connection connection = null;
        Statement statement = null;
        String sqlStatement = null;
        try {
            connection = DBConnectionFactory.getConnection(systemName);
            connection.setAutoCommit(false);
            StringTokenizer tokenizer = new StringTokenizer(ddlStatements, ";");
            while (tokenizer.hasMoreTokens()) {
                sqlStatement = tokenizer.nextToken();
                if (StringUtils.isNotEmpty(sqlStatement) && !sqlStatement.startsWith("#")) {
                    // logger.debug(sqlStatement);
                    try {
                        statement = connection.createStatement();
                        statement.executeUpdate(sqlStatement);
                        JdbcUtils.close(statement);
                    } catch (Exception ex) {
                        buffer.append(FileUtils.newline);
                        buffer.append(sqlStatement).append(";");
                        buffer.append(FileUtils.newline);
                    } finally {
                        JdbcUtils.close(statement);
                    }
                }
            }
            if (buffer.length() == 0) {
                // ??
                connection.commit();
            }
            return buffer.toString();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(statement);
            JdbcUtils.close(connection);
        }
    }

    public static void executeSchemaResource(String operation, String resourceName, InputStream inputStream,
            Map<String, Object> context) {
        Exception exception = null;
        Connection connection = null;
        Statement statement = null;
        String sqlStatement = null;
        try {
            connection = DBConnectionFactory.getConnection();
            connection.setAutoCommit(false);
            byte[] bytes = FileUtils.getBytes(inputStream);
            String ddlStatements = new String(bytes);
            StringTokenizer tokenizer = new StringTokenizer(ddlStatements, ";");
            while (tokenizer.hasMoreTokens()) {
                sqlStatement = tokenizer.nextToken();
                if (StringUtils.isNotEmpty(sqlStatement) && !sqlStatement.startsWith("#")) {
                    sqlStatement = ExpressionTools.evaluate(sqlStatement, context);
                    try {
                        statement = connection.createStatement();
                        statement.execute(sqlStatement);
                    } catch (Exception e) {
                        if (exception == null) {
                            exception = e;
                        }
                        logger.debug("problem during schema " + operation + ", statement '" + sqlStatement, e);
                    } finally {
                        JdbcUtils.close(statement);
                    }
                }
            }

            if (exception != null) {
                throw exception;
            }

            connection.commit();

            logger.info("extension db schema " + operation + " successful");

        } catch (Exception e) {
            throw new RuntimeException("couldn't " + operation + " db schema: " + sqlStatement, e);
        } finally {
            JdbcUtils.close(statement);
            JdbcUtils.close(connection);
        }
    }

    public static void executeSchemaResourceIgnoreException(Connection conn, String ddlStatements) {
        Statement statement = null;
        String sqlStatement = null;
        try {
            StringTokenizer tokenizer = new StringTokenizer(ddlStatements, ";");
            while (tokenizer.hasMoreTokens()) {
                sqlStatement = tokenizer.nextToken();
                if (StringUtils.isNotEmpty(sqlStatement) && !sqlStatement.startsWith("#")) {
                    logger.debug(sqlStatement);
                    statement = conn.createStatement();
                    try {
                        statement.executeUpdate(sqlStatement);
                    } catch (Exception ex) {
                        logger.error(" execute statement error: " + sqlStatement, ex);
                    } finally {
                        JdbcUtils.close(statement);
                    }
                }
            }
        } catch (Exception ex) {
            throw new RuntimeException("couldn't execute db schema: " + sqlStatement, ex);
        } finally {
            JdbcUtils.close(statement);
        }
    }

    public static String executeSchemaResourceIgnoreException(String systemName, String ddlStatements) {
        StringBuilder buffer = new StringBuilder();
        Connection connection = null;
        Statement statement = null;
        String sqlStatement = null;
        try {
            connection = DBConnectionFactory.getConnection(systemName);
            connection.setAutoCommit(false);
            StringTokenizer tokenizer = new StringTokenizer(ddlStatements, ";");
            while (tokenizer.hasMoreTokens()) {
                sqlStatement = tokenizer.nextToken().trim();
                if (StringUtils.isNotEmpty(sqlStatement) && !sqlStatement.startsWith("#")) {
                    // logger.debug(sqlStatement);
                    try {
                        statement = connection.createStatement();
                        statement.executeUpdate(sqlStatement);
                        connection.commit();
                    } catch (Exception ex) {
                        buffer.append(FileUtils.newline);
                        buffer.append(sqlStatement).append(";");
                        buffer.append(FileUtils.newline);
                    } finally {
                        JdbcUtils.close(statement);
                    }
                }
            }
            return buffer.toString();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException("can't get connection: ", ex);
        } finally {
            JdbcUtils.close(statement);
            JdbcUtils.close(connection);
        }
    }

    public static String getAddColumnSql(String dbType, String tableName, ColumnDefinition field) {
        StringBuffer buffer = new StringBuffer();
        buffer.append(" alter table ").append(tableName);
        buffer.append(" add ").append(field.getColumnName());
        if ("h2".equalsIgnoreCase(dbType)) {
            if ("Integer".equals(field.getJavaType())) {
                buffer.append(" integer ");
            } else if ("Long".equals(field.getJavaType())) {
                buffer.append(" bigint ");
            } else if ("Double".equals(field.getJavaType())) {
                buffer.append(" double ");
            } else if ("Date".equals(field.getJavaType())) {
                buffer.append(" timestamp ");
            } else if ("Clob".equals(field.getJavaType())) {
                buffer.append(" clob ");
            } else if ("Blob".equals(field.getJavaType())) {
                buffer.append(" longvarbinary ");
            } else if ("byte[]".equals(field.getJavaType())) {
                buffer.append(" longvarbinary ");
            } else if ("Boolean".equals(field.getJavaType())) {
                buffer.append(" boolean ");
            } else if ("String".equals(field.getJavaType())) {
                buffer.append(" varchar ");
                if (field.getLength() > 0) {
                    buffer.append(" (").append(field.getLength()).append(") ");
                } else {
                    buffer.append(" (50) ");
                }
            }
        } else if ("db2".equalsIgnoreCase(dbType)) {
            if ("Integer".equals(field.getJavaType())) {
                buffer.append(" integer ");
            } else if ("Long".equals(field.getJavaType())) {
                buffer.append(" bigint ");
            } else if ("Double".equals(field.getJavaType())) {
                buffer.append(" double precision ");
            } else if ("Date".equals(field.getJavaType())) {
                buffer.append(" timestamp ");
            } else if ("Clob".equals(field.getJavaType())) {
                buffer.append(" clob (10240000) ");
            } else if ("Blob".equals(field.getJavaType())) {
                buffer.append(" blob ");
            } else if ("byte[]".equals(field.getJavaType())) {
                buffer.append(" blob ");
            } else if ("Boolean".equals(field.getJavaType())) {
                buffer.append(" smallint ");
            } else if ("String".equals(field.getJavaType())) {
                buffer.append(" varchar ");
                if (field.getLength() > 0) {
                    buffer.append(" (").append(field.getLength()).append(") ");
                } else {
                    buffer.append(" (50) ");
                }
            }
        } else if ("oracle".equalsIgnoreCase(dbType)) {
            if ("Integer".equals(field.getJavaType())) {
                buffer.append(" INTEGER ");
            } else if ("Long".equals(field.getJavaType())) {
                buffer.append(" NUMBER(19,0) ");
            } else if ("Double".equals(field.getJavaType())) {
                buffer.append(" NUMBER(*,10) ");
            } else if ("Date".equals(field.getJavaType())) {
                buffer.append(" TIMESTAMP(6) ");
            } else if ("Clob".equals(field.getJavaType())) {
                buffer.append(" CLOB ");
            } else if ("Blob".equals(field.getJavaType())) {
                buffer.append(" BLOB ");
            } else if ("byte[]".equals(field.getJavaType())) {
                buffer.append(" BLOB ");
            } else if ("Boolean".equals(field.getJavaType())) {
                buffer.append(" NUMBER(1,0) ");
            } else if ("String".equals(field.getJavaType())) {
                buffer.append(" NVARCHAR2 ");
                if (field.getLength() > 0) {
                    buffer.append(" (").append(field.getLength()).append(") ");
                } else {
                    buffer.append(" (50) ");
                }
            }
        } else if ("mysql".equalsIgnoreCase(dbType)) {
            if ("Integer".equals(field.getJavaType())) {
                buffer.append(" integer ");
            } else if ("Long".equals(field.getJavaType())) {
                buffer.append(" bigint ");
            } else if ("Double".equals(field.getJavaType())) {
                buffer.append(" double ");
            } else if ("Date".equals(field.getJavaType())) {
                buffer.append(" datetime ");
            } else if ("Clob".equals(field.getJavaType())) {
                buffer.append(" longtext ");
            } else if ("Blob".equals(field.getJavaType())) {
                buffer.append(" longblob ");
            } else if ("byte[]".equals(field.getJavaType())) {
                buffer.append(" longblob ");
            } else if ("Boolean".equals(field.getJavaType())) {
                buffer.append(" tinyint ");
            } else if ("String".equals(field.getJavaType())) {
                buffer.append(" varchar");
                if (field.getLength() > 0) {
                    buffer.append("(").append(field.getLength()).append(") ");
                } else {
                    buffer.append("(50) ");
                }
            }
        } else if ("sqlserver".equalsIgnoreCase(dbType)) {
            if ("Integer".equals(field.getJavaType())) {
                buffer.append(" integer ");
            } else if ("Long".equals(field.getJavaType())) {
                buffer.append(" bigint ");
            } else if ("Double".equals(field.getJavaType())) {
                buffer.append(" double precision ");
            } else if ("Date".equals(field.getJavaType())) {
                buffer.append(" datetime ");
            } else if ("Clob".equals(field.getJavaType())) {
                buffer.append(" text ");
            } else if ("Blob".equals(field.getJavaType())) {
                buffer.append(" image ");
            } else if ("byte[]".equals(field.getJavaType())) {
                buffer.append(" image ");
            } else if ("Boolean".equals(field.getJavaType())) {
                buffer.append(" tinyint ");
            } else if ("String".equals(field.getJavaType())) {
                buffer.append(" nvarchar ");
                if (field.getLength() > 0) {
                    buffer.append(" (").append(field.getLength()).append(") ");
                } else {
                    buffer.append(" (50) ");
                }
            }
        } else if (POSTGRESQL.equalsIgnoreCase(dbType)) {
            if ("Integer".equals(field.getJavaType())) {
                buffer.append(" integer ");
            } else if ("Long".equals(field.getJavaType())) {
                buffer.append(" bigint ");
            } else if ("Double".equals(field.getJavaType())) {
                buffer.append(" double precision ");
            } else if ("Date".equals(field.getJavaType())) {
                buffer.append(" timestamp ");
            } else if ("Clob".equals(field.getJavaType())) {
                buffer.append(" text ");
            } else if ("Blob".equals(field.getJavaType())) {
                buffer.append(" bytea ");
            } else if ("byte[]".equals(field.getJavaType())) {
                buffer.append(" bytea ");
            } else if ("Boolean".equals(field.getJavaType())) {
                buffer.append(" boolean ");
            } else if ("String".equals(field.getJavaType())) {
                buffer.append(" varchar ");
                if (field.getLength() > 0) {
                    buffer.append(" (").append(field.getLength()).append(") ");
                } else {
                    buffer.append(" (50) ");
                }
            }
        } else if ("sqlite".equalsIgnoreCase(dbType)) {
            if ("Integer".equals(field.getJavaType())) {
                buffer.append(" INTEGER ");
            } else if ("Long".equals(field.getJavaType())) {
                buffer.append(" INTEGER ");
            } else if ("Double".equals(field.getJavaType())) {
                buffer.append(" REAL ");
            } else if ("Date".equals(field.getJavaType())) {
                buffer.append(" TEXT ");
            } else if ("Clob".equals(field.getJavaType())) {
                buffer.append(" TEXT ");
            } else if ("Blob".equals(field.getJavaType())) {
                buffer.append(" BLOB ");
            } else if ("byte[]".equals(field.getJavaType())) {
                buffer.append(" BLOB ");
            } else if ("String".equals(field.getJavaType())) {
                buffer.append(" TEXT ");
                if (field.getLength() > 0) {
                    buffer.append(" (").append(field.getLength()).append(") ");
                } else {
                    buffer.append(" (50) ");
                }
            }
        } else {
            throw new RuntimeException(dbType + " is not support database type.");
        }

        buffer.append(";");

        return buffer.toString();
    }

    public static String getAlterTable(TableDefinition classDefinition) {
        StringBuffer buffer = new StringBuffer();
        List<String> cloumns = new java.util.ArrayList<String>();
        Connection connection = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            connection = DBConnectionFactory.getConnection();
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            stmt = connection.createStatement();
            rs = stmt.executeQuery("select * from " + classDefinition.getTableName() + " where 1=0 ");
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String column = rsmd.getColumnName(i);
                cloumns.add(column.toUpperCase());
            }

            Collection<ColumnDefinition> fields = classDefinition.getColumns();
            for (ColumnDefinition field : fields) {
                if (field.getColumnName() != null && !cloumns.contains(field.getColumnName().toUpperCase())) {
                    String str = getAddColumnSql(dbType, classDefinition.getTableName(), field);
                    buffer.append(str);
                    buffer.append("\r\r");
                }
            }

        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
            JdbcUtils.close(connection);
        }

        return buffer.toString();
    }

    public static List<ColumnDefinition> getColumnDefinitions(Connection conn, String tableName) {
        List<ColumnDefinition> columns = new java.util.ArrayList<ColumnDefinition>();
        ResultSet rs = null;
        try {
            List<String> primaryKeys = getPrimaryKeys(conn, tableName);
            String dbType = DBConnectionFactory.getDatabaseType(conn);
            DatabaseMetaData metaData = conn.getMetaData();
            if ("h2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("oracle".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("db2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("mysql".equals(dbType)) {
                tableName = tableName.toLowerCase();
            } else if ("postgresql".equals(dbType)) {
                tableName = tableName.toLowerCase();
            }
            rs = metaData.getColumns(null, null, tableName, null);
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                String typeName = rs.getString("TYPE_NAME");
                int dataType = rs.getInt("DATA_TYPE");
                int nullable = rs.getInt("NULLABLE");
                int length = rs.getInt("COLUMN_SIZE");
                int ordinal = rs.getInt("ORDINAL_POSITION");
                ColumnDefinition column = new ColumnDefinition();
                column.setColumnName(columnName.toLowerCase());
                column.setTitle(column.getName());
                column.setEnglishTitle(column.getName());
                column.setJavaType(FieldType.getJavaType(dataType));
                column.setName(StringTools.camelStyle(column.getColumnName().toLowerCase()));
                if (nullable == 1) {
                    column.setNullable(true);
                } else {
                    column.setNullable(false);
                }
                column.setLength(length);
                column.setOrdinal(ordinal);

                if ("String".equals(column.getJavaType())) {
                    if (column.getLength() > 8000) {
                        column.setJavaType("Clob");
                    }
                }

                if ("Double".equals(column.getJavaType())) {
                    if (column.getLength() == 19) {
                        column.setJavaType("Long");
                    }
                }

                if (StringUtils.equalsIgnoreCase(typeName, "bool")
                        || StringUtils.equalsIgnoreCase(typeName, "boolean")
                        || StringUtils.equalsIgnoreCase(typeName, "bit")
                        || StringUtils.equalsIgnoreCase(typeName, "tinyint")
                        || StringUtils.equalsIgnoreCase(typeName, "smallint")) {
                    column.setJavaType("Boolean");
                }

                if (primaryKeys.contains(columnName.toLowerCase())) {
                    column.setPrimaryKey(true);
                }

                if (!columns.contains(column)) {
                    logger.debug("column name:" + column.getColumnName());
                    columns.add(column);
                }
            }

            return columns;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
        }
    }

    public static List<ColumnDefinition> getColumnDefinitions(String tableName) {
        List<ColumnDefinition> columns = new java.util.ArrayList<ColumnDefinition>();
        Connection conn = null;
        ResultSet rs = null;
        try {
            List<String> primaryKeys = getPrimaryKeys(tableName);

            conn = DBConnectionFactory.getConnection();
            String dbType = DBConnectionFactory.getDatabaseType(conn);

            DatabaseMetaData metaData = conn.getMetaData();
            if ("h2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("oracle".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("db2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("mysql".equals(dbType)) {
                tableName = tableName.toLowerCase();
            } else if ("postgresql".equals(dbType)) {
                tableName = tableName.toLowerCase();
            }
            rs = metaData.getColumns(null, null, tableName, null);
            while (rs.next()) {
                String name = rs.getString("COLUMN_NAME");
                String typeName = rs.getString("TYPE_NAME");
                int dataType = rs.getInt("DATA_TYPE");
                int nullable = rs.getInt("NULLABLE");
                int length = rs.getInt("COLUMN_SIZE");
                int ordinal = rs.getInt("ORDINAL_POSITION");

                ColumnDefinition column = new ColumnDefinition();
                column.setColumnName(name);
                column.setJavaType(FieldType.getJavaType(dataType));
                if (nullable == 1) {
                    column.setNullable(true);
                } else {
                    column.setNullable(false);
                }
                column.setLength(length);
                column.setOrdinal(ordinal);
                column.setName(StringTools.camelStyle(column.getColumnName().toLowerCase()));

                logger.debug(
                        name + " typeName:" + typeName + "[" + dataType + "] " + FieldType.getJavaType(dataType));

                if ("String".equals(column.getJavaType())) {
                    if (column.getLength() > 8000) {
                        column.setJavaType("Clob");
                    }
                }

                if ("Double".equals(column.getJavaType())) {
                    if (column.getLength() == 19) {
                        column.setJavaType("Long");
                    }
                }

                if (StringUtils.equalsIgnoreCase(typeName, "bool")
                        || StringUtils.equalsIgnoreCase(typeName, "boolean")
                        || StringUtils.equalsIgnoreCase(typeName, "bit")
                        || StringUtils.equalsIgnoreCase(typeName, "tinyint")
                        || StringUtils.equalsIgnoreCase(typeName, "smallint")) {
                    column.setJavaType("Boolean");
                }

                if (primaryKeys.contains(name) || primaryKeys.contains(name.toUpperCase())
                        || primaryKeys.contains(name.toLowerCase())) {
                    column.setPrimaryKey(true);
                }

                if (!columns.contains(column)) {
                    columns.add(column);
                }
            }

            return columns;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(conn);
        }
    }

    public static List<ColumnDefinition> getColumnDefinitions(String systemName, String tableName) {
        Connection conn = null;
        try {
            conn = DBConnectionFactory.getConnection(systemName);
            return getColumnDefinitions(conn, tableName);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(conn);
        }
    }

    public static String getCreateTableDDL() {
        StringBuffer buffer = new StringBuffer();
        List<String> tables = getTables();
        String dbType = DBConnectionFactory.getDatabaseType();
        if (tables != null && !tables.isEmpty()) {
            for (String tableName : tables) {
                List<ColumnDefinition> columns = getColumnDefinitions(tableName);
                TableDefinition tableDefinition = new TableDefinition();
                tableDefinition.setTableName(tableName);
                List<String> pks = getPrimaryKeys(tableName);
                if (pks != null && columns != null && !columns.isEmpty()) {
                    for (ColumnDefinition c : columns) {
                        if (pks.contains(c.getColumnName())) {
                            c.setPrimaryKey(true);
                            tableDefinition.setIdColumn(c);
                        }
                    }
                }
                tableDefinition.setColumns(columns);
                String str = getCreateTableScript(dbType, tableDefinition);
                buffer.append(str);
                buffer.append(FileUtils.newline);
                buffer.append(FileUtils.newline);
            }
        }
        return buffer.toString();
    }

    public static String getCreateTableDDL(String targetDbType) {
        StringBuffer buffer = new StringBuffer();
        List<String> tables = getTables();

        if (tables != null && !tables.isEmpty()) {
            for (String tableName : tables) {
                List<ColumnDefinition> columns = getColumnDefinitions(tableName);
                TableDefinition tableDefinition = new TableDefinition();
                tableDefinition.setTableName(tableName);
                List<String> pks = getPrimaryKeys(tableName);
                if (pks != null && columns != null && !columns.isEmpty()) {
                    for (ColumnDefinition c : columns) {
                        if (pks.contains(c.getColumnName())) {
                            c.setPrimaryKey(true);
                            tableDefinition.setIdColumn(c);
                        }
                    }
                }
                tableDefinition.setColumns(columns);
                String str = getCreateTableScript(targetDbType, tableDefinition);
                buffer.append(str);
                buffer.append(FileUtils.newline);
                buffer.append(FileUtils.newline);
            }
        }
        return buffer.toString();
    }

    public static String getCreateTableScript(String dbType, TableDefinition tableDefinition) {
        StringBuffer buffer = new StringBuffer();
        Collection<ColumnDefinition> columns = tableDefinition.getColumns();
        buffer.append(" create table ").append(tableDefinition.getTableName().toUpperCase());
        buffer.append(" ( ");
        Collection<String> cols = new HashSet<String>();
        ColumnDefinition idField = tableDefinition.getIdColumn();
        if (idField != null) {
            buffer.append(newline);
            buffer.append("    ").append(idField.getColumnName().toUpperCase());
            if ("db2".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(idField.getJavaType())) {
                    buffer.append(" integer ");
                } else if ("Long".equals(idField.getJavaType())) {
                    buffer.append(" bigint ");
                } else if ("Double".equals(idField.getJavaType())) {
                    buffer.append(" double precision ");
                } else if ("Date".equals(idField.getJavaType())) {
                    buffer.append(" timestamp ");
                } else if ("String".equals(idField.getJavaType())) {
                    buffer.append(" varchar ");
                    if (idField.getLength() > 0) {
                        buffer.append(" (").append(idField.getLength()).append(") ");
                    } else {
                        buffer.append(" (50) ");
                    }
                }
            } else if ("oracle".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(idField.getJavaType())) {
                    buffer.append(" INTEGER ");
                } else if ("Long".equals(idField.getJavaType())) {
                    buffer.append(" NUMBER(19,0) ");
                } else if ("Double".equals(idField.getJavaType())) {
                    buffer.append(" NUMBER(*,10) ");
                } else if ("Date".equals(idField.getJavaType())) {
                    buffer.append(" TIMESTAMP(6) ");
                } else if ("String".equals(idField.getJavaType())) {
                    buffer.append(" NVARCHAR2 ");
                    if (idField.getLength() > 0) {
                        buffer.append(" (").append(idField.getLength()).append(") ");
                    } else {
                        buffer.append(" (50) ");
                    }
                }
            } else if ("mysql".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(idField.getJavaType())) {
                    buffer.append(" integer ");
                } else if ("Long".equals(idField.getJavaType())) {
                    buffer.append(" bigint ");
                } else if ("Double".equals(idField.getJavaType())) {
                    buffer.append(" double ");
                } else if ("Date".equals(idField.getJavaType())) {
                    buffer.append(" datetime ");
                } else if ("String".equals(idField.getJavaType())) {
                    buffer.append(" varchar ");
                    if (idField.getLength() > 0) {
                        buffer.append(" (").append(idField.getLength()).append(") ");
                    } else {
                        buffer.append(" (50) ");
                    }
                }
            } else if ("sqlserver".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(idField.getJavaType())) {
                    buffer.append(" integer ");
                } else if ("Long".equals(idField.getJavaType())) {
                    buffer.append(" bigint ");
                } else if ("Double".equals(idField.getJavaType())) {
                    buffer.append(" double precision ");
                } else if ("Date".equals(idField.getJavaType())) {
                    buffer.append(" datetime ");
                } else if ("String".equals(idField.getJavaType())) {
                    buffer.append(" nvarchar ");
                    if (idField.getLength() > 0) {
                        buffer.append(" (").append(idField.getLength()).append(") ");
                    } else {
                        buffer.append(" (50) ");
                    }
                }
            } else if ("postgresql".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(idField.getJavaType())) {
                    buffer.append(" integer ");
                } else if ("Long".equals(idField.getJavaType())) {
                    buffer.append(" bigint ");
                } else if ("Double".equals(idField.getJavaType())) {
                    buffer.append(" double precision ");
                } else if ("Date".equals(idField.getJavaType())) {
                    buffer.append(" timestamp ");
                } else if ("String".equals(idField.getJavaType())) {
                    buffer.append(" varchar ");
                    if (idField.getLength() > 0) {
                        buffer.append(" (").append(idField.getLength()).append(") ");
                    } else {
                        buffer.append(" (50) ");
                    }
                }
            } else if ("h2".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(idField.getJavaType())) {
                    buffer.append(" integer ");
                } else if ("Long".equals(idField.getJavaType())) {
                    buffer.append(" bigint ");
                } else if ("Double".equals(idField.getJavaType())) {
                    buffer.append(" double ");
                } else if ("Date".equals(idField.getJavaType())) {
                    buffer.append(" timestamp ");
                } else if ("String".equals(idField.getJavaType())) {
                    buffer.append(" varchar ");
                    if (idField.getLength() > 0) {
                        buffer.append(" (").append(idField.getLength()).append(") ");
                    } else {
                        buffer.append(" (50) ");
                    }
                }
            } else if ("sqlite".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(idField.getJavaType())) {
                    buffer.append(" INTEGER ");
                } else if ("Long".equals(idField.getJavaType())) {
                    buffer.append(" INTEGER ");
                } else if ("Double".equals(idField.getJavaType())) {
                    buffer.append(" REAL ");
                } else if ("Date".equals(idField.getJavaType())) {
                    buffer.append(" TEXT ");
                } else if ("String".equals(idField.getJavaType())) {
                    buffer.append(" TEXT ");
                }
            }
            buffer.append(" not null ");
        }
        int index = 0;
        for (ColumnDefinition column : columns) {
            if (StringUtils.isEmpty(column.getColumnName()) || StringUtils.isEmpty(column.getJavaType())
                    || (idField != null
                            && StringUtils.equalsIgnoreCase(idField.getColumnName(), column.getColumnName()))) {
                continue;
            }
            if (cols.contains(column.getColumnName().toLowerCase())) {
                continue;
            }
            if (idField != null) {
                buffer.append(",");
            } else {
                if (index > 0) {
                    buffer.append(",");
                }
            }

            index++;
            buffer.append(newline);
            buffer.append("    ").append(column.getColumnName().toUpperCase());
            if ("db2".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(column.getJavaType())) {
                    buffer.append(" integer ");
                } else if ("Long".equals(column.getJavaType())) {
                    buffer.append(" bigint ");
                } else if ("Double".equals(column.getJavaType())) {
                    buffer.append(" double precision ");
                } else if ("Date".equals(column.getJavaType())) {
                    buffer.append(" timestamp ");
                } else if ("Clob".equals(column.getJavaType())) {
                    buffer.append(" clob (10240000) ");
                } else if ("Blob".equals(column.getJavaType())) {
                    buffer.append(" blob ");
                } else if ("byte[]".equals(column.getJavaType())) {
                    buffer.append(" blob ");
                } else if ("Boolean".equals(column.getJavaType())) {
                    buffer.append(" smallint ");
                } else if ("String".equals(column.getJavaType())) {
                    buffer.append(" varchar ");
                    if (column.getLength() > 0 && column.getLength() <= 4000) {
                        buffer.append(" (").append(column.getLength()).append(") ");
                    } else {
                        buffer.append(" (250) ");
                    }
                }
            } else if ("oracle".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(column.getJavaType())) {
                    buffer.append(" INTEGER ");
                } else if ("Long".equals(column.getJavaType())) {
                    buffer.append(" NUMBER(19,0) ");
                } else if ("Double".equals(column.getJavaType())) {
                    buffer.append(" NUMBER(*,10) ");
                } else if ("Date".equals(column.getJavaType())) {
                    buffer.append(" TIMESTAMP(6) ");
                } else if ("Clob".equals(column.getJavaType())) {
                    buffer.append(" CLOB ");
                } else if ("Blob".equals(column.getJavaType())) {
                    buffer.append(" BLOB ");
                } else if ("byte[]".equals(column.getJavaType())) {
                    buffer.append(" BLOB ");
                } else if ("Boolean".equals(column.getJavaType())) {
                    buffer.append(" NUMBER(1,0) ");
                } else if ("String".equals(column.getJavaType())) {
                    buffer.append(" NVARCHAR2 ");
                    if (column.getLength() > 0 && column.getLength() <= 4000) {
                        buffer.append(" (").append(column.getLength()).append(") ");
                    } else {
                        buffer.append(" (250) ");
                    }
                }
            } else if ("mysql".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(column.getJavaType())) {
                    buffer.append(" integer ");
                } else if ("Long".equals(column.getJavaType())) {
                    buffer.append(" bigint ");
                } else if ("Double".equals(column.getJavaType())) {
                    buffer.append(" double ");
                } else if ("Date".equals(column.getJavaType())) {
                    buffer.append(" datetime ");
                } else if ("Clob".equals(column.getJavaType())) {
                    buffer.append(" longtext ");
                } else if ("Blob".equals(column.getJavaType())) {
                    buffer.append(" longblob ");
                } else if ("byte[]".equals(column.getJavaType())) {
                    buffer.append(" longblob ");
                } else if ("Boolean".equals(column.getJavaType())) {
                    buffer.append(" tinyint ");
                } else if ("String".equals(column.getJavaType())) {
                    buffer.append(" varchar ");
                    if (column.getLength() > 0 && column.getLength() <= 4000) {
                        buffer.append(" (").append(column.getLength()).append(") ");
                    } else {
                        buffer.append(" (250) ");
                    }
                }
            } else if ("sqlserver".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(column.getJavaType())) {
                    buffer.append(" integer ");
                } else if ("Long".equals(column.getJavaType())) {
                    buffer.append(" bigint ");
                } else if ("Double".equals(column.getJavaType())) {
                    buffer.append(" double precision ");
                } else if ("Date".equals(column.getJavaType())) {
                    buffer.append(" datetime ");
                } else if ("Clob".equals(column.getJavaType())) {
                    buffer.append(" text ");
                } else if ("Blob".equals(column.getJavaType())) {
                    buffer.append(" image ");
                } else if ("byte[]".equals(column.getJavaType())) {
                    buffer.append(" image ");
                } else if ("Boolean".equals(column.getJavaType())) {
                    buffer.append(" tinyint ");
                } else if ("Boolean".equals(column.getJavaType())) {
                    buffer.append(" tinyint ");
                } else if ("String".equals(column.getJavaType())) {
                    buffer.append(" nvarchar ");
                    if (column.getLength() > 0 && column.getLength() <= 4000) {
                        buffer.append(" (").append(column.getLength()).append(") ");
                    } else {
                        buffer.append(" (250) ");
                    }
                }
            } else if ("postgresql".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(column.getJavaType())) {
                    buffer.append(" integer ");
                } else if ("Long".equals(column.getJavaType())) {
                    buffer.append(" bigint ");
                } else if ("Double".equals(column.getJavaType())) {
                    buffer.append(" double precision ");
                } else if ("Date".equals(column.getJavaType())) {
                    buffer.append(" timestamp ");
                } else if ("Clob".equals(column.getJavaType())) {
                    buffer.append(" text ");
                } else if ("Blob".equals(column.getJavaType())) {
                    buffer.append(" bytea ");
                } else if ("byte[]".equals(column.getJavaType())) {
                    buffer.append(" bytea ");
                } else if ("Boolean".equals(column.getJavaType())) {
                    buffer.append(" boolean ");
                } else if ("String".equals(column.getJavaType())) {
                    buffer.append(" varchar ");
                    if (column.getLength() > 0 && column.getLength() <= 4000) {
                        buffer.append(" (").append(column.getLength()).append(") ");
                    } else {
                        buffer.append(" (250) ");
                    }
                }
            } else if ("h2".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(column.getJavaType())) {
                    buffer.append(" integer ");
                } else if ("Long".equals(column.getJavaType())) {
                    buffer.append(" bigint ");
                } else if ("Double".equals(column.getJavaType())) {
                    buffer.append(" double ");
                } else if ("Date".equals(column.getJavaType())) {
                    buffer.append(" timestamp ");
                } else if ("Clob".equals(column.getJavaType())) {
                    buffer.append(" clob ");
                } else if ("Blob".equals(column.getJavaType())) {
                    buffer.append(" longvarbinary ");
                } else if ("byte[]".equals(column.getJavaType())) {
                    buffer.append(" longvarbinary ");
                } else if ("Boolean".equals(column.getJavaType())) {
                    buffer.append(" boolean ");
                } else if ("String".equals(column.getJavaType())) {
                    buffer.append(" varchar ");
                    if (column.getLength() > 0 && column.getLength() <= 4000) {
                        buffer.append(" (").append(column.getLength()).append(") ");
                    } else {
                        buffer.append(" (50) ");
                    }
                }
            } else if ("sqlite".equalsIgnoreCase(dbType)) {
                if ("Integer".equals(column.getJavaType())) {
                    buffer.append(" INTEGER ");
                } else if ("Boolean".equals(column.getJavaType())) {
                    buffer.append(" INTEGER ");
                } else if ("Long".equals(column.getJavaType())) {
                    buffer.append(" INTEGER ");
                } else if ("Double".equals(column.getJavaType())) {
                    buffer.append(" REAL ");
                } else if ("Date".equals(column.getJavaType())) {
                    buffer.append(" TEXT ");
                } else if ("Clob".equals(column.getJavaType())) {
                    buffer.append(" TEXT ");
                } else if ("Blob".equals(column.getJavaType())) {
                    buffer.append(" BLOB ");
                } else if ("byte[]".equals(column.getJavaType())) {
                    buffer.append(" BLOB ");
                } else if ("String".equals(column.getJavaType())) {
                    buffer.append(" TEXT ");
                }
            }
            cols.add(column.getColumnName().toLowerCase());
        }

        if (idField != null) {
            buffer.append(",");
            buffer.append(newline);
            buffer.append("    primary key (").append(idField.getColumnName().toUpperCase()).append(") ");
        }
        buffer.append(newline);
        if ("mysql".equalsIgnoreCase(dbType)) {
            buffer.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;");
        } else {
            buffer.append(");");
        }

        return buffer.toString();
    }

    public static List<FieldDefinition> getFieldDefinitions(String tableName) {
        List<FieldDefinition> columns = new java.util.ArrayList<FieldDefinition>();
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionFactory.getConnection();
            List<String> primaryKeys = getPrimaryKeys(conn, tableName);
            String dbType = DBConnectionFactory.getDatabaseType(conn);
            DatabaseMetaData metaData = conn.getMetaData();

            if ("h2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("oracle".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("db2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("mysql".equals(dbType)) {
                tableName = tableName.toLowerCase();
            } else if ("postgresql".equals(dbType)) {
                tableName = tableName.toLowerCase();
            }
            rs = metaData.getColumns(null, null, tableName, null);
            while (rs.next()) {
                String name = rs.getString("COLUMN_NAME");
                int dataType = rs.getInt("DATA_TYPE");
                int nullable = rs.getInt("NULLABLE");
                int length = rs.getInt("COLUMN_SIZE");
                int ordinal = rs.getInt("ORDINAL_POSITION");
                FieldDefinition column = new ColumnDefinition();
                column.setColumnName(name);
                column.setType(FieldType.getJavaType(dataType));
                if (nullable == 1) {
                    column.setNullable(true);
                } else {
                    column.setNullable(false);
                }
                column.setLength(length);
                column.setSortNo(ordinal);

                if ("String".equals(column.getType())) {
                    if (column.getLength() > 8000) {
                        column.setType("Clob");
                    }
                }

                if (primaryKeys.contains(name)) {
                    column.setNullable(false);
                }

                column.setName(StringTools.camelStyle(name));
                column.setEnglishTitle(StringTools.camelStyle(name));

                columns.add(column);
            }
            return columns;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(conn);
        }
    }

    public static List<String> getPrimaryKeys(Connection connection, String tableName) {
        ResultSet rs = null;
        List<String> primaryKeys = new java.util.ArrayList<String>();
        try {
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            DatabaseMetaData metaData = connection.getMetaData();

            if ("h2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("oracle".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("db2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("mysql".equals(dbType)) {
                tableName = tableName.toLowerCase();
            } else if (POSTGRESQL.equals(dbType)) {
                tableName = tableName.toLowerCase();
            }

            rs = metaData.getPrimaryKeys(null, null, tableName);
            while (rs.next()) {
                primaryKeys.add(rs.getString("column_name").toLowerCase());
            }

            // logger.debug(tableName + " primaryKeys:" + primaryKeys);
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
        }
        return primaryKeys;
    }

    public static List<String> getPrimaryKeys(String tableName) {
        List<String> primaryKeys = new java.util.ArrayList<String>();
        Connection connection = null;
        ResultSet rs = null;
        try {
            connection = DBConnectionFactory.getConnection();
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            DatabaseMetaData metaData = connection.getMetaData();

            if ("h2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("oracle".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("db2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("mysql".equals(dbType)) {
                tableName = tableName.toLowerCase();
            } else if (POSTGRESQL.equals(dbType)) {
                tableName = tableName.toLowerCase();
            }

            rs = metaData.getPrimaryKeys(null, null, tableName);
            while (rs.next()) {
                primaryKeys.add(rs.getString("column_name"));
            }

            // logger.debug(tableName + " primaryKeys:" + primaryKeys);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(connection);
        }
        return primaryKeys;
    }

    public static List<String> getPrimaryKeys(String systemName, String tableName) {
        List<String> primaryKeys = new java.util.ArrayList<String>();
        Connection connection = null;
        ResultSet rs = null;
        try {
            connection = DBConnectionFactory.getConnection(systemName);
            String dbType = DBConnectionFactory.getDatabaseType(connection);
            DatabaseMetaData metaData = connection.getMetaData();

            if ("h2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("oracle".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("db2".equals(dbType)) {
                tableName = tableName.toUpperCase();
            } else if ("mysql".equals(dbType)) {
                tableName = tableName.toLowerCase();
            } else if (POSTGRESQL.equals(dbType)) {
                tableName = tableName.toLowerCase();
            }

            rs = metaData.getPrimaryKeys(null, null, tableName);
            while (rs.next()) {
                primaryKeys.add(rs.getString("column_name"));
            }

        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(connection);
        }
        return primaryKeys;
    }

    public static int getTableCount(Connection connection, String tableName) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(" select count(*) from " + tableName);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
        }
        return -1;
    }

    public static int getTableCount(String tableName) {
        Connection connection = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            connection = DBConnectionFactory.getConnection();
            stmt = connection.createStatement();
            rs = stmt.executeQuery(" select count(*) from " + tableName);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
            JdbcUtils.close(stmt);
            JdbcUtils.close(connection);
        }
        return -1;
    }

    public static List<String> getTables() {
        List<String> tables = new java.util.ArrayList<String>();
        String[] types = { "TABLE" };
        Connection connection = null;
        try {
            connection = DBConnectionFactory.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet rs = metaData.getTables(null, null, null, types);
            while (rs.next()) {
                tables.add(rs.getObject("TABLE_NAME").toString());
            }
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(connection);
        }
        return tables;
    }

    public static List<String> getTables(Connection connection) {
        List<String> tables = new java.util.ArrayList<String>();
        String[] types = { "TABLE" };
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet rs = metaData.getTables(null, null, null, types);
            while (rs.next()) {
                tables.add(rs.getObject("TABLE_NAME").toString());
            }
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
        return tables;
    }

    public static boolean isAllowedSql(String sql) {
        if (StringUtils.isEmpty(sql)) {
            return false;
        }

        boolean isLegal = true;

        sql = sql.toLowerCase();
        if (sql.indexOf("userinfo") != -1) {
            isLegal = false;
        }
        if (sql.indexOf("sys_user") != -1) {
            isLegal = false;
        }
        if (sql.indexOf("sys_database") != -1) {
            isLegal = false;
        }
        if (sql.indexOf("proj_muiprojlist") != -1) {
            isLegal = false;
        }
        if (sql.indexOf("sys_key") != -1) {
            isLegal = false;
        }
        if (sql.indexOf("sys_property") != -1) {
            isLegal = false;
        }

        return isLegal;
    }

    public static boolean isAllowedTable(String tableName) {
        if (StringUtils.equalsIgnoreCase(tableName, "userinfo")) {
            return false;
        } else if (StringUtils.equalsIgnoreCase(tableName, "sys_user")) {
            return false;
        } else if (StringUtils.equalsIgnoreCase(tableName, "sys_database")) {
            return false;
        } else if (StringUtils.equalsIgnoreCase(tableName, "sys_key")) {
            return false;
        } else if (StringUtils.equalsIgnoreCase(tableName, "proj_muiprojlist")) {
            return false;
        }
        return true;
    }

    public static boolean isLegalQuerySql(String sql) {
        if (StringUtils.isEmpty(sql)) {
            return false;
        }

        boolean isLegal = true;

        sql = sql.toLowerCase();
        if (sql.indexOf(" insert ") != -1) {
            isLegal = false;
        }
        if (sql.indexOf(" update ") != -1) {
            isLegal = false;
        }
        if (sql.indexOf(" delete ") != -1) {
            isLegal = false;
        }
        if (sql.indexOf(" create ") != -1) {
            isLegal = false;
        }
        if (sql.indexOf(" alter ") != -1) {
            isLegal = false;
        }
        if (sql.indexOf(" drop ") != -1) {
            isLegal = false;
        }

        return isLegal;
    }

    public static boolean isTableColumn(String columnName) {
        if (columnName == null || columnName.trim().length() < 2 || columnName.trim().length() > 26) {
            return false;
        }
        char[] sourceChrs = columnName.toCharArray();
        Character chr = Character.valueOf(sourceChrs[0]);
        if (!((chr.charValue() == 95) || (65 <= chr.charValue() && chr.charValue() <= 90)
                || (97 <= chr.charValue() && chr.charValue() <= 122))) {
            return false;
        }
        for (int i = 1; i < sourceChrs.length; i++) {
            chr = Character.valueOf(sourceChrs[i]);
            if (!((chr.charValue() == 95) || (47 <= chr.charValue() && chr.charValue() <= 57)
                    || (65 <= chr.charValue() && chr.charValue() <= 90)
                    || (97 <= chr.charValue() && chr.charValue() <= 122))) {
                return false;
            }
        }
        return true;
    }

    public static boolean isTableName(String sourceString) {
        if (sourceString == null || sourceString.trim().length() < 2 || sourceString.trim().length() > 25) {
            return false;
        }
        char[] sourceChrs = sourceString.toCharArray();
        Character chr = Character.valueOf(sourceChrs[0]);
        if (!((chr.charValue() == 95) || (65 <= chr.charValue() && chr.charValue() <= 90)
                || (97 <= chr.charValue() && chr.charValue() <= 122))) {
            return false;
        }
        for (int i = 1; i < sourceChrs.length; i++) {
            chr = Character.valueOf(sourceChrs[i]);
            if (!((chr.charValue() == 95) || (47 <= chr.charValue() && chr.charValue() <= 57)
                    || (65 <= chr.charValue() && chr.charValue() <= 90)
                    || (97 <= chr.charValue() && chr.charValue() <= 122))) {
                return false;
            }
        }
        return true;
    }

    public static boolean isTemoraryTable(String tableName) {
        tableName = tableName.toLowerCase();

        if (tableName.startsWith("tmp_")) {
            return true;
        }
        if (tableName.startsWith("temp_")) {
            return true;
        }
        if (tableName.startsWith("_log_")) {
            return true;
        }
        if (tableName.startsWith("demo_")) {
            return true;
        }
        if (tableName.startsWith("wwv_")) {
            return true;
        }
        if (tableName.startsWith("aq_")) {
            return true;
        }
        if (tableName.startsWith("bsln_")) {
            return true;
        }
        if (tableName.startsWith("mgmt_")) {
            return true;
        }
        if (tableName.startsWith("ogis_")) {
            return true;
        }
        if (tableName.startsWith("ols_")) {
            return true;
        }
        if (tableName.startsWith("em_")) {
            return true;
        }
        if (tableName.startsWith("openls_")) {
            return true;
        }
        if (tableName.startsWith("mrac_")) {
            return true;
        }
        if (tableName.startsWith("orddcm_")) {
            return true;
        }
        if (tableName.startsWith("x_")) {
            return true;
        }
        if (tableName.startsWith("wlm_")) {
            return true;
        }
        if (tableName.startsWith("olap_")) {
            return true;
        }
        if (tableName.startsWith("ggs_")) {
            return true;
        }

        if (tableName.startsWith("logmnrc_")) {
            return true;
        }
        if (tableName.startsWith("logmnrg_")) {
            return true;
        }
        if (tableName.startsWith("olap_")) {
            return true;
        }
        if (tableName.startsWith("sto_")) {
            return true;
        }
        if (tableName.startsWith("sdo_")) {
            return true;
        }
        if (tableName.startsWith("sys_iot_")) {
            return true;
        }
        if (tableName.indexOf("$") != -1) {
            return true;
        }
        if (tableName.indexOf("+") != -1) {
            return true;
        }
        if (tableName.indexOf("-") != -1) {
            return true;
        }
        if (tableName.indexOf("?") != -1) {
            return true;
        }
        if (tableName.indexOf("=") != -1) {
            return true;
        }
        return false;
    }

    private static Map<String, Object> lowerKeyMap(Map<String, Object> params) {
        Map<String, Object> dataMap = new java.util.HashMap<String, Object>();
        Set<Entry<String, Object>> entrySet = params.entrySet();
        for (Entry<String, Object> entry : entrySet) {
            String key = entry.getKey();
            Object value = entry.getValue();
            dataMap.put(key, value);
            dataMap.put(key.toLowerCase(), value);
        }
        return dataMap;
    }

    public static void main(String[] args) {
        System.out.println(DBUtils.isAllowedSql(" select * from  userinfo"));
        List<ColumnDefinition> columns = new java.util.ArrayList<ColumnDefinition>();
        ColumnDefinition c1 = new ColumnDefinition();
        c1.setColumnName("choosepublishflag");
        c1.setLength(1);
        c1.setJavaType("String");
        columns.add(c1);

        ColumnDefinition c2 = new ColumnDefinition();
        c2.setColumnName("choosepublishtime");
        c2.setJavaType("Long");
        columns.add(c2);

        // DBUtils.alterTable("qlhighway", "volume", columns);

        System.out.println();
        // FileUtils.save("data/pMagicDev.sql",
        // DBUtils.getCreateTableDDL("pMagicDev")
        // .getBytes());

        FileUtils.save("data/glaf.sql", DBUtils.getCreateTableDDL("glaf").getBytes());
    }

    public static String removeOrders(String sql) {
        Assert.hasText(sql);
        Pattern pattern = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        StringBuffer buf = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(buf, "");
        }
        matcher.appendTail(buf);
        return buf.toString();
    }

    public static SqlExecutor replaceSQL(String sql, Map<String, Object> params) {
        SqlExecutor sqlExecutor = new SqlExecutor();
        sqlExecutor.setSql(sql);
        if (sql == null || params == null) {
            return sqlExecutor;
        }

        List<Object> values = new java.util.ArrayList<Object>();
        Map<String, Object> dataMap = lowerKeyMap(params);
        StringBuffer sb = new StringBuffer();
        int begin = 0;
        int end = 0;
        boolean flag = false;
        for (int i = 0; i < sql.length(); i++) {
            if (sql.charAt(i) == '#' && sql.charAt(i + 1) == '{') {
                sb.append(sql.substring(end, i));
                begin = i + 2;
                flag = true;
            }
            if (flag && sql.charAt(i) == '}') {
                String temp = sql.substring(begin, i);
                temp = temp.toLowerCase();
                if (dataMap.get(temp) != null) {
                    Object value = dataMap.get(temp);
                    /**
                     * Collection?
                     */
                    if (value != null && value instanceof Collection) {
                        Collection<?> coll = (Collection<?>) value;
                        if (coll != null && !coll.isEmpty()) {
                            Iterator<?> iter = coll.iterator();
                            while (iter.hasNext()) {
                                values.add(iter.next());
                                sb.append(" ? ");
                                if (iter.hasNext()) {
                                    sb.append(", ");
                                }
                            }
                        }
                    } else {
                        sb.append(" ? ");
                        values.add(value);
                    }
                    end = i + 1;
                    flag = false;
                } else {
                    sb.append(" ? ");
                    end = i + 1;
                    flag = false;
                    values.add(null);
                }
            }
            if (i == sql.length() - 1) {
                sb.append(sql.substring(end, i + 1));
            }
        }
        sqlExecutor.setParameter(values);
        sqlExecutor.setSql(sb.toString());
        return sqlExecutor;
    }

    public static String replaceTextParas(String str, Map<String, Object> params) {
        if (str == null || params == null) {
            return str;
        }
        Map<String, Object> dataMap = lowerKeyMap(params);
        StringBuffer sb = new StringBuffer();
        int begin = 0;
        int end = 0;
        boolean flag = false;
        for (int i = 0; i < str.length(); i++) {
            if (str.charAt(i) == '#' && str.charAt(i + 1) == '{') {
                sb.append(str.substring(end, i));
                begin = i + 2;
                flag = true;
            }
            if (flag && str.charAt(i) == '}') {
                String temp = str.substring(begin, i);
                temp = temp.toLowerCase();
                if (dataMap.get(temp) != null) {
                    Object value = dataMap.get(temp);
                    if (value instanceof Date) {
                        String s = DateUtils.getDate((Date) value);
                        sb.append(s);
                    } else {
                        sb.append(value.toString());
                    }
                    end = i + 1;
                    flag = false;
                } else {
                    sb.append("#{").append(temp).append('}');
                    end = i + 1;
                    flag = false;
                }
            }
            if (i == str.length() - 1) {
                sb.append(str.substring(end, i + 1));
            }
        }
        return sb.toString();
    }

    @SuppressWarnings("resource")
    public static boolean tableExists(Connection connection, String tableName) {
        DatabaseMetaData dbmd = null;
        ResultSet rs = null;
        try {
            dbmd = connection.getMetaData();
            rs = dbmd.getTables(null, null, null, new String[] { "TABLE" });
            while (rs.next()) {
                String table = rs.getString("TABLE_NAME");
                if (StringUtils.equalsIgnoreCase(tableName, table)) {
                    return true;
                }
            }
            logger.info(tableName + " not exist.");
            return false;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(rs);
        }
    }

    /**
     * ??
     * 
     * @param systemName
     * @param tableName
     * @return
     */
    public static boolean tableExists(String tableName) {
        Connection conn = null;
        try {
            conn = DBConnectionFactory.getConnection();
            return tableExists(conn, tableName);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(conn);
        }
    }

    /**
     * ??
     * 
     * @param systemName
     * @param tableName
     * @return
     */
    public static boolean tableExists(String systemName, String tableName) {
        Connection conn = null;
        try {
            conn = DBConnectionFactory.getConnection(systemName);
            return tableExists(conn, tableName);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        } finally {
            JdbcUtils.close(conn);
        }
    }

}