mysql 省市县三级联动查询_三级联动查询全国省市区（xml与数据库）

提供有china.xml和china.sql文件，实现全国省市区的三级联动效果

一、xml实现

import java.awt.EventQueue;

import javax.swing.JFrame;

import javax.swing.JPanel;

import javax.swing.border.EmptyBorder;

import javax.swing.JComboBox;

import javax.swing.JLabel;

import org.dom4j.Attribute;

import org.dom4j.Document;

import org.dom4j.DocumentException;

import org.dom4j.Element;

import org.dom4j.Node;

import org.dom4j.io.SAXReader;

import java.awt.Font;

import java.awt.event.ItemEvent;

import java.awt.event.ItemListener;

import java.io.File;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

import javax.swing.DefaultComboBoxModel;

@SuppressWarnings("serial")

public class ChinaJFrame extends JFrame {

private JPanel contentPane;

private List cityList=null;

private List provinceList=null;

private List countyList=null;

@SuppressWarnings("rawtypes")

private JComboBox provinceComboBox, cityComboBox, countyComboBox;

SAXReader reader = new SAXReader();

Document document = null;

List list=null;

/**

* Launch the application.

*/

public static void main(String[] args) {

EventQueue.invokeLater(new Runnable() {

public void run() {

try {

ChinaJFrame frame = new ChinaJFrame();

frame.setVisible(true);

} catch (Exception e) {

e.printStackTrace();

}

}

});

}

/**

* Create the frame.

*

* @throws DocumentException

*/

@SuppressWarnings({ "rawtypes", "unchecked" })

public ChinaJFrame() throws DocumentException {

setResizable(false);

setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

setBounds(100, 100, 450, 300);

contentPane = new JPanel();

contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));

setContentPane(contentPane);

contentPane.setLayout(null);

provinceComboBox = new JComboBox();

provinceComboBox.setModel(new DefaultComboBoxModel(new String[] { "省份" }));

provinceComboBox.setBounds(33, 106, 108, 21);

cityComboBox = new JComboBox();

cityComboBox.setBounds(171, 106, 108, 21);

cityComboBox.setModel(new DefaultComboBoxModel(new String[] { "地级市" }));

countyComboBox = new JComboBox();

countyComboBox.setBounds(302, 106, 108, 21);

countyComboBox.setModel(new DefaultComboBoxModel(new String[] { "市、县级市" }));

provinceList = getProvince("province");

for (String s : provinceList) {

provinceComboBox.addItem(s);

}

provinceComboBox.addItemListener(new ItemListener() {

@Override

public void itemStateChanged(ItemEvent e) {

if(e.getStateChange() == ItemEvent.SELECTED){

int ProvinceIndex = provinceComboBox.getSelectedIndex();

cityList = getCity(ProvinceIndex);

cityComboBox.removeAllItems();

for (String s : cityList) {

cityComboBox.addItem(s);

}

}

}

});

cityComboBox.addItemListener(new ItemListener() {

@Override

public void itemStateChanged(ItemEvent e) {

if(e.getStateChange() == ItemEvent.SELECTED){

cityComboBox=(JComboBox) e.getSource();

String name2=(String) cityComboBox.getSelectedItem();

countyList=getCounty(name2);

countyComboBox.removeAllItems();

for(String cl:countyList){

countyComboBox.addItem(cl);

}

}

}

});

JLabel lblNewLabel = new JLabel(

"\u5168\u56FD\u57CE\u5E02\u4E09\u7EA7\u8054\u52A8");

lblNewLabel.setFont(new Font("宋体", Font.BOLD, 18));

lblNewLabel.setBounds(140, 25, 160, 48);

contentPane.add(provinceComboBox);

contentPane.add(cityComboBox);

contentPane.add(countyComboBox);

contentPane.add(lblNewLabel);

}

@SuppressWarnings("unchecked")

public List getProvince(String name) {

list = new ArrayList();

try {

document = reader.read(new File("src/china.xml"));

} catch (DocumentException e) {

e.printStackTrace();

}

Element root = document.getRootElement();

for(Iterator i = root.elementIterator(name); i.hasNext();) {

Element node = i.next();

List attrs = node.attributes();

if (attrs != null) {

for (Attribute attr : attrs) {

list.add(attr.getValue());

}

}

}

return list;

}

@SuppressWarnings("unchecked")

public List getCity(int index) {

list = new ArrayList();

try {

document = reader.read(new File("src/china.xml"));

} catch (DocumentException e) {

e.printStackTrace();

}

Element root = document.getRootElement();

List elements = root.elements();

List elements1 = elements.get(index-1).elements();

for (int i=0; i < elements1.size(); i++) {

List attrs = elements1.get(i).attributes();

if (attrs != null) {

for (Attribute attr : attrs) {

list.add(attr.getValue());

}

}

}

return list;

}

@SuppressWarnings("unchecked")

public List getCounty(String name2){

list = new ArrayList();

try {

document = reader.read(new File("src/china.xml"));

} catch (DocumentException e) {

e.printStackTrace();

}

province[@name='北京市']

List nodes = document.selectNodes("//city[@name='" + name2 + "']//county//@name");

for (Node node : nodes) {

list.add(node.getText());

}

return list;

}

}

二、数据库实现

(1)数据库连接的配置文件

com.mysql.jdbc.Driver

jdbc:mysql://localhost:3306/china

root

root

100

20

10

5

com.mysql.jdbc.Driver

jdbc:mysql://localhost:3306/china

root

root

100

20

10

5

(2)实体Bean

public class Province {//省

private Integer id;

private String name;

}

public class City {//市

private Integer id;

private String name;

private Integer p_id;

}

public class County {//区

private Integer id;

private String name;

private Integer c_id;

}

(3)数据库操作接口定义和实现

public interface ChinaDao {

List getObjects();

List getCityObjectsById(Integer id);

List getCountyObjectsById(Integer id);

City getIdByName(String name);

}

public class ChinaDaoImpl implements ChinaDao{

private JdbcTemplate jdbcTemplate=new JdbcTemplate(DBConn.getDataSourse());

private List entities=null;

@SuppressWarnings({ "unchecked", "rawtypes" })

@Override

public List getObjects() {

String sql="select id, name from province";

entities=jdbcTemplate.query(sql, new RowMapper(){

@Override

public Object mapRow(ResultSet rs, int arg1) throws SQLException {

Province province=new Province();

province.setId(rs.getInt("id"));

province.setName(rs.getString("name"));

return province;

}

});

return entities;

}

@SuppressWarnings({ "unchecked", "rawtypes" })

@Override

public List getCityObjectsById(Integer id) {

String sql="select id, name ,p_id from city where p_id=?";

entities=jdbcTemplate.query(sql, new Object[]{id},new RowMapper(){

@Override

public Object mapRow(ResultSet rs, int arg1) throws SQLException {

City city=new City();

city.setId(rs.getInt("id"));

city.setName(rs.getString("name"));

city.setP_id(rs.getInt("p_id"));

return city;

}

});

return entities;

}

@SuppressWarnings({ "unchecked", "rawtypes" })

@Override

public List getCountyObjectsById(Integer id) {

String sql="select id, name ,c_id from county where c_id=?";

entities=jdbcTemplate.query(sql, new Object[]{id},new RowMapper(){

@Override

public Object mapRow(ResultSet rs, int arg1) throws SQLException {

County county=new County();

county.setId(rs.getInt("id"));

county.setName(rs.getString("name"));

county.setC_id(rs.getInt("c_id"));

return county;

}

});

return entities;

}

@SuppressWarnings({ "unchecked", "rawtypes" })

@Override

public City getIdByName(String name) {

String sql="select id, name ,p_id from city where name=?";

City entity=jdbcTemplate.queryForObject(sql, new Object[]{name},new RowMapper(){

@Override

public Object mapRow(ResultSet rs, int arg1) throws SQLException {

City city=new City();

city.setId(rs.getInt("id"));

city.setName(rs.getString("name"));

city.setP_id(rs.getInt("p_id"));

return city;

}

});

return entity;

}

}

(4)具体实现

public class ChinaJFrame extends JFrame {

private ChinaDao chinaDao=new ChinaDaoImpl();

private JPanel contentPane;

private List cityList=null;

private List provinceList=null;

private List countyList=null;

@SuppressWarnings("rawtypes")

private JComboBox provinceComboBox, cityComboBox, countyComboBox;

SAXReader reader = new SAXReader();

Document document = null;

List list=null;

/**

* Launch the application.

*/

public static void main(String[] args) {

EventQueue.invokeLater(new Runnable() {

public void run() {

try {

ChinaJFrame frame = new ChinaJFrame();

frame.setVisible(true);

} catch (Exception e) {

e.printStackTrace();

}

}

});

}

/**

* Create the frame.

*

* @throws DocumentException

*/

@SuppressWarnings({ "rawtypes", "unchecked" })

public ChinaJFrame() throws DocumentException {

setResizable(false);

setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

setBounds(100, 100, 450, 300);

contentPane = new JPanel();

contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));

setContentPane(contentPane);

contentPane.setLayout(null);

provinceComboBox = new JComboBox();

provinceComboBox.setModel(new DefaultComboBoxModel(new String[] { "省份" }));

provinceComboBox.setBounds(33, 106, 108, 21);

cityComboBox = new JComboBox();

cityComboBox.setBounds(171, 106, 108, 21);

cityComboBox.setModel(new DefaultComboBoxModel(new String[] { "地级市" }));

countyComboBox = new JComboBox();

countyComboBox.setBounds(302, 106, 108, 21);

countyComboBox.setModel(new DefaultComboBoxModel(new String[] { "市、县级市" }));

List provinces=chinaDao.getObjects();

provinceList = new ArrayList();

for(Province p:provinces){

provinceList.add(p.getName());

}

for (String s : provinceList) {

provinceComboBox.addItem(s);

}

provinceComboBox.addItemListener(new ItemListener() {

@Override

public void itemStateChanged(ItemEvent e) {

if(e.getStateChange() == ItemEvent.SELECTED){

cityList=new ArrayList();

int ProvinceIndex = provinceComboBox.getSelectedIndex();

List cities=chinaDao.getCityObjectsById(ProvinceIndex);

for(City city:cities){

cityList.add(city.getName());

}

cityComboBox.removeAllItems();

for (String s : cityList) {

cityComboBox.addItem(s);

}

}

}

});

cityComboBox.addItemListener(new ItemListener() {

@Override

public void itemStateChanged(ItemEvent e) {

if(e.getStateChange() == ItemEvent.SELECTED){

cityComboBox=(JComboBox) e.getSource();

String name=(String) cityComboBox.getSelectedItem();

countyList=new ArrayList();

int CityIndex = chinaDao.getIdByName(name).getId();

List counties=chinaDao.getCountyObjectsById(CityIndex);

for(County county:counties){

countyList.add(county.getName());

}

countyComboBox.removeAllItems();

for(String cl:countyList){

countyComboBox.addItem(cl);

}

}

}

});

JLabel lblNewLabel = new JLabel(

"\u5168\u56FD\u57CE\u5E02\u4E09\u7EA7\u8054\u52A8");

lblNewLabel.setFont(new Font("宋体", Font.BOLD, 18));

lblNewLabel.setBounds(140, 25, 160, 48);

contentPane.add(provinceComboBox);

contentPane.add(cityComboBox);

contentPane.add(countyComboBox);

contentPane.add(lblNewLabel);

}

}