[FIXED] Get Entity ID after executing Native insert query + Spring JPA

Issue

I am using a Native query in my JPA Repository to run INSERT query - because, I couldn't run a few queries through JPA.

String INSERT_USER_IN_TO_DATABASE = "INSERT INTO USER_MASTER " +
            "(" +
            "MOBILE_X,USER_TYPE,COUNTRYCODE,USER_N,USER_LAST_N,GENDER,DOB) " +
            "VALUES ( " +
            "EncryptByKey(Key_GUID(convert(varchar,?1)), ?2)," +
            "1,+91,?3,?4,?5,?6" +
            ")";

    @Query(value = INSERT_USER_IN_TO_DATABASE, nativeQuery = true)
    @Modifying
    UserMasterEntity saveNewUser(String encryptionKey,
                                 String phoneNumber,
                                 String name,
                                 String lastName,
                                 String gender,
                                 String dob);

My intention is to execute the INSERT statement and get me the userMaster entity. But I get the below exception

Caused by: java.lang.IllegalArgumentException: Modifying queries can only use void or int/Integer as return type! Offending method: public abstract com.officeride.fileprocess.job.bulkuser.UserMasterEntity com.officeride.fileprocess.job.bulkuser.UserMasterRepository.saveNewUser(java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String)

I used @ColumnTransformer stuff too in my entity and nothing is working out for me.

How to tackle this?

Solution

You cannot make the INSERT INTO statement return anything. Depending on your database's support, on that end you could execute multiple statements, e.g., INSERT INTO ...; SELECT ..., but Spring Data JDBC does not support this.

• What you can do is implement saveNewUser and perform the insert then select, sequentially and synchronously. See this: https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#repositories.single-repository-behavior.
• If your database supports it, you could create a stored procedure that performs the insert then select.

Answered By - Oliver
Answer Checked By - Gilberto Lyons (JavaFixing Admin)