级联

目录

列：设计一个级联

数据库设计

城市表 city

省份表province

使用到的相关包和文件

数据库连接db.properties

工具类DBUTils.java

封装省Province.java

封装城市City.java

创建省的接口ProvinceDao.java

省的实现类ProvinceDaoImpl.java

创建service层

service层接口ProvinceService.java

service层接口实现类ProvinceServiceImpl.java

创建页面 default.jsp

servlet层 实现省InitProvinceServlet.java

结果：

servlet层 查询城市

---

列：设计一个级联

用到技术

数据库设计

城市表 city

省份表province

使用到的相关包和文件

使用的库文件

数据库连接db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/ajax
username=root
password=root

工具类DBUTils.java

实现数据的连接 

public class DBUTils {
	 public static String driver;
	 public static String url;
	 public static String user;
	 public static String password;
	 public static Properties prop;
      static{
    	  //只执行一次
    	  try {
    	    prop=new Properties();
			prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
			driver=prop.getProperty("driver");
			url=prop.getProperty("url");
			user=prop.getProperty("username");
			password=prop.getProperty("password");
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	  
      }
      public static Connection getConn(){
    	  try {
		   Class.forName(driver);  //加载驱动
		   Connection conn=DriverManager.getConnection(url, user, password);
		   System.out.println("conn==="+conn);
		   return conn;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
      }
     
}

封装省Province.java

package com.iotek.po;

public class City {
    private Integer id;
    private String name;
    private Integer pid;    //省ID
    public City() {
		// TODO Auto-generated constructor stub
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getPid() {
		return pid;
	}
	public void setPid(Integer pid) {
		this.pid = pid;
	}
	@Override
	public String toString() {
		return "City [id=" + id + ", name=" + name + ", pid=" + pid + "]";
	}
   
    }

封装城市City.java

package com.iotek.po;

public class Province {
      private Integer id;
      private String name;
      public Province() {
		// TODO Auto-generated constructor stub
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Province [id=" + id + ", name=" + name + "]";
	}
      
}

创建省的接口ProvinceDao.java

public interface ProvinceDao {
      public List<Province> queryProvince();//查询省
      public List<City> queryCityByPid(int pid);//查询城市
}

省的实现类ProvinceDaoImpl.java

import java.sql.Connection;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.iotek.po.City;
import com.iotek.po.Province;
import com.iotek.utils.DBUTils;

public class ProvinceDaoImpl implements ProvinceDao {
     private QueryRunner qr;
     private Connection conn;
	 
	@Override
	public List<Province> queryProvince() {
		// TODO Auto-generated method stub
		try {
			conn=DBUTils.getConn();//连接数据库
			qr=new QueryRunner();//
			String  sql="select * from province";//查询语句
			//查询数据库
			List<Province> provinces=qr.query(conn, sql,new BeanListHandler<Province>(Province.class));
			System.out.println(provinces+"*****");
			return provinces;//返回省的集合
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return  null;//当没有找到时，返回空
	}

	@Override
	public List<City> queryCityByPid(int pid) {
		// TODO Auto-generated method stub
		try {
			conn=DBUTils.getConn();
			qr=new QueryRunner();
			String  sql="select * from city where pid=?";//查询语句：根据id查询城市
			List<City> cities=qr.query(conn, sql,new BeanListHandler<City>(City.class),pid);
			System.out.println(cities+"####");
			return cities;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return  null;
	}
}

创建service层

service层接口ProvinceService.java

public interface ProvinceService {
	 public List<Province> queryProvince();
     public List<City> queryCityByPid(int pid);
}

service层接口实现类ProvinceServiceImpl.java

public class ProvinceServiceImpl implements ProvinceService {
     private ProvinceDao pd=new ProvinceDaoImpl();
	@Override
	public List<Province> queryProvince() {//查询省
		// TODO Auto-generated method stub
		return pd.queryProvince();
	}

	@Override
	public List<City> queryCityByPid(int pid) {//根据id查询城市
		// TODO Auto-generated method stub
		return pd.queryCityByPid(pid);
	}

}

创建页面 default.jsp

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="js/jquery-2.0.2.min.js"></script>
  <script type="text/javascript">
       $(function(){
			// $.post()发送一个post请求
    	   $.post("initProvince.do",
    			   null,
    			   function(data){
    		      // alert(data);
    		      $.each(data,function(index,obj)
    		      {//将查询到的数据绑定到下拉别表框（select下拉列表框）
    		    	  $('#province').append('<option value="'+obj.id+'">'+obj.name +'</option>');//动态添加option
    		      });
    		},"json");
    	   
			 //当城市的下拉列表改变时，触发此方法
    	   $('#province').bind("change",function(){
    		   var v_pid=$(this).val();//获取省的id this指#province
    		   //bug,设置城市的下拉列表框默认状态,并清空下拉列表框;
    		   $('#city').html('<option value="0">请选择</option>');
    		   //如果v_pid 为0表示用户选择是请选择,中断程序执行,不要向服务器端发送请求;
    		   if(v_pid==0){
    			   return false;
    		   }
    				 
    		   $.post("queryCityByPid.do",
    				   {"pid":v_pid},
    				   function(data){
    					   $.each(data,function(index,obj)
    				    		      {
    				    		    	  $('#city').append('<option value="'+obj.id+'">'+obj.name +'</option>');
    				    		      });
    				   },"json");
    		   
    	   })   	      	   
       });  
  </script>
</head>
<body>
   省份:<select id="province">
      <option value="0">请选择</option>
   </select>
   城市:<select id="city">
      <option value="0">请选择</option>
   </select>
</body>
</html>

结果：

data.jsp

servlet层 实现省InitProvinceServlet.java

将查询到的省的数据添加到页面的下拉列表当中

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;

import com.iotek.dao.ProvinceDaoImpl;
import com.iotek.po.Province;
import com.iotek.service.ProvinceService;
import com.iotek.service.ProvinceServiceImpl;

public class InitProvinceServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	//调用service层
    private ProvinceService ps=new ProvinceServiceImpl();
	//initProvince.do
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		List<Province> prvonices=ps.queryProvince();//查询省
		System.out.println("0000"+prvonices);
		System.out.println(JSONArray.fromObject(prvonices)+"json...");
		// 将查询的数据（集合）转换成json格式并传到页面
		request.setAttribute("data", JSONArray.fromObject(prvonices));
		//发送请求
		request.getRequestDispatcher("data.jsp").forward(request, response);
	}

}

结果：

servlet层 查询城市

根据省的id查询城市的名称，之后将城市的名称添加到下拉列表中

public class QueryCityByPidServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private ProvinceService ps=new ProvinceServiceImpl();
	//queryCityByPid.do
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		int pid=Integer.parseInt(request.getParameter("pid"));//获取省的id
		List<City> cities=ps.queryCityByPid(pid);
		request.setAttribute("data", JSONArray.fromObject(cities));
		request.getRequestDispatcher("data.jsp").forward(request, response);
			
	}
}

结果：