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

The following examples show how to use org.skife.jdbi.v2.sqlobject.SqlQuery. 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: 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 #2
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 #3
Source File: ShardDao.java    From presto with Apache License 2.0 6 votes vote down vote up
@SqlQuery("SELECT n.node_identifier, x.bytes\n" +
        "FROM (\n" +
        "  SELECT node_id, sum(compressed_size) bytes\n" +
        "  FROM (\n" +
        "      SELECT sn.node_id, s.compressed_size\n" +
        "      FROM shards s\n" +
        "      JOIN shard_nodes sn ON (s.shard_id = sn.shard_id)\n" +
        "      WHERE s.bucket_number IS NULL\n" +
        "    UNION ALL\n" +
        "      SELECT b.node_id, s.compressed_size\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" +
        "  ) x\n" +
        "  GROUP BY node_id\n" +
        ") x\n" +
        "JOIN nodes n ON (x.node_id = n.node_id)")
@Mapper(NodeSize.Mapper.class)
Set<NodeSize> getNodeSizes();
 
Example #4
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 #5
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 #6
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 #7
Source File: IStoragePostgreSql.java    From cassandra-reaper with Apache License 2.0 5 votes vote down vote up
@SqlQuery(SQL_GET_METRICS_FOR_CLUSTER)
@Mapper(GenericMetricMapper.class)
Collection<GenericMetric> getMetricsForCluster(
    @Bind("cluster") String cluster,
    @Bind("metricDomain") String metricDomain,
    @Bind("metricType") String metricType,
    @Bind("since") Instant since
);
 
Example #8
Source File: IStoragePostgreSql.java    From cassandra-reaper with Apache License 2.0 5 votes vote down vote up
@SqlQuery(SQL_GET_NODE_METRICS_BY_NODE)
@Mapper(NodeMetricsMapper.class)
NodeMetrics getNodeMetricsByNode(
    @Bind("runId") long runId,
    @Bind("expirationTime") Instant expirationTime,
    @Bind("node") String node
);
 
Example #9
Source File: MetadataDao.java    From presto with Apache License 2.0 5 votes vote down vote up
@SqlQuery("SELECT schema_name, table_name, create_time, update_time, table_version,\n" +
        "  shard_count, row_count, compressed_size, uncompressed_size\n" +
        "FROM tables\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")
@Mapper(TableStatsRow.Mapper.class)
List<TableStatsRow> getTableStatsRows(
        @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
@SqlQuery("SELECT table_id\n" +
        "FROM tables\n" +
        "WHERE organization_enabled\n" +
        "  AND maintenance_blocked IS NULL\n" +
        "  AND table_id IN\n" +
        "       (SELECT table_id\n" +
        "        FROM columns\n" +
        "        WHERE sort_ordinal_position IS NOT NULL)")
Set<Long> getOrganizationEligibleTables();
 
Example #11
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 #12
Source File: IStoragePostgreSql.java    From cassandra-reaper with Apache License 2.0 5 votes vote down vote up
@SqlQuery(SQL_GET_METRICS_FOR_HOST)
@Mapper(GenericMetricMapper.class)
Collection<GenericMetric> getMetricsForHost(
    @Bind("cluster") String cluster,
    @Bind("host") String host,
    @Bind("metricDomain") String metricDomain,
    @Bind("metricType") String metricType,
    @Bind("since") Instant since
);
 
Example #13
Source File: TestingShardDao.java    From presto with Apache License 2.0 5 votes vote down vote up
@SqlQuery("SELECT s.shard_uuid, n.node_identifier\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 s.table_id = :tableId")
@Mapper(ShardNode.Mapper.class)
Set<ShardNode> getShardNodes(@Bind("tableId") long tableId);
 
Example #14
Source File: ArticleDao.java    From rufus with MIT License 4 votes vote down vote up
@SqlQuery("select * from rufususer left outer join articles on rufususer.userid = articles.userid where rufususer.userid = :id")
Set<Article> getBookmarked(@Bind("id") long id);
 
Example #15
Source File: MonitorNiPingResultDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@SqlQuery("SELECT R.COLLECTED_TIME, R.TR2 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.TR2 IS NOT NULL ORDER BY R.COLLECTED_TIME")
@RegisterMapper(MetricsMapper.class)
List<Metrics> selectTRMetrics(@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 #16
Source File: MonitorNiPingResultDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@SqlQuery("SELECT CITY FROM SNM_MONITOR M INNER JOIN SNM_NIPING_RESULT R ON R.MONITOR_ID = M.MONITOR_ID WHERE R.ACCOUNT_ID = :accountId AND COUNTRY = :country AND PROVINCE = :province  GROUP BY CITY")
List<String> selectCities(@Bind("accountId") String accountId, @Bind("country") String country, @Bind("province") String province);
 
Example #17
Source File: IStoragePostgreSql.java    From cassandra-reaper with Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_GET_REPAIR_RUNS_FOR_CLUSTER)
@Mapper(RepairRunMapper.class)
Collection<RepairRun> getRepairRunsForCluster(
    @Bind("clusterName") String clusterName,
    @Bind("limit") int limit);
 
Example #18
Source File: IStoragePostgreSql.java    From cassandra-reaper with Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_GET_REPAIR_SCHEDULES_FOR_KEYSPACE)
@Mapper(RepairScheduleMapper.class)
Collection<RepairSchedule> getRepairSchedulesForKeyspace(
    @Bind("keyspaceName") String keyspaceName);
 
Example #19
Source File: MonitorNiPingResultDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@SqlQuery("SELECT COUNTRY FROM SNM_MONITOR M INNER JOIN SNM_NIPING_RESULT R ON R.MONITOR_ID = M.MONITOR_ID WHERE R.ACCOUNT_ID = :accountId GROUP BY COUNTRY")
List<String> selectCountries(@Bind("accountId") String accountId);
 
Example #20
Source File: MonitorNiPingResultDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@SqlQuery("SELECT M.MONITOR_ID, M.NAME, M.IP FROM (SELECT MONITOR_ID FROM SNM_NIPING_RESULT WHERE ACCOUNT_ID = :accountId AND TASK_ID = :taskId GROUP BY MONITOR_ID) TMP " +
        " INNER JOIN SNM_MONITOR M ON M.MONITOR_ID = TMP.MONITOR_ID <condition>")
@RegisterMapper(ResultMonitorMapper.class)
List<Monitor> selectMonitors(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Define("condition") String condition);
 
Example #21
Source File: MetadataDao.java    From presto with 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 #22
Source File: MonitorNiPingResultDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@SqlQuery("SELECT R.TASK_ID, NAME FROM SNM_NIPING_RESULT R INNER JOIN SNM_TASK T ON T.TASK_ID = R.TASK_ID WHERE T.ACCOUNT_ID = " +
        ":accountId GROUP BY R.TASK_ID")
@RegisterMapper(ResultTaskMapper.class)
List<Task> selectTasks(@Bind("accountId") String accountId);
 
Example #23
Source File: MonitorNiPingResultDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@SqlQuery("SELECT TASK_ID, ERRNO, ERRNO, TR2, AV2, M.MONITOR_ID, M.NAME, M.IP, M.COUNTRY, M.PROVINCE, M.CITY, M.ISP FROM SNM_NIPING_RESULT R " +
        "INNER JOIN SNM_MONITOR M ON M.MONITOR_ID = R.MONITOR_ID " +
        "WHERE R.ACCOUNT_ID = :accountId AND R.TASK_ID = :taskId AND TYPE = :type AND collected_Time <ge> :startDate")
@RegisterMapper(MonitorNiPingResultMapper.class)
List<MonitorNiPingResult> selectByTaskId(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("startDate") long startTime, @Bind("type") int type, @Define("ge") String ge);
 
Example #24
Source File: AccessCredentialsDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@SqlQuery("SELECT TOKEN FROM SNM_ACCESS_CREDENTIALS WHERE ACCOUNT_ID = :accountId")
String getTokenByAccountId(@Bind("accountId") String accountId);
 
Example #25
Source File: IStoragePostgreSql.java    From cassandra-reaper with Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_GET_REPAIR_SCHEDULES_FOR_CLUSTER_AND_KEYSPACE)
@Mapper(RepairScheduleMapper.class)
Collection<RepairSchedule> getRepairSchedulesForClusterAndKeySpace(
    @Bind("clusterName") String clusterName,
    @Bind("keyspaceName") String keyspaceName);
 
Example #26
Source File: IStoragePostgreSql.java    From cassandra-reaper with Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_COUNT_RUNNING_REAPERS)
int countRunningReapers(
    @Bind("expirationTime") Instant expirationTime
);
 
Example #27
Source File: TaskDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@SqlQuery("SELECT * FROM (SELECT SNM_TASK.TASK_ID, MONITOR_ID, ACCOUNT_ID, NAME, TASK_INTERVAL AS 'INTERVAL', CONFIG_JSON, STATUS, CREATION_TIME, MODIFIED_TIME FROM SNM_TASK LEFT JOIN SNM_MONITOR_TASK ON SNM_MONITOR_TASK.TASK_ID = SNM_TASK.TASK_ID " +
        " WHERE ACCOUNT_ID = :accountId AND SNM_TASK.TASK_ID = :taskId AND SNM_TASK.STATUS <ne> :taskDeleteStatus) AS TMP GROUP BY MONITOR_ID")
@RegisterMapper(TaskMapper.class)
List<Task> get(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("taskDeleteStatus") int taskDeleteStatus, @Define("ne") String notEqual);
 
Example #28
Source File: TaskDao.java    From SAPNetworkMonitor with 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 #29
Source File: IStoragePostgreSql.java    From cassandra-reaper with Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_GET_REPAIR_RUNS_WITH_STATE)
@Mapper(RepairRunMapper.class)
Collection<RepairRun> getRepairRunsWithState(
    @Bind("state") RepairRun.RunState state);
 
Example #30
Source File: MonitorNiPingResultDao.java    From SAPNetworkMonitor with GNU General Public License v3.0 4 votes vote down vote up
@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);