Examples with SqlStatisticsEntity com.varsql.web.model.entity.sql.SqlStatisticsEntity used on opensource projects

Example 1 with SqlStatisticsEntity

use of com.varsql.web.model.entity.sql.SqlStatisticsEntity 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;
}