Use jpa native sql @ Query operation to add delete and modify

jpa native sql @ Query operation addition, deletion and modification

1. sql statement of jpa native update:

1. Named parameters (recommended): You can define parameter names and assign values using @ Param ("parameter name"), regardless of order.

How to pass parameters for the @ Query annotation 1: Named parameters

/*@Query("delete from product where p.id=:id")
List<Person> testQueryAnnotationParams2(@Param("status") String status, @Param("id") String id);
void updateByProductId(@Param("id") String id);*/

2. The index parameters are as follows. The index value starts from 1, and the query "? The number of "X" needs to be equal to the number of parameters defined by the method, and the order should also be equal to one.

@Modifying(clearAutomatically=true)
@Query(value = "update Product set status=1 where id=?1",nativeQuery = true)
void updateByProductId(String id);

2. jpa sql statement of native delete:

@Modifying(clearAutomatically=true)
@Query(value = "delete from Productwhere id=?1",nativeQuery = true)
void delByProductId(String id);

3. jpa sql statement of native insert:

@Modifying(clearAutomatically=true)
@Query(value="insert into product(id,name,category,description,status) values(?1,?2,?3,?4,?5)",nativeQuery = true)
void addProduct(String id,String name,String cagetory,String description,String status);

4. jpa sql statement of native insert:

Haven't figured it out yet. . . . . . . . . . . .

//jpa  Table union query with many-to-many relationship  DAO Layer 
    @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 Layer 
    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 performs operations with native sql statements

Here, add an attribute of nativeQuery = true in the @ Query annotation, and you can write the query in the way of native SQL statement.

@Query(nativeQuery = true, value = "SELECT * FROM AUTH_USER WHERE name = :name1  OR name = :name2 ")
  List<UserDO> findSQL(@Param("name1") String name1, @Param("name2") String name2);