基于MySQL的任意表转JSON字符串（含分页）

1.问题描述

输入

• 一个任意数据表的表名
• 第几页
• 每页条数

输出

这个表的JSON数组字符串（每一行数据相当于一个JSON对象）

应用

对客户端（Web或移动端都行）向服务器端发送获取含分页的表格数据时，对服务器代码的重构，不需要根据表格的不同写不同的业务（尤其是表格数很多且键很多时不适合写枚举）。客户端收到后直接解析JSON再进一步处理即可。

2.需要导入的jar包

第一个是MYSQL的JDBC驱动，需要注意的是MYSQL的版本是5.7.x或8.0.x,这里以5.7.x为例。

其余是封装JSON对象所用到的jar包，六个互相依赖，缺一不可，并且注意版本号，很多比较新的版本并不是互相兼容的，这里给出的是经过测试过的可以用的一组版本号。

• mysql-connector-java-5.1.45-bin.jar
• json-lib-2.4-jdk15.jar
• ezmorph-1.0.6.jar
• commons-logging-1.2.jar
• commons-lang-2.3.jar
• commons-collections-3.2.jar
• commons-beanutils-1.9.4.jar

下载地址：

• mysql-connector-java：https://dev.mysql.com/downloads/connector/j/
  （Select Operating System:下拉框选择Platform Independent）

• json-lib：https://sourceforge.net/projects/json-lib/files/json-lib/

• ezmorph：https://sourceforge.net/projects/ezmorph/files/

• commons开头的：https://archive.apache.org/dist/commons/

3.关键技术

JDBC如何获取表的键的个数？

    ResultSet rs = stmt.executeQuery("select *from "+tableName);//tableName为输入的表名
    ResultSetMetaData resultSetMetaData = rs.getMetaData();
    resultSetMetaData.getColumnCount()//键的个数

JDBC如何获取表的键名和键值？

    //i为第i个键，最小为1，最大为表的键的个数，获取方法见上文
    resultSetMetaData.getColumnName(i)//键名
    rs.getString(i)//键值

分页如何做

在rs.next()循环中，每次cnt++，根据第几页和每页条数算一下符合条件的cnt即可。

4.源代码

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import java.sql.*;

public class Main{
    //这里注意修改数据库的名，不是表名，也可以动态写入函数中去。
    private static String url = "jdbc:mysql://localhost:3306/db1?useSSL=true&characterEncoding=utf8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
    //注意版本MySQL号，5.7.x为这个驱动，8.0.x为“com.mysql.cj.jdbc”
    private static String jdbc = "com.mysql.jdbc.Driver";
    //注意修改用户名
    private static String user = "root";
    //注意修改密码
    private static String password = "123456";
    //与数据库的连接
    private static Connection conn;
    //与数据库的查询
    private static Statement stmt;
    //与数据库的查询结果
    private static ResultSet rs;
    /**
     * 将一个表转换为JSONArray字符串
     * @param tableName 表名
     * @param page 第几页（从1开始计算）
     * @param perPage 每页条数
     * @return 这个表的JSONArray字符串
     */
    public static String getDB(String tableName, int page, int perPage){
        if(page<1||perPage<1)
            return "[]";
        int cnt = 1;
        JSONObject jsonObject;
        JSONArray jsonArray = new JSONArray();
        try{
            Class.forName(jdbc);
            conn = DriverManager.getConnection(url,user,password);
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select *from "+tableName);
            ResultSetMetaData resultSetMetaData = rs.getMetaData();
            while(rs.next())
            {
                if(cnt>(page-1)*perPage&&cnt<=page*perPage){
                    jsonObject = new JSONObject();
                    for(int i=1;i<=resultSetMetaData.getColumnCount();i++){
                        jsonObject.put(resultSetMetaData.getColumnName(i),rs.getString(i));
                    }
                    jsonArray.add(jsonObject);
                }
                cnt++;
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch(SQLException |ClassNotFoundException e){
            e.printStackTrace();
        }
        return jsonArray.toString();
    }
    public static void main(String[] args) {
        System.out.println(getDB("user",1,2));
    }
}

5.运行效果

测试数据表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `pwd` varchar(255) NOT NULL,
  `st_id` varchar(255) NOT NULL,
  `sex` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'zhangsan', '333', '20190001', 'girl');
INSERT INTO `user` VALUES ('8', 'lisi', '444', '20190002', 'girl');

控制台输出

[{“id”:“1”,“username”:“zhangsan”,“pwd”:“333”,“st_id”:“20190001”,“sex”:“girl”},{“id”:“8”,“username”:“lisi”,“pwd”:“444”,“st_id”:“20190002”,“sex”:“girl”}]