public static <T> List<T> find_by_sql(String sql){ @SuppressWarnings("unchecked") List<T> list = (List<T>) DB.sql2o.beginTransaction().createQuery(sql).executeAndFetchTable().asList(); return list; }
public static Map<String, Boolean> getCurrentResultsForTest(Connection connection, String test) { String query = "select servers.domain,success from current_tests" + " inner join servers on servers.domain = current_tests.domain" + " where test = :test and listed = 1"; Table table = connection.createQuery(query) .addParameter("test", test) .executeAndFetchTable(); SortedMap<String, Boolean> results = new TreeMap<>(); table.rows().forEach( row -> { String domain = row.getString("domain"); boolean success = row.getInteger("success") == 1; results.put(domain, success); }); return results; }
public static Map<String, HashMap<String, Boolean>> getCurrentResultsByServer(Connection connection) { Map<String, HashMap<String, Boolean>> resultsByServer = new LinkedHashMap<>(); List<String> tests = TestUtils.getTestNames(); Table table = connection.createQuery("select test,servers.domain,success from current_tests" + " inner join servers on servers.domain = current_tests.domain" + " where test in (:tests) and listed = 1" + " order by (select sum(success) from current_tests" + " where domain=servers.domain and test in (:tests))" + "desc, servers.domain asc" ) .addParameter("tests", tests) .executeAndFetchTable(); table.rows().forEach( row -> { String domain = row.getString("domain"); String test = row.getString("test"); boolean success = row.getInteger("success") == 1; resultsByServer.putIfAbsent(domain, new HashMap<>()); resultsByServer.get(domain).put(test, success); } ); return resultsByServer; }
public static List<Result> getHistoricalResultsFor(Connection connection, String domain, int iteration) { Table table = connection.createQuery("select test,success from periodic_tests " + "where domain=:domain and iteration_number = :iteration order by test") .addParameter("domain", domain) .addParameter("iteration", iteration) .executeAndFetchTable(); ArrayList<Result> r = table.rows().stream() .map(row -> new Result( TestUtils.getTestFrom(row.getString("test")), row.getInteger("success") == 1 )) .collect(Collectors.toCollection(ArrayList::new)); return r; }
public static Map<String, HashMap<String, Boolean>> getCurrentResultsByTest(Connection connection) { HashMap<String, HashMap<String, Boolean>> resultsByTests = new HashMap<>(); List<String> tests = TestUtils.getAllTestNames(); Table table = connection.createQuery("select test,servers.domain,success from current_tests" + " inner join servers on servers.domain = current_tests.domain" + " where test in (:tests) and listed=1") .addParameter("tests", tests) .executeAndFetchTable(); table.rows().forEach( row -> { String domain = row.getString("domain"); String test = row.getString("test"); boolean success = row.getInteger("success") == 1; resultsByTests.putIfAbsent(test, new HashMap<>()); resultsByTests.get(test).put(domain, success); } ); return resultsByTests; }
public static List<Result> getCurrentResultsForServer(Connection connection, String domain) { ArrayList<Result> results = new ArrayList<>(); String query = "select test,success from current_tests" + " where domain = :domain order by test"; Table table = connection.createQuery(query) .addParameter("domain", domain) .executeAndFetchTable(); table.rows().forEach( row -> { String test = row.getString("test"); boolean success = row.getInteger("success") == 1; ComplianceTest complianceTest = TestUtils.getTestFrom(test); Result result = new Result(complianceTest, success); results.add(result); } ); return results; }
public static Map<String, HashMap<String, Boolean>> getHistoricalTableFor(Connection connection, int iterationNumber) { Map<String, HashMap<String, Boolean>> resultsByServer = new LinkedHashMap<>(); List<String> tests = TestUtils.getTestNames(); Table table = connection.createQuery("select test,servers.domain,success from periodic_tests" + " inner join servers on servers.domain = periodic_tests.domain" + " where test in (:tests) and" + " listed = 1 and" + " iteration_number = :iteration_number" + " order by (select sum(success) from periodic_tests" + " where domain=servers.domain and test in (:tests) and iteration_number=:iteration_number " + ") desc, servers.domain asc" ) .addParameter("iteration_number", iterationNumber) .addParameter("tests", tests) .executeAndFetchTable(); table.rows().forEach( row -> { String domain = row.getString("domain"); String test = row.getString("test"); boolean success = row.getInteger("success") == 1; resultsByServer.putIfAbsent(domain, new HashMap<>()); resultsByServer.get(domain).put(test, success); } ); return resultsByServer; }
.addParameter("domain", domain) .addParameter("test", test) .executeAndFetchTable(); addHistoricalPoints(resultChanges, test, table.rows()); .addParameter("domain", domain) .addParameter("tests", tests) .executeAndFetchTable() .rows() .get(0);
.addParameter("domain", domain) .addParameter("test", test) .executeAndFetchTable(); addHistoricalPoints(resultChanges, domain, table.rows()); .addParameter("test", test) .addParameter("domains", domains) .executeAndFetchTable() .rows() .get(0);
public static List<Map<String, Object>> getMapList(String sql, Map<String, Object> params){ Connection con = sql2o.open(); Query query = con.createQuery(sql); executeQuery(query, params); List<Map<String, Object>> t = query.executeAndFetchTable().asList(); con.close(); return t; }
/** * Querying a List<Map> * * @param sql sql statement * @param params params * @return List<Map> */ public List<Map<String, Object>> queryListMap(String sql, Object[] params) { Connection conn = getConn(); try { return conn.createQuery(sql) .withParams(params) .setAutoDeriveColumnNames(true) .throwOnMappingFailure(false) .executeAndFetchTable() .asList(); } finally { this.closeConn(conn); this.clean(null); } }