How to do a native query using parameters data members?

January 17, 2024

I’m trying to create a upsert query in a CrudRepository<> which has a PostgresSQL query within it, but the values need to be populated from the properties of an object. I tried using the syntax expected to work with JPQL, but I get the error Reason: The query is not a valid SQL query whenever I try to run the application.

How do I create a native query in Spring which allows me to access the data members of the passed object?

package com.yuknis.ziwi.ziwidataservice.data.author.entity;

import java.util.Optional;
import java.util.UUID;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface AuthorRepo extends JpaRepository<AuthorEntity, UUID> {
    
    @Query(
            value = """
                    INSERT
                    INTO author
                    VALUES (
                        :authorEntity.getId(),
                        :authorEntity.getName()
                    )
                    RETURNING *
                    ON CONFLICT (name)
                    DO UPDATE
                    """,
            nativeQuery = true
    )
    AuthorEntity saveByName(@Param("authorEntity") AuthorEntity authorEntity);

    Optional<AuthorEntity> findByName(String name);
    
}

>Solution :

Can you check the following version

@Query(
        value = "INSERT INTO author (id, name) VALUES (:#{#authorEntity.id}, :#{#authorEntity.name})" +
                " ON CONFLICT (name) DO UPDATE SET name = :#{#authorEntity.name} RETURNING *",
        nativeQuery = true
)
AuthorEntity saveByName(@Param("authorEntity") AuthorEntity authorEntity);