queststring.java

企业进销存管理系统主要包括：基础信息管理、库存管理、商品销售、查询统计、往来管理和系统设置六大管理模块。

package com.mingri.info;

import com.mingri.dbconn.DBResult;
import java.sql.*;
import java.io.*;

public class QuestString
{
  private int curPage;//当前页数
  private int maxPage;//最大页数
  private int maxRowCount;//总记录数(即从数据库中查出的所有记录)
  private int pageSize=2;//每页要显示的记录数
  private DBResult db;//记录集对象
  private String httpFile;//当前地址栏的文件，即具体的JSP文件
  private String cif;//选择的查询字段
  private String ccif;//选择的查询条件运算符
  private String qvalue;//查询关键字
  private String countSql=null;//用来存储select count (*) ……语句
  private String topSql=null;//用来存储 select top 2……语句
  private String nowPage=null;//初始化当前页curPage变量,即获当前页的具体页号
  private String str_parameter;//在做翻页的时传递的除pages以外的其他参数
  private String andor;//查询的与/或条件
  private String sdate;// 查询起始时间,即从什么日期开始查询
  private String edate;// 查询结束时间,即到什么日期结束
  private String orderby;//排序条件,即按什么排序
  private String paixu;//排序方法,即升序或降序

  public QuestString(){
    db=new DBResult();
  }
  public int getCurPage() {
    return curPage;
  }
  public void setCurPage(int curPage) {
    this.curPage = curPage;
  }
  public int getMaxPage() {
    return maxPage;
  }
  public void setMaxPage(int maxPage) {
    this.maxPage = maxPage;
  }
  public int getMaxRowCount() {
    return maxRowCount;
  }
  public void setMaxRowCount(int maxRowCount) {
    this.maxRowCount = maxRowCount;
  }
  public int getPageSize() {
    return pageSize;
  }
  public void setPageSize(int pageSize) {
    this.pageSize = pageSize;
  }
  public String getHttpFile() {
    return httpFile;
  }
  public void setHttpFile(String httpFile) {
    this.httpFile = httpFile;
  }
  public String getCcif() {
    return ccif;
  }
  public void setCcif(String ccif) {
    this.ccif = ccif;
  }
  public String getCif() {
    return cif;
  }
  public void setCif(String cif) {
    this.cif = cif;
  }
  public String getQValue() {
    return qvalue;
  }
  public void setQValue(String qValue) {
    this.qvalue = qValue;
  }
  public String getStr_parameter() {
  return str_parameter;
  }
  public void setStr_parameter(String str_parameter) {
     this.str_parameter = str_parameter;
  }
  public String getAndor() {
    return andor;
  }
  public void setAndor(String andor) {
    this.andor = andor;
  }
  public String getSdate() {
    return sdate;
  }
  public void setSdate(String sdate) {
    this.sdate = sdate;
  }
  public String getEdate() {
    return edate;
  }
  public void setEdate(String edate) {
    this.edate = edate;
  }
  public String getOrderby() {
    return orderby;
  }
  public void setOrderby(String orderby) {
    this.orderby = orderby;
  }
  public String getPaixu() {
    return paixu;
  }
  public void setPaixu(String paixu) {
    this.paixu = paixu;
  }

  /**
   * 通过参数传递来设置Bean中的属性
   */

  public void setQuerySql(String httpfile,String pages,String strCount){
    this.nowPage=pages;//将表单提交过来的参数pages(第几页)的值传递给变量nowPage保存
    this.httpFile=httpfile;//用来动态设置处理表单请求的JSP文件
    this.countSql=strCount;//用来设置执行select count(*)……的SQL语句
    try{
      querySql(countSql);//调用querySql方法，并将参数countSql传递到该方法中
    }catch(Exception e){
      e.printStackTrace();
    }
  }
  /**
   * 执行SQL语句，获得分页显示时的各个属性
   */
  public void querySql(String countSql) throws SQLException{
    //在setQuerySql方法中已经设置了nowPage的值,其值就是pages
    if(this.nowPage==null){//如果nowPage的值为空，也就是传递pages的值为空
      this.curPage=1;//那么就将当前页的值设为1；
    }
    else{
      this.curPage=Integer.parseInt(this.nowPage);
      if(this.curPage<1){//如果当前页小于1，那么就将当前页的值设为1
        this.curPage = 1;
      }
    }
    ResultSet rsCount=db.getResult(countSql);//执行SQL语句获得记录总数
    if(rsCount.next()){
      this.maxRowCount=rsCount.getInt(1);//获取记录总数，即所要查询记录的总行数
    }
    //获取总页数，即如果当总记录数除以每页显示的记录的余数为0时，总页数就等于两数整除的结果
    //如果余数不等于0，那么总页数就等于两数整除的结果加1
    this.maxPage=(this.maxRowCount%this.pageSize==0)?(this.maxRowCount/this.pageSize):(this.maxRowCount/this.pageSize+1);
    if(this.curPage>this.maxPage){//如果当前页大于总页数，则当前页等于总页数
      this.curPage=this.maxPage;//那么当前页就是最大页
    }
    rsCount.close();//关闭总记录数的结集
  }
  /**
   * 一个字符串，用来动态的给出一个表单
   * 该表单用来进行分页和统统页面间的跳转
   */
  public String pageFooter()
  {
    //创建一个表单
    String str = "<form action=" + this.httpFile + " name=formBean methord=post>";
    int prev = this.curPage- 1;//向前翻页，即当前页减1
    int next = this.curPage +1;//向后翻页，即当前页加1

    str = str + "<font style='font-size: 10pt'>总计<font color='red'>" + this.getMaxRowCount() +
                 "</font>条记录," + "【共<font  color='red'>" + this.getMaxPage()+ "</font>页】";
    str = str + "【 <font color='red'>" + this.pageSize + "</font>条/页】 当前第<font color='red'>" +
                 this.getCurPage() + "</font>页 &nbsp; ";
    if(this.curPage > 1)
      str = str + " <A href=" + this.httpFile + "?pages=1" + str_parameter + ">首页</A> ";
    else
      str = str + " 首页 ";
    if(this.curPage > 1)
      str = str + " <A href=" + this.httpFile + "?pages=" + prev + str_parameter + ">上一页</A> ";
    else
      str = str + " 上一页 ";
    if(this.curPage < this.maxPage)
      str = str + " <A href=" + this.httpFile + "?pages=" + next + str_parameter + ">下一页</A> ";
    else
      str = str + " 下一页 ";
    if(this.maxPage > 1 && this.curPage!= this.maxPage)
      str = str + " <A href=" + this.httpFile + "?pages=" + this.maxPage + str_parameter + ">尾页</A>";
    else
      str = str + " 尾页</font>";
    //在页面跳转间设置隐藏表单，来保存不同的请求
    str = str + "转到<input type ='text' name='pages' size='2'>页"+
                    "<input type='hidden' name='ccif' value='"+this.ccif+
                    "'><input type ='hidden' name='cif' value='"+this.cif+
                    "'><input type ='hidden' name='qvalue' value='"+this.qvalue+
                    "'><input type ='hidden' name='andor' value='"+this.andor+
                    "'><input type ='hidden' name='sdate' value='"+this.sdate+
                    "'><input type ='hidden' name='edate' value='"+this.edate+
                    "'><input type ='hidden' name='orderby' value='"+this.orderby+
                    "'><input type ='hidden' name='paixu' value='"+this.paixu+
                    "'><input type='submit' name='sumbmit' value='go'></form>";
    return str;
  }
  /**
   * 根据不同条件来获取不同的查询前N条的SQL语句
   */
  public String getString(String table){
  if(ccif.equals("="))
  {
   String strSql="select top "+this.pageSize*this.curPage+" * from "+table+
                 " where"+" "+cif+"="+"'"+qvalue+"'";
   return strSql;
  }
  else if(ccif.equals("LIKE"))
  {
   String strSql="select top "+this.pageSize*this.curPage+" * from "+table+
                 " where"+" "+cif+" "+"like"+" "+"'%"+qvalue+"%'";
   return strSql;
  }
  else if(ccif.equals("ALL"))
  {
   String strSql="select top "+this.pageSize*this.curPage+" * from "+table;
   return strSql;
  }
  else if(ccif.equals("<"))
  {
   String strSql="select top "+this.pageSize*this.curPage+" * from "+table+
                 " where "+cif+" < '"+qvalue+"'";
   return strSql;
  }
  return null;
 }
 /**
  * 根据不同条件来获得不同的计算记录总数的SQL语句
  */
 public String getCount(String table){
  if(ccif.equals("="))
  {
   String strSql="select count(*) from "+table+" where"+" "+cif+"="+"'"+qvalue+"'";
   return strSql;
  }
  else if(ccif.equals("LIKE"))
  {
   String strSql="select count(*) from "+table+" where"+" "+cif+" "+"like"+" "+"'%"+qvalue+"%'";
   return strSql;
  }
  else if(ccif.equals("ALL"))
  {
   String strSql="select count(*) from "+table;
   return strSql;
  }
  else if(ccif.equals("<"))
  {
   String strSql="select count(*) from "+table+" where "+cif+" < '"+qvalue+"'";
   return strSql;
  }
  return null;
 }
 public String getXSCount(String table){
  if(ccif.equals("="))
  {
   String strSql="select count(*) from "+table+" where"+" "+cif+"="+"'"+qvalue+"' and whether='否'";
   return strSql;
  }
  else if(ccif.equals("LIKE"))
  {
   String strSql="select count(*) from "+table+" where"+" "+cif+" "+"like"+" "+"'%"+qvalue+"%' and whether='否'";
   return strSql;
  }
  return null;
 }
 public String getXSString(String table){
 if(ccif.equals("="))
 {
  String strSql="select top "+this.pageSize*this.curPage+" * from "+table+
                " where"+" "+cif+"="+"'"+qvalue+"' and whether='否'";
  return strSql;
 }
 else if(ccif.equals("LIKE"))
 {
  String strSql="select top "+this.pageSize*this.curPage+" * from "+table+
                " where"+" "+cif+" "+"like"+" "+"'%"+qvalue+"%'and whether='否'";
  return strSql;
 }
 return null;
}

 /**
  * 根据不同条件和不同的启起始日期和结束日期来获得不同的计算
  * 记录总数的SQL语句
  */
 public String getDateCount(String table,String date){
   if(ccif.equals("="))//在sdate到edate时间段中进行准确值查询
   {
    String strSql="select count(*) from "+table+" where "+cif+" = '"+qvalue+"'"+
              andor+" "+date+" between '"+sdate+"' and '"+edate+"'";
    return strSql;
   }
   else if(ccif.equals("LIKE"))//在sdate到edate时间段中进行模糊查询
   {
    String strSql="select count(*) from "+table+" where "+cif+" like '%"+qvalue+"%'"+
              andor+" "+date+" between '"+sdate+"' and '"+edate+"'";
    return strSql;
   }
   else if(ccif.equals("ALL"))//取出表中所有记录
   {
    String strSql="select count(*) from "+table;
    return strSql;
   }
   return null;
 }
 /**
  * 根据不同条件和不同的启起始日期和结束日期来获得不同的查询
  * 前N条的SQL语句
  */
 public String getDateString(String table,String date){
  if(ccif.equals("="))
  {
    String strSql="select top "+this.pageSize*this.curPage+" * from "+table+
                  " where "+cif+" = '"+qvalue+"'"+andor+
                   " "+date + " between '"+sdate+"' and '"+edate+"'";
    return strSql;
  }
  else if(ccif.equals("LIKE"))
  {
    String strSql="select top "+this.pageSize*this.curPage+" * from "+table+
                  " where "+cif+" like '%"+qvalue+"%'"+andor+
                  " "+date +" between '"+sdate+"' and '"+edate+"'";
    return strSql;
  }
  else if(ccif.equals("ALL"))
  {
   String strSql="select top "+this.pageSize*this.curPage+" * from "+table;
   return strSql;
  }
  return null;
 }
 /**
  * 子查询中得到从起始日期到结束日期这段时间所有不重复的spid(商品id)
  * 并返回不重复的spid的总数
  */
 public String getOrderCount(String table,String date){
   String strSql="select count(*) from (select spid from "+table+
              " where "+ date+" between '"+sdate+"' and '"+edate+
              "' group by spid) as aa";
   return strSql;
 }
 /**
  * 联合查询
  * 查询出某一表中从起始到结束日期间所有不重复的spid(商品id)
  * 并且将所有相同spid下的sl(数量)和je(金额)求和,即计算出某一商品在
  * 某一时间段内的总销售数量和销售总额
  * 最后通过联合查询在tb_brand表(商品信息表)中将该spid所对应商品的
  * 其他一些信息查询出来
  */
 public String getOrderString(String table,String date){
   String strSql="select top "+ this.pageSize*this.curPage+
       "* from tb_brand a inner join (select spid,sum(sl) as sl,sum(je) as je"+
       " from "+table+" where "+date+" between '"+sdate+"' and '"+edate+"' group by spid ) as b"+
       " on a.id=b.spid order by "+orderby+" "+paixu;
   return strSql;
 }
}