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

Example 41 with SQLTemplate

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

the class DataContextSQLTemplateIT method testBindObjectEqualFullNonArray.

@Test
public void testBindObjectEqualFullNonArray() throws Exception {
    createFourArtistsAndThreePaintingsDataSet();
    Artist a = Cayenne.objectForPK(context, Artist.class, 101);
    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", a));
    List<?> objects = context.performQuery(query);
    assertEquals(1, objects.size());
    Painting p = (Painting) objects.get(0);
    assertEquals(7, Cayenne.intPKForObject(p));
}

Example 42 with SQLTemplate

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

the class DataContextSharedCacheIT method testCacheRefreshingOnSelect.

/**
 * Checks that cache is refreshed when a query "refreshingObjects" property is set to
 * true.
 */
@Test
public void testCacheRefreshingOnSelect() throws Exception {
    String originalName = artist.getArtistName();
    final String newName = "version2";
    DataContext context = (DataContext) artist.getObjectContext();
    DataRow oldSnapshot = context.getObjectStore().getDataRowCache().getCachedSnapshot(artist.getObjectId());
    assertNotNull(oldSnapshot);
    assertEquals(originalName, oldSnapshot.get("ARTIST_NAME"));
    // update artist using raw SQL
    SQLTemplate update = sqlTemplateCustomizer.createSQLTemplate(Artist.class, "UPDATE ARTIST SET ARTIST_NAME = #bind($newName) WHERE ARTIST_NAME = #bind($oldName)");
    Map<String, Object> map = new HashMap<>(3);
    map.put("newName", newName);
    map.put("oldName", originalName);
    update.setParams(map);
    context.performNonSelectingQuery(update);
    // fetch updated artist without refreshing
    Expression qual = ExpressionFactory.matchExp("artistName", newName);
    SelectQuery query = new SelectQuery<>(Artist.class, qual);
    List artists = context.performQuery(query);
    assertEquals(1, artists.size());
    artist = (Artist) artists.get(0);
    // check underlying cache
    DataRow freshSnapshot = context.getObjectStore().getDataRowCache().getCachedSnapshot(artist.getObjectId());
    assertNotSame(oldSnapshot, freshSnapshot);
    assertEquals(newName, freshSnapshot.get("ARTIST_NAME"));
    // check an artist
    assertEquals(newName, artist.getArtistName());
}

Example 43 with SQLTemplate

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

the class DataNodeQueriesIT method testPerfomQueriesSelectingSQLTemplateAlias.

@Test
public void testPerfomQueriesSelectingSQLTemplateAlias() throws Exception {
    createFourArtists();
    String template = "SELECT #result('ARTIST_ID' 'int' 'A') FROM ARTIST ORDER BY ARTIST_ID";
    Query query = new SQLTemplate(Object.class, template);
    MockOperationObserver observer = new MockOperationObserver();
    node.performQueries(Collections.singletonList(query), observer);
    List<DataRow> data = observer.rowsForQuery(query);
    assertEquals(4, data.size());
    DataRow row = data.get(2);
    assertEquals(1, row.size());
    assertEquals(201, row.get("A"));
}

Example 44 with SQLTemplate

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

the class DataNodeQueriesIT method testPerfomQueriesSelectingSQLTemplate2.

@Test
public void testPerfomQueriesSelectingSQLTemplate2() throws Exception {
    createFourArtists();
    String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
    SQLTemplate query = new SQLTemplate(Object.class, template);
    sqlTemplateCustomizer.updateSQLTemplate(query);
    MockOperationObserver observer = new MockOperationObserver();
    node.performQueries(Collections.singletonList((Query) query), observer);
    List<DataRow> data = observer.rowsForQuery(query);
    assertEquals(4, data.size());
    DataRow row = data.get(2);
    assertEquals(3, row.size());
    Number id = (Number) row.get("ARTIST_ID");
    assertNotNull(id);
    assertEquals(201, id.intValue());
}

Example 45 with SQLTemplate

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

the class DataNodeQueriesIT method testPerfomQueriesSQLTemplate.

@Test
public void testPerfomQueriesSQLTemplate() throws Exception {
    String template = "INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME, DATE_OF_BIRTH) " + "VALUES (#bind($id), #bind($name), #bind($dob 'DATE'))";
    SQLTemplate query = new SQLTemplate(Object.class, template);
    Map<String, Object> bindings = new HashMap<>();
    bindings.put("id", 1l);
    bindings.put("name", "a1");
    bindings.put("dob", new Date(System.currentTimeMillis()));
    query.setParameters(bindings);
    MockOperationObserver observer = new MockOperationObserver();
    node.performQueries(Collections.singletonList((Query) query), observer);
    assertNotNull(observer.countsForQuery(query));
    assertEquals(1, observer.countsForQuery(query)[0]);
    // check the data
    assertEquals(1, tArtist.getRowCount());
    assertEquals(1l, tArtist.getLong("ARTIST_ID"));
    assertEquals("a1", tArtist.getString("ARTIST_NAME").trim());
}