public List<String> findAlarmIds(String tenantId, Map<String, String> dimensions) { final String FIND_ALARM_IDS_SQL = "select distinct a.id " + "from alarm as a " + "join alarm_definition as ad on a.alarm_definition_id = ad.id " + "%s " + "where ad.tenant_id = :tenantId and ad.deleted_at is NULL " + "order by ad.created_at"; List<String> alarmIdList; try (Handle h = this.mysql.open()) { final String sql = String.format(FIND_ALARM_IDS_SQL, buildJoinClauseFor(dimensions)); Query<Map<String, Object>> query = h.createQuery(sql).bind("tenantId", tenantId); logger.debug("mysql sql: {}", sql); DimensionQueries.bindDimensionsToQuery(query, dimensions); alarmIdList = query.map(StringMapper.FIRST).list(); } return alarmIdList; }
@Before public void setUp() throws InterruptedException { jdbcDataFetcher = new JdbcDataFetcher( derbyConnectorRule.getMetadataConnectorConfig(), "tableName", "keyColumn", "valueColumn", 100); handle = derbyConnectorRule.getConnector().getDBI().open(); Assert.assertEquals( 0, handle .createStatement( String.format( "CREATE TABLE %s (%s VARCHAR(64), %s VARCHAR(64))", tableName, keyColumn, valueColumn)) .setQueryTimeout(1) .execute()); handle .createStatement(String.format("TRUNCATE TABLE %s", tableName)) .setQueryTimeout(1) .execute(); for (Map.Entry<String, String> entry : lookupMap.entrySet()) { insertValues(entry.getKey(), entry.getValue(), handle); } handle.commit(); }
private void verifyAccountEmailAuditAndHistoryCount( final UUID accountId, final int expectedCount) { final Handle handle = dbi.open(); // verify audit StringBuilder sb = new StringBuilder(); sb.append("select * from audit_log a "); sb.append("inner join account_email_history aeh on a.record_id = aeh.history_record_id "); sb.append("where a.table_name = 'account_email_history' "); sb.append(String.format("and aeh.account_id='%s'", accountId.toString())); List<Map<String, Object>> result = handle.select(sb.toString()); assertEquals(result.size(), expectedCount); // ***** NOT IDEAL // ... but this works after the email record has been deleted; will likely fail when multiple // emails exist for the same account // verify history table sb = new StringBuilder(); sb.append("select * from account_email_history aeh "); sb.append(String.format("where aeh.account_id='%s'", accountId.toString())); result = handle.select(sb.toString()); assertEquals(result.size(), expectedCount); handle.close(); }
public H2QueryRunner() { handle = DBI.open("jdbc:h2:mem:test" + System.nanoTime()); TpchMetadata tpchMetadata = new TpchMetadata(""); handle.execute( "CREATE TABLE orders (\n" + " orderkey BIGINT PRIMARY KEY,\n" + " custkey BIGINT NOT NULL,\n" + " orderstatus CHAR(1) NOT NULL,\n" + " totalprice DOUBLE NOT NULL,\n" + " orderdate DATE NOT NULL,\n" + " orderpriority CHAR(15) NOT NULL,\n" + " clerk CHAR(15) NOT NULL,\n" + " shippriority BIGINT NOT NULL,\n" + " comment VARCHAR(79) NOT NULL\n" + ")"); handle.execute("CREATE INDEX custkey_index ON orders (custkey)"); TpchTableHandle ordersHandle = tpchMetadata.getTableHandle( null, new SchemaTableName(TINY_SCHEMA_NAME, ORDERS.getTableName())); insertRows( tpchMetadata.getTableMetadata(ordersHandle), handle, createTpchRecordSet(ORDERS, ordersHandle.getScaleFactor())); handle.execute( "CREATE TABLE lineitem (\n" + " orderkey BIGINT,\n" + " partkey BIGINT NOT NULL,\n" + " suppkey BIGINT NOT NULL,\n" + " linenumber BIGINT,\n" + " quantity BIGINT NOT NULL,\n" + " extendedprice DOUBLE NOT NULL,\n" + " discount DOUBLE NOT NULL,\n" + " tax DOUBLE NOT NULL,\n" + " returnflag CHAR(1) NOT NULL,\n" + " linestatus CHAR(1) NOT NULL,\n" + " shipdate DATE NOT NULL,\n" + " commitdate DATE NOT NULL,\n" + " receiptdate DATE NOT NULL,\n" + " shipinstruct VARCHAR(25) NOT NULL,\n" + " shipmode VARCHAR(10) NOT NULL,\n" + " comment VARCHAR(44) NOT NULL,\n" + " PRIMARY KEY (orderkey, linenumber)" + ")"); TpchTableHandle lineItemHandle = tpchMetadata.getTableHandle( null, new SchemaTableName(TINY_SCHEMA_NAME, LINE_ITEM.getTableName())); insertRows( tpchMetadata.getTableMetadata(lineItemHandle), handle, createTpchRecordSet(LINE_ITEM, lineItemHandle.getScaleFactor())); }
@Override public synchronized void unlock() { if (handle != null) { handle .createQuery("select release_lock(:name)") .bind("name", name) .map(IntegerMapper.FIRST) .first(); handle.close(); handle = null; } }
@Test public void testTxActuallyCommits() throws Exception { Handle h2 = dbi.open(); Dao one = handle.attach(Dao.class); Dao two = h2.attach(Dao.class); // insert in @Transaction method Something inserted = one.insertAndFetch(1, "Brian"); // fetch from another connection Something fetched = two.findById(1); assertThat(fetched, equalTo(inserted)); }
@Override public <ReturnType> ReturnType inTransaction( final Handle handle, final TransactionIsolationLevel level, final TransactionCallback<ReturnType> callback) { final TransactionIsolationLevel initial = handle.getTransactionIsolationLevel(); try { handle.setTransactionIsolation(level); return inTransaction(handle, callback); } finally { handle.setTransactionIsolation(initial); } }
public boolean insert(Template template) { if (template == null) { return false; } Handle handle = dbi.open(); try { TemplateDao db = handle.attach(TemplateDao.class); return db.insertBean(template) > 0; } finally { handle.close(); } }
private void insertValues(final String key, final String val, Handle handle) { final String query; handle .createStatement(String.format("DELETE FROM %s WHERE %s='%s'", tableName, keyColumn, key)) .setQueryTimeout(1) .execute(); query = String.format( "INSERT INTO %s (%s, %s) VALUES ('%s', '%s')", tableName, keyColumn, valueColumn, key, val); Assert.assertEquals(1, handle.createStatement(query).setQueryTimeout(1).execute()); handle.commit(); }
/** * @param entitySqlDaoTransactionWrapper transaction to execute * @param <ReturnType> object type to return from the transaction * @return result from the transaction fo type ReturnType */ public <ReturnType> ReturnType execute( final EntitySqlDaoTransactionWrapper<ReturnType> entitySqlDaoTransactionWrapper) { final Handle handle = dbi.open(); try { final EntitySqlDao<EntityModelDao<Entity>, Entity> entitySqlDao = handle.attach(InitialEntitySqlDao.class); return entitySqlDao.inTransaction( TransactionIsolationLevel.READ_COMMITTED, new JdbiTransaction<ReturnType, EntityModelDao<Entity>, Entity>( handle, entitySqlDaoTransactionWrapper)); } finally { handle.close(); } }
@Test public void testSimpleCreate() throws Exception { Foo foo = handle.attach(Foo.class); foo.insert(1, "Stephane"); Something s = foo.createBar().findById(1); assertThat(s, equalTo(new Something(1, "Stephane"))); }
@AfterMethod(alwaysRun = true) public void tearDown() throws Exception { if (dummyHandle != null) { dummyHandle.close(); } deleteRecursively(temporary); }
/** Called to test if a handle is in a transaction */ public boolean isInTransaction(Handle handle) { try { return !handle.getConnection().getAutoCommit(); } catch (SQLException e) { throw new TransactionException("Failed to check status of transaction", e); } }
@Test public void testTransmogrifiable() throws Exception { Hobbsian h = handle.attach(Hobbsian.class); h.insert(2, "Cora"); Something s = h.become(TransactionStuff.class).byId(2); assertThat(s, equalTo(new Something(2, "Cora"))); }
@Test public void testNestedTransactions() throws Exception { Dao dao = handle.attach(Dao.class); Something s = dao.insertAndFetchWithNestedTransaction(1, "Ian"); assertThat(s, equalTo(new Something(1, "Ian"))); }
@Before public void setUp() throws Exception { dbi = new DBI("jdbc:h2:mem:" + UUID.randomUUID()); dbi.registerMapper(new SomethingMapper()); handle = dbi.open(); handle.execute("create table something (id int primary key, name varchar(100))"); }
@Override public boolean tableExists(Handle handle, String tableName) { return !handle .createQuery("select * from SYS.SYSTABLES where tablename = :tableName") .bind("tableName", tableName.toUpperCase()) .list() .isEmpty(); }
public void setUp() throws Exception { JdbcDataSource ds = new JdbcDataSource(); ds.setURL("jdbc:h2:mem:test;MVCC=TRUE"); dbi = new DBI(ds); handle = dbi.open(); handle.execute("create table something (id int primary key, name varchar(100))"); }
@Before public void setUp() throws Exception { UUID uuid = UUID.randomUUID(); dbi = new DBI("jdbc:h2:mem:" + uuid); handle = dbi.open(); handle.execute("create table something (id int primary key, name varchar(100))"); }
@Override public boolean tableExists(final Handle handle, final String tableName) { return !handle .createQuery("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = :tableName") .bind("tableName", tableName) .map(StringMapper.FIRST) .list() .isEmpty(); }
public void testJustJdbiTransactions() throws Exception { Handle h1 = dbi.open(); Handle h2 = dbi.open(); h1.execute("insert into something (id, name) values (8, 'Mike')"); h1.begin(); h1.execute("update something set name = 'Miker' where id = 8"); assertEquals( "Mike", h2.createQuery("select name from something where id = 8").map(StringMapper.FIRST).first()); h1.commit(); h1.close(); h2.close(); }
@BeforeMethod public void setup() throws Exception { H2EmbeddedDataSourceConfig dataSourceConfig = new H2EmbeddedDataSourceConfig().setFilename("mem:"); DataSource dataSource = new H2EmbeddedDataSource(dataSourceConfig); DBI h2Dbi = new DBI(dataSource); handle = h2Dbi.open(); dao = handle.attach(ShardManagerDao.class); ShardManagerDao.Utils.createShardTablesWithRetry(dao); }
@Test public void testTxFail() throws Exception { Dao dao = handle.attach(Dao.class); try { dao.failed(1, "Ian"); fail("should have raised exception"); } catch (TransactionFailedException e) { assertThat(e.getCause().getMessage(), equalTo("woof")); } assertThat(dao.findById(1), nullValue()); }
@Override public Long withHandle(final Handle handle) throws Exception { return (Long) handle .select("select count(distinct record_id) count from bus_events") .get(0) .get("count") + (Long) handle .select( "select count(distinct record_id) count from notifications where effective_date < ?", clock.getUTCNow().toDate()) .get(0) .get("count") + (Long) handle .select( "select count(distinct record_id) count from notifications where processing_state = 'IN_PROCESSING'") .get(0) .get("count"); }
@Override public <T extends Schedule> void updateSchedules( int projId, List<T> schedules, ScheduleUpdateAction<T> func) throws ResourceConflictException { Map<String, Integer> oldScheduleNames = idNameListToHashMap(dao.getScheduleNames(projId)); // Concurrent call of updateSchedules doesn't happen because having // ProjectControlStore means that the project is locked. // // However, ScheduleExecutor modifies schedules without locking the // project. Instead, ScheduleExecutor locks schedules. To avoid // concurrent update of schedules, here needs to lock schedules // before UPDATE. for (T schedule : schedules) { Integer matchedSchedId = oldScheduleNames.get(schedule.getWorkflowName()); if (matchedSchedId != null) { // found the same name. lock it and update ScheduleStatus status = dao.lockScheduleById(matchedSchedId); if (status != null) { ScheduleTime newSchedule = func.apply(status, schedule); dao.updateScheduleById( matchedSchedId, schedule.getWorkflowDefinitionId(), newSchedule.getRunTime().getEpochSecond(), newSchedule.getTime().getEpochSecond()); oldScheduleNames.remove(schedule.getWorkflowName()); } } else { // not found this name. inserting a new entry. catchConflict( () -> dao.insertSchedule( projId, schedule.getWorkflowDefinitionId(), schedule.getNextRunTime().getEpochSecond(), schedule.getNextScheduleTime().getEpochSecond()), "workflow_definition_id=%d", schedule.getWorkflowDefinitionId()); } } // delete unused schedules if (!oldScheduleNames.isEmpty()) { // those names don exist any more. handle .createStatement( "delete from schedules" + " where id " + inLargeIdListExpression(oldScheduleNames.values())) .execute(); } }
private boolean _lock(long duration, TimeUnit unit) { if (handle == null) { handle = dbi.open(); int got_lock = handle .createQuery("select get_lock(:name, :time)") .bind("name", name) .bind("time", unit.toSeconds(duration)) .map(IntegerMapper.FIRST) .first(); if (got_lock == 1) { return true; } else { handle.close(); handle = null; return false; } } else { // we already have the lock! return true; } }
public MaterializedResult execute( Session session, @Language("SQL") String sql, List<? extends Type> resultTypes) { MaterializedResult materializedRows = new MaterializedResult( handle.createQuery(sql).map(rowMapper(resultTypes)).list(), resultTypes); // H2 produces dates in the JVM time zone instead of the session timezone materializedRows = materializedRows.toTimeZone( DateTimeZone.getDefault(), getDateTimeZone(session.getTimeZoneKey())); return materializedRows; }
private static void insertRows( ConnectorTableMetadata tableMetadata, Handle handle, RecordSet data) { List<ColumnMetadata> columns = ImmutableList.copyOf( Iterables.filter( tableMetadata.getColumns(), new Predicate<ColumnMetadata>() { @Override public boolean apply(ColumnMetadata columnMetadata) { return !columnMetadata.isHidden(); } })); String vars = Joiner.on(',').join(nCopies(columns.size(), "?")); String sql = format("INSERT INTO %s VALUES (%s)", tableMetadata.getTable().getTableName(), vars); RecordCursor cursor = data.cursor(); while (true) { // insert 1000 rows at a time PreparedBatch batch = handle.prepareBatch(sql); for (int row = 0; row < 1000; row++) { if (!cursor.advanceNextPosition()) { batch.execute(); return; } PreparedBatchPart part = batch.add(); for (int column = 0; column < columns.size(); column++) { Type type = columns.get(column).getType(); if (BOOLEAN.equals(type)) { part.bind(column, cursor.getBoolean(column)); } else if (BIGINT.equals(type)) { part.bind(column, cursor.getLong(column)); } else if (DOUBLE.equals(type)) { part.bind(column, cursor.getDouble(column)); } else if (VARCHAR.equals(type)) { part.bind(column, cursor.getSlice(column).toStringUtf8()); } else if (DATE.equals(type)) { long millisUtc = TimeUnit.DAYS.toMillis(cursor.getLong(column)); // H2 expects dates in to be millis at midnight in the JVM timezone long localMillis = DateTimeZone.UTC.getMillisKeepLocal(DateTimeZone.getDefault(), millisUtc); part.bind(column, new Date(localMillis)); } else { throw new IllegalArgumentException("Unsupported type " + type); } } } batch.execute(); } }
public Team insertWithTxHandle(final Team team) { // in this case we use an explicit handle, and attach the dao's to the same handle (connection) try (Handle handle = jdbiHelper.getTxHandle()) { handle.begin(); TeamDao teamDao = handle.attach(TeamDao.class); TeamPersonDao teamPersonDao = handle.attach(TeamPersonDao.class); // team->person mapping table long teamId; if (team.getPointOfContact() != null) { teamId = teamDao.insertWithPoC(team); } else { teamId = teamDao.insertWithoutPoC(team); } for (Person p : team.getMembers()) { // update the team->person mapping table teamPersonDao.insert(new TeamPerson(teamId, p.getId())); } // add test code for checking that TX is handled correctly checkIfTxShouldBeRolledBack(team); handle.commit(); return get(teamId); } }
public byte[] lookupWithHandle( final Handle handle, final String tableName, final String keyColumn, final String valueColumn, final String key) { final String selectStatement = String.format("SELECT %s FROM %s WHERE %s = :key", valueColumn, tableName, keyColumn); List<byte[]> matched = handle.createQuery(selectStatement).bind("key", key).map(ByteArrayMapper.FIRST).list(); if (matched.isEmpty()) { return null; } if (matched.size() > 1) { throw new ISE( "Error! More than one matching entry[%d] found for [%s]?!", matched.size(), key); } return matched.get(0); }