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

来源：互联网发布：shell脚本编程教程编辑：程序博客网时间：2024/06/14 21:00

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

需要两张表:

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;@Overridepublic 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++){  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语句中了

效果如下:

阅读全文

3 0