Home > Article > Web Front-end > Ajax and Jquery combine with the database to realize the secondary linkage of the drop-down box

Ajax and Jquery combine with the database to realize the secondary linkage of the drop-down box

php中世界最好的语言Original: 2018-03-15 09:39:373679browse

This time I will bring you Ajax and Jquery combined with the database to realize the secondary linkage of the drop-down box. Ajax and Jquery combined with the database to realize the secondary linkage of the drop-down box. What are the precautions? What are the following? This is a practical case, let’s take a look at it.

First we need to create a database and insert some data into it

We need two tables:

province: Province table

city: City table

as shown in the picture:

Then create the relevant table in java The entity class corresponds to it

Then, we can start to do jdbc operations

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); 
   }    
 }

First we can get all the provinces when the page is loaded The information, the SQL statement is as follows

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; 
 }

Put the found data into the background and create a SelectedServlet class to receive the queried information about all provinces

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());

A collection will be used here To convert Json objects, we need to import the following packages

Then we start writing the front page:

<body> 
  省份:<select id="province"> 
   <option>--请选择省份--</option> 
  </select>    
  城市:<select id="city"> 
    <option>--请选择城市--</option> 
   </select> 
  <br/><br/> 
  <span></span> 
 </body>

Then the jQuery code is as follows: (Since I imported The jQuery version is relatively low, so the method used is getJSON instead of 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); 
 });

This way, the data can be obtained when the page is loaded

Then Let's do linkage again. First, add a change event to the drop-down box, then obtain the selected information, and send the selected information to another 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); 
 });       
 });

The server queries the obtained information through sql statements, and the SQL code As follows:

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; 
 }

Send the queried data to the background. After receiving the data, the background converts it into a Json object and sends it to the frontend through the callback function. Then the frontend can pass the event Get the data directly without having to jump to various pages. This is 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()); 
 }

. As for the code to display the page, it was also written in the jQuery statement earlier.

The effect is as follows:

# I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other php Chinese websites related articles!

Ajax and Jquery combine with the database to realize the secondary linkage of the drop-down box

Related articles