Customize SQL query results using JPA

Directory JPA Custom SQL Query Results Directly on Code Last Run 1 demo Code JPA SQL Query 1 Eye 2 JPA NamedQuery Query 3 Use @ Query Query

JPA Custom SQL Query Results

Many times you will encounter custom sql, custom return fields instead of pojo classes. This situation is returned through the interface definition.

Direct code

 @Query(value = "select m.field AS field,COUNT(m.field) AS size from MigrationObject m where m.xmlName = ?1 and m.groupName = ?2 group by m.field")
    List<WorkCenter> getKey(String xmlName, String groupName);

In this case, only two fields are returned, and you need to define an interface to receive them. (Note the configuration of the AS alias.)

public interface WorkCenter { 
    String getField();
    String getSize();
}

Finally, run the demo code for 1 time

   List<WorkCenter> list = migrationObjectRepository.getKey("EN_Work centerResource.xml","Key");
        for (WorkCenter workCenter:list){
            System.out.println(workCenter.getField());
            System.out.println(workCenter.getSize());
        }

ARBPL
5
SPRAS
2
CANUM
2
ENDDA
1
WERKS
5

SQL Query for JPA

1-point eye

JAP queries 1 through SQL in two ways: queries through NamedQuery and queries using @ Query.

2 NamedQuery Query for JPA

1 Description

Spring Data JPA supports NameQuery of JPA to define the query method, that is, one name maps one query statement.

2 Definition

@Entity 
@NamedQuery(name = "Person.withNameAndAddressNamedQuery",
query = "select p from Person p where p.name=?1 and address=?2")
public class Person {
    ......
}

3 How to use it

public interface PersonRepo extends JpaRepository<Person,Long>{
    // Using the NameQuery Instead of querying according to the method name 
    Person withNameAndAddressNamedQuery(String name,String address);
}

3 Query with @ Query

1 Using parameter indexes

public interface PersonRepo extends JpaRepository<Person,Long>{
    @Query("select p from Person p where p.address=  ? 1")
    List<Person> findByAddress(String address);
}

2 Using named parameters

public interface PersonRepo extends JpaRepository<Person,Long>{
    @Query("select p from Person p where p.name= :name and p.address= :address")
    Person withNameAndAddressQuery(@Param("name")String name,@Param("address")String address);
}

3 Update query

public interface PersonRepo extends JpaRepository<Person,Long>{
    @Modifying
    @Transactional
    @Query("update Person p set p.name=?1")
    int setName(String name);// Indicates the number of rows affected by the update statement 
}