skip to Main Content

Maven pom dependency:

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.json/json -->
        <dependency>
            <groupId>org.json</groupId>
            <artifactId>json</artifactId>
            <version>20230227</version>
        </dependency>

Configuration Dialect:

spring:
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
    show-sql: 'true'
    hibernate:
      ddl-auto: update

I have the following Entity:

@Entity
@Data
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Slf4j
public class CourseEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(columnDefinition = "json")
    private String list_id_professors;
}

And the SqlCourseRepository file:

import jakarta.transaction.Transactional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.Optional;

@Repository
public interface SqlCourseRepository extends JpaRepository<CourseEntity, Integer>, JpaSpecificationExecutor {
    //multiple criteria: JpaSpecificationExecutor

    @Transactional
    @Modifying
    @Query("update CourseEntity set list_id_professors = :list_id_professors where id = :id")
    Integer updateCourseListIdProfessors(@Param(value = "id") Integer id, @Param(value = "list_id_professors") String list_id_professors);
    // If okk -> integer = num rows affected. 0 if no changes. -1 if error.
}

I have a Postgresql Database:

Postgresql Database Field Initial Value

If i want to update the JSON field, i can execute on SQL Query Tool inside the database:

update course_entity set list_id_professors = '[1,2]'::json where id = 1;

Postgresql Database Field Updated Value

My problem is how to do that on the SqlCourseRepository.

Because i cant put "::json" after the variable

Error adding "::json"

Also, another possibility is doind insted of a Json a int[] variable on the definition of the Entity’s attribute. But i have this another issue because i don’t know how to do it:

Error changing type to "Integer[]"

So i dont know how to put a SQL query that can interact with the Postgresql Database correctly

2

Answers


  1. Chosen as BEST ANSWER

    The solution is to edit the value on the SQL command that is going to execute the JpaRepository. For that its neccessary to add the field "value" and the "nativeQuery" field. Also, its necessary to change the "::json" to a CAST to work properly.

    Instead of:

    @Query("update CourseEntity set list_id_professors = :list_id_professors where id = :id")
    

    Change to:

    @Query(value = "update course_entity set list_id_professors = CAST(:list_id_professors AS json) where id = (:id);", nativeQuery = true)
    

    Complete solution:

    @Transactional
    @Modifying
    @Query(value = "update course_entity set list_id_professors = CAST(:list_id_professors AS json) where id = (:id);", nativeQuery = true)
    Integer updateCourseListIdProfessors(@Param(value = "id") Integer id, @Param(value = "list_id_professors") String list_id_professors);
    // If okk -> integer = num rows affected. 0 if no changes. -1 if error.
    

    Its important to add:

    1- As its native SQL command and not JPA interpreted, we have to change from CourseEntity to the name of the posytgresql column course_entity.

    2- The name of the parametrs must coincide 100% with the name that expects the @Param value


  2. you can try to escape the :: operator like following

        @Query("update CourseEntity set list_id_professors = :list_id_professors \:\:json where id = :id", nativeQuery = true)
    
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search