Examples with WriteMetadataInfo com.vartech.common.io.writer.WriteMetadataInfo used on opensource projects

Example 1 with WriteMetadataInfo

use of com.vartech.common.io.writer.WriteMetadataInfo 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 2 with WriteMetadataInfo

use of com.vartech.common.io.writer.WriteMetadataInfo 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);
}