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 = String.format("SELECT %s FROM %s".formatted(String.join(", ", columns), tableName));
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(String.format(" AND %s = '%s'", parameterName, value));
41 } else {
42 whereConditions.append(String.format(" AND %s = %s", 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(String.format(" AND %s >= %s", parameterName, timestampStart));
52 }
53 if (timestampEnd != null) {
54 timeQuery.append(String.format(" AND %s <= %s", 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(String.format(" AND %s @> '%s'", 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 String.format(
117 " AND jsonb_typeof(%s #> '{%s}') = 'number' AND (%s #>> '{%s}')::NUMERIC %s %s",
118 parameterName,
119 filterCondition.getKey(),
120 parameterName,
121 filterCondition.getKey(),
122 filterCondition.getOperator().getOperatorString(),
123 filterCondition.getValue()
124 )
125 )
126 );
127 return this;
128 }
129
130 public NativeQueryStringBuilder addLimitClause(Integer limit) {
131 if (limit == null) return this;
132 limitClause = String.format(" LIMIT %d", limit);
133 return this;
134 }
135
136 public NativeQueryStringBuilder addSkipClause(Integer skip) {
137 if (skip == null) return this;
138 skipClause = String.format(" AND id %% %d = 0", skip);
139 return this;
140 }
141
142 public String build() {
143 var res = String.join(
144 "",
145 selectString,
146 " WHERE 1 = 1",
147 whereConditions.toString(),
148 timeCondition,
149 jsonConditions.toString(),
150 measurementsFilterConditions.toString(),
151 geometryFilterCondition,
152 skipClause,
153 limitClause,
154 ";"
155 );
156 return res;
157 }
158 }