-
Notifications
You must be signed in to change notification settings - Fork 188
/
SqlGeneration.java
138 lines (118 loc) · 6.18 KB
/
SqlGeneration.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
package com.evolveum.midpoint.repo.sql.pure;
import static com.querydsl.core.group.GroupBy.groupBy;
import static com.evolveum.midpoint.repo.sql.metamodel.QAuditDelta.M_AUDIT_DELTA;
import static com.evolveum.midpoint.repo.sql.metamodel.QAuditEventRecord.M_AUDIT_EVENT;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.function.BiConsumer;
import com.querydsl.core.Tuple;
import com.querydsl.core.group.GroupBy;
import com.querydsl.sql.RelationalPathBase;
import com.querydsl.sql.SQLQuery;
import com.querydsl.sql.SQLQueryFactory;
import com.querydsl.sql.SQLTemplates;
import org.jetbrains.annotations.Nullable;
// TODO MID-6319 must go after done
@Deprecated
public class SqlGeneration {
public static void main(String[] args) throws Exception {
org.h2.Driver.load();
/* this requires querydsl-sql-codegen
java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:h2:tcp://localhost:5437/midpoint", "sa", "");
com.querydsl.sql.codegen.MetaDataExporter exporter = new com.querydsl.sql.codegen.MetaDataExporter();
exporter.setPackageName("com.myproject.mydomain");
exporter.setTargetFolder(new java.io.File("target/generated-sources/java"));
exporter.setBeanSerializer(new com.querydsl.codegen.BeanSerializer());
exporter.export(conn.getMetaData());
*/
examples();
}
private static void examples() {
System.out.println(M_AUDIT_EVENT);
System.out.println("\nColumns: " + M_AUDIT_EVENT.getColumns());
System.out.println("\nAnnotated element: " + M_AUDIT_EVENT.getAnnotatedElement());
System.out.println("\nFKs: " + M_AUDIT_EVENT.getForeignKeys());
System.out.println("\nInverse FKs: " + M_AUDIT_EVENT.getInverseForeignKeys());
System.out.println();
SQLQueryFactory queryFactory = new SQLQueryFactory(SQLTemplates.DEFAULT, () ->
getConnection());
System.out.println("audit size = " + queryFactory.selectFrom(M_AUDIT_EVENT).fetchCount());
SQLQuery<Tuple> query = queryFactory
.select(M_AUDIT_EVENT, M_AUDIT_DELTA)
// .select(M_AUDIT_EVENT.id, M_AUDIT_DELTA.checksum)
// .from(M_AUDIT_EVENT)
// leftJoin if we want also events without deltas
// .join(M_AUDIT_EVENT._auditDeltaFk, M_AUDIT_DELTA)
// alternatively:
// .join(M_AUDIT_DELTA).on(M_AUDIT_DELTA.recordId.eq(M_AUDIT_EVENT.id))
// .orderBy(M_AUDIT_EVENT.id.asc())
.from(M_AUDIT_EVENT, M_AUDIT_DELTA)
.where(M_AUDIT_EVENT.id.eq(M_AUDIT_DELTA.recordId)) // this replaces "join-on", but only inner
.where(M_AUDIT_EVENT.id.eq(452L));
List<Tuple> result = query.fetch();
System.out.println("result = " + result);
System.out.println("\nsize: " + result.size());
System.out.println("\ncount: " + query.transform(groupBy(M_AUDIT_EVENT.id).as(M_AUDIT_DELTA.count())));
Map<?, ?> transform = query.transform(GroupBy.groupBy(M_AUDIT_EVENT.id).as(GroupBy.list(M_AUDIT_DELTA)));
System.out.println("transform = " + transform);
// "manual" transformation of one-to-many to proper graph
List<Tuple> plainResult = queryFactory
.select(M_AUDIT_EVENT, M_AUDIT_DELTA)
.from(M_AUDIT_EVENT)
.leftJoin(M_AUDIT_EVENT._auditDeltaFk, M_AUDIT_DELTA)
// alternatively:
// .leftJoin(M_AUDIT_DELTA).on(M_AUDIT_DELTA.recordId.eq(M_AUDIT_EVENT.id))
// .orderBy(M_AUDIT_EVENT.id.asc())
.where(M_AUDIT_EVENT.id.eq(452L))
.fetch();
Map<MAuditEventRecord, Collection<MAuditDelta>> resultMap =
mapOneToMany(plainResult, M_AUDIT_EVENT, M_AUDIT_DELTA, (o, m) -> o.addDelta(m));
System.out.println("\nFinal result" + resultMap);
System.out.println("deltas for 1st item: " + resultMap.keySet().iterator().next().deltas);
}
/**
* Resolves one-to-many relations between two paths from the {@link Tuple}-based result.
* Returns map with "one" entities as keys (preserving original order) and related "many"
* entities as a collection in the value for each key.
* Optional accumulator can call further processing on both objects for each "many" item.
* <p>
* Note that proper equals/hashCode must be implemented for {@code <O>} type.
*
* @param rawResult collection of tuples, unprocessed result
* @param onePath path expression designating "one" role of the relationship
* @param manyPath path expression designating "many" role of the relationship
* @param manyAccumulator optional, called for each row with respective "one" and "many" items
* @param <O> type of "one" role
* @param <M> type of "many" role
* @return map of one->[many*] with keys in the original iterating order
*/
private static <O, M> Map<O, Collection<M>> mapOneToMany(
Collection<Tuple> rawResult,
RelationalPathBase<O> onePath,
RelationalPathBase<M> manyPath,
@Nullable BiConsumer<O, M> manyAccumulator) {
Map<O, O> canonicalKey = new HashMap<>();
Map<O, Collection<M>> result = new LinkedHashMap<>();
for (Tuple row : rawResult) {
O oneItem = Objects.requireNonNull(row.get(onePath),
"result for path " + onePath + " not found in tuple " + row);
M manyItem = Objects.requireNonNull(row.get(manyPath),
"result for path " + manyPath + " not found in tuple " + row);
oneItem = canonicalKey.computeIfAbsent(oneItem, v -> v);
result.computeIfAbsent(oneItem, o -> new ArrayList<>())
.add(manyItem);
if (manyAccumulator != null) {
manyAccumulator.accept(oneItem, manyItem);
}
}
return result;
}
private static Connection getConnection() {
try {
return java.sql.DriverManager.getConnection("jdbc:h2:tcp://localhost:5437/midpoint", "sa", "");
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}