/*****************************************************************
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you 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 org.apache.cayenne.query;
import java.util.Collections;
import java.util.List;
import org.apache.cayenne.DataRow;
import org.apache.cayenne.ObjectContext;
import org.apache.cayenne.di.Inject;
import org.apache.cayenne.exp.Expression;
import org.apache.cayenne.exp.ExpressionFactory;
import org.apache.cayenne.map.DbEntity;
import org.apache.cayenne.map.EntityResolver;
import org.apache.cayenne.map.ObjEntity;
import org.apache.cayenne.map.ObjRelationship;
import org.apache.cayenne.test.jdbc.DBHelper;
import org.apache.cayenne.test.jdbc.TableHelper;
import org.apache.cayenne.testdo.testmap.Artist;
import org.apache.cayenne.testdo.testmap.ArtistExhibit;
import org.apache.cayenne.testdo.testmap.ClobTestEntity;
import org.apache.cayenne.testdo.testmap.Exhibit;
import org.apache.cayenne.testdo.testmap.Gallery;
import org.apache.cayenne.testdo.testmap.Painting;
import org.apache.cayenne.unit.AccessStackAdapter;
import org.apache.cayenne.unit.di.server.ServerCase;
import org.apache.cayenne.unit.di.server.UseServerRuntime;
@UseServerRuntime(ServerCase.TESTMAP_PROJECT)
public class SelectQueryTest extends ServerCase {
@Inject
protected ObjectContext context;
@Inject
protected DBHelper dbHelper;
@Inject
protected AccessStackAdapter accessStackAdapter;
@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 createClobDataSet() throws Exception {
TableHelper tClobTest = new TableHelper(dbHelper, "CLOB_TEST");
tClobTest.setColumns("CLOB_TEST_ID", "CLOB_COL");
tClobTest.deleteAll();
tClobTest.insert(1, "clob1");
tClobTest.insert(2, "clob2");
}
protected void createArtistsDataSet() throws Exception {
TableHelper tArtist = new TableHelper(dbHelper, "ARTIST");
tArtist.setColumns("ARTIST_ID", "ARTIST_NAME", "DATE_OF_BIRTH");
long dateBase = System.currentTimeMillis();
for (int i = 1; i <= 20; i++) {
tArtist.insert(i, "artist" + i, new java.sql.Date(dateBase + 10000 * i));
}
}
protected void createArtistsWildcardDataSet() throws Exception {
TableHelper tArtist = new TableHelper(dbHelper, "ARTIST");
tArtist.setColumns("ARTIST_ID", "ARTIST_NAME");
tArtist.insert(1, "_X");
tArtist.insert(2, "Y_");
}
public void testFetchLimit() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
query.setFetchLimit(7);
List objects = context.performQuery(query);
assertNotNull(objects);
assertEquals(7, objects.size());
}
public void testFetchOffset() throws Exception {
createArtistsDataSet();
int totalRows = context.performQuery(new SelectQuery(Artist.class)).size();
SelectQuery query = new SelectQuery(Artist.class);
query.addOrdering("db:" + Artist.ARTIST_ID_PK_COLUMN, SortOrder.ASCENDING);
query.setFetchOffset(5);
List<Artist> results = context.performQuery(query);
assertEquals(totalRows - 5, results.size());
assertEquals("artist6", results.get(0).getArtistName());
}
public void testDbEntityRoot() throws Exception {
createArtistsDataSet();
DbEntity artistDbEntity = context.getEntityResolver().getDbEntity("ARTIST");
SelectQuery query = new SelectQuery(artistDbEntity);
List results = context.performQuery(query);
assertEquals(20, results.size());
assertTrue(results.get(0) instanceof DataRow);
}
public void testFetchLimitWithOffset() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
query.addOrdering("db:" + Artist.ARTIST_ID_PK_COLUMN, SortOrder.ASCENDING);
query.setFetchOffset(15);
query.setFetchLimit(4);
List<Artist> results = context.performQuery(query);
assertEquals(4, results.size());
assertEquals("artist16", results.get(0).getArtistName());
}
public void testFetchOffsetWithQualifier() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
query.setQualifier(Expression.fromString("db:ARTIST_ID > 3"));
query.setFetchOffset(5);
List objects = context.performQuery(query);
int size = objects.size();
SelectQuery sizeQ = new SelectQuery(Artist.class);
sizeQ.setQualifier(Expression.fromString("db:ARTIST_ID > 3"));
List objects1 = context.performQuery(sizeQ);
int sizeAll = objects1.size();
assertEquals(size, sizeAll - 5);
}
public void testFetchLimitWithQualifier() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
query.setQualifier(Expression.fromString("db:ARTIST_ID > 3"));
query.setFetchLimit(7);
List objects = context.performQuery(query);
assertEquals(7, objects.size());
}
public void testSelectAllObjectsRootEntityName() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery("Artist");
List objects = context.performQuery(query);
assertEquals(20, objects.size());
}
public void testSelectAllObjectsRootClass() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
List objects = context.performQuery(query);
assertEquals(20, objects.size());
}
public void testSelectAllObjectsRootObjEntity() throws Exception {
createArtistsDataSet();
ObjEntity artistEntity = context
.getEntityResolver()
.lookupObjEntity(Artist.class);
SelectQuery query = new SelectQuery(artistEntity);
List objects = context.performQuery(query);
assertEquals(20, objects.size());
}
public void testSelectLikeExactMatch() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.likeExp("artistName", "artist1");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(1, objects.size());
}
public void testSelectNotLikeSingleWildcardMatch() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.notLikeExp("artistName", "artist11%");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(19, objects.size());
}
public void testSelectNotLikeIgnoreCaseSingleWildcardMatch() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.notLikeIgnoreCaseExp(
"artistName",
"aRtIsT11%");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(19, objects.size());
}
public void testSelectLikeCaseSensitive() throws Exception {
if (!accessStackAdapter.supportsCaseSensitiveLike()) {
return;
}
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.likeExp("artistName", "aRtIsT%");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(0, objects.size());
}
public void testSelectLikeSingleWildcardMatch() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.likeExp("artistName", "artist11%");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(1, objects.size());
}
public void testSelectLikeSingleWildcardMatchAndEscape() throws Exception {
createArtistsWildcardDataSet();
SelectQuery query = new SelectQuery(Artist.class);
query.andQualifier(ExpressionFactory.likeExp("artistName", "=_%", '='));
List objects = context.performQuery(query);
assertEquals(1, objects.size());
}
public void testSelectLikeMultipleWildcardMatch() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.likeExp("artistName", "artist1%");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(11, objects.size());
}
/** Test how "like ignore case" works when using uppercase parameter. */
public void testSelectLikeIgnoreCaseObjects1() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.likeIgnoreCaseExp("artistName", "ARTIST%");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(20, objects.size());
}
/** Test how "like ignore case" works when using lowercase parameter. */
public void testSelectLikeIgnoreCaseObjects2() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.likeIgnoreCaseExp("artistName", "artist%");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(20, objects.size());
}
/** Test how "like ignore case" works when using uppercase parameter. */
public void testSelectLikeIgnoreCaseClob() throws Exception {
if (accessStackAdapter.supportsLobs()) {
createClobDataSet();
SelectQuery query = new SelectQuery(ClobTestEntity.class);
Expression qual = ExpressionFactory.likeIgnoreCaseExp("clobCol", "clob%");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(2, objects.size());
}
}
public void testSelectEqualsClob() throws Exception {
if (accessStackAdapter.supportsLobs()) {
createClobDataSet();
SelectQuery query = new SelectQuery(ClobTestEntity.class);
Expression qual = ExpressionFactory.matchExp("clobCol", "clob1");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(1, objects.size());
}
}
public void testSelectNotEqualsClob() throws Exception {
if (accessStackAdapter.supportsLobs()) {
createClobDataSet();
SelectQuery query = new SelectQuery(ClobTestEntity.class);
Expression qual = ExpressionFactory.noMatchExp("clobCol", "clob1");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(1, objects.size());
}
}
public void testSelectIn() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = Expression.fromString("artistName in ('artist1', 'artist2')");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(2, objects.size());
}
public void testSelectParameterizedIn() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = Expression.fromString("artistName in $list");
query.setQualifier(qual);
query = query.queryWithParameters(Collections.singletonMap("list", new Object[] {
"artist1", "artist2"
}));
List objects = context.performQuery(query);
assertEquals(2, objects.size());
}
public void testSelectParameterizedEmptyIn() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = Expression.fromString("artistName in $list");
query.setQualifier(qual);
query = query.queryWithParameters(Collections.singletonMap(
"list",
new Object[] {}));
List objects = context.performQuery(query);
assertEquals(0, objects.size());
}
public void testSelectParameterizedEmptyNotIn() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = Expression.fromString("artistName not in $list");
query.setQualifier(qual);
query = query.queryWithParameters(Collections.singletonMap(
"list",
new Object[] {}));
List objects = context.performQuery(query);
assertEquals(20, objects.size());
}
public void testSelectEmptyIn() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.inExp("artistName");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(0, objects.size());
}
public void testSelectEmptyNotIn() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.notInExp("artistName");
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(20, objects.size());
}
public void testSelectBooleanTrue() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.expTrue();
qual = qual.andExp(ExpressionFactory.matchExp("artistName", "artist1"));
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(1, objects.size());
}
public void testSelectBooleanNotTrueOr() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.expTrue();
qual = qual.notExp();
qual = qual.orExp(ExpressionFactory.matchExp("artistName", "artist1"));
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(1, objects.size());
}
public void testSelectBooleanFalse() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.expFalse();
qual = qual.andExp(ExpressionFactory.matchExp("artistName", "artist1"));
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(0, objects.size());
}
public void testSelectBooleanFalseOr() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class);
Expression qual = ExpressionFactory.expFalse();
qual = qual.orExp(ExpressionFactory.matchExp("artistName", "artist1"));
query.setQualifier(qual);
List objects = context.performQuery(query);
assertEquals(1, objects.size());
}
/**
* Tests that all queries specified in prefetch are executed in a more complex
* prefetch scenario.
*/
public void testRouteWithPrefetches() {
EntityResolver resolver = context.getEntityResolver();
MockQueryRouter router = new MockQueryRouter();
SelectQuery q = new SelectQuery(Artist.class, ExpressionFactory.matchExp(
"artistName",
"a"));
q.route(router, resolver, null);
assertEquals(1, router.getQueryCount());
q.addPrefetch("paintingArray");
router.reset();
q.route(router, resolver, null);
assertEquals(2, router.getQueryCount());
q.addPrefetch("paintingArray.toGallery");
router.reset();
q.route(router, resolver, null);
assertEquals(3, router.getQueryCount());
q.addPrefetch("artistExhibitArray.toExhibit");
router.reset();
q.route(router, resolver, null);
assertEquals(4, router.getQueryCount());
q.removePrefetch("paintingArray");
router.reset();
q.route(router, resolver, null);
assertEquals(3, router.getQueryCount());
}
/**
* Tests that all queries specified in prefetch are executed in a more complex
* prefetch scenario with no reverse obj relationships.
*/
public void testRouteQueryWithPrefetchesNoReverse() {
EntityResolver resolver = context.getEntityResolver();
ObjEntity paintingEntity = resolver.lookupObjEntity(Painting.class);
ObjEntity galleryEntity = resolver.lookupObjEntity(Gallery.class);
ObjEntity artistExhibitEntity = resolver.lookupObjEntity(ArtistExhibit.class);
ObjEntity exhibitEntity = resolver.lookupObjEntity(Exhibit.class);
ObjRelationship paintingToArtistRel = (ObjRelationship) paintingEntity
.getRelationship("toArtist");
paintingEntity.removeRelationship("toArtist");
ObjRelationship galleryToPaintingRel = (ObjRelationship) galleryEntity
.getRelationship("paintingArray");
galleryEntity.removeRelationship("paintingArray");
ObjRelationship artistExhibitToArtistRel = (ObjRelationship) artistExhibitEntity
.getRelationship("toArtist");
artistExhibitEntity.removeRelationship("toArtist");
ObjRelationship exhibitToArtistExhibitRel = (ObjRelationship) exhibitEntity
.getRelationship("artistExhibitArray");
exhibitEntity.removeRelationship("artistExhibitArray");
Expression e = ExpressionFactory.matchExp("artistName", "artist1");
SelectQuery q = new SelectQuery("Artist", e);
q.addPrefetch("paintingArray");
q.addPrefetch("paintingArray.toGallery");
q.addPrefetch("artistExhibitArray.toExhibit");
try {
MockQueryRouter router = new MockQueryRouter();
q.route(router, resolver, null);
assertEquals(4, router.getQueryCount());
}
finally {
paintingEntity.addRelationship(paintingToArtistRel);
galleryEntity.addRelationship(galleryToPaintingRel);
artistExhibitEntity.addRelationship(artistExhibitToArtistRel);
exhibitEntity.addRelationship(exhibitToArtistExhibitRel);
}
}
/**
* Test prefetching with qualifier on the root query being the path to the prefetch.
*/
public void testRouteQueryWithPrefetchesPrefetchExpressionPath() {
// find the painting not matching the artist (this is the case where such prefetch
// at least makes sense)
Expression exp = ExpressionFactory.noMatchExp("toArtist", new Object());
SelectQuery q = new SelectQuery(Painting.class, exp);
q.addPrefetch("toArtist");
// test how prefetches are resolved in this case - this was a stumbling block for
// a while
EntityResolver resolver = context.getEntityResolver();
MockQueryRouter router = new MockQueryRouter();
q.route(router, resolver, null);
assertEquals(2, router.getQueryCount());
}
public void testLeftJoinAndPrefetchToMany() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Artist.class, ExpressionFactory.matchExp(
"paintingArray+.toGallery",
null));
query.addPrefetch("artistExhibitArray");
context.performQuery(query);
}
public void testLeftJoinAndPrefetchToOne() throws Exception {
createArtistsDataSet();
SelectQuery query = new SelectQuery(Painting.class, ExpressionFactory.matchExp(
"toArtist+.artistName",
null));
query.addPrefetch("toGallery");
context.performQuery(query);
}
}