Java Query类代码示例

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query("SELECT u FROM UserEntity u WHERE u.tenantId = :tenantId " +
        "AND u.customerId = :customerId AND u.authority = :authority " +
        "AND LOWER(u.searchText) LIKE LOWER(CONCAT(:searchText, '%'))" +
        "AND u.id > :idOffset ORDER BY u.id")
List<UserEntity> findUsersByAuthority(@Param("tenantId") String tenantId,
                                      @Param("customerId") String customerId,
                                      @Param("idOffset") String idOffset,
                                      @Param("searchText") String searchText,
                                      @Param("authority") Authority authority,
                                      Pageable pageable);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query (
    value = "(SELECT SUM(points) FROM (SELECT COUNT(topic.id_user) AS points FROM topic WHERE topic.id_user = :id" +
            " UNION ALL SELECT 2 * COUNT(answer.id_user) AS points FROM answer WHERE answer.id_user = :id UNION ALL " +
            "SELECT 3 * COUNT(answer.id_user) AS points FROM answer WHERE answer.id_user = :id AND answer.useful = TRUE) t)",
    nativeQuery = true
)
Long getPoints(@Param("id") Long id);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query("SELECT wb FROM WidgetsBundleEntity wb WHERE wb.tenantId IN (:tenantId, :nullTenantId) " +
        "AND LOWER(wb.searchText) LIKE LOWER(CONCAT(:textSearch, '%')) " +
        "AND wb.id > :idOffset ORDER BY wb.id")
List<WidgetsBundleEntity> findAllTenantWidgetsBundlesByTenantId(@Param("tenantId") String tenantId,
                                                                @Param("nullTenantId") String nullTenantId,
                                                                @Param("textSearch") String textSearch,
                                                                @Param("idOffset") String idOffset,
                                                                Pageable pageable);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query("SELECT DISTINCT new org.apache.servicecomb.saga.alpha.core.TxEvent("
     + "t.serviceName, t.instanceId, t.globalTxId, t.localTxId, t.parentTxId, t.type, t.compensationMethod, t.payloads"
     + ") FROM TxEvent t "
     + "WHERE t.globalTxId = ?1 AND t.type = 'TxStartedEvent'"
     + "AND EXISTS ("
     + "  SELECT t1.globalTxId"
     + "  FROM TxEvent t1 "
     + "  WHERE t1.globalTxId = ?1 "
     + "  AND t1.localTxId = t.localTxId "
     + "  AND t1.type = 'TxEndedEvent'"
     + ") AND NOT EXISTS ( "
     + "  SELECT t2.globalTxId"
     + "  FROM TxEvent t2 "
     + "  WHERE t2.globalTxId = ?1 "
     + "  AND t2.localTxId = t.localTxId "
     + "  AND t2.type = 'TxCompensatedEvent')"
)
 List<TxEvent> findStartedEventsWithMatchingEndedButNotCompensatedEvents(String globalTxId);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query(value="SELECT t FROM #{#entityName} t "
		+ "WHERE t.boTypeId = :boTypeId AND (t.title LIKE %:keywords% OR t.content LIKE %:keywords%) "
		+ "ORDER BY t.lastUpdate DESC")
Page<Article> findByBoAndKeywords(
		@Param(value="boTypeId") long boTypeId, 
		@Param(value="keywords") String keywords, 
		Pageable pageable);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
/**
 *  SELECT EXECUTOR
 * */

@Query(value = "SELECT routerItemModel FROM RouterItemModel routerItemModel WHERE routerItemModel.routerIp =:routerIp " +
                "AND routerItemModel.routerMac =:routerMac " +
                "AND routerItemModel.model =:model")
public RouterItemModel findByRouterIpAndRouterMacAndModel(@Param("routerIp") String routerIp,
                                                          @Param("routerMac")String routerMac,
                                                          @Param("model")String model);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query("from Movie m " +
        "left outer join fetch m.country " +
        "left outer join fetch m.movieStatus " +
        "left outer join fetch m.movieType " +
        "left outer join fetch m.movieStyleSet " +
        "where m.movieId = ?1")
Movie findOneWithAllDetails(Integer movieId);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query("select c.cinemaHallId, c.cinema.cinemaId, c.hallName from CinemaHall c where c.cinemaHallId = ?1")
List<Object[]> findWithoutSeatLayout(Integer cinemaHallId);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query("SELECT t FROM TenantEntity t WHERE t.region = :region " +
        "AND LOWER(t.searchText) LIKE LOWER(CONCAT(:textSearch, '%')) " +
        "AND t.id > :idOffset ORDER BY t.id")
List<TenantEntity> findByRegionNextPage(@Param("region") String region,
                                        @Param("textSearch") String textSearch,
                                        @Param("idOffset") String idOffset,
                                        Pageable pageable);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Modifying
@Query(QueryProperties.updateEmployeeProfileByEmpId)
int updateEmployeeProfile(@Param("employeeFirstName") String employeeFirstName,
                          @Param("employeeLastName") String employeeLastName,
                          @Param("employeeTitle") String employeeTitle,
                          @Param("employeePhone") String employeePhone,
                          @Param("employeeId") long employeeId);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query("SELECT a FROM AssetEntity a WHERE a.tenantId = :tenantId " +
        "AND a.customerId = :customerId " +
        "AND LOWER(a.searchText) LIKE LOWER(CONCAT(:textSearch, '%')) " +
        "AND a.id > :idOffset ORDER BY a.id")
List<AssetEntity> findByTenantIdAndCustomerId(@Param("tenantId") String tenantId,
                                              @Param("customerId") String customerId,
                                              @Param("textSearch") String textSearch,
                                              @Param("idOffset") String idOffset,
                                              Pageable pageable);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
/**
 * Updates counts of test run.
 * @param id Test run id.
 * @param totalCases Total test case count.
 * @param failedCases Total failed test cases count.
 */
@Modifying
@Transactional
@Query("UPDATE TestRun tr SET tr.exampleCount = COALESCE(tr.exampleCount, 0) + :totalCases, " +
		"tr.failureCount = COALESCE(tr.failureCount, 0) + :failedCases, " +
		"tr.duration = COALESCE(tr.duration, 0) + :duration " +
		"WHERE tr.id = :id")
void updateCounts(@Param("id")  Long id, @Param("totalCases") int totalCases, @Param("failedCases") int failedCases,
				  @Param("duration") double duration);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query("SELECT pmd FROM PluginMetaDataEntity pmd WHERE pmd.tenantId IN (:tenantId, :nullTenantId) " +
        "AND LOWER(pmd.searchText) LIKE LOWER(CONCAT(:textSearch, '%')) " +
        "AND pmd.id > :idOffset ORDER BY pmd.id")
List<PluginMetaDataEntity> findAllTenantPluginsByTenantId(@Param("tenantId") String tenantId,
                                                          @Param("nullTenantId") String nullTenantId,
                                                          @Param("textSearch") String textSearch,
                                                          @Param("idOffset") String idOffset,
                                                          Pageable pageable);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query("SELECT pmd FROM PluginMetaDataEntity pmd WHERE pmd.tenantId = :tenantId " +
        "AND LOWER(pmd.searchText) LIKE LOWER(CONCAT(:textSearch, '%')) " +
        "AND pmd.id > :idOffset ORDER BY pmd.id")
List<PluginMetaDataEntity> findByTenantIdAndPageLink(@Param("tenantId") String tenantId,
                                                     @Param("textSearch") String textSearch,
                                                     @Param("idOffset") String idOffset,
                                                     Pageable pageable);
 

import org.springframework.data.jpa.repository.Query; //导入依赖的package包/类
@Query("SELECT di FROM DashboardInfoEntity di WHERE di.tenantId = :tenantId " +
        "AND LOWER(di.searchText) LIKE LOWER(CONCAT(:searchText, '%')) " +
        "AND di.id > :idOffset ORDER BY di.id")
List<DashboardInfoEntity> findByTenantId(@Param("tenantId") String tenantId,
                                         @Param("searchText") String searchText,
                                         @Param("idOffset") String idOffset,
                                         Pageable pageable);