org.skife.jdbi.v2.sqlobject.customizers.RegisterMapper Java Examples

@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);
 

@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);
 

@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);
 

@RegisterMapper(SourceMapper.class)
@SqlQuery("select * from sources where :tag = any (tags) and userid = :id")
List<Source> getSourcesByTag(@Bind("id") long id, @Bind("tag") String tag);
 

@RegisterMapper(SourceMapper.class)
@SqlQuery("select * from publicsources")
List<Source> getPublicSources();
 

@RegisterMapper(SourceMapper.class)
@SqlQuery("select * from rufususer left outer join sources on rufususer.userid = sources.userid where rufususer.userid = :id")
List<Source> getSources(@Bind("id") long id);
 

@RegisterMapper(UserMapper.class)
@SqlUpdate("delete from rufususer where email = :email")
void deleteUser(@Bind("email") String email);
 

@RegisterMapper(UserMapper.class)
@SqlUpdate("insert into rufususer (EMAIL, PASSWORD) values (:email, :password)")
void addUser(@BindBean User user);
 

@RegisterMapper(UserMapper.class)
@SqlQuery("select * from rufususer where EMAIL = :email")
User findByEmail(@Bind("email") String email);
 

@RegisterMapper(UserMapper.class)
@SqlQuery("select * from rufususer")
List<User> getAll();
 

@SqlQuery("SELECT R.COLLECTED_TIME, R.START_TIME, R.END_TIME, R.TYPE, R.AV2, R.ERRNO, R.ERRMSG, R.TR2 " +
        "FROM SNM_NIPING_RESULT R " +
        "WHERE R.ACCOUNT_ID = :accountId AND R.MONITOR_ID = :monitorId AND R.TASK_ID = :taskId AND R.COLLECTED_TIME <ge> :time <condition> ORDER BY R.COLLECTED_TIME DESC")
@RegisterMapper(MonitorNiPingResultListMapper.class)
List<MonitorNiPingResult> selectAll(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("monitorId") String monitorId, @Bind("time") long time, @Define("condition") String condition, @Define("ge") String ge);
 

@SqlQuery("SELECT R.COLLECTED_TIME, R.START_TIME, R.END_TIME, R.TYPE, R.AV2, R.ERRNO, R.ERRMSG, R.TR2 " +
        "FROM SNM_NIPING_RESULT R " +
        "WHERE R.ACCOUNT_ID = :accountId AND R.MONITOR_ID = :monitorId AND R.TASK_ID = :taskId AND R.COLLECTED_TIME <ge> :time <condition> ORDER BY R.COLLECTED_TIME DESC <page>")
@RegisterMapper(MonitorNiPingResultListMapper.class)
List<MonitorNiPingResult> select(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("monitorId") String monitorId, @Bind("time") long time, @Define("condition") String condition, @Define("page") String page, @Define("ge") String ge);
 

@SqlQuery("SELECT R.TYPE, R.ERRNO " +
        "FROM SNM_NIPING_RESULT R " +
        "WHERE R.ACCOUNT_ID = :accountId AND R.MONITOR_ID = :monitorId AND R.TASK_ID = :taskId AND R.COLLECTED_TIME <ge> :time <condition> ")
@RegisterMapper(MonitorNiPingResultUsableMapper.class)
List<MonitorNiPingResult> getMonitorNiPingResult(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("monitorId") String monitorId, @Bind("time") long time, @Define("ge") String ge);
 

@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);
 

@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);
 

@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);
 

@SqlQuery("SELECT USER_ID, ACCOUNT_ID, NAME, PASSWORD, PASSWORD_SALT, LOGIN_NAME FROM SNM_USER " +
        " WHERE LOGIN_NAME = :loginName AND STATUS = :status")
@RegisterMapper(UserMapper.class)
public User getUser(@Bind("loginName") String loginName, @Bind("status") int status);
 

@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);
 

@SqlQuery("SELECT TOKEN, ACCOUNT_ID, COMMENT, EXPIRY_DATE, CREATION_TIME, MODIFIED_TIME FROM SNM_ACCESS_CREDENTIALS " +
        " WHERE TOKEN = :token")
@RegisterMapper(AccessCredentialsMapper.class)
public AccessCredentials getCredentialsByToken(@Bind("token") String token);
 

@SqlQuery("SELECT T.TASK_ID, T.NAME FROM SNM_TASK T " +
        "INNER JOIN SNM_NIPING_RESULT R ON T.TASK_ID = R.TASK_ID " +
        "WHERE T.ACCOUNT_ID = :accountId GROUP BY TASK_ID")
@RegisterMapper(TaskMapper.class)
List<Task> selectTasksWithResult(@Bind("accountId") String accountId);
 

@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);
 

@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);
 

@SqlQuery("SELECT SNM_TASK.TASK_ID, MONITOR_ID, ACCOUNT_ID, NAME, TASK_INTERVAL AS 'INTERVAL', CONFIG_JSON, STATUS, CREATION_TIME, MODIFIED_TIME FROM SNM_TASK INNER JOIN SNM_MONITOR_TASK ON SNM_MONITOR_TASK.TASK_ID = SNM_TASK.TASK_ID " +
        " WHERE SNM_MONITOR_TASK.MONITOR_ID = :monitorId AND REDISPATCHER = :needRedispatcher AND STATUS = :taskEnableStatus")
@MaxRows(1)
@RegisterMapper(TaskMapper.class)
Task getNextRestartTask(@Bind("monitorId") String monitorId, @Bind("needRedispatcher") int needRedispatcher, @Bind("taskEnableStatus") int taskEnableStatus) ;
 

@SqlQuery("SELECT SNM_TASK.TASK_ID, MONITOR_ID, ACCOUNT_ID, NAME, TASK_INTERVAL AS 'INTERVAL', CONFIG_JSON, STATUS, CREATION_TIME, MODIFIED_TIME FROM SNM_TASK INNER JOIN SNM_MONITOR_TASK ON SNM_MONITOR_TASK.TASK_ID = SNM_TASK.TASK_ID " +
        " WHERE SNM_MONITOR_TASK.MONITOR_ID = :monitorId AND SNM_MONITOR_TASK.TASK_ID NOT IN (<runningTaskIds>) AND STATUS = :taskEnableStatus AND REDISPATCHER <ne> :redispatcher")
@MaxRows(1)
@RegisterMapper(TaskMapper.class)
Task getNextStartTask(@Bind("monitorId") String monitorId, @BindIn("runningTaskIds") List<String> runningTaskIds, @Bind("taskEnableStatus") int taskEnableStatus, @Define("ne") String notEqual, @Bind("redispatcher") int redispatcher) ;
 

@SqlQuery("SELECT SNM_MONITOR.MONITOR_ID, VERSION, ACCOUNT_ID, NAME, COUNTRY, PROVINCE, CITY, ISP, AREA, IP, NIPING_T, STATUS, CREATION_TIME, MODIFIED_TIME FROM SNM_MONITOR INNER JOIN SNM_MONITOR_TASK " +
        " ON SNM_MONITOR.MONITOR_ID = SNM_MONITOR_TASK.MONITOR_ID" +
        " WHERE ACCOUNT_ID = :accountId AND TASK_ID = :taskId <condition>")
@RegisterMapper(MonitorMapper.class)
List<Monitor> selectMonitors(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Define("condition") String condition);
 

@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")
@RegisterMapper(MonitorMapper.class)
List<Monitor> selectAllByAccountId( @Bind("accountId") String accountId);
 

@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);