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 |
|
A static SQL statement with all information hard-coded. |
ApplicationModule.createViewObjectFromQueryStmt
Note: View Objects created using |
|
|
A static SQL statement with a WHERE clause added at run time. |
ViewObject.setWhereClause | |
|
A query that takes one or more parameters. |
ViewObject.setWhereClauseParam, | |
|
A class that shows how to use methods for working with query clauses and query-by-example. |
ViewObject.setWhereClause, |
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();
} 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);
} 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);
}
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);
}
}