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函数根本没有调用)

结果:




阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页