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 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<String, Object>();
        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);


        Connection c = dataSource.getConnection();
        try {

View Full Code Here

    }


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


        SQLTemplate template = new SQLTemplate(
                Object.class,
                "delete from ARTIST where ARTIST_NAME like 'a%'");


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

View Full Code Here

    }


    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<String, Object>();
        bindings1.put("id", new Long(1));
        bindings1.put("name", "a1");
        bindings1.put("dob", new Date(System.currentTimeMillis()));


        Map<String, Object> bindings2 = new HashMap<String, Object>();
        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);

View Full Code Here

        // to compare dates we need to create the binding correctly
        // assertEquals(bindings2.get("dob"), row2.get("DATE_OF_BIRTH"));
    }


    public void testExtractTemplateString() throws Exception {
        SQLTemplate template = new SQLTemplate(Artist.class, "A\nBC");
        SQLTemplateAction action = new SQLTemplateAction(template, adapter, objectContext
                .getEntityResolver());


        assertEquals("A BC", action.extractTemplateString());
    }

View Full Code Here

        // now select only with names: Artist1 and Artist3
        Set<String> artistNames = new HashSet<String>();
        artistNames.add("Artist1");
        artistNames.add("Artist3");
        String sql = "SELECT * FROM ARTIST WHERE ARTIST_NAME in (#bind($ARTISTNAMES))";
        SQLTemplate query = new SQLTemplate(Artist.class, sql);
        query.setColumnNamesCapitalization(CapsStrategy.UPPER);
        query.setParameters(Collections.singletonMap("ARTISTNAMES", artistNames));
        List<DataRow> result = createDataContext().performQuery(query);
        assertEquals(2, result.size());
    }

View Full Code Here

        }
        else {
            templateString = "INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME, DATE_OF_BIRTH) "
                    + "VALUES (#bind($id), #bind($name), #bind($dob))";
        }
        SQLTemplate template = new SQLTemplate(Object.class, templateString);


        template.setParameters(parameters);


        SQLTemplateAction action = new SQLTemplateAction(
                template,
                getAccessStackAdapter().getAdapter(),
                getDomain().getEntityResolver());

View Full Code Here


        c1 = new DataContext(getDomain(), new ObjectStore(cache));
        c2 = new DataContext(getDomain(), new ObjectStore(cache));


        // prepare a single artist record
        SQLTemplate insert = new SQLTemplate(
                Artist.class,
                "insert into ARTIST (ARTIST_ID, ARTIST_NAME) values (1, 'version1')");
        c1.performNonSelectingQuery(insert);
    }

View Full Code Here

                + "FROM ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID) "
                + "GROUP BY t0.ARTIST_ID, t0.ARTIST_NAME, t0.DATE_OF_BIRTH "
                + "ORDER BY t0.ARTIST_ID";


        DataMap map = context.getEntityResolver().getDataMap("testmap");
        SQLTemplate query = new SQLTemplate(map, sql, false);
        query.setColumnNamesCapitalization(CapsStrategy.UPPER);


        EntityResult artistResult = new EntityResult(Artist.class);
        artistResult.addDbField(Artist.ARTIST_ID_PK_COLUMN, "X");
        artistResult.addObjectField(Artist.ARTIST_NAME_PROPERTY, "Y");
        artistResult.addObjectField(Artist.DATE_OF_BIRTH_PROPERTY, "Z");


        SQLResult rsMap = new SQLResult();
        rsMap.addEntityResult(artistResult);
        rsMap.addColumnResult("C");
        query.setResult(rsMap);


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


        Object o1 = objects.get(0);

View Full Code Here

        createFourArtists();


        String sql = "SELECT count(1) AS X 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 FROM ARTIST");
        query.setTemplate(
                OpenBaseAdapter.class.getName(),
                "SELECT COUNT(ARTIST_ID) X FROM ARTIST");
        query.setColumnNamesCapitalization(CapsStrategy.UPPER);


        SQLResult rsMap = new SQLResult();
        rsMap.addColumnResult("X");
        query.setResult(rsMap);


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


        Object o = objects.get(0);

View Full Code Here

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

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