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

最新推荐文章于 2023-03-07 17:18:35 发布

evan_qb

最新推荐文章于 2023-03-07 17:18:35 发布

阅读量2w

点赞数 14

分类专栏： Java学习 # javaweb 文章标签：数据库 java ajax jquery

本文链接：https://blog.csdn.net/Evan_QB/article/details/75442389

版权

Java学习同时被 2 个专栏收录

193 篇文章 1 订阅

订阅专栏

javaweb

31 篇文章 0 订阅

订阅专栏

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

需要两张表:

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++){
  		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( A synchronous J avascript And X ML),

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

效果如下: