JSP+Servlet+javabean+oracle實現頁面多條件模糊查詢

標籤：

之前寫過一篇JSP+Servlet+javabean+mysql實現頁面多條件模糊查詢 使用的是mysql進行的分頁查詢，mysql用limit控制，而oracle則是用rownum,今天第一次寫oracle，查了一下資料試了一下，把代碼帖出來

oracle的資料來源配置也不同：

driverName=oracle.jdbc.driver.OracleDriverurl=jdbc\:oracle\:thin\:@127.0.0.1\:1521\:orclusername=marketManagerpwd=marketManager

直接帖實作類別(特別注意紅色代碼體)：

package pb.market.com.cn.dao.impl;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import pb.market.com.cn.dao.ProviderDao;import pb.market.com.cn.pojo.Provider;import pb.market.com.cn.util.BaseDao;public class ProviderDaoImpl extends BaseDao implements ProviderDao {    //擷取供應商列表    public List<Provider> getProviderList(int pageNo, int pageSize, String where) {        List<Provider> providerList =new ArrayList<Provider>();        String sql="select * from ( select rownum as r,a.* from gongyingshang a where "+where+" rownum<=? ) where r>?";        Object[] params ={pageSize*pageNo,(pageNo-1)*pageSize};        if(this.getConnection()){            System.out.println(sql);            ResultSet rs = this.executeQuery(sql, params);            try {                while(rs.next()){                    Provider provider = new Provider();                    provider.setId(rs.getInt("id"));                    provider.setGysno(rs.getInt("gysno"));                    provider.setGysname(rs.getString("gysname"));                    provider.setGystel(rs.getString("gystel"));                    provider.setGysfax(rs.getString("gysfax"));                    provider.setCreatedate(rs.getTimestamp("createdate"));                    provider.setCreateby(rs.getString("createby"));                    provider.setModifydate(rs.getTimestamp("modifydate"));                    provider.setModifyby(rs.getString("modifyby"));                    providerList.add(provider);                                    }            } catch (SQLException e) {                e.printStackTrace();            }finally{                this.closeConnection();            }        }        return providerList;    }    /*public static void main(String[] args) {                ProviderDao dd=new ProviderDaoImpl();        String where="金";        String where1="gysname like ‘%"+where+"%‘ and";        List<Provider> providerList =dd.getProviderList(1, 5, where1);                for (Provider provider : providerList) {            System.out.println("11111111111111");            System.out.println(provider.getId());        }        }*/    //擷取供應商總數量    public int getProviderCount(String where) {        int count=0;        String sql="select count(*)as count from gongyingshang "+where;        Object[] params={};        if(this.getConnection()){            ResultSet rs  =this.executeQuery(sql, params);            try {                if(rs.next()){                    count = rs.getInt("count");                }            } catch (SQLException e) {                e.printStackTrace();            }finally{                this.closeConnection();            }        }        return count;    }}

中間的service實作類別，我的業務較簡單，service層基本不做什麼，只是調用一下dao層實作類別的方法而已：

package pb.market.com.cn.service.impl;import java.util.List;import pb.market.com.cn.dao.ProviderDao;import pb.market.com.cn.dao.impl.ProviderDaoImpl;import pb.market.com.cn.pojo.Provider;import pb.market.com.cn.service.ProviderService;public class ProviderServiceImpl implements ProviderService {    private ProviderDao providerDao;    public ProviderServiceImpl(){        providerDao=new ProviderDaoImpl();    }    public List<Provider> getProviderList(int pageNo, int pageSize, String where) {        return providerDao.getProviderList(pageNo, pageSize, where);    }    public int getProviderCount(String where) {        return providerDao.getProviderCount(where);    }}

servlet：

package pb.market.com.cn.servlet;import java.io.IOException;import java.io.PrintWriter;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import pb.market.com.cn.pojo.Provider;import pb.market.com.cn.service.ProviderService;import pb.market.com.cn.service.impl.ProviderServiceImpl;public class ProviderListServlet extends HttpServlet {    public void doGet(HttpServletRequest request, HttpServletResponse response)            throws ServletException, IOException {        try{            request.setCharacterEncoding("utf-8");            response.setCharacterEncoding("utf-8");            ProviderService providerService =new ProviderServiceImpl();            StringBuffer sb =new StringBuffer(1024);            StringBuffer sb1 =new StringBuffer(1024);            //擷取模糊查詢的值            String where = request.getParameter("proName");            //System.out.println("where================="+where);            //如果為空白            if(where!=null && !"".equals(where)){                sb.append("where gysname like ‘%"+where+"%‘");                sb1.append("gysname like ‘%"+where+"%‘ and");            }            //設定每頁顯示3條            int pageSize=3;            //得總條數            int totalCount =providerService.getProviderCount(sb.toString());                        //得到總頁數,如果總頁數對一頁的量模數==0，則。。。            int totalPageCount=0;            if((totalCount%pageSize)==0){                totalPageCount=totalCount/pageSize;            }else{                totalPageCount=totalCount/pageSize +1;            }            //擷取當前頁，預設一開啟進來即是第1頁            String pageIndex = request.getParameter("pageIndex");            //System.out.println("pageIndex==============="+pageIndex);            int currentPageNo=0;            if(pageIndex==null){                currentPageNo=1;            }else{                //System.out.println("這裡怎麼可能進得來？？？？？？？？？？？");                currentPageNo= Integer.parseInt(pageIndex);            }                        // 首頁和尾頁的異常控制            if (currentPageNo <= 0) {                currentPageNo = 1;            } else if (currentPageNo > totalPageCount) {                currentPageNo = totalPageCount;            }            //把各個頁碼傳給JSp            request.setAttribute("pageSize", pageSize);            request.setAttribute("totalCount", totalCount);            request.setAttribute("totalPageCount", totalPageCount);            request.setAttribute("currentPageNo", currentPageNo);            //擷取providerList            List<Provider> providerList = providerService.getProviderList(currentPageNo, pageSize, sb1.toString());            request.setAttribute("providerList", providerList);            request.getRequestDispatcher("/jsp/providerList.jsp").forward(request, response);                    }catch(Exception e){            e.printStackTrace();        }                    }        public void doPost(HttpServletRequest request, HttpServletResponse response)            throws ServletException, IOException {        doGet(request, response);            }}

後面的JSP頁面我就省略不寫了。。。。。跟文頭說的那篇mysql流程是一樣，寫法也類似

 

JSP+Servlet+javabean+oracle實現頁面多條件模糊查詢