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

        assertTrue(sql, sql.endsWith("WHERE t0.PAINTING_TITLE "
                + "LIKE #bind('Stuff' 'VARCHAR')"));
    }


    public void testSelectFromWhereNotLike() {
        SQLTemplate query = translateSelect("select p from Painting p where p.paintingTitle NOT like 'Stuff'");
        String sql = query.getDefaultTemplate();


        assertTrue(sql, sql.endsWith("WHERE t0.PAINTING_TITLE "
                + "NOT LIKE #bind('Stuff' 'VARCHAR')"));
    }

View Full Code Here


    public void testSelectPositionalParameters() {
        Map<Integer, Object> params = new HashMap<Integer, Object>();
        params.put(new Integer(1), "X");
        params.put(new Integer(2), "Y");
        SQLTemplate query = translateSelect(
                "select a from Artist a where a.artistName = ?1 or a.artistName = ?2",
                params);
        String sql = query.getDefaultTemplate();
        assertTrue(sql, sql
                .endsWith("t0.ARTIST_NAME = #bind($id0) OR t0.ARTIST_NAME = #bind($id1)"));
    }

View Full Code Here

        assertTrue(sql, sql
                .endsWith("t0.ARTIST_NAME = #bind($id0) OR t0.ARTIST_NAME = #bind($id1)"));
    }


    public void testMax() {
        SQLTemplate query = translateSelect("select max(p.estimatedPrice) from Painting p");
        String sql = query.getDefaultTemplate();


        assertTrue(sql, sql.startsWith("SELECT "
                + "#result('MAX(t0.ESTIMATED_PRICE)' 'java.math.BigDecimal' 'sc0') "
                + "FROM PAINTING t0"));
    }

View Full Code Here

                + "#result('MAX(t0.ESTIMATED_PRICE)' 'java.math.BigDecimal' 'sc0') "
                + "FROM PAINTING t0"));
    }


    public void testDistinctSum() {
        SQLTemplate query = translateSelect("select sum( distinct p.estimatedPrice) from Painting p");
        String sql = query.getDefaultTemplate();


        assertTrue(
                sql,
                sql
                        .startsWith("SELECT #result('SUM(DISTINCT t0.ESTIMATED_PRICE)' 'java.math.BigDecimal' 'sc0') "

View Full Code Here

                        .startsWith("SELECT #result('SUM(DISTINCT t0.ESTIMATED_PRICE)' 'java.math.BigDecimal' 'sc0') "
                                + "FROM PAINTING t0"));
    }


    public void testColumnPaths() {
        SQLTemplate query = translateSelect("select p.estimatedPrice, p.toArtist.artistName from Painting p");
        String sql = query.getDefaultTemplate();


        assertTrue(sql, sql.startsWith("SELECT "
                + "#result('t0.ESTIMATED_PRICE' 'java.math.BigDecimal' 'sc0' 'sc0' 3), "
                + "#result('t1.ARTIST_NAME' 'java.lang.String' 'sc1' 'sc1' 1) FROM"));
    }

View Full Code Here

        tArtist.insert(3001, "artist5", date);
    }


    public void testProperties() throws Exception {
        DbAdapter adapter = new JdbcAdapter();
        SQLTemplate template = new SQLTemplate(Object.class, "AAAAA");
        SQLTemplateAction action = new SQLTemplateAction(template, adapter, objectContext
                .getEntityResolver());
        assertSame(adapter, action.getAdapter());
        assertSame(template, action.getQuery());
    }

View Full Code Here


    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<String, Object>();
        bindings.put("id", 201l);
        template.setParameters(bindings);


        // must ensure the right SQLTemplateAction is created


        SQLAction plan = adapter.getAction(template, node);
        assertTrue(plan instanceof SQLTemplateAction);

View Full Code Here

    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<String, Object>();
        bindings.put("id", 101);
        template.setParameters(bindings);


        SQLAction plan = adapter.getAction(template, node);


        MockOperationObserver observer = new MockOperationObserver();
        Connection c = dataSource.getConnection();

View Full Code Here

    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<String, Object>();
        bindings.put("id", 101);
        template.setParameters(bindings);


        SQLAction plan = adapter.getAction(template, node);


        MockOperationObserver observer = new MockOperationObserver();
        Connection c = dataSource.getConnection();

View Full Code Here

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


        String templateString = "SELECT #result('DATE_OF_BIRTH' 'java.sql.Timestamp' 'DOB') "
                + "FROM ARTIST WHERE ARTIST_ID = #bind($id)";
        SQLTemplate template = new SQLTemplate(Object.class, templateString);
        sqlTemplateCustomizer.updateSQLTemplate(template);


        Map<String, Object> bindings = new HashMap<String, Object>();
        bindings.put("id", 201);
        template.setParameters(bindings);


        SQLAction plan = adapter.getAction(template, node);


        MockOperationObserver observer = new MockOperationObserver();
        Connection c = dataSource.getConnection();

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