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

更新时间：2020-06-25 05:30:02 作者：startmvc

首先我们需要先建立好数据库，将一些数据插入进去需要两张表：province：省份表city：城市

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

需要两张表：

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语句中了

效果如下：