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
<link href="${ctx}/Content/jquery.bsgrid-1.37/merged/bsgrid.all.min.css" rel="stylesheet" />
<!-- @*CSS皮肤(需引用于bsgrid.all.min.css之后)*@ -->
<link href="${ctx}/Content/jquery.bsgrid-1.37/css/skins/grid_blue.min.css" rel="stylesheet" />
<script type="text/javascript" src="${ctx}/js/jquery-1.12.4/jquery-1.12.4.min.js"></script>
<script type="text/javascript" src="${ctx}/js/jquery.bsgrid-1.37/js/lang/grid.zh-CN.min.js"></script>
<script type="text/javascript" src="${ctx}/js/jquery.bsgrid-1.37/merged/grid.all.min.js"></script>
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
}
结果: