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

Example 1 with SQLAction

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

the class SQLTemplateActionIT method testExecuteSelect.

@Test
public void testExecuteSelect() throws Exception {
    createFourArtists();
    String templateString = "SELECT * FROM ARTIST WHERE ARTIST_ID = #bind($id)";
    SQLTemplate template = new SQLTemplate(Object.class, templateString);
    sqlTemplateCustomizer.updateSQLTemplate(template);
    Map<String, Object> bindings = new HashMap<>();
    bindings.put("id", 201l);
    template.setParameters(bindings);
    // must ensure the right SQLTemplateAction is created
    SQLAction plan = adapter.getAction(template, node);
    assertTrue(plan instanceof SQLTemplateAction);
    MockOperationObserver observer = new MockOperationObserver();
    try (Connection c = dataSourceFactory.getSharedDataSource().getConnection()) {
        plan.performAction(c, observer);
    }
    List<DataRow> rows = observer.rowsForQuery(template);
    assertNotNull(rows);
    assertEquals(1, rows.size());
    DataRow row = rows.get(0);
    // In the absence of ObjEntity most DB's return a Long here, except for
    // Oracle
    // that has no BIGINT type and
    // returns BigDecimal, so do a Number comparison
    Number id = (Number) row.get("ARTIST_ID");
    assertNotNull(id);
    assertEquals(((Number) bindings.get("id")).longValue(), id.longValue());
    assertEquals("artist4", row.get("ARTIST_NAME"));
    assertTrue(row.containsKey("DATE_OF_BIRTH"));
}

Example 2 with SQLAction

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

the class SQLTemplateActionIT method testSelectSQLDate.

@Test
public void testSelectSQLDate() throws Exception {
    createFourArtists();
    String templateString = "SELECT #result('DATE_OF_BIRTH' 'java.sql.Date' 'DOB') " + "FROM ARTIST WHERE ARTIST_ID = #bind($id)";
    SQLTemplate template = new SQLTemplate(Object.class, templateString);
    sqlTemplateCustomizer.updateSQLTemplate(template);
    Map<String, Object> bindings = new HashMap<>();
    bindings.put("id", 101);
    template.setParameters(bindings);
    SQLAction plan = adapter.getAction(template, node);
    MockOperationObserver observer = new MockOperationObserver();
    try (Connection c = dataSourceFactory.getSharedDataSource().getConnection()) {
        plan.performAction(c, observer);
    }
    List<DataRow> rows = observer.rowsForQuery(template);
    assertNotNull(rows);
    assertEquals(1, rows.size());
    DataRow row = rows.get(0);
    assertNotNull(row.get("DOB"));
    assertEquals(java.sql.Date.class, row.get("DOB").getClass());
}

Example 3 with SQLAction

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

the class SQLTemplateActionIT method testExecuteUpdateBatch.

@Test
public void testExecuteUpdateBatch() throws Exception {
    String templateString = "INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME, DATE_OF_BIRTH) " + "VALUES (#bind($id), #bind($name), #bind($dob 'DATE'))";
    SQLTemplate template = new SQLTemplate(Object.class, templateString);
    Map<String, Object> bindings1 = new HashMap<>();
    bindings1.put("id", new Long(1));
    bindings1.put("name", "a1");
    bindings1.put("dob", new Date(System.currentTimeMillis()));
    Map<String, Object> bindings2 = new HashMap<>();
    bindings2.put("id", new Long(33));
    bindings2.put("name", "a$$$$$");
    bindings2.put("dob", new Date(System.currentTimeMillis()));
    template.setParameters(new Map[] { bindings1, bindings2 });
    SQLAction genericAction = adapter.getAction(template, node);
    assertTrue(genericAction instanceof SQLTemplateAction);
    SQLTemplateAction action = (SQLTemplateAction) genericAction;
    assertSame(node, action.dataNode);
    assertSame(template, action.getQuery());
    try (Connection c = dataSourceFactory.getSharedDataSource().getConnection()) {
        MockOperationObserver observer = new MockOperationObserver();
        action.performAction(c, observer);
        int[] batches = observer.countsForQuery(template);
        assertNotNull(batches);
        assertEquals(2, batches.length);
        assertEquals(1, batches[0]);
        assertEquals(1, batches[1]);
    }
    MockOperationObserver observer = new MockOperationObserver();
    SelectQuery query = new SelectQuery(Artist.class);
    query.addOrdering("db:ARTIST_ID", SortOrder.ASCENDING);
    node.performQueries(Collections.singletonList((Query) query), observer);
    List<DataRow> data = observer.rowsForQuery(query);
    assertEquals(2, data.size());
    DataRow row1 = data.get(0);
    assertEquals(bindings1.get("id"), row1.get("ARTIST_ID"));
    assertEquals(bindings1.get("name"), row1.get("ARTIST_NAME"));
    // to compare dates we need to create the binding correctly
    // assertEquals(bindings1.get("dob"), row.get("DATE_OF_BIRTH"));
    DataRow row2 = data.get(1);
    assertEquals(bindings2.get("id"), row2.get("ARTIST_ID"));
    assertEquals(bindings2.get("name"), row2.get("ARTIST_NAME"));
// to compare dates we need to create the binding correctly
// assertEquals(bindings2.get("dob"), row2.get("DATE_OF_BIRTH"));
}

Example 4 with SQLAction

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

the class SQLTemplateActionIT method testExecuteUpdate.

@Test
public void testExecuteUpdate() throws Exception {
    String templateString = "INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME, DATE_OF_BIRTH) " + "VALUES (#bind($id), #bind($name), #bind($dob 'DATE'))";
    SQLTemplate template = new SQLTemplate(Object.class, templateString);
    Map<String, Object> bindings = new HashMap<>();
    bindings.put("id", new Long(1));
    bindings.put("name", "a1");
    bindings.put("dob", new Date(System.currentTimeMillis()));
    template.setParameters(bindings);
    SQLAction action = adapter.getAction(template, node);
    try (Connection c = dataSourceFactory.getSharedDataSource().getConnection()) {
        MockOperationObserver observer = new MockOperationObserver();
        action.performAction(c, observer);
        int[] batches = observer.countsForQuery(template);
        assertNotNull(batches);
        assertEquals(1, batches.length);
        assertEquals(1, batches[0]);
    }
    assertEquals(1, tArtist.getRowCount());
    assertEquals(1l, tArtist.getLong("ARTIST_ID"));
    assertEquals("a1", tArtist.getString("ARTIST_NAME").trim());
}

Example 5 with SQLAction

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

the class SQLTemplateActionIT method testSelectUtilDate.

@Test
public void testSelectUtilDate() throws Exception {
    createFourArtists();
    String templateString = "SELECT #result('DATE_OF_BIRTH' 'java.util.Date' 'DOB') " + "FROM ARTIST WHERE ARTIST_ID = #bind($id)";
    SQLTemplate template = new SQLTemplate(Object.class, templateString);
    sqlTemplateCustomizer.updateSQLTemplate(template);
    Map<String, Object> bindings = new HashMap<>();
    bindings.put("id", 101);
    template.setParameters(bindings);
    SQLAction plan = adapter.getAction(template, node);
    MockOperationObserver observer = new MockOperationObserver();
    try (Connection c = dataSourceFactory.getSharedDataSource().getConnection()) {
        plan.performAction(c, observer);
    }
    List<DataRow> rows = observer.rowsForQuery(template);
    assertNotNull(rows);
    assertEquals(1, rows.size());
    DataRow row = rows.get(0);
    assertNotNull(row.get("DOB"));
    assertEquals(java.util.Date.class, row.get("DOB").getClass());
}