Examples with RowCallbackHandler org.springframework.jdbc.core.RowCallbackHandler used on opensource projects

Example 46 with RowCallbackHandler

use of org.springframework.jdbc.core.RowCallbackHandler in project OsmAnd-tools by osmandapp.

the class AdminController method getYearSubscriptionsRetentionReport.

private Collection<YearSubscriptionRetentionReport> getYearSubscriptionsRetentionReport() {
    final Map<String, YearSubscriptionRetentionReport> res = new LinkedHashMap<String, YearSubscriptionRetentionReport>();
    jdbcTemplate.query("select  to_char(starttime, 'YYYY-MM') \"start\", \n" + "    round(extract(day from expiretime - starttime)/365) \"years\", \n" + "    sku, introcycles,\n" + "    count(*) FILTER (WHERE valid and     (autorenewing and now() - '8 days'::interval < expiretime)) \"active\",\n" + "    count(*) FILTER (WHERE valid and not (autorenewing and now() - '8 days'::interval < expiretime)) \"possiblygone\",\n" + "    count(*) FILTER (WHERE not valid) \"gone\"\n" + "    from supporters_device_sub \n" + "    where sku like '%annual%' and extract(day from expiretime - starttime) > 180\n" + "    group by \"start\", \"years\", sku, introcycles\n" + "    order by 1 desc, 2, 3, 4;", new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            int ind = 1;
            String month = rs.getString(ind++);
            int years = rs.getInt(ind++);
            String sku = rs.getString(ind++);
            boolean intro = rs.getInt(ind++) > 0;
            int active = rs.getInt(ind++);
            int possibleGone = rs.getInt(ind++);
            int gone = rs.getInt(ind++);
            YearSubscriptionRetentionReport report = res.get(month);
            if (report == null) {
                report = new YearSubscriptionRetentionReport(month);
                res.put(month, report);
            }
            if (sku.startsWith("net.osmand")) {
                report.ios.addNumber(years, active, possibleGone, gone);
                if (intro) {
                    report.iosIntro.addNumber(years, active, possibleGone, gone);
                } else {
                    report.iosFull.addNumber(years, active, possibleGone, gone);
                }
            } else if (sku.contains("v1")) {
                report.android.addNumber(years, active, possibleGone, gone);
                if (intro) {
                    report.androidIntro.addNumber(years, active, possibleGone, gone);
                } else {
                    report.androidFull.addNumber(years, active, possibleGone, gone);
                }
            } else if (sku.contains("v2")) {
                report.android.addNumber(years, active, possibleGone, gone);
                report.androidV2.addNumber(years, active, possibleGone, gone);
            }
            report.total.addNumber(years, active, possibleGone, gone);
        }
    });
    ArrayList<YearSubscriptionRetentionReport> list = new ArrayList<>(res.values());
    YearSubscriptionRetentionReport totalAll = new YearSubscriptionRetentionReport("All");
    Map<String, YearSubscriptionRetentionReport> yearsTotal = new TreeMap<String, AdminController.YearSubscriptionRetentionReport>();
    for (YearSubscriptionRetentionReport r : res.values()) {
        String year = r.month.substring(0, 4);
        YearSubscriptionRetentionReport yearSubscriptionReport = yearsTotal.get(year);
        if (yearSubscriptionReport == null) {
            yearSubscriptionReport = new YearSubscriptionRetentionReport(year);
            yearsTotal.put(year, yearSubscriptionReport);
        }
        yearSubscriptionReport.plus(r);
        totalAll.plus(r);
    }
    for (YearSubscriptionRetentionReport y : yearsTotal.values()) {
        list.add(0, y);
    }
    list.add(0, totalAll);
    return list;
}

Example 47 with RowCallbackHandler

use of org.springframework.jdbc.core.RowCallbackHandler in project OsmAnd-tools by osmandapp.

the class AdminController method parseExchangeRates.

private ExchangeRates parseExchangeRates() {
    SimpleDateFormat dayFormat = new SimpleDateFormat("yyyy-MM-dd");
    ExchangeRates rates = new ExchangeRates();
    jdbcTemplate.query("select currency, month, eurrate from exchange_rates", new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            try {
                Date parse = dayFormat.parse(rs.getString(2));
                String cur = rs.getString(1);
                double eurRate = rs.getDouble(3);
                rates.add(cur, parse.getTime(), eurRate);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
    });
    return rates;
}

Example 48 with RowCallbackHandler

use of org.springframework.jdbc.core.RowCallbackHandler in project Gemma by PavlidisLab.

the class LinkAnalysisServiceTest method checkUnsupportedLinksHaveNoSupport.

private void checkUnsupportedLinksHaveNoSupport() {
    JdbcTemplate jt = new JdbcTemplate(dataSource);
    // see SupportDetailsTest for validation that these strings represent empty byte arrays. I think the 1 at
    // position 12 is important.
    final Collection<Long> checkme = new HashSet<>();
    // maybe these patterns aren't this reproducible.
    jt.query(// 000002BB00000001000000000000001600000000
    "SELECT ID FROM MOUSE_LINK_SUPPORT_DETAILS WHERE HEX(BYTES) LIKE '00000___0000000100000000000000%'", new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            Long id = rs.getLong(1);
            checkme.add(id);
        }
    });
    // we should definitely have some of these
    assertTrue(checkme.size() > 0);
    jt.query("SELECT SUPPORT FROM MOUSE_GENE_COEXPRESSION WHERE SUPPORT_DETAILS_FK IN (?) AND SUPPORT > 0", new Object[] { checkme.toArray() }, new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) {
            fail("Should not have had any rows");
        }
    });
}

Example 49 with RowCallbackHandler

use of org.springframework.jdbc.core.RowCallbackHandler in project ma-core-public by infiniteautomation.

the class Upgrade9 method upgrade.

@Override
public void upgrade() throws Exception {
    // Run the script.
    Map<String, String[]> scripts = new HashMap<>();
    scripts.put(DatabaseType.DERBY.name(), derbyScript);
    scripts.put(DatabaseType.MYSQL.name(), mysqlScript);
    scripts.put(DatabaseType.MSSQL.name(), mssqlScript);
    scripts.put(DatabaseType.H2.name(), mysqlScript);
    runScript(scripts);
    // Convert existing permissions data.
    // First the data source data
    final Map<Integer, String> dsPermission = new HashMap<>();
    ejt.query("SELECT ds.dataSourceId, u.username FROM dataSourceUsers ds JOIN users u ON ds.userId=u.id", new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            int dsId = rs.getInt(1);
            String username = rs.getString(2);
            updatePermissionString(dsPermission, dsId, username);
        }
    });
    for (Entry<Integer, String> e : dsPermission.entrySet()) ejt.update("UPDATE dataSources SET editPermission=? WHERE id=?", e.getValue(), e.getKey());
    // Now the data point data
    final Map<Integer, String> dpReadPermission = new HashMap<>();
    final Map<Integer, String> dpSetPermission = new HashMap<>();
    ejt.query("SELECT p.dataPointId, p.permission, u.username FROM dataPointUsers p JOIN users u ON p.userId=u.id", new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            int dpId = rs.getInt(1);
            int permission = rs.getInt(2);
            String username = rs.getString(3);
            if (// Read
            permission == 1)
                updatePermissionString(dpReadPermission, dpId, username);
            else if (// Set
            permission == 2)
                updatePermissionString(dpSetPermission, dpId, username);
            else
                LOG.warn("Unknown permission type in dataPointUsers: " + permission + ", ignored");
        }
    });
    for (Entry<Integer, String> e : dpReadPermission.entrySet()) ejt.update("UPDATE dataPoints SET readPermission=? WHERE id=?", e.getValue(), e.getKey());
    for (Entry<Integer, String> e : dpSetPermission.entrySet()) ejt.update("UPDATE dataPoints SET setPermission=? WHERE id=?", e.getValue(), e.getKey());
    // Goodbye permission tables.
    scripts.put(DatabaseType.DERBY.name(), dropScript);
    scripts.put(DatabaseType.MYSQL.name(), dropScript);
    scripts.put(DatabaseType.MSSQL.name(), dropScript);
    scripts.put(DatabaseType.H2.name(), dropScript);
    runScript(scripts);
}

Example 50 with RowCallbackHandler

use of org.springframework.jdbc.core.RowCallbackHandler in project ma-core-public by infiniteautomation.

the class Upgrade29 method upgradeEventHandlers.

private void upgradeEventHandlers(OutputStream out) {
    // Add permission id columns
    Map<String, String[]> scripts = new HashMap<>();
    scripts.put(DatabaseType.MYSQL.name(), eventHandlersPermissionMySQL);
    scripts.put(DatabaseType.H2.name(), eventHandlersPermissionH2);
    scripts.put(DatabaseType.MSSQL.name(), eventHandlersPermissionMSSQL);
    scripts.put(DatabaseType.POSTGRES.name(), eventHandlersPermissionMySQL);
    runScript(scripts, out);
    // set permission to superadmin
    Integer readId = getOrCreatePermission(MangoPermission.superadminOnly()).getId();
    Integer editId = getOrCreatePermission(MangoPermission.superadminOnly()).getId();
    ejt.update("UPDATE eventHandlers SET readPermissionId=?, editPermissionId=?", readId, editId);
    // Restrict to NOT NULL
    scripts = new HashMap<>();
    scripts.put(DatabaseType.MYSQL.name(), eventHandlersPermissionNotNullMySQL);
    scripts.put(DatabaseType.H2.name(), eventHandlersPermissionNotNull);
    scripts.put(DatabaseType.MSSQL.name(), eventHandlersPermissionNotNull);
    scripts.put(DatabaseType.POSTGRES.name(), eventHandlersPermissionNotNull);
    runScript(scripts, out);
    // Upgrade Email Event Handlers to fix the script permission serialization, we don't need to clean the
    // recipient list here as we aren't going to use it.
    this.ejt.query("SELECT eh.id, eh.data FROM eventHandlers eh WHERE eh.eventHandlerType=?", new Object[] { EmailEventHandlerDefinition.TYPE_NAME }, new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            int id = rs.getInt(1);
            EmailEventHandlerVO vo = (EmailEventHandlerVO) SerializationHelper.readObjectInContext(rs.getBinaryStream(2));
            Set<String> legacyScriptRoles = vo.getLegacyScriptRoles();
            if (legacyScriptRoles != null) {
                Set<Role> roles = new HashSet<>();
                for (String r : legacyScriptRoles) {
                    roles.add(getOrCreateRole(new Role(Common.NEW_ID, r)));
                }
                vo.setScriptRoles(new ScriptPermissions(roles, vo.getLegacyPermissionHolderName()));
            } else {
                // Must be a ScriptPermission that might need to be upgraded
                ScriptPermissions permission = vo.getScriptRoles();
                if (permission == null) {
                    vo.setScriptRoles(new ScriptPermissions());
                } else if (permission.getLegacyScriptRoles() != null) {
                    Set<Role> roles = new HashSet<>();
                    for (String r : permission.getLegacyScriptRoles()) {
                        roles.add(getOrCreateRole(new Role(Common.NEW_ID, r)));
                    }
                    ScriptPermissions upgraded = new ScriptPermissions(roles, permission.getPermissionHolderName());
                    vo.setScriptRoles(upgraded);
                }
            }
            ejt.update("UPDATE eventHandlers SET data=? where id=?", SerializationHelper.writeObjectToArray(vo), id);
        }
    });
    // Upgrade Set Point Event Handlers
    this.ejt.query("SELECT eh.id, eh.data FROM eventHandlers eh WHERE eh.eventHandlerType=?", new Object[] { SetPointEventHandlerDefinition.TYPE_NAME }, new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            int id = rs.getInt(1);
            SetPointEventHandlerVO vo = (SetPointEventHandlerVO) SerializationHelper.readObjectInContext(rs.getBinaryStream(2));
            Set<String> legacyScriptRoles = vo.getLegacyScriptRoles();
            if (legacyScriptRoles != null) {
                Set<Role> roles = new HashSet<>();
                for (String r : legacyScriptRoles) {
                    roles.add(getOrCreateRole(new Role(Common.NEW_ID, r)));
                }
                vo.setScriptRoles(new ScriptPermissions(roles, vo.getLegacyPermissionHolderName()));
            } else {
                // Must be a ScriptPermission that might need to be upgraded
                ScriptPermissions permission = vo.getScriptRoles();
                if (permission == null) {
                    vo.setScriptRoles(new ScriptPermissions());
                } else if (permission.getLegacyScriptRoles() != null) {
                    Set<Role> roles = new HashSet<>();
                    for (String r : permission.getLegacyScriptRoles()) {
                        roles.add(getOrCreateRole(new Role(Common.NEW_ID, r)));
                    }
                    ScriptPermissions upgraded = new ScriptPermissions(roles, permission.getPermissionHolderName());
                    vo.setScriptRoles(upgraded);
                }
            }
            ejt.update("UPDATE eventHandlers SET data=? where id=?", SerializationHelper.writeObjectToArray(vo), id);
        }
    });
}