Java SqlQuery类代码示例

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@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);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@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);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SqlQuery("SELECT stateId FROM q_values " +
        "WHERE :x BETWEEN x-5 AND x+5 AND :y BETWEEN y-5 AND y+5 " +
        "AND targetObjectType=:targetObjectType " +
        "AND :aboveCount BETWEEN aboveCount-2 AND aboveCount+2" +
        "AND :leftCount BETWEEN leftCount-2 AND leftCount+2 " +
        "AND :rightCount BETWEEN rightCount-2 AND rightCount+2 " +
        "AND :belowCount BETWEEN belowCount-2 AND belowCount+2 " +
        "AND :distanceToPig BETWEEN distanceToPig-3 AND distanceToPig+3 " +
        "AND trajectorytype=:trajectoryType "+
        "AND pigsLeft =:pigsLeft")
List<Integer> getStateIds(@Bind("x") int x,
                          @Bind("y") int y,
                          @Bind("targetObjectType") String targetObjectType,
                          @Bind("aboveCount") int aboveCount,
                          @Bind("leftCount") int leftCount,
                          @Bind("rightCount") int rightCount,
                          @Bind("belowCount") int belowCount,
                          @Bind("distanceToPig") double distanceToPig,
                          @Bind("trajectoryType") String trajectoryType,
                          @Bind("pigsLeft") int pigsLeft);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SqlQuery(SELECT_FROM_STATEMENT + " WHERE claim.submitter_id = :submitterId" + ORDER_BY_ID_DESCENDING)
List<Claim> getBySubmitterId(@Bind("submitterId") String submitterId);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
/**
 * NOTE: we don't limit by the geometry so all tiles get all data (which caches well)
 * This could (should) be revised for e.g. Aves
 */
@SqlQuery("SELECT"
          + "  X(geom) AS longitude,"
          + "  Y(geom) AS latitude,"
          + "  yearCounts"
          + "  FROM lepidoptera"
          + "  WHERE speciesKey=:speciesKey")
List<PointFeature> getSpeciesCounts(@Bind("speciesKey") int speciesKey);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SqlQuery("SELECT q_value FROM q_values WHERE stateId=:stateId AND x=:x AND y=:y AND targetObjectType=:targetObjectType AND aboveCount=:aboveCount " +
        "AND leftCount=:leftCount AND rightCount=:rightCount AND belowCount=:belowCount AND distanceToPig=:distanceToPig AND trajectorytype=:trajectoryType AND pigsLeft=:pigsLeft;")
double getQValue(
        @Bind("stateId") int stateId,
        @Bind("x") int x,
        @Bind("y") int y,
        @Bind("targetObjectType") String targetObjectType,
        @Bind("aboveCount") int aboveCount,
        @Bind("leftCount") int leftCount,
        @Bind("rightCount") int rightCount,
        @Bind("belowCount") int belowCount,
        @Bind("distanceToPig") double distanceToPig,
        @Bind("trajectoryType") String trajectoryType,
        @Bind("pigsLeft") int pigsLeft
);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SqlQuery("SELECT count(stateId) FROM q_values WHERE x=:x AND y=:y AND targetObjectType=:targetObjectType AND aboveCount=:aboveCount " +
        "AND leftCount=:leftCount AND rightCount=:rightCount AND belowCount=:belowCount AND distanceToPig=:distanceToPig AND trajectorytype=:trajectoryType AND pigsLeft=:pigsLeft")
int countStateIds(@Bind("x") int x,
                  @Bind("y") int y,
                  @Bind("targetObjectType") String targetObjectType,
                  @Bind("aboveCount") int aboveCount,
                  @Bind("leftCount") int leftCount,
                  @Bind("rightCount") int rightCount,
                  @Bind("belowCount") int belowCount,
                  @Bind("distanceToPig") double distanceToPig,
                  @Bind("trajectoryType") String trajectoryType);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SqlQuery(SQL_GET_REPAIR_UNIT_BY_CLUSTER_AND_TABLES)
@Mapper(RepairUnitMapper.class)
RepairUnit getRepairUnitByClusterAndTables(
    @Bind("clusterName") String clusterName,
    @Bind("keyspaceName") String keyspaceName,
    @Bind("columnFamilies") Collection<String> columnFamilies,
    @Bind("nodes") Collection<String> nodes,
    @Bind("datacenters") Collection<String> datacenters,
    @Bind("blacklisted_tables") Collection<String> blacklistedTables);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SqlQuery(SELECT_FROM_STATEMENT + ORDER_BY_ID_DESCENDING)
List<Claim> findAll();
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SingleValueResult
@SqlQuery(SELECT_FROM_STATEMENT + " WHERE claim.letter_holder_id = :letterHolderId")
Optional<Claim> getByLetterHolderId(@Bind("letterHolderId") String letterHolderId);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SingleValueResult
@SqlQuery(SELECT_FROM_STATEMENT + " WHERE claim.external_id = :externalId")
Optional<Claim> getClaimByExternalId(@Bind("externalId") String externalId);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SqlQuery(SELECT_FROM_STATEMENT + " WHERE claim.defendant_id = :defendantId" + ORDER_BY_ID_DESCENDING)
List<Claim> getByDefendantId(@Bind("defendantId") String defendantId);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SingleValueResult
@SqlQuery(SELECT_FROM_STATEMENT + " WHERE claim.reference_number = :claimReferenceNumber")
Optional<Claim> getByClaimReferenceNumber(@Bind("claimReferenceNumber") String claimReferenceNumber);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SingleValueResult
@SqlQuery(SELECT_FROM_STATEMENT + " WHERE claim.reference_number = :claimReferenceNumber "
    + "AND claim.submitter_id = :submitterId")
Optional<Claim> getByClaimReferenceAndSubmitter(@Bind("claimReferenceNumber") String claimReferenceNumber,
                                                @Bind("submitterId") String submitterId);
 

import org.skife.jdbi.v2.sqlobject.SqlQuery; //导入依赖的package包/类
@SqlQuery(SELECT_FROM_STATEMENT + " WHERE claim.submitter_id = :submitterId "
    + " AND claim.claim ->>'externalReferenceNumber' = :externalReference" + ORDER_BY_ID_DESCENDING)
List<Claim> getByExternalReference(@Bind("externalReference") String externalReference,
                                   @Bind("submitterId") String submitterId);