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

Example 1 with CfDatasource

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

the class CfDatasourceDAOImpl method findByName.

@Override
public CfDatasource findByName(String name) {
    Session session = this.sessionFactory.getCurrentSession();
    TypedQuery query = (TypedQuery) session.getNamedQuery("CfDatasource.findByName");
    query.setParameter("name", name);
    CfDatasource cfdatasource = (CfDatasource) query.getSingleResult();
    return cfdatasource;
}

Example 2 with CfDatasource

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

the class DatasourceList method onCreateContent.

/**
 * Creates an external datasource
 * @param actionEvent
 */
public void onCreateContent(ActionEvent actionEvent) {
    try {
        CfDatasource newdatasourcecontent = new CfDatasource();
        newdatasourcecontent.setName(datasourceName);
        newdatasourcecontent.setDatabasename(datasourceDatabasename);
        newdatasourcecontent.setDriverclass(datasourceDriverclass);
        newdatasourcecontent.setPassword(datasourcePassword);
        newdatasourcecontent.setPort(datasourcePort);
        newdatasourcecontent.setServer(datasourceServer);
        newdatasourcecontent.setUrl(datasourceURL);
        newdatasourcecontent.setUser(datasourceUser);
        cfdatasourceService.create(newdatasourcecontent);
        datasourcelist = cfdatasourceService.findAll();
    } catch (ConstraintViolationException ex) {
        LOGGER.error(ex.getMessage());
    }
}

Example 3 with CfDatasource

use of io.clownfish.clownfish.dbentities.CfDatasource 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 4 with CfDatasource

use of io.clownfish.clownfish.dbentities.CfDatasource 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 5 with CfDatasource

use of io.clownfish.clownfish.dbentities.CfDatasource 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;
}