Examples of org.apache.cayenne.query.SQLTemplate

org.apache.cayenne.query.SQLTemplate
A query that executes unchanged (except for template preprocessing) "raw" SQL specified by the user.
Template Script

SQLTemplate stores a dynamic template for the SQL query that supports parameters and customization using Velocity scripting language. The most straightforward use of scripting abilities is to build parameterized queries. For example:
```
 SELECT ID, NAME FROM SOME_TABLE WHERE NAME LIKE $a 
```
For advanced scripting options see "Scripting SQLTemplate" chapter in the User Guide.

Per-Database Template Customization

SQLTemplate has a {@link #getDefaultTemplate() default template script}, but also it allows to configure multiple templates and switch them dynamically. This way a single query can have multiple "dialects" specific to a given database.

Parameter Sets

SQLTemplate supports multiple sets of parameters, so a single query can be executed multiple times with different parameters. "Scrolling" through parameter list is done by calling {@link #parametersIterator()}. This iterator goes over parameter sets, returning a Map on each call to "next()"
@since 1.1


    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"));

View Full Code Here


    public void testFetchDataRows() throws Exception {
        createFourArtists();


        String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
        SQLTemplate query = new SQLTemplate(Artist.class, template);


        sqlTemplateCustomizer.updateSQLTemplate(query);


        query.setFetchingDataRows(true);


        List<DataRow> rows = context.performQuery(query);
        assertEquals(4, rows.size());


        DataRow row2 = rows.get(1);

View Full Code Here


    public void testFetchObjects() throws Exception {
        createFourArtists();


        String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
        SQLTemplate query = sqlTemplateCustomizer.createSQLTemplate(
                Artist.class,
                template);


        query.setFetchingDataRows(false);


        List<?> objects = context.performQuery(query);
        assertEquals(4, objects.size());
        assertTrue(objects.get(1) instanceof Artist);

View Full Code Here


        Artist a = Cayenne.objectForPK(context, Artist.class, 101);


        String template = "SELECT * FROM PAINTING "
                + "WHERE #bindObjectEqual($a) ORDER BY PAINTING_ID";
        SQLTemplate query = new SQLTemplate(Painting.class, template);
        query.setColumnNamesCapitalization(CapsStrategy.UPPER);
        query.setParameters(Collections.singletonMap("a", a));


        List<?> objects = context.performQuery(query);
        assertEquals(1, objects.size());


        Painting p = (Painting) objects.get(0);

View Full Code Here


        Artist a = Cayenne.objectForPK(context, Artist.class, 101);


        String template = "SELECT * FROM PAINTING "
                + "WHERE #bindObjectNotEqual($a) ORDER BY PAINTING_ID";
        SQLTemplate query = new SQLTemplate(Painting.class, template);
        query.setColumnNamesCapitalization(CapsStrategy.UPPER);
        query.setParameters(Collections.singletonMap("a", a));


        List<?> objects = context.performQuery(query);


        // null comparison is unpredictable across DB's ... some would return true on null
        // <> value, some - false

View Full Code Here


        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.setParameters(Collections.singletonMap("a", a));


        List<?> objects = context.performQuery(query);
        assertEquals(1, objects.size());


        Painting p = (Painting) objects.get(0);

View Full Code Here


        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.setParameters(Collections.singletonMap("a", a));


        List<?> objects = context.performQuery(query);
        assertEquals(1, objects.size());


        Painting p = (Painting) objects.get(0);

View Full Code Here

    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.setParameters(Collections.singletonMap("a", null));


        List<?> objects = context.performQuery(query);
        assertEquals(1, objects.size());


        Painting p = (Painting) objects.get(0);

View Full Code Here


        Artist a = Cayenne.objectForPK(context, Artist.class, 101);


        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.setParameters(Collections.singletonMap("a", a));


        List<?> objects = context.performQuery(query);
        // null comparison is unpredictable across DB's ... some would return true on null
        // <> value, some - false
        assertTrue(objects.size() == 1 || objects.size() == 2);

View Full Code Here

        deleteTestData();
    }


    public void testReleaseUnreferenced() throws Exception {
        final DataContext context = createDataContext();
        context.performGenericQuery(new SQLTemplate(
                Artist.class,
                "insert into ARTIST (ARTIST_ID, ARTIST_NAME) values (1, 'aa')"));


        assertEquals(0, context.getObjectStore().registeredObjectsCount());
        context.performQuery(new SelectQuery(Artist.class));

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.apache.cayenne.query.SQLTemplate

net.bnubot.db.Command

org.apache.cayenne.access.DataContextCharPKTest

org.apache.cayenne.access.DataContextDecoratedStackTest

org.apache.cayenne.access.DataContextDeleteRulesTest

org.apache.cayenne.access.DataContextEJBQLConditionsPeopleTest

org.apache.cayenne.access.DataContextEJBQLFunctionalExpressions

org.apache.cayenne.access.DataContextEJBQLFunctionalExpressionsTemp

org.apache.cayenne.access.DataContextExtendedTypeOperationsTest

org.apache.cayenne.access.DataContextExtrasTest

org.apache.cayenne.access.DataContextObjectIdQueryTest

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.

Examples of org.apache.cayenne.query.SQLTemplate

Template Script

Per-Database Template Customization

Parameter Sets

Related Classes of org.apache.cayenne.query.SQLTemplate