public List<IndexEntry> findIndexesForCache(Connection con, CacheEntry cache) { return con.createQuery("select id, indexId, crc, revision from `index` where cache = :cache") .addParameter("cache", cache.getId()) .executeAndFetch(IndexEntry.class); }
public void associateFileToArchive(Connection con, ArchiveEntry archive, int fileId, int nameHash) { if (associateFile == null) { associateFile = con.createQuery("insert into file (archive, fileId, nameHash) values (:archive, :fileId, :nameHash)"); } associateFile .addParameter("archive", archive.getId()) .addParameter("fileId", fileId) .addParameter("nameHash", nameHash) .executeUpdate(); } }
public ArchiveEntry findArchive(Connection con, IndexEntry index, int archiveId, int nameHash, int crc, int revision) { if (findArchive == null) { findArchive = con.createQuery("select distinct archive.id, archive.archiveId, archive.nameHash," + " archive.crc, archive.revision, archive.hash from archive " + " join index_archive on index_archive.archive = archive.id" + " join `index` on index.id = index_archive.index" + " where archive.archiveId = :archiveId" + " and archive.nameHash = :nameHash" + " and archive.crc = :crc" + " and archive.revision = :revision" + " and index.indexId = :indexId"); } ArchiveEntry entry = findArchive .addParameter("archiveId", archiveId) .addParameter("nameHash", nameHash) .addParameter("crc", crc) .addParameter("revision", revision) .addParameter("indexId", index.getIndexId()) .executeAndFetchFirst(ArchiveEntry.class); return entry; }
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 List<Credential> getCredentials(Connection connection) { String query = "select domain,jid,password from credentials"; return connection.createQuery(query).executeAndFetch(Credential.class); }
public static boolean removeServer(Connection connection, Server server) { String query = "delete from servers where domain=:domain"; connection.createQuery(query) .bind(server) .executeUpdate(); return true; }
public Pizza save(Pizza pizza) { try (Connection con = sql2o.beginTransaction()) { long pizzaId = (long) con.createQuery("INSERT INTO pizza (base_id, name, price) VALUES (:baseId, :name, :price)", true) .addParameter("baseId", pizza.getBase().getId()) .addParameter("name", pizza.getName()) .addParameter("price", pizza.getPrice()) .executeUpdate() .getKey(); Query queryForPt = con.createQuery("INSERT INTO pizza_toppings (pizza_id, toppings_id) VALUES (:pizzaId, :toppingsId)"); pizza.getToppings().forEach(t -> queryForPt .addParameter("pizzaId", pizzaId) .addParameter("toppingsId", t.getId()) .addToBatch()); queryForPt.executeBatch(); con.commit(); } return pizza; }
public static Subscriber removeSubscriber(Connection connection, String unsubscribeCode) { Subscriber subscriber = connection.createQuery( "select email,domain,unsubscribeCode from subscribers " + "where unsubscribeCode=:unsubscribeCode") .addParameter("unsubscribeCode", unsubscribeCode) .executeAndFetchFirst(Subscriber.class); connection.createQuery("delete from subscribers " + "where unsubscribeCode=:unsubscribeCode") .addParameter("unsubscribeCode", unsubscribeCode) .executeUpdate(); return subscriber; }
public <T> T executeAndFetchFirst(Class<T> returnType){ return executeAndFetchFirst(newResultSetHandlerFactory(returnType)); }
public static void delete(int id ,Class<?> c){ String name = Util.getclassName(c).toLowerCase(); String sql = "delete from "+ name + " where id = '" + id+"'"; Logger logger = LogManager.getLogger(ActiveRecordBase.class); logger.info(Constant.LOG_AC_NAME+" execute sql " + sql); DB.sql2o.beginTransaction().createQuery(sql).executeUpdate().commit(); } public static void delete_by_sql(String sql){
public static Server getServer(Connection connection, String domain) { String query = "select domain,software_name,software_version,listed from servers where domain = :domain"; return connection.createQuery(query) .addColumnMapping("software_name", "softwareName") .addColumnMapping("software_version", "softwareVersion") .addParameter("domain", domain) .executeAndFetchFirst(Server.class); }
/** * @param connection * @param domain * @param results * @param timestamp * @return */ public static boolean addCurrentResults(Connection connection, String domain, List<Result> results, Instant timestamp) { String queryText = "insert into current_tests(domain,test,success,timestamp) " + "values(:domain,:test,:success,:timestamp)"; Query query = connection.createQuery(queryText); results.forEach( result -> query .addParameter("test", result.getTest().short_name()) .addParameter("success", result.isSuccess()) .addParameter("domain", domain) .addParameter("timestamp", timestamp) .addToBatch() ); query.executeBatch(); return true; }
public <T> List<T> executeAndFetch(Class<T> returnType){ return executeAndFetch(newResultSetHandlerFactory(returnType)); }
public <T> T query(Class<T> type, String sql, Object... args) { int pos = 1; while (sql.contains(SQL_QM)) { sql = sql.replaceFirst("\\?", ":p" + (pos++)); } try (Connection con = getSql2o().open()) { log.debug(EXECUTE_SQL_PREFIX + " => {}", sql); log.debug(PARAMETER_PREFIX + " => {}", Arrays.toString(args)); this.cleanParam(); Query query = con.createQuery(sql).withParams(args).throwOnMappingFailure(false); QueryMeta queryMeta = SqlBuilder.buildFindAllSql(this, null); if (queryMeta.hasColumnMapping()) { queryMeta.getColumnMapping().forEach(query::addColumnMapping); } return query.executeAndFetchFirst(type); } }
public <T> List<T> executeScalarList(final Class<T> returnType){ return executeAndFetch(newScalarResultSetHandler(returnType)); }
/** * Querying a model * * @param type model type * @param sql sql statement * @param params params * @param <S> * @return S */ public <S> S queryOne(Class<S> type, String sql, Object[] params) { Connection conn = getConn(); try { Query query = conn.createQuery(sql) .withParams(params) .setAutoDeriveColumnNames(true) .throwOnMappingFailure(false); return ifReturn(AnimaUtils.isBasicType(type), () -> query.executeScalar(type), () -> query.executeAndFetchFirst(type)); } finally { this.closeConn(conn); this.clean(null); } }
public Query withParams(Object... paramValues){ int i=0; for (Object paramValue : paramValues) { addParameter("p" + (++i), paramValue); } return this; }
public static List<Server> getPublicServers(Connection connection) { String query = "select domain,software_name,software_version,listed from servers where listed=1"; return connection.createQuery(query) .addColumnMapping("software_name", "softwareName") .addColumnMapping("software_version", "softwareVersion") .executeAndFetch(Server.class); }
public static boolean isSubscribed(Connection connection, String email, String domain) { boolean subscribed = connection.createQuery( "select count(email) from subscribers " + "where domain = :domain and email = :email") .addParameter("email", email) .addParameter("domain", domain) .executeScalar(Integer.class) > 0; return subscribed; }
public <T> List<T> findAll(Class<T> type, Supplier<ConditionEnum>... conditions) { QueryMeta queryMeta = SqlBuilder.buildFindAllSql(this, conditions); try (Connection con = getSql2o().open()) { log.debug(EXECUTE_SQL_PREFIX + " => {}", queryMeta.getSql()); log.debug(PARAMETER_PREFIX + " => {}", Arrays.toString(queryMeta.getParams())); this.cleanParam(); Query query = con.createQuery(queryMeta.getSql()).withParams(queryMeta.getParams()).throwOnMappingFailure(false); if (queryMeta.hasColumnMapping()) { queryMeta.getColumnMapping().forEach(query::addColumnMapping); } return query.executeAndFetch(type); } }