使用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語句中了

效果如下：