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

Example 1 with SqlSource

use of com.varsql.core.sql.builder.SqlSource 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 SqlSource

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

the class SqlUpdateExecutor method execute.

@Override
public SQLExecuteResult execute(SqlStatementInfo statementInfo, AbstractSQLExecutorHandler resultHandler) throws SQLException {
    SQLExecuteResult result = new SQLExecuteResult();
    Map sqlParamMap = VartechUtils.jsonStringToObject(statementInfo.getSqlParam(), HashMap.class);
    ResponseResult parseInfo = SqlSourceBuilder.parseResponseResult(statementInfo.getSql(), sqlParamMap, DBType.getDBType(statementInfo.getDbType()));
    List<SqlSource> sqlList = parseInfo.getItems();
    result.setStartTime(System.currentTimeMillis());
    SqlSource tmpSqlSource = null;
    int sqlIdx = 0, sqlSize = sqlList.size();
    int addCount = 0;
    Connection conn = null;
    Statement statement = null;
    try {
        conn = ConnectionFactory.getInstance().getConnection(statementInfo.getVconnid());
        conn.setAutoCommit(false);
        statement = conn.createStatement();
        boolean handleFlag = (resultHandler != null);
        for (sqlIdx = 0; sqlIdx < sqlSize; sqlIdx++) {
            tmpSqlSource = sqlList.get(sqlIdx);
            if (handleFlag) {
                resultHandler.addTotalCount();
                if (!resultHandler.handle(SQLHandlerParameter.builder().sql(tmpSqlSource.getQuery()).parameter(sqlParamMap).build())) {
                    continue;
                }
            }
            ;
            statement.addBatch(tmpSqlSource.getQuery());
            addCount++;
            if (addCount % getBatchCount() == 0) {
                statement.executeBatch();
                statement.clearBatch();
            }
        }
        if (addCount % getBatchCount() != 0) {
            statement.executeBatch();
            statement.clearBatch();
        }
        conn.commit();
    } catch (Throwable e) {
        if (conn != null)
            conn.rollback();
        result.setResultCode(VarsqlAppCode.EC_SQL_EXECUTOR);
        result.setMessage("errorLine : " + sqlIdx + ", error message :  " + e.getMessage());
        logger.error("update : {} ", e.getMessage(), e);
    } finally {
        if (conn != null) {
            conn.setAutoCommit(true);
            JdbcUtils.close(conn, statement);
        }
    }
    result.setTotalCount(resultHandler.getTotalCount());
    result.setEndTime(System.currentTimeMillis());
    result.setExecuteCount(addCount);
    result.setResult(addCount);
    return result;
}

Example 3 with SqlSource

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

the class SqlStatement method getSqlSourceBean.

private static SqlSource getSqlSourceBean(SQLStatement statement, String tmpQuery, Map<String, String> param, DBType dbType) {
    SqlSource sqlSource = new SqlSource();
    sqlSource.setOrginSqlParam(param);
    if (statement instanceof SQLSelectStatement) {
        sqlSource.setCommandType("SELECT");
    } else if (statement instanceof SQLInsertStatement) {
        sqlSource.setCommandType("INSERT");
    } else if (statement instanceof SQLUpdateStatement) {
        sqlSource.setCommandType("UPDATE");
    } else if (statement instanceof SQLDeleteStatement) {
        sqlSource.setCommandType("DELETE");
    } else if (statement instanceof SQLAlterStatement) {
        sqlSource.setCommandType("ALTER");
    } else if (statement instanceof SQLDropStatement) {
        sqlSource.setCommandType("DROP");
    } else if (statement instanceof SQLTruncateStatement) {
        sqlSource.setCommandType("TRUNCATE");
    } else {
        String simpleName = statement.getClass().getSimpleName();
        sqlSource.setCommandType(simpleName.replaceAll(removeClassNameRegular, ""));
    }
    if (statement.isAfterSemi()) {
        tmpQuery = tmpQuery.replaceAll(";$", "");
    }
    if (param != null) {
        SqlStatement sqlstate = getSqlStatement(tmpQuery, param, false, dbType);
        sqlSource.setQuery(sqlstate.sql);
        sqlSource.setParamList(sqlstate.parameter);
    } else {
        sqlSource.setQuery(tmpQuery);
    }
    return sqlSource;
}

Example 4 with SqlSource

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

the class SqlStatement method getSqlSourceList.

public static List<SqlSource> getSqlSourceList(String sql, Map<String, String> param, DBType dbType) {
    List<SqlSource> queries = new LinkedList<SqlSource>();
    com.alibaba.druid.DbType parser = getDbParser(dbType);
    List<SQLStatement> statements = SQLUtils.toStatementList(sql, parser);
    if (statements.size() == 1) {
        queries.add(getSqlSourceBean(statements.get(0), sql, param, dbType));
    } else {
        String tmpQuery = "";
        for (SQLStatement statement : statements) {
            tmpQuery = SQLUtils.toSQLString(statement, parser, null);
            queries.add(getSqlSourceBean(statement, tmpQuery, param, dbType));
        }
    }
    return queries;
}

Example 5 with SqlSource

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

the class SqlStatement method getDefaultSqlSource.

public static List<SqlSource> getDefaultSqlSource(String query, Map<String, String> param, DBType dbType) {
    List<SqlSource> queries = new LinkedList<SqlSource>();
    SqlSource sqlSource = new SqlSource();
    sqlSource.setCommandType("OTHER");
    sqlSource.setOrginSqlParam(param);
    if (param != null) {
        SqlStatement sqlstate = getSqlStatement(query, param, false, dbType);
        sqlSource.setQuery(sqlstate.sql);
        sqlSource.setParamList(sqlstate.parameter);
    } else {
        sqlSource.setQuery(query);
    }
    queries.add(sqlSource);
    return queries;
}