Java Code Examples for org.skife.jdbi.v2.sqlobject.Bind

The following examples show how to use org.skife.jdbi.v2.sqlobject.Bind. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
@SqlQuery("SELECT R.TASK_ID, R.AV2, R.TR2, R.ERRNO, R.MONITOR_ID, M.MONITOR_ID, M.NAME, M.IP, M.COUNTRY, M.PROVINCE, M.CITY, M.ISP " +
        "FROM SNM_NIPING_RESULT AS R " +
        "INNER JOIN " +
        "( " +
        "SELECT MAX(RES2.ID) AS ID " +
        "FROM SNM_NIPING_RESULT RES2 " +
        "INNER JOIN ( " +
        "SELECT MAX(COLLECTED_TIME) AS T1, MONITOR_ID, TASK_ID FROM SNM_NIPING_RESULT WHERE ACCOUNT_ID = :accountId AND TASK_ID = :taskId AND TYPE = :type GROUP BY TASK_ID, MONITOR_ID " +
        ") AS RES1 " +
        "ON RES2.COLLECTED_TIME = RES1.T1 AND RES1.MONITOR_ID = RES2.MONITOR_ID AND RES1.TASK_ID = RES2.TASK_ID " +
        "GROUP BY RES2.COLLECTED_TIME, RES2.TASK_ID, RES2.MONITOR_ID " +
        ") AS RES3 " +
        "ON R.ID = RES3.ID " +
        "INNER JOIN SNM_MONITOR M ON M.MONITOR_ID = R.MONITOR_ID")
@RegisterMapper(MonitorNiPingResultMapper.class)
List<MonitorNiPingResult> selectByTaskId(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("type") int type);
 
Example 2
Source Project: SAPNetworkMonitor   Source File: TaskDao.java    License: GNU General Public License v3.0 6 votes vote down vote up
@SqlQuery("SELECT *, RESULT.ID AS RESULT_ID, RESULT.ERRNO FROM (SELECT T.TASK_ID, MT.MONITOR_ID, M.NAME AS MONITOR_NAME, T.ACCOUNT_ID, T.NAME, TASK_INTERVAL AS 'INTERVAL', CONFIG_JSON, T.STATUS, T.CREATION_TIME, T.MODIFIED_TIME FROM SNM_TASK T " +
"LEFT JOIN SNM_MONITOR_TASK MT ON MT.TASK_ID = T.TASK_ID " +
"LEFT JOIN SNM_MONITOR M ON M.MONITOR_ID = MT.MONITOR_ID " +
"WHERE T.ACCOUNT_ID = :accountId AND T.STATUS <ne> :taskDeleteStatus GROUP BY T.TASK_ID, MONITOR_ID) AS TMP " +
"LEFT JOIN ( " +
        "SELECT R.ID, R.COLLECTED_TIME, R.MONITOR_ID, R.TASK_ID, R.ERRNO " +
        "FROM SNM_NIPING_RESULT AS R " +
        "INNER JOIN  " +
        "( " +
        "SELECT MAX(RES2.ID) AS ID " +
        "FROM SNM_NIPING_RESULT RES2 " +
        "INNER JOIN ( " +
        "SELECT MAX(COLLECTED_TIME) AS T1, MONITOR_ID, TASK_ID FROM SNM_NIPING_RESULT WHERE COLLECTED_TIME <ge> :lasthour AND TYPE = :type GROUP BY TASK_ID, MONITOR_ID " +
        ") AS RES1 " +
        "ON RES2.COLLECTED_TIME = RES1.T1 AND RES1.MONITOR_ID = RES2.MONITOR_ID AND RES1.TASK_ID = RES2.TASK_ID " +
        "GROUP BY RES2.COLLECTED_TIME, RES2.TASK_ID, RES2.MONITOR_ID\n" +
        ") AS RES3 " +
        "ON R.ID = RES3.ID "+
") AS RESULT ON TMP.MONITOR_ID = RESULT.MONITOR_ID AND TMP.TASK_ID = RESULT.TASK_ID " +
" ORDER BY TMP.CREATION_TIME DESC")
@RegisterMapper(TaskMapper.class)
List<Task> selectByAccountId(@Bind("accountId") String accountId, @Bind("taskDeleteStatus") int taskDeleteStatus, @Bind("lasthour") Date lasthour, @Bind("type") int type, @Define("ne") String notEqual, @Define("ge") String greaterThan);
 
Example 3
Source Project: presto   Source File: ShardDao.java    License: Apache License 2.0 6 votes vote down vote up
@SqlQuery("SELECT " + SHARD_METADATA_COLUMNS + "\n" +
        "FROM (\n" +
        "    SELECT s.*\n" +
        "    FROM shards s\n" +
        "    JOIN shard_nodes sn ON (s.shard_id = sn.shard_id)\n" +
        "    JOIN nodes n ON (sn.node_id = n.node_id)\n" +
        "    WHERE n.node_identifier = :nodeIdentifier\n" +
        "      AND s.bucket_number IS NULL\n" +
        "      AND (s.table_id = :tableId OR :tableId IS NULL)\n" +
        "  UNION ALL\n" +
        "    SELECT s.*\n" +
        "    FROM shards s\n" +
        "    JOIN tables t ON (s.table_id = t.table_id)\n" +
        "    JOIN distributions d ON (t.distribution_id = d.distribution_id)\n" +
        "    JOIN buckets b ON (\n" +
        "      d.distribution_id = b.distribution_id AND\n" +
        "      s.bucket_number = b.bucket_number)\n" +
        "    JOIN nodes n ON (b.node_id = n.node_id)\n" +
        "    WHERE n.node_identifier = :nodeIdentifier\n" +
        "      AND (s.table_id = :tableId OR :tableId IS NULL)\n" +
        ") x")
@Mapper(ShardMetadata.Mapper.class)
Set<ShardMetadata> getNodeShards(@Bind("nodeIdentifier") String nodeIdentifier, @Bind("tableId") Long tableId);
 
Example 4
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery(TABLE_INFORMATION_SELECT +
        "WHERE t.schema_name = :schemaName\n" +
        "  AND t.table_name = :tableName")
@Mapper(TableMapper.class)
Table getTableInformation(
        @Bind("schemaName") String schemaName,
        @Bind("tableName") String tableName);
 
Example 5
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery(TABLE_COLUMN_SELECT +
        "WHERE t.table_id = :tableId\n" +
        "  AND c.column_id = :columnId\n" +
        "ORDER BY c.ordinal_position\n")
TableColumn getTableColumn(
        @Bind("tableId") long tableId,
        @Bind("columnId") long columnId);
 
Example 6
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery(TABLE_COLUMN_SELECT +
        "WHERE (schema_name = :schemaName OR :schemaName IS NULL)\n" +
        "  AND (table_name = :tableName OR :tableName IS NULL)\n" +
        "ORDER BY schema_name, table_name, ordinal_position")
List<TableColumn> listTableColumns(
        @Bind("schemaName") String schemaName,
        @Bind("tableName") String tableName);
 
Example 7
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery("SELECT schema_name, table_name, data\n" +
        "FROM views\n" +
        "WHERE (schema_name = :schemaName OR :schemaName IS NULL)\n" +
        "  AND (table_name = :tableName OR :tableName IS NULL)\n" +
        "ORDER BY schema_name, table_name\n")
@Mapper(ViewResult.Mapper.class)
List<ViewResult> getViews(
        @Bind("schemaName") String schemaName,
        @Bind("tableName") String tableName);
 
Example 8
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlUpdate("UPDATE tables SET\n" +
        "  update_time = :updateTime\n" +
        ", table_version = table_version + 1\n" +
        "WHERE table_id = :tableId")
void updateTableVersion(
        @Bind("tableId") long tableId,
        @Bind("updateTime") long updateTime);
 
Example 9
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlUpdate("UPDATE tables SET\n" +
        "  shard_count = shard_count + :shardCount \n" +
        ", row_count = row_count + :rowCount\n" +
        ", compressed_size = compressed_size + :compressedSize\n" +
        ", uncompressed_size = uncompressed_size + :uncompressedSize\n" +
        "WHERE table_id = :tableId")
void updateTableStats(
        @Bind("tableId") long tableId,
        @Bind("shardCount") long shardCount,
        @Bind("rowCount") long rowCount,
        @Bind("compressedSize") long compressedSize,
        @Bind("uncompressedSize") long uncompressedSize);
 
Example 10
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlUpdate("INSERT INTO columns (table_id, column_id, column_name, ordinal_position, data_type, sort_ordinal_position, bucket_ordinal_position)\n" +
        "VALUES (:tableId, :columnId, :columnName, :ordinalPosition, :dataType, :sortOrdinalPosition, :bucketOrdinalPosition)")
void insertColumn(
        @Bind("tableId") long tableId,
        @Bind("columnId") long columnId,
        @Bind("columnName") String columnName,
        @Bind("ordinalPosition") int ordinalPosition,
        @Bind("dataType") String dataType,
        @Bind("sortOrdinalPosition") Integer sortOrdinalPosition,
        @Bind("bucketOrdinalPosition") Integer bucketOrdinalPosition);
 
Example 11
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlUpdate("UPDATE tables SET\n" +
        "  schema_name = :newSchemaName\n" +
        ", table_name = :newTableName\n" +
        "WHERE table_id = :tableId")
void renameTable(
        @Bind("tableId") long tableId,
        @Bind("newSchemaName") String newSchemaName,
        @Bind("newTableName") String newTableName);
 
Example 12
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlUpdate("UPDATE columns SET column_name = :target\n" +
        "WHERE table_id = :tableId\n" +
        "  AND column_id = :columnId")
void renameColumn(
        @Bind("tableId") long tableId,
        @Bind("columnId") long columnId,
        @Bind("target") String target);
 
Example 13
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlUpdate("INSERT INTO distributions (distribution_name, column_types, bucket_count)\n" +
        "VALUES (:distributionName, :columnTypes, :bucketCount)")
@GetGeneratedKeys
long insertDistribution(
        @Bind("distributionName") String distributionName,
        @Bind("columnTypes") String columnTypes,
        @Bind("bucketCount") int bucketCount);
 
Example 14
Source Project: presto   Source File: H2ShardDao.java    License: Apache License 2.0 5 votes vote down vote up
@Override
@SqlUpdate("DELETE FROM transactions\n" +
        "WHERE end_time < :maxEndTime\n" +
        "  AND successful IN (TRUE, FALSE)\n" +
        "  AND transaction_id NOT IN (SELECT transaction_id FROM created_shards)\n" +
        "LIMIT " + CLEANUP_TRANSACTIONS_BATCH_SIZE)
int deleteOldCompletedTransactions(@Bind("maxEndTime") Timestamp maxEndTime);
 
Example 15
Source Project: presto   Source File: MySqlShardDao.java    License: Apache License 2.0 5 votes vote down vote up
@Override
// 'order by' is needed in this statement in order to make it compatible with statement-based replication
@SqlUpdate("DELETE FROM transactions\n" +
        "WHERE end_time < :maxEndTime\n" +
        "  AND successful IN (TRUE, FALSE)\n" +
        "  AND transaction_id NOT IN (SELECT transaction_id FROM created_shards)\n" +
        "ORDER BY end_time, transaction_id\n" +
        "LIMIT " + CLEANUP_TRANSACTIONS_BATCH_SIZE)
int deleteOldCompletedTransactions(@Bind("maxEndTime") Timestamp maxEndTime);
 
Example 16
Source Project: presto   Source File: ShardDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery("SELECT b.bucket_number, n.node_identifier\n" +
        "FROM buckets b\n" +
        "JOIN nodes n ON (b.node_id = n.node_id)\n" +
        "WHERE b.distribution_id = :distributionId\n" +
        "ORDER BY b.bucket_number")
@Mapper(BucketNode.Mapper.class)
List<BucketNode> getBucketNodes(@Bind("distributionId") long distributionId);
 
Example 17
Source Project: presto   Source File: ShardOrganizerDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlUpdate("UPDATE shard_organizer_jobs SET last_start_time = :lastStartTime\n" +
        "   WHERE node_identifier = :nodeIdentifier\n" +
        "     AND table_id = :tableId")
void updateLastStartTime(
        @Bind("nodeIdentifier") String nodeIdentifier,
        @Bind("tableId") long tableId,
        @Bind("lastStartTime") long lastStartTime);
 
Example 18
Source Project: presto   Source File: TestingShardDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlUpdate("INSERT INTO shards (shard_uuid, table_id, bucket_number, create_time, row_count, compressed_size, uncompressed_size, xxhash64)\n" +
        "VALUES (:shardUuid, :tableId, :bucketNumber, CURRENT_TIMESTAMP, :rowCount, :compressedSize, :uncompressedSize, :xxhash64)")
@GetGeneratedKeys
long insertShard(
        @Bind("shardUuid") UUID shardUuid,
        @Bind("tableId") long tableId,
        @Bind("bucketNumber") Integer bucketNumber,
        @Bind("rowCount") long rowCount,
        @Bind("compressedSize") long compressedSize,
        @Bind("uncompressedSize") long uncompressedSize,
        @Bind("xxhash64") long xxhash64);
 
Example 19
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(TABLE_INFORMATION_SELECT +
        "WHERE t.table_id = :tableId")
@Mapper(TableMapper.class)
Table getTableInformation(@Bind("tableId") long tableId);
 
Example 20
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT schema_name, table_name\n" +
        "FROM tables\n" +
        "WHERE (schema_name = :schemaName OR :schemaName IS NULL)")
@Mapper(SchemaTableNameMapper.class)
List<SchemaTableName> listTables(
        @Bind("schemaName") String schemaName);
 
Example 21
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(TABLE_COLUMN_SELECT +
        "WHERE t.table_id = :tableId\n" +
        "ORDER BY c.ordinal_position")
List<TableColumn> listTableColumns(@Bind("tableId") long tableId);
 
Example 22
Source Project: SAPNetworkMonitor   Source File: TaskDao.java    License: GNU General Public License v3.0 4 votes vote down vote up
@SqlQuery("SELECT TASK_ID, ACCOUNT_ID, NAME, TASK_INTERVAL AS 'INTERVAL', " +
        " CONFIG_JSON, STATUS, CREATION_TIME, MODIFIED_TIME FROM SNM_TASK " +
        "WHERE ACCOUNT_ID = :accountId")
@RegisterMapper(TaskMapper.class)
List<Task> selectByAccountId(@Bind("accountId") String accountId);
 
Example 23
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(TABLE_COLUMN_SELECT +
        "WHERE t.table_id = :tableId\n" +
        "  AND c.bucket_ordinal_position IS NOT NULL\n" +
        "ORDER BY c.bucket_ordinal_position")
List<TableColumn> listBucketColumns(@Bind("tableId") long tableId);
 
Example 24
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT schema_name, table_name, data\n" +
        "FROM views\n" +
        "WHERE (schema_name = :schemaName OR :schemaName IS NULL)")
@Mapper(SchemaTableNameMapper.class)
List<SchemaTableName> listViews(
        @Bind("schemaName") String schemaName);
 
Example 25
@SqlQuery("SELECT R.COLLECTED_TIME, R.AV2 AS VALUE " +
        "FROM SNM_NIPING_RESULT R " +
        "WHERE R.ACCOUNT_ID = :accountId AND R.MONITOR_ID = :monitorId AND R.TASK_ID = :taskId AND TYPE = :type AND R.COLLECTED_TIME <ge> :time AND R.AV2 IS NOT NULL <condition> ORDER BY R.COLLECTED_TIME")
@RegisterMapper(MetricsMapper.class)
List<Metrics> selectAVMetrics(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("monitorId") String monitorId, @Bind("type") int type, @Bind("time") long time, @Define("ge") String ge);
 
Example 26
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlUpdate("DELETE FROM columns\n" +
        " WHERE table_id = :tableId\n" +
        "  AND column_id = :columnId")
void dropColumn(
        @Bind("tableId") long tableId,
        @Bind("columnId") long column);
 
Example 27
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlUpdate("INSERT INTO views (schema_name, table_name, data)\n" +
        "VALUES (:schemaName, :tableName, :data)")
void insertView(
        @Bind("schemaName") String schemaName,
        @Bind("tableName") String tableName,
        @Bind("data") String data);
 
Example 28
@SqlUpdate("DELETE FROM SNM_MONITOR_TASK WHERE TASK_ID = :taskId")
void removeMonitorTask(@Bind("taskId") String taskId);
 
Example 29
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlUpdate("DELETE FROM columns WHERE table_id = :tableId")
int dropColumns(@Bind("tableId") long tableId);
 
Example 30
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlUpdate("DELETE FROM views\n" +
        "WHERE schema_name = :schemaName\n" +
        "  AND table_name = :tableName")
int dropView(
        @Bind("schemaName") String schemaName,
        @Bind("tableName") String tableName);