多条件查询

最新推荐文章于 2024-06-18 17:28:10 发布

芷琪 i

最新推荐文章于 2024-06-18 17:28:10 发布

阅读量592

点赞数

本文链接：https://blog.csdn.net/weixin_44600235/article/details/90748331

版权

dao.impl实现类的方法：

private String findAllPage = "select p.SetBudgetName,f.BudgetTypeName,un.UnitEngineeringName,de.DepartmentName from B_SetBudget p left join S_BudgetType f on p.BudgetTypeID=f.BudgetTypeID " +

"left join SYS_UnitEngineering un on p.UnitEngineeringID=un.UnitEngineeringID " +

"left join S_Department de on p.DepartmentID=de.DepartmentID " ;

只是在多表查询的基础上加上了几个条件。

因为用的是插件，所以是在插件的方法里面加上要查询的多条件参数。

@Override

public List<B_SetBudgetPo> selectAll(int startIndex, int pageSize,String setBudgetName, int budgetTypeID, int unitEngineeringID, int departmentID) {

List<B_SetBudgetPo> list = new ArrayList<B_SetBudgetPo>();

try {

String str="where ";

con = DBUtil.getConnection();

if (budgetTypeID>0) { //大于0的话就去数据库查询下拉框ID加上上面的参数

str+=" p.BudgetTypeID="+budgetTypeID+" and";

}

if (unitEngineeringID>0) { //传0过来的话就查询所有

str+=" p.UnitEngineeringID="+unitEngineeringID+" and";

}

if (departmentID>0) {

str+=" p.DepartmentID="+departmentID+" and";

}

str+=" p.SetBudgetName like '%"+setBudgetName+"%' ";//模糊查询

str+="limit ?,?";// limit分页的关键字

findAllPage=findAllPage+str; //拼接sql语句

ps = con.prepareStatement(findAllPage);

ps.setInt(1, startIndex);

ps.setInt(2, pageSize);

rs = ps.executeQuery();

list=JdbcHelper.getResult(rs, B_SetBudgetPo.class); // JdbcHelper方法里面用反射机制去获取有关 ResultSet结果集中列的名称和类型的信息。和obj java类的class

} catch (SQLException e) {

e.printStackTrace();

} finally {

DBUtil.close(con, ps, rs);

}

return list;

}

private String getTotalRow="SELECT COUNT(*) FROM B_SetBudget";

@Override
   public int getTotalRow() {//获取总行数的方法
       int intTotalRow=0;
       try {
           con=DBUtil.getConnection();
           ps=con.prepareStatement(getTotalRow);
           rs=ps.executeQuery();
           while (rs.next()) {
               intTotalRow=rs.getInt(1);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       }
       finally{
           DBUtil.close(con, ps, rs);
       }
       return intTotalRow;
   }

servlet的方法：

// 分页

public void bsGridList(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {

response.setContentType("text/html;charset=utf-8");//servlet传中文到页面乱码（已解决）

String currentPageStr = request.getParameter("curPage");

String pageSizeStr = request.getParameter("pageSize");

int currentPage = 1;

int pageSize = 10;

String setBudgetName = request.getParameter("yusuanNAME");//获取name值

String yusuanType = request.getParameter("yusuanType");

if (setBudgetName==null) {//预算名称要用模糊查询，因为不是下拉框

setBudgetName="";

}else{

setBudgetName=setBudgetName.trim();

}

String DanWeiUnit = request.getParameter("DanWeiUnit");

String BuMenName = request.getParameter("BuMenName");

int budgetTypeName=0;//申明三个int等于0，如果页面没有数据过来的话就让它等于0，如果有数据传过来，就等于页面的数据

int unitEngineeringName=0;

int departmentName=0;

if (yusuanType!=null) {//如果不等于空，就把int的值变成穿过来的值，这样子就可以让它一进来的时候就加载数据

budgetTypeName=Integer.valueOf(yusuanType.trim());//因为三个下拉框是int类型的所以用Integer.valueOf(）来转

}

if (DanWeiUnit!=null) {

unitEngineeringName=Integer.valueOf(DanWeiUnit.trim());

}

if (BuMenName!=null) {

departmentName=Integer.valueOf(BuMenName.trim());

}

if (currentPageStr != null && Tools.isNum(currentPageStr)) {//Tools这个封装的方法里面判断字符串，数字是否为空

currentPage = Integer.parseInt(currentPageStr);

}

if (pageSizeStr != null && Tools.isNum(pageSizeStr)) {

pageSize = Integer.parseInt(pageSizeStr);

}

int startIndex = (currentPage - 1) * pageSize;

B_SetBudgetService userService = new B_SetBudgetServiceImpl();

List<B_SetBudgetPo> listUser = userService.selectAll(startIndex,pageSize,setBudgetName,budgetTypeName, unitEngineeringName, departmentName); //跟方法里面的参数类型要对应

int totalRow = userService.getTotalRow();//getTotalRow这个方法是记录总条数的

Bsgrid<B_SetBudgetPo> bsgrid = new Bsgrid<B_SetBudgetPo>( );

bsgrid.setSuccess(true);

bsgrid.setCurPage(currentPage);

bsgrid.setTotalRows(totalRow);

bsgrid.setData(listUser);

JSONObject jsonObject = JSONObject.fromObject(bsgrid);//转json

PrintWriter out = response.getWriter();//json的输出

out.write(jsonObject.toString());

out.flush();

out.close();

}

jsp的方法：

插件用的是：jquery.bsgrid-1.37

var tableSkinDetails;

jQuery(document).ready(function($) {

//初始化 bbsgrid tableSkinDetails

tableSkinDetails = $.fn.bsgrid.init('tabyusuan', {

url: "${ctx}/servlet/YuSuanSheZhiServlet?fun=bsGridList",

autoLoad: true,

stripeRows: true,//隔行变色

rowHoverColor: true,//划过行变色

pageSize: 10,

pageAll: false,

pageSizeSelect: true,//是否选择分页页数下拉框

pagingLittleToolbar: true,//精简的图标按钮分页工具条

pagingToolbarAlign: "left",//分页工具条的显示位置

displayBlankRows: false,//不显示空白行

});

function searchStudent(){//搜索按钮的点击事件

var YuSuanBiao=$("#YuSuanBiao").val();//获取那四个参数的ID

var YSLX=$("#YSLX").val();

var DWGC=$("#DWGC").val();

var BMEN=$("#BMEN").val();

//通过search来条件查询

tableSkinDetails.search({ yusuanNAME: YuSuanBiao, yusuanType: YSLX, DanWeiUnit: DWGC, BuMenName: BMEN });//用name值的参数：上面获取到的四个id

}

结果：

芷琪 i

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
多条件查询

dao.impl实现类的方法：private String findAllPage = "select p.SetBudgetName,f.BudgetTypeName,un.UnitEngineeringName,de.DepartmentName from B_SetBudget p left join S_BudgetType f on p.BudgetTypeID=f.BudgetTy...
复制链接

扫一扫