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

Example 1 with ExportColumnInfo

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

the class ImportXmlData method getExportInfo.

private static void getExportInfo(XMLStreamReader xmlreader, AbstractImportDataHandler importDataHandler) throws XMLStreamException {
    List<ExportColumnInfo> columns = new ArrayList<ExportColumnInfo>();
    ExportColumnInfo eci = null;
    boolean breakFlag = false;
    while (xmlreader.hasNext()) {
        switch(xmlreader.getEventType()) {
            case XMLStreamReader.START_ELEMENT:
                {
                    String localName = xmlreader.getLocalName();
                    if ("tableName".equals(localName)) {
                        importDataHandler.setTableName(xmlreader.getElementText());
                    }
                    if ("item".equals(localName)) {
                        // column info start
                        eci = new ExportColumnInfo();
                        for (int i = 0; i < xmlreader.getAttributeCount(); i++) {
                            String attrName = xmlreader.getAttributeLocalName(i);
                            String attrVal = xmlreader.getAttributeValue(i);
                            if ("name".equals(attrName)) {
                                eci.setName(attrVal);
                            } else if ("type".equals(attrName)) {
                                eci.setType(attrVal);
                            } else if ("number".equals(attrName)) {
                                eci.setNumber(Boolean.parseBoolean(attrVal));
                            } else if ("lob".equals(attrName)) {
                                eci.setLob(Boolean.parseBoolean(attrVal));
                            } else if ("alias".equals(attrName)) {
                                eci.setAlias(attrVal);
                            }
                        }
                    }
                }
                break;
            // 닫기 태그
            case XMLStreamReader.END_ELEMENT:
                {
                    String localName = xmlreader.getLocalName();
                    if ("item".equals(localName)) {
                        if (StringUtils.isBlank(eci.getAlias())) {
                            eci.setAlias(eci.getName());
                        }
                        columns.add(eci);
                    }
                    if ("columns".equals(localName)) {
                        breakFlag = true;
                    }
                }
                break;
            default:
                break;
        }
        xmlreader.next();
        if (breakFlag)
            break;
    }
    importDataHandler.setColumns(columns);
}

Example 2 with ExportColumnInfo

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

the class AbstractImportDataHandler method setColumns.

public void setColumns(List<ExportColumnInfo> columns) {
    this.columns = columns;
    StringBuffer querySb = new StringBuffer();
    querySb.append("insert into ").append(this.tableName).append(" (");
    StringBuilder paramSb = new StringBuilder();
    boolean firstFlag = true;
    for (ExportColumnInfo gci : this.columns) {
        if (firstFlag) {
            querySb.append(gci.getName());
            paramSb.append("?");
            firstFlag = false;
        } else {
            querySb.append(", ").append(gci.getName());
            paramSb.append(", ?");
        }
    }
    querySb.append(") values ( ").append(paramSb.toString()).append(") ");
    this.sql = querySb.toString();
}

Example 3 with ExportColumnInfo

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

the class FileImportExecutor method execute.

@Override
public SQLExecuteResult execute(SqlStatementInfo statementInfo) throws SQLException {
    SQLExecuteResult result = new SQLExecuteResult();
    File importFile;
    try {
        importFile = ResourceUtils.getResource(String.valueOf(statementInfo.getCustom().get(IMPORT_FILE_PARAM_NAME))).getFile();
        if (!importFile.exists() || importFile.length() == 0) {
            result.setResultCode(VarsqlAppCode.EC_SQL_EXECUTOR);
            result.setMessage(" import file size zero ");
            return result;
        }
    } catch (IOException e1) {
        result.setResultCode(VarsqlAppCode.EC_SQL_EXECUTOR);
        result.setMessage(" error message :  " + e1.getMessage());
        return result;
    }
    result.setStartTime(System.currentTimeMillis());
    Connection conn = ConnectionFactory.getInstance().getConnection(statementInfo.getVconnid());
    final HandlerVariable handlerVariable = new HandlerVariable(conn);
    try {
        conn.setAutoCommit(false);
        ImportData ijd = null;
        if (VarsqlFileType.JSON.equals(statementInfo.getExportType())) {
            ijd = new ImportJsonData(importFile);
        } else if (VarsqlFileType.XML.equals(statementInfo.getExportType())) {
            ijd = new ImportXmlData(importFile);
        }
        ijd.startImport(new AbstractImportDataHandler() {

            boolean firstFlag = true;

            ExportColumnInfo[] columnArr = null;

            private PreparedStatement statement;

            @Override
            public void handler(Map rowInfo) throws SQLException {
                if (firstFlag) {
                    statement = handlerVariable.getStatement(getSql());
                    firstFlag = false;
                    columnArr = getColumns().toArray(new ExportColumnInfo[0]);
                }
                for (int i = 0; i < columnArr.length; i++) {
                    ExportColumnInfo eci = columnArr[i];
                    // SQLDataType.getCode(eci.getType()).setParameter(this.statement,i + 1, rowInfo.get(eci.getAlias()));
                    statement.setObject(i + 1, rowInfo.get(eci.getAlias()));
                }
                statement.addBatch();
                statement.clearParameters();
                handlerVariable.addCount();
                if (handlerVariable.getCount() % getBatchCount() == 0) {
                    statement.executeBatch();
                    statement.clearBatch();
                }
            }
        });
        if (handlerVariable.getCount() % getBatchCount() != 0) {
            handlerVariable.getStatement().executeBatch();
            handlerVariable.getStatement().clearBatch();
        }
        conn.commit();
    } catch (Throwable e) {
        if (conn != null)
            conn.rollback();
        result.setResultCode(VarsqlAppCode.EC_SQL_EXECUTOR);
        result.setMessage("errorLine : " + handlerVariable.getCount() + ", error message :  " + e.getMessage());
        logger.error("update : {} ", e.getMessage(), e);
    } finally {
        if (conn != null) {
            conn.setAutoCommit(true);
            JdbcUtils.close(conn, handlerVariable.getStatement());
        }
    }
    result.setTotalCount(handlerVariable.getCount());
    result.setEndTime(System.currentTimeMillis());
    result.setExecuteCount(handlerVariable.getCount());
    result.setResult(handlerVariable.getCount());
    return result;
}

Example 4 with ExportColumnInfo

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

the class SQLServiceImpl method dataExport.

/**
 * 데이터 내보내기.
 * @param paramMap
 * @param req
 */
@SuppressWarnings("rawtypes")
public void dataExport(ParamMap paramMap, SqlExecuteDTO sqlExecuteInfo, HttpServletRequest req, HttpServletResponse res) throws Exception {
    String objectName = sqlExecuteInfo.getObjectName();
    if (!sqlExecuteInfo.getBaseSchema().equals(sqlExecuteInfo.getSchema())) {
        objectName = sqlExecuteInfo.getSchema() + "." + objectName;
    }
    StringBuilder reqQuerySb = new StringBuilder().append("select * from ").append(objectName).append(" where 1=1 ");
    String conditionQuery = paramMap.getString("conditionQuery");
    if (!StringUtils.isBlank(conditionQuery)) {
        conditionQuery = StringUtils.lTrim(conditionQuery);
        if (conditionQuery.startsWith("where")) {
            conditionQuery = conditionQuery.replaceFirst("where", "");
        }
        if (conditionQuery.startsWith("and")) {
            conditionQuery = conditionQuery.replaceFirst("and", "");
        }
        conditionQuery = " and " + conditionQuery;
        reqQuerySb.append(conditionQuery);
    }
    sqlExecuteInfo.setSqlParam("{}");
    sqlExecuteInfo.setSql(reqQuerySb.toString());
    String exportCharset = paramMap.getString("charset", VarsqlConstants.CHAR_SET);
    OutputStream outstream = null;
    AbstractWriter writer = null;
    try {
        VarsqlFileType exportType = sqlExecuteInfo.getExportType();
        Path fileExportPath = FileServiceUtils.getSavePath(UploadFileType.EXPORT);
        String downloadFilePath = FileUtils.pathConcat(fileExportPath.toAbsolutePath().toString(), exportType.concatExtension(VartechUtils.generateUUID()));
        outstream = new FileOutputStream(downloadFilePath);
        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);
        } else {
            writer = new SQLWriter(outstream, DBType.getDBType(sqlExecuteInfo.getDbType()), objectName, exportCharset);
        }
        logger.debug("data export downloadFilePath :{} , query : {}", downloadFilePath, sqlExecuteInfo.getSql());
        final String tableName = objectName;
        SQLExecuteResult ser = new SelectExecutor().execute(sqlExecuteInfo, new AbstractSQLExecutorHandler(writer) {

            private boolean firstFlag = true;

            @Override
            public boolean handle(SQLHandlerParameter handleParam) {
                if (firstFlag) {
                    WriteMetadataInfo whi = new WriteMetadataInfo("exportInfo");
                    List<ExportColumnInfo> columns = new LinkedList<ExportColumnInfo>();
                    handleParam.getColumnInfoList().forEach(item -> {
                        ExportColumnInfo gci = new ExportColumnInfo();
                        gci.setName(item.getLabel());
                        gci.setAlias(item.getKey());
                        gci.setType(item.getDbType());
                        gci.setNumber(item.isNumber());
                        gci.setLob(item.isLob());
                        columns.add(gci);
                    });
                    whi.addMetedata("tableName", tableName);
                    whi.addMetedata("columns", columns);
                    getWriter().setMetadata(whi);
                    if (VarsqlFileType.SQL.equals(exportType)) {
                        ((SQLWriter) getWriter()).setColumnInfos(handleParam.getColumnInfoList());
                    }
                    firstFlag = false;
                }
                try {
                    getWriter().addRow(handleParam.getRowObject());
                } catch (IOException e) {
                    logger.error(e.getMessage(), e);
                    return false;
                }
                return true;
            }
        });
        writer.writeAndClose();
        if (ser.getResultCode() != null) {
            throw new DataDownloadException(ser.getResultCode(), ser.getMessage(), new VarsqlAppException(ser.getMessage()));
        }
        String exportFileName = ValidateUtils.getValidFileName(paramMap.getString("fileName", objectName));
        VarsqlUtils.setResponseDownAttr(res, req, exportType.concatExtension(exportFileName));
        try (FileInputStream fileInputStream = new FileInputStream(new File(downloadFilePath));
            OutputStream downloadStream = res.getOutputStream()) {
            byte[] buf = new byte[8192];
            int read = 0;
            while ((read = fileInputStream.read(buf)) != -1) {
                downloadStream.write(buf, 0, read);
            }
            downloadStream.flush();
            downloadStream.close();
            fileInputStream.close();
        }
    } catch (Exception e) {
        throw e;
    } finally {
        IOUtils.close(writer);
        IOUtils.close(outstream);
    }
}

Example 5 with ExportColumnInfo

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

the class ExportServiceImpl method downloadTableData2.

public ResponseResult downloadTableData2(PreferencesRequestDTO preferencesInfo, HttpServletRequest req, HttpServletResponse res) throws Exception {
    DataDownloadInfo ddi = VartechUtils.jsonStringToObject(preferencesInfo.getPrefVal(), DataDownloadInfo.class, true);
    DatabaseInfo databaseInfo = SecurityUtil.loginInfo().getDatabaseInfo().get(ddi.getConuid());
    logger.debug("downloadTableData : {}", preferencesInfo.getPrefVal());
    String prefixSchema = StringUtils.isBlank(ddi.getSchema()) ? "" : ddi.getSchema() + ".";
    String charset = StringUtils.isBlank(ddi.getCharset()) ? VarsqlConstants.CHAR_SET : ddi.getCharset();
    String exportFileName = ValidateUtils.getValidFileName(ddi.getFileName());
    String fileId = VartechUtils.generateUUID();
    Path fileExportPath = FileServiceUtils.getSavePath(UploadFileType.EXPORT);
    File zipFile = new File(FileUtils.pathConcat(fileExportPath.toAbsolutePath().toString(), VarsqlFileType.ZIP.concatExtension(fileId)));
    int bufferSize = 2048;
    final VarsqlFileType exportType = ddi.getExportType();
    DBType dbType = DBType.getDBType(databaseInfo.getType());
    String requid = ddi.getRequid();
    ProgressInfo progressInfo = new ProgressInfo();
    progressInfo.setTotalItemSize(ddi.getItems().size());
    String sessAttrKey = HttpSessionConstants.progressKey(requid);
    System.out.println("downloadTableData2 sessAttrKey : " + sessAttrKey);
    HttpSession session = req.getSession();
    session.setAttribute(sessAttrKey, progressInfo);
    try (ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(zipFile), Charset.forName(charset))) {
        int idx = 0;
        for (DownloadItemInfo item : ddi.getItems()) {
            ++idx;
            SqlExecuteDTO seDto = new SqlExecuteDTO();
            seDto.setLimit(ddi.getLimit());
            seDto.setConuid(ddi.getConuid(), exportFileName, databaseInfo);
            String objectName = prefixSchema + item.getName();
            seDto.setSqlParam("{}");
            seDto.setSql(SQLUtils.generateSelectQuery(objectName, item.getCondition(), dbType));
            String downloadFilePath = FileUtils.pathConcat(fileExportPath.toAbsolutePath().toString(), exportType.concatExtension(VartechUtils.generateUUID()));
            progressInfo.setName(item.getName());
            progressInfo.setItemIdx(idx);
            AbstractWriter writer = null;
            try (OutputStream outstream = new FileOutputStream(downloadFilePath)) {
                if (VarsqlFileType.CSV.equals(exportType)) {
                    writer = new CSVWriter(outstream, ',', charset);
                } else if (VarsqlFileType.JSON.equals(exportType)) {
                    writer = new JSONWriter(outstream, "row", charset);
                } else if (VarsqlFileType.XML.equals(exportType)) {
                    writer = new XMLWriter(outstream, "row", charset);
                } else if (VarsqlFileType.EXCEL.equals(exportType)) {
                    writer = new ExcelWriter(outstream);
                } else {
                    writer = new SQLWriter(outstream, dbType, objectName, charset);
                }
                logger.debug("data export downloadFilePath :{} , query : {}", downloadFilePath, seDto.getSql());
                final String tableName = item.getName();
                SQLExecuteResult ser = (new SelectExecutor()).execute(seDto, new AbstractSQLExecutorHandler(writer) {

                    private boolean firstFlag = true;

                    private int rowIdx = 0;

                    public boolean handle(SQLHandlerParameter handleParam) {
                        if (this.firstFlag) {
                            WriteMetadataInfo whi = new WriteMetadataInfo("exportInfo");
                            List<ExportColumnInfo> columns = new LinkedList<>();
                            handleParam.getColumnInfoList().forEach(item -> {
                                ExportColumnInfo gci = new ExportColumnInfo();
                                gci.setName(item.getLabel());
                                // 추가 할것.
                                gci.setAlias(item.getKey());
                                gci.setType(item.getDbType());
                                gci.setNumber(item.isNumber());
                                gci.setLob(item.isLob());
                                columns.add(gci);
                            });
                            whi.addMetedata("tableName", tableName);
                            whi.addMetedata("columns", columns);
                            getWriter().setMetadata(new WriteMetadataInfo[] { whi });
                            if (VarsqlFileType.SQL.equals(exportType)) {
                                ((SQLWriter) getWriter()).setColumnInfos(handleParam.getColumnInfoList());
                            }
                            this.firstFlag = false;
                        }
                        progressInfo.setProgressContentLength(++rowIdx);
                        try {
                            getWriter().addRow(handleParam.getRowObject());
                        } catch (IOException e) {
                            logger.error(e.getMessage(), e);
                            return false;
                        }
                        return true;
                    }
                });
                writer.writeAndClose();
                String zipFileName = exportType.concatExtension(item.getName());
                if (ser.getResultCode() != null) {
                    VarsqlUtils.textDownload(new FileOutputStream(downloadFilePath), ser.getMessage());
                    zipFileName = item.getName() + "-export-error.txt";
                }
                try (BufferedInputStream bis = new BufferedInputStream(new FileInputStream(downloadFilePath))) {
                    ZipEntry zentry = new ZipEntry(zipFileName);
                    zentry.setSize(new File(downloadFilePath).length());
                    zos.putNextEntry(zentry);
                    byte[] buffer = new byte[bufferSize];
                    int cnt = 0;
                    while ((cnt = bis.read(buffer, 0, bufferSize)) != -1) {
                        zos.write(buffer, 0, cnt);
                    }
                    zos.closeEntry();
                }
                new File(downloadFilePath).delete();
            } catch (Exception e) {
                throw e;
            } finally {
                IOUtils.close(writer);
            }
        }
        IOUtils.close(zos);
    }
    FileInfoEntity fie = FileInfoEntity.builder().fileId(fileId).fileContId(fileId).contGroupId(databaseInfo.getVconnid()).fileDiv(UploadFileType.EXPORT.getDiv()).fileFieldName("downloadZipFile").fileName(VarsqlFileType.ZIP.concatExtension(exportFileName)).fileSize(zipFile.length()).fileExt(VarsqlFileType.ZIP.getExtension()).filePath(FileUtils.pathConcat(FileServiceUtils.getSaveRelativePath(UploadFileType.EXPORT), zipFile.getName())).build();
    fileInfoEntityRepository.save(fie);
    session.setAttribute(sessAttrKey, "complete");
    return VarsqlUtils.getResponseResultItemOne(fie);
}