省市区 地址联动 数据怎么创建

1、在做这个小项目前的准备工作：

1.1、分析：

由于省、市、县城（区）这些数据是存储到了MySQL数据库中的，我们要通过后台servlet获取数据库中的数据，然后再通过转发或者重定向的方式将数据呈现到前台页面中。

1.2、需要导入的jar包有:

mysql驱动包：mysql-connector-Java-5.1.7-bin.jar

c3p0数据库连接池：c3p0-0.9.2.1.jar、mysql-connector-java-5.1.7-bin.jar（c3p0依赖包）

前台c标签（需要通过遍历的方式呈现——c:forEach）：jstl-1.0.2.jar、standard-1.0.1.jar（jstl依赖包）

将集合或者数组转换成json数据格式（Jackson包）：jackson-annotations-2.2.1.jar、jackson-core-2.2.1.jar、jackson-databind-2.2.1.jar

前台页面需要用到jQuery，故还需要导入jquery-1.7.2.js库文件

1.3、该小项目用到的技术：

jdbc的知识、servlet的知识、jquery的知识、Ajax的知识（局部刷新）、标签的知识、EL表达式的知识、JSP的知识

2、开发过程：

2.1、准备数据源

创建一个数据库，命名为thereaction并创建三个表——province（省）、city（市）、county（县/区）

2.2后台开发

创建三个Javabean分别是Province、City、County。（由于太过简单，这里就不粘代码了）

创建Java类和c3p0连接池实现与数据库的连接：DAO.java(实现获取数据库数据的功能)、jdbctools.java（实现获取数据库连接、释放连接的功能）、c3p0-config.xml

jdbctools.java代码如下：

[java] view plain copy

package com.xiaojie.dao

import java.io.IOException

import java.sql.Connection

import java.sql.PreparedStatement

import java.sql.ResultSet

import java.sql.SQLException

import javax.sql.DataSource

import com.mchange.v2.c3p0.ComboPooledDataSource

public class Jdbctools {

private static DataSource ds=null

//数据库连接池应只被初始化一次

static{

ds=new ComboPooledDataSource("helloc3p0")

}

//获取数据库连接

public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException{

return ds.getConnection()

}

public static void shifanglianjie(Connection ct, PreparedStatement ps,ResultSet rs) {

if(rs!=null){

try {

rs.close()

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace()

}

}

if(ps!=null){

try {

ps.close()

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace()

}

}

if(ct!=null){

try {

ct.close()

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace()

}

}

}

}

DAO.java的代码如下

[java] view plain copy

package com.xiaojie.dao

import java.io.IOException

import java.sql.Connection

import java.sql.PreparedStatement

import java.sql.ResultSet

import java.sql.SQLException

import java.util.ArrayList

import java.util.List

import com.xiaojie.beans.City

import com.xiaojie.beans.County

import com.xiaojie.beans.Province

public class DAO {

public List<Province>getprovince(String sql ,Object...args) throws ClassNotFoundException, SQLException, IOException{

List<Province>provinces=new ArrayList<Province>()

//准备去查数据库

Connection ct=null

ct=Jdbctools.getConnection()

System.out.println("获取到数据库的连接了")

PreparedStatement ps=null

ResultSet rs=null

ps=ct.prepareStatement(sql)

for(int i=0i<args.lengthi++){

ps.setObject(i+1, args[i])

}

rs=ps.executeQuery()

while(rs.next()){

provinces.add(new Province(rs.getInt("province_id"),rs.getString("province_name")))

}

Jdbctools.shifanglianjie(ct, ps, rs)

return provinces

}

public List<City>getcity(String sql ,Object...args) throws SQLException, ClassNotFoundException, IOException{

List<City>cities=new ArrayList<City>()

//准备去查数据库

Jdbctools jt=new Jdbctools()

Connection ct=null

ct=jt.getConnection()

PreparedStatement ps=null

ResultSet rs=null

ps=ct.prepareStatement(sql)

for(int i=0i<args.lengthi++){

ps.setObject(i+1, args[i])

}

rs=ps.executeQuery()

while(rs.next()){

cities.add(new City(rs.getInt("city_id"),rs.getString("city_name")))

}

jt.shifanglianjie(ct, ps, rs)

return cities

}

public List<County>getcounty(String sql,Object...args ) throws SQLException, ClassNotFoundException, IOException{

List<County>counties=new ArrayList<County>()

//准备去查数据库

Jdbctools jt=new Jdbctools()

Connection ct=null

ct=jt.getConnection()

PreparedStatement ps=null

ResultSet rs=null

ps=ct.prepareStatement(sql)

for(int i=0i<args.lengthi++){

ps.setObject(i+1, args[i])

}

rs=ps.executeQuery()

while(rs.next()){

counties.add(new County(rs.getInt("county_id"),rs.getString("county_name")))

}

jt.shifanglianjie(ct, ps, rs)

return counties

}

}

c3p0-config.xml的代码如下：

[html] view plain copy

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>

<named-config name="helloc3p0">

<!-- 连接数据源的基本属性 -->

<property name="user">root</property>

<property name="password"></property>

<property name="driverClass">com.mysql.jdbc.Driver</property>

<property name="jdbcUrl">jdbc:mysql:///thereaction</property>

<!-- 若数据库中连接数不足时，一次向数据库服务器申请多少个连接 -->

<property name="acquireIncrement">5</property>

<!-- 初始化数据库连接池时连接的数量 -->

<property name="initialPoolSize">5</property>

<!-- 数据库连接池中的最小的数据库连接数 -->

<property name="minPoolSize">5</property>

<!-- 数据库连接池中的最大的数据库连接数 -->

<property name="maxPoolSize">10</property>

<!-- c3p0数据库连接可以维护的statement的个数 -->

<property name="maxStatements">20</property>

<!-- 每个连接同时可以使用的statement对象的个数 -->

<property name="maxStatementsPerConnection">5</property>

</named-config>

</c3p0-config>

创建servlet.java 文件

[java] view plain copy

package com.xiaojie.servlet

import java.io.IOException

import java.lang.reflect.Method

import java.sql.SQLException

import java.util.List

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 com.fasterxml.jackson.databind.ObjectMapper

import com.xiaojie.beans.City

import com.xiaojie.beans.County

import com.xiaojie.beans.Province

import com.xiaojie.dao.DAO

/**

* Servlet implementation class ThreeactiondServlet

*/

@WebServlet("/threeactiondServlet")

public class ThreeactiondServlet extends HttpServlet {

private static final long serialVersionUID = 1L

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

String methodname=request.getParameter("method")

try {

Method method=getClass().getDeclaredMethod(methodname,HttpServletRequest.class,HttpServletResponse.class)

method.invoke(this, request,response)//调用各自的方法

} catch (Exception e) {

e.printStackTrace()

}

}

private DAO dao=new DAO()

protected void province(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ClassNotFoundException, SQLException {

System.out.println("province的servlet进入了")

String sql="select province_id,province_name from province"

List<Province>provinces=dao.getprovince(sql)

request.setAttribute("provinces", provinces)

System.out.println(provinces)

//注意：这里不能用重定向的形式，因为我们好不容易在request请求域中存储了省的信息，目的是在前台页面中能够从请求域中获取到我们存在数据库中的值

//故这里只能用转发的方式

request.getRequestDispatcher("/index2.jsp").forward(request, response)

}

protected void city(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ClassNotFoundException, SQLException {

System.out.println("city的servlet进入了")

String province_id=request.getParameter("province_id")

String sql="select city_id,city_name from city where province_id=?"

List<City>cities=dao.getcity(sql,Integer.parseInt(province_id))

ObjectMapper mapper=new ObjectMapper()

String result=mapper.writeValueAsString(cities)

System.out.println(result)

response.setContentType("text/javascript")

response.getWriter().print(result)

}

protected void county(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ClassNotFoundException, SQLException {

System.out.println("county的servlet进入了")

String city_id=request.getParameter("city_id")

String sql="select county_id,county_name from county where city_id=?"

List<County>counties=dao.getcounty(sql,Integer.parseInt(city_id))

ObjectMapper mapper=new ObjectMapper()

String result=mapper.writeValueAsString(counties)

System.out.println(result)

response.setContentType("text/javascript")

response.getWriter().print(result)

}

}

把本地sql server数据库导入到服务器的方法步骤（SQL 2000为例）：

首先打开本地的SQL 2000的企业管理器,然后根据数据库开通信中的地址、用户名密码,建立远程SQL注册,连上远程服务器上的数据库:

然后登录到远程数据库服务器:

1、右击准备导入数据的数据库,选择“所有任务”下的“导入数据”

2、进入DTS导入/导出向导,点击“下一步”按钮继续

3、选择数据源,输入数据源所在的数据库服务器IP、用户名、密码和要复制数据的源数据库,点击“下一步”按钮

4、选择目的,输入目的数据库所在的数据库服务器名称、用户名、密码和要复制数据的目的数据库(这个信息在用户区,数据库管理中可以看到),点击“下一步”按钮

5、选择“在SQL Server数据库之间复制对象和数据”方式,点“下一步”继续

6、注意取消安全措施选项

7、设定调度方式,一般选“立即运行”就可以,然后点“下一步”继续

8、点“完成”执行

--省级 provincial

create table provincial

(

provincialID int,

provincialName varchar(50),

primary key (provincialID)

)engine=INNODB default charset=gbk

insert into provincial values(1,'北京市')

insert into provincial values(2,'天津市')

insert into provincial values(3,'上海市')

insert into provincial values(4,'重庆市')

insert into provincial values(5,'河北省')

insert into provincial values(6,'山西省')

insert into provincial values(7,'台湾省')

insert into provincial values(8,'辽宁省')

insert into provincial values(9,'吉林省')

insert into provincial values(10,'黑龙江省')

insert into provincial values(11,'江苏省')

insert into provincial values(12,'浙江省')

insert into provincial values(13,'安徽省')

insert into provincial values(14,'福建省')

insert into provincial values(15,'江西省')

insert into provincial values(16,'山东省')

insert into provincial values(17,'河南省')

insert into provincial values(18,'湖北省')

insert into provincial values(19,'湖南省')

insert into provincial values(20,'广东省')

insert into provincial values(21,'甘肃省')

insert into provincial values(22,'四川省')

insert into provincial values(23,'贵州省')

insert into provincial values(24,'海南省')

insert into provincial values(25,'云南省')

insert into provincial values(26,'青海省')

insert into provincial values(27,'陕西省')

insert into provincial values(28,'广西壮族自治区')

insert into provincial values(29,'西藏自治区')

insert into provincial values(30,'宁夏回族自治区')

insert into provincial values(31,'新疆维吾尔自治区')

insert into provincial values(32,'内蒙古自治区')

insert into provincial values(33,'澳门特别行政区')

insert into provincial values(34,'香港特别行政区')

select provincialID,provincialName from provincial

--城市 city

create table city

(

cityID int not null,

cityName varchar(50) not null,

provincialID int not null,

primary key (cityID)

)engine=INNODB default charset=gbkalter table city add constraint FK_pro_city_provincialID foreign key (provincialID)

references provincial (provincialID)

----------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------

--插入各个省的城市数据

--4个直辖市

insert into city values(1,'北京市',1)

insert into city values(2,'天津市',2)

insert into city values(3,'上海市',3)

insert into city values(4,'重庆市',4)

--select * from city where provincialID=4

----------------------------------------------------------------

--5河北省 11个地级市

insert into city values(5,'石家庄市',5)

insert into city values(6,'唐山市',5)

insert into city values(7,'秦皇岛市',5)

insert into city values(8,'邯郸市',5)

insert into city values(9,'邢台市',5)

insert into city values(10,'保定市',5)

insert into city values(11,'张家口市',5)

insert into city values(12,'承德市',5)

insert into city values(13,'沧州市',5)

insert into city values(14,'廊坊市',5)

insert into city values(15,'衡水市',5)

--select * from city where provincialID=5 order by cityID

----------------------------------------------------------------

--6山西省 11个城市

insert into city values(16,'太原市',6)

insert into city values(17,'大同市',6)

insert into city values(18,'阳泉市',6)

insert into city values(19,'长治市',6)

insert into city values(20,'晋城市',6)

insert into city values(21,'朔州市',6)

insert into city values(22,'晋中市',6)

insert into city values(23,'运城市',6)

insert into city values(24,'忻州市',6)

insert into city values(25,'临汾市',6)

insert into city values(26,'吕梁市',6)

--select * from city where provincialID=6 order by cityID

----------------------------------------------------------------

--7台湾省(台湾本岛和澎湖共设7市、16县，其中台北市和高雄市为“院辖市”，直属“行政院”，其余属台湾省；市下设区，县下设市（县辖市）、镇、乡，合称区市镇乡。)

insert into city values(27,'台北市',7)

insert into city values(28,'高雄市',7)

insert into city values(29,'基隆市',7)

insert into city values(30,'台中市',7)

insert into city values(31,'台南市',7)

insert into city values(32,'新竹市',7)

insert into city values(33,'嘉义市',7)

insert into city values(34,'台北县',7)

insert into city values(35,'宜兰县',7)

insert into city values(36,'桃园县',7)

insert into city values(37,'新竹县',7)

insert into city values(38,'苗栗县',7)

insert into city values(39,'台中县',7)

insert into city values(40,'彰化县',7)

insert into city values(41,'南投县',7)

insert into city values(42,'云林县',7)

insert into city values(43,'嘉义县',7)

insert into city values(44,'台南县',7)

insert into city values(45,'高雄县',7)

insert into city values(46,'屏东县',7)

insert into city values(47,'澎湖县',7)

insert into city values(48,'台东县',7)

insert into city values(49,'花莲县',7)

--select * from city where provincialID=7 order by cityID

----------------------------------------------------------------

--8辽宁省 14个地级市

insert into city values(50,'沈阳市',8)

insert into city values(51,'大连市',8)

insert into city values(52,'鞍山市',8)

insert into city values(53,'抚顺市',8)

insert into city values(54,'本溪市',8)

insert into city values(55,'丹东市',8)

insert into city values(56,'锦州市',8)

insert into city values(57,'营口市',8)

insert into city values(58,'阜新市',8)

insert into city values(59,'辽阳市',8)

insert into city values(60,'盘锦市',8)

insert into city values(61,'铁岭市',8)

insert into city values(62,'朝阳市',8)

insert into city values(63,'葫芦岛市',8)

--select * from city where provincialID=8 order by cityID

----------------------------------------------------------------

--9吉林省(2006年，辖：8个地级市、1个自治州；20个市辖区、20个县级市、17个县、3个自治县。)

insert into city values(64,'长春市',9)

insert into city values(65,'吉林市',9)

insert into city values(66,'四平市',9)

insert into city values(67,'辽源市',9)

insert into city values(68,'通化市',9)

insert into city values(69,'白山市',9)

insert into city values(70,'松原市',9)

insert into city values(71,'白城市',9)

insert into city values(72,'延边朝鲜族自治州',9)

--select * from city where provincialID=9 order by cityID

----------------------------------------------------------------

--10黑龙江省(2006年，辖：12地级市、1地区；64市辖区、18县级市、45县、1自治县)

insert into city values(73,'哈尔滨市',10)

insert into city values(74,'齐齐哈尔市',10)

insert into city values(75,'鹤 岗 市',10)

insert into city values(76,'双鸭山市',10)

insert into city values(77,'鸡 西 市',10)

insert into city values(78,'大 庆 市',10)

insert into city values(79,'伊 春 市',10)

insert into city values(80,'牡丹江市',10)

insert into city values(81,'佳木斯市',10)

insert into city values(82,'七台河市',10)

insert into city values(83,'黑 河 市',10)

insert into city values(84,'绥 化 市',10)

insert into city values(85,'大兴安岭地区',10)

--select * from city where provincialID=10 order by cityID

----------------------------------------------------------------

--11江苏省(2005年辖：13个地级市；54个市辖区、27个县级市、25个县)

insert into city values(86,'南京市',11)

insert into city values(87,'无锡市',11)

insert into city values(88,'徐州市',11)

insert into city values(89,'常州市',11)

insert into city values(90,'苏州市',11)

insert into city values(91,'南通市',11)

insert into city values(92,'连云港市',11)

insert into city values(93,'淮安市',11)

insert into city values(94,'盐城市',11)

insert into city values(95,'扬州市',11)

insert into city values(96,'镇江市',11)

insert into city values(97,'泰州市',11)

insert into city values(98,'宿迁市',11)

--select * from city where provincialID=11 order by cityID

----------------------------------------------------------------

--12浙江省(2006年，辖：11个地级市；32个市辖区、22个县级市、35个县、1个自治县。)

insert into city values(99,'杭州市',12)

insert into city values(100,'宁波市',12)

insert into city values(101,'温州市',12)

insert into city values(102,'嘉兴市',12)

insert into city values(103,'湖州市',12)

insert into city values(104,'绍兴市',12)

insert into city values(105,'金华市',12)

insert into city values(106,'衢州市',12)

insert into city values(107,'舟山市',12)

insert into city values(108,'台州市',12)

insert into city values(109,'丽水市',12)

--select * from city where provincialID=12 order by cityID

---------------------------------------------------------------- 没有写完, 写不下了,你加我! 775790318

---