Customize SQL query results using JPA
- 2021-12-12 08:47:50
- OfStack
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
}