com.hangum.tadpole.engine.sql.util.resultset.ResultSetUtils.java Source code

Java tutorial

Introduction

Here is the source code for com.hangum.tadpole.engine.sql.util.resultset.ResultSetUtils.java

Source

/*******************************************************************************
 * Copyright (c) 2013 hangum.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the GNU Lesser Public License v2.1
 * which accompanies this distribution, and is available at
 * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
 * 
 * Contributors:
 *     hangum - initial API and implementation
 ******************************************************************************/
package com.hangum.tadpole.engine.sql.util.resultset;

import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.hangum.tadpole.commons.libs.core.define.PublicTadpoleDefine;
import com.hangum.tadpole.engine.define.DBDefine;
import com.hangum.tadpole.engine.query.dao.system.UserDBDAO;
import com.hangum.tadpole.engine.query.dao.system.accesscontrol.AccessCtlObjectDAO;
import com.hangum.tadpole.engine.query.dao.system.accesscontrol.DBAccessControlDAO;

/**
 * ResultSet utils
 * 
 * @author hangum
 * 
 */
public class ResultSetUtils {
    private static final Logger logger = Logger.getLogger(ResultSetUtils.class);

    /**
     * ResultSet to List
     * 
     * @param rs
     * @param limitCount
     * @return
     * @throws SQLException
     */
    public static TadpoleResultSet getResultToList(final ResultSet rs, final int limitCount) throws SQLException {
        return getResultToList(false, rs, limitCount, 0);
    }

    /**
     * ResultSet to List
     * 
     * 
     * 
     * @param isShowRowNum  ?    ?.
     * @param rs ResultSet
     * @param limitCount 
     * @param intLastIndex
     * @return
     * @throws SQLException
     */
    public static TadpoleResultSet getResultToList(boolean isShowRowNum, final ResultSet rs, final int limitCount,
            int intLastIndex) throws SQLException {
        TadpoleResultSet returnRS = new TadpoleResultSet();
        Map<Integer, Object> tmpRow = null;

        //  ??    ? .
        int rowCnt = intLastIndex;
        while (rs.next()) {
            tmpRow = new HashMap<Integer, Object>();

            int intStartIndex = 0;
            if (isShowRowNum) {
                intStartIndex++;
                tmpRow.put(0, rowCnt + 1);
            }

            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                final int intColIndex = i + 1;
                final int intShowColIndex = i + intStartIndex;
                Object obj = null;
                try {
                    obj = rs.getObject(intColIndex);
                    //               int type = rs.getMetaData().getColumnType(intColIndex);
                    //               if(obj instanceof oracle.sql.STRUCT) {
                    //                  tmpRow.put(intShowColIndex, rs.getObject(intColIndex));
                    //               } else
                    if (obj == null) {
                        tmpRow.put(intShowColIndex, PublicTadpoleDefine.DEFINE_NULL_VALUE);
                    } else {
                        String type = obj.getClass().getSimpleName();
                        if (type.toUpperCase().contains("LOB")) {
                            tmpRow.put(intShowColIndex, rs.getObject(intColIndex));
                        } else {
                            tmpRow.put(intShowColIndex, rs.getString(intColIndex));
                        }
                        //                  if(logger.isDebugEnabled()) {
                        //                     logger.debug("======[jdbc type ]===> " + rs.getMetaData().getColumnType(intColIndex) + ", class type is " + obj.getClass().getName());
                        //                  }
                    }
                } catch (Exception e) {
                    logger.error("ResutSet fetch error", e); //$NON-NLS-1$
                    tmpRow.put(i + intStartIndex, ""); //$NON-NLS-1$
                }
            }

            returnRS.getData().add(tmpRow);

            //    ? ? . (hive driver getRow ? ) --;; 2013.08.19, hangum
            if (limitCount == (rowCnt + 1)) {
                returnRS.setEndOfRead(false);
                break;
            }

            rowCnt++;
        }

        return returnRS;
    }

    /**
     * get column type
     * 
     * @param rsm
     * @return
     * @throws SQLException
     */
    public static Map<Integer, Integer> getColumnType(ResultSetMetaData rsm) throws SQLException {
        return getColumnType(false, rsm);
    }

    /**
     * column of type
     * 
     * @param isShowRowNum    ?  ?? .
     * @param rsm
     * @return
     * @throws SQLException
     */
    public static Map<Integer, Integer> getColumnType(boolean isShowRowNum, ResultSetMetaData rsm)
            throws SQLException {
        Map<Integer, Integer> mapColumnType = new HashMap<Integer, Integer>();
        int intStartIndex = 0;

        if (isShowRowNum) {
            intStartIndex++;
            mapColumnType.put(0, java.sql.Types.INTEGER);
        }

        for (int i = 0; i < rsm.getColumnCount(); i++) {
            //         logger.debug("\t ==[column start]================================ ColumnName  :  "    + rsm.getColumnName(i+1));
            //         logger.debug("\tColumnLabel        :  "    + rsm.getColumnLabel(i+1));

            //         logger.debug("\t AutoIncrement     :  "    + rsm.isAutoIncrement(i+1));
            //         logger.debug("\t Nullable           :  "    + rsm.isNullable(i+1));
            //         logger.debug("\t CaseSensitive     :  "    + rsm.isCaseSensitive(i+1));
            //         logger.debug("\t Currency           :  "    + rsm.isCurrency(i+1));
            //         
            //         logger.debug("\t DefinitelyWritable :  "    + rsm.isDefinitelyWritable(i+1));
            //         logger.debug("\t ReadOnly           :  "    + rsm.isReadOnly(i+1));
            //         logger.debug("\t Searchable           :  "    + rsm.isSearchable(i+1));
            //         logger.debug("\t Signed              :  "    + rsm.isSigned(i+1));
            ////         logger.debug("\t Currency           :  "    + rsm.isWrapperFor(i+1));
            //         logger.debug("\t Writable           :  "    + rsm.isWritable(i+1));
            //         
            //         logger.debug("\t ColumnClassName     :  "    + rsm.getColumnClassName(i+1));
            //         logger.debug("\t CatalogName        :  "    + rsm.getCatalogName(i+1));
            //         logger.debug("\t ColumnDisplaySize  :  "    + rsm.getColumnDisplaySize(i+1));
            //         logger.debug("\t ColumnType        :  "    + rsm.getColumnType(i+1));
            //         logger.debug("\t ColumnTypeName    :  "    + rsm.getColumnTypeName(i+1));
            //
            // mysql json ? ?  1  ? , ?? pgsql? json ? ? 1111 .
            //                     - 2015.10.21 mysql 5.7
            if (StringUtils.equalsIgnoreCase("json", rsm.getColumnTypeName(i + 1))) {
                mapColumnType.put(i + intStartIndex, 1111);
            } else {
                mapColumnType.put(i + intStartIndex, rsm.getColumnType(i + 1));
            }
            //         logger.debug("\t Column Label " + rsm.getColumnLabel(i+1) );

            //         logger.debug("\t Precision          :  "    + rsm.getPrecision(i+1));
            //         logger.debug("\t Scale             :  "    + rsm.getScale(i+1));
            //         logger.debug("\t SchemaName          :  "    + rsm.getSchemaName(i+1));
            //         logger.debug("\t TableName          :  "    + rsm.getTableName(i+1));
            //         logger.debug("\t ==[column end]================================ ColumnName  :  "    + rsm.getColumnName(i+1));
        }

        return mapColumnType;
    }

    /**
     * column name of table.
     * but this method is db access control.
     * 
     * @param userDB
     * @param columnTableName
     * @param isShowRownum
     * @param rs
     * @return
     */
    public static Map<Integer, String> getColumnName(UserDBDAO userDB, Map<Integer, String> columnTableName,
            boolean isShowRowNum, ResultSet rs) throws Exception {
        Map<Integer, String> mapColumnName = getColumnName(isShowRowNum, rs);
        DBAccessControlDAO dbAccessCtlDao = userDB.getDbAccessCtl();
        Map<String, AccessCtlObjectDAO> mapDetailCtl = dbAccessCtlDao.getMapSelectAccessCtl();

        if (!mapDetailCtl.isEmpty()) {
            Map<Integer, String> mapReturnColumnName = new HashMap<Integer, String>();
            int intColumnCnt = 0;

            //  ? db access   ??   ?.
            for (int i = 0; i < mapColumnName.size(); i++) {
                String strTableName = columnTableName.get(i);

                // Is filter column?
                if (mapDetailCtl.containsKey(strTableName)) {
                    // is filter table?
                    AccessCtlObjectDAO acDao = mapDetailCtl.get(strTableName);

                    String strTableOfAccessColumns = acDao.getDetail_obj();
                    String strResultColumn = mapColumnName.get(i);
                    if (StringUtils.containsIgnoreCase(strTableOfAccessColumns, strResultColumn)
                            | acDao.getDontuse_object().equals("YES")) {
                        //                  if(logger.isDebugEnabled()) logger.debug("This colum is remove stauts " + strResultColumn);
                    } else {
                        //                  if(logger.isDebugEnabled()) logger.debug("This colum is normal stauts " + strResultColumn);
                        mapReturnColumnName.put(intColumnCnt, mapColumnName.get(i));
                        intColumnCnt++;
                    }
                } else {
                    mapReturnColumnName.put(intColumnCnt, mapColumnName.get(i));
                    intColumnCnt++;
                }
            }

            return mapReturnColumnName;
        } else {
            return mapColumnName;
        }
    }

    /**
     * column name of table
     * 
     * @param isShowRowNum
     * @param rs
     * @return
     * @throws Exception
     */
    public static Map<Integer, String> getColumnName(boolean isShowRowNum, ResultSet rs) throws Exception {
        Map<Integer, String> mapColumnName = new HashMap<Integer, String>();
        int intStartIndex = 0;

        if (isShowRowNum) {
            intStartIndex++;
            mapColumnName.put(0, "#");
        }

        ResultSetMetaData rsm = rs.getMetaData();
        for (int i = 0; i < rsm.getColumnCount(); i++) {
            mapColumnName.put(i + intStartIndex, rsm.getColumnName(i + 1));
        }

        return mapColumnName;
    }

    public static Map<Integer, String> getColumnLabelName(UserDBDAO userDB, Map<Integer, String> columnTableName,
            boolean isShowRowNum, ResultSet rs) throws Exception {
        Map<Integer, String> mapColumnName = getColumnLabelName(isShowRowNum, rs);
        DBAccessControlDAO dbAccessCtlDao = userDB.getDbAccessCtl();
        Map<String, AccessCtlObjectDAO> mapDetailCtl = dbAccessCtlDao.getMapSelectAccessCtl();

        if (!mapDetailCtl.isEmpty()) {
            Map<Integer, String> mapReturnColumnName = new HashMap<Integer, String>();
            int intColumnCnt = 0;

            //  ? db access   ??   ?.
            for (int i = 0; i < mapColumnName.size(); i++) {
                String strTableName = columnTableName.get(i);

                // Is filter column?
                if (mapDetailCtl.containsKey(strTableName)) {
                    // is filter table?
                    AccessCtlObjectDAO acDao = mapDetailCtl.get(strTableName);

                    String strTableOfAccessColumns = acDao.getDetail_obj();
                    String strResultColumn = mapColumnName.get(i);
                    if (StringUtils.containsIgnoreCase(strTableOfAccessColumns, strResultColumn)
                            | acDao.getDontuse_object().equals("YES")) {
                        //            if(logger.isDebugEnabled()) logger.debug("This colum is remove stauts " + strResultColumn);
                    } else {
                        //            if(logger.isDebugEnabled()) logger.debug("This colum is normal stauts " + strResultColumn);
                        mapReturnColumnName.put(intColumnCnt, mapColumnName.get(i));
                        intColumnCnt++;
                    }
                } else {
                    mapReturnColumnName.put(intColumnCnt, mapColumnName.get(i));
                    intColumnCnt++;
                }
            }

            return mapReturnColumnName;
        } else {
            return mapColumnName;
        }
    }

    /**
     * column label name of table
     * 
     * @param isShowRowNum
     * @param rs
     * @return
     * @throws Exception
     */
    public static Map<Integer, String> getColumnLabelName(boolean isShowRowNum, ResultSet rs) throws Exception {
        Map<Integer, String> mapColumnName = new HashMap<Integer, String>();
        int intStartIndex = 0;

        if (isShowRowNum) {
            intStartIndex++;
            mapColumnName.put(0, "#");
        }

        ResultSetMetaData rsm = rs.getMetaData();
        for (int i = 0; i < rsm.getColumnCount(); i++) {
            mapColumnName.put(i + intStartIndex, rsm.getColumnLabel(i + 1));
        }

        return mapColumnName;
    }

    public static Map<Integer, String> getColumnTableName(final UserDBDAO userDB, ResultSet rs) throws Exception {
        return getColumnTableName(userDB, false, rs);
    }

    /**
     * ? table name
     * 
     * @param isShowRowNum
     * @param rs
     * @return
     * @throws Exception
     */
    public static Map<Integer, String> getColumnTableName(final UserDBDAO userDB, boolean isShowRowNum,
            ResultSet rs) throws Exception {
        Map<Integer, String> mapColumnName = new HashMap<Integer, String>();
        int intStartIndex = 0;

        if (isShowRowNum) {
            intStartIndex++;
            mapColumnName.put(0, "#");
        }

        ResultSetMetaData rsm = rs.getMetaData();
        for (int i = 0; i < rsm.getColumnCount(); i++) {
            //         if(userDB.getDBDefine() == DBDefine.POSTGRE_DEFAULT) {
            //            PGResultSetMetaData pgsqlMeta = (PGResultSetMetaData)rsm;
            //            mapColumnName.put(i+intStartIndex, pgsqlMeta.getBaseTableName(i+1));
            //            
            ////            if(logger.isDebugEnabled()) logger.debug("Table name is " + pgsqlMeta.getBaseTableName(i+1));
            //         } else
            if (userDB.getDBDefine() == DBDefine.HIVE_DEFAULT || userDB.getDBDefine() == DBDefine.HIVE2_DEFAULT) {
                mapColumnName.put(i + intStartIndex, "Apache Hive is not support this method.");
            } else {
                if (rsm.getSchemaName(i + 1) == null || "".equals(rsm.getSchemaName(i + 1))) {
                    //               if(logger.isDebugEnabled()) logger.debug("Table name is " + rsm.getTableName(i+1) + ", schema name is " + rsm.getSchemaName(i+1));

                    mapColumnName.put(i + intStartIndex, rsm.getTableName(i + 1));
                } else {
                    mapColumnName.put(i + intStartIndex, rsm.getSchemaName(i + 1) + "." + rsm.getTableName(i + 1));
                }
            }
        }

        return mapColumnName;
    }

    /**
     * metadata     .
     * 
     * @param rs
     * @return index? 
     */
    public static Map<Integer, String> getColumnName(ResultSet rs) throws Exception {
        return getColumnName(false, rs);
    }

    /**
     * ?  ?    .
     *  pgsql  ?.
     * 
     * mysql, maria, oracle?  ? alias ?  ??  .
     *    ?.
     * 2014-11-13 
     * 
     * @param rsm
     * @return
     * @throws SQLException
     */
    public static Map<Integer, Map> getColumnTableColumnName(UserDBDAO userDB, ResultSetMetaData rsm) {
        Map<Integer, Map> mapTableColumn = new HashMap<Integer, Map>();

        //   ?  .
        mapTableColumn.put(0, new HashMap());

        //      try {
        //         if(userDB.getDBDefine() == DBDefine.POSTGRE_DEFAULT) {
        //            PGResultSetMetaData pgsqlMeta = (PGResultSetMetaData)rsm;
        //            for(int i=0;i<rsm.getColumnCount(); i++) {
        //               int columnSeq = i+1;
        //               Map<String, String> metaData = new HashMap<String, String>();
        //               metaData.put("schema", pgsqlMeta.getBaseSchemaName(columnSeq));
        //               metaData.put("table", pgsqlMeta.getBaseTableName(columnSeq));
        //               metaData.put("column", pgsqlMeta.getBaseColumnName(columnSeq));
        //               metaData.put("type",    ""+rsm.getColumnType(columnSeq));
        //               metaData.put("typeName",    ""+rsm.getColumnTypeName(columnSeq));
        //               
        ////               if(logger.isDebugEnabled()) {
        ////                  logger.debug("\tschema :" + pgsqlMeta.getBaseSchemaName(columnSeq) + "\ttable:" + pgsqlMeta.getBaseTableName(columnSeq) + "\tcolumn:" + pgsqlMeta.getBaseColumnName(columnSeq));
        ////               }
        //               
        //               mapTableColumn.put(i+1, metaData);
        //            }
        //            
        ////         /**
        ////          * table name alia
        ////          * 
        ////          */
        ////         } else if(userDB.getDBDefine() == DBDefine.MYSQL_DEFAULT ||
        ////                     userDB.getDBDefine() == DBDefine.MARIADB_DEFAULT
        ////         ) {
        //////            com.mysql.jdbc.ResultSetMetaData mysqlMeta = (com.mysql.jdbc.ResultSetMetaData)rsm;
        ////            org.mariadb.jdbc.MySQLResultSetMetaData mysqlMeta = (org.mariadb.jdbc.MySQLResultSetMetaData)rsm;
        ////            for(int i=0;i<rsm.getColumnCount(); i++) {
        ////               int columnSeq = i+1;
        ////               Map<String, String> metaData = new HashMap<String, String>();
        ////               if(logger.isDebugEnabled()) {
        ////                  logger.debug("\tschema :" + mysqlMeta.getCatalogName(columnSeq) + "\ttable:" + mysqlMeta.getTableName(columnSeq) + "\tcolumn:" + mysqlMeta.getColumnName(columnSeq));
        ////               }
        ////               
        ////               metaData.put("schema", mysqlMeta.getCatalogName(columnSeq));
        ////               metaData.put("table", mysqlMeta.getTableName(columnSeq));
        ////               metaData.put("column", mysqlMeta.getColumnName(columnSeq));
        ////               
        ////               mapTableColumn.put(i+1, metaData);
        ////            }
        //            
        //         } else if(userDB.getDBDefine() == DBDefine.MSSQL_8_LE_DEFAULT 
        //                  || userDB.getDBDefine() == DBDefine.MSSQL_DEFAULT
        //                  || userDB.getDBDefine() == DBDefine.ORACLE_DEFAULT                     
        //            ) {
        //            for(int i=0;i<rsm.getColumnCount(); i++) {
        //               int columnSeq = i+1;
        //               Map<String, String> metaData = new HashMap<String, String>();
        //               metaData.put("schema",    rsm.getSchemaName(columnSeq));
        //               metaData.put("table",    rsm.getTableName(columnSeq));
        //               metaData.put("column",    rsm.getColumnName(columnSeq));
        //               metaData.put("type",    ""+rsm.getColumnType(columnSeq));
        //               metaData.put("typeName",    ""+rsm.getColumnTypeName(columnSeq));
        //               
        ////               if(logger.isDebugEnabled()) {
        ////                  logger.debug("\tschema :" + rsm.getSchemaName(columnSeq) + "\ttable:" + rsm.getTableName(columnSeq) + "\tcolumn:" + rsm.getColumnName(columnSeq)
        ////                   + "\ttype : " + rsm.getColumnType(columnSeq) + "\ttypename : " + rsm.getColumnTypeName(columnSeq))
        ////                  ;
        ////               }
        //               
        //               mapTableColumn.put(i+1, metaData);
        //            }
        //         }
        //      } catch(Exception e) {
        //         logger.error("resultset metadata exception", e);
        //      }

        return mapTableColumn;
    }

}