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
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
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:
Change to:
Complete solution:
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
you can try to escape the :: operator like following