Refine search
private boolean addLock(Handle handle, String entryId, LockType lock) throws JsonProcessingException { final String statement = StringUtils.format( "INSERT INTO %1$s (%2$s_id, lock_payload) VALUES (:entryId, :payload)", lockTable, entryTypeName ); return handle.createStatement(statement) .bind("entryId", entryId) .bind("payload", jsonMapper.writeValueAsBytes(lock)) .execute() == 1; }
@Override public Void withHandle(Handle handle) { handle.createStatement(statement) .bind("id", segmentId) .bind("dataSource", dataSource) .bind("created_date", createdDate) .bind("start", start) .bind("end", end) .bind("partitioned", partitioned) .bind("version", version) .bind("used", used) .bind("payload", payload) .execute(); return null; } }
private boolean removeSegmentFromTable(String segmentId) { final int removed = connector.getDBI().withHandle( handle -> handle .createStatement(StringUtils.format("UPDATE %s SET used=false WHERE id = :segmentID", getSegmentsTable())) .bind("segmentID", segmentId) .execute() ); return removed > 0; }
.createQuery( StringUtils.format( "SELECT id from %s where datasource=:dataSource", .bind("dataSource", defaultDatasourceName) .list(); handle.createStatement( StringUtils.format( "INSERT INTO %s (id, dataSource, version, payload) VALUES (:id, :dataSource, :version, :payload)", .bind("id", StringUtils.format("%s_%s", defaultDatasourceName, version)) .bind("dataSource", defaultDatasourceName) .bind("version", version) .bind("payload", jsonMapper.writeValueAsBytes(defaultRules)) .execute();
.createQuery( StringUtils.format("SELECT COUNT(*) FROM %1$s WHERE %2$s = :key", tableName, keyColumn) .bind("key", key) .map(IntegerMapper.FIRST) .first(); if (count == 0) { handle.createStatement( StringUtils.format( "INSERT INTO %1$s (%2$s, %3$s) VALUES (:key, :value)", .bind("key", key) .bind("value", value) .execute(); } else { handle.createStatement( StringUtils.format( "UPDATE %1$s SET %3$s=:value WHERE %2$s=:key", .bind("key", key) .bind("value", value) .execute();
@Override public void delete(String reportType, int project, String name) { try (Handle handle = dbi.open()) { handle.createStatement("DELETE FROM custom_reports WHERE report_type = :reportType AND project_id = :project AND name = :name") .bind("reportType", reportType) .bind("project", project) .bind("name", name).execute(); } }
@Test public void testJustNext() throws Exception { h.createStatement("insert into something (id, name) values (1, 'eric')").execute(); h.createStatement("insert into something (id, name) values (2, 'brian')").execute(); h.createStatement("insert into something (id, name) values (3, 'john')").execute(); ResultIterator<Map<String, Object>> it = h.createQuery("select * from something order by id") .cleanupHandle() .iterator(); it.next(); it.next(); it.next(); }
@Test @Category(JDBIQuarantineTests.class) public void testFancyDynamicTemplateLookups() throws Exception { final DBI dbi = new DBI(h2); Handle h = dbi.open(); try { h.define("template", "create(name) ::= <% create table <name> (id int primary key) %>"); h.createStatement("create") .define("name", "something") .execute(); h.execute("insert into something (id) values (1)"); final List<Integer> ids = h.createQuery("select") .define("template", "select() ::= <% select id from something %>") .map(new IntegerMapper()) .list(); assertThat(ids).containsExactly(1); } finally {
public void revokeApiKeys(int user, int project, String masterKey) { try (Handle handle = dbi.open()) { if (!hasMasterAccess(handle, project, user)) { throw new RakamException("You do not have master key permission", UNAUTHORIZED); } try { handle.createStatement("DELETE FROM web_user_api_key " + "WHERE user_id = :user_id AND project_id = :project AND master_key = :masterKey") .bind("user_id", user) .bind("project", project) .bind("masterKey", masterKey).execute(); } catch (Throwable e) { if (e.getMessage().contains("web_user_api_key_permission")) { List<String> list = handle.createQuery("SELECT web_user.email FROM web_user_api_key_permission permission " + "JOIN web_user ON (web_user.id = permission.user_id) " + "WHERE api_key_id in (SELECT id FROM web_user_api_key WHERE master_key = :masterKey and user_id = :userId and project_id = :project)") .bind("masterKey", masterKey).bind("userId", user).bind("project", project).map(StringMapper.FIRST).list(); if (!list.isEmpty()) { throw new RakamException("Users [" + list.stream().collect(Collectors.joining(", ")) + "] use this key." + " You need to revoke the access of the user in order to be able to delete this key", BAD_REQUEST); } } throw e; } } }
@Override public Object withHandle(final Handle h) throws Exception { h.execute("createSomething"); h.createStatement("insert") .define("table", "something") .bind("id", 1) .bind("name", "Ven") .execute(); final Something s = h.createQuery("findById") .bind("id", 1) .map(new ResultSetMapper<Something>() { @Override public Something map(final int index, final ResultSet r, final StatementContext ctx) throws SQLException { return new Something(r.getInt("id"), r.getString("name")); } }) .first(); assertThat(s).isEqualTo(new Something(1, "Ven")); return null; } });
@Test public void testRegisterOnDBI() throws Exception { dbi.registerArgumentFactory(new NameAF()); Handle h2 = dbi.open(); h2.createStatement("insert into something (id, name) values (:id, :name)") .bind("id", 7) .bind("name", new Name("Brian", "McCallister")) .execute(); String full_name = h.createQuery("select name from something where id = 7").mapTo(String.class).first(); assertThat(full_name, equalTo("Brian McCallister")); h2.close(); }
try (Handle handle = dbi.open()) { try { int id = handle.createStatement("INSERT INTO web_user (email, password, name, created_at, gender, user_locale, google_id, external) VALUES (:email, :password, :name, now(), :gender, :locale, :googleId, :external)") .bind("email", email) .bind("name", name) .bind("gender", gender) .bind("locale", locale) .bind("external", external) .bind("googleId", googleId) .bind("password", scrypt).executeAndReturnGeneratedKeys(IntegerMapper.FIRST).first(); Map<String, Object> existingUser = handle.createQuery("SELECT created_at FROM web_user WHERE lower(email) = lower(:email)").bind("email", email).first(); if (existingUser != null) { if (existingUser.get("created_at") != null) { } else { int id = handle.createStatement("UPDATE web_user SET password = :password, name = :name, created_at = now() WHERE lower(email) = lower(:email)") .bind("email", email) .bind("name", name) .bind("password", scrypt).executeAndReturnGeneratedKeys(IntegerMapper.FIRST).first(); if (id > 0) { webuser = new WebUser(id, email, name, false, Instant.now(), generateIntercomHash(email), ImmutableList.of());
@Test public void testBindBeanFactory() throws Exception { BindBeanFactory factory = new BindBeanFactory(); @SuppressWarnings("unchecked") Binder<BindBean, Object> beanBinder = factory.build(new BindBeanImpl()); final DBI dbi = new DBI(DERBY_HELPER.getDataSource()); final Handle handle = dbi.open(); final Update testStatement = handle.createStatement("does not matter"); TestBean testBean = new TestBean(); beanBinder.bind(testStatement, new BindBeanImpl(), testBean); StatementContext context = testStatement.getContext(); Binding binding = context.getBinding(); assertEquals("LongArgument", binding.forName("ALong").getClass().getSimpleName()); assertEquals("BooleanArgument", binding.forName("ARealBoolean").getClass().getSimpleName()); assertEquals("BooleanArgument", binding.forName("ANullBoolean").getClass().getSimpleName()); assertEquals("StringArgument", binding.forName("AString").getClass().getSimpleName()); assertEquals("ObjectArgument", binding.forName("AFoo").getClass().getSimpleName()); assertEquals("ShortArgument", binding.forName("AShort").getClass().getSimpleName()); handle.close(); }
public List<String> revokeUserAccess(int userId, int project, String email) { try (Handle handle = dbi.open()) { if (!hasMasterAccess(handle, project, userId)) { throw new RakamException("You do not have master key permission", UNAUTHORIZED); } List<Map<String, Object>> list = handle.createQuery("SELECT api_key.id, api_key.master_key FROM web_user_api_key_permission permission " + "JOIN web_user_api_key api_key ON (api_key.id = permission.api_key_id) " + "WHERE project_id = :project AND permission.user_id = " + "(SELECT id FROM web_user WHERE lower(email) = lower(:email))") .bind("project", project) .bind("email", email).list(); if (list.isEmpty()) { throw new RakamException(NOT_FOUND); } handle.createStatement("DELETE FROM web_user_api_key_permission WHERE api_key_id in (" + list.stream().map(a -> a.get("id").toString()).collect(Collectors.joining(", ")) + ")") .execute(); return list.stream().map(e -> e.get("master_key").toString()).collect(Collectors.toList()); } }
@Test public void testMapEnumValues() throws Exception { Handle h = openHandle(); h.createStatement("insert into something (id, name) values (1, 'eric')").execute(); h.createStatement("insert into something (id, name) values (2, 'brian')").execute(); List<SomethingElse> results = h.createQuery("select * from something order by id") .map(SomethingElse.class) .list(); assertEquals(SomethingElse.Name.eric, results.get(0).name); assertEquals(SomethingElse.Name.brian, results.get(1).name); }
@Test public void testRegisterOnHandle() throws Exception { h.registerArgumentFactory(new NameAF()); h.createStatement("insert into something (id, name) values (:id, :name)") .bind("id", 7) .bind("name", new Name("Brian", "McCallister")) .execute(); String full_name = h.createQuery("select name from something where id = 7").mapTo(String.class).first(); assertThat(full_name, equalTo("Brian McCallister")); }
@Test public void testMapToEnum() throws Exception { Handle h = openHandle(); h.createStatement("insert into something (id, name) values (1, 'eric')").execute(); h.createStatement("insert into something (id, name) values (2, 'brian')").execute(); List<SomethingElse.Name> results = h.createQuery("select name from something order by id") .mapTo(SomethingElse.Name.class) .list(); assertEquals(SomethingElse.Name.eric, results.get(0)); assertEquals(SomethingElse.Name.brian, results.get(1)); }
@Test public void testFoo() throws Exception { Handle h = openHandle(); h.setStatementLocator(new StatementLocator() { @Override public String locate(String name, StatementContext ctx) throws Exception { return name.replaceAll("<table>", String.valueOf(ctx.getAttribute("table"))); } }); final int inserted = h.createStatement("insert into <table> (id, name) values (:id, :name)") .bind("id", 7) .bind("name", "Martin") .define("table", "something") .execute(); assertEquals(1, inserted); } }
public Integer saveApiKeys(Handle handle, int user, int projectId, String readKey, String writeKey, String masterKey) { if (!hasMasterAccess(handle, projectId, user)) { throw new RakamException("You do not have master key permission", UNAUTHORIZED); } return handle.createStatement("INSERT INTO web_user_api_key " + "(user_id, project_id, read_key, write_key, master_key) " + "VALUES (:userId, :project, :readKey, :writeKey, :masterKey)") .bind("userId", user) .bind("project", projectId) .bind("readKey", readKey) .bind("writeKey", writeKey) .bind("masterKey", masterKey) .executeAndReturnGeneratedKeys((index, r, ctx) -> r.getInt("id")).first(); }
@Override public Void withHandle(Handle handle) { handle.createStatement(StringUtils.format("DROP TABLE %s", tableName)) .execute(); return null; } }