Examples with CfSitedatasource io.clownfish.clownfish.dbentities.CfSitedatasource used on opensource projects

Example 1 with CfSitedatasource

use of io.clownfish.clownfish.dbentities.CfSitedatasource in project Clownfish by rawdog71.

the class CfSitedatasourceDAOImpl method findAll.

@Override
public List<CfSitedatasource> findAll() {
    Session session = this.sessionFactory.getCurrentSession();
    TypedQuery query = (TypedQuery) session.getNamedQuery("CfSitedatasource.findAll");
    List<CfSitedatasource> cfsitedatasourcelist = query.getResultList();
    return cfsitedatasourcelist;
}

Example 2 with CfSitedatasource

use of io.clownfish.clownfish.dbentities.CfSitedatasource in project Clownfish by rawdog71.

the class DatabaseUtil method getDbexport.

public HashMap<String, HashMap> getDbexport(List<CfSitedatasource> sitedatasourcelist, HashMap<String, DatatableProperties> datatableproperties, HashMap<String, DatatableNewProperties> datatablenewproperties, HashMap<String, DatatableDeleteProperties> datatabledeleteproperties, HashMap<String, DatatableUpdateProperties> datatableupdateproperties) {
    HashMap<String, HashMap> dbexport = new HashMap<>();
    for (CfSitedatasource sitedatasource : sitedatasourcelist) {
        CfDatasource cfdatasource = cfdatasourceService.findById(sitedatasource.getCfSitedatasourcePK().getDatasourceref());
        JDBCUtil jdbcutil = new JDBCUtil(cfdatasource.getDriverclass(), cfdatasource.getUrl(), cfdatasource.getUser(), cfdatasource.getPassword());
        Connection con = jdbcutil.getConnection();
        if (null != con) {
            try {
                DatabaseMetaData dmd = con.getMetaData();
                ResultSet resultSetTables = dmd.getTables(null, null, null, new String[] { "TABLE" });
                HashMap<String, ArrayList> dbtables = new HashMap<>();
                HashMap<String, Object> dbvalues = new HashMap<>();
                while (resultSetTables.next()) {
                    String tablename = resultSetTables.getString("TABLE_NAME");
                    // System.out.println(tablename);
                    if (datatableproperties.get(tablename) != null) {
                        manageTableRead(con, dmd, tablename, datatableproperties, dbtables, dbvalues);
                    }
                    if (datatablenewproperties.get(tablename) != null) {
                        boolean ok = manageTableInsert(con, dmd, tablename, datatablenewproperties, dbtables, dbvalues);
                        if (ok) {
                            dbvalues.put("INSERT", "true");
                        } else {
                            dbvalues.put("INSERT", "false");
                        }
                    }
                    if (datatabledeleteproperties.get(tablename) != null) {
                        boolean ok = manageTableDelete(con, dmd, tablename, datatabledeleteproperties, dbtables, dbvalues);
                        if (ok) {
                            dbvalues.put("DELETE", "true");
                        } else {
                            dbvalues.put("DELETE", "false");
                        }
                    }
                    if (datatableupdateproperties.get(tablename) != null) {
                        boolean ok = manageTableUpdate(con, dmd, tablename, datatableupdateproperties, dbtables, dbvalues);
                        if (ok) {
                            dbvalues.put("UPDATE", "true");
                        } else {
                            dbvalues.put("UPDATE", "false");
                        }
                    }
                }
                resultSetTables = dmd.getTables(null, null, null, new String[] { "VIEW" });
                while (resultSetTables.next()) {
                    String tablename = resultSetTables.getString("TABLE_NAME");
                    // System.out.println(tablename);
                    if (datatableproperties.get(tablename) != null) {
                        manageTableRead(con, dmd, tablename, datatableproperties, dbtables, dbvalues);
                    }
                }
                dbvalues.put("table", dbtables);
                dbexport.put(cfdatasource.getDatabasename(), dbvalues);
            } catch (SQLException ex) {
                LOGGER.error(ex.getMessage());
            }
        } else {
            return null;
        }
    }
    return dbexport;
}

Example 3 with CfSitedatasource

use of io.clownfish.clownfish.dbentities.CfSitedatasource in project Clownfish by rawdog71.

the class DatabaseTemplateBean method dbexecute.

public boolean dbexecute(String catalog, String sqlstatement) {
    boolean ok = false;
    // LOGGER.info("START dbexecute: " + sqlstatement);
    for (CfSitedatasource sitedatasource : sitedatasourcelist) {
        CfDatasource cfdatasource = cfdatasourceService.findById(sitedatasource.getCfSitedatasourcePK().getDatasourceref());
        JDBCUtil jdbcutil = new JDBCUtil(cfdatasource.getDriverclass(), cfdatasource.getUrl(), cfdatasource.getUser(), cfdatasource.getPassword());
        Connection con = jdbcutil.getConnection();
        if (null != con) {
            String catalogName;
            try {
                if (cfdatasource.getDriverclass().contains("oracle")) {
                    // Oracle driver
                    catalogName = con.getSchema();
                } else {
                    // other drivers
                    catalogName = con.getCatalog();
                }
                if (catalogName.compareToIgnoreCase(catalog) == 0) {
                    try (Statement stmt = con.createStatement()) {
                        int count = stmt.executeUpdate(sqlstatement);
                        if (count > 0) {
                            ok = true;
                            LOGGER.info("START dbexecute TRUE");
                        } else {
                            LOGGER.info("START dbexecute FALSE");
                        }
                    }
                }
                con.close();
            } catch (SQLIntegrityConstraintViolationException e) {
                LOGGER.error(e.getMessage());
                ok = true;
            } catch (SQLException ex) {
                LOGGER.error(ex.getMessage());
            } finally {
                try {
                    con.close();
                } catch (SQLException ex) {
                    LOGGER.error(ex.getMessage());
                }
            }
        } else {
            LOGGER.warn("Connection to database not established");
        }
    }
    // LOGGER.info("END dbexecute");
    return ok;
}

Example 4 with CfSitedatasource

use of io.clownfish.clownfish.dbentities.CfSitedatasource in project Clownfish by rawdog71.

the class ImportTemplateBean method readCsvAndFillDatabase.

public long readCsvAndFillDatabase(String fileIn, String schemaName, String tblName, boolean bHeader, boolean bTruncate, String encoding) {
    // Default UTF-8 encoding
    encoding = encoding != null ? encoding : "UTF8";
    File fileIn1 = new File(fileIn);
    boolean status;
    long iTotalRecords = 0;
    for (CfSitedatasource sitedatasource : sitedatasourcelist) {
        try {
            CfDatasource cfdatasource = cfdatasourceService.findById(sitedatasource.getCfSitedatasourcePK().getDatasourceref());
            JDBCUtil jdbcutil = new JDBCUtil(cfdatasource.getDriverclass(), cfdatasource.getUrl(), cfdatasource.getUser(), cfdatasource.getPassword());
            Connection connection = jdbcutil.getConnection();
            if (connection != null) {
                String catalogName;
                if (cfdatasource.getDriverclass().contains("oracle")) {
                    // Oracle driver
                    catalogName = connection.getSchema();
                } else {
                    // other drivers
                    catalogName = connection.getCatalog();
                }
                boolean bSkipFirstLine;
                if (catalogName.compareToIgnoreCase(schemaName) == 0) {
                    ArrayList<String> header = new ArrayList<>();
                    // If our CSV has a header, use it
                    if (bHeader) {
                        Reader readr = new BufferedReader(new InputStreamReader(new FileInputStream(fileIn1), encoding));
                        CSVParser prsr = new CSVParserBuilder().withSeparator(';').withIgnoreLeadingWhiteSpace(true).build();
                        CSVReader csvReadr = new CSVReaderBuilder(readr).withCSVParser(prsr).build();
                        Collections.addAll(header, csvReadr.readNext());
                        bSkipFirstLine = true;
                        csvReadr.close();
                        readr.close();
                    } else // ...otherwise, grab all the table's column names
                    {
                        String query = "SELECT * FROM " + schemaName + "." + tblName + " LIMIT 1;";
                        Statement stmt = connection.createStatement();
                        ResultSet result = stmt.executeQuery(query);
                        ResultSetMetaData rmd = result.getMetaData();
                        for (int i = 1; i <= rmd.getColumnCount(); i++) {
                            header.add(rmd.getColumnName(i));
                        }
                        bSkipFirstLine = false;
                    }
                    Reader reader = new BufferedReader(new InputStreamReader(new FileInputStream(fileIn1), encoding));
                    CSVParser parser = new CSVParserBuilder().withSeparator(';').withIgnoreLeadingWhiteSpace(true).build();
                    CSVReader csvReader;
                    if (bSkipFirstLine)
                        csvReader = new CSVReaderBuilder(reader).withCSVParser(parser).withSkipLines(1).build();
                    else
                        csvReader = new CSVReaderBuilder(reader).withCSVParser(parser).build();
                    String[] nextLine;
                    int iLines = 0;
                    final int iBatchSize = 10;
                    if (bTruncate) {
                        Statement truncate = connection.createStatement();
                        truncate.execute("TRUNCATE TABLE " + tblName + ";");
                    }
                    PreparedStatement statement = connection.prepareStatement(generateSqlStatement(header, tblName));
                    // Read CSV and write to database in batches
                    while ((nextLine = csvReader.readNext()) != null) {
                        iLines++;
                        // Add results to batch
                        for (int i = 0; i < header.size(); i++) {
                            statement.setString(i + 1, nextLine[i].trim());
                        }
                        statement.addBatch();
                        // Execute SQL statement once batch size is reached
                        if (iLines >= iBatchSize) {
                            iTotalRecords += doExecute(statement);
                            iLines = 0;
                        }
                        // Finish up remaining rows
                        if (iLines >= 0) {
                            System.out.println(statement.toString());
                            iTotalRecords += doExecute(statement);
                        }
                    }
                    status = true;
                    LOGGER.info("Finished database import successfully! " + iTotalRecords + " records added.");
                    csvReader.close();
                    reader.close();
                }
                connection.close();
                return iTotalRecords;
            } else {
                status = false;
                LOGGER.error("Connection to database not established");
                return -1;
            }
        } catch (SQLException | IOException | CsvValidationException ex) {
            LOGGER.error(ex.getMessage());
            return -1;
        }
    }
    return -1;
}

Example 5 with CfSitedatasource

use of io.clownfish.clownfish.dbentities.CfSitedatasource in project Clownfish by rawdog71.

the class PDFUtil method createPDF.

public ByteArrayOutputStream createPDF(String name, String param) throws IOException {
    // Fetch site
    CfSite site = cfSiteService.findByName(name);
    HashMap<String, String> params = new HashMap<>();
    // Put request params in HashMap
    if (param != null) {
        String[] arr = param.split("\\$");
        int counter = 0;
        for (String key : arr) {
            if ((counter > 0) && ((counter % 2) != 0)) {
                params.put(arr[counter - 1], arr[counter]);
            }
            counter++;
        }
    }
    // Get the current template content
    long currentTemplateVersion;
    try {
        cfTemplate = cfTemplateService.findById(site.getTemplateref().longValue());
        currentTemplateVersion = cfTemplateversionService.findMaxVersion(cfTemplate.getId());
    } catch (NullPointerException ex) {
        currentTemplateVersion = 0;
    }
    String templateContent = templateUtil.getVersion(cfTemplate.getId(), currentTemplateVersion);
    // Search and replace params key/values in template content
    for (String key : params.keySet()) {
        templateContent = templateContent.replaceAll("@" + key + "@", params.get(key));
    }
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    // Fetch site datasources
    List<CfSitedatasource> sitedatasourcelist = cfSitedatasourceService.findBySiteref(site.getId());
    for (CfSitedatasource source : sitedatasourcelist) {
        CfDatasource datasource = cfDatasourceService.findById(source.getCfSitedatasourcePK().getDatasourceref());
        InputStream template = new ByteArrayInputStream(templateContent.getBytes(StandardCharsets.UTF_8));
        out = JasperReportCompiler.exportToPdf(datasource.getUser(), datasource.getPassword(), datasource.getUrl(), template, datasource.getDriverclass());
        byte[] bytes = out.toByteArray();
        FileOutputStream fileOut;
        StringBuilder stringBuilder = new StringBuilder();
        AtomicInteger count = new AtomicInteger();
        params.forEach((k, v) -> {
            stringBuilder.append("-");
            if (count.get() < params.size() && params.size() > 1)
                stringBuilder.append(v);
            else
                stringBuilder.append(v);
            count.getAndIncrement();
        });
        fileOut = new FileOutputStream(propertyUtil.getPropertyValue("folder_pdf") + File.separator + name + stringBuilder + ".pdf");
        fileOut.write(bytes);
        fileOut.close();
        out.write(bytes, 0, bytes.length);
        out.flush();
        out.close();
    }
    return out;
}