如何转换成一个Java的ResultSet成JSON？ [英] How to convert a Java resultset into JSON?

问题描述

我有一个结果作为使用JDBC连接一个MySQL查询的结果。所以，我的工作是把结果集转换成JSON格式。所以，我可以将它发送到客户方作为Ajax响应。有些人能够解释如何做转换成JSON格式，因为我是新来的Java和以及JSON的概念

I have a resultset as a result of a MySQL query using the JDBC connector. So my job is to convert the resultset into a JSON format. So that I can send it to the clientside as a AJAX response. Can some one explain how to do the conversion to JSON format as I am new to both Java and as well as the concept of JSON

推荐答案

如果您使用的是JSON我推荐的杰克逊JSON库。

If you are using JSON I recommend the Jackson JSON library.

http://wiki.fasterxml.com/JacksonHome

JAR文件可以在这里找到：

The jar files can be found here:

http://wiki.fasterxml.com/JacksonDownload

下面是一般的code我用转换任何结果集为一个地图＆LT;>或者List＆LT;地图＆LT;>>使用JacksonJSON是pretty的直线前进（见下文）转换为JSON

Here is the generic code I use to convert any result set into a Map<> or List< Map<> > Converting this to JSON using JacksonJSON is pretty straight forward (See Below).

package com.naj.tmoi.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class EntityFactory {

    public EntityFactory(Connection connection, String queryString) {
        this.queryString = queryString;
        this.connection = connection;
    }

    public Map<String, Object> findSingle(Object[] params) throws SQLException {
        List<Map<String, Object>> objects = this.findMultiple(params);

        if (objects.size() != 1) {
            throw new SQLException("Query did not produce one object it produced: " + objects.size() + " objects.");
        }

        Map<String, Object> object = objects.get(0);  //extract only the first item;

        return object;
    }

    public List<Map<String, Object>> findMultiple(Object[] params) throws SQLException {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = this.connection.prepareStatement(this.queryString);
            for (int i = 0; i < params.length; ++i) {
                ps.setObject(1, params[i]);
            }

            rs = ps.executeQuery();
            return getEntitiesFromResultSet(rs);
        } catch (SQLException e) {
            throw (e);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
    }

    protected List<Map<String, Object>> getEntitiesFromResultSet(ResultSet resultSet) throws SQLException {
        ArrayList<Map<String, Object>> entities = new ArrayList<>();
        while (resultSet.next()) {
            entities.add(getEntityFromResultSet(resultSet));
        }
        return entities;
    }

    protected Map<String, Object> getEntityFromResultSet(ResultSet resultSet) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        Map<String, Object> resultsMap = new HashMap<>();
        for (int i = 1; i <= columnCount; ++i) {
            String columnName = metaData.getColumnName(i).toLowerCase();
            Object object = resultSet.getObject(i);
            resultsMap.put(columnName, object);
        }
        return resultsMap;
    }
    private final String queryString;
    protected Connection connection;
}

在Servlet中使用它把Java泛型成JSON字符串的com.fasterxml.jackson.databind.ObjectMapper我转换列表为JSON。

In the servlet I convert the List into JSON using the com.fasterxml.jackson.databind.ObjectMapper which converts Java Generics into a JSON String.

    Connection connection = null;
    try {
        connection = DataSourceSingleton.getConnection();
        EntityFactory nutrientEntityFactory = new EntityFactory(connection, NUTRIENT_QUERY_STRING);
        List<Map<String, Object>> nutrients = nutrientEntityFactory.findMultiple(new Object[]{});

        ObjectMapper mapper = new ObjectMapper();

        String json = mapper.writeValueAsString(nutrients);


        response.setContentType("application/json;charset=UTF-8");
        response.getWriter().write(json);
    } catch (SQLException e) {
        throw new ServletException(e);
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new ServletException(e);
            }
        }
    }

您可以传递参数preparedStatement是这样的：

You can pass in Parameters to the PreparedStatement like this:

String name = request.getHeader("name");
EntityFactory entityFactory = new EntityFactory(DataSourceSingleton.getConnection(), QUERY_STRING);
Map<String, Object> object = entityFactory.findSingle(new String[]{name});


private static final String QUERY_STRING = "SELECT NAME, PASSWORD, TOKEN, TOKEN_EXPIRATION FROM USER WHERE NAME = ?";

}

这篇关于如何转换成一个Java的ResultSet成JSON？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！