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 <T> Query addParameter(String name, Class<T> parameterClass, T value){ //TODO: must cover most of types: BigDecimal,Boolean,SmallInt,Double,Float,byte[] if(InputStream.class.isAssignableFrom(parameterClass)) return addParameter(name, (InputStream)value); if(Integer.class==parameterClass) return addParameter(name, (Integer)value); if(Long.class==parameterClass) return addParameter(name, (Long)value); if(String.class==parameterClass) return addParameter(name, (String)value); if(Timestamp.class==parameterClass) return addParameter(name, (Timestamp)value); if(Time.class==parameterClass) return addParameter(name, (Time)value); if(parameterClass.isArray() // byte[] is used for blob already && byte[].class != parameterClass) { return addParameter(name, toObjectArray(value)); } if(Collection.class.isAssignableFrom(parameterClass)) { return addParameter(name, (Collection) value); } final Object convertedValue = convertParameter(value); addParameterInternal(name, new ParameterSetter() { public void setParameter(int paramIdx, PreparedStatement statement) throws SQLException { getConnection().getSql2o().getQuirks().setParameter(statement, paramIdx, convertedValue); } }); return this; }
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 void associateArchiveToIndex(Connection con, ArchiveEntry archive, IndexEntry index) { if (associateArchive == null) { associateArchive = con.createQuery("insert into index_archive (`index`, archive) values (:index, :archive)"); } associateArchive .addParameter("index", index.getId()) .addParameter("archive", archive.getId()) .executeUpdate(); }
public IndexEntry createIndex(Connection con, CacheEntry cache, int indexId, int crc, int revision) { int id = con.createQuery("insert into `index` (cache, indexId, crc, revision) values (:cache, :indexId, :crc, :revision)") .addParameter("cache", cache.getId()) .addParameter("indexId", indexId) .addParameter("crc", crc) .addParameter("revision", revision) .executeUpdate() .getKey(int.class); IndexEntry entry = new IndexEntry(); entry.setId(id); entry.setIndexId(indexId); entry.setCrc(crc); entry.setRevision(revision); return entry; }
public ArchiveEntry createArchive(Connection con, IndexEntry index, int archiveId, int nameHash, int crc, int revision, byte[] hash) { if (insertArchive == null) { insertArchive = con.createQuery("insert into archive (archiveId, nameHash, crc, revision, hash) values " + "(:archiveId, :nameHash, :crc, :revision, :hash)"); } int id = insertArchive .addParameter("archiveId", archiveId) .addParameter("nameHash", nameHash) .addParameter("crc", crc) .addParameter("revision", revision) .addParameter("hash", hash) .executeUpdate() .getKey(int.class); ArchiveEntry entry = new ArchiveEntry(); entry.setId(id); entry.setArchiveId(archiveId); entry.setNameHash(nameHash); entry.setCrc(crc); entry.setRevision(revision); entry.setHash(hash); return entry; }
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 ResultSetIterable<ArchiveEntry> findArchivesForIndex(Connection con, IndexEntry indexEntry) { return con.createQuery("select archive.id, archive.archiveId, archive.nameHash," + " archive.crc, archive.revision, archive.hash from index_archive " + "join archive on index_archive.archive = archive.id " + "where index_archive.index = :id") .addParameter("id", indexEntry.getId()) .executeAndFetchLazy(ArchiveEntry.class); }
public CacheEntry createCache(Connection con, int revision, Instant date) { int cacheId = con.createQuery("insert into cache (revision, date) values (:revision, :date)") .addParameter("revision", revision) .addParameter("date", date) .executeUpdate() .getKey(int.class); CacheEntry entry = new CacheEntry(); entry.setId(cacheId); entry.setRevision(revision); entry.setDate(date); return entry; }
public Query withParams(Object... paramValues){ int i=0; for (Object paramValue : paramValues) { addParameter("p" + (++i), paramValue); } return this; }
@SuppressWarnings("unchecked") public Query addParameter(String name, Object value) { return value == null ? addParameter(name, Object.class, null) : addParameter(name, (Class<Object>) value.getClass(), value); }
/** * Set an array parameter.<br> * See {@link #addParameter(String, Object...)} for details */ public Query addParameter(String name, final Collection<?> values) { if(values == null) { throw new NullPointerException("Array parameter cannot be null"); } return addParameter(name, values.toArray()); }
public Query bind(final Object pojo) { Class clazz = pojo.getClass(); Map<String, PojoIntrospector.ReadableProperty> propertyMap = PojoIntrospector.readableProperties(clazz); for (PojoIntrospector.ReadableProperty property : propertyMap.values()) { try { if( this.getParamNameToIdxMap().containsKey(property.name)) { @SuppressWarnings("unchecked") final Class<Object> type = (Class<Object>) property.type; this.addParameter(property.name, type, property.get(pojo)); } } catch(IllegalArgumentException ex) { logger.debug("Ignoring Illegal Arguments", ex); } catch(IllegalAccessException | InvocationTargetException ex) { throw new RuntimeException(ex); } } return this; }
@SuppressWarnings("unchecked") public Query addParameter(String name, Object value) { return value == null ? addParameter(name, Object.class, null) : addParameter(name, (Class<Object>) value.getClass(), value); }
/** * Set an array parameter.<br> * See {@link #addParameter(String, Object...)} for details */ public Query addParameter(String name, final Collection<?> values) { if(values == null) { throw new NullPointerException("Array parameter cannot be null"); } return addParameter(name, values.toArray()); }
public static List<Subscriber> getSubscribersFor(Connection connection, String domain) { List<Subscriber> subscribers = connection.createQuery( "select email,domain,unsubscribeCode from subscribers where domain=:domain") .addParameter("domain", domain) .executeAndFetch(Subscriber.class); return subscribers; }
@Override public void updatePost(Post post) { try (Connection conn = sql2o.open()) { conn.createQuery("update posts set title=:title, content=:content where post_uuid=:post_uuid") .addParameter("post_uuid", post.getPost_uuid()) .addParameter("title", post.getTitle()) .addParameter("content", post.getContent()) .executeUpdate(); } }
@Override public boolean existPost(UUID post) { try (Connection conn = sql2o.open()) { List<Post> posts = conn.createQuery("select * from posts where post_uuid=:post") .addParameter("post", post) .executeAndFetch(Post.class); return posts.size() > 0; } }
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); }
@Override public Todo findById(Long id) { return sql2oHandler.withSql2o(sql2o -> sql2o.withConnection((connection, argument) -> { return connection.createQuery("SELECT * FROM todos WHERE id = :id") .addParameter("id", id) .executeAndFetchFirst(Todo.class); })); }