# Creating and Changing View Object Queries in Code

The examples in this topic show techniques for using SQL to define and modify query statements used by View Objects. The following table describes the example methods and lists the key Business Components for Java framework methods each one uses.

Example | Description | Key framework methods
demoSimpleFetch | A static SQL statement with all information hard-coded. |
    ApplicationModule.createViewObjectFromQueryStmt
Note: View Objects created using `createViewObjectFromQueryStmt` are read-only because there are no underlying Entity Objects.
demoWhereClause | A static SQL statement with a WHERE clause added at run time. |
    ViewObject.setWhereClause
demoParamFetch | A query that takes one or more parameters. |
    ViewObject.setWhereClauseParam,
    ViewObject.setWhereClauseParams
ClausesDemo | A class that shows how to use methods for working with query clauses and query-by-example. |
    ViewObject.setWhereClause,
    ViewObject.setOrderByClause,
    ApplicationModule.createViewObjectFromQueryClauses,
    ViewObject.createViewCriteria,
    ViewCriteria.createViewCriteriaRow,
    ViewCriteriaRow.setAttribute,
    ViewCriteria.addElement,
    ViewObject.applyViewCriteria

### Simple Fetch Example

A static SQL statement with all information hard-coded.

    public static void demoSimpleFetch(ApplicationModule appMod) {
       // Define basic query string.
       String sqlStr = "SELECT Emp.ename, Emp.mgr FROM EMP Emp ";
       ViewObject vo = appMod.createViewObjectFromQueryStmt("QueryDemo", sqlStr);
       printViewObject(vo);
       vo.remove();
    }

### Where Clause Example

    public static void demoWhereClause(ApplicationModule appMod) {
       // Define basic query string.
       String sqlStr = "SELECT Emp.ename, Emp.mgr FROM EMP Emp ";
       ViewObject vo = appMod.createViewObjectFromQueryStmt("whereDemo", sqlStr);
       // Define and set WHERE clause. (See also ViewObject.setOrderByClause)
       vo.setWhereClause(" Emp.mgr > 7700 ");
       printViewObject(vo);
    }

### Parameter Fetching Example

The following examples uses the question mark (`?`) parameter style instead of the colon (`:`) style. The View Object Wizard requires that you choose one style for use by your query. Be sure to set the Use ? style parameter field in the View Object Wizard as appropriate for your dynamic query.

    public static void demoParamFetch(ApplicationModule appMod, Object inParam) {

        // Query takes one parameter: Emp.mgr.
        String sqlStr = "SELECT Emp.ename, Emp.mgr FROM EMP Emp ";
        ViewObject vo = appMod.createViewObjectFromQueryStmt("oneParam",
                                                             sqlStr);

        vo.setWhereClause(" Emp.mgr > ? ");

        // Set one parameter value. 0 specifies the first parameter.
        vo.setWhereClauseParam(0, inParam);

        // To remove or reset parameters, call vo.setWhereClauseParams(null).
        printViewObject(vo);

        // Query takes two parameters: Emp.mgr and Emp.sal.
        sqlStr = "SELECT Emp.ename, Emp.mgr, Emp.sal FROM EMP Emp " +
                 "WHERE Emp.mgr = ? " +
                 "AND Emp.sal <= ? ";

        vo = appMod.createViewObjectFromQueryStmt("multiParams", sqlStr);

        // Use an array to set several parameters with one method call.
        Object[] params = new Object[2];
        params[0] = inParam;
        params[1] = new Integer(3000);

        vo.setWhereClauseParams(params);

        // To remove or reset parameters, call vo.setWhereClauseParams(null).
        printViewObject(vo);
    }

### Examples of Working with View Objects, SQL, and Query Clauses

The following example class shows how to use methods for working with View Objects, SQL, and query clauses. It also shows how to define criteria to support query-by-example.

    package d2e;
    import oracle.jbo.*;

    public class ClausesDemo
    {
       // Helper routine connects to the generic Application Module.

          public static void main(String[] args) {
          ApplicationModule appMod =
                        QueryDemo.getGenericAppMod(JboContext.PLATFORM_LOCAL);

          // Define basic query string.
          String sqlStr = "SELECT empno, ename, job, mgr FROM EMP ";

          // View Object based on Query clauses.
          ViewObject vo = appMod.createViewObjectFromQueryClauses("clausesVO",
                 "d2e.Emp",
                 "empno, ename, job, mgr",
                 "Emp",
                 null,
                 null);

          demoWhereClause(vo);
          demoOrderByClause(vo);
          demoCriteria(appMod);
          demoCreateFromClauses(appMod);
       }

       public static void demoWhereClause(ViewObject vo) {

          // Define and set WHERE clause.
          String whereStr = " Emp.empno = 7839 ";
          vo.setWhereClause(whereStr);
          System.out.println("Demo Where Emp.empno = 7839");

          //Should print only one employee with empno = 7839
          QueryDemo.printViewObject(vo);

          // Replace the previous WHERE clause with the following.
          System.out.println("Demo Where Emp.empno = 7566");
          whereStr = " Emp.empno = 7566 ";
          vo.setWhereClause(whereStr);

          //Should print only one employee with empno = 7566
          QueryDemo.printViewObject(vo);
       }

       public static void demoOrderByClause(ViewObject vo) {

          // Define and set ORDER BY clause.
          String orderStr = " Emp.job ";
          vo.setWhereClause("");
          vo.setOrderByClause(orderStr);
          System.out.println("Demo Order By Emp.job");

          //Should print all employees ordered by Job column
          QueryDemo.printViewObject(vo);

          // Replace the previous ORDER BY clause with the following.
          orderStr = " Emp.mgr ";
          vo.setOrderByClause(orderStr);
          System.out.println("Demo Order By Emp.mgr");

          //Should print all employees ordered by Mgr column
          QueryDemo.printViewObject(vo);
       }

       public static void demoCriteria(ApplicationModule appMod) {

          // Create and populate criteria rows to support query-by-example.
          ViewObject empView = appMod.createViewObject("emp",
          "d2e.EmpView");
          ViewCriteria vc = empView.createViewCriteria();
          ViewCriteriaRow vcRow = vc.createViewCriteriaRow();

          // ViewCriteriaRow attribute name is case-sensitive.
          // ViewCriteriaRow attribute value requires operator and value.
          // Note also single-quotes around string value.
          vcRow.setAttribute("Job", "= 'MANAGER'");
          vc.addElement(vcRow);

          vcRow = vc.createViewCriteriaRow();
          vcRow.setAttribute("Sal", "> 2500");

          vc.addElement(vcRow);
          empView.applyViewCriteria(vc);

          // Multiple rows are OR-ed in WHERE clause.
          System.out.println("Demo View Criteria");

          //Should print employees that are MANAGER or have Sal > 2500
          QueryDemo.printViewObject(empView);
       }

       public static void demoCreateFromClauses(ApplicationModule appMod)
       {
          String voName = "demoClauses";
          String eoDefName = "d2e.Emp";
          String selectClause = "E.EMPNO, E.ENAME, E.JOB, E.DEPTNO";
          String fromClause = "EMP E";
          String whereClause = "E.DEPTNO = 10 ";
          String orderByClause = null;
          ViewObject vo = appMod.createViewObjectFromQueryClauses(
                                              voName,
                                              eoDefName,
                                              selectClause,
                                              fromClause,
                                              whereClause,
                                              orderByClause);

          System.out.println("Demo ViewObject created from clauses");

          //Should print all employees for Dept = 10
          QueryDemo.printViewObject(vo);

          // WHERE clauses are AND-ed together.
          vo.setWhereClause("job = 'MANAGER' ");

          System.out.println("Demo where clauses AND-ed together");

          //Should print all employees for Dept = 10 and Job = 'MANAGER'
          QueryDemo.printViewObject(vo);
       }
    }