Java读取数据库并返回省市区三级联动的Json数据

本文主要介绍如何从数据库中取出省市区的三级联动数据，并合并转换为Json。

一、Json数据的效果

我们最终返回的Json数据格式的效果是这样的：（部分）

[
    {
        "province": "湖北省",
        "provinceid": 0,
        "cities": [
            {
                "city": "武汉市",
                "areas": [
                    {
                        "area": "东西湖区",
                        "areaid": "420112"
                    },
                    {
                        "area": "汉南区",
                        "areaid": "420113"
                    },
                    {
                        "area": "蔡甸区",
                        "areaid": "420114"
                    },
                    {
                        "area": "江夏区",
                        "areaid": "420115"
                    },
                    {
                        "area": "黄陂区",
                        "areaid": "420116"
                    },
                    {
                        "area": "新洲区",
                        "areaid": "420117"
                    }

二、在Mysql中新建省市区三个表的数据（如有可忽略）

这是我在Mysql中添加的一些测试数据，如果你已经有这些数据了，请自动忽略。

省的表数据

市的表数据：

区的表数据：

关于省市区的表数据的SQL源码，请查看下面的博客：

mysql省市区三级联动数据库的源码

三、引入maven依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.62</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.39</version>
</dependency>

四、新建省市区的实体类

get和set方法省略了，请自己补上

省：

public class Province {
    private int provinceid;
    private String province;
    private List<City> cities;
}

市：

public class City {
    private String cityid;
    private String city;
    private String provinceid;
    private List<Area> areas;
}

区：

public class Area {
    private String areaid;
    private String area;
    private String cityid;
}

五、查询数据库的工具类

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MysqlUtil {

    private static String DRIVER = "com.mysql.jdbc.Driver";
    private static String URL = "xxxx";//自己的数据库地址
    private static String USERNAME = "xxx";//自己的数据库用户名
    private static String PASSWORD = "xxx";//自己的数据库密码

    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    /**
     * 获取数据库连接
     *
     * @return
     */
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);

    }

    /**
     * 获取省的数据
     * @return
     */
    public List<Province> getProvince() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Province> list = new ArrayList<>();
        try {
            conn = getConnection();
            String sql = "SELECT * FROM provinces";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Province province = new Province();
                province.setProvinceid(rs.getInt("provinceid"));
                province.setProvince(rs.getString("province"));
                list.add(province);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, pstmt, rs);
        }
        return list;
    }

    /**
     * 获取市的数据
     * @return
     */
    public List<City> getCity(String provinceid) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<City> list = new ArrayList<>();
        try {
            conn = getConnection();
            String sql = "SELECT * FROM cities WHERE provinceid = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,provinceid);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                City city = new City();
                city.setCityid(rs.getString("cityid"));
                city.setCity(rs.getString("city"));
                list.add(city);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, pstmt, rs);
        }
        return list;
    }

    /**
     * 获取区的数据
     * @return
     */
    public List<Area> getArea(String cityid) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Area> list = new ArrayList<>();
        try {
            conn = getConnection();
            String sql = "SELECT * FROM areas WHERE cityid = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,cityid);
            rs = pstmt.executeQuery();
            while (rs.next()) {
               Area area = new Area();
               area.setAreaid(rs.getString("areaid"));
               area.setArea(rs.getString("area"));
                list.add(area);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, pstmt, rs);
        }
        return list;
    }

    /**
     * 关闭数据库连接
     *
     * @param conn
     */
    private static void closeConnection(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

六、主类

以下是我运行的测试类，可以实现生成三级联动的Json数据

 public static void main(String[] args) {
        MysqlUtil mysqlUtil = new MysqlUtil();
        //获取省的数据
        List<Province> provinces = mysqlUtil.getProvince();
        List<Province> provinceList = new ArrayList<>();
        for (Province province : provinces) {
            Province province1 = new Province();
            province1.setProvince(province.getProvince());

            //获取市的数据
            List<City> cities = mysqlUtil.getCity(String.valueOf(province.getProvinceid()));
            List<City> cityList = new ArrayList<>();
            for (City city : cities) {
                City city1 = new City();
                city1.setCity(city.getCity());

                //获取区的数据
                List<Area> areas = mysqlUtil.getArea(city.getCityid());
                city1.setAreas(areas);
                cityList.add(city1);
            }
            province1.setCities(cityList);
            provinceList.add(province1);
        }
        String jsonString = JSON.toJSONString(provinceList);
        System.out.println(jsonString);
    }

七、运行效果

解析后的Json数据

八、更多

更多技术文章，欢迎关注微信公众号