jpa（二）----jpa @Query注解原生insert\delete\update\select语句

本是古典何须时尚 2022-05-23 01:20 692阅读 0赞

\-- 如果@Query注解加上nativeQuery=true 则查询语句使用原生sql，不加则使用HQL

**一、jpa 原生insert的sql语句：**

@Modifying @Query(value = "insert into t_sys_org_user(org_id,user_id) values(?1,?2)",nativeQuery = true) int addUserToOrg(Long orgId,Long userId);

jpa 原生delete的sql语句：

@Modifying @Query(value = "delete from t_sys_org_user where org_id=?1 and user_id=?2",nativeQuery = true) int deleteUserFromOrg(Long orgId,Long userId);

jpa 原生update的sql语句：

@Modifying @Query(value = "update t_sys_user set status=0 where user_id=?1",nativeQuery = true) int updateUserStatus(Long userId);

jpa 原生select的sql语句：

//jpa 多对多关系的表联合查询 DAO层
    @Query(value = "select s from SysUserDTO s left join s.sysOrgDTOSet o where (?1 is null or s.username like ?1) and (?2 is null or o.name like ?2)")
        Page<SysUserDTO> findByUsernameAndOrgName(String username, String orgName, Pageable pageable);
    // service层
    public Page<SysUserDTO> findByUsernameAndOrgName(String username, String orgName, Pageable pageable){
            String name = (username==null)?null:"%"+username+"%";
            String orgname = (orgName==null)?null:"%"+orgName+"%";
            return sysUserDAO.findByUsernameAndOrgName(name,orgname,pageable);
        }

**二、jpa hql中使用in关键字**

@Query(value = "select distinct r from SysRoleDTO r left join r.sysOrgDTOSet o where (?1 is null or r.name like ?1) "+ "and (?2 is null or r.code like ?2) and o.id in (?3)") Page<SysRoleDTO> findAllRole(String name, String code, List<Long> ids, Pageable pageable);

@Transactional
    	@Modifying
    	@Query("delete from MeetingQRSign where mpid = ?1")
    	void deleteByMpid(String mpid);
    
    	@Modifying
    	@Query("update AccountEntity w set w.version=:version where w.id=:id")
    	void update(@Param("version") int version,@Param("id") String id);