1 package de.dlr.shepard.data.spatialdata.repositories;
2
3 import com.fasterxml.jackson.databind.ObjectMapper;
4 import de.dlr.shepard.data.spatialdata.io.FilterCondition;
5 import java.util.HashMap;
6 import java.util.List;
7 import java.util.Map;
8
9 public class NativeQueryStringBuilder {
10
11 private String selectString = "";
12
13 private StringBuilder whereConditions = new StringBuilder();
14
15 private String timeCondition = "";
16
17 private StringBuilder jsonConditions = new StringBuilder();
18
19 private StringBuilder measurementsFilterConditions = new StringBuilder();
20
21 private String geometryFilterCondition = "";
22 private Map<String, Object> queryParameters = new HashMap<>();
23
24 private String limitClause = "";
25
26 private String skipClause = "";
27
28 public Map<String, Object> getQueryParameters() {
29 return queryParameters;
30 }
31
32 public NativeQueryStringBuilder select(String tableName, String[] columns) {
33 selectString = "SELECT %s FROM %s".formatted(String.join(", ", columns), tableName).formatted();
34 return this;
35 }
36
37 public NativeQueryStringBuilder addWhereCondition(String parameterName, Object value) {
38 if (value == null) return this;
39 if (value.getClass() == String.class) {
40 whereConditions.append(" AND %s = '%s'".formatted(parameterName, value));
41 } else {
42 whereConditions.append(" AND %s = %s".formatted(parameterName, value));
43 }
44 return this;
45 }
46
47 public NativeQueryStringBuilder addTimeCondition(String parameterName, Long timestampStart, Long timestampEnd) {
48 if (timestampStart == null && timestampEnd == null) return this;
49 var timeQuery = new StringBuilder();
50 if (timestampStart != null) {
51 timeQuery.append(" AND %s >= %s".formatted(parameterName, timestampStart));
52 }
53 if (timestampEnd != null) {
54 timeQuery.append(" AND %s <= %s".formatted(parameterName, timestampEnd));
55 }
56 timeCondition = timeQuery.toString();
57 return this;
58 }
59
60 public NativeQueryStringBuilder addJsonContainsCondition(String parameterName, Map<String, Object> filter) {
61 if (filter.isEmpty()) return this;
62 try {
63 var mapper = new ObjectMapper();
64 var filterAsString = mapper.writeValueAsString(filter);
65 jsonConditions.append(" AND %s @> '%s'".formatted(parameterName, filterAsString));
66 } catch (Exception e) {
67 throw new RuntimeException(e);
68 }
69 return this;
70 }
71
72 public NativeQueryStringBuilder addKNNGeometryCondition(double x1, double y1, double z1, int k) {
73 geometryFilterCondition = " ORDER BY position <<->> ST_MakePoint(:x1, :y1, :z1) LIMIT :k";
74 queryParameters.put("x1", x1);
75 queryParameters.put("y1", y1);
76 queryParameters.put("z1", z1);
77 queryParameters.put("k", k);
78 return this;
79 }
80
81 public NativeQueryStringBuilder addAABBGeometryCondition(
82 double x1,
83 double y1,
84 double z1,
85 double x2,
86 double y2,
87 double z2
88 ) {
89 geometryFilterCondition =
90 " AND position &&& ST_3DMakeBox(ST_MakePoint(:x1, :y1, :z1), ST_MakePoint(:x2, :y2, :z2))";
91 queryParameters.put("x1", x1);
92 queryParameters.put("y1", y1);
93 queryParameters.put("z1", z1);
94 queryParameters.put("x2", x2);
95 queryParameters.put("y2", y2);
96 queryParameters.put("z2", z2);
97 return this;
98 }
99
100 public NativeQueryStringBuilder addBSGeometryCondition(double x1, double y1, double z1, double radius) {
101 geometryFilterCondition = " AND ST_3DDWithin(position, ST_MakePoint(:x1, :y1, :z1), :radius)";
102 queryParameters.put("x1", x1);
103 queryParameters.put("y1", y1);
104 queryParameters.put("z1", z1);
105 queryParameters.put("radius", radius);
106 return this;
107 }
108
109 public NativeQueryStringBuilder addJsonFilterConditions(
110 String parameterName,
111 List<FilterCondition> measurementsFilter
112 ) {
113 if (measurementsFilter.isEmpty()) return this;
114 measurementsFilter.forEach(filterCondition ->
115 measurementsFilterConditions.append(
116 " AND jsonb_typeof(%s #> '{%s}') = 'number' AND (%s #>> '{%s}')::NUMERIC %s %s".formatted(
117 parameterName,
118 filterCondition.getKey(),
119 parameterName,
120 filterCondition.getKey(),
121 filterCondition.getOperator().getOperatorString(),
122 filterCondition.getValue()
123 )
124 )
125 );
126 return this;
127 }
128
129 public NativeQueryStringBuilder addLimitClause(Integer limit) {
130 if (limit == null) return this;
131 limitClause = " LIMIT %d".formatted(limit);
132 return this;
133 }
134
135 public NativeQueryStringBuilder addSkipClause(Integer skip) {
136 if (skip == null) return this;
137 skipClause = " AND id %% %d = 0".formatted(skip);
138 return this;
139 }
140
141 public String build() {
142 var res = String.join(
143 "",
144 selectString,
145 " WHERE 1 = 1",
146 whereConditions.toString(),
147 timeCondition,
148 jsonConditions.toString(),
149 measurementsFilterConditions.toString(),
150 geometryFilterCondition,
151 skipClause,
152 limitClause,
153 ";"
154 );
155 return res;
156 }
157 }