如何将 Java 结果集转换为 JSON？

Question

提问by Pramod

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

作为使用 JDBC 连接器的 MySQL 查询的结果，我有一个结果集。所以我的工作是将结果集转换为 JSON 格式。这样我就可以将它作为 AJAX 响应发送到客户端。有人可以解释如何转换为 JSON 格式，因为我是 Java 和 JSON 的新手

Answer 1

回答by Mike Thomsen

Use jsonlibfor Java. Iterate over the result set and add the properties you want as JSONObjectobjects from jsonlib.

将jsonlib用于 Java。迭代结果集并JSONObject从 jsonlib添加您想要的属性作为对象。

Answer 2

回答by Nathaniel Johnson

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

如果您使用 JSON，我推荐 Hymanson JSON 库。

http://wiki.fasterxml.com/HymansonHome

The jar files can be found here:

jar 文件可以在这里找到：

http://wiki.fasterxml.com/HymansonDownload

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

这是我用来将任何结果集转换为 Map<> 或 List< Map<> > 使用 HymansonJSON 将其转换为 JSON 的通用代码（见下文）。

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;
}

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

在 servlet 中，我使用 com.fasterxml.Hymanson.databind.ObjectMapper 将列表转换为 JSON，它将 Java 泛型转换为 JSON 字符串。

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

You can pass in Parameters to the PreparedStatement like this:

您可以像这样将参数传递给 PreparedStatement：

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 = ?";

}

Answer 3

回答by RokL

Convert resultset into List<Map<String, Object>>(each map contains a row with column names as keys and column content as value, List is a list of such rows)
Use Gson or Hymanson library to covert this object into JSON.

将结果集转换为List<Map<String, Object>>（每个映射包含一行，以列名为键，以列内容为值，List 是这些行的列表）
使用 Gson 或 Hymanson 库将此对象转换为 JSON。

Answer 4

回答by Whome

I have used Google GSON library, its one tiny gson-2.2.4.jar 190KB library in a mywebapp/WEB-INF/lib folder. http://code.google.com/p/google-gson/

我使用过 Google GSON 库，它是一个位于 mywebapp/WEB-INF/lib 文件夹中的微小 gson-2.2.4.jar 190KB 库。 http://code.google.com/p/google-gson/

import com.google.gson.stream.JsonWriter;
---
httpres.setContentType("application/json; charset=UTF-8");
httpres.setCharacterEncoding("UTF-8");
JsonWriter writer = new JsonWriter(new OutputStreamWriter(httpres.getOutputStream(), "UTF-8"));
while(rs.next()) {
   writer.beginObject();
   // loop rs.getResultSetMetadata columns
   for(int idx=1; idx<=rsmd.getColumnCount(); idx++) {
     writer.name(rsmd.getColumnLabel(idx)); // write key:value pairs
     writer.value(rs.getString(idx));
   }
   writer.endObject();
}
writer.close();
httpres.getOutputStream().flush();

If you want typed JSON key:value pairs there is a writer.value(String,long,integer,etc..) setters. Do switch-case within foreach rsmd loop and use appropriate setter for numbered sql types. Default could use writer.value(rs.getString(idx)) setter.

如果你想要输入的 JSON 键：值对，有一个 writer.value(String,long,integer,etc..) setter。在 foreach rsmd 循环中执行 switch-case 并为编号的 sql 类型使用适当的 setter。默认可以使用 writer.value(rs.getString(idx)) setter。

Using JsonWriter allows writing large json replys CPU+RAM effectively. You dont need to loop sqlresultset first and create massive List in RAM. Then loop list again while writing json document. This example flows as it goes, http reply is being chunked while remaining data is still written to servlet output.

使用 JsonWriter 可以有效地编写大型 json 回复 CPU+RAM。您不需要先循环 sqlresultset 并在 RAM 中创建大量列表。然后在编写json文档时再次循环列表。这个例子是流动的，http 回复被分块，而剩余的数据仍然写入 servlet 输出。

Its relatively easy to create higher-level wrapper utils around GSON+Sql resultset. jsp page could use SqlIterator(sqlquery) methods (.next(), getColumnCount(), getType(idx), .getString(idx), .getLong(idx) ...) while writing http reply. It loops the original sql without intermediate List. This does not matter for smaller apps but heavy-use apps must consider cpu+ram usage patterns more closely. Or even better do SqlToJson(httpresponse, sqlrs) helper then jsp or servlet code noice is minimal.

围绕 GSON+Sql 结果集创建更高级别的包装器工具相对容易。在编写 http 回复时，jsp 页面可以使用 SqlIterator(sqlquery) 方法（.next()、getColumnCount()、getType(idx)、.getString(idx)、.getLong(idx) ...）。它在没有中间列表的情况下循环原始 sql。这对于较小的应用程序无关紧要，但大量使用的应用程序必须更密切地考虑 cpu+ram 的使用模式。或者甚至更好地做 SqlToJson(httpresponse, sqlrs) helper 然后 jsp 或 servlet 代码 noice 是最小的。

Answer 5

回答by Neil McGuigan

It's pretty easy if you want to use Spring:

如果你想使用 Spring，这很容易：

@RestController
public class MyController

  @Autowired
  private JdbcTemplate jdbcTemplate;

  @RequestMapping("/")
  List<Map<String,Object>> getAll() {
    return jdbcTemplate.queryForList("select * from my_table");
  }
}

In mvc-dispatcher-servlet.xml, you'd setup your JdbcTemplate like this:

在 mvc-dispatcher-servlet.xml 中，您可以像这样设置 JdbcTemplate：

<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
  <property name="dataSource">
    ...data source config...
  </property>
</bean>

Hymanson should be in your classpath (ie a Maven dependency).

Hymanson 应该在您的类路径中（即 Maven 依赖项）。

Answer 6

回答by phoenix

Many people have answered the question correctly. But, I think i can add more value to the post with the following small snippet of code. It uses the Apache-DBUtilsand the Gsonlibrary.

许多人已经正确回答了这个问题。但是，我认为我可以使用以下一小段代码为帖子添加更多价值。它使用Apache-DBUtils和Gson库。

public static String resultSetToJson(Connection connection, String query) {
        List<Map<String, Object>> listOfMaps = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            listOfMaps = queryRunner.query(connection, query, new MapListHandler());
        } catch (SQLException se) {
            throw new RuntimeException("Couldn't query the database.", se);
        } finally {
            DbUtils.closeQuietly(connection);
        }
        return new Gson().toJson(listOfMaps);
    }

Answer 7

回答by coderz

You can use any JSON library.

您可以使用任何 JSON 库。

The following is an implementation of this, return a list, with each element a JSON Object:

下面是这个的实现，返回一个列表，每个元素都有一个 JSON 对象：

/*
 * Convert ResultSet to a common JSON Object array
 * Result is like: [{"ID":"1","NAME":"Tom","AGE":"24"}, {"ID":"2","NAME":"Bob","AGE":"26"}, ...]
 */
public static List<JSONObject> getFormattedResult(ResultSet rs) {
    List<JSONObject> resList = new ArrayList<JSONObject>();
    try {
        // get column names
        ResultSetMetaData rsMeta = rs.getMetaData();
        int columnCnt = rsMeta.getColumnCount();
        List<String> columnNames = new ArrayList<String>();
        for(int i=1;i<=columnCnt;i++) {
            columnNames.add(rsMeta.getColumnName(i).toUpperCase());
        }

        while(rs.next()) { // convert each object to an human readable JSON object
            JSONObject obj = new JSONObject();
            for(int i=1;i<=columnCnt;i++) {
                String key = columnNames.get(i - 1);
                String value = rs.getString(i);
                obj.put(key, value);
            }
            resList.add(obj);
        }
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return resList;
}

Answer 8

回答by U.Eberle

Within my applications (MySQL/java servlet/javascript on browser) I use a string function with the fast stringbuilder methods and a generic rs.getObject(). I think it is the most elegant way to do the job:

在我的应用程序（浏览器上的 MySQL/java servlet/javascript）中，我使用带有快速 stringbuilder 方法和通用 rs.getObject() 的字符串函数。我认为这是完成这项工作的最优雅的方式：

public  String rStoJason(ResultSet rs) throws SQLException 
{
  if(rs.first() == false) {return "[]";} else {rs.beforeFirst();} // empty rs
  StringBuilder sb=new StringBuilder();
  Object item; String value;
  java.sql.ResultSetMetaData rsmd = rs.getMetaData();
  int numColumns = rsmd.getColumnCount();

  sb.append("[{");
  while (rs.next()) {

    for (int i = 1; i < numColumns + 1; i++) {
        String column_name = rsmd.getColumnName(i);
        item=rs.getObject(i);
        if (item !=null )
           {value = item.toString(); value=value.replace('"', '\'');}
        else 
           {value = "null";}
        sb.append("\"" + column_name+ "\":\"" + value +"\",");

    }                                   //end For = end record

    sb.setCharAt(sb.length()-1, '}');   //replace last comma with curly bracket
    sb.append(",{");
 }                                      // end While = end resultset

 sb.delete(sb.length()-3, sb.length()); //delete last two chars
 sb.append("}]");

 return sb.toString();
}

Answer 9

回答by Balkrushna Patil

I found best solution here.

我在这里找到了最好的解决方案。

import org.json.JSONArray;
import org.json.JSONObject;
import java.sql.ResultSet;

/**
 * Convert a result set into a JSON Array
 * @param resultSet
 * @return a JSONArray
 * @throws Exception
 */
public static JSONArray convertToJSON(ResultSet resultSet)
        throws Exception {
    JSONArray jsonArray = new JSONArray();
    while (resultSet.next()) {
        int total_rows = resultSet.getMetaData().getColumnCount();
        for (int i = 0; i < total_rows; i++) {
            JSONObject obj = new JSONObject();
            obj.put(resultSet.getMetaData().getColumnLabel(i + 1)
                    .toLowerCase(), resultSet.getObject(i + 1));
            jsonArray.put(obj);
        }
    }
    return jsonArray;
}

Answer 10

回答by Yan Pak

If you utilizing the Spring' JDBCTemplate for executing stored functions which returns cursor as list of the tables entries and, in turns, you wish to map it as a list of the specified bean, then there is the most neat solution:

如果您使用 Spring 的 JDBCTemplate 来执行存储函数，该函数将游标作为表条目列表返回，并且反过来，您希望将其映射为指定 bean 的列表，那么有一个最简洁的解决方案：

import com.fasterxml.Hymanson.databind.ObjectMapper;

...

final static ObjectMapper mapper = new ObjectMapper();

...

<T> List<T> populateExecuteRetrieve(SimpleJdbcCall call, Map inputParameters, Class<T> outputClass) {
    List<?> sqlResult;
    sqlResult = call.executeFunction(ArrayList.class, parameter);
    return sqlResult
            .stream()
            .map(entry -> mapper.convertValue(entry, outputBeanClass))
            .collect(Collectors.toList());
}

You are welcome!

不客气！

Happy coding!

快乐编码！

如何将 Java 结果集转换为 JSON？

提问by Pramod

回答by Mike Thomsen

回答by Nathaniel Johnson

回答by RokL

回答by Whome

回答by Neil McGuigan

回答by phoenix

回答by coderz

回答by U.Eberle

回答by Balkrushna Patil

回答by Yan Pak

相关推荐

最近更新

标签

如何将 Java 结果集转换为 JSON？

提问by Pramod

回答by Mike Thomsen

回答by Nathaniel Johnson

回答by RokL

回答by Whome

回答by Neil McGuigan

回答by phoenix

回答by coderz

回答by U.Eberle

回答by Balkrushna Patil

回答by Yan Pak

相关推荐

Java 如何在片段Android中获取PackageManager

如何在Java中将字符串转换为十六进制

Java 无法实例化类型集

java.lang.ClassLoader.loadClass 中的错误（来源不明）

相关推荐

最近更新

标签