From c5ea6785a5ce16ce9c856e220ced92091b9e1ef5 Mon Sep 17 00:00:00 2001 From: James Moger Date: Mon, 7 May 2012 13:46:59 -0400 Subject: [PATCH] Draft support for "where xxx in(select bbb from...)" --- docs/05_releases.mkd | 5 ++++ src/com/iciql/Constants.java | 6 ++-- src/com/iciql/Query.java | 9 ++++++ src/com/iciql/QueryCondition.java | 5 ++++ src/com/iciql/QueryWhere.java | 32 ++++++++++++++++++++++ src/com/iciql/SubQuery.java | 32 ++++++++++++++++++++++ src/com/iciql/SubQueryCondition.java | 41 ++++++++++++++++++++++++++++ tests/com/iciql/test/JoinTest.java | 12 ++++++++ 8 files changed, 139 insertions(+), 3 deletions(-) create mode 100644 src/com/iciql/SubQuery.java create mode 100644 src/com/iciql/SubQueryCondition.java diff --git a/docs/05_releases.mkd b/docs/05_releases.mkd index 934e710..37de385 100644 --- a/docs/05_releases.mkd +++ b/docs/05_releases.mkd @@ -6,6 +6,11 @@ **%VERSION%** ([zip](http://code.google.com/p/iciql/downloads/detail?name=%ZIP%)|[jar](http://code.google.com/p/iciql/downloads/detail?name=%JAR%))   *released %BUILDDATE%* +- Added support for single column subquery *select name, address from user_table where user_id in (select user_id from invoice table where paid = false)* +- Added support for left outer join (Github/backpaper0) + +**0.7.10**   *released 2012-01-27* + - Fixed default String value bug where a default empty string threw an IndexOutOfBounds exception **0.7.9**   *released 2012-01-24* diff --git a/src/com/iciql/Constants.java b/src/com/iciql/Constants.java index 2ce664a..fc27ace 100644 --- a/src/com/iciql/Constants.java +++ b/src/com/iciql/Constants.java @@ -25,14 +25,14 @@ public class Constants { // The build script extracts this exact line so be careful editing it // and only use A-Z a-z 0-9 .-_ in the string. - public static final String VERSION = "0.7.10"; + public static final String VERSION = "1.0.0-SNAPSHOT"; // The build script extracts this exact line so be careful editing it // and only use A-Z a-z 0-9 .-_ in the string. - public static final String VERSION_DATE = "2012-01-27"; + public static final String VERSION_DATE = "2012-05-07"; // The build script extracts this exact line so be careful editing it // and only use A-Z a-z 0-9 .-_ in the string. - public static final String API_CURRENT = "13"; + public static final String API_CURRENT = "14"; } diff --git a/src/com/iciql/Query.java b/src/com/iciql/Query.java index c2f81af..6c0ecf9 100644 --- a/src/com/iciql/Query.java +++ b/src/com/iciql/Query.java @@ -203,6 +203,15 @@ public class Query { return stat.toSQL().trim(); } + String toSubQuery(Z z) { + SQLStatement stat = getSelectStatement(false); + SelectColumn col = aliasMap.get(z); + String columnName = col.getFieldDefinition().columnName; + stat.appendColumn(columnName); + appendFromWhere(stat); + return stat.toSQL(); + } + private List select(boolean distinct) { List result = Utils.newArrayList(); TableDefinition def = from.getAliasDefinition(); diff --git a/src/com/iciql/QueryCondition.java b/src/com/iciql/QueryCondition.java index 2595531..9613b1b 100644 --- a/src/com/iciql/QueryCondition.java +++ b/src/com/iciql/QueryCondition.java @@ -36,6 +36,11 @@ public class QueryCondition { this.x = x; } + public QueryWhere in(SubQuery q) { + query.addConditionToken(new SubQueryCondition(x, q)); + return new QueryWhere(query); + } + public QueryWhere is(A y) { query.addConditionToken(new Condition(x, y, CompareType.EQUAL)); return new QueryWhere(query); diff --git a/src/com/iciql/QueryWhere.java b/src/com/iciql/QueryWhere.java index 31228c9..3f1afe1 100644 --- a/src/com/iciql/QueryWhere.java +++ b/src/com/iciql/QueryWhere.java @@ -289,7 +289,39 @@ public class QueryWhere { public String toSQL(boolean distinct, K k) { return query.toSQL(distinct, k); } + + public SubQuery subQuery(Z x) { + return new SubQuery(query, x); + } + + public SubQuery subQuery(boolean x) { + return subQuery(query.getPrimitiveAliasByValue(x)); + } + + public SubQuery subQuery(byte x) { + return subQuery(query.getPrimitiveAliasByValue(x)); + } + + public SubQuery subQuery(short x) { + return subQuery(query.getPrimitiveAliasByValue(x)); + } + public SubQuery subQuery(int x) { + return subQuery(query.getPrimitiveAliasByValue(x)); + } + + public SubQuery subQuery(long x) { + return subQuery(query.getPrimitiveAliasByValue(x)); + } + + public SubQuery subQuery(float x) { + return subQuery(query.getPrimitiveAliasByValue(x)); + } + + public SubQuery subQuery(double x) { + return subQuery(query.getPrimitiveAliasByValue(x)); + } + public List select(Z x) { return query.select(x); } diff --git a/src/com/iciql/SubQuery.java b/src/com/iciql/SubQuery.java new file mode 100644 index 0000000..398d214 --- /dev/null +++ b/src/com/iciql/SubQuery.java @@ -0,0 +1,32 @@ +/* + * Copyright 2012 James Moger. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package com.iciql; + +public class SubQuery { + + final Query query; + final Z z; + + public SubQuery(Query query, Z x) { + this.query = query; + this.z = x; + } + + public void appendSQL(SQLStatement stat) { + stat.appendSQL(query.toSubQuery(z)); + } +} diff --git a/src/com/iciql/SubQueryCondition.java b/src/com/iciql/SubQueryCondition.java new file mode 100644 index 0000000..effea3b --- /dev/null +++ b/src/com/iciql/SubQueryCondition.java @@ -0,0 +1,41 @@ +/* + * Copyright 2012 James Moger. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package com.iciql; + +/** + * A condition that contains a subquery. + * + * @param + * the operand type + */ + +class SubQueryCondition implements Token { + A x; + SubQuery subquery; + + SubQueryCondition(A x, SubQuery subquery) { + this.x = x; + this.subquery = subquery; + } + + public void appendSQL(SQLStatement stat, Query query) { + query.appendSQL(stat, null, x); + stat.appendSQL(" in ("); + subquery.appendSQL(stat); + stat.appendSQL(")"); + } +} diff --git a/tests/com/iciql/test/JoinTest.java b/tests/com/iciql/test/JoinTest.java index 1fea444..3e36eff 100644 --- a/tests/com/iciql/test/JoinTest.java +++ b/tests/com/iciql/test/JoinTest.java @@ -28,6 +28,7 @@ import org.junit.Test; import com.iciql.Db; import com.iciql.Iciql.IQColumn; import com.iciql.Iciql.IQTable; +import com.iciql.QueryWhere; /** * Tests of Joins. @@ -91,6 +92,17 @@ public class JoinTest { assertEquals(4, notes.get(0).id); } + @Test + public void testSubQuery() throws Exception { + final UserId u = new UserId(); + final UserNote n = new UserNote(); + + QueryWhere q = db.from(u).where(u.id).in(db.from(n).where(n.userId).exceeds(0).subQuery(n.userId)); + assertEquals("SELECT * FROM UserId WHERE id in (SELECT userId FROM UserNote WHERE userId > 0 )", q.toSQL()); + List notes = q.select(); + assertEquals(3, notes.size()); + } + @IQTable public static class UserId { -- 2.39.5