You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by aa...@apache.org on 2014/11/14 18:47:04 UTC
[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 <aa...@apache.org>
Authored: Sun Nov 2 22:59:04 2014 +0300
Committer: aadamchik <aa...@apache.org>
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<T> extends IndirectQuery implements Select<T> {
protected QueryCacheStrategy cacheStrategy;
protected String[] cacheGroups;
protected Map<String, Object> params;
+ protected List<Object> positionalParams;
protected CapsStrategy columnNameCaps;
protected int limit;
protected int offset;
@@ -103,7 +106,6 @@ public class SQLSelect<T> extends IndirectQuery implements Select<T> {
public SQLSelect(Class<T> persistentType, String sql) {
this.persistentType = persistentType;
this.sqlBuffer = sql != null ? new StringBuilder(sql) : new StringBuilder();
- this.params = new HashMap<String, Object>();
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<T> extends IndirectQuery implements Select<T> {
}
public SQLSelect<T> params(String name, Object value) {
- params.put(name, value);
- this.replacementQuery = null;
+ params(Collections.singletonMap(name, value));
return this;
}
@SuppressWarnings({ "rawtypes", "unchecked" })
public SQLSelect<T> params(Map<String, ?> parameters) {
- Map bareMap = parameters;
- parameters.putAll(bareMap);
+
+ if (this.params == null) {
+ this.params = new HashMap<String, Object>(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<T> paramsArray(Object... params) {
+ return paramsList(params != null ? Arrays.asList(params) : null);
+ }
+
+ public SQLSelect<T> paramsList(List<Object> 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<String, Object> getParams() {
- return params;
+ return params != null ? params : Collections.<String, Object> emptyMap();
}
@Override
@@ -195,7 +219,13 @@ public class SQLSelect<T> extends IndirectQuery implements Select<T> {
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<Object> 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<Object>(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<DataRow> 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<DataRow> 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<DataRow> q1 = SQLSelect.dataRowQuery("tstmap", "SELECT * FROM ARTIST");
- assertTrue(q1.isFetchingDataRows());
+ SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST");
+ assertTrue(q1.isFetchingDataRows());
- List<DataRow> result = context.select(q1);
- assertEquals(20, result.size());
- assertTrue(result.get(0) instanceof DataRow);
- }
+ List<DataRow> 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<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST");
- assertTrue(q1.isFetchingDataRows());
+ SQLSelect<Artist> q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST");
+ assertFalse(q1.isFetchingDataRows());
+ List<Artist> result = context.select(q1);
+ assertEquals(20, result.size());
+ assertTrue(result.get(0) instanceof Artist);
+ }
- List<DataRow> 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<Artist> q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a)");
+ q1.params("a", "artist3");
- SQLSelect<Artist> q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST");
- assertFalse(q1.isFetchingDataRows());
- List<Artist> 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<Artist> q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a)");
- q1.getParams().put("a", "artist3");
+ SQLSelect<Artist> 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<Artist> 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<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST WHERE ARTIST_NAME = 'artist2'");
+ q1.upperColumnNames();
- SQLSelect<Artist> 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<Artist> 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<DataRow> 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<DataRow> 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<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST");
- q1.limit(5);
+ SQLSelect<DataRow> 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<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM ARTIST");
- q1.offset(4);
+ SQLSelect<Artist> q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST")
+ .append(" WHERE ARTIST_NAME = #bind($a)").params("a", "artist3");
- assertEquals(16, context.select(q1).size());
- }
+ List<Artist> result = context.select(q1);
+ assertEquals(1, result.size());
+ }
- public void test_Append() throws Exception {
+ public void test_Select() throws Exception {
- createArtistsDataSet();
+ createArtistsDataSet();
- SQLSelect<Artist> q1 = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST")
- .append(" WHERE ARTIST_NAME = #bind($a)").params("a", "artist3");
+ List<Artist> result = SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ARTIST_NAME = #bind($a)")
+ .params("a", "artist3").select(context);
- List<Artist> 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<Artist> 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<Long> 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<Long> 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: