cayenne-server/src/test/java/org/apache/cayenne/query/SQLSelectIT.java - cayenne - Git at Google

 /*****************************************************************
  *   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
  *
  *    https://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.sql.Date;
 import java.sql.SQLException;
 import java.sql.Types;
 import java.time.LocalDateTime;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;

 import org.apache.cayenne.CayenneRuntimeException;
 import org.apache.cayenne.DataRow;
 import org.apache.cayenne.ResultBatchIterator;
 import org.apache.cayenne.ResultIterator;
 import org.apache.cayenne.access.DataContext;
 import org.apache.cayenne.di.Inject;
 import org.apache.cayenne.test.jdbc.DBHelper;
 import org.apache.cayenne.test.jdbc.TableHelper;
 import org.apache.cayenne.testdo.testmap.Painting;
 import org.apache.cayenne.unit.di.server.CayenneProjects;
 import org.apache.cayenne.unit.di.server.ServerCase;
 import org.apache.cayenne.unit.di.server.UseServerRuntime;
 import org.junit.Before;
 import org.junit.Ignore;
 import org.junit.Test;

 import static org.hamcrest.CoreMatchers.instanceOf;
 import static org.junit.Assert.*;

 @UseServerRuntime(CayenneProjects.TESTMAP_PROJECT)
 public class SQLSelectIT extends ServerCase {

 	@Inject
 	private DataContext context;

 	@Inject
 	private DBHelper dbHelper;

 	private TableHelper tPainting;

 	private TableHelper tArtistCt;

 	private TableHelper tPaintingInfo;

 	@Before
 	public void before() {
 		tPainting = new TableHelper(dbHelper, "PAINTING")
 				.setColumns("PAINTING_ID", "PAINTING_TITLE", "ESTIMATED_PRICE")
 				.setColumnTypes(Types.INTEGER, Types.VARCHAR, Types.DECIMAL);

 		tArtistCt = new TableHelper(dbHelper, "ARTIST_CT")
 				.setColumns("ARTIST_ID", "ARTIST_NAME", "DATE_OF_BIRTH");

 		tPaintingInfo = new TableHelper(dbHelper, "PAINTING_INFO")
 				.setColumns("PAINTING_ID", "IMAGE_BLOB")
 				.setColumnTypes(Types.INTEGER, Types.LONGVARBINARY);
 	}

 	private void createPaintingsDataSet() throws Exception {
 		for (int i = 1; i <= 20; i++) {
 			tPainting.insert(i, "painting" + i, 10000. * i);
 		}
 	}

 	private void createArtistDataSet() throws SQLException {
 		tArtistCt.insert(1, "Test", new Date(System.currentTimeMillis()));
 		tArtistCt.insert(2, "Test1", new Date(System.currentTimeMillis()));
 	}

 	@Test
 	public void test_DataRows_DataMapNameRoot() throws Exception {
 		createPaintingsDataSet();

 		SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("testmap", "SELECT * FROM PAINTING");
 		assertTrue(q1.isFetchingDataRows());

 		List<DataRow> result = context.select(q1);
 		assertEquals(20, result.size());
 		assertTrue(result.get(0) instanceof DataRow);
 	}

 	@Test
 	public void test_DataRows_DefaultRoot() throws Exception {
 		createPaintingsDataSet();

 		SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM PAINTING");
 		assertTrue(q1.isFetchingDataRows());

 		List<DataRow> result = context.select(q1);
 		assertEquals(20, result.size());
 		assertTrue(result.get(0) instanceof DataRow);
 	}

 	@Test
 	public void test_DataRows_ClassRoot() throws Exception {
 		createPaintingsDataSet();

 		SQLSelect<Painting> q1 = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING").columnNameCaps(
 				CapsStrategy.UPPER);
 		assertFalse(q1.isFetchingDataRows());
 		List<Painting> result = context.select(q1);
 		assertEquals(20, result.size());
 		assertTrue(result.get(0) instanceof Painting);
 	}

 	@Test
 	public void test_DataRowWithTypes() throws Exception {
 		createArtistDataSet();

 		List<DataRow> result = SQLSelect.dataRowQuery("SELECT * FROM ARTIST_CT", Integer.class, String.class, LocalDateTime.class)
 				.columnNameCaps(CapsStrategy.UPPER)
 				.select(context);
 		assertEquals(2, result.size());
 		assertTrue(result.get(0) instanceof DataRow);
 		assertThat(result.get(0).get("DATE_OF_BIRTH"), instanceOf(LocalDateTime.class));
 	}

 	@Test
 	public void test_DataRowWithTypesMapped() throws Exception {
 		createArtistDataSet();

 		List<Object> result = SQLSelect.dataRowQuery("SELECT * FROM ARTIST_CT", Integer.class, String.class, LocalDateTime.class)
 				.columnNameCaps(CapsStrategy.UPPER)
 				.map(dataRow -> dataRow.get("ARTIST_ID"))
 				.select(context);
 		assertEquals(2, result.size());
 		assertTrue(result.get(0) instanceof Integer);
 	}

 	@Test
 	public void test_DataRowWithDirectives() throws Exception {
 		createArtistDataSet();

 		List<DataRow> result = SQLSelect.dataRowQuery("SELECT #result('ARTIST_ID' 'java.lang.Double'), #result('ARTIST_NAME' 'java.lang.String') FROM ARTIST_CT")
 				.select(context);
 		assertEquals(2, result.size());
 		assertTrue(result.get(0) instanceof DataRow);
 		assertTrue(result.get(0).get("ARTIST_ID") instanceof Double);
 	}

 	@Test(expected = CayenneRuntimeException.class)
 	public void test_DataRowWithTypesException() throws Exception {
 		createArtistDataSet();

 		SQLSelect.dataRowQuery("SELECT * FROM ARTIST_CT", Integer.class, String.class)
 				.select(context);
 	}

 	@Test
 	public void testObjectArrayWithDefaultTypesReturnAndDirectives() throws Exception {
 		createArtistDataSet();

 		List<Object[]> result = SQLSelect.columnQuery("SELECT #result('ARTIST_ID' 'java.lang.Long'), #result('ARTIST_NAME' 'java.lang.String') FROM ARTIST_CT")
 				.select(context);

 		assertEquals(2, result.size());
 		assertTrue(result.get(0) instanceof Object[]);
 		assertEquals(2, result.get(0).length);
 		assertTrue(result.get(0)[0] instanceof Long);
 		assertTrue(result.get(0)[1] instanceof String);
 	}

 	@Test
 	public void testObjectArrayWithDefaultTypesReturnAndDirectivesMappedToPojo() throws Exception {
 		createArtistDataSet();

 		List<ArtistDataWrapper> result = SQLSelect
 				.columnQuery("SELECT #result('ARTIST_ID' 'java.lang.Long'), #result('ARTIST_NAME' 'java.lang.String') FROM ARTIST_CT")
 				.map(ArtistDataWrapper::new)
 				.select(context);

 		assertEquals(2, result.size());
 		assertTrue(result.get(0).id > 0);
 		assertNotNull(result.get(0).name);
 	}

 	@Test(expected = CayenneRuntimeException.class)
 	public void testObjectArrayReturnAndDirectives() throws Exception {
 		createArtistDataSet();

 		SQLSelect.columnQuery("SELECT #result('ARTIST_ID' 'java.lang.Long'), #result('ARTIST_NAME' 'java.lang.String') FROM ARTIST_CT",
 				Integer.class, String.class).select(context);
 	}

 	@Test
 	public void testObjectArrayWithOneObjectDefaultTypesReturnAndDirectives() throws Exception {
 		createArtistDataSet();

 		List<Object[]> result = SQLSelect.columnQuery("SELECT #result('ARTIST_ID' 'java.lang.Long') FROM ARTIST_CT")
 				.select(context);

 		assertEquals(2, result.size());
 		assertTrue(result.get(0) instanceof Object[]);
 		assertEquals(1, result.get(0).length);
 		assertTrue(result.get(0)[0] instanceof Long);
 	}

 	@Test
 	public void test_ObjectArrayQueryWithDefaultTypes() throws Exception {
 		createPaintingsDataSet();

 		List<Object[]> result = SQLSelect.columnQuery("SELECT PAINTING_ID, PAINTING_TITLE, ESTIMATED_PRICE FROM PAINTING")
 				.select(context);

 		assertEquals(20, result.size());
 		assertEquals(3, result.get(0).length);
 	}

 	@Test
 	public void test_ObjectQueryWithDefaultType() throws Exception {
 		createPaintingsDataSet();

 		List<Object[]> result = SQLSelect.columnQuery("SELECT PAINTING_ID FROM PAINTING")
 				.select(context);
 		assertEquals(20, result.size());
 		assertTrue(result.get(0) instanceof Object[]);
 		assertTrue(result.get(0)[0] instanceof Integer);
 	}

 	@Test(expected = CayenneRuntimeException.class)
 	public void test_ObjectArrayQueryException() throws Exception {
 		createPaintingsDataSet();

 		SQLSelect<Object[]> query = SQLSelect.columnQuery("SELECT PAINTING_ID, PAINTING_TITLE, ESTIMATED_PRICE FROM PAINTING", Integer.class, String.class);
 		context.performQuery(query);
 	}

 	@Test
 	public void test_SingleObjectQuery() throws Exception {
 		createPaintingsDataSet();

 		List<Integer> result = SQLSelect.scalarQuery("SELECT PAINTING_ID FROM PAINTING", Integer.class)
 				.select(context);
 		assertEquals(20, result.size());
 		assertTrue(result.get(0) instanceof Integer);
 	}

 	@Test
 	public void testObjectArrayWithCustomType() throws SQLException {
 		createArtistDataSet();

 		List<Object[]> results = SQLSelect.columnQuery("SELECT * FROM ARTIST_CT",
 				Integer.class, String.class, LocalDateTime.class).select(context);

 		assertEquals(2, results.size());
 		assertTrue(results.get(0) instanceof Object[]);
 		assertEquals(3, results.get(0).length);
 		assertTrue(results.get(0)[2] instanceof LocalDateTime);
 	}

 	@Test
 	public void testObjectArrayWithCustomTypeMappedToPojo() throws SQLException {
 		createArtistDataSet();

 		List<ArtistDataWrapper> result = SQLSelect.scalarQuery("SELECT * FROM ARTIST_CT",
 				Integer.class, String.class, LocalDateTime.class)
 				.map(ArtistDataWrapper::new)
 				.select(context);

 		assertEquals(2, result.size());
 		assertTrue(result.get(0).id > 0);
 		assertNotNull(result.get(0).name);
 		assertNotNull(result.get(0).date);
 	}

 	@Test
 	public void test_DataRows_ClassRoot_Parameters() throws Exception {
 		createPaintingsDataSet();

 		SQLSelect<Painting> q1 = SQLSelect.query(Painting.class,
 				"SELECT * FROM PAINTING WHERE PAINTING_TITLE = #bind($a)");
 		q1.param("a", "painting3").columnNameCaps(CapsStrategy.UPPER);

 		assertFalse(q1.isFetchingDataRows());
 		Painting a = context.selectOne(q1);
 		assertEquals("painting3", a.getPaintingTitle());
 	}

 	@Test
 	public void test_DataRows_ClassRoot_Bind() throws Exception {
 		createPaintingsDataSet();

 		SQLSelect<Painting> q1 = SQLSelect.query(Painting.class,
 				"SELECT * FROM PAINTING WHERE PAINTING_TITLE = #bind($a) OR PAINTING_TITLE = #bind($b)")
 				.columnNameCaps(CapsStrategy.UPPER);
 		q1.param("a", "painting3").param("b", "painting4");

 		List<Painting> result = context.select(q1);
 		assertEquals(2, result.size());
 	}

 	@Test
 	public void test_DataRows_ColumnNameCaps() {
 		SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM PAINTING WHERE PAINTING_TITLE = 'painting2'");
 		q1.upperColumnNames();

 		SQLTemplate r1 = (SQLTemplate) q1.getReplacementQuery(context.getEntityResolver());
 		assertEquals(CapsStrategy.UPPER, r1.getColumnNamesCapitalization());

 		q1.lowerColumnNames();
 		SQLTemplate r2 = (SQLTemplate) q1.getReplacementQuery(context.getEntityResolver());
 		assertEquals(CapsStrategy.LOWER, r2.getColumnNamesCapitalization());
 	}

 	@Test
 	public void test_DataRows_FetchLimit() throws Exception {
 		createPaintingsDataSet();

 		SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM PAINTING");
 		q1.limit(5);

 		assertEquals(5, context.select(q1).size());
 	}

 	@Test
 	public void test_DataRows_FetchOffset() throws Exception {
 		createPaintingsDataSet();

 		SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM PAINTING");
 		q1.offset(4);

 		assertEquals(16, context.select(q1).size());
 	}

 	@Test
 	public void test_Append() throws Exception {
 		createPaintingsDataSet();

 		SQLSelect<Painting> q1 = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING")
 				.append(" WHERE PAINTING_TITLE = #bind($a)").param("a", "painting3")
 				.columnNameCaps(CapsStrategy.UPPER);

 		List<Painting> result = context.select(q1);
 		assertEquals(1, result.size());
 	}

 	@Test
 	public void test_Select() throws Exception {
 		createPaintingsDataSet();

 		List<Painting> result = SQLSelect
 				.query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE = #bind($a)")
 				.param("a", "painting3")
 				.columnNameCaps(CapsStrategy.UPPER)
 				.select(context);

 		assertEquals(1, result.size());
 	}

 	@Test
 	public void test_SelectOne() throws Exception {
 		createPaintingsDataSet();

 		Painting a = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE = #bind($a)")
 				.param("a", "painting3").columnNameCaps(CapsStrategy.UPPER).selectOne(context);

 		assertEquals("painting3", a.getPaintingTitle());
 	}

 	@Test
 	public void test_SelectFirst() throws Exception {
 		createPaintingsDataSet();

 		Painting p = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING ORDER BY PAINTING_TITLE").columnNameCaps(CapsStrategy.UPPER).selectFirst(
 				context);

 		assertNotNull(p);
 		assertEquals("painting1", p.getPaintingTitle());
 	}

 	@Test
 	public void test_SelectFirstByContext() throws Exception {
 		createPaintingsDataSet();

 		SQLSelect<Painting> q = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING ORDER BY PAINTING_TITLE").columnNameCaps(CapsStrategy.UPPER);
 		Painting p = context.selectFirst(q);

 		assertNotNull(p);
 		assertEquals("painting1", p.getPaintingTitle());
 	}

 	@Test
 	public void test_Iterate() throws Exception {
 		createPaintingsDataSet();

 		final int[] count = new int[1];
 		SQLSelect.query(Painting.class, "SELECT * FROM PAINTING").columnNameCaps(CapsStrategy.UPPER)
 				.iterate(context, object -> {
 					assertNotNull(object.getPaintingTitle());
 					count[0]++;
 				});

 		assertEquals(20, count[0]);
 	}

 	@Test
 	public void test_Iterator() throws Exception {
 		createPaintingsDataSet();

 		try (ResultIterator<Painting> it = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING")
 				.columnNameCaps(CapsStrategy.UPPER).iterator(context)) {
 			int count = 0;

 			for (Painting p : it) {
 				count++;
 			}

 			assertEquals(20, count);
 		}
 	}

 	@Test
 	public void test_BatchIterator() throws Exception {
 		createPaintingsDataSet();

 		try (ResultBatchIterator<Painting> it = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING")
 				.columnNameCaps(CapsStrategy.UPPER).batchIterator(context, 5)) {
 			int count = 0;

 			for (List<Painting> paintingList : it) {
 				count++;
 				assertEquals(5, paintingList.size());
 			}

 			assertEquals(4, count);
 		}
 	}

 	@Test
 	public void test_SelectLong() throws Exception {
 		createPaintingsDataSet();

 		long id = SQLSelect
 				.scalarQuery( "SELECT PAINTING_ID FROM PAINTING WHERE PAINTING_TITLE = #bind($a)",
 						Integer.class)
 				.param("a", "painting3").selectOne(context);

 		assertEquals(3L, id);
 	}

 	@Test
 	public void test_SelectLongArray() throws Exception {
 		createPaintingsDataSet();

 		List<Integer> ids = SQLSelect.scalarQuery("SELECT PAINTING_ID FROM PAINTING ORDER BY PAINTING_ID",
 				Integer.class).select(context);

 		assertEquals(20, ids.size());
 		assertEquals(2L, ids.get(1).intValue());
 	}

 	@Test
 	public void test_SelectCount() throws Exception {
 		createPaintingsDataSet();

 		int c = SQLSelect.scalarQuery("SELECT COUNT(*) FROM PAINTING", Integer.class).selectOne(context);

 		assertEquals(20, c);
 	}

 	@Test
 	public void test_ParamsArray_Single() throws Exception {
 		createPaintingsDataSet();

 		Integer id = SQLSelect
 				.scalarQuery( "SELECT PAINTING_ID FROM PAINTING WHERE PAINTING_TITLE = #bind($a)",
 						Integer.class)
 				.paramsArray("painting3").selectOne(context);

 		assertEquals(3L, id.intValue());
 	}

 	@Test
 	public void test_ParamsArray_Multiple() throws Exception {
 		createPaintingsDataSet();

 		List<Integer> ids = SQLSelect
 				.scalarQuery("SELECT PAINTING_ID FROM PAINTING WHERE PAINTING_TITLE = #bind($a) " +
 						"OR PAINTING_TITLE = #bind($b) ORDER BY PAINTING_ID",
 						Integer.class)
 				.paramsArray("painting3", "painting2").select(context);

 		assertEquals(2L, ids.get(0).intValue());
 		assertEquals(3L, ids.get(1).intValue());
 	}

 	@Test
 	@Ignore("This is supported by Velocity only")
 	// TODO: move this test to new cayenne-velocity module
 	public void test_ParamsArray_Multiple_OptionalChunks() throws Exception {
 		tPainting.insert(1, "painting1", 1.0);
 		tPainting.insert(2, "painting2", null);

 		List<Integer> ids = SQLSelect
 				.scalarQuery(
 						"SELECT PAINTING_ID FROM PAINTING #chain('OR' 'WHERE') "
 								+ "#chunk($a) ESTIMATED_PRICE #bindEqual($a) #end "
 								+ "#chunk($b) PAINTING_TITLE #bindEqual($b) #end #end ORDER BY PAINTING_ID",
 						Integer.class)
 				.paramsArray(null, "painting1").select(context);

 		assertEquals(1, ids.size());
 		assertEquals(1L, ids.get(0).longValue());
 	}

 	@Test
 	@Ignore("This is supported by Velocity only")
 	// TODO: move this test to new cayenne-velocity module
 	public void test_Params_Multiple_OptionalChunks() throws Exception {
 		tPainting.insert(1, "painting1", 1.0);
 		tPainting.insert(2, "painting2", null);

 		Map<String, Object> params = new HashMap<>();
 		params.put("a", null);
 		params.put("b", "painting1");

 		List<Integer> ids = SQLSelect
 				.scalarQuery(
 						"SELECT PAINTING_ID FROM PAINTING #chain('OR' 'WHERE') "
 								+ "#chunk($a) ESTIMATED_PRICE #bindEqual($a) #end "
 								+ "#chunk($b) PAINTING_TITLE #bindEqual($b) #end #end ORDER BY PAINTING_ID",
 						Integer.class)
 				.params(params).select(context);

 		assertEquals(1, ids.size());
 		assertEquals(1L, ids.get(0).longValue());
 	}

 	@Test
 	public void testByteArray() throws Exception {
 		byte[] data = {1, 2, 3};
 		tPainting.insert(1, "test", 0);
 		tPaintingInfo.insert(1, data);

 		byte[] bytes = SQLSelect
 				.scalarQuery("SELECT IMAGE_BLOB FROM PAINTING_INFO", byte[].class)
 				.selectOne(context);
 		assertArrayEquals(data, bytes);
 	}

 	static class ArtistDataWrapper {
 		long id;
 		String name;
 		LocalDateTime date;
 		ArtistDataWrapper(Object[] data) {
 			id = ((Number)data[0]).longValue();
 			name = (String)data[1];
 			if(data.length > 2) {
 				date = (LocalDateTime)data[2];
 			}
 		}
 	}
 }
	/*****************************************************************
	* 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
	*
	* https://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.sql.Date;
	import java.sql.SQLException;
	import java.sql.Types;
	import java.time.LocalDateTime;
	import java.util.HashMap;
	import java.util.List;
	import java.util.Map;

	import org.apache.cayenne.CayenneRuntimeException;
	import org.apache.cayenne.DataRow;
	import org.apache.cayenne.ResultBatchIterator;
	import org.apache.cayenne.ResultIterator;
	import org.apache.cayenne.access.DataContext;
	import org.apache.cayenne.di.Inject;
	import org.apache.cayenne.test.jdbc.DBHelper;
	import org.apache.cayenne.test.jdbc.TableHelper;
	import org.apache.cayenne.testdo.testmap.Painting;
	import org.apache.cayenne.unit.di.server.CayenneProjects;
	import org.apache.cayenne.unit.di.server.ServerCase;
	import org.apache.cayenne.unit.di.server.UseServerRuntime;
	import org.junit.Before;
	import org.junit.Ignore;
	import org.junit.Test;

	import static org.hamcrest.CoreMatchers.instanceOf;
	import static org.junit.Assert.*;

	@UseServerRuntime(CayenneProjects.TESTMAP_PROJECT)
	public class SQLSelectIT extends ServerCase {

	@Inject
	private DataContext context;

	@Inject
	private DBHelper dbHelper;

	private TableHelper tPainting;

	private TableHelper tArtistCt;

	private TableHelper tPaintingInfo;

	@Before
	public void before() {
	tPainting = new TableHelper(dbHelper, "PAINTING")
	.setColumns("PAINTING_ID", "PAINTING_TITLE", "ESTIMATED_PRICE")
	.setColumnTypes(Types.INTEGER, Types.VARCHAR, Types.DECIMAL);

	tArtistCt = new TableHelper(dbHelper, "ARTIST_CT")
	.setColumns("ARTIST_ID", "ARTIST_NAME", "DATE_OF_BIRTH");

	tPaintingInfo = new TableHelper(dbHelper, "PAINTING_INFO")
	.setColumns("PAINTING_ID", "IMAGE_BLOB")
	.setColumnTypes(Types.INTEGER, Types.LONGVARBINARY);
	}

	private void createPaintingsDataSet() throws Exception {
	for (int i = 1; i <= 20; i++) {
	tPainting.insert(i, "painting" + i, 10000. * i);
	}
	}

	private void createArtistDataSet() throws SQLException {
	tArtistCt.insert(1, "Test", new Date(System.currentTimeMillis()));
	tArtistCt.insert(2, "Test1", new Date(System.currentTimeMillis()));
	}

	@Test
	public void test_DataRows_DataMapNameRoot() throws Exception {
	createPaintingsDataSet();

	SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("testmap", "SELECT * FROM PAINTING");
	assertTrue(q1.isFetchingDataRows());

	List<DataRow> result = context.select(q1);
	assertEquals(20, result.size());
	assertTrue(result.get(0) instanceof DataRow);
	}

	@Test
	public void test_DataRows_DefaultRoot() throws Exception {
	createPaintingsDataSet();

	SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM PAINTING");
	assertTrue(q1.isFetchingDataRows());

	List<DataRow> result = context.select(q1);
	assertEquals(20, result.size());
	assertTrue(result.get(0) instanceof DataRow);
	}

	@Test
	public void test_DataRows_ClassRoot() throws Exception {
	createPaintingsDataSet();

	SQLSelect<Painting> q1 = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING").columnNameCaps(
	CapsStrategy.UPPER);
	assertFalse(q1.isFetchingDataRows());
	List<Painting> result = context.select(q1);
	assertEquals(20, result.size());
	assertTrue(result.get(0) instanceof Painting);
	}

	@Test
	public void test_DataRowWithTypes() throws Exception {
	createArtistDataSet();

	List<DataRow> result = SQLSelect.dataRowQuery("SELECT * FROM ARTIST_CT", Integer.class, String.class, LocalDateTime.class)
	.columnNameCaps(CapsStrategy.UPPER)
	.select(context);
	assertEquals(2, result.size());
	assertTrue(result.get(0) instanceof DataRow);
	assertThat(result.get(0).get("DATE_OF_BIRTH"), instanceOf(LocalDateTime.class));
	}

	@Test
	public void test_DataRowWithTypesMapped() throws Exception {
	createArtistDataSet();

	List<Object> result = SQLSelect.dataRowQuery("SELECT * FROM ARTIST_CT", Integer.class, String.class, LocalDateTime.class)
	.columnNameCaps(CapsStrategy.UPPER)
	.map(dataRow -> dataRow.get("ARTIST_ID"))
	.select(context);
	assertEquals(2, result.size());
	assertTrue(result.get(0) instanceof Integer);
	}

	@Test
	public void test_DataRowWithDirectives() throws Exception {
	createArtistDataSet();

	List<DataRow> result = SQLSelect.dataRowQuery("SELECT #result('ARTIST_ID' 'java.lang.Double'), #result('ARTIST_NAME' 'java.lang.String') FROM ARTIST_CT")
	.select(context);
	assertEquals(2, result.size());
	assertTrue(result.get(0) instanceof DataRow);
	assertTrue(result.get(0).get("ARTIST_ID") instanceof Double);
	}

	@Test(expected = CayenneRuntimeException.class)
	public void test_DataRowWithTypesException() throws Exception {
	createArtistDataSet();

	SQLSelect.dataRowQuery("SELECT * FROM ARTIST_CT", Integer.class, String.class)
	.select(context);
	}

	@Test
	public void testObjectArrayWithDefaultTypesReturnAndDirectives() throws Exception {
	createArtistDataSet();

	List<Object[]> result = SQLSelect.columnQuery("SELECT #result('ARTIST_ID' 'java.lang.Long'), #result('ARTIST_NAME' 'java.lang.String') FROM ARTIST_CT")
	.select(context);

	assertEquals(2, result.size());
	assertTrue(result.get(0) instanceof Object[]);
	assertEquals(2, result.get(0).length);
	assertTrue(result.get(0)[0] instanceof Long);
	assertTrue(result.get(0)[1] instanceof String);
	}

	@Test
	public void testObjectArrayWithDefaultTypesReturnAndDirectivesMappedToPojo() throws Exception {
	createArtistDataSet();

	List<ArtistDataWrapper> result = SQLSelect
	.columnQuery("SELECT #result('ARTIST_ID' 'java.lang.Long'), #result('ARTIST_NAME' 'java.lang.String') FROM ARTIST_CT")
	.map(ArtistDataWrapper::new)
	.select(context);

	assertEquals(2, result.size());
	assertTrue(result.get(0).id > 0);
	assertNotNull(result.get(0).name);
	}

	@Test(expected = CayenneRuntimeException.class)
	public void testObjectArrayReturnAndDirectives() throws Exception {
	createArtistDataSet();

	SQLSelect.columnQuery("SELECT #result('ARTIST_ID' 'java.lang.Long'), #result('ARTIST_NAME' 'java.lang.String') FROM ARTIST_CT",
	Integer.class, String.class).select(context);
	}

	@Test
	public void testObjectArrayWithOneObjectDefaultTypesReturnAndDirectives() throws Exception {
	createArtistDataSet();

	List<Object[]> result = SQLSelect.columnQuery("SELECT #result('ARTIST_ID' 'java.lang.Long') FROM ARTIST_CT")
	.select(context);

	assertEquals(2, result.size());
	assertTrue(result.get(0) instanceof Object[]);
	assertEquals(1, result.get(0).length);
	assertTrue(result.get(0)[0] instanceof Long);
	}

	@Test
	public void test_ObjectArrayQueryWithDefaultTypes() throws Exception {
	createPaintingsDataSet();

	List<Object[]> result = SQLSelect.columnQuery("SELECT PAINTING_ID, PAINTING_TITLE, ESTIMATED_PRICE FROM PAINTING")
	.select(context);

	assertEquals(20, result.size());
	assertEquals(3, result.get(0).length);
	}

	@Test
	public void test_ObjectQueryWithDefaultType() throws Exception {
	createPaintingsDataSet();

	List<Object[]> result = SQLSelect.columnQuery("SELECT PAINTING_ID FROM PAINTING")
	.select(context);
	assertEquals(20, result.size());
	assertTrue(result.get(0) instanceof Object[]);
	assertTrue(result.get(0)[0] instanceof Integer);
	}

	@Test(expected = CayenneRuntimeException.class)
	public void test_ObjectArrayQueryException() throws Exception {
	createPaintingsDataSet();

	SQLSelect<Object[]> query = SQLSelect.columnQuery("SELECT PAINTING_ID, PAINTING_TITLE, ESTIMATED_PRICE FROM PAINTING", Integer.class, String.class);
	context.performQuery(query);
	}

	@Test
	public void test_SingleObjectQuery() throws Exception {
	createPaintingsDataSet();

	List<Integer> result = SQLSelect.scalarQuery("SELECT PAINTING_ID FROM PAINTING", Integer.class)
	.select(context);
	assertEquals(20, result.size());
	assertTrue(result.get(0) instanceof Integer);
	}

	@Test
	public void testObjectArrayWithCustomType() throws SQLException {
	createArtistDataSet();

	List<Object[]> results = SQLSelect.columnQuery("SELECT * FROM ARTIST_CT",
	Integer.class, String.class, LocalDateTime.class).select(context);

	assertEquals(2, results.size());
	assertTrue(results.get(0) instanceof Object[]);
	assertEquals(3, results.get(0).length);
	assertTrue(results.get(0)[2] instanceof LocalDateTime);
	}

	@Test
	public void testObjectArrayWithCustomTypeMappedToPojo() throws SQLException {
	createArtistDataSet();

	List<ArtistDataWrapper> result = SQLSelect.scalarQuery("SELECT * FROM ARTIST_CT",
	Integer.class, String.class, LocalDateTime.class)
	.map(ArtistDataWrapper::new)
	.select(context);

	assertEquals(2, result.size());
	assertTrue(result.get(0).id > 0);
	assertNotNull(result.get(0).name);
	assertNotNull(result.get(0).date);
	}

	@Test
	public void test_DataRows_ClassRoot_Parameters() throws Exception {
	createPaintingsDataSet();

	SQLSelect<Painting> q1 = SQLSelect.query(Painting.class,
	"SELECT * FROM PAINTING WHERE PAINTING_TITLE = #bind($a)");
	q1.param("a", "painting3").columnNameCaps(CapsStrategy.UPPER);

	assertFalse(q1.isFetchingDataRows());
	Painting a = context.selectOne(q1);
	assertEquals("painting3", a.getPaintingTitle());
	}

	@Test
	public void test_DataRows_ClassRoot_Bind() throws Exception {
	createPaintingsDataSet();

	SQLSelect<Painting> q1 = SQLSelect.query(Painting.class,
	"SELECT * FROM PAINTING WHERE PAINTING_TITLE = #bind($a) OR PAINTING_TITLE = #bind($b)")
	.columnNameCaps(CapsStrategy.UPPER);
	q1.param("a", "painting3").param("b", "painting4");

	List<Painting> result = context.select(q1);
	assertEquals(2, result.size());
	}

	@Test
	public void test_DataRows_ColumnNameCaps() {
	SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM PAINTING WHERE PAINTING_TITLE = 'painting2'");
	q1.upperColumnNames();

	SQLTemplate r1 = (SQLTemplate) q1.getReplacementQuery(context.getEntityResolver());
	assertEquals(CapsStrategy.UPPER, r1.getColumnNamesCapitalization());

	q1.lowerColumnNames();
	SQLTemplate r2 = (SQLTemplate) q1.getReplacementQuery(context.getEntityResolver());
	assertEquals(CapsStrategy.LOWER, r2.getColumnNamesCapitalization());
	}

	@Test
	public void test_DataRows_FetchLimit() throws Exception {
	createPaintingsDataSet();

	SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM PAINTING");
	q1.limit(5);

	assertEquals(5, context.select(q1).size());
	}

	@Test
	public void test_DataRows_FetchOffset() throws Exception {
	createPaintingsDataSet();

	SQLSelect<DataRow> q1 = SQLSelect.dataRowQuery("SELECT * FROM PAINTING");
	q1.offset(4);

	assertEquals(16, context.select(q1).size());
	}

	@Test
	public void test_Append() throws Exception {
	createPaintingsDataSet();

	SQLSelect<Painting> q1 = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING")
	.append(" WHERE PAINTING_TITLE = #bind($a)").param("a", "painting3")
	.columnNameCaps(CapsStrategy.UPPER);

	List<Painting> result = context.select(q1);
	assertEquals(1, result.size());
	}

	@Test
	public void test_Select() throws Exception {
	createPaintingsDataSet();

	List<Painting> result = SQLSelect
	.query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE = #bind($a)")
	.param("a", "painting3")
	.columnNameCaps(CapsStrategy.UPPER)
	.select(context);

	assertEquals(1, result.size());
	}

	@Test
	public void test_SelectOne() throws Exception {
	createPaintingsDataSet();

	Painting a = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE = #bind($a)")
	.param("a", "painting3").columnNameCaps(CapsStrategy.UPPER).selectOne(context);

	assertEquals("painting3", a.getPaintingTitle());
	}

	@Test
	public void test_SelectFirst() throws Exception {
	createPaintingsDataSet();

	Painting p = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING ORDER BY PAINTING_TITLE").columnNameCaps(CapsStrategy.UPPER).selectFirst(
	context);

	assertNotNull(p);
	assertEquals("painting1", p.getPaintingTitle());
	}

	@Test
	public void test_SelectFirstByContext() throws Exception {
	createPaintingsDataSet();

	SQLSelect<Painting> q = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING ORDER BY PAINTING_TITLE").columnNameCaps(CapsStrategy.UPPER);
	Painting p = context.selectFirst(q);

	assertNotNull(p);
	assertEquals("painting1", p.getPaintingTitle());
	}

	@Test
	public void test_Iterate() throws Exception {
	createPaintingsDataSet();

	final int[] count = new int[1];
	SQLSelect.query(Painting.class, "SELECT * FROM PAINTING").columnNameCaps(CapsStrategy.UPPER)
	.iterate(context, object -> {
	assertNotNull(object.getPaintingTitle());
	count[0]++;
	});

	assertEquals(20, count[0]);
	}

	@Test
	public void test_Iterator() throws Exception {
	createPaintingsDataSet();

	try (ResultIterator<Painting> it = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING")
	.columnNameCaps(CapsStrategy.UPPER).iterator(context)) {
	int count = 0;

	for (Painting p : it) {
	count++;
	}

	assertEquals(20, count);
	}
	}

	@Test
	public void test_BatchIterator() throws Exception {
	createPaintingsDataSet();

	try (ResultBatchIterator<Painting> it = SQLSelect.query(Painting.class, "SELECT * FROM PAINTING")
	.columnNameCaps(CapsStrategy.UPPER).batchIterator(context, 5)) {
	int count = 0;

	for (List<Painting> paintingList : it) {
	count++;
	assertEquals(5, paintingList.size());
	}

	assertEquals(4, count);
	}
	}

	@Test
	public void test_SelectLong() throws Exception {
	createPaintingsDataSet();

	long id = SQLSelect
	.scalarQuery( "SELECT PAINTING_ID FROM PAINTING WHERE PAINTING_TITLE = #bind($a)",
	Integer.class)
	.param("a", "painting3").selectOne(context);

	assertEquals(3L, id);
	}

	@Test
	public void test_SelectLongArray() throws Exception {
	createPaintingsDataSet();

	List<Integer> ids = SQLSelect.scalarQuery("SELECT PAINTING_ID FROM PAINTING ORDER BY PAINTING_ID",
	Integer.class).select(context);

	assertEquals(20, ids.size());
	assertEquals(2L, ids.get(1).intValue());
	}

	@Test
	public void test_SelectCount() throws Exception {
	createPaintingsDataSet();

	int c = SQLSelect.scalarQuery("SELECT COUNT(*) FROM PAINTING", Integer.class).selectOne(context);

	assertEquals(20, c);
	}

	@Test
	public void test_ParamsArray_Single() throws Exception {
	createPaintingsDataSet();

	Integer id = SQLSelect
	.scalarQuery( "SELECT PAINTING_ID FROM PAINTING WHERE PAINTING_TITLE = #bind($a)",
	Integer.class)
	.paramsArray("painting3").selectOne(context);

	assertEquals(3L, id.intValue());
	}

	@Test
	public void test_ParamsArray_Multiple() throws Exception {
	createPaintingsDataSet();

	List<Integer> ids = SQLSelect
	.scalarQuery("SELECT PAINTING_ID FROM PAINTING WHERE PAINTING_TITLE = #bind($a) " +
	"OR PAINTING_TITLE = #bind($b) ORDER BY PAINTING_ID",
	Integer.class)
	.paramsArray("painting3", "painting2").select(context);

	assertEquals(2L, ids.get(0).intValue());
	assertEquals(3L, ids.get(1).intValue());
	}

	@Test
	@Ignore("This is supported by Velocity only")
	// TODO: move this test to new cayenne-velocity module
	public void test_ParamsArray_Multiple_OptionalChunks() throws Exception {
	tPainting.insert(1, "painting1", 1.0);
	tPainting.insert(2, "painting2", null);

	List<Integer> ids = SQLSelect
	.scalarQuery(
	"SELECT PAINTING_ID FROM PAINTING #chain('OR' 'WHERE') "
	+ "#chunk($a) ESTIMATED_PRICE #bindEqual($a) #end "
	+ "#chunk($b) PAINTING_TITLE #bindEqual($b) #end #end ORDER BY PAINTING_ID",
	Integer.class)
	.paramsArray(null, "painting1").select(context);

	assertEquals(1, ids.size());
	assertEquals(1L, ids.get(0).longValue());
	}

	@Test
	@Ignore("This is supported by Velocity only")
	// TODO: move this test to new cayenne-velocity module
	public void test_Params_Multiple_OptionalChunks() throws Exception {
	tPainting.insert(1, "painting1", 1.0);
	tPainting.insert(2, "painting2", null);

	Map<String, Object> params = new HashMap<>();
	params.put("a", null);
	params.put("b", "painting1");

	List<Integer> ids = SQLSelect
	.scalarQuery(
	"SELECT PAINTING_ID FROM PAINTING #chain('OR' 'WHERE') "
	+ "#chunk($a) ESTIMATED_PRICE #bindEqual($a) #end "
	+ "#chunk($b) PAINTING_TITLE #bindEqual($b) #end #end ORDER BY PAINTING_ID",
	Integer.class)
	.params(params).select(context);

	assertEquals(1, ids.size());
	assertEquals(1L, ids.get(0).longValue());
	}

	@Test
	public void testByteArray() throws Exception {
	byte[] data = {1, 2, 3};
	tPainting.insert(1, "test", 0);
	tPaintingInfo.insert(1, data);

	byte[] bytes = SQLSelect
	.scalarQuery("SELECT IMAGE_BLOB FROM PAINTING_INFO", byte[].class)
	.selectOne(context);
	assertArrayEquals(data, bytes);
	}

	static class ArtistDataWrapper {
	long id;
	String name;
	LocalDateTime date;
	ArtistDataWrapper(Object[] data) {
	id = ((Number)data[0]).longValue();
	name = (String)data[1];
	if(data.length > 2) {
	date = (LocalDateTime)data[2];
	}
	}
	}
	}