动态查询

动态获取数据：

根据查询条件 查询  例如： 查询 男 or  查询年龄15 or 男年龄15

数据库查询：

/** *  查询表中的记录总条数 ，动态查询 * */public int count(String name, String sex, String phone) {int c = 0;StringBuffer sql = new StringBuffer("select count(*) from membership WHERE 1=1 ");Connection con = getConnection();PreparedStatement ps = null;ResultSet rs = null;if (name != null && !"null".equals(name)) {sql.append("and mname like ? ");}if (sex != null) {sql.append("and msex=?");}if (phone != null && !"null".equals(phone)) {sql.append("and mmobile=?");}try {ps = con.prepareStatement(sql.toString());int index = 0;if (name != null && !"null".equals(name)) {ps.setString(++index, "%"+name+"%");}if (sex != null) {ps.setString(++index, sex);}if (phone != null && !"null".equals(phone)) {ps.setString(++index, phone);}rs = ps.executeQuery();if (rs.next()) {                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              c = rs.getInt("count(*)");}} catch (SQLException e) {e.printStackTrace();} finally {close(rs, ps, con);}return c;}/** * 分页显示会员列表-- 动态查询 */public List<Membership> limitshowMembersDao(String name ,String sex, String phone,int pageNo, int rowsCount) {membership = new ArrayList<>();StringBuffer sql=new StringBuffer("SELECT c.cardname ,m.mname,m.msex,m.mmobile,m.mjointime ,m.MTotalConsume,m.menablemoney,  m.MTotalIntegral,m.MAddress,m.MBirthday "+ "FROM membership AS m,card  AS c WHERE m.CardID=c.CardID  " );Connection con = getConnection();PreparedStatement ps = null;ResultSet rs = null;if (name != null && !"null".equals(name)) {sql.append("and mname like ? ");}if (sex != null) {sql.append("and msex=?");}if (phone != null && !"null".equals(phone)) {sql.append("and mmobile=?");}sql.append("limit ? ,?");try {ps = con.prepareStatement(sql.toString());int index = 0;if (name != null && !"null".equals(name) ) {ps.setString(++index, "%"+name+"%");}if (sex != null) {ps.setString(++index, sex);}if (phone != null && !"null".equals(phone)) {ps.setString(++index, phone);}ps.setInt(++index, pageNo);ps.setInt(++index, rowsCount);rs = ps.executeQuery();while (rs.next()) {String cardname = rs.getString("cardname");String mname = rs.getString("mname");String msex = rs.getString("msex");String mmobile = rs.getString("mmobile");int totalconsume = rs.getInt("MTotalConsume");int enablemoney = rs.getInt("menablemoney");int totalinegral = rs.getInt("MTotalIntegral");String addrdss = rs.getString("MAddress");String birthady = rs.getString("MBirthday");Membership member = new Membership(cardname, mname, msex, mmobile, totalconsume, enablemoney,totalinegral, addrdss, birthady);membership.add(member);}} catch (SQLException e) {e.printStackTrace();} finally {close(rs, ps, con);}return membership;}



servlet：
protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {String name=request.getParameter("name"); //获取姓名String phone = request.getParameter("phone"); // 获取电话号码String  curpage=request.getParameter("page");String namep=name==null||"".equals(name) ?null:name;String phonep =phone==null ||"".equals(phone) ? null:phone;int currentNo =curpage==null ? 1:Integer.parseInt(curpage);Pager pager=new Pager(msi.count(namep, null, phonep),currentNo);  //传入总条数 ，当前页数pager.setUrl("Selemembership.do");   List<Membership> membership=msi.limitshowMembersServer(namep, null, phonep, pager.getIStart(), pager.getIStep());  //分页查询信息pager.addParameter("name", namep);   //把信息添加，以用于下一页或上一页查询pager.addParameter("phone", phonep);request.setAttribute("membership", membership); // 添加会员信息到sessionrequest.setAttribute("currentPage",currentNo );   //request.setAttribute("pageCount", pager.getPageCount()); // request.setAttribute("pageStr", pager.getPageStr()); // if (membership == null||membership.equals("")) {request.setAttribute("phone", 1);WebContext.foward("selemembership", request, response);//request.getRequestDispatcher("/jsp/selemembership.jsp?phone=1").forward(request, response); //没有这个用户return;}WebContext.foward("selemembership", request, response);//request.getRequestDispatcher("/jsp/selemembership.jsp").forward(request, response); // 转发到显示页面}