我有一个结果集作为使用JDBC连接器的MySQL查询的结果。因此,我的工作是将结果集转换为JSON格式。这样我就可以将其作为AJAX响应发送到客户端。有人能解释一下如何将JSON格式转换为JSON格式吗?因为我对Java和JSON都是新手
发布于 2014-12-28 01:50:51
很多人都正确回答了这个问题。但是,我认为我可以用下面的一小段代码为这篇文章增加更多的价值。它使用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);
}
发布于 2013-09-23 21:35:55
如果您正在使用JSON,我推荐Jackson JSON库。
http://wiki.fasterxml.com/JacksonHome
jar文件可以在以下位置找到:
http://wiki.fasterxml.com/JacksonDownload
下面是我用来将任何结果集转换为Map<>或List< Map<>的通用代码,使用JacksonJSON将其转换为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;
}
在servlet中,我使用com.fasterxml.jackson.databind.ObjectMapper将列表转换为JSON,它将Java Generics转换为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);
}
}
}
您可以像这样将参数传递给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 = ?";
}
发布于 2013-09-23 22:16:10
我使用过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();
如果您需要键入JSON key:value对,则可以使用writer.value(String、long、integer等)。二传手。在foreach rsmd循环中执行switch-case,并对编号的sql类型使用适当的setter。默认情况下可以使用writer.value(rs.getString(idx))设置器。
使用JsonWriter可以有效地编写大型json回复CPU+RAM。您不需要先循环sqlresultset,然后在RAM中创建大量列表。然后在编写json文档时再次循环列表。在这个示例中,http回复被分块,而其余的数据仍被写入servlet输出。
围绕GSON+Sql结果集创建更高级的包装器实用程序相对容易。jsp页面可以使用SqlIterator(sqlquery)方法(.next(),getColumnCount(),getType(idx),.getString(idx),.getLong(idx) ...)同时编写http回复。它在没有中间列表的情况下循环原始sql。这对于较小的应用程序并不重要,但大量使用的应用程序必须更仔细地考虑cpu+ram的使用模式。或者更好的做法是使用SqlToJson(httpresponse,sqlrs)帮助程序,而jsp或servlet代码噪音最小。
https://stackoverflow.com/questions/18960446
复制相似问题