jp.co.golorp.emarf.sql.MetaData.java Source code

Java tutorial

Introduction

Here is the source code for jp.co.golorp.emarf.sql.MetaData.java

Source

/*
 * Apache License, Version 2.0???????
 * ?????????????
 *
 * ??http://www.apache.org/licenses/LICENSE-2.0?????
 *
 * ???????????????
 * ????????
 * ??????????????????????
 *
 * ????????????????????????
 */
package jp.co.golorp.emarf.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Set;
import java.util.TreeMap;

import org.apache.commons.dbutils.DbUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import jp.co.golorp.emarf.exception.SystemError;
import jp.co.golorp.emarf.sql.info.ColumnInfo;
import jp.co.golorp.emarf.sql.info.TableInfo;
import jp.co.golorp.emarf.sql.info.ViewInfo;
import jp.co.golorp.emarf.util.StringUtil;

/**
 * 
 *
 * @author oukuf@golorp
 */
public final class MetaData {

    /*
     * ************************************************************ 
     */

    /** Logger */
    private static final Logger LOG = LoggerFactory.getLogger(MetaData.class);

    /** MetaData.properties */
    private static final ResourceBundle BUNDLE = ResourceBundle.getBundle(MetaData.class.getSimpleName());

    /** ??TABLE_INFOS?????TABLE_INFOS?????? */
    private static final Set<String> NOT_EXIST_COLUMN_INFO_NAMES = new HashSet<String>();

    /**  */
    private static final List<TableInfo> TABLE_INFOS = MetaData.prepareTableInfos();

    /** ????? */
    private static TableCommentSources commentSource = null;

    /**
     * ?????
     *
     * @author oukuf@golorp
     */
    private enum TableCommentSources {

        /** MySQL?? */
        showTableStatus,

        /** Oracle?? */
        userTabComments
    }

    /*
     * ************************************************************ 
     */

    /**
     * 
     */
    private MetaData() {
    }

    /*
     * ************************************************************ 
     */

    /**
     * @return 
     */
    public static List<TableInfo> getTableInfos() {
        return TABLE_INFOS;
    }

    /**
     * ???????
     *
     * @return ????????????
     */
    public static Map<String, String> getModelMeis() {

        Map<String, String> ret = new TreeMap<String, String>();
        for (TableInfo tableInfo : TABLE_INFOS) {
            String tableName = tableInfo.getTableName();
            String modelName = StringUtil.toUpperCamelCase(tableName);
            ret.put(modelName, tableInfo.getTableMei());
        }

        return ret;
    }

    /**
     * @param modelName
     *            ??
     * @return 
     */
    public static TableInfo getTableInfo(final String modelName) {

        for (TableInfo tableInfo : TABLE_INFOS) {
            if (tableInfo.getModelName().equals(modelName)) {
                return tableInfo;
            }
        }

        LOG.debug("table is not exist. [" + modelName + "]");

        return null;
    }

    /**
     * @param modelName
     *            ??
     * @return 
     */
    public static List<ColumnInfo> getColumnInfos(final String modelName) {

        TableInfo tableInfo = getTableInfo(modelName);
        if (tableInfo != null) {
            return tableInfo.getColumnInfos();
        }

        return null;
    }

    /**
     * @param modelName
     *            ??
     * @param propertyName
     *            ??
     * @return ????????
     */
    public static ColumnInfo getColumnInfo(final String modelName, final String propertyName) {

        // ??????????
        TableInfo tableInfo = MetaData.getTableInfo(modelName);
        if (tableInfo != null) {
            List<ColumnInfo> columnInfos = tableInfo.getColumnInfos();
            for (ColumnInfo columnInfo : columnInfos) {
                if (columnInfo.getPropertyName().equals(propertyName)) {
                    return columnInfo;
                }
            }
        }

        return null;
    }

    /**
     * @return ?
     */
    private static List<TableInfo> prepareTableInfos() {

        String catalog = BUNDLE.getString("catalog");

        String schemaPattern = BUNDLE.getString("schemaPattern");

        String tableNamePattern = BUNDLE.getString("tableNamePattern");

        String typesText = BUNDLE.getString("types");

        String[] types = null;
        if (StringUtil.isNotBlank(typesText)) {
            types = StringUtil.split(typesText);
        }

        // oracle
        if (catalog.equals("")) {
            catalog = null;
        }
        if (schemaPattern.equals("")) {
            schemaPattern = null;
        }
        if (tableNamePattern.equals("")) {
            tableNamePattern = null;
        }
        if (StringUtil.isBlank(types)) {
            types = null;
        }

        List<TableInfo> tableInfos = new ArrayList<TableInfo>();

        Connection cn = Connections.get();
        try {
            ResultSet rs = null;
            try {

                // ?
                rs = cn.getMetaData().getTables(catalog, schemaPattern, tableNamePattern, types);
                while (rs.next()) {

                    // ??
                    String tableName = rs.getString("TABLE_NAME");

                    // 
                    String tableType = rs.getString("TABLE_TYPE");

                    // 
                    String remarks = rs.getString("REMARKS");
                    if (StringUtil.isBlank(remarks)) {
                        if (commentSource == TableCommentSources.showTableStatus) {
                            // MySQL???showTableStatus?????
                            remarks = getTableCommentByShowTableStatus(cn, tableName);
                        } else if (commentSource == TableCommentSources.userTabComments) {
                            // Oracle???userTabComments?????
                            remarks = getTableCommentByUserTabComments(cn, tableName);
                        } else {
                            // ??
                            remarks = getTableComment(cn, tableName);
                        }
                    }

                    // view??
                    Map<String, ViewInfo> viewInfos = null;
                    Set<String> primaryKeys = null;
                    if (tableType.equals("VIEW")) {
                        viewInfos = getViewInfos(cn, tableName);
                    } else {
                        primaryKeys = MetaData.getPrimaryKeys(cn, tableName);
                    }

                    // ?????
                    String tableMei = null;
                    if (StringUtil.isNotBlank(remarks)) {
                        tableMei = remarks.split("\t")[0];
                    }

                    // ?
                    List<ColumnInfo> columnInfos = MetaData.getColumnInfos(cn, tableName);

                    // ??
                    String modelName = StringUtil.toUpperCamelCase(tableName);

                    // 
                    tableInfos.add(new TableInfo(modelName, tableName, tableType, tableMei, primaryKeys,
                            columnInfos, viewInfos));
                }

            } catch (SQLException e) {
                throw new SystemError(e);
            } finally {
                DbUtils.closeQuietly(rs);
            }
        } catch (Exception e) {
            throw new SystemError(e);
        } finally {
            Connections.close();
        }

        for (String columnInfoName : NOT_EXIST_COLUMN_INFO_NAMES) {
            LOG.trace("Column MetaData [" + columnInfoName + "] is not exists.");
        }

        return tableInfos;
    }

    /**
     * ?
     *
     * @param cn
     *            ?
     * @param tableName
     *            ??
     * @return 
     */
    private static String getTableComment(final Connection cn, final String tableName) {

        String s = null;

        // MySQL????
        if (s == null) {
            s = getTableCommentByShowTableStatus(cn, tableName);
        }

        // Oracle????
        if (s == null) {
            s = getTableCommentByUserTabComments(cn, tableName);
        }

        return s;
    }

    /**
     * ??mysql??
     *
     * @param cn
     *            ?
     * @param tableName
     *            ??
     * @return 
     */
    private static String getTableCommentByShowTableStatus(final Connection cn, final String tableName) {

        ResultSet rs = null;
        try {
            rs = cn.createStatement().executeQuery("show table status where name = '" + tableName + "'");
            if (rs.next()) {
                commentSource = TableCommentSources.showTableStatus;
                return rs.getString("COMMENT");
            }
        } catch (SQLException e) {
        } finally {
            DbUtils.closeQuietly(rs);
        }

        return null;
    }

    /**
     * ??oracle??
     *
     * @param cn
     *            ?
     * @param tableName
     *            ??
     * @return 
     */
    private static String getTableCommentByUserTabComments(final Connection cn, final String tableName) {

        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME = '" + tableName + "'";
            rs = cn.createStatement().executeQuery(sql);
            if (rs.next()) {
                commentSource = TableCommentSources.userTabComments;
                return rs.getString("COMMENTS");
            }
        } catch (SQLException e) {
        } finally {
            DbUtils.closeQuietly(rs);
        }

        return null;
    }

    /**
     * @param cn
     *            ?
     * @param tableName
     *            ??
     * @return ???Set
     */
    private static Set<String> getPrimaryKeys(final Connection cn, final String tableName) {

        List<String> pkList = null;

        ResultSet rs = null;
        try {

            // ??
            DatabaseMetaData dmd = cn.getMetaData();
            rs = dmd.getPrimaryKeys(null, null, tableName);
            while (rs.next()) {

                if (pkList == null) {
                    pkList = new ArrayList<String>();
                }

                String columnName = rs.getString("COLUMN_NAME");

                // rdbms? 1,2,3,
                // sqlite?0,1,2, ??
                int keySeq = rs.getShort("KEY_SEQ");

                while (pkList.size() <= keySeq) {
                    pkList.add(null);
                }
                pkList.set(keySeq, columnName);
            }

        } catch (SQLException e) {
            throw new SystemError(e);
        } finally {
            DbUtils.closeQuietly(rs);
        }

        List<String> primaryKeys = null;

        if (pkList != null) {
            for (String pk : pkList) {
                if (StringUtil.isNotBlank(pk)) {
                    if (primaryKeys == null) {
                        primaryKeys = new ArrayList<String>();
                    }
                    primaryKeys.add(pk);
                }
            }
        }

        if (primaryKeys == null) {
            return null;
        }

        return new LinkedHashSet<String>(primaryKeys);
    }

    /**
     * @param cn
     *            DB?
     * @param tableName
     *            ??
     * @return ????
     */
    private static List<ColumnInfo> getColumnInfos(final Connection cn, final String tableName) {

        List<ColumnInfo> columnInfos = new ArrayList<ColumnInfo>();

        ResultSet rs = null;
        try {

            // ??
            rs = cn.getMetaData().getColumns(null, null, tableName, null);
            while (rs.next()) {

                // ?
                ColumnInfo col = new ColumnInfo();
                col.setTableCat(getString(rs, "TABLE_CAT"));
                col.setTableSchem(getString(rs, "TABLE_SCHEM"));
                col.setTableName(getString(rs, "TABLE_NAME"));
                col.setColumnName(getString(rs, "COLUMN_NAME"));
                col.setDataType(getInt(rs, "DATA_TYPE"));
                col.setTypeName(getString(rs, "TYPE_NAME"));
                col.setColumnSize(getInt(rs, "COLUMN_SIZE"));
                col.setBufferLength(getInt(rs, "BUFFER_LENGTH"));
                col.setDecimalDigits(getInt(rs, "DECIMAL_DIGITS"));
                col.setNumPrecRadix(getInt(rs, "NUM_PREC_RADIX"));
                col.setNullable(getInt(rs, "NULLABLE"));
                col.setRemarks(getString(rs, "REMARKS"));
                col.setColumnDef(getString(rs, "COLUMN_DEF"));
                col.setSqlDataType(getInt(rs, "SQL_DATA_TYPE"));
                col.setSqlDatetimeSub(getInt(rs, "SQL_DATETIME_SUB"));
                col.setCharOctetLength(getInt(rs, "CHAR_OCTET_LENGTH"));
                col.setOrdinalPosition(getInt(rs, "ORDINAL_POSITION"));
                col.setIsNullable(getString(rs, "IS_NULLABLE"));
                col.setScopeCatalog(getString(rs, "SCOPE_CATALOG"));
                col.setScopeSchema(getString(rs, "SCOPE_SCHEMA"));
                col.setScopeTable(getString(rs, "SCOPE_TABLE"));
                col.setSourceDataType(getShort(rs, "SOURCE_DATA_TYPE"));
                col.setIsAutoincrement(getString(rs, "IS_AUTOINCREMENT"));
                col.setIsGeneratedcolumn(getString(rs, "IS_GENERATEDCOLUMN"));

                // ?????
                col.setPropertyName(StringUtil.toCamelCase(col.getColumnName()));

                // REMARKS ???? USER_COL_COMMENTS ??
                if (col.getRemarks() == null) {
                    col.setRemarks(getColumnCommentByUserColComments(cn, tableName, col.getColumnName()));
                }

                // REMARKS????
                if (col.getRemarks() != null) {
                    col.setColumnMei(col.getRemarks().split("\t")[0]);
                }

                columnInfos.add(col);
            }

        } catch (SQLException e) {
            throw new SystemError(e);
        } finally {
            DbUtils.closeQuietly(rs);
        }

        return columnInfos;
    }

    /**
     * @param rs
     *            rs
     * @param columnInfoName
     *            columnLabel
     * @return String
     */
    private static String getString(final ResultSet rs, final String columnInfoName) {
        try {
            LOG.trace(columnInfoName + ":" + rs.getString(columnInfoName));
            return rs.getString(columnInfoName);
        } catch (SQLException e) {
            NOT_EXIST_COLUMN_INFO_NAMES.add(columnInfoName);
        }
        return null;
    }

    /**
     * @param rs
     *            rs
     * @param columnInfoName
     *            columnLabel
     * @return String
     */
    private static Integer getInt(final ResultSet rs, final String columnInfoName) {
        try {
            LOG.trace(columnInfoName + ":" + rs.getString(columnInfoName));
            return rs.getInt(columnInfoName);
        } catch (SQLException e) {
            NOT_EXIST_COLUMN_INFO_NAMES.add(columnInfoName);
        }
        return null;
    }

    /**
     * @param rs
     *            rs
     * @param columnInfoName
     *            columnLabel
     * @return String
     */
    private static Short getShort(final ResultSet rs, final String columnInfoName) {
        try {
            LOG.trace(columnInfoName + ":" + rs.getString(columnInfoName));
            return rs.getShort(columnInfoName);
        } catch (SQLException e) {
            NOT_EXIST_COLUMN_INFO_NAMES.add(columnInfoName);
        }
        return null;
    }

    /**
     * ??Oracle??
     *
     * @param cn
     *            ?
     * @param tableName
     *            ??
     * @param columnName
     *            ??
     * @return 
     */
    private static String getColumnCommentByUserColComments(final Connection cn, final String tableName,
            final String columnName) {

        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = '" + tableName
                    + "' AND COLUMN_NAME = '" + columnName + "'";
            rs = cn.createStatement().executeQuery(sql);
            if (rs.next()) {
                return rs.getString("COMMENTS");
            }
        } catch (SQLException e) {
        } finally {
            DbUtils.closeQuietly(rs);
        }

        return null;
    }

    /***/
    private static final String SENTENCE_SELECT = "\\bSELECT\\b";

    /***/
    private static final int SENTENCE_SELECT_LENGTH = 6;

    /***/
    private static final String SENTENCE_FROM = "\\bFROM\\b";

    /***/
    private static final int SENTENCE_FROM_LENGTH = 4;

    /***/
    private static final String SENTENCE_WHERE = "\\bWHERE\\b";

    /***/
    private static final char COMMA = ',';

    /***/
    private static final char LEFT = '(';

    /***/
    private static final char RIGHT = ')';

    /***/
    private static final int COLUMN_DEF_EXPR_SIZE = 3;

    /**
     * @param cn
     *            cn
     * @param tableName
     *            tableName
     * @return view
     */
    public static Map<String, ViewInfo> getViewInfos(final Connection cn, final String tableName) {

        String ddl = null;

        // mysql??
        if (ddl == null) {
            ddl = retrieveCreateViewMySQL(cn, tableName);
        }

        // sqlite??
        if (ddl == null) {
            ddl = retrieveCreateViewSqlite(cn, tableName);
        }

        // view?ddl????????
        if (ddl == null) {
            return null;
        }

        // select??
        int boFrom = StringUtil.indexOfIgnoreCase(ddl, SENTENCE_FROM);
        String select = ddl.substring(SENTENCE_SELECT_LENGTH, boFrom);

        // from??
        int boWhere = StringUtil.indexOfIgnoreCase(ddl, SENTENCE_WHERE);
        if (boWhere < 0) {
            boWhere = ddl.length();
        }
        String from = ddl.substring(boFrom + SENTENCE_FROM_LENGTH, boWhere);

        Map<String, ViewInfo> viewInfos = null;

        int brackets = 0;

        // select?,??split??
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < select.length(); i++) {
            char c = select.charAt(i);
            if (c == LEFT) {
                ++brackets;
            } else if (c == RIGHT) {
                --brackets;
            }
            if (c == COMMA && brackets > 0) {
                sb.append("");
            } else {
                sb.append(c);
            }
        }
        String[] columns = sb.toString().split(",");
        for (String columnDef : columns) {

            LOG.trace("columnDef: " + columnDef);

            ViewInfo viewInfo = new ViewInfo();

            // column?.??AS??
            columnDef = StringUtil.trim(columnDef).replaceAll("'|`|\"", "");
            String[] columnDefs = columnDef.split("(?i)(\\.|\\bAS\\b)");
            for (int i = 0; i < columnDefs.length; i++) {
                columnDefs[i] = columnDefs[i].trim();
            }

            List<String> columnDefParts = new ArrayList<String>();
            for (int i = 0; i < columnDefs.length; i++) {
                if (StringUtil.isNotBlank(columnDefs[i])) {
                    columnDefParts.add(columnDefs[i]);
                }
            }

            /*
             * columnName
             */

            String columnName = columnDefParts.get(columnDefParts.size() - 1);
            viewInfo.setColumnName(columnName);

            /*
             * orgColumnName
             */

            String orgColumnName = columnDefs[1];
            if (columnDefParts.size() > COLUMN_DEF_EXPR_SIZE) {
                orgColumnName = "expr";
            }
            viewInfo.setOrgColumnName(orgColumnName);

            /*
             * tableName
             */

            String asTableName = columnDefs[0];
            if (columnDefParts.size() > COLUMN_DEF_EXPR_SIZE) {
                asTableName = "expr";
            }
            viewInfo.setTableName(asTableName);

            /*
             * orgTableName
             */

            int l = from.indexOf(viewInfo.getTableName());

            int m = from.lastIndexOf("`", l - 2);
            if (m >= 0) {
                int n = from.lastIndexOf("`", m - 1);
                viewInfo.setOrgTableName(from.substring(n + 1, m));
            } else {
                viewInfo.setOrgTableName(asTableName);
            }

            /*
             * viewInfos?
             */

            if (viewInfos == null) {
                viewInfos = new HashMap<String, ViewInfo>();
            }
            String propertyName = StringUtil.toCamelCase(viewInfo.getColumnName());
            viewInfos.put(propertyName, viewInfo);

            LOG.trace("`" + tableName + "`.`" + propertyName + "` = `" + viewInfo.getOrgTableName() + "`.`"
                    + viewInfo.getOrgColumnName() + "` AS `" + viewInfo.getTableName() + "`.`"
                    + viewInfo.getColumnName() + "`");
        }

        return viewInfos;
    }

    /**
     * @param cn
     *            cn
     * @param tableName
     *            tableName
     * @return create view ddl
     */
    private static String retrieveCreateViewSqlite(final Connection cn, final String tableName) {

        String ddl = null;

        ResultSet rs = null;
        try {
            String sql = "select * from sqlite_master where type = 'view' and name = '" + tableName + "';";
            rs = cn.createStatement().executeQuery(sql);
            if (rs.next()) {
                String createView = rs.getString("sql");
                int i = StringUtil.indexOfIgnoreCase(createView, SENTENCE_SELECT);
                ddl = createView.substring(i);
            }
        } catch (SQLException e) {
        } finally {
            DbUtils.closeQuietly(rs);
        }

        return ddl;
    }

    /**
     * @param cn
     *            cn
     * @param tableName
     *            tableName
     * @return create view ddl
     */
    private static String retrieveCreateViewMySQL(final Connection cn, final String tableName) {

        String ddl = null;

        ResultSet rs = null;
        try {
            String sql = "show create view " + tableName;
            rs = cn.createStatement().executeQuery(sql);
            if (rs.next()) {
                String createView = rs.getString("create View");
                int i = StringUtil.indexOfIgnoreCase(createView, SENTENCE_SELECT);
                ddl = createView.substring(i);
            }
        } catch (SQLException e) {
        } finally {
            DbUtils.closeQuietly(rs);
        }

        return ddl;
    }

}