@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);
@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);
@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);
/** * 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);
@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);
@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);
@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);
@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);
@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);
/** * 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);
@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);
@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);
@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);
@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);
@Query("select f " + "from Feedback f " + "where f.student = ?1") List<Feedback> findAllOfStudent(Student student);
@Query("SELECT user FROM User user WHERE user.loginId = ?1 AND user.password = ?2") List<User> getUserByLoginIdAndPassword(String loginId, String password);
@Query(FIND_ALL_SORTED_QUERY) List<Skill> findAllSorted();
@Modifying @Query(value = "update ticket t set t.message = ?1 where t.id = ?2", nativeQuery = true) void modifyTicketMessageById(String message, long ticketTd);
@Modifying @Query("delete from Message m where m.author.id=:id") void deleteAllUsersMessages(@Param("id") Integer id) throws DataAccessException;
@Query(value="SELECT sum(ifnull(b.quantity,0)) FROM order_item b where 1=1 and b.food_id=:food_id", nativeQuery = true) Integer findFoodOrderedQuantities (@Param("food_id") Long food_id);
@Query(value = "select * from document where columnd = ?1 order by date desc limit ?2",nativeQuery = true) List<Document> getNewDocumentTopN(String columd,Integer topN);
@Query("SELECT price FROM Item i WHERE i.itemId=?1") double price(long itemId);
@Query("select distinct c from SiScolCommune c, SiScolEtablissement etab where c.siScolDepartement.codDep = :codDep and etab.siScolDepartement.codDep = :codDep and etab.siScolCommune.codCom = c.codCom ") public List<SiScolCommune> getCommuneByDepartement(@Param("codDep") String codDep);
@Query("select c.cinemaHallId, c.cinema.cinemaId, c.hallName from CinemaHall c where c.cinemaHallId = ?1") List<Object[]> findWithoutSeatLayout(Integer cinemaHallId);
@Query("SELECT count(s) FROM Suggestion s, SuggestionVote sv WHERE s.id = :idS and s.id = sv.suggestion.id and sv.vote = 'IN_FAVOUR'") Long countInFavourVotes(@Param("idS") Long id);
@Query("SELECT COUNT(DISTINCT j.name) FROM Lamp l LEFT JOIN l.jobs j WHERE l.team.id = :teamId") long countNameDistinctly(@Param("teamId") long teamId);
@Query( value = "select count(p) from PROCESS_INSTANCE p JOIN p.subjects s where p.startTime between :start and :end and s.userId = :user", nativeQuery = false) Long getAmountOfStartedProcessesBetweenRangeForUser(@Param("start") LocalDateTime start, @Param("end") LocalDateTime end, @Param("user") Long userId);
@Query("SELECT COUNT(b) FROM Bridge b LEFT JOIN b.user u WHERE LOWER(u.email) LIKE %:item% OR LOWER(b.ip) LIKE %:item%") long count(@Param("item") String searchItem);
@Query("select l from Lecturer l where ACCOUNT_ID = ?1") Lecturer findLecturerByAccountId(Long id);
@Query( value = "SELECT * FROM BUSINESS_OBJECT_FIELD_PERMISSION bofp WHERE bofm_id = :bofmId AND s_id = :sId", nativeQuery = true) BusinessObjectFieldPermissionImpl getBusinessObjectFieldPermissionInState( @Param("bofmId") Long bofmId, @Param("sId") Long sId);
@Modifying @Transactional @Query(value = "update message set status='READ' where status='UNREAD' and to_user_id = ?1", nativeQuery = true) void readUserMessages(Long toUserId);
@Query("select distinct p from Post p left join p.tags t where p.isPublished = true and t.tagId = ?1") List<Post> findAllPublishedByTagId(long tagId);
@Transactional @Modifying @Query(APPROVE_DECLINE) void approveOrDecline(@Param("organizationId") Integer organizationId, @Param("status") String status);
@Query("select e From Event e ,Review r where e.eventId = r.eventId and r.personId = :person") List<Event> getEventsByPerson(@Param("person") Person person);
@Query("select u from User u where u.name = ?1") List<User> findUsersByNameUsingQuery(String name);
@Modifying @Query(value="delete from Role bean where code = :code", nativeQuery = false) void deleteByCode(@Param("code") String code);
@Transactional @Query(value="delete from holidays where year(holidate)=:year ", nativeQuery = true) @Modifying void deleteByYear(@Param("year") int year);
@Query("select u from GoogleUser u " + "inner join u.openIDAuthentication o " + "where o.subject = :subject") Optional<GoogleUser> findByOpenIDSubject( @Param("subject") String subject);
@Query("SELECT max(s.updated) FROM Shipment s") Date lastUpdate();
@Modifying @Transactional(readOnly = false) @Query("delete from #{#entityName} e where e.expiration is not null and e.expiration < NOW()") void deleteExpired();
