Examples with SQLTemplate org.apache.cayenne.query.SQLTemplate used on opensource projects

Example 31 with SQLTemplate

use of org.apache.cayenne.query.SQLTemplate in project cayenne by apache.

the class DataContextSQLTemplateIT method testSQLResultSetMappingScalarArray.

@Test
public void testSQLResultSetMappingScalarArray() throws Exception {
    createFourArtists();
    String sql = "SELECT count(1) AS X, 77 AS Y FROM ARTIST";
    DataMap map = context.getEntityResolver().getDataMap("testmap");
    SQLTemplate query = new SQLTemplate(map, sql, false);
    query.setTemplate(FrontBaseAdapter.class.getName(), "SELECT COUNT(ARTIST_ID) X, 77 Y FROM ARTIST GROUP BY Y");
    query.setTemplate(OpenBaseAdapter.class.getName(), "SELECT COUNT(ARTIST_ID) X, 77 Y FROM ARTIST GROUP BY 77");
    query.setColumnNamesCapitalization(CapsStrategy.UPPER);
    SQLResult rsMap = new SQLResult();
    rsMap.addColumnResult("X");
    rsMap.addColumnResult("Y");
    query.setResult(rsMap);
    List<?> objects = context.performQuery(query);
    assertEquals(1, objects.size());
    Object o = objects.get(0);
    assertTrue(o instanceof Object[]);
    Object[] row = (Object[]) o;
    assertEquals(2, row.length);
    assertEquals(4, ((Number) row[0]).intValue());
    assertEquals(77, ((Number) row[1]).intValue());
}

Example 32 with SQLTemplate

use of org.apache.cayenne.query.SQLTemplate in project cayenne by apache.

the class DataContextSQLTemplateIT method testQueryWithLineBreakAfterMacro.

@Test
public void testQueryWithLineBreakAfterMacro() throws Exception {
    createFourArtists();
    // see CAY-726 for details
    String template = "SELECT #result('count(*)' 'int' 'X')" + System.getProperty("line.separator") + "FROM ARTIST";
    SQLTemplate query = sqlTemplateCustomizer.createSQLTemplate(Artist.class, template);
    query.setFetchingDataRows(true);
    List<?> result = context.performQuery(query);
    assertEquals(4, ((DataRow) result.get(0)).get("X"));
}

Example 33 with SQLTemplate

use of org.apache.cayenne.query.SQLTemplate in project cayenne by apache.

the class DataContextSQLTemplateIT method testBindObjectEqualNull.

@Test
public void testBindObjectEqualNull() throws Exception {
    createFourArtistsAndThreePaintingsDataSet();
    String template = "SELECT * FROM PAINTING t0" + " WHERE #bindObjectEqual($a [ 't0.ARTIST_ID' ] [ 'ARTIST_ID' ] ) ORDER BY PAINTING_ID";
    SQLTemplate query = new SQLTemplate(Painting.class, template);
    query.setColumnNamesCapitalization(CapsStrategy.UPPER);
    query.setParams(Collections.singletonMap("a", null));
    List<?> objects = context.performQuery(query);
    assertEquals(1, objects.size());
    Painting p = (Painting) objects.get(0);
    assertEquals(8, Cayenne.intPKForObject(p));
}

Example 34 with SQLTemplate

use of org.apache.cayenne.query.SQLTemplate in project cayenne by apache.

the class DataContextSQLTemplateIT method testColumnNamesCapitalization.

@Test
public void testColumnNamesCapitalization() throws Exception {
    createFourArtistsAndThreePaintingsDataSet();
    String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
    SQLTemplate query = new SQLTemplate(Artist.class, template);
    query.setColumnNamesCapitalization(CapsStrategy.LOWER);
    query.setFetchingDataRows(true);
    List<DataRow> rows = context.performQuery(query);
    DataRow row1 = rows.get(0);
    assertFalse(row1.containsKey("ARTIST_ID"));
    assertTrue(row1.containsKey("artist_id"));
    DataRow row2 = rows.get(1);
    assertFalse(row2.containsKey("ARTIST_ID"));
    assertTrue(row2.containsKey("artist_id"));
    query.setColumnNamesCapitalization(CapsStrategy.UPPER);
    List<DataRow> rowsUpper = context.performQuery(query);
    DataRow row3 = rowsUpper.get(0);
    assertFalse(row3.containsKey("artist_id"));
    assertTrue(row3.containsKey("ARTIST_ID"));
    DataRow row4 = rowsUpper.get(1);
    assertFalse(row4.containsKey("artist_id"));
    assertTrue(row4.containsKey("ARTIST_ID"));
}

Example 35 with SQLTemplate

use of org.apache.cayenne.query.SQLTemplate in project cayenne by apache.

the class DataContextSQLTemplateIT method testBindObjectNotEqualNull.

@Test
public void testBindObjectNotEqualNull() throws Exception {
    createFourArtistsAndThreePaintingsDataSet();
    String template = "SELECT * FROM PAINTING t0" + " WHERE #bindObjectNotEqual($a [ 't0.ARTIST_ID' ] [ 'ARTIST_ID' ] ) ORDER BY PAINTING_ID";
    SQLTemplate query = new SQLTemplate(Painting.class, template);
    query.setColumnNamesCapitalization(CapsStrategy.UPPER);
    query.setParams(Collections.singletonMap("a", null));
    List<Painting> objects = context.performQuery(query);
    assertEquals(2, objects.size());
    Painting p1 = objects.get(0);
    assertEquals(6, Cayenne.intPKForObject(p1));
    Painting p2 = objects.get(1);
    assertEquals(7, Cayenne.intPKForObject(p2));
}