作者:邹爱红
撰写时间:2019年06月04日
dao.impl实现类的方法:
private String findAllPage= "select p.SetBudgetName,f.BudgetTypeName,un.UnitEngineeringName,de.DepartmentNamefrom B_SetBudget p left join S_BudgetType f on p.BudgetTypeID=f.BudgetTypeID" +
"leftjoin SYS_UnitEngineering un on p.UnitEngineeringID=un.UnitEngineeringID " +
"leftjoin S_Department de on p.DepartmentID=de.DepartmentID " ;
多条件查询只是在多表查询的基础上加上了几个条件。
@Override
publicList<B_SetBudgetPo> selectAll(int startIndex, int pageSize,StringsetBudgetName, int budgetTypeID, int unitEngineeringID, int departmentID) {
List<B_SetBudgetPo> list = newArrayList<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;
}
servlet的方法:
// 分页
publicvoid bsGridList(HttpServletRequestrequest,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)) {
currentPage =Integer.parseInt(currentPageStr);
}
if (pageSizeStr != null &&Tools.isNum(pageSizeStr)) {
pageSize =Integer.parseInt(pageSizeStr);
}
int startIndex = (currentPage - 1) *pageSize;
B_SetBudgetService userService = newB_SetBudgetServiceImpl();
List<B_SetBudgetPo> listUser =userService.selectAll(startIndex,pageSize,setBudgetName,budgetTypeName,unitEngineeringName, departmentName); //跟方法里面的参数类型要对应
int totalRow =userService.getTotalRow();//getTotalRow这个方法是记录总条数的
Bsgrid<B_SetBudgetPo> bsgrid = newBsgrid<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
<linkhref="${ctx}/Content/jquery.bsgrid-1.37/merged/bsgrid.all.min.css"rel="stylesheet" />
<!-- @*CSS皮肤(需引用于bsgrid.all.min.css之后)*@ -->
<linkhref="${ctx}/Content/jquery.bsgrid-1.37/css/skins/grid_blue.min.css"rel="stylesheet" />
<scripttype="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
}
结果:
撰写时间:2019年06月04日
dao.impl实现类的方法:
private String findAllPage= "select p.SetBudgetName,f.BudgetTypeName,un.UnitEngineeringName,de.DepartmentNamefrom B_SetBudget p left join S_BudgetType f on p.BudgetTypeID=f.BudgetTypeID" +
"leftjoin SYS_UnitEngineering un on p.UnitEngineeringID=un.UnitEngineeringID " +
"leftjoin S_Department de on p.DepartmentID=de.DepartmentID " ;
多条件查询只是在多表查询的基础上加上了几个条件。
@Override
publicList<B_SetBudgetPo> selectAll(int startIndex, int pageSize,StringsetBudgetName, int budgetTypeID, int unitEngineeringID, int departmentID) {
List<B_SetBudgetPo> list = newArrayList<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;
}
servlet的方法:
// 分页
publicvoid bsGridList(HttpServletRequestrequest,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)) {
currentPage =Integer.parseInt(currentPageStr);
}
if (pageSizeStr != null &&Tools.isNum(pageSizeStr)) {
pageSize =Integer.parseInt(pageSizeStr);
}
int startIndex = (currentPage - 1) *pageSize;
B_SetBudgetService userService = newB_SetBudgetServiceImpl();
List<B_SetBudgetPo> listUser =userService.selectAll(startIndex,pageSize,setBudgetName,budgetTypeName,unitEngineeringName, departmentName); //跟方法里面的参数类型要对应
int totalRow =userService.getTotalRow();//getTotalRow这个方法是记录总条数的
Bsgrid<B_SetBudgetPo> bsgrid = newBsgrid<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
<linkhref="${ctx}/Content/jquery.bsgrid-1.37/merged/bsgrid.all.min.css"rel="stylesheet" />
<!-- @*CSS皮肤(需引用于bsgrid.all.min.css之后)*@ -->
<linkhref="${ctx}/Content/jquery.bsgrid-1.37/css/skins/grid_blue.min.css"rel="stylesheet" />
<scripttype="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
}
结果: