使用ajax+Jquery配合数据库实现下拉框的二级联动

数据库建school（学院表），major（专业表）

用于获取的两个bean

    package SearchStu.beans;  
      
    public class School {  
     private String sid;  
     public String getSid() {  
        return sid;  
    }  
    public void setSid(String sid) {  
        this.sid = sid;  
    }  
    public String getYname() {  
        return yname;  
    }  
    public void setYname(String yname) {  
        this.yname = yname;  
    }  
    private String yname;  
      
    }  
    -------------------------------------------------------------  
    package SearchStu.beans;  
      
    public class Major {  
     public String getId() {  
            return id;  
        }  
        public void setId(String id) {  
            this.id = id;  
        }  
        public String getMname() {  
            return mname;  
        }  
        public void setMname(String mname) {  
            this.mname = mname;  
        }  
        public String getSchool_sid() {  
            return school_sid;  
        }  
        public void setSchool_sid(String school_sid) {  
            this.school_sid = school_sid;  
        }  
    private String id;  
     private String mname;  
     private String school_sid;  
       
    }  

用于操作数据库的两个dao

先school

    public List<School> getAllSchools() {  
            List<School> list = null;  
            Connection conn = getDBConnection();  
            String sql = new String("SELECT sid,yname FROM school");  
            try {  
                PreparedStatement ps = conn.prepareStatement(sql);  
                ResultSet rs = ps.executeQuery();  
                list = new ArrayList<School>();  
                while (rs.next()) {  
                    School s = new School();  
                    s.setSid(rs.getString(1));  
                    s.setYname(rs.getString(2));  
                      
                    list.add(s);  
                }  
                rs.close();  
                ps.close();  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
            closeConnection(conn);  
            return list;  
        }  
      
        private Connection getDBConnection() {  
            Connection conn = null;  
            try {  
                Class.forName("org.postgresql.Driver");  
                String url = "jdbc:postgresql://localhost:5432/jspdb";  
                conn = DriverManager.getConnection(url, "postgres", "199431@JB");  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
            return conn;  
        }  
      
        private void closeConnection(Connection conn) {  
            try {  
                if (conn != null)  
                    conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
      
    }  

（个人建议先在下拉框中把学院的名称获取出来，所以做完这一步可以跳到school的servlet）

然后major

    public class MajorDao {  
        public List<Major> getAllMajors(String name) {  
            List<Major> list = null;  
            Connection conn = getDBConnection();  
              
             String sql = "select m.mname from major m,"    
                     + "school s where m.school_sid = "    
                     + " (select sid from school where yname ='"+name+"')"  
                     + " and m.school_sid = s.sid";    
      
              
            try {  
                PreparedStatement ps = conn.prepareStatement(sql);  
                //ps.setString(1,name);  
                 System.out.println(sql);    
                ResultSet rs = ps.executeQuery();  
                list = new ArrayList<Major>();  
                while (rs.next()) {  
                    Major s = new Major();  
              
      
                    s.setMname(rs.getString(1));  
      
    //一定要注意这里啊！！之前报错总是显示栏位索引值比栏位数多，在网上查的说法是与参数name前后的单引号有关但是我删掉引号会显示数据库会显示字段不存在（当然会不存在啦但是我打死都不信还是尝试了一下），  
      
    结果发现是我  s.setMname(rs.getString(1));里的1写成了2，也就是说明明只查的出数据库里的一条字段，但你偏要人家获取的是第二条，一个根本就不存在的字段，故出错。。。  
      
                  
                      
                    list.add(s);  
                     System.out.println(s);    
                }  
                rs.close();  
                ps.close();  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
            closeConnection(conn);  
            return list;  
        }  
      
        private Connection getDBConnection() {  
            Connection conn = null;  
            try {  
                Class.forName("org.postgresql.Driver");  
                String url = "jdbc:postgresql://localhost:5432/jspdb";  
                conn = DriverManager.getConnection(url, "postgres", "199431@JB");  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
            return conn;  
        }  
      
        private void closeConnection(Connection conn) {  
            try {  
                if (conn != null)  
                    conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
      
    }  

用于请求响应的servlet

school

    public class ListSchool extends HttpServlet {  
        private static final long serialVersionUID = 1L;  
         
        public ListSchool() {  
            super();  
             
        }  
      
          
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {  
              
            request.setCharacterEncoding("utf-8");  
            response.setContentType("text/html; charset=utf-8");  
            SchoolDao sdao = new SchoolDao();  
            List<School> list = sdao.getAllSchools();    
            ObjectMapper om = new ObjectMapper();  
            response.getWriter().print(om.writeValueAsString(list));  
        }  
      
          
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {  
          
            doGet(request, response);  
        }  
      
    }  

检查school是否在下拉框获取成功。建jsp

成功则显示为：

接下来做major

    public class ListMajor extends HttpServlet {  
        private static final long serialVersionUID = 1L;  
         
        public ListMajor() {  
            super();  
            
        }  
      
          
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {  
             request.setCharacterEncoding("utf-8");    
                response.setContentType("text/html;charset=utf-8");  
               
               
                MajorDao mdao = new MajorDao();  
                String yName = request.getParameter("yxname");  
                List<Major> list = mdao.getAllMajors(yName);    
                System.out.println(yName);//检测参数是否传成功  
            ObjectMapper om = new ObjectMapper();  
                response.getWriter().print(om.writeValueAsString(list));  
      
        }  
      
          
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {  
              
            doGet(request, response);  
        }  
      
    }  

ok，后台都做完了，现在是前端

记得在change函数结束后加上    document.getElementById("mname").innerHTML="";     });

表示清空之前下拉框记录

否则就会

<html>

要注意两个函数放置的位置，如果将bulidYnameSelect放在change之后，change函数是根本没有办法被调用的，所以seled参数没有办法传到ListMajor（这个地方也花了我超久去找bug一度以为我jQuery链接失效导致change函数根本没有调用）

结果：