Return-Path: X-Original-To: apmail-cayenne-commits-archive@www.apache.org Delivered-To: apmail-cayenne-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 66824F725 for ; Fri, 14 Nov 2014 17:47:03 +0000 (UTC) Received: (qmail 73751 invoked by uid 500); 14 Nov 2014 17:47:03 -0000 Delivered-To: apmail-cayenne-commits-archive@cayenne.apache.org Received: (qmail 73695 invoked by uid 500); 14 Nov 2014 17:47:03 -0000 Mailing-List: contact commits-help@cayenne.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@cayenne.apache.org Delivered-To: mailing list commits@cayenne.apache.org Received: (qmail 73614 invoked by uid 99); 14 Nov 2014 17:47:03 -0000 Received: from tyr.zones.apache.org (HELO tyr.zones.apache.org) (140.211.11.114) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 14 Nov 2014 17:47:03 +0000 Received: by tyr.zones.apache.org (Postfix, from userid 65534) id 05C25941448; Fri, 14 Nov 2014 17:47:03 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: aadamchik@apache.org To: commits@cayenne.apache.org Date: Fri, 14 Nov 2014 17:47:04 -0000 Message-Id: <486f1765b41945ad88f7d6f89162ac0e@git.apache.org> In-Reply-To: References: X-Mailer: ASF-Git Admin Mailer Subject: [03/50] [abbrv] cayenne git commit: CAY-1966 SQLTemplate/SQLSelect positional parameter binding CAY-1966 SQLTemplate/SQLSelect positional parameter binding * positional parameter bindings in SQLSelect Project: http://git-wip-us.apache.org/repos/asf/cayenne/repo Commit: http://git-wip-us.apache.org/repos/asf/cayenne/commit/8f16b607 Tree: http://git-wip-us.apache.org/repos/asf/cayenne/tree/8f16b607 Diff: http://git-wip-us.apache.org/repos/asf/cayenne/diff/8f16b607 Branch: refs/heads/CAY-1946 Commit: 8f16b60728ac7bb0b54497053d91c6cd5c566359 Parents: 14e9dc3 Author: aadamchik Authored: Sun Nov 2 22:59:04 2014 +0300 Committer: aadamchik Committed: Sun Nov 2 23:17:15 2014 +0300 ---------------------------------------------------------------------- .../org/apache/cayenne/query/SQLSelect.java | 48 +++- .../org/apache/cayenne/query/SQLTemplate.java | 11 +- .../org/apache/cayenne/query/SQLSelectIT.java | 250 ++++++++++--------- docs/doc/src/main/resources/RELEASE-NOTES.txt | 1 + 4 files changed, 179 insertions(+), 131 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/cayenne/blob/8f16b607/cayenne-server/src/main/java/org/apache/cayenne/query/SQLSelect.java ---------------------------------------------------------------------- diff --git a/cayenne-server/src/main/java/org/apache/cayenne/query/SQLSelect.java b/cayenne-server/src/main/java/org/apache/cayenne/query/SQLSelect.java index fc413fa..5509cb4 100644 --- a/cayenne-server/src/main/java/org/apache/cayenne/query/SQLSelect.java +++ b/cayenne-server/src/main/java/org/apache/cayenne/query/SQLSelect.java @@ -18,6 +18,8 @@ ****************************************************************/ package org.apache.cayenne.query; +import java.util.Arrays; +import java.util.Collections; import java.util.HashMap; import java.util.List; import java.util.Map; @@ -90,6 +92,7 @@ public class SQLSelect extends IndirectQuery implements Select { protected QueryCacheStrategy cacheStrategy; protected String[] cacheGroups; protected Map params; + protected List positionalParams; protected CapsStrategy columnNameCaps; protected int limit; protected int offset; @@ -103,7 +106,6 @@ public class SQLSelect extends IndirectQuery implements Select { public SQLSelect(Class persistentType, String sql) { this.persistentType = persistentType; this.sqlBuffer = sql != null ? new StringBuilder(sql) : new StringBuilder(); - this.params = new HashMap(); this.limit = QueryMetadata.FETCH_LIMIT_DEFAULT; this.offset = QueryMetadata.FETCH_OFFSET_DEFAULT; this.pageSize = QueryMetadata.PAGE_SIZE_DEFAULT; @@ -148,26 +150,48 @@ public class SQLSelect extends IndirectQuery implements Select { } public SQLSelect params(String name, Object value) { - params.put(name, value); - this.replacementQuery = null; + params(Collections.singletonMap(name, value)); return this; } @SuppressWarnings({ "rawtypes", "unchecked" }) public SQLSelect params(Map parameters) { - Map bareMap = parameters; - parameters.putAll(bareMap); + + if (this.params == null) { + this.params = new HashMap(parameters); + } else { + Map bareMap = parameters; + this.params.putAll(bareMap); + } + this.replacementQuery = null; + + // since named parameters are specified, resetting positional + // parameters + this.positionalParams = null; + return this; + } + + public SQLSelect paramsArray(Object... params) { + return paramsList(params != null ? Arrays.asList(params) : null); + } + + public SQLSelect paramsList(List params) { + // since named parameters are specified, resetting positional + // parameters + this.params = null; + + this.positionalParams = params; return this; } /** - * Returns a mutable map of parameters that will be bound to SQL. A caller - * is free to add/remove parameters from the returned map as needed. + * Returns an immmutable map of parameters that will be bound to SQL. A + * caller is free to add/remove parameters from the returned map as needed. * Alternatively one may use chained {@link #params(String, Object)} */ public Map getParams() { - return params; + return params != null ? params : Collections. emptyMap(); } @Override @@ -195,7 +219,13 @@ public class SQLSelect extends IndirectQuery implements Select { template.setDefaultTemplate(getSql()); template.setCacheGroups(cacheGroups); template.setCacheStrategy(cacheStrategy); - template.setParams(params); + + if (positionalParams != null) { + template.setParamsArray(positionalParams); + } else { + template.setParams(params); + } + template.setColumnNamesCapitalization(columnNameCaps); template.setFetchLimit(limit); template.setFetchOffset(offset); http://git-wip-us.apache.org/repos/asf/cayenne/blob/8f16b607/cayenne-server/src/main/java/org/apache/cayenne/query/SQLTemplate.java ---------------------------------------------------------------------- diff --git a/cayenne-server/src/main/java/org/apache/cayenne/query/SQLTemplate.java b/cayenne-server/src/main/java/org/apache/cayenne/query/SQLTemplate.java index dd4b176..0241975 100644 --- a/cayenne-server/src/main/java/org/apache/cayenne/query/SQLTemplate.java +++ b/cayenne-server/src/main/java/org/apache/cayenne/query/SQLTemplate.java @@ -19,6 +19,7 @@ package org.apache.cayenne.query; +import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.Collections; @@ -357,11 +358,17 @@ public class SQLTemplate extends AbstractQuery implements ParameterizedQuery, XM * @since 4.0 */ public void setParamsArray(Object... params) { + setParamsList(params != null ? Arrays.asList(params) : null); + } + + /** + * @since 4.0 + */ + public void setParamsList(List params) { // since positional parameters are specified, resetting named // parameters this.parameters = null; - - this.positionalParams = params != null ? Arrays.asList(params) : null; + this.positionalParams = params != null ? new ArrayList(params) : null; } /** http://git-wip-us.apache.org/repos/asf/cayenne/blob/8f16b607/cayenne-server/src/test/java/org/apache/cayenne/query/SQLSelectIT.java ---------------------------------------------------------------------- diff --git a/cayenne-server/src/test/java/org/apache/cayenne/query/SQLSelectIT.java b/cayenne-server/src/test/java/org/apache/cayenne/query/SQLSelectIT.java index bf5d66b..0d7acf0 100644 --- a/cayenne-server/src/test/java/org/apache/cayenne/query/SQLSelectIT.java +++ b/cayenne-server/src/test/java/org/apache/cayenne/query/SQLSelectIT.java @@ -18,6 +18,8 @@ ****************************************************************/ package org.apache.cayenne.query; +import java.util.List; + import org.apache.cayenne.DataRow; import org.apache.cayenne.access.DataContext; import org.apache.cayenne.di.Inject; @@ -27,187 +29,195 @@ import org.apache.cayenne.testdo.testmap.Artist; import org.apache.cayenne.unit.di.server.ServerCase; import org.apache.cayenne.unit.di.server.UseServerRuntime; -import java.util.List; - @UseServerRuntime(ServerCase.TESTMAP_PROJECT) public class SQLSelectIT extends ServerCase { - @Inject - private DataContext context; + @Inject + private DataContext context; + + @Inject + private DBHelper dbHelper; + + @Override + protected void setUpAfterInjection() throws Exception { + dbHelper.deleteAll("PAINTING_INFO"); + dbHelper.deleteAll("PAINTING"); + dbHelper.deleteAll("ARTIST_EXHIBIT"); + dbHelper.deleteAll("ARTIST_GROUP"); + dbHelper.deleteAll("ARTIST"); + } + + protected void createArtistsDataSet() throws Exception { + TableHelper tArtist = new TableHelper(dbHelper, "ARTIST"); + tArtist.setColumns("ARTIST_ID", "ARTIST_NAME", "DATE_OF_BIRTH"); + + long dateBase = System.currentTimeMillis(); - @Inject - private DBHelper dbHelper; + for (int i = 1; i <= 20; i++) { + tArtist.insert(i, "artist" + i, new java.sql.Date(dateBase + 10000 * i)); + } + } - @Override - protected void setUpAfterInjection() throws Exception { - dbHelper.deleteAll("PAINTING_INFO"); - dbHelper.deleteAll("PAINTING"); - dbHelper.deleteAll("ARTIST_EXHIBIT"); - dbHelper.deleteAll("ARTIST_GROUP"); - dbHelper.deleteAll("ARTIST"); - } + public void test_DataRows_DataMapNameRoot() throws Exception { - protected void createArtistsDataSet() throws Exception { - TableHelper tArtist = new TableHelper(dbHelper, "ARTIST"); - tArtist.setColumns("ARTIST_ID", "ARTIST_NAME", "DATE_OF_BIRTH"); + createArtistsDataSet(); - long dateBase = System.currentTimeMillis(); + SQLSelect q1 = SQLSelect.dataRowQuery("tstmap", "SELECT * FROM ARTIST"); + assertTrue(q1.isFetchingDataRows()); - for (int i = 1; i <= 20; i++) { - tArtist.insert(i, "artist" + i, new java.sql.Date(dateBase + 10000 * i)); - } - } + List result = context.select(q1); + assertEquals(20, result.size()); + assertTrue(result.get(0) instanceof DataRow); + } - public void test_DataRows_DataMapNameRoot() throws Exception { + public void test_DataRows_DefaultRoot() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - SQLSelect q1 = SQLSelect.dataRowQuery("tstmap", "SELECT * FROM ARTIST"); - assertTrue(q1.isFetchingDataRows()); + SQLSelect q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST"); + assertTrue(q1.isFetchingDataRows()); - List result = context.select(q1); - assertEquals(20, result.size()); - assertTrue(result.get(0) instanceof DataRow); - } + List result = context.select(q1); + assertEquals(20, result.size()); + assertTrue(result.get(0) instanceof DataRow); + } - public void test_DataRows_DefaultRoot() throws Exception { + public void test_DataRows_ClassRoot() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - SQLSelect q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST"); - assertTrue(q1.isFetchingDataRows()); + SQLSelect q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST"); + assertFalse(q1.isFetchingDataRows()); + List result = context.select(q1); + assertEquals(20, result.size()); + assertTrue(result.get(0) instanceof Artist); + } - List result = context.select(q1); - assertEquals(20, result.size()); - assertTrue(result.get(0) instanceof DataRow); - } + public void test_DataRows_ClassRoot_Parameters() throws Exception { - public void test_DataRows_ClassRoot() throws Exception { + createArtistsDataSet(); - createArtistsDataSet(); + SQLSelect q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a)"); + q1.params("a", "artist3"); - SQLSelect q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST"); - assertFalse(q1.isFetchingDataRows()); - List result = context.select(q1); - assertEquals(20, result.size()); - assertTrue(result.get(0) instanceof Artist); - } + assertFalse(q1.isFetchingDataRows()); + Artist a = context.selectOne(q1); + assertEquals("artist3", a.getArtistName()); + } - public void test_DataRows_ClassRoot_Parameters() throws Exception { + public void test_DataRows_ClassRoot_Bind() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - SQLSelect q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a)"); - q1.getParams().put("a", "artist3"); + SQLSelect q1 = SQLSelect.query(Artist.class, + "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a) OR ARTIST_NAME = #bind($b)"); + q1.params("a", "artist3").params("b", "artist4"); - assertFalse(q1.isFetchingDataRows()); - Artist a = context.selectOne(q1); - assertEquals("artist3", a.getArtistName()); - } + List result = context.select(q1); + assertEquals(2, result.size()); + } - public void test_DataRows_ClassRoot_Bind() throws Exception { + public void test_DataRows_ColumnNameCaps() throws Exception { - createArtistsDataSet(); + SQLSelect q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST WHERE ARTIST_NAME = 'artist2'"); + q1.upperColumnNames(); - SQLSelect q1 = SQLSelect.query(Artist.class, - "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a) OR ARTIST_NAME = #bind($b)"); - q1.params("a", "artist3").params("b", "artist4"); + SQLTemplate r1 = (SQLTemplate) q1.getReplacementQuery(context.getEntityResolver()); + assertEquals(CapsStrategy.UPPER, r1.getColumnNamesCapitalization()); - List result = context.select(q1); - assertEquals(2, result.size()); - } + q1.lowerColumnNames(); + SQLTemplate r2 = (SQLTemplate) q1.getReplacementQuery(context.getEntityResolver()); + assertEquals(CapsStrategy.LOWER, r2.getColumnNamesCapitalization()); + } - public void test_DataRows_ColumnNameCaps() throws Exception { + public void test_DataRows_FetchLimit() throws Exception { - SQLSelect q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST WHERE ARTIST_NAME = 'artist2'"); - q1.upperColumnNames(); + createArtistsDataSet(); - SQLTemplate r1 = (SQLTemplate) q1.getReplacementQuery(context.getEntityResolver()); - assertEquals(CapsStrategy.UPPER, r1.getColumnNamesCapitalization()); + SQLSelect q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST"); + q1.limit(5); - q1.lowerColumnNames(); - SQLTemplate r2 = (SQLTemplate) q1.getReplacementQuery(context.getEntityResolver()); - assertEquals(CapsStrategy.LOWER, r2.getColumnNamesCapitalization()); - } + assertEquals(5, context.select(q1).size()); + } - public void test_DataRows_FetchLimit() throws Exception { + public void test_DataRows_FetchOffset() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - SQLSelect q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST"); - q1.limit(5); + SQLSelect q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST"); + q1.offset(4); - assertEquals(5, context.select(q1).size()); - } + assertEquals(16, context.select(q1).size()); + } - public void test_DataRows_FetchOffset() throws Exception { + public void test_Append() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - SQLSelect q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST"); - q1.offset(4); + SQLSelect q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST") + .append(" WHERE ARTIST_NAME = #bind($a)").params("a", "artist3"); - assertEquals(16, context.select(q1).size()); - } + List result = context.select(q1); + assertEquals(1, result.size()); + } - public void test_Append() throws Exception { + public void test_Select() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - SQLSelect q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST") - .append(" WHERE ARTIST_NAME = #bind($a)").params("a", "artist3"); + List result = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a)") + .params("a", "artist3").select(context); - List result = context.select(q1); - assertEquals(1, result.size()); - } + assertEquals(1, result.size()); + } - public void test_Select() throws Exception { + public void test_SelectOne() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - List result = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a)") - .params("a", "artist3").select(context); + Artist a = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a)") + .params("a", "artist3").selectOne(context); - assertEquals(1, result.size()); - } + assertEquals("artist3", a.getArtistName()); + } - public void test_SelectOne() throws Exception { + public void test_SelectLong() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - Artist a = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a)") - .params("a", "artist3").selectOne(context); + long id = SQLSelect.scalarQuery(Long.class, "SELECT ARTIST_ID FROM ARTIST WHERE ARTIST_NAME = #bind($a)") + .params("a", "artist3").selectOne(context); - assertEquals("artist3", a.getArtistName()); - } + assertEquals(3l, id); + } - public void test_SelectLong() throws Exception { + public void test_SelectLongArray() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - long id = SQLSelect.scalarQuery(Long.class, "SELECT ARTIST_ID FROM ARTIST WHERE ARTIST_NAME = #bind($a)") - .params("a", "artist3").selectOne(context); + List ids = SQLSelect.scalarQuery(Long.class, "SELECT ARTIST_ID FROM ARTIST ORDER BY ARTIST_ID").select( + context); - assertEquals(3l, id); - } + assertEquals(20, ids.size()); + assertEquals(2l, ids.get(1).longValue()); + } - public void test_SelectLongArray() throws Exception { + public void test_SelectCount() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - List ids = SQLSelect.scalarQuery(Long.class, "SELECT ARTIST_ID FROM ARTIST ORDER BY ARTIST_ID").select( - context); + int c = SQLSelect.scalarQuery(Integer.class, "SELECT #result('COUNT(*)' 'int') FROM ARTIST").selectOne(context); - assertEquals(20, ids.size()); - assertEquals(2l, ids.get(1).longValue()); - } + assertEquals(20, c); + } - public void test_SelectCount() throws Exception { + public void testSQLTemplate_PositionalParams() throws Exception { - createArtistsDataSet(); + createArtistsDataSet(); - int c = SQLSelect.scalarQuery(Integer.class, "SELECT #result('COUNT(*)' 'int') FROM ARTIST").selectOne(context); + Long id = SQLSelect.scalarQuery(Long.class, "SELECT ARTIST_ID FROM ARTIST WHERE ARTIST_NAME = #bind($a)") + .paramsArray("artist3").selectOne(context); - assertEquals(20, c); - } + assertEquals(3l, id.longValue()); + } } http://git-wip-us.apache.org/repos/asf/cayenne/blob/8f16b607/docs/doc/src/main/resources/RELEASE-NOTES.txt ---------------------------------------------------------------------- diff --git a/docs/doc/src/main/resources/RELEASE-NOTES.txt b/docs/doc/src/main/resources/RELEASE-NOTES.txt index 75c7756..ea1e1fd 100644 --- a/docs/doc/src/main/resources/RELEASE-NOTES.txt +++ b/docs/doc/src/main/resources/RELEASE-NOTES.txt @@ -69,6 +69,7 @@ CAY-1958 SelectById - a new full-featured select query to get objects by id CAY-1960 ExpressionFactory.exp(..) , and(..), or(..) CAY-1962 Implement CayenneTable column resize on double-click on the header separator CAY-1965 Change version from 3.2 to 4.0 +CAY-1966 SQLTemplate/SQLSelect positional parameter binding CAY-1967 Deprecate SQLTemplate parameter batches Bug Fixes: