Java 使用数据库中的数据创建 JSON 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20884455/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creation of JSON Array with data from database
提问by SteeleDev
I have values for a particular field in my database rel_act stored as 1234,5678,9112 I read these values through a map array 'rslt' so when I read them as
我的数据库 rel_act 中的特定字段的值存储为 1234,5678,9112 我通过映射数组“rslt”读取这些值，因此当我将它们读取为
I wish to create a json object in the particular format
我希望以特定格式创建一个 json 对象
"rel_act": [{"ref":"1234"},{"ref":"5678"},{"ref":"9112"}]
"rel_act": [{"ref":"1234"},{"ref":"5678"},{"ref":"9112"}]
Please advise
请指教
Thanks
谢谢
采纳答案by Aries
You could use a simple wrapper to your JDBC code: (Note that the SQL2JSON.convertDateToString() method has not been included in this example, delete it or add your own)
您可以对 JDBC 代码使用一个简单的包装器：（请注意，此示例中未包含 SQL2JSON.convertDateToString() 方法，请删除它或添加您自己的方法）
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
/**
* Creates a JSONARRAY from an java.sql.ResultSet.
* @author Aries
*
*/
public class SQL2JSON
{
public static JSONArray convert(ResultSet rs) throws SQLException, JSONException
{
JSONArray json = new JSONArray();
ResultSetMetaData rsmd = rs.getMetaData();
rs.beforeFirst();
while(rs.next()) {
int numColumns = rsmd.getColumnCount();
JSONObject obj = new JSONObject();
for(int i=1; i<numColumns+1; i++) {
String column_name = rsmd.getColumnLabel(i); //Bugfix , works better than getColumnName() /Aries
switch( rsmd.getColumnType( i ) ) {
case java.sql.Types.ARRAY:
obj.put(column_name, rs.getArray(column_name)); break;
case java.sql.Types.BIGINT:
obj.put(column_name, rs.getInt(column_name)); break;
case java.sql.Types.BOOLEAN:
obj.put(column_name, rs.getBoolean(column_name)); break;
case java.sql.Types.BLOB:
obj.put(column_name, rs.getBlob(column_name)); break;
case java.sql.Types.DOUBLE:
obj.put(column_name, rs.getDouble(column_name)); break;
case java.sql.Types.FLOAT:
obj.put(column_name, rs.getFloat(column_name)); break;
case java.sql.Types.INTEGER:
obj.put(column_name, rs.getInt(column_name)); break;
case java.sql.Types.NVARCHAR:
obj.put(column_name, rs.getNString(column_name)); break;
case java.sql.Types.VARCHAR:
obj.put(column_name, rs.getString(column_name)); break;
case java.sql.Types.TINYINT:
obj.put(column_name, rs.getInt(column_name)); break;
case java.sql.Types.SMALLINT:
obj.put(column_name, rs.getInt(column_name)); break;
case java.sql.Types.DATE:
obj.put(column_name, SQL2JSON.convertDateToString(rs.getDate(column_name))); break;
case java.sql.Types.TIMESTAMP:
obj.put(column_name, SQL2JSON.convertDateToString(rs.getTimestamp(column_name))); break;
default:
obj.put(column_name, rs.getObject(column_name)); break;
}
}
json.put(obj);
}
return json;
}
}
Here is an example how it is called. I am using a custom MySQL transactionpool and JDBC SQL handle object, but it should be modifiable to any JDBC driver object (MySQL, Sybase, H2, HSQLDB, Oracle, ...)
这是一个如何调用它的示例。我正在使用自定义 MySQL 事务池和 JDBC SQL 句柄对象，但它应该可以修改为任何 JDBC 驱动程序对象（MySQL、Sybase、H2、HSQLDB、Oracle 等）
import java.sql.ResultSet;
import java.sql.SQLException;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
public synchronized JSONObject getCustomerReferences(int custID) {
DBTransaction trans=null;
TransactionPool transactionPool = null;
JSONArray myArrayOfRefs= new JSONArray();
JSONObject myObject = new JSONObject();
try {
transactionPool = TransactionPool.get();
trans = transactionPool.beginWork();
Map<String, Object> optionalParams = new HashMap<String, Object>();
optionalParams.put("customerId", custID);
String selectQuery ="SELECT orderId AS ref FROM table1 WHERE custId='[customerId]';" //with 'AS' you can customize your output to any JSON name
JSONArray myArrayOfRefs = null;
ResultSet rs = mySQLTransactionHandle.query(selectQuery, optionalParams);
if(rs.next()) {
myArrayOfRefs = SQL2JSON.convert(rs);
}
//add JSON array to a JSON object
myObject.put("rel_act", myArrayOfRefs); //Add null check.
} catch (Exception e) {
e.printStackTrace(); //Add logging
}
finally
{
try {
transactionPool.commit(mySQLTransactionHandle);
} catch (SQLException e1) {
e1.printStackTrace(); //Add logging
}
}
return myObject;
}
回答by Alessio
In a recent project I used Google gson, you can create a custom serializer in order to create the format you want from an object.
在我最近使用 Google gson 的一个项目中，您可以创建自定义序列化程序，以便从对象创建您想要的格式。
https://sites.google.com/site/gson/gson-user-guide#TOC-Custom-Serialization-and-Deserialization
https://sites.google.com/site/gson/gson-user-guide#TOC-Custom-Serialization-and-Deserialization
Example:
例子：
GsonBuilder gb = new GsonBuilder();
gb.registerTypeAdapter(YourClass.class, new CustomSerializer());
Gson gson = gb.create();
JsonElement json = gson.toJsonTree(yourObjectInstance);
And for the CustomSerializer:
对于 CustomSerializer：
public class CustomSerializer implements JsonSerializer<Object> {
public JsonElement serialize(Object object, Type typeOfSrc, JsonSerializationContext context) {
JsonObject jo = new JsonObject();
//create your custom json here
return jo;
}
}
回答by chiccodoro
First advice: Do not hand-write your JSON serialization code. Use a well-proven library such as GSON. This library allows you to generate a JSON string from a dictionary, object, or array with a single line of code.
第一个建议：不要手写您的 JSON 序列化代码。使用经过充分验证的库，例如GSON。该库允许您使用一行代码从字典、对象或数组生成 JSON 字符串。
On a side note you may want to make your code and JSON more readable. For example, if your only values per array item are the numbers from your example, you don't need key-value pairs. You can simplify your JSON to "rel_act": [ "1234", "5678", "9112" ]. Furthermore instead of rel_actyou can use a more readable name following the camelCase convention - the advantage is that your Java object from which you serialize the JSON will have a typical Java Name, and also any JavaScript deserializing the JSON will get a name that follows typical JavaScript naming conventions.
附带说明一下，您可能希望使您的代码和 JSON 更具可读性。例如，如果每个数组项的唯一值是示例中的数字，则不需要键值对。您可以将 JSON 简化为"rel_act": [ "1234", "5678", "9112" ]. 此外，rel_act您可以使用遵循驼峰命名规则的更易读的名称 - 优点是您从中序列化 JSON 的 Java 对象将具有典型的 Java 名称，并且任何反序列化 JSON 的 JavaScript 都将获得遵循典型 JavaScript 的名称命名约定。
回答by Jamel ESSOUSSI
Use:
用：
JSONArray array = new JSONArray("\"rel_act\": [ \"1234\", \"5678\", \"9112\" ]");
