Problems and Solutions of SpringDataJpa Writing Native sql

Directory SpringDataJpa Problems Encountered in Writing Native sql Spring data jpa Custom SQL Statement Encountered Error Not supported for DML operations

Problems encountered in writing native sql from SpringDataJpa

@Repository
public interface EduCourseDao extends JpaRepository<EduCourse,Long>, JpaSpecificationExecutor<EduCourse> {
    // According to the curriculum id Query the confirmation information of the course 
    @Query(value = "SELECT ec.id,ec.title,ec.price,ec.lesson_num, " +
            "ecd.description, " +
            "es1.title AS oneSubject, " +
            "es2.title AS twoSubject " +
            "FROM edu_course ec LEFT JOIN edu_course_description ecd ON ec.id=ecd.id " +
            "LEFT JOIN edu_teacher et ON ec.teacher_id=et.id " +
            "LEFT JOIN edu_subject es1 ON ec.subject_parent_id=es1.id " +
            "LEFT JOIN edu_subject es2 ON ec.subject_id=es2.id " +
            "WHERE ec.id=?1 ",nativeQuery = true)
    List< Map<String,Object>> findPublishInfoById(String id);

}

Because other tables are involved, the return type uses List < Map < String,Object > > Instead of using the originally defined vo class

At the end of each line, there should be a space between "and", otherwise an error will be reported.

Spring data jpa Custom SQL Statement Encountered Error

Not supported for DML operations

Today, I encountered an error while customizing an Update statement, which shows that Not supported for DML operations means that DML operation is not supported.

My UserRepository is an inherited PagingAndSortingRepository interface. After looking at the documentation for JPA, I found that this interface does not support update transactions, so I need to add @ Modifying to the annotation.

The original text reads as follows:

3.3.7. Modifying queries

All the sections above describe how to declare queries to access a given entity or collection of entities.Of course you can add custom modifying behaviour by using facilities described in Customimplementations for Spring Data repositories. As this approach is feasible for comprehensive customfunctionality, you can achieve the execution of modifying queries that actually only need parameterbinding by annotating the query method with @Modifying:

Example 45. Declaring manipulating queries

@Modifying
@Query("update User u set u.firstname = ?1 where u.lastname = ?2")
int setFixedFirstnameFor(String firstname, String lastname);
This will trigger the query annotated to the method as updating query instead of a selecting one. As theEntityManager might contain outdated entities after the execution of the modifying query, we do notautomatically clear it (see JavaDoc of EntityManager.clear() for details) since this will effectively dropall non-flushed changes still pending in the EntityManager. If you wish the EntityManager to be clearedautomatically you can set @Modifying annotation's clearAutomatically attribute to true.

Here is my source code:

@Modifying
@Query("update User u set u.u_name=?2,u.u_sex=?3,u.u_date = ?4,u.u_minzu = ?5,u.u_area=?6,u.u_country=?7 where u.u_id =?1")
public void updateUserById(String u_id,String u_name,String u_sex,
                               String u_date, String u_minzu,String u_area,
                               String u_country);