在Java -MySql中处理多个过滤器

Question

有三个过滤器Class、Section和Date。现在有七种可能性：

1-用户输入类

2-)用户输入部分

3)用户输入日期

4-)用户输入日期和类别

以此类推。

我已经在java中编写了以下代码来处理这些可能性：

if( mySection.equals("0") && myClass.equals("0") && ( FDate.trim().compareTo("")==0 || TDate.trim().compareTo("")==0    ) )
        {
        out.println("Note: Please provide valid filteration attributes. Atleast one filter is required.");
        return;
        }



        if( !mySection.equals("0") && myClass.equals("0") && ( FDate.trim().compareTo("")==0 || TDate.trim().compareTo("")==0    ) )
        {
            Query = "select st.rollno, concat(st.firstname,' ',st.midname,' ',st.lastname), st.level, st.section, st.rfidtag, sc.schoolname, sa.time, date_format(sa.time,'%T') from AAV.studentattendence sa, AAV.Students st, AAV.Schools sc where sa.studentindexptr=st.indexptr and sc.indexptr=st.schoolindexptr and st.section='"+mySection+"'";
        }

        if( !mySection.equals("0") && !myClass.equals("0") && ( FDate.trim().compareTo("")==0 || TDate.trim().compareTo("")==0    ) )
        {
            Query = "select st.rollno, concat(st.firstname,' ',st.midname,' ',st.lastname), st.level, st.section, st.rfidtag, sc.schoolname, sa.time, date_format(sa.time,'%T') from AAV.studentattendence sa, AAV.Students st, AAV.Schools sc where sa.studentindexptr=st.indexptr and sc.indexptr=st.schoolindexptr and st.section='"+mySection+"' and st.level='"+myClass+"' ";
        }


        if( mySection.equals("0") && !myClass.equals("0") && ( FDate.trim().compareTo("")==0 || TDate.trim().compareTo("")==0    ) )
        {
            Query = "select st.rollno, concat(st.firstname,' ',st.midname,' ',st.lastname), st.level, st.section, st.rfidtag, sc.schoolname, sa.time, date_format(sa.time,'%T') from AAV.studentattendence sa, AAV.Students st, AAV.Schools sc where sa.studentindexptr=st.indexptr and sc.indexptr=st.schoolindexptr and st.level='"+myClass+"' ";
        }


        if( mySection.equals("0") && myClass.equals("0") && ( !(FDate.trim().compareTo("")==0) && !(TDate.trim().compareTo("")==0)    ) )
        {
            Query = "select st.rollno, concat(st.firstname,' ',st.midname,' ',st.lastname), st.level, st.section, st.rfidtag, sc.schoolname, sa.time, date_format(sa.time,'%T') from AAV.studentattendence sa, AAV.Students st, AAV.Schools sc where sa.studentindexptr=st.indexptr and sc.indexptr=st.schoolindexptr and date_format(time,'%Y-%m-%d %T') between '"+FromDate+"'  and '"+ToDate+"'";
        }


        if( mySection.equals("0") && !myClass.equals("0") && ( !(FDate.trim().compareTo("")==0) && !(TDate.trim().compareTo("")==0)    ) )
        {
            Query = "select st.rollno, concat(st.firstname,' ',st.midname,' ',st.lastname), st.level, st.section, st.rfidtag, sc.schoolname, sa.time, date_format(sa.time,'%T') from AAV.studentattendence sa, AAV.Students st, AAV.Schools sc where sa.studentindexptr=st.indexptr and sc.indexptr=st.schoolindexptr and date_format(time,'%Y-%m-%d %T') between '"+FromDate+"'  and '"+ToDate+"' and st.level='"+myClass+"'";
        }


        if( !mySection.equals("0") && myClass.equals("0") && ( !(FDate.trim().compareTo("")==0) && !(TDate.trim().compareTo("")==0)    ) )
        {
            Query = "select st.rollno, concat(st.firstname,' ',st.midname,' ',st.lastname), st.level, st.section, st.rfidtag, sc.schoolname, sa.time, date_format(sa.time,'%T') from AAV.studentattendence sa, AAV.Students st, AAV.Schools sc where sa.studentindexptr=st.indexptr and sc.indexptr=st.schoolindexptr and date_format(time,'%Y-%m-%d %T') between '"+FromDate+"'  and '"+ToDate+"' st.section='"+mySection+"'";
        }


        if( !mySection.equals("0") && !myClass.equals("0") && ( !(FDate.trim().compareTo("")==0) && !(TDate.trim().compareTo("")==0)    ) )
        {
            Query = "select st.rollno, concat(st.firstname,' ',st.midname,' ',st.lastname), st.level, st.section, st.rfidtag, sc.schoolname, sa.time, date_format(sa.time,'%T') from AAV.studentattendence sa, AAV.Students st, AAV.Schools sc where sa.studentindexptr=st.indexptr and sc.indexptr=st.schoolindexptr and date_format(time,'%Y-%m-%d %T') between '"+FromDate+"'  and '"+ToDate+"' and st.section='"+mySection+"' and st.level='"+myClass+"'";
        }

问题是，当我添加另一个过滤器时，这些可能性会急剧增加，因此会有更多的查询和处理。为了通过JAVA或MYSQL有效地处理这个问题，必须有一种方法。

这种情况下的通用sql语句？

JAVA中处理多个输入查询的库？

或者别的什么..。

怎样才能用尽可能少的代码来处理这种情况(考虑到过滤器可能会增加)？

Answer 1

解决了，在考虑了一段时间后，我得到了一个解决方案。下面是解决这个问题的通用单个查询：

 if( mySection.equals("0") && myClass.equals("0") && ( FDate.trim().compareTo("")==0 || TDate.trim().compareTo("")==0    ) )
        {
        out.println("Note: Please provide valid filteration attributes. Atleast one filter is required.");
        return;
        }


        Query = "select st.rollno, concat(st.firstname,' ',st.midname,' ',st.lastname), st.level, st.section, st.rfidtag, sc.schoolname, sa.time, date_format(sa.time,'%T') from AAV.studentattendence sa, AAV.Students st, AAV.Schools sc where sa.studentindexptr=st.indexptr and sc.indexptr=st.schoolindexptr and ( '0'='"+mySection+"' or st.section='"+mySection+"') and ('0'='"+myClass+"' or st.level='"+myClass+"') and (  (' 00:00:00'='"+FromDate+"' and ' 23:59:59'='"+ToDate+"')  or (date_format(time,'%Y-%m-%d %T') between '"+FromDate+"'  and '"+ToDate+"' )  )";


executeQuery(Query);

当被当做一个表达式来处理时，它在缺省值上解析为true，也就是我的例子中的'0‘，查询就变成了泛型，我们可以根据需要添加任意数量的筛选子句。