1. Problem Description

input

• Table Name of an Arbitrary Data Table
• What page is it?
• Number of entries per page

output

JSON array string for this table (each row of data corresponds to a JSON object)

application

When the client (Web or mobile) sends to the server to get the paging table data, the server code reconstruction does not need to write different services according to the different tables (especially when the number of tables is large and the keys are too many to write enumeration). After the client receives the JSON, it can be parsed directly and processed further.

2. The jar package that needs to be imported

The first one is MYSQL's JDBC driver. It should be noted that MYSQL's version is 5.7.x or 8.0.x. Take 5.7.x as an example.

The rest are the jar packages used to encapsulate JSON objects. Six are interdependent and indispensable. Note the version number. Many newer versions are not compatible with each other. Here is a set of version numbers that have been tested and can be used.

• 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

Download address:

• mysql-connector-java: https://dev.mysql.com/downloads/connector/j/
  (Select Operating System: The drop-down box selects Platform Independent)

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

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

• Starting with commons: https://archive.apache.org/dist/commons/

3. Key Technologies

How does JDBC get the number of keys in a table?

    ResultSet rs = stmt.executeQuery("select *from "+tableName);//tableName is the name of the table entered
    ResultSetMetaData resultSetMetaData = rs.getMetaData();
    resultSetMetaData.getColumnCount()//Number of keys

How does JDBC get the key name and key value of a table?

    //i is the first key, the smallest is 1, and the largest is the number of keys in the table.
    resultSetMetaData.getColumnName(i)//Key name
    rs.getString(i)//Key value

How to Page

In the rs.next() loop, every cnt++, according to the number of pages and each page, calculate the qualified cnt.

4. Source code

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

public class Main{
    //Note here that the name of the database, not the table name, can also be dynamically written to the function.
    private static String url = "jdbc:mysql://localhost:3306/db1?useSSL=true&characterEncoding=utf8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
    //Note the version MySQL number, 5.7.x for this driver, 8.0.x for "com.mysql.cj.jdbc"
    private static String jdbc = "com.mysql.jdbc.Driver";
    //Pay attention to modifying user names
    private static String user = "root";
    //Pay attention to changing the password
    private static String password = "123456";
    //Connection with database
    private static Connection conn;
    //Queries with databases
    private static Statement stmt;
    //Query results with database
    private static ResultSet rs;
    /**
     * Converting a table to a JSONArray string
     * @param tableName Table name
     * @param page Page 1
     * @param perPage Number of entries per page
     * @return JSONArray string for this table
     */
    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. Operation effect

Test Data Table

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');

console output

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