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?
Speichern Sie Java-Objekt als JSON in der DB-Tabelle in Spring Data JDBC und suchen Sie bald mit gespeichertem JSON ⇐ Java
-
- Similar Topics
- Replies
- Views
- Last post