使用Ajax和Jquery配合数据库实现下拉框的二级联动的示例

2019-11-19 14:28:41

字体：大中小

来源：转载

供稿：网友

首先我们需要先建立好数据库，将一些数据插入进去

需要两张表：

province：省份表

city：城市表

如图：

然后再在java中建立相关的实体类与之对应

再然后，我们就能开始做jdbc的操作了

public class ConnectionFactory {  private static String driver;  private static String url;  private static String user;  private static String password;  static {   Properties prop = new Properties();   //读取文件      try {    InputStream in = ConnectionFactory.class.getResourceAsStream("./jdbc.properties");        prop.load(in);    driver = prop.getProperty("jdbc.driver");    url = prop.getProperty("jdbc.url");    user = prop.getProperty("jdbc.user");    password = prop.getProperty("jdbc.password");   } catch (IOException e) {    e.printStackTrace();   }     }    /**   * 获取连接对象   * @return   */  public static Connection getConnection(){   Connection conn = null;      try {    Class.forName(driver);    conn = DriverManager.getConnection(url, user, password);       } catch (Exception e) {    throw new RuntimeException(e);   }      return conn;  }    /**   * 关闭资源   * @param conn   * @param pstmt   * @param stmt   * @param rs   */  public static void close(Connection conn,PreparedStatement pstmt,Statement stmt,ResultSet rs){        try {     if (conn != null) {      conn.close();     }          if (pstmt != null) {      pstmt.close();     }          if (stmt != null) {      stmt.close();     }          if (rs != null) {      rs.close();     }    } catch (SQLException e) {     throw new RuntimeException(e);    }     }

首先我们可以在页面加载的时候获取所有省份的信息，SQL语句如下

Connection conn = null;  PreparedStatement pstmt = null;  Province province2 = null;    @Override  public ArrayList<Province> findAllPro() {   ResultSet rs = null;   ArrayList<Province> pros = null;   try {    String sql = "select id,place from province";    conn = ConnectionFactory.getConnection();    pstmt = conn.prepareStatement(sql);    pros = new ArrayList<Province>();        rs = pstmt.executeQuery();        while(rs.next()){     Province province = new Province();     province.setId(rs.getInt(1));     province.setPlace(rs.getString(2));     pros.add(province);    }       } catch (SQLException e) {    throw new RuntimeException(e);   }      return pros;  }

将查到的数据放到后台,建立一个SelectedServlet类，用于接收查询到的所有省份的信息

response.setContentType("application/json;charset=utf-8");   response.setCharacterEncoding("utf-8");   request.setCharacterEncoding("utf-8");   //创建一个Place对象   ArrayList<Province> pros= new Place().findAllPro();   PrintWriter out = response.getWriter();   //将集合直接转换为Json对象   out.write(JSONArray.fromObject(pros).toString());

在这里会用到集合转换Json对象，我们需要导入以下几个包

然后我们开始写前台页面：

<body>   省份:<select id="province">    <option>--请选择省份--</option>   </select>      城市:<select id="city">     <option>--请选择城市--</option>    </select>   <br/><br/>   <span></span>  </body>

然后jQuery代码如下:（由于我导入的jQuery版本比较低，所以使用的方法是getJSON，而不是getJson）

$.getJSON("SelectedServlet",function(data,textStatus){  var provinces = data;        var res = "";  for(var i =0;i<provinces.length;i++){  <span style="white-space:pre"> </span>res += "<option>"+provinces[i].place+"</option>";  }   $("#province").append(res);  });

这样就能在页面加载的时候获取到数据

然后我们再来做联动，首先给下拉框添加一个change事件，然后获取选中的信息，将选中的信息发送到另一个CityServlet中

//下拉框改变时触发的事件  $("#province").change(function(){  var seled = $("option:selected").html();        $("span").html(seled);  $.getJSON("CityServlet",{   "province":encodeURI(encodeURI(seled))      },function(data){   $("#city").html("");   var citys = data;   var res = "";   for(var i = 0;i<citys.length;i++){    res += "<option>"+citys[i].place+"</option>";   }   $("#city").append(res);  });        });

服务器通过获得的信息通过sql语句查询出来，SQL代码如下：

public ArrayList<City> findAllCityByPro(String name) {   ResultSet rs = null;   ArrayList<City> citys = null;   try {    //通过名字获得所有值    String sql = "select c.city_place from city c ,"      + "province p where c.province_id = "      + " (select id from province where place = '"+ name +"') "      + " and c.province_id = p.id";    conn = ConnectionFactory.getConnection();    pstmt = conn.prepareStatement(sql);    citys = new ArrayList<City>();    System.out.println(sql);    rs = pstmt.executeQuery();        while(rs.next()){     City city = new City();     city.setPlace(rs.getString(1));     citys.add(city);    }    System.out.println(citys);       } catch (SQLException e) {    e.printStackTrace();   }      return citys;  }

将查询到的数据发送到后台，后台接收到数据后将其转换为Json对象，并通过回调函数发送到前台，然后前台就可以通过事件直接获取到数据，而不用各种跳转页面，这就是Ajax(Asynchronous Javascript And XML),

protected void doGet(HttpServletRequest request, HttpServletResponse response)    throws ServletException, IOException {   response.setContentType("application/json;charset=utf-8");   response.setCharacterEncoding("utf-8");   request.setCharacterEncoding("utf-8"); //  String proName = "浙江";   String proName = URLDecoder.decode(URLDecoder.decode(request.getParameter("province"), "utf-8"),     "utf-8");   ArrayList<City> citys= new Place().findAllCityByPro(proName);   PrintWriter out = response.getWriter();   out.write(JSONArray.fromObject(citys).toString());     }

至于显示页面的代码也在前面写到jQuery语句中了

效果如下：