第一次肝代码肝到这么晚,总算是写出来了,虽然简单,但还是很有成就感的。话不多说,上代码
这次使用的是8.0的数据库,连接方式与5.0版本的数据库略有不同。
首先是项目的结构:(entity包没用,忽略掉)
数据库的配置文件:
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL
username=root
password=123123
这里我的数据库密码为123123。
连接数据库的Until类:
package util;
import java.sql.*;
import java.util.Properties;
public final class JdbcUtil {
private static String driver ;
private static String url ;
private static String user ;
private static String password ;
private static Properties pr=new Properties();
private JdbcUtil() {}
//设计该工具类的静态初始化器中的代码,该代码在装入类时执行,且只执行一次
static {
try {pr.load(JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"));
driver=pr.getProperty("driver");
url=pr.getProperty("url");
user=pr.getProperty("username");
password=pr.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
//设计获得连接对象的方法getConnection()
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
//设计释放结果集、语句和连接的方法free()
public static void free(ResultSet rs, Statement st, Connection conn) {
try { if (rs != null) rs.close();
} catch (SQLException e) {e.printStackTrace();
} finally {
try { if (st != null) st.close();
} catch (SQLException e) {e.printStackTrace();
} finally {
if (conn != null)
try { conn.close();
} catch (SQLException e) {e.printStackTrace();
}
}
}
}
public static void main(String[] args) throws Exception{
JdbcUtil.getConnection();
System.out.println("连接成功");
}
}
数据库的配置信息与连接函数写完后可直接调用使用。注意导入mysql-connect的jar包(具体版本具体应对)。
准备工作做好进行数据库的数据导入(省市级联数据库设计sql文件):
链接:https://pan.baidu.com/s/1s5ICFzr0P9dM0QxMrAu_cA 提取码:gl3q
jsp页面:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<script type="text/javascript" src="js/ajax.js"></script>
<script type="text/javascript">
function refreshCity(){
var p=document.getElementById("prov").value;
var city=document.getElementById("city");
if(p==""){
city.options.length=0;
city.options.add(new Option("--请选择市--"))
}
else{
var url="list";
var params="id="+p ;
sendRequest(url,params,'POST',showCity);
}
}
function showCity(){
var city=document.getElementById("city");
if (httpRequest.readyState == 4) {
if (httpRequest.status == 200) {
var citylist=httpRequest.responseText.split(",");
var citynum=citylist.length;
city.options.length=0;
for(i=0;i<citynum;i++)
city.options.add(new Option(citylist[i]))
}
}
}
</script>
</head>
<body>
<select name="prov" id="prov" onchange="refreshCity();">
<option value="">--请选择省--</option>
<option value="2">北京市</option>
<option value="3">天津市</option>
<option value="4">河北省</option>
<option value="5">山西省</option>
<option value="6">内蒙古自治区</option>
<option value="7">辽宁省</option>
<option value="8">吉林省</option>
<option value="9">黑龙江省</option>
<option value="10">上海市</option>
<option value="11">江苏省</option>
<option value="12">浙江省</option>
<option value="13">安徽省</option>
<option value="14">福建省</option>
<option value="15">江西省</option>
<option value="16">山东省</option>
<option value="17">河南省</option>
<option value="18">湖北省</option>
<option value="19">湖南省</option>
<option value="20">广东省</option>
<option value="21">广西壮族自治区</option>
<option value="22">海南省</option>
<option value="23">重庆市</option>
<option value="24">四川省</option>
<option value="25">贵州省</option>
<option value="26">云南省</option>
<option value="27">西藏自治区</option>
<option value="28">陕西省</option>
<option value="29">甘肃省</option>
<option value="30">青海省</option>
<option value="31">宁夏回族自治区</option>
<option value="32">新疆维吾尔自治区</option>
</select>
<select name="city" id="city" onchange="refreshDistrict();">
<option value="">--请选择市--</option>
</select>
</body>
</html>
Servlet:
package Servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.catalina.mbeans.RoleMBean;
import entity.city;
import util.JdbcUtil;
/**
* Servlet implementation class list
*/
@WebServlet("/list")
public class list extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public list() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
int i = 1;
response.setContentType("text/html;charset = UTF-8");
PrintWriter out = response.getWriter();//定义一个输出
request.setCharacterEncoding("UTF-8");
String id = request.getParameter("id");
List<String> list = new ArrayList<String>();
//Map<String,String> pm = new HashMap<String,String>();
try {
conn = JdbcUtil.getConnection();
String sql = "select region_name from region where parent_id = ?";
ps=conn.prepareStatement(sql);
ps.setString(1,id);
rs=ps.executeQuery();
while(rs.next()){
list.add(rs.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {JdbcUtil.free(rs, ps, conn);}
for(String x:list){
out.print(x+",");
}
}
}
ajax的js文件:
var httpRequest=null;
function createXHR(){
if(window.XMLHttpRequest){ //Mozilla,Safari,Opera,IE7等
httpRequest = new XMLHttpRequest();
}else if(window.ActiveXObject){
try{
httpRequest = new ActiveXObject("Msxml2.XMLHTTP"); //IE较新版本
}catch(e){
try {
httpRequest = new ActiveXObject("Microsoft.XMLHTTP");//IE较老版本
}catch(e){
httpRequest = null;
}
}
}
if(!httpRequest){
alert("fail to create httpRequest");
}
}
function sendRequest(url,params,method,handler){
createXHR();
if(!httpRequest) return false;
httpRequest.onreadystatechange = handler;
if(method == "GET"){
httpRequest.open(method,url+ '?' + params,true);
httpRequest.send(null);
}
if(method == "POST"){
httpRequest.open(method,url,true);
httpRequest.setRequestHeader("Content-type","application/x-www-form-urlencoded");
httpRequest.send(params);
}
}
PS:注意一些jar包的导入