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

The following examples show how to use org.skife.jdbi.v2.sqlobject.Bind. 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
Source File: MonitorNiPingResultDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 6 votes vote down vote up
@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 File: TaskDao.java    From SAPNetworkMonitor with 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 File: ShardDao.java    From presto with 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 File: ShardDao.java    From presto with 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 #5
Source File: MetadataDao.java    From presto with 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 #6
Source File: MetadataDao.java    From presto with 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 #7
Source File: MetadataDao.java    From presto with 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 #8
Source File: MetadataDao.java    From presto with 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 #9
Source File: MetadataDao.java    From presto with 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 #10
Source File: MetadataDao.java    From presto with 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 #11
Source File: MetadataDao.java    From presto with 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 #12
Source File: TestingShardDao.java    From presto with 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 #13
Source File: MySqlShardDao.java    From presto with 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 #14
Source File: H2ShardDao.java    From presto with 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 File: ShardOrganizerDao.java    From presto with 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 #16
Source File: MetadataDao.java    From presto with 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 #17
Source File: MetadataDao.java    From presto with 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 #18
Source File: MetadataDao.java    From presto with 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 #19
Source File: ShardDao.java    From presto with Apache License 2.0 4 votes vote down vote up
@SqlUpdate("INSERT INTO deleted_shards (shard_uuid, delete_time)\n" +
        "SELECT shard_uuid, CURRENT_TIMESTAMP\n" +
        "FROM shards\n" +
        "WHERE table_id = :tableId")
void insertDeletedShards(@Bind("tableId") long tableId);
 
Example #20
Source File: ArticleDao.java    From rufus with MIT License 4 votes vote down vote up
@SqlUpdate("update sources set frontpage = FALSE where source = :source")
void removeFrontpage(@Bind("id") long id, @BindSource Source source);
 
Example #21
Source File: ShardDao.java    From presto with Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT successful FROM transactions WHERE transaction_id = :transactionId")
Boolean transactionSuccessful(@Bind("transactionId") long transactionId);
 
Example #22
Source File: ShardDao.java    From presto with Apache License 2.0 4 votes vote down vote up
@SqlUpdate("DELETE FROM created_shards WHERE transaction_id = :transactionId")
void deleteCreatedShards(@Bind("transactionId") long transactionId);
 
Example #23
Source File: ShardDao.java    From presto with Apache License 2.0 4 votes vote down vote up
@SqlUpdate("INSERT INTO created_shards (shard_uuid, transaction_id)\n" +
        "VALUES (:shardUuid, :transactionId)")
void insertCreatedShard(
        @Bind("shardUuid") UUID shardUuid,
        @Bind("transactionId") long transactionId);
 
Example #24
Source File: MonitorTaskDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@SqlUpdate("UPDATE SNM_MONITOR_TASK SET REDISPATCHER = :needRedispatcher WHERE TASK_ID = :taskId")
void updateMonitorTaskRedispatcher(@Bind("taskId") String taskId, @Bind("needRedispatcher") int needRedispatcher);
 
Example #25
Source File: ShardDao.java    From presto with Apache License 2.0 4 votes vote down vote up
@SqlBatch("DELETE FROM deleted_shards WHERE shard_uuid = :shardUuid")
void deleteCleanedShards(@Bind("shardUuid") Iterable<UUID> shardUuids);
 
Example #26
Source File: ArticleDao.java    From rufus with MIT License 4 votes vote down vote up
@SqlUpdate("insert into sources(userid, source, frontpage) values((select userid from rufususer where userid = :id), :source, true)")
void addFrontpageSource(@Bind("id") long id, @Bind("source") String source);
 
Example #27
Source File: ArticleDao.java    From rufus with MIT License 4 votes vote down vote up
@SqlUpdate("update sources set frontpage = TRUE where source = :source")
void setFrontpage(@Bind("id") long id, @BindSource Source source);
 
Example #28
Source File: MonitorDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@SqlQuery("SELECT MONITOR_ID, VERSION, ACCOUNT_ID, NAME, COUNTRY, PROVINCE, CITY, ISP, AREA, IP, NIPING_T, STATUS, CREATION_TIME, MODIFIED_TIME FROM SNM_MONITOR " +
        " WHERE ACCOUNT_ID = :accountId AND STATUS = :monitorEnableStatus")
@RegisterMapper(MonitorMapper.class)
List<Monitor> selectByAccountId( @Bind("accountId") String accountId,  @Bind("monitorEnableStatus") int status);
 
Example #29
Source File: ShardDao.java    From presto with Apache License 2.0 4 votes vote down vote up
@SqlUpdate("UPDATE transactions SET\n" +
        "  successful = FALSE\n" +
        ", end_time = CURRENT_TIMESTAMP\n" +
        "WHERE successful IS NULL\n" +
        "  AND start_time < :maxStartTime")
void abortOldTransactions(@Bind("maxStartTime") Timestamp maxStartTime);
 
Example #30
Source File: ShardDao.java    From presto with Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT shard_uuid\n" +
        "FROM deleted_shards\n" +
        "WHERE delete_time < :maxDeleteTime\n" +
        "LIMIT " + CLEANABLE_SHARDS_BATCH_SIZE)
Set<UUID> getCleanableShardsBatch(@Bind("maxDeleteTime") Timestamp maxDeleteTime);