Speichern Sie Java-Objekt als JSON in der DB-Tabelle in Spring Data JDBC und suchen Sie bald mit gespeichertem JSONJava

Java-Forum
Anonymous
 Speichern Sie Java-Objekt als JSON in der DB-Tabelle in Spring Data JDBC und suchen Sie bald mit gespeichertem JSON

Post by Anonymous »

Ich verwende Spring-Data-JDBC und möchte Java-Objekte als JSON in der Datenbank speichern und wenn ich finde oder suche Im Repository verwende ich normales _, wie wir es in einem normalen verschachtelten Spring-Data-JDBC-Objekt tun, als Beispiel findAllByCommonInfo_IsActive(boolean isActive), um innerhalb des aufgerufenen Objekts zu suchen commonInfo, das ist die folgende Darstellung, auf die ich meine Frage anwenden möchte
LuCallingRequestCase.java
< pre class="lang-java Prettyprint-override">
@Data
@Builder
@Table
@NoArgsConstructor
@AllArgsConstructor
public class LuCallingRequestCase implements Serializable {

@Id
private Long luCallingRequestCaseId;

private Long luProsecutionWorkRequestTypeId;

private CommonInfo commonInfo;

}

CommonInfo.java
@Data
@Builder
public class CommonInfo {

@NonNull
private final String createdBy;

@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "dd-MM-yyyy HH:mm:ss", timezone = "GMT+2")
private final Instant createdAt;

private String updatedBy;

@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "dd-MM-yyyy HH:mm:ss", timezone = "GMT+2")
private Instant updatedAt;

@Builder.Default
private boolean isActive = true;

@Builder.Default
private boolean isUsed = false;

@Builder.Default
@JsonProperty("isDeleted")
private boolean isDeleted = false;
}

SQL-Tabelle

CREATE TABLE LU_CALLING_REQUEST_CASE
(
LU_CALLING_REQUEST_CASE_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL,
LU_PROSECUTION_WORK_REQUEST_TYPE_ID NUMBER NOT NULL,
COMMON_INFO JSON DEFAULT '{
"createdBy": null,
"updatedBy": null,
"createdAt": null,
"updatedAt": null,
"isActive": true,
"isUsed": false,
"isDeleted": false
}',
CONSTRAINT FK4_PROSECUTION_WORK_REQUEST_TYPE FOREIGN KEY (LU_PROSECUTION_WORK_REQUEST_TYPE_ID) REFERENCES LU_PROSECUTION_WORK_REQUEST_TYPE (LU_PROSECUTION_WORK_REQUEST_TYPE_ID) ON DELETE CASCADE
);


LuCallingRequestCaseRepository.java

@Repository
public interface LuCallingRequestCaseRepository extends PagingAndSortingRepository, CrudRepository {

// Query to count records based on available filter without using 'isDeleted'
@Query("""
SELECT COUNT(*)
FROM LU_CALLING_REQUEST_CASE
WHERE ( LU_PROSECUTION_WORK_REQUEST_TYPE_ID = :prosecutionWorkTypeId OR :prosecutionWorkTypeId IS NULL )
""")
int countByFilter(Long prosecutionWorkTypeId);

// Query to find records with pagination
@Query("""
SELECT
LU_CALLING_REQUEST_CASE_ID,
LU_PROSECUTION_WORK_REQUEST_TYPE_ID,
JSON_SERIALIZE(LU_PROSECUTION_WORK_REQUEST_TYPE_IDS) AS LU_PROSECUTION_WORK_REQUEST_TYPE_IDS,
JSON_SERIALIZE(LU_CASE_TYPE_IDS) AS LU_CASE_TYPE_IDS
FROM LU_CALLING_REQUEST_CASE
WHERE ( LU_PROSECUTION_WORK_REQUEST_TYPE_ID = :prosecutionWorkTypeId OR :prosecutionWorkTypeId IS NULL )
ORDER BY LU_CALLING_REQUEST_CASE_ID
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
""")
List findByFilter(
@Param("offset") int offset,
@Param("limit") int limit,
Long prosecutionWorkTypeId);

// Query to find a specific record by LU_CALLING_REQUEST_CASE_ID
@Query("""
SELECT
LU_CALLING_REQUEST_CASE_ID,
LU_PROSECUTION_WORK_REQUEST_TYPE_ID,
JSON_SERIALIZE(LU_PROSECUTION_WORK_REQUEST_TYPE_IDS) AS LU_PROSECUTION_WORK_REQUEST_TYPE_IDS,
JSON_SERIALIZE(LU_CASE_TYPE_IDS) AS LU_CASE_TYPE_IDS
FROM LU_CALLING_REQUEST_CASE
WHERE LU_CALLING_REQUEST_CASE_ID = :callingRequestCaseId
""")
Optional findByCallingRequestCaseId(
@Param("callingRequestCaseId") Long callingRequestCaseId);

}


Wie kann das Repo so optimiert werden, dass es weniger Codestruktur in der Repo-Ebene anstelle von dichtem SQL-Code hat, indem Java-Objekte als JSON gespeichert und das Framework als Beispiel verstanden werden? findAllByCommonInfo_IsActive(boolean isActive ) und es durchsucht alle Datensätze und überprüft, ob isActive aus dem Common Info JSON wahr oder falsch ist?

Quick Reply

Change Text Case: 
   
  • Similar Topics
    Replies
    Views
    Last post