Examples with JDBCUtil io.clownfish.clownfish.jdbc.JDBCUtil used on opensource projects

Example 1 with JDBCUtil

use of io.clownfish.clownfish.jdbc.JDBCUtil in project Clownfish by rawdog71.

the class JasperReportCompiler method exportToPdf.

public static ByteArrayOutputStream exportToPdf(String user, String password, String dataBaseUrl, InputStream template, String driver) {
    HashMap<String, Object> hm = new HashMap<>();
    try {
        JDBCUtil db = new JDBCUtil(driver, dataBaseUrl, user, password);
        // Fill the report
        JasperReport rp = JasperCompileManager.compileReport(template);
        JasperPrint print = JasperFillManager.fillReport(rp, hm, db.getConnection());
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        JasperExportManager.exportReportToPdfStream(print, out);
        return out;
    } catch (JRException e) {
        LOGGER.error(e.getMessage());
        return null;
    }
}

Example 2 with JDBCUtil

use of io.clownfish.clownfish.jdbc.JDBCUtil in project Clownfish by rawdog71.

the class Main method bootstrap_update.

/**
 * Checks the applications.properties file and runs the bootstrap routine when the bootstrap parameter is set to 1
 * Fetches the database (MySQL) parameters from applications.properties and runs the sql-bootstrap.sql script
 * The script creates the database user for reading/writing (user=clownfish), creates all tables and initializes some tables with data
 */
public static void bootstrap_update() {
    InputStream fis = null;
    try {
        Properties props = new Properties();
        String propsfile = "application.properties";
        fis = new FileInputStream(propsfile);
        if (null != fis) {
            props.load(fis);
        }
        String dbclass = props.getProperty("app.datasource.driverClassName");
        String dburl = props.getProperty("app.datasource.url");
        String dbuser = props.getProperty("app.datasource.username");
        String dbpassword = props.getProperty("app.datasource.password");
        String path = new File(".").getCanonicalPath();
        File bootstrapDirectory = new File(path);
        File[] files = bootstrapDirectory.listFiles();
        Arrays.sort(files);
        for (int i = 0; i < files.length; i++) {
            if (files[i].getName().startsWith("bootstrap_")) {
                InputStream is = null;
                try {
                    Properties boot_props = new Properties();
                    is = new FileInputStream(files[i]);
                    if (null != is) {
                        boot_props.load(is);
                    }
                    int bootstrap = Integer.parseInt(boot_props.getProperty("bootstrap"));
                    String version = boot_props.getProperty("version");
                    String bootstrapfile = boot_props.getProperty("bootstrapfile");
                    if (1 == bootstrap) {
                        bootstrap = 0;
                        AnsiConsole.systemInstall();
                        System.out.println(ansi().fg(GREEN));
                        System.out.println("BOOTSTRAPPING UPDATE VERSION " + version);
                        System.out.println(ansi().reset());
                        JDBCUtil jdbcutil = new JDBCUtil(dbclass, dburl, dbuser, dbpassword);
                        ScriptRunner runner = new ScriptRunner(jdbcutil.getConnection(), false, false);
                        runner.runScript(new BufferedReader(new FileReader(bootstrapfile)));
                        File f = new File(files[i].getName());
                        OutputStream out = new FileOutputStream(f);
                        boot_props.setProperty("bootstrap", String.valueOf(bootstrap));
                        DefaultPropertiesPersister p = new DefaultPropertiesPersister();
                        p.store(boot_props, out, "Bootstrap properties");
                    }
                } catch (FileNotFoundException ex) {
                    LOGGER.error(ex.getMessage());
                } catch (IOException | SQLException ex) {
                    LOGGER.error(ex.getMessage());
                } finally {
                    try {
                        if (null != is) {
                            is.close();
                        }
                    } catch (IOException ex) {
                        LOGGER.error(ex.getMessage());
                    }
                }
            }
        }
    } catch (FileNotFoundException ex) {
        LOGGER.error(ex.getMessage());
    } catch (IOException ex) {
        LOGGER.error(ex.getMessage());
    } finally {
        try {
            if (null != fis) {
                fis.close();
            }
        } catch (IOException ex) {
            LOGGER.error(ex.getMessage());
        }
    }
}

Example 3 with JDBCUtil

use of io.clownfish.clownfish.jdbc.JDBCUtil in project Clownfish by rawdog71.

the class Main method bootstrap.

/**
 * Checks the applications.properties file and runs the bootstrap routine when the bootstrap parameter is set to 1
 * Fetches the database (MySQL) parameters from applications.properties and runs the sql-bootstrap.sql script
 * The script creates the database user for reading/writing (user=clownfish), creates all tables and initializes some tables with data
 */
public static void bootstrap() {
    InputStream is = null;
    try {
        Properties props = new Properties();
        String propsfile = "application.properties";
        is = new FileInputStream(propsfile);
        if (null != is) {
            props.load(is);
        }
        int bootstrap = Integer.parseInt(props.getProperty("bootstrap"));
        String dbclass = props.getProperty("app.datasource.driverClassName");
        String dburl = props.getProperty("app.datasource.urlroot");
        String dbuser = props.getProperty("app.datasource.root");
        String dbpassword = props.getProperty("app.datasource.rootpw");
        if (1 == bootstrap) {
            AnsiConsole.systemInstall();
            System.out.println(ansi().fg(GREEN));
            System.out.println("BOOTSTRAPPING I");
            System.out.println(ansi().reset());
            JDBCUtil jdbcutil = new JDBCUtil(dbclass, dburl, dbuser, dbpassword);
            ScriptRunner runner = new ScriptRunner(jdbcutil.getConnection(), true, false);
            String file = "sql-bootstrap.sql";
            runner.runScript(new BufferedReader(new FileReader(file)));
        }
    } catch (FileNotFoundException ex) {
        LOGGER.error(ex.getMessage());
    } catch (IOException | SQLException ex) {
        LOGGER.error(ex.getMessage());
    } finally {
        try {
            if (null != is) {
                is.close();
            }
        } catch (IOException ex) {
            LOGGER.error(ex.getMessage());
        }
    }
}

Example 4 with JDBCUtil

use of io.clownfish.clownfish.jdbc.JDBCUtil 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 5 with JDBCUtil

use of io.clownfish.clownfish.jdbc.JDBCUtil 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;
}