Overriding default query listener ,field validation of af:query- Oracle ADF
Hello All,
this post talks about a common requirement -
can we apply custom validation in af:query fields ?
yes , we can do that by overriding default query listener of af:query component
What af:query is ?
see-
Oracle Docs-af:query
"The query component provides the user the ability to perform a query based on a saved search or personalize saved searches. The component displays a search panel with various elements, each of which help the user to accomplish various tasks."
Follow steps to apply validation on af:query-
- Here i am taking example of Departments table of HR Schema
- First create business components for Departments table
- then create bind variables and a view-criteria in departments view object
- See the view criteria is applied for LocationId, ManagerId and Department Name
- Now create a page and drop criteria with table on page- it will look like this
-
- Next is to select af:query on page and go to property inspector and copy text of default query listener
- Now create a managed bean and create a custom query listener for af:query
- In this custom query listener , in this example i am checking for negative Location Id and Manager Id, if there is any negative values ,custom method will show a message otherwise default queryListenerwill be executed
- We can get af:query's components value using 2 methods
First One - Using Bind Variable (getNamedWhereClauseParam)
Second One - Using QueryDescriptor and ConjunctionCriterion - I'm going to explain both methods - see code of custom query listener using first method, two generic methods will be used in this process to invoke expression language and to get BindingContainer of current context
/**Method to invoke EL Expression
* @param el
* @param paramTypes
* @param params
* @return
*/
public static Object invokeEL(String el, Class[] paramTypes, Object[] params) {
FacesContext facesContext = FacesContext.getCurrentInstance();
ELContext elContext = facesContext.getELContext();
ExpressionFactory expressionFactory = facesContext.getApplication().getExpressionFactory();
MethodExpression exp = expressionFactory.createMethodExpression(elContext, el, Object.class, paramTypes);
return exp.invoke(elContext, params);
}
/**Method to get Binding Container of current page
* @return
*/
public BindingContainer getBindings() {
return BindingContext.getCurrent().getCurrentBindingsEntry();
}
-
- See the first method to get af:query components value and validate that
/**Custome Query Listener- Using getNamedWhereClauseParam
* @param queryEvent
*/
public void customQueryListener(QueryEvent queryEvent) {
String deptName = null;
Integer locId = null;
Integer mgrId = null;
/**Get Iterator of Table*/
DCIteratorBinding iter = (DCIteratorBinding)getBindings().get("DepartmentsView1Iterator");
/**Get ViewObject from Iterator*/
ViewObjectImpl vo = (ViewObjectImpl)iter.getViewObject();
/**Get Bind Variable's Value*/
if (vo.getNamedWhereClauseParam("LocIdBind") != null) {
locId = Integer.parseInt(vo.getNamedWhereClauseParam("LocIdBind").toString());
}
if (vo.getNamedWhereClauseParam("MgrIdBind") != null) {
mgrId = Integer.parseInt(vo.getNamedWhereClauseParam("MgrIdBind").toString());
}
if (vo.getNamedWhereClauseParam("DeptNmBind") != null) {
deptName = vo.getNamedWhereClauseParam("DeptNmBind").toString();
}
/**Check for Negative values*/
if ((locId != null && locId < 0) || (mgrId != null && mgrId < 0)) {
FacesMessage msg = new FacesMessage("Id Value can not be negative");
msg.setSeverity(FacesMessage.SEVERITY_ERROR);
FacesContext.getCurrentInstance().addMessage(null, msg);
} else {
/**Execute default query listener with help of invokeEL method*/
invokeEL("#{bindings.DepartmentsViewCriteriaQuery.processQuery}", new Class[] { QueryEvent.class },
new Object[] { queryEvent });
}
}
-
- And Second Method using QueryDescriptor is-
/**Custom Query Listener-Using QueryDescriptor
* @param queryEvent
*/
public void customqueryProcess(QueryEvent queryEvent) {
String deptName = null;
Integer locId = null;
Integer mgrId = null;
/**Reference-Frank Nimphius Example- ADF Code Corner
* http://www.oracle.com/technetwork/developer-tools/adf/learnmore/85-querycomponent-fieldvalidation-427197.pdf
* */
QueryDescriptor qd = queryEvent.getDescriptor();
ConjunctionCriterion conCrit = qd.getConjunctionCriterion();
//access the list of search fields
List<Criterion> criterionList = conCrit.getCriterionList();
//iterate over the attributes to find FromDate and ToDate
for (Criterion criterion : criterionList) {
AttributeDescriptor attrDescriptor = ((AttributeCriterion)criterion).getAttribute();
if (attrDescriptor.getName().equalsIgnoreCase("DepartmentName")) {
deptName = (String)((AttributeCriterion)criterion).getValues().get(0);
} else {
if (attrDescriptor.getName().equalsIgnoreCase("LocationId")) {
locId = (Integer)((AttributeCriterion)criterion).getValues().get(0);
}
}
if (attrDescriptor.getName().equalsIgnoreCase("ManagerId")) {
mgrId = (Integer)((AttributeCriterion)criterion).getValues().get(0);
}
}
if ((locId != null && locId < 0) || (mgrId != null && mgrId < 0)) {
FacesMessage msg = new FacesMessage("Id Value can not be negative");
msg.setSeverity(FacesMessage.SEVERITY_ERROR);
FacesContext.getCurrentInstance().addMessage(null, msg);
} else {
/**Process default query listener*/
invokeEL("#{bindings.DepartmentsViewCriteriaQuery.processQuery}", new Class[] { QueryEvent.class },
new Object[] { queryEvent });
}
}
---以上资料摘自网络上；
下面应用在收费系统的学生学杂费中的汇总
/**
* 重写queryListener,获取query的属性字段值,传入存储过程查询合计结果(应缴费用、已交金额、减免费用和欠费)
* 两种方法：1.通过绑定变量(getNamedWhereClauseParam); 2.使用QueryDescriptor and ConjunctionCriterion
* 注意:如果使用第二种方法是获取不到下拉值,所以下面结合这两种方法去获取query的输入框和下拉值(年级、校区、院系、专业、班级)
* @param queryEvent
* */
public void queryListener(QueryEvent queryEvent) {
JSFUtils.invokeMethodExpression("#{bindings.SfglXsxxVoCriteriaQuery.processQuery}",
Object.class, QueryEvent.class,
queryEvent); //默认执行自带的查询方法
ViewObject vo = ADFUtils.findIterator("SfglXsxxVo1Iterator").getViewObject();
//---使用Using QueryDescriptor and ConjunctionCriterion
QueryDescriptor qd = queryEvent.getDescriptor();
ConjunctionCriterion conCrit = qd.getConjunctionCriterion();
List<Criterion> criterionList = conCrit.getCriterionList(); //页面查询条件
String jflx = new String();
String jfsjStart = new String();
String jfsjEnd = new String();
String xh = new String();;
String xm = new String();;
String sfzh = new String();;
String njdm = new String();;
String xqdm = new String();;
String dwh = new String();;
String zydm = new String();;
String bjdm = new String();;
for (Criterion criterion : criterionList) {
AttributeDescriptor attrDescriptor =
((AttributeCriterion)criterion).getAttribute();
if (attrDescriptor.getName().contains("Jflx") && !((AttributeCriterion)criterion).getValues().get(0).equals("")) {
jflx = String.valueOf(((AttributeCriterion)criterion).getValues().get(0));
}
if (attrDescriptor.getName().contains("CreateTime")) {
if(((AttributeCriterion)criterion).getValues().get(0) != null){
jfsjStart = String.valueOf(((AttributeCriterion)criterion).getValues().get(0));
if(jfsjStart.length() >19){ //取消时间字符串秒后面的数字
jfsjStart = jfsjStart.substring(0, 19);
}
}
if(((AttributeCriterion)criterion).getValues().get(1) != null){
jfsjEnd = String.valueOf(((AttributeCriterion)criterion).getValues().get(1));
if(jfsjEnd.length() >19){ //取消时间字符串秒后面的数字
jfsjEnd = jfsjEnd.substring(0, 19);
}
}
}
if (attrDescriptor.getName().contains("Xh") && ((AttributeCriterion)criterion).getValues().get(0) != null) {
xh = (String)((AttributeCriterion)criterion).getValues().get(0);
}
if (attrDescriptor.getName().contains("Xm") && ((AttributeCriterion)criterion).getValues().get(0) != null) {
xm = (String)((AttributeCriterion)criterion).getValues().get(0);
}
if (attrDescriptor.getName().contains("Sfzh") && ((AttributeCriterion)criterion).getValues().get(0) != null) {
sfzh = (String)((AttributeCriterion)criterion).getValues().get(0);
}
if (attrDescriptor.getName().contains("Nj") && !((AttributeCriterion)criterion).getValues().get(0).equals("")) {
njdm = vo.getNamedWhereClauseParam("p_nj").toString();
}
if (attrDescriptor.getName().contains("Xqdm") && !((AttributeCriterion)criterion).getValues().get(0).equals("")) {
xqdm = vo.getNamedWhereClauseParam("p_xqdm").toString();
}
if (attrDescriptor.getName().contains("Yxdm") && !((AttributeCriterion)criterion).getValues().get(0).equals("")) {
dwh = vo.getNamedWhereClauseParam("p_dwh").toString();
}
if (attrDescriptor.getName().contains("Zydm") && !((AttributeCriterion)criterion).getValues().get(0).equals("")) {
zydm = vo.getNamedWhereClauseParam("p_zydm").toString();
}
if (attrDescriptor.getName().contains("Bjdm") && !((AttributeCriterion)criterion).getValues().get(0).equals("")) {
bjdm = vo.getNamedWhereClauseParam("p_bjdm").toString();
}
}
//System.out.println("jflx:"+jflx+",jfsjStart:"+jfsjStart+",jfsjEnd:"+jfsjEnd+",xh:"+xh+",xm:"+xm+",sfzh:"+sfzh+",njdm:"+njdm+",xqdm:"+xqdm+",dwh:"+dwh+",zydm:"+zydm+",bjdm:"+bjdm);
try {
SfxtglAMImpl am =
(SfxtglAMImpl)ADFUtils.findIterator("SfglXsxxVo1Iterator").getViewObject().getApplicationModule();
String stmt = "BEGIN SFGL_XSXX_QUERY(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); END;";
java.sql.CallableStatement st = null;
try {
st = am.getDBTransaction().createCallableStatement(stmt, 0);
st.setString(1, jflx);
st.setString(2, jfsjStart);
st.setString(3, jfsjEnd);
st.setString(4, xh);
st.setString(5, xm);
st.setString(6, sfzh);
st.setString(7, njdm);
st.setString(8, xqdm);
st.setString(9, dwh);
st.setString(10, zydm);
st.setString(11, bjdm);
st.registerOutParameter(12, Types.CHAR); //yjfy 应缴费用
st.registerOutParameter(13, Types.CHAR); //yjje已缴金额
st.registerOutParameter(14, Types.CHAR); //jmfy 减免费用
st.registerOutParameter(15, Types.CHAR); //qf 欠费
st.execute();
JSFUtils.setExpressionValue("#{bindings.Totalyjfy.inputValue}", st.getString(12)==null?"0":st.getString(12));
JSFUtils.setExpressionValue("#{bindings.Totalyjje.inputValue}",st.getString(13)==null?"0":st.getString(13));
JSFUtils.setExpressionValue("#{bindings.Totaljmfy.inputValue}", st.getString(14)==null?"0":st.getString(14));
JSFUtils.setExpressionValue("#{bindings.Totalqf.inputValue}", st.getString(15)==null?"0":st.getString(15));
JSFUtils.refresh(richTable);
} catch (java.sql.SQLException s) {
s.printStackTrace();
throw new oracle.jbo.JboException(s);
} finally {
try {
if (st != null) {
st.close();
}
} catch (java.sql.SQLException s2) {
throw new oracle.jbo.JboException(s2);
}
}
} catch (Exception e) {
log.severe(e);
}
}
create or replace procedure SFGL_XSXX_QUERY(p_jflx in varchar2,
p_jfsjStart in varchar2,
p_jfsjEnd in varchar2,
p_xh in varchar2,
p_xm in varchar2,
p_sfzh in varchar2,
p_njdm in varchar2,
p_xqdm in varchar2,
p_dwh in varchar2,
p_zydm in varchar2,
p_bjdm in varchar2,
o_totalYjfy out varchar2,
o_totalYjje out varchar2,
o_totalJmfy out varchar2,
o_totalQf out varchar2) is
/**
* 自定义学杂费查询
* 功能：传入条件参数查询
* 2015年12月4日
* p_jflx 缴费类型
* p_jfsjStart 缴费开始时间
* p_jfsjEnd 缴费结束时间
* p_xh 学号
* p_xm 姓名
* p_sfzh 身份证
* p_njdm 年级
* p_xqdm 校区
* p_dwh 院系
* p_zydm 专业代码
* p_bjdm 班级代码
* o_totalYjfy 合计应缴费用
* o_totalYjje 合计已缴金额
* o_totalJmfy 合计减免费用
* o_totalQf 合计欠费
*/
v_sql STRING(3000); --存放查询语句
v_dqxn varchar2(20); --当前学年
xsxx_rec SFGL_SUMTEMP%rowtype; --创建与SFGL_SUMTEMP相同类型的临时集合
begin
SELECT xndm into v_dqxn FROM sfgl_xn WHERE sfdqxn=1; --当前学年
--根据查询条件初始化的游标语句
v_sql := 'SELECT
''1'' zj,
trim(DECODE(sum(yjfy),0,''0.00'',TO_CHAR(sum(yjfy),''9,999,999,999.99''))) yjfy,
trim(DECODE(sum(yjje),0,''0.00'',TO_CHAR(sum(yjje),''9,999,999,999.99''))) yjje,
trim(DECODE(sum(qf),0,''0.00'',TO_CHAR(sum(qf),''9,999,999,999.99''))) qf,
trim(DECODE(sum(jmfy),0,''0.00'',TO_CHAR(sum(jmfy),''9,999,999,999.99''))) jmfy FROM (
SELECT sfglxsxx.xm,
sfglxsxx.nj,
(nvl(SUM(decode(a.fylx, 1, a.fy)), 0) +
nvl(SUM(decode(a.fylx, 2, a.fy)), 0) +
nvl(SUM(decode(a.fylx, 3, a.fy)), 0) +
nvl(SUM(decode(a.fylx, 4, a.fy)), 0)) yjfy, --应缴费用=学费+住宿费+医保费+其他费用
nvl(SUM(a.yjfy), 0) yjje, --已缴费用
(nvl(SUM(decode(a.fylx, 1, a.fy)), 0) +
nvl(SUM(decode(a.fylx, 2, a.fy)), 0) +
nvl(SUM(decode(a.fylx, 3, a.fy)), 0) +
nvl(SUM(decode(a.fylx, 4, a.fy)), 0) - nvl(SUM(a.yjfy), 0)) qf, --欠费=应缴费用-已缴费用
nvl(SUM(jl.je), 0) jmfy, --减免费用
a.xn
FROM sfgl_xsxx sfglxsxx,
sfgl_yjfy a,
(SELECT sum(t.je) je, t.yjfyid, t.xn
FROM sfgl_jfjl t
WHERE t.jflx = ''3''
group by t.yjfyid, t.xn) jl
WHERE sfglxsxx.xszt = ''01''
AND sfglxsxx.xsid = a.yjyh(+)
AND a.xn(+) = '''||v_dqxn||'''
AND a.xn = jl.xn(+)
AND a.yjfyid = jl.yjfyid(+)
';
if nvl(p_xh, 'N') <> 'N' then
v_sql := v_sql || ' and sfglxsxx.xh = ''' || p_xh || '''';
end if;
if nvl(p_xm, 'N') <> 'N' then
v_sql := v_sql || ' and sfglxsxx.xm = ''' || p_xm || '''';
end if;
if nvl(p_sfzh, 'N') <> 'N' then
v_sql := v_sql || ' and sfglxsxx.sfzh = ''' || p_sfzh || '''';
end if;
if nvl(p_njdm, 'N') <> 'N' then
v_sql := v_sql || ' and sfglxsxx.nj = ''' || p_njdm || '''';
end if;
if nvl(p_xqdm, 'N') <> 'N' then
v_sql := v_sql || ' and sfglxsxx.xqdm = ''' || p_xqdm || '''';
end if;
if nvl(p_dwh, 'N') <> 'N' then
v_sql := v_sql || ' and sfglxsxx.yxdm = ''' || p_dwh || '''';
end if;
if nvl(p_zydm, 'N') <> 'N' then
v_sql := v_sql || ' and sfglxsxx.zydm = ''' || p_zydm || '''';
end if;
if nvl(p_bjdm, 'N') <> 'N' then
v_sql := v_sql || ' and sfglxsxx.bjdm = ''' || p_bjdm || '''';
end if;
if nvl(p_jflx, 'N') <> 'N' or nvl(p_jfsjStart, 'N') <> 'N' or nvl(p_jfsjEnd, 'N') <> 'N' then
v_sql := v_sql || ' and exists(
SELECT 1 FROM sfgl_yjfy syjfy,sfgl_jfjl sjfjl WHERE syjfy.yjfyid = sjfjl.yjfyid
and syjfy.yjyh = sfglxsxx.xsid ';
if nvl(p_jflx, 'N') <> 'N' then
v_sql := v_sql || ' and sjfjl.jflx = ''' || p_jflx || '''';
end if;
if nvl(p_jfsjStart, 'N') <> 'N' then
v_sql := v_sql || ' and sjfjl.create_time >=
to_date('''||p_jfsjStart||''',''yyyy-MM-dd HH24:mi:ss'')';
end if;
if nvl(p_jfsjEnd, 'N') <> 'N' then
v_sql := v_sql || ' and sjfjl.create_time <=
to_date('''||p_jfsjEnd||''',''yyyy-MM-dd HH24:mi:ss'')';
end if;
v_sql := v_sql || ' )';
end if;
v_sql :=v_sql||' GROUP BY sfglxsxx.xm,
sfglxsxx.nj,
a.xn
ORDER BY sfglxsxx.nj desc
)';
/* dbms_output.put_line('查询语句：'||v_sql);*/
execute immediate v_sql into xsxx_rec;
o_totalYjfy:=xsxx_rec.yjfy;
o_totalYjje:=xsxx_rec.yjje;
o_totalJmfy:=xsxx_rec.jmfy;
o_totalQf:=xsxx_rec.qf;
/*dbms_output.put_line('yjfy:'||o_totalYjfy||',yjje:'||o_totalYjje||',jmfy:'||o_totalJmfy||',qf:'||o_totalQf);
*/
exception
when others then
dbms_output.put_line('程序运行出现内部错误，请联系管理员。'||dbms_utility.format_error_backtrace()||'---'||SQLCODE||'---'||SQLERRM);
raise;
end SFGL_XSXX_QUERY;
扫一扫
3089
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
