Skip to content

Commit b7682ad

Browse files
joshmgjoshmg
committed
Added alternate IN clause format support.
1 parent 9b3976e commit b7682ad

File tree

3 files changed

+237
-20
lines changed

3 files changed

+237
-20
lines changed

‎build.gradle‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
group 'com.softwareverde'
2-
version '3.2.3'
2+
version '3.2.4'
33

44
apply plugin: 'java'
55
apply plugin: 'java-library'

‎src/main/java/com/softwareverde/database/query/Query.java‎

Lines changed: 101 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,20 @@
1616
public class Query {
1717
public static final TypedParameter NULL = TypedParameter.NULL;
1818
public static final String NULL_STRING = "NULL";
19+
public static final String TRUE_STRING = "TRUE";
20+
21+
protected static final Pattern IN_CLAUSE_PATTERN = Pattern.compile("((\\([^()]+\\))|(\\w+))? IN[ ]*\\(\\?\\)");
22+
protected static final Pattern TUPLE_PATTERN = Pattern.compile("([^,()\\s]+)");
23+
24+
protected static class ExtendedInClauseParameters {
25+
public final List<InClauseParameter> inClauseParameters;
26+
public final Boolean isExtendedInClause;
27+
28+
public ExtendedInClauseParameters(final List<InClauseParameter> inClauseParameters, final Boolean isExtendedInClause) {
29+
this.inClauseParameters = inClauseParameters;
30+
this.isExtendedInClause = isExtendedInClause;
31+
}
32+
}
1933

2034
protected static StringBuilder buildParenthesisList(final Integer parameterCount) {
2135
final StringBuilder stringBuilder = new StringBuilder();
@@ -64,25 +78,69 @@ protected static String buildInClause(final List<InClauseParameter> inClausePara
6478
return stringBuilder.toString();
6579
}
6680

81+
protected static String buildAndList(final List<String> columnNames) {
82+
final StringBuilder stringBuilder = new StringBuilder();
83+
84+
String separator = "";
85+
for (final String columnName : columnNames) {
86+
stringBuilder.append(separator);
87+
stringBuilder.append(columnName);
88+
stringBuilder.append(" = ?");
89+
separator = " AND ";
90+
}
91+
92+
return stringBuilder.toString();
93+
}
94+
95+
protected static String buildExpandedWhereInClause(final List<String> columnNames, final Integer tupleCount) {
96+
if (tupleCount == 0) { return TRUE_STRING; }
97+
98+
final StringBuilder stringBuilder = new StringBuilder();
99+
String separator = "";
100+
for (int i = 0; i < tupleCount; ++i) {
101+
stringBuilder.append(separator);
102+
stringBuilder.append("(");
103+
104+
stringBuilder.append(Query.buildAndList(columnNames));
105+
106+
stringBuilder.append(")");
107+
108+
separator = " OR ";
109+
}
110+
return stringBuilder.toString();
111+
}
112+
67113
protected final String _query;
68114
protected final MutableList<TypedParameter> _parameters;
69115
protected final ParameterFactory _parameterFactory;
70116

71-
protected final MutableList<List<InClauseParameter>> _inClauseParameters = new MutableList<List<InClauseParameter>>();
117+
protected final MutableList<ExtendedInClauseParameters> _inClauseParameters = new MutableList<ExtendedInClauseParameters>();
72118
protected final MutableList<Integer> _inClauseParameterIndexes = new MutableList<Integer>();
73119
protected Integer _nextParameterIndex = 0;
74120

75-
protected void _setInClauseParameters(final InClauseParameter inClauseParameter, final InClauseParameter[] extraInClauseParameters) {
121+
protected void _setInClauseParameters(final InClauseParameter inClauseParameter, final InClauseParameter[] extraInClauseParameters, final Boolean enableExpandedInClause) {
76122
final MutableList<InClauseParameter> valueTuples = new MutableList<InClauseParameter>((extraInClauseParameters != null ? extraInClauseParameters.length : 0) + 1);
77-
valueTuples.add(inClauseParameter);
123+
valueTuples.add(Util.coalesce(inClauseParameter, InClauseParameter.NULL));
78124

79125
if (extraInClauseParameters != null) {
80126
for (final InClauseParameter extraInClauseParameter : extraInClauseParameters) {
81127
valueTuples.add(extraInClauseParameter);
82128
}
83129
}
84130

85-
_inClauseParameters.add(valueTuples);
131+
_inClauseParameters.add(new ExtendedInClauseParameters(valueTuples, enableExpandedInClause));
132+
_inClauseParameterIndexes.add(_nextParameterIndex);
133+
_nextParameterIndex += 1;
134+
}
135+
136+
protected <T> void _setInClauseParameters(final Iterable<? extends T> values, final ValueExtractor<T> valueExtractor, final Boolean enableExpandedInClause) {
137+
final MutableList<InClauseParameter> typedParameters = new MutableList<InClauseParameter>();
138+
for (final T value : values) {
139+
final InClauseParameter inClauseParameter = valueExtractor.extractValues(value);
140+
typedParameters.add(Util.coalesce(inClauseParameter, InClauseParameter.NULL));
141+
}
142+
143+
_inClauseParameters.add(new ExtendedInClauseParameters(typedParameters, enableExpandedInClause));
86144
_inClauseParameterIndexes.add(_nextParameterIndex);
87145
_nextParameterIndex += 1;
88146
}
@@ -211,28 +269,32 @@ public Query setNullParameter() {
211269
}
212270

213271
public Query setInClauseParameters(final InClauseParameter inClauseParameter, final InClauseParameter... extraInClauseParameters) {
214-
_setInClauseParameters(Util.coalesce(inClauseParameter, InClauseParameter.NULL), extraInClauseParameters);
272+
_setInClauseParameters(inClauseParameter, extraInClauseParameters, false);
215273
return this;
216274
}
217275

218276
public <T> Query setInClauseParameters(final Iterable<? extends T> values, final ValueExtractor<T> valueExtractor) {
219-
final MutableList<InClauseParameter> typedParameters = new MutableList<InClauseParameter>();
220-
for (final T value : values) {
221-
final InClauseParameter inClauseParameter = valueExtractor.extractValues(value);
222-
typedParameters.add(Util.coalesce(inClauseParameter, InClauseParameter.NULL));
223-
}
277+
_setInClauseParameters(values, valueExtractor, false);
278+
return this;
279+
}
224280

225-
_inClauseParameters.add(typedParameters);
226-
_inClauseParameterIndexes.add(_nextParameterIndex);
227-
_nextParameterIndex += 1;
281+
public <T> Query setExpandedInClauseParameters(final InClauseParameter inClauseParameter, final InClauseParameter... extraInClauseParameters) {
282+
_setInClauseParameters(inClauseParameter, extraInClauseParameters, true);
283+
return this;
284+
}
285+
286+
public <T> Query setExpandedInClauseParameters(final Iterable<? extends T> values, final ValueExtractor<T> valueExtractor) {
287+
_setInClauseParameters(values, valueExtractor, true);
228288
return this;
229289
}
230290

231291
public String getQueryString() {
232292
final int inClauseCount = _inClauseParameters.getCount();
293+
if (inClauseCount == 0) {
294+
return _query;
295+
}
233296

234-
final Pattern pattern = Pattern.compile("IN[ ]*\\(\\?\\)");
235-
final Matcher matcher = pattern.matcher(_query);
297+
final Matcher matcher = Query.IN_CLAUSE_PATTERN.matcher(_query);
236298

237299
final StringBuffer stringBuffer = new StringBuffer();
238300
{
@@ -242,9 +304,28 @@ public String getQueryString() {
242304
break;
243305
}
244306

245-
final List<InClauseParameter> inClauseValues = _inClauseParameters.get(matchCount);
246-
final String inClause = Query.buildInClause(inClauseValues);
247-
matcher.appendReplacement(stringBuffer, inClause);
307+
final ExtendedInClauseParameters extendedInClauseParameters = _inClauseParameters.get(matchCount);
308+
final List<InClauseParameter> inClauseParameters = extendedInClauseParameters.inClauseParameters;
309+
final Boolean useExpandedInClause = extendedInClauseParameters.isExtendedInClause;
310+
311+
final String rawColumnNames = matcher.group(1);
312+
if (useExpandedInClause) {
313+
final Matcher columnNameMatcher = Query.TUPLE_PATTERN.matcher(rawColumnNames);
314+
315+
final MutableList<String> columnNames = new MutableList<String>();
316+
while (columnNameMatcher.find()) {
317+
final String columnName = columnNameMatcher.group(1);
318+
columnNames.add(columnName);
319+
}
320+
321+
final String inClause = Query.buildExpandedWhereInClause(columnNames, inClauseParameters.getCount());
322+
matcher.appendReplacement(stringBuffer, inClause);
323+
}
324+
else {
325+
final String inClause = Query.buildInClause(inClauseParameters);
326+
matcher.appendReplacement(stringBuffer, (rawColumnNames + " " + inClause));
327+
}
328+
248329
matchCount += 1;
249330
}
250331
matcher.appendTail(stringBuffer);
@@ -278,7 +359,8 @@ public java.util.List<TypedParameter> getParameters() {
278359
final int loopCount = (_parameters.getCount() + inClauseCount);
279360
for (int i = 0; i < loopCount; ++i) {
280361
if (nextInClauseParameterIndex == returnedParameterIndex) {
281-
for (final InClauseParameter inClauseParameter : _inClauseParameters.get(inClauseIndex)) {
362+
final ExtendedInClauseParameters extendedInClauseParameters = _inClauseParameters.get(inClauseIndex);
363+
for (final InClauseParameter inClauseParameter : extendedInClauseParameters.inClauseParameters) {
282364
if (inClauseParameter.getType() == InClauseParameter.Type.NULL) {
283365
parameters.add(TypedParameter.NULL);
284366
}

‎src/test/java/com/softwareverde/database/QueryTests.java‎

Lines changed: 135 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -173,4 +173,139 @@ public InClauseParameter extractValues(final Tuple<String, String> tuple) {
173173
Assert.assertEquals("SELECT id FROM table WHERE (value0, value1) IN ((?,?), (?,?), (?,?))", queryString);
174174
Assert.assertEquals(6, parameters.size());
175175
}
176+
177+
@Test
178+
public void should_create_query_with_tuples_and_regular_parameter() {
179+
// Setup
180+
final Query query = new Query("SELECT id FROM table WHERE (value0, value1) IN (?) AND `key` = ?");
181+
final Long keyValue = 1L;
182+
183+
final ValueExtractor<Tuple<String, String>> stringTupleExtractor = new ValueExtractor<Tuple<String, String>>() {
184+
@Override
185+
public InClauseParameter extractValues(final Tuple<String, String> tuple) {
186+
final TypedParameter typedParameter0 = new TypedParameter(tuple.first);
187+
final TypedParameter typedParameter1 = new TypedParameter(tuple.second);
188+
return new InClauseParameter(typedParameter0, typedParameter1);
189+
}
190+
};
191+
192+
final MutableList<Tuple<String, String>> values = new MutableList<Tuple<String, String>>();
193+
for (int i = 0; i < 3; ++i) {
194+
values.add(new Tuple<String, String>("Value0-" + i, "Value1-" + i));
195+
}
196+
197+
// Action
198+
query.setInClauseParameters(values, stringTupleExtractor);
199+
query.setParameter(keyValue);
200+
201+
final String queryString = query.getQueryString();
202+
final List<TypedParameter> parameters = query.getParameters();
203+
204+
// Assert
205+
Assert.assertEquals("SELECT id FROM table WHERE (value0, value1) IN ((?,?), (?,?), (?,?)) AND `key` = ?", queryString);
206+
Assert.assertEquals(7, parameters.size());
207+
Assert.assertEquals("Value0-0", parameters.get(0).value);
208+
Assert.assertEquals("Value1-0", parameters.get(1).value);
209+
Assert.assertEquals("Value0-1", parameters.get(2).value);
210+
Assert.assertEquals("Value1-1", parameters.get(3).value);
211+
Assert.assertEquals("Value0-2", parameters.get(4).value);
212+
Assert.assertEquals("Value1-2", parameters.get(5).value);
213+
Assert.assertEquals(keyValue, parameters.get(6).value);
214+
}
215+
216+
@Test
217+
public void should_create_query_with_expanded_in_clause() {
218+
// Setup
219+
final Query query = new Query("SELECT id FROM table WHERE key IN (?)");
220+
221+
final MutableList<String> values = new MutableList<String>();
222+
for (int i = 0; i < 3; ++i) {
223+
values.add("Value-" + i);
224+
}
225+
226+
// Action
227+
query.setExpandedInClauseParameters(values, ValueExtractor.STRING);
228+
229+
final String queryString = query.getQueryString();
230+
final List<TypedParameter> parameters = query.getParameters();
231+
232+
// Assert
233+
Assert.assertEquals("SELECT id FROM table WHERE (key = ?) OR (key = ?) OR (key = ?)", queryString);
234+
Assert.assertEquals(3, parameters.size());
235+
Assert.assertEquals("Value-0", parameters.get(0).value);
236+
Assert.assertEquals("Value-1", parameters.get(1).value);
237+
Assert.assertEquals("Value-2", parameters.get(2).value);
238+
}
239+
240+
@Test
241+
public void should_create_query_with_expanded_in_clause_with_tuple() {
242+
// Setup
243+
final Query query = new Query("SELECT id FROM table WHERE (key0, key1) IN (?)");
244+
245+
final ValueExtractor<Tuple<String, String>> stringTupleExtractor = new ValueExtractor<Tuple<String, String>>() {
246+
@Override
247+
public InClauseParameter extractValues(final Tuple<String, String> tuple) {
248+
final TypedParameter typedParameter0 = new TypedParameter(tuple.first);
249+
final TypedParameter typedParameter1 = new TypedParameter(tuple.second);
250+
return new InClauseParameter(typedParameter0, typedParameter1);
251+
}
252+
};
253+
254+
final MutableList<Tuple<String, String>> values = new MutableList<Tuple<String, String>>();
255+
for (int i = 0; i < 3; ++i) {
256+
values.add(new Tuple<String, String>("Value0-" + i, "Value1-" + i));
257+
}
258+
259+
// Action
260+
query.setExpandedInClauseParameters(values, stringTupleExtractor);
261+
262+
final String queryString = query.getQueryString();
263+
final List<TypedParameter> parameters = query.getParameters();
264+
265+
// Assert
266+
Assert.assertEquals("SELECT id FROM table WHERE (key0 = ? AND key1 = ?) OR (key0 = ? AND key1 = ?) OR (key0 = ? AND key1 = ?)", queryString);
267+
Assert.assertEquals(6, parameters.size());
268+
Assert.assertEquals("Value0-0", parameters.get(0).value);
269+
Assert.assertEquals("Value1-0", parameters.get(1).value);
270+
Assert.assertEquals("Value0-1", parameters.get(2).value);
271+
Assert.assertEquals("Value1-1", parameters.get(3).value);
272+
Assert.assertEquals("Value0-2", parameters.get(4).value);
273+
Assert.assertEquals("Value1-2", parameters.get(5).value);
274+
}
275+
276+
@Test
277+
public void should_create_query_with_expanded_in_clause_with_escaped_tuple() {
278+
// Setup
279+
final Query query = new Query("SELECT id FROM table WHERE (`key0`, `key1`) IN (?)");
280+
281+
final ValueExtractor<Tuple<String, String>> stringTupleExtractor = new ValueExtractor<Tuple<String, String>>() {
282+
@Override
283+
public InClauseParameter extractValues(final Tuple<String, String> tuple) {
284+
final TypedParameter typedParameter0 = new TypedParameter(tuple.first);
285+
final TypedParameter typedParameter1 = new TypedParameter(tuple.second);
286+
return new InClauseParameter(typedParameter0, typedParameter1);
287+
}
288+
};
289+
290+
final MutableList<Tuple<String, String>> values = new MutableList<Tuple<String, String>>();
291+
for (int i = 0; i < 3; ++i) {
292+
values.add(new Tuple<String, String>("Value0-" + i, "Value1-" + i));
293+
}
294+
295+
// Action
296+
query.setExpandedInClauseParameters(values, stringTupleExtractor);
297+
298+
final String queryString = query.getQueryString();
299+
final List<TypedParameter> parameters = query.getParameters();
300+
301+
// Assert
302+
Assert.assertEquals("SELECT id FROM table WHERE (`key0` = ? AND `key1` = ?) OR (`key0` = ? AND `key1` = ?) OR (`key0` = ? AND `key1` = ?)", queryString);
303+
Assert.assertEquals(6, parameters.size());
304+
Assert.assertEquals("Value0-0", parameters.get(0).value);
305+
Assert.assertEquals("Value1-0", parameters.get(1).value);
306+
Assert.assertEquals("Value0-1", parameters.get(2).value);
307+
Assert.assertEquals("Value1-1", parameters.get(3).value);
308+
Assert.assertEquals("Value0-2", parameters.get(4).value);
309+
Assert.assertEquals("Value1-2", parameters.get(5).value);
310+
}
176311
}

0 commit comments

Comments
 (0)