org.apache.ibatis.annotations.Select Java Examples

@Select({"SELECT configurationApplications.id       AS id, " +
        "       configurations.id                  AS configurationId, " +
        "       configurations.name                AS configurationName, " +
        "       configurations.customerId          AS customerId, " +
        "       applications.id                    AS applicationId, " +
        "       applications.name                  AS applicationName, " +
        "       COALESCE(configurationApplications.showIcon, applications.showIcon) AS showIcon, " +
        "       applications.id                    AS applicationVersionId, " +
        "       applications.id                    AS versionText, " +
        "       configurationApplications.remove   AS remove, " +
        "       configurationApplications.action   AS action " +
        "FROM configurations " +
        "INNER JOIN applicationVersions ON applicationVersions.id = #{id} " +
        "INNER JOIN applications ON applications.id = applicationVersions.applicationId " +
        "LEFT JOIN configurationApplications ON configurations.id = configurationApplications.configurationId AND configurationApplications.applicationVersionId = applicationVersions.id " +
        "WHERE configurations.customerId = #{customerId} " +
        "ORDER BY LOWER(configurations.name)"})
List<ApplicationVersionConfigurationLink> getApplicationVersionConfigurationsWithCandidates(
        @Param("customerId") Integer customerId, @Param("id") Integer applicationVersionId
);
 

/**
 * 通过降级点来查询
 *
 * @param point
 * @return
 */
@Select("select * from point_return_value where app_group_name = #{appGroupName} and app_name = #{appName}  " +
        "and point = #{point}")
@Results({
        @Result(property = "id", column = "id"),
        @Result(property = "appGroupName", column = "app_group_name"),
        @Result(property = "appName", column = "app_name"),
        @Result(property = "point", column = "point"),
        @Result(property = "returnValueStr", column = "return_value_str"),
        @Result(property = "operatorName", column = "operator_name"),
        @Result(property = "operatorEmail", column = "operator_email"),
        @Result(property = "creatorName", column = "creator_name"),
        @Result(property = "creatorEmail", column = "creator_email"),
        @Result(property = "modifiedTime", column = "modify_time"),
        @Result(property = "createTime", column = "create_time")
})
PointReturnValueDO queryByPoint(@Param("appGroupName") String appGroupName, @Param("appName") String appName,
                                @Param("point") String point);
 

/**
 * 通过应用组名称来查询
 *
 * @param appGroupName
 * @param appName
 * @param sdsSchemeName
 * @return
 */
@Select("select * from sds_scheme where app_group_name = #{appGroupName} " +
        " and app_name = #{appName} " +
        " and sds_scheme_name = #{sdsSchemeName} " +
        " order by modify_time desc ")
@Results({
        @Result(property = "id", column = "id"),
        @Result(property = "appGroupName", column = "app_group_name"),
        @Result(property = "appName", column = "app_name"),
        @Result(property = "sdsSchemeName", column = "sds_scheme_name"),
        @Result(property = "operatorName", column = "operator_name"),
        @Result(property = "operatorEmail", column = "operator_email"),
        @Result(property = "modifiedTime", column = "modify_time"),
        @Result(property = "createTime", column = "create_time")
})
SdsSchemeDO queryByGroupName(@Param("appGroupName") String appGroupName, @Param("appName") String appName,
                                 @Param("sdsSchemeName") String sdsSchemeName);
 

@Select({"SELECT configurationApplications.id       AS id, " +
        "       configurations.id                  AS configurationId, " +
        "       configurations.name                AS configurationName, " +
        "       configurations.customerId          AS customerId, " +
        "       applications.id                    AS applicationId, " +
        "       applications.name                  AS applicationName, " +
        "       COALESCE(configurationApplications.showIcon, applications.showIcon) AS showIcon, " +
        "       configurationApplications.remove   AS remove, " +
        "       latestAppVersion.version   AS latestVersionText, " +
        "       currentAppVersion.version   AS currentVersionText, " +
        "       (configurationApplications.configurationId IS NOT NULL AND applications.latestVersion <> configurationApplications.applicationVersionId) AS outdated, " +
        "       configurationApplications.action AS action " +
        "FROM configurations " +
        "         LEFT JOIN applications ON applications.id = #{id} " +
        "         INNER JOIN applicationVersions AS latestAppVersion ON latestAppVersion.applicationId = applications.id AND latestAppVersion.id=applications.latestversion " +
        "         LEFT JOIN configurationApplications ON configurations.id = configurationApplications.configurationId AND " +
        "                                                applications.id = configurationApplications.applicationId " +
        "         LEFT JOIN applicationVersions AS currentAppVersion ON currentAppVersion.applicationId = applications.id AND currentAppVersion.id=configurationApplications.applicationVersionId " +
        "WHERE configurations.customerId = #{customerId} " +
        "ORDER BY LOWER(configurations.name)"})
List<ApplicationConfigurationLink> getApplicationConfigurations(@Param("customerId") Integer customerId,
                                                                @Param("id") Integer applicationId);
 

/**
 * 按条件查询心跳数据
 *
 * @param appGroupName
 * @param appName
 * @param point
 * @param startTime
 * @param endTime
 * @return
 */
@Select("select * from heartbeat where app_group_name = #{appGroupName} " +
        " and app_name = #{appName} " +
        " and point = #{point} " +
        " and statistics_cycle_time >= #{startTime}" +
        " and statistics_cycle_time <= #{endTime}")
@Results({
        @Result(property = "id", column = "id"),
        @Result(property = "appGroupName", column = "app_group_name"),
        @Result(property = "appName", column = "app_name"),
        @Result(property = "point", column = "point"),
        @Result(property = "downgradeNum", column = "downgrade_num"),
        @Result(property = "visitNum", column = "visit_num"),
        @Result(property = "exceptionNum", column = "exception_num"),
        @Result(property = "timeoutNum", column = "timeout_num"),
        @Result(property = "maxConcurrentNum", column = "max_concurrent_num"),
        @Result(property = "appIp", column = "app_ip"),
        @Result(property = "statisticsCycleTime", column = "statistics_cycle_time")
})
List<HeartbeatDO> queryHeartbeatList(@Param("appGroupName") String appGroupName, @Param("appName") String appName,
                                     @Param("point") String point, @Param("startTime") Date startTime, @Param(
                                             "endTime") Date endTime);
 

@Select("SELECT c.name " +
        "FROM configurationFiles cf " +
        "INNER JOIN configurations c ON c.id = cf.configurationId " +
        "INNER JOIN uploadedFiles uf ON uf.id = cf.fileId " +
        "WHERE c.customerId = #{customerId} " +
        "AND uf.filePath = #{fileName}")
List<String> getUsingConfigurations(@Param("customerId") int customerId, @Param("fileName") String fileName);
 

@Select({SELECT_BY_VERSION_BASE +
        "WHERE (customerId = #{customerId} OR customers.master = TRUE )" +
        "AND pkg = #{pkg} " +
        "AND applicationVersions.version=#{version}"})
List<Application> findByPackageIdAndVersion(@Param("customerId") int customerId,
                                            @Param("pkg") String pkg,
                                            @Param("version") String version);
 

@Select({"SELECT COUNT(*) AS cnt " +
        "FROM applications " +
        "INNER JOIN customers ON customers.id = applications.customerId " +
        "INNER JOIN applicationVersions ON applicationVersions.id = applications.latestVersion " +
        "WHERE (applications.customerId = #{customerId})" +
        "AND (applicationVersions.url=#{url}) "})
long countAllApplicationsByUrl(@Param("customerId") int customerId, @Param("url") String url);
 

/**
 * 查询下一章节ID
 * @param bookId
 * @param currentSortNumber
 * @return
 */
@Select("select id from book_chapter  " +
        " where book_id=#{bookId} " +
        " and sort_number > #{currentSortNumber} " +
        " order by sort_number asc limit 1")
Integer selectNextChapterId(@Param("bookId") Integer bookId,
                            @Param("currentSortNumber") Integer currentSortNumber);
 

/**
 * 查询所有降级预案
 *
 * @param appGroupName
 * @param appName
 * @return
 */
@Select("select * from sds_scheme where app_group_name = #{appGroupName} and app_name = #{appName} order by "
        + "modify_time desc ")
@Results({
        @Result(property = "id", column = "id"),
        @Result(property = "appGroupName", column = "app_group_name"),
        @Result(property = "appName", column = "app_name"),
        @Result(property = "sdsSchemeName", column = "sds_scheme_name"),
        @Result(property = "operatorName", column = "operator_name"),
        @Result(property = "operatorEmail", column = "operator_email"),
        @Result(property = "modifiedTime", column = "modify_time"),
        @Result(property = "createTime", column = "create_time")
})
List<SdsSchemeDO> queryAllSdsScheme(@Param("appGroupName") String appGroupName,
                                            @Param("appName") String appName);
 

/**
 * 根据年份和物料id获取进出仓记录
 */
@Select("SELECT materialsSell.id as id,materials.materialsName as materialsName,materialsSellDetail.total as total,materialsSell.type as type,DATE(materialsSell.date) as date,user.username as username "
		+ "FROM materialsSell,materialsSellDetail,user,materials "
		+ "WHERE "
		+ "materialsSell.id = materialsSellDetail.materialsSellId "
		+ "AND materialsSell.userId = user.id "
		+ "AND materials.id = materialsSellDetail.materialsId "
		+ "AND YEAR(materialsSell.date) = #{year} "
		+ "AND materials.id = #{materialsId}")
public List<InOutDataVo> getBillDateByYearAndMaterial(@Param("materialsId") String materialsId, @Param("year") String year);
 

@Select("select * from sys_permission t where t.permission = #{permission}")
SysPermission findByPermission(String permission);
 

@Select("SELECT devices.id FROM devices WHERE configurationId = #{configurationId}")
List<Device> getDeviceIdsBySoleConfigurationId(@Param("configurationId") int configurationId);
 

@Select("select id from sys_depart where org_code=#{orgCode}")
public String queryDepartIdByOrgCode(@Param("orgCode") String orgCode);
 

@Select("<script> select b.title,b.pm,b.category,b.type,b.number,b.add_time ,u.nickname " +
        "from yx_user_bill b left join yx_user u on u.uid=b.uid  where 1=1  "  +
        "<if test =\"category !=''\">and b.category=#{category}</if> " +
        "<if test =\"type !=''\">and b.type=#{type}</if> " +
        "<if test =\"nickname !=''\">and u.nickname LIKE CONCAT('%',#{nickname},'%')</if> </script> ")
List<YxUserBillDto> findAllByQueryCriteria(@Param("category") String category, @Param("type") String type, @Param("nickname") String nickname);
 

@Select("SELECT pid FROM sys_dict WHERE id = #{id}")
Long findPid(@Param("id") Long id);
 

@Select("select p.id,p.product_name as productName,p.price as price from product p")
List<Product> queryAllProduct();
 

@Select("select * from sys_permission t where t.id = #{id}")
SysPermission findById(Long id);
 

@Select("select * from file_info t where t.id = #{id}")
FileInfo getById(String id);
 

@Select("<script>select table_name ,create_time , engine, table_collation, table_comment from information_schema.tables " +
        "where table_schema = (select database()) order by create_time desc</script>")
List<TableInfo> selectTables();
 

@Select("select * from sys_role t where t.code = #{code}")
SysRole findByCode(String code);
 

@Select("SELECT COUNT(*) AS cnt " +
        "FROM icons ic " +
        "INNER JOIN uploadedFiles uf ON uf.id = ic.fileId " +
        "WHERE uf.filePath = #{fileName}")
long countFileUsedAsIcon(@Param("fileName") String fileName);
 

@Select("select * from sys_user t where t.username = #{username}")
Map getUser(String username);
 

@Select("select * from sys_role r inner join sys_role_user ru on r.id = ru.roleId where ru.userId = #{userId}")
List<Client> listByUserId(Long userId);
 

@Select("select id from sys_depart where org_code=#{orgCode}")
public String queryDepartIdByOrgCode(@Param("orgCode") String orgCode);
 

@Select("SELECT ID FROM sys_category WHERE CODE = #{code,jdbcType=VARCHAR}")
public String queryIdByCode(@Param("code")  String code);
 

@Select("SELECT * FROM JEECG_ORDER_TICKET WHERE ORDER_ID = #{mainId}")
public List<JeecgOrderTicket> selectTicketsByMainId(String mainId);
 

@Select({SELECT_BASE +
        "WHERE applications.id = #{id}"})
Application findById(@Param("id") int id);
 

@Select({SELECT_BY_VERSION_BASE +
        "WHERE pkg = #{pkg}"})
List<Application> findAllByPackageId(@Param("pkg") String pkg);
 

@Select("select * from sys_user t where t.id = #{id}")
SysUser findById(Long id);