首先我們需要先建立好數(shù)據(jù)庫,將一些數(shù)據(jù)插入進(jìn)去
讓客戶滿意是我們工作的目標(biāo),不斷超越客戶的期望值來自于我們對(duì)這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶,將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長期合作伙伴,公司提供的服務(wù)項(xiàng)目有:域名注冊(cè)、網(wǎng)頁空間、營銷軟件、網(wǎng)站建設(shè)、綏江網(wǎng)站維護(hù)、網(wǎng)站推廣。
需要兩張表:
province:省份表
city: 城市表
如圖:
然后再在java中建立相關(guān)的實(shí)體類與之對(duì)應(yīng)
再然后,我們就能開始做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(); } } /** * 獲取連接對(duì)象 * @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; } /** * 關(guān)閉資源 * @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); } }
首先我們可以在頁面加載的時(shí)候獲取所有省份的信息,SQL語句如下
Connection conn = null; PreparedStatement pstmt = null; Province province2 = null; @Override public ArrayListfindAllPro() { ResultSet rs = null; ArrayList pros = null; try { String sql = "select id,place from province"; conn = ConnectionFactory.getConnection(); pstmt = conn.prepareStatement(sql); pros = new ArrayList (); 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; }
將查到的數(shù)據(jù)放到后臺(tái),建立一個(gè)SelectedServlet類,用于接收查詢到的所有省份的信息
response.setContentType("application/json;charset=utf-8"); response.setCharacterEncoding("utf-8"); request.setCharacterEncoding("utf-8"); //創(chuàng)建一個(gè)Place對(duì)象 ArrayListpros= new Place().findAllPro(); PrintWriter out = response.getWriter(); //將集合直接轉(zhuǎn)換為Json對(duì)象 out.write(JSONArray.fromObject(pros).toString());
在這里會(huì)用到集合轉(zhuǎn)換Json對(duì)象,我們需要導(dǎo)入以下幾個(gè)包
然后我們開始寫前臺(tái)頁面:
省份: 城市:
然后jQuery代碼如下:(由于我導(dǎo)入的jQuery版本比較低,所以使用的方法是getJSON,而不是getJson)
$.getJSON("SelectedServlet",function(data,textStatus){ var provinces = data; var res = ""; for(var i =0;ires += ""; } $("#province").append(res); });
這樣就能在頁面加載的時(shí)候獲取到數(shù)據(jù)
然后我們?cè)賮碜雎?lián)動(dòng),首先給下拉框添加一個(gè)change事件,然后獲取選中的信息,將選中的信息發(fā)送到另一個(gè)CityServlet中
//下拉框改變時(shí)觸發(fā)的事件 $("#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[i].place+""; } $("#city").append(res); }); });
服務(wù)器通過獲得的信息通過sql語句查詢出來,SQL代碼如下:
public ArrayListfindAllCityByPro(String name) { ResultSet rs = null; ArrayList 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 (); 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; }
將查詢到的數(shù)據(jù)發(fā)送到后臺(tái),后臺(tái)接收到數(shù)據(jù)后將其轉(zhuǎn)換為Json對(duì)象,并通過回調(diào)函數(shù)發(fā)送到前臺(tái),然后前臺(tái)就可以通過事件直接獲取到數(shù)據(jù),而不用各種跳轉(zhuǎn)頁面,這就是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"); ArrayListcitys= new Place().findAllCityByPro(proName); PrintWriter out = response.getWriter(); out.write(JSONArray.fromObject(citys).toString()); }
至于顯示頁面的代碼也在前面寫到j(luò)Query語句中了
效果如下:
以上這篇使用Ajax和Jquery配合數(shù)據(jù)庫實(shí)現(xiàn)下拉框的二級(jí)聯(lián)動(dòng)的示例就是小編分享給大家的全部內(nèi)容了,希望能給大家一個(gè)參考,也希望大家多多支持創(chuàng)新互聯(lián)。