Examples with SqlSourceResultVO com.varsql.core.sql.builder.SqlSourceResultVO used on opensource projects

Example 1 with SqlSourceResultVO

use of com.varsql.core.sql.builder.SqlSourceResultVO in project varsql by varsqlinfo.

the class SQLServiceImpl method sqlData.

/**
 * @Method Name  : sqlData
 * @Method 설명 : 쿼리 데이터 보기.
 * @작성자   : ytkim
 * @작성일   : 2015. 4. 9.
 * @변경이력  :
 * @param sqlExecuteInfo
 * @param req
 * @return
 * @throws Exception
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public ResponseResult sqlData(SqlExecuteDTO sqlExecuteInfo, HttpServletRequest req) throws Exception {
    Map sqlParamMap = VartechUtils.jsonStringToObject(sqlExecuteInfo.getSqlParam(), HashMap.class);
    DatabaseInfo dbinfo = SecurityUtil.userDBInfo(sqlExecuteInfo.getConuid());
    ResponseResult parseInfo = SqlSourceBuilder.parseResponseResult(sqlExecuteInfo.getSql(), sqlParamMap, DBType.getDBType(sqlExecuteInfo.getDbType()));
    List<SqlSource> sqlList = parseInfo.getItems();
    int limit = sqlExecuteInfo.getLimit();
    if (!SecurityUtil.isAdmin()) {
        sqlExecuteInfo.setLimit(limit > dbinfo.getMaxSelectCount() ? dbinfo.getMaxSelectCount() : limit);
    }
    ArrayList<SqlSourceResultVO> reLst = new ArrayList<SqlSourceResultVO>();
    ResponseResult result = new ResponseResult();
    Connection conn = null;
    SqlSourceResultVO ssrv = null;
    long stddt = System.currentTimeMillis();
    String[] mmddHH = DateUtils.dateformat("MM-dd-HH", stddt).split("-");
    SqlLogInfoDTO sqlLogInfo = new SqlLogInfoDTO();
    sqlLogInfo.setVconnid(sqlExecuteInfo.getVconnid());
    sqlLogInfo.setViewid(sqlExecuteInfo.getViewid());
    sqlLogInfo.setStartTime(stddt);
    sqlLogInfo.setSMm(Integer.valueOf(mmddHH[0]));
    sqlLogInfo.setSDd(Integer.valueOf(mmddHH[1]));
    sqlLogInfo.setSHh(Integer.valueOf(mmddHH[2]));
    sqlLogInfo.setUsrIp(VarsqlUtils.getClientIp(req));
    SqlSource tmpSqlSource = null;
    int sqldx = 0, sqlSize = sqlList.size();
    String errorMsg = "";
    try {
        conn = ConnectionFactory.getInstance().getConnection(sqlExecuteInfo.getVconnid());
        if (!StringUtils.isBlank(sqlExecuteInfo.get_requid_())) {
            SqlExecuteManager.getInstance().setStatementInfo(sqlExecuteInfo.get_requid_(), null);
        }
        conn.setAutoCommit(false);
        List<SqlStatisticsEntity> allSqlStatistics = new LinkedList<SqlStatisticsEntity>();
        for (sqldx = 0; sqldx < sqlSize; sqldx++) {
            tmpSqlSource = sqlList.get(sqldx);
            ssrv = new SqlSourceResultVO();
            reLst.add(ssrv);
            tmpSqlSource.setResult(ssrv);
            ssrv.setStarttime(System.currentTimeMillis());
            getRequestSqlData(sqlExecuteInfo, conn, tmpSqlSource, dbinfo, true);
            ssrv.setEndtime(System.currentTimeMillis());
            ssrv.setDelay((ssrv.getEndtime() - ssrv.getStarttime()) / 1000);
            ssrv.setResultMessage((ssrv.getDelay()) + " SECOND : " + StringUtils.escape(ssrv.getResultMessage(), EscapeType.html));
            sqlLogInfo.setStartTime(ssrv.getStarttime());
            sqlLogInfo.setCommandType(tmpSqlSource.getCommandType());
            sqlLogInfo.setEndTime(ssrv.getEndtime());
            allSqlStatistics.add(SqlStatisticsEntity.builder().vconnid(sqlLogInfo.getVconnid()).viewid(sqlLogInfo.getViewid()).startTime(ConvertUtils.longToLocalDateTime(sqlLogInfo.getStartTime())).endTime(ConvertUtils.longToLocalDateTime(sqlLogInfo.getEndTime())).delayTime(sqlLogInfo.getDelayTime()).sMm(sqlLogInfo.getSMm()).sDd(sqlLogInfo.getSDd()).sHh(sqlLogInfo.getSHh()).resultCount(sqlLogInfo.getResultCount()).commandType(sqlLogInfo.getCommandType()).build());
            if (SqlDataConstants.VIEWTYPE.GRID.val().equals(ssrv.getViewType())) {
                break;
            }
        }
        commonServiceImpl.sqlLogInsert(allSqlStatistics);
        result.setItemList(reLst);
        conn.commit();
    } catch (Throwable e) {
        if (conn != null && !conn.isClosed())
            conn.rollback();
        errorMsg = e.getMessage();
        if (e instanceof ResultSetConvertException) {
            result.setResultCode(VarsqlAppCode.EC_SQL_RESULT_CONVERT);
            ssrv = ((ResultSetConvertException) e).getSsrv();
            if (ssrv != null) {
                ssrv = new SqlSourceResultVO();
            }
            ssrv.setViewType(SqlDataConstants.VIEWTYPE.GRID.val());
        } else {
            boolean ssrvNullFlag = false;
            if (ssrv == null) {
                ssrvNullFlag = true;
                ssrv = new SqlSourceResultVO();
            }
            ssrv.setEndtime(System.currentTimeMillis());
            String tmpMsg = parseInfo.getMessage();
            tmpMsg = (tmpMsg == null || "".equals(tmpMsg) ? "" : StringUtils.escape(parseInfo.getMessage(), EscapeType.html) + "<br/>");
            if (e instanceof ConnectionFactoryException) {
                if (((ConnectionFactoryException) e).getErrorCode() == VarsqlAppCode.EC_DB_POOL_CLOSE) {
                    result.setResultCode(VarsqlAppCode.EC_DB_POOL_CLOSE);
                } else {
                    result.setResultCode(VarsqlAppCode.EC_DB_POOL);
                }
            } else {
                result.setResultCode(VarsqlAppCode.EC_SQL);
            }
            result.setMessage(tmpMsg + StringUtils.escape(ssrv.getResultMessage(), EscapeType.html));
            if (ssrvNullFlag) {
                result.setMessage(errorMsg);
            }
        }
        result.addCustoms("errorLine", sqldx);
        result.setItemOne(tmpSqlSource == null ? sqlList.get(0) : tmpSqlSource);
        LoggerFactory.getLogger("sqlErrorLog").error("sqlData errorLine : {}", sqldx, e);
    } finally {
        if (conn != null && !conn.isClosed()) {
            conn.setAutoCommit(true);
            JdbcUtils.close(conn);
        }
    }
    if (!StringUtils.isBlank(sqlExecuteInfo.get_requid_())) {
        SqlExecuteManager.getInstance().removeStatementInfo(sqlExecuteInfo.get_requid_());
    }
    long enddt = System.currentTimeMillis();
    commonServiceImpl.saveSqlHistory(SqlHistoryEntity.builder().vconnid(sqlLogInfo.getVconnid()).viewid(sqlLogInfo.getViewid()).startTime(ConvertUtils.longToTimestamp(stddt)).endTime(ConvertUtils.longToTimestamp(enddt)).delayTime((int) ((enddt - stddt) / 1000)).logSql(sqlExecuteInfo.getSql()).usrIp(sqlLogInfo.getUsrIp()).errorLog(errorMsg).build());
    return result;
}

Example 2 with SqlSourceResultVO

use of com.varsql.core.sql.builder.SqlSourceResultVO in project varsql by varsqlinfo.

the class SQLServiceImpl method getRequestSqlData.

/**
 * @param sqlExecuteInfo
 * @Method Name  : getResultData
 * @Method 설명 : 데이터 얻기
 * @작성일   : 2015. 4. 9.
 * @작성자   : ytkim
 * @변경이력  :
 * @param conn
 * @param tmpSqlSource
 * @param dbinfo
 * @param vconnid
 * @param maxRow
 * @return
 * @throws SQLException
 */
protected void getRequestSqlData(SqlExecuteDTO sqlExecuteInfo, Connection conn, SqlSource tmpSqlSource, DatabaseInfo dbInfo, boolean gridKeyAlias) throws SQLException {
    Statement stmt = null;
    ResultSet rs = null;
    SqlSourceResultVO ssrv = tmpSqlSource.getResult();
    int maxRow = sqlExecuteInfo.getLimit();
    String requid = sqlExecuteInfo.get_requid_();
    try {
        boolean hasResults;
        if (VarsqlStatementType.STATEMENT.equals(tmpSqlSource.getStatementType())) {
            stmt = conn.createStatement();
            SqlExecuteManager.getInstance().setStatementInfo(requid, stmt);
            setMaxRow(stmt, maxRow);
            hasResults = stmt.execute(tmpSqlSource.getQuery());
        } else if (VarsqlStatementType.CALLABLE.equals(tmpSqlSource.getStatementType())) {
            CallableStatement callStatement = conn.prepareCall(tmpSqlSource.getQuery());
            SqlExecuteManager.getInstance().setStatementInfo(requid, callStatement);
            SQLParamUtils.setCallableParameter(callStatement, tmpSqlSource);
            setMaxRow(callStatement, maxRow);
            hasResults = callStatement.execute();
            int cursorObjIdx = -1;
            List<GridColumnInfo> columnInfoList = new ArrayList<>();
            boolean isOutResult = false;
            Map resultInfo = new HashMap();
            if (tmpSqlSource.getParamList() != null) {
                int idx = 1;
                for (ParameterMapping param : tmpSqlSource.getParamList()) {
                    if (param.getMode() == ParameterMode.OUT || param.getMode() == ParameterMode.INOUT) {
                        isOutResult = true;
                        SQLDataType dataType = param.getDataType();
                        if (SQLDataType.CURSOR.equals(dataType) || SQLDataType.ORACLE_CURSOR.equals(dataType)) {
                            cursorObjIdx = idx;
                            hasResults = true;
                        } else {
                            String key = param.getProperty();
                            key = StringUtils.isBlank(key) ? idx + "" : key;
                            GridColumnInfo columnInfo = new GridColumnInfo();
                            columnInfo.setKey(key);
                            columnInfo.setLabel(key);
                            columnInfo.setDbType(dataType != null ? dataType.name() : SQLDataType.OTHER.name());
                            columnInfoList.add(columnInfo);
                            resultInfo.put(key, callStatement.getObject(idx));
                        }
                    }
                    idx++;
                }
            }
            if (isOutResult && !hasResults) {
                ArrayList<Map<Object, Object>> rows = new ArrayList();
                rows.add(resultInfo);
                ssrv.setColumn(columnInfoList);
                ssrv.setResultCnt(1);
                ssrv.setViewType(SqlDataConstants.VIEWTYPE.GRID.val());
                ssrv.setData(rows);
                return;
            } else if (hasResults) {
                rs = (ResultSet) callStatement.getObject(cursorObjIdx);
                if (rs != null) {
                    SQLResultSetUtils.resultSetHandler(rs, ssrv, sqlExecuteInfo, dbInfo, maxRow, gridKeyAlias);
                    ssrv.setViewType(SqlDataConstants.VIEWTYPE.GRID.val());
                    ssrv.setResultMessage(String.format("select count : %s ", new Object[] { Long.valueOf(ssrv.getResultCnt()) }));
                } else {
                    ssrv.setViewType(SqlDataConstants.VIEWTYPE.MSG.val());
                    ssrv.setResultMessage("Cursor is null");
                }
                return;
            }
            stmt = callStatement;
        } else {
            PreparedStatement pstmt = conn.prepareStatement(tmpSqlSource.getQuery());
            SqlExecuteManager.getInstance().setStatementInfo(requid, pstmt);
            SQLParamUtils.setSqlParameter(pstmt, tmpSqlSource);
            setMaxRow(pstmt, maxRow);
            hasResults = pstmt.execute();
            stmt = pstmt;
        }
        if (hasResults) {
            rs = stmt.getResultSet();
            SQLResultSetUtils.resultSetHandler(rs, ssrv, sqlExecuteInfo, dbInfo, maxRow, gridKeyAlias);
            ssrv.setViewType(SqlDataConstants.VIEWTYPE.GRID.val());
            ssrv.setResultMessage(String.format("select count : %s ", ssrv.getResultCnt()));
        } else {
            ssrv.setViewType(SqlDataConstants.VIEWTYPE.MSG.val());
            ssrv.setResultCnt(stmt.getUpdateCount());
            if (VarsqlCommandType.isUpdateCountCommand(tmpSqlSource.getCommandType())) {
                ssrv.setResultMessage(String.format("%s count : %s", tmpSqlSource.getCommandType(), ssrv.getResultCnt()));
            } else {
                ssrv.setResultMessage(String.format("%s success", tmpSqlSource.getCommandType()));
            }
        }
        ssrv.setResultType(SqlDataConstants.RESULT_TYPE.SUCCESS.val());
    } catch (SQLException e) {
        ssrv.setViewType(SqlDataConstants.VIEWTYPE.MSG.val());
        ssrv.setResultType(SqlDataConstants.RESULT_TYPE.FAIL.val());
        ssrv.setResultMessage(String.format("error code :%s ;\nsql state : %s ;\nmessage : %s", e.getErrorCode(), e.getSQLState(), e.getMessage()));
        // logger.error(" sqlData : {}", tmpSqlSource.getQuery() ,e);
        throw new SQLException(ssrv.getResultMessage(), e);
    } finally {
        JdbcUtils.close(stmt, rs);
    }
}