Examples with GridColumnInfo com.varsql.core.sql.beans.GridColumnInfo used on opensource projects

Example 1 with GridColumnInfo

use of com.varsql.core.sql.beans.GridColumnInfo in project varsql by varsqlinfo.

the class SQLServiceImpl method gridDownload.

/**
 * @Method Name  : gridDownload
 * @Method 설명 : 그리드 데이터 다운로드.
 * @작성자   : ytkim
 * @작성일   : 2019. 8. 9.
 * @변경이력  :
 * @param sqlGridDownloadInfo
 * @param req
 * @param res
 * @throws IOException
 */
public void gridDownload(SqlGridDownloadInfo sqlGridDownloadInfo, HttpServletRequest req, HttpServletResponse res) throws IOException {
    VarsqlFileType exportType = sqlGridDownloadInfo.getExportType();
    String exportCharset = VarsqlConstants.CHAR_SET;
    String downloadName = "varsql-select-data";
    AbstractWriter writer = null;
    JsonParser parser = null;
    try (OutputStream outstream = res.getOutputStream()) {
        if (VarsqlFileType.CSV.equals(exportType)) {
            writer = new CSVWriter(outstream, ',', exportCharset);
        } else if (VarsqlFileType.JSON.equals(exportType)) {
            writer = new JSONWriter(outstream, "row", exportCharset);
        } else if (VarsqlFileType.XML.equals(exportType)) {
            writer = new XMLWriter(outstream, "row", exportCharset);
        } else if (VarsqlFileType.EXCEL.equals(exportType)) {
            writer = new ExcelWriter(outstream);
        }
        VarsqlUtils.setResponseDownAttr(res, req, exportType.concatExtension(downloadName));
        List<GridColumnInfo> columnInfos = VartechUtils.jsonStringToObject(sqlGridDownloadInfo.getHeaderInfo(), new TypeReference<LinkedList<GridColumnInfo>>() {
        }, true);
        logger.debug("grid download : {} ", columnInfos);
        Map<String, GridColumnInfo> gridColumnInfoMap = GridUtils.getKeyMap(columnInfos);
        parser = new JsonFactory().createParser(sqlGridDownloadInfo.getGridData());
        // start reading the file
        parser.nextToken();
        Map<String, Object> rowInfo;
        GridColumnInfo columnInfo;
        JsonToken valueToken;
        while (parser.nextToken() != JsonToken.END_ARRAY) {
            // loop until "}"
            rowInfo = new LinkedHashMap<>();
            while (parser.nextToken() != JsonToken.END_OBJECT) {
                columnInfo = GridUtils.getGridInfoForKey(parser.getCurrentName(), gridColumnInfoMap);
                if (columnInfo != null) {
                    parser.nextToken();
                    valueToken = parser.currentToken();
                    if (valueToken == null) {
                        rowInfo.put(columnInfo.getLabel(), null);
                    } else {
                        if (columnInfo.isNumber() && valueToken.isNumeric()) {
                            rowInfo.put(columnInfo.getLabel(), parser.getNumberValue());
                        } else {
                            rowInfo.put(columnInfo.getLabel(), parser.getText());
                        }
                    }
                }
            }
            writer.addRow(rowInfo);
        }
        writer.writeAndClose();
        parser.close();
        if (outstream != null)
            outstream.close();
    } catch (Exception e) {
        logger.error(" param {} ", sqlGridDownloadInfo);
        logger.error(" gridDownload {}", e.getMessage(), e);
    } finally {
        IOUtils.close(writer);
        if (parser != null)
            parser.close();
    }
}

Example 2 with GridColumnInfo

use of com.varsql.core.sql.beans.GridColumnInfo 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);
    }
}

Example 3 with GridColumnInfo

use of com.varsql.core.sql.beans.GridColumnInfo in project varsql by varsqlinfo.

the class SQLWriter method addRow.

@SuppressWarnings({ "rawtypes" })
@Override
public <E> void addRow(Object addRow) throws IOException {
    Map rowValue = (Map) addRow;
    if (firstFlag) {
        StringBuilder columnSb = new StringBuilder();
        if (columnInfos == null) {
            columnInfos = new LinkedList<GridColumnInfo>();
            Object[] keyArr = rowValue.keySet().toArray();
            int keyLen = keyArr.length;
            for (int i = 0; i < keyLen; i++) {
                GridColumnInfo gci = new GridColumnInfo();
                String key = keyArr[i].toString();
                Object colVal = rowValue.get(keyArr[i]);
                gci.setKey(key);
                gci.setLabel(key);
                if (colVal instanceof Number) {
                    gci.setNumber(true);
                } else {
                    gci.setNumber(false);
                }
                columnInfos.add(gci);
            }
        }
        for (int i = 0; i < columnInfos.size(); i++) {
            GridColumnInfo columnInfo = columnInfos.get(i);
            columnSb.append(i == 0 ? "" : ", ").append(columnInfo.getLabel());
        }
        insertQueryPrefix = String.format("insert into %s(%s) values (", tableName, columnSb.toString());
        firstFlag = false;
    }
    writer.write(insertQueryPrefix);
    StringBuilder valueSb = new StringBuilder();
    for (int i = 0; i < columnInfos.size(); i++) {
        GridColumnInfo columnInfo = columnInfos.get(i);
        Object colVal = rowValue.get(columnInfo.getKey());
        valueSb.append(i == 0 ? "" : ", ");
        if (colVal == null) {
            valueSb.append("null");
        } else {
            if (columnInfo.isNumber()) {
                valueSb.append("".equals(colVal) ? null : colVal);
            } else {
                if (columnInfo.isLob()) {
                    valueSb.append("null");
                } else {
                    valueSb.append("'").append(SQLUtils.escapeValue(colVal)).append("'");
                    ;
                }
            }
        }
    }
    writer.write(valueSb.toString());
    writer.write(");");
    writer.newLine();
    addRowIdx();
}

Example 4 with GridColumnInfo

use of com.varsql.core.sql.beans.GridColumnInfo in project varsql by varsqlinfo.

the class SQLResultSetUtils method resultSetHandler.

public static SqlSourceResultVO resultSetHandler(ResultSet rs, SqlSourceResultVO ssrv, SqlStatementInfo sqlExecuteInfo, DatabaseInfo dbInfo, int maxRow, boolean gridKeyAlias) throws SQLException {
    if (rs == null) {
        return ssrv;
    }
    ResultSetMetaData rsmd = rs.getMetaData();
    int count = rsmd.getColumnCount();
    String[] columnNameArr = new String[count];
    String[] columnGridKeyArr = GridUtils.getAliasKeyArr(count);
    List<GridColumnInfo> columnInfoList = new ArrayList<GridColumnInfo>();
    String columnName = "";
    String viewColumnInfo = sqlExecuteInfo.getColumnInfo();
    Set<String> viewColumnCheck = Collections.emptySet();
    boolean columnChkFlag = false;
    if (viewColumnInfo != null && !"".equals(viewColumnInfo)) {
        columnChkFlag = true;
        viewColumnCheck = new HashSet<String>(Arrays.asList(viewColumnInfo.toUpperCase().split(",")));
    }
    Map<String, Integer> columnKeyCheck = new HashMap<String, Integer>();
    int idx = 0;
    int columnType = -1;
    String columnTypeName = "";
    GridColumnInfo columnInfo = null;
    int columnWidth = count > 10 ? 70 : 0;
    boolean useColumnLabel = dbInfo.isUseColumnLabel();
    for (int i = 0; i < count; i++) {
        idx = i + 1;
        columnName = useColumnLabel ? rsmd.getColumnLabel(idx) : rsmd.getColumnName(idx);
        if (columnChkFlag && !viewColumnCheck.contains(columnName.toUpperCase())) {
            continue;
        }
        columnType = rsmd.getColumnType(idx);
        columnTypeName = rsmd.getColumnTypeName(idx);
        if (columnKeyCheck.containsKey(columnName)) {
            int idxVal = columnKeyCheck.get(columnName) + 1;
            columnKeyCheck.put(columnName, idxVal);
            columnName = columnName + "_" + idxVal;
        } else {
            columnKeyCheck.put(columnName, 0);
        }
        columnNameArr[i] = columnName;
        columnInfo = new GridColumnInfo();
        setColumnTypeInfo(columnType, columnInfo);
        columnInfo.setNo(idx);
        columnInfo.setLabel(columnName);
        if (gridKeyAlias) {
            columnInfo.setKey(columnGridKeyArr[i]);
        } else {
            columnInfo.setKey(columnName);
            columnGridKeyArr[i] = columnName;
        }
        columnInfo.setDbType(columnTypeName);
        columnInfo.setWidth(columnWidth);
        columnInfoList.add(columnInfo);
    }
    columnKeyCheck = null;
    int first = 0, last = maxRow;
    ssrv.setColumn(columnInfoList);
    Map row = null;
    ArrayList rows = new ArrayList();
    int totalCnt = 0;
    try {
        ResultSetHandler resultsetHandler = MetaControlFactory.getDbInstanceFactory(DBType.getDBType(sqlExecuteInfo.getDbType())).getResultsetHandler();
        while (rs.next()) {
            row = new LinkedHashMap(count);
            for (int colIdx = 0; colIdx < count; colIdx++) {
                if (columnNameArr[colIdx] != null) {
                    row = resultsetHandler.getDataValue(rs, row, columnInfoList.get(colIdx));
                // row = resultsetHandler.getDataValue(row, columnGridKeyArr[colIdx], columnNameArr[colIdx], rs, colIdx+1, columnTypeArr[colIdx], columnTypeNameArr[colIdx]);
                }
            }
            rows.add(row);
            ++first;
            totalCnt++;
            if (first >= last)
                break;
        }
    } catch (SQLException e) {
        ssrv.setData(rows);
        ssrv.setResultCnt(totalCnt);
        ssrv.setResultMessage(e.getMessage());
        throw new ResultSetConvertException(VarsqlAppCode.EC_SQL_RESULT_CONVERT, e, ssrv);
    }
    ssrv.setData(rows);
    ssrv.setResultCnt(totalCnt);
    return ssrv;
}

Example 5 with GridColumnInfo

use of com.varsql.core.sql.beans.GridColumnInfo in project varsql by varsqlinfo.

the class SQLResultSetUtils method resultSetHandler.

public static void resultSetHandler(ResultSet rs, SqlStatementInfo sqlExecuteInfo, AbstractSQLExecutorHandler baseExecutorHandler, boolean gridKeyAlias) throws SQLException {
    if (rs == null) {
        return;
    }
    ResultSetMetaData rsmd = rs.getMetaData();
    int count = rsmd.getColumnCount();
    String[] columnNameArr = new String[count];
    String[] columnGridKeyArr = GridUtils.getAliasKeyArr(count);
    int columnType = -1;
    String columnTypeName = "";
    GridColumnInfo columnInfo = null;
    List<GridColumnInfo> columnInfoList = new ArrayList<GridColumnInfo>();
    String columnName = "";
    String viewColumnInfo = sqlExecuteInfo.getColumnInfo();
    Set<String> viewColumnCheck = Collections.emptySet();
    boolean columnChkFlag = false;
    if (viewColumnInfo != null && !"".equals(viewColumnInfo)) {
        columnChkFlag = true;
        viewColumnCheck = new HashSet<String>(Arrays.asList(viewColumnInfo.toUpperCase().split(",")));
    }
    Map<String, Integer> columnKeyCheck = new HashMap<String, Integer>();
    int idx = 0;
    int columnWidth = count > 10 ? 70 : 0;
    for (int i = 0; i < count; i++) {
        idx = i + 1;
        columnName = rsmd.getColumnName(idx);
        if (columnChkFlag && !viewColumnCheck.contains(columnName.toUpperCase())) {
            continue;
        }
        columnType = rsmd.getColumnType(idx);
        columnTypeName = rsmd.getColumnTypeName(idx);
        if (columnKeyCheck.containsKey(columnName)) {
            int idxVal = columnKeyCheck.get(columnName) + 1;
            columnKeyCheck.put(columnName, idxVal);
            columnName = columnName + "_" + idxVal;
        } else {
            columnKeyCheck.put(columnName, 0);
        }
        columnNameArr[i] = columnName;
        columnInfo = new GridColumnInfo();
        setColumnTypeInfo(columnType, columnInfo);
        if (gridKeyAlias) {
            columnInfo.setKey(columnGridKeyArr[i]);
        } else {
            columnInfo.setKey(columnName);
            columnGridKeyArr[i] = columnName;
        }
        columnInfo.setNo(idx);
        columnInfo.setLabel(columnName);
        columnInfo.setDbType(columnTypeName);
        columnInfo.setWidth(columnWidth);
        columnInfoList.add(columnInfo);
    }
    columnKeyCheck = null;
    try {
        ResultSetHandler resultsetHandler = MetaControlFactory.getDbInstanceFactory(DBType.getDBType(sqlExecuteInfo.getDbType())).getResultsetHandler();
        Map row = null;
        while (rs.next()) {
            row = new LinkedHashMap(count);
            for (int colIdx = 0; colIdx < count; colIdx++) {
                if (columnNameArr[colIdx] != null) {
                    row = resultsetHandler.getDataValue(rs, row, columnInfoList.get(colIdx));
                }
            }
            boolean addFlag = baseExecutorHandler.handle(SQLHandlerParameter.builder().rowObject(row).columnInfoList(columnInfoList).build());
            if (addFlag) {
                baseExecutorHandler.addTotalCount();
            } else {
                baseExecutorHandler.addFailCount();
            }
        }
    } catch (SQLException e) {
        throw new ResultSetConvertException(VarsqlAppCode.EC_SQL_RESULT_CONVERT, e);
    }
}