将MySQL中数据转为层级Json格式

·  阅读 139

本文已参与“新人创作礼”活动,一起开启掘金创作之路。​ ​

  目前需要将mysql中的论文数据转为json格式,但是其中合作作者一列中多个人民通过封号隔开,最后转化样式如下:

{
"1681005884": {
		"id": "1681005884",
		"title": "Cerebellar neural network feedforward and inverse compensation-fuzzy PID control for giant magnetostrictive actuator",
		"time": "2015-03-01",
		"publication": "Optical Precision Engineering",
		"department": "24",
		"authors": [{
			"name": "MengAiHua"
		}, {
			"name": "LiuChengLong"
		}, {
			"name": "ChenWenYi"
		}, {
			"name": "YangJianFeng"
		}, {
			"name": "LiMingFan"
		}],
		"level": "EI",
		"type": "Journal articles"
	}
}
复制代码

一、加载包

在pom.xml中加载相关jar包:

        <dependency>
            <groupId>net.sf.json-lib</groupId>
            <artifactId>json-lib</artifactId>
            <version>2.4</version>
            <classifier>jdk15</classifier>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.35</version>
        </dependency>
复制代码

 二、获取数据库的数据

DBHelper类

public class DBHelper {
    static {
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接的方法
     * @return
     */
    public Connection getConnection() throws SQLException {
        Connection con = null;
        //创建连接
        con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/name_disambiguation", "root", "0425");
        return con;
    }

    /**
     * 查询
     * @param sql 要执行的查询语句
     * @return
     */
    public List<PaperFin>  findCMEnAll (String sql){
        List<PaperFin> list = new ArrayList<PaperFin>();

        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try{
            //获取连接
            con=this.getConnection();
            //预编译查询语句
            pstmt = con.prepareStatement(sql);
            //执行预编译语句并获取结果集
            rs=pstmt.executeQuery();

            while(rs.next()){
                list.add(new PaperFin(rs.getString("id"),
                        rs.getString("title"),
                        rs.getString("firstAuthor"),
                        rs.getString("time"),
                        rs.getString("publications"),
                        rs.getString("company"),
                        rs.getString("allAuthor"),
                        rs.getString("category"),
                        rs.getString("type")));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally{
            //关闭资源
            this.closeAll(con, pstmt, rs);
        }

        return list;
    }
}
复制代码

Paper_to_Json类

    //得到数据库中的论文信息
    public static List<PaperFin> getCMEnPaper(){
        List<PaperFin> papers = new ArrayList<PaperFin>();

        DBHelper db = new DBHelper();
        String sql = "select * from paper;";
        papers = db.findCMEnAll(sql);
        return papers;
    }
复制代码

 三、实体类

PaperFin类(用于获取数据库中的数据):

public class PaperFin {
    private String id;              //论文的id
    private String name;            //论文题目
    private String firstAuthor;     //第一作者
    private String time;            //发表时间
    private String publications;    //发表刊物
    private String company;         //作者单位
    private String allAuthor;       //所有作者
    private String category;        //所有类别
    private String type;            //刊物级别

    ………………
}
复制代码

Paper2Json类(转为json时调用类):

public class Paper2Json {
    private String id;              //论文的id
    private String name;            //论文题目
    private String firstAuthor;     //第一作者
    private String time;            //发表时间
    private String publications;    //发表刊物
    private String company;         //作者单位
    private List<String> allAuthor;       //所有作者
    private String category;        //所有类别
    private String type;            //刊物级别

    …………
}
复制代码

 四、按照json格式实现转化类

JsonUtil类( 这里提供两种格式——JSONObjectJSONArray

public class JsonUtil {
    /**
     * 把List集合转换成JsonArray数组
     *
     * @return
     * @throws Exception
     */
    public static JSONArray formatListToJsonArray(List<Paper2Json> pj) throws Exception {
        int num = pj.size();    // 得到行的总数
        JSONArray array = new JSONArray();// json数组,根据下标找值;[{name1:wp},{name2:{name3:'ww'}}]name为key值,wp为value值

        for (int i = 0; i < num; i++) {
            JSONObject mapOfColValues = new JSONObject();   // 创建json对象就是一个{论文id:{论文信息}}
            JSONObject paperValues = new JSONObject();  //论文的信息json对象
            List<JSONObject> paperAuthors = new ArrayList<JSONObject>();  //论文的合作作者集合
            List<String> authors = pj.get(i).getAllAuthor();

            paperValues.put("id", pj.get(i).getId());    // 添加键值对,比如说{id:120812}
            paperValues.put("title", pj.get(i).getName());
            paperValues.put("time", pj.get(i).getTime());
            paperValues.put("publication", pj.get(i).getPublications());
            paperValues.put("department", pj.get(i).getCompany());
            for (int j = 0; j < authors.size(); j++) {
                JSONObject person = new JSONObject();
                person.put("name", authors.get(j));
                paperAuthors.add(person);
            }
            paperValues.put("authors", paperAuthors);
            paperValues.put("level", pj.get(i).getType());  //论文的期刊等级
            paperValues.put("type", pj.get(i).getCategory());  //论文的类别

            mapOfColValues.put(pj.get(i).getId(), paperValues);
//            System.out.println(mapOfColValues.toString());
            array.add(mapOfColValues);
        }
        return array;
    }

    /**
     * 把List集合转换成JSONObject数组
     *
     * @return
     * @throws Exception
     */
    public static JSONObject formatListToJsonObjec(List<Paper2Json> pj) throws Exception {
        int num = pj.size();    // 得到行的总数

        JSONObject object = new JSONObject();   //将String转换为JSONArray格式
        for (int i = 0; i < num; i++) {
            JSONObject mapOfColValues = new JSONObject();   // 创建json对象就是一个{论文id:{论文信息}}
            JSONObject paperValues = new JSONObject();  //论文的信息json对象
            List<JSONObject> paperAuthors = new ArrayList<JSONObject>();  //论文的合作作者集合
            List<String> authors = pj.get(i).getAllAuthor();

            paperValues.put("id", pj.get(i).getId());    // 添加键值对,比如说{id:120812}
            paperValues.put("title", pj.get(i).getName());
            paperValues.put("time", pj.get(i).getTime());
            paperValues.put("publication", pj.get(i).getPublications());
            paperValues.put("department", pj.get(i).getCompany());
            for (int j = 0; j < authors.size(); j++) {
                JSONObject person = new JSONObject();
                person.put("name", authors.get(j));
                paperAuthors.add(person);
            }
            paperValues.put("authors", paperAuthors);
            paperValues.put("level", pj.get(i).getType());  //论文的期刊等级
            paperValues.put("type", pj.get(i).getCategory());  //论文的类别

            object.put(pj.get(i).getId(), paperValues);
        }
        return object;
    }
}
复制代码

五、主函数

Paper_to_Json类

public class Paper_to_Json {
    public static void main(String[] args) throws Exception {
//        List<PaperFin> papers = getCMPaper();  //得到所有带有中文姓名的重名的数据
        List<PaperFin> papers = getCMEnPaper();  //得到所有重名英文的数据
        List<Paper2Json> paper = new ArrayList<>();     //得到所有作者切分后的数据

        System.out.println("转化前:" + papers.get(1));
        for (PaperFin p: papers) {

            List<String> allAuthor = new ArrayList<>();
            String aus = p.getAllAuthor();
            String [] a = aus.split(";");
            for (int i = 0; i < a.length; i++) {
                allAuthor.add( a[i] );
            }
            //新建转化为json的实体类
            Paper2Json pj = new Paper2Json(p.getId(),p.getName(),p.getFirstAuthor(),p.getTime(),
                    p.getPublications(),p.getCompany(),allAuthor,p.getCategory(),p.getType());
            paper.add(pj);
        }
        System.out.println("转化后:" + paper.get(1));

        JsonUtil js = new JsonUtil();
//        JSONArray ja =  js.formatListToJsonArray(paper);
//        System.out.println("Json转化后:" + ja);

        JSONObject jo = js.formatListToJsonObjec(paper);
        System.out.println("Json转化后:" + jo);
    }
}
复制代码

将最后输出的json通过在线解析即可得到最后要求的结果:

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改