Java ResultSet 到 JSON 的最有效转换？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6514876/
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
Most efficient conversion of ResultSet to JSON?
提问by Devin Dixon
The following code converts a ResultSetto a JSON string using JSONArrayand JSONObject.
以下代码ResultSet使用JSONArray和将 a 转换为 JSON 字符串JSONObject。
import org.json.JSONArray;
import org.json.JSONObject;
import org.json.JSONException;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class ResultSetConverter {
public static JSONArray convert( ResultSet rs )
throws SQLException, JSONException
{
JSONArray json = new JSONArray();
ResultSetMetaData rsmd = rs.getMetaData();
while(rs.next()) {
int numColumns = rsmd.getColumnCount();
JSONObject obj = new JSONObject();
for (int i=1; i<numColumns+1; i++) {
String column_name = rsmd.getColumnName(i);
if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){
obj.put(column_name, rs.getArray(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.BIGINT){
obj.put(column_name, rs.getInt(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){
obj.put(column_name, rs.getBoolean(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.BLOB){
obj.put(column_name, rs.getBlob(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.DOUBLE){
obj.put(column_name, rs.getDouble(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.FLOAT){
obj.put(column_name, rs.getFloat(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.INTEGER){
obj.put(column_name, rs.getInt(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){
obj.put(column_name, rs.getNString(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.VARCHAR){
obj.put(column_name, rs.getString(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.TINYINT){
obj.put(column_name, rs.getInt(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.SMALLINT){
obj.put(column_name, rs.getInt(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.DATE){
obj.put(column_name, rs.getDate(column_name));
}
else if(rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){
obj.put(column_name, rs.getTimestamp(column_name));
}
else{
obj.put(column_name, rs.getObject(column_name));
}
}
json.put(obj);
}
return json;
}
}
- Is there a faster way?
- Is there a way that uses less memory?
- 有没有更快的方法？
- 有没有一种方法可以使用更少的内存？
采纳答案by Andrew White
The JIT Compiler is probably going to make this pretty fast since it's just branches and basic tests. You could probably make it more elegant with a HashMap lookup to a callback but I doubt it would be any faster. As to memory, this is pretty slim as is.
JIT Compiler 可能会非常快，因为它只是分支和基本测试。您可能可以通过对回调的 HashMap 查找使其更优雅，但我怀疑它会更快。至于内存，这是非常苗条的。
Somehow I doubt this code is actually a critical bottle neck for memory or performance. Do you have any real reason to try to optimize it?
不知何故，我怀疑这段代码实际上是内存或性能的关键瓶颈。您有任何真正的理由尝试优化它吗？
回答by oravecz
Two things that will make this faster are:
有两件事可以加快速度：
Move your call to rsmd.getColumnCount()out of the while loop. The column count should not vary across rows.
将您的呼叫rsmd.getColumnCount()移出 while 循环。列数不应因行而异。
For each column type, you end up calling something like this:
对于每种列类型，您最终会调用如下内容：
obj.put(column_name, rs.getInt(column_name));
It will be slightly faster to use the column index to retrieve the column value:
使用列索引检索列值会稍微快一点：
obj.put(column_name, rs.getInt(i));
回答by Plap
I think there's a way to use less memory (a fixed and not linear amount depending on data cardinality) but this imply to change the method signature. In fact we may print the Json data directly on an output stream as soon as we fetch them from the ResultSet: the already written data will be garbage collected since we don't need an array that keeps them in memory.
我认为有一种方法可以使用更少的内存（固定的而非线性的数量，取决于数据基数），但这意味着要更改方法签名。事实上，一旦我们从 ResultSet 中获取 Json 数据，我们就可以直接在输出流上打印它们：已经写入的数据将被垃圾收集，因为我们不需要将它们保存在内存中的数组。
I use GSON that accepts type adapters. I wrote a type adapter to convert ResultSet to JsonArray and it looks very like to your code. I'm waiting the "Gson 2.1: Targeted Dec 31, 2011" release which will have the "Support for user-defined streaming type adapters". Then I'll modify my adapter to be a streaming adapter.
我使用接受类型适配器的 GSON。我写了一个类型适配器来将 ResultSet 转换为 JsonArray，它看起来很像你的代码。我正在等待“Gson 2.1：目标于 2011 年 12 月 31 日”发布，它将具有“对用户定义的流类型适配器的支持”。然后我将我的适配器修改为流适配器。
Update
更新
As promised I'm back but not with Gson, instead with Hymanson 2. Sorry to be late (of 2 years).
正如承诺的那样，我回来了，但不是和 Gson 在一起，而是和 Hymanson 2 在一起。抱歉迟到了（2 年）。
Preface:The key to use less memory of the result itsef is in the "server side" cursor. With this kind of cursors (a.k.a. resultset to Java devs) the DBMS sends data incrementally to client (a.k.a. driver) as the client goes forward with the reading. I think Oracle cursor are server side by default. For MySQL > 5.0.2 look for useCursorFetch at connection url paramenter. Check about your favourite DBMS.
前言：减少结果本身使用内存的关键在于“服务器端”光标。使用这种游标（又名 Java 开发人员的结果集），随着客户端继续读取，DBMS 将数据增量发送到客户端（又名驱动程序）。我认为 Oracle 游标默认是服务器端。对于 MySQL > 5.0.2 在连接 url 参数中查找 useCursorFetch 。检查您最喜欢的 DBMS。
1:So to use less memory we must:
1：所以要使用更少的内存，我们必须：
- use server side cursor behind the scene
- use resultset open as read onlyand, of course, forward only;
- avoid to load all the cursor in a list (or a
JSONArray) but write each row directly on an output line, where for output line I mean an output stream or a writer or also a json generator that wraps an output stream or a writer.
- 在场景后面使用服务器端光标
- 使用结果集打开为只读，当然，只转发；
- 避免将所有光标加载到列表（或 a
JSONArray）中，而是将每一行直接写在输出行上，对于输出行，我指的是输出流或编写器，或者包装输出流或编写器的 json 生成器。
2:As Hymanson Documentation says:
2：正如Hyman逊文档所说：
Streaming API is best performing (lowest overhead, fastest read/write; other 2 methods build on it)
流 API 性能最佳（开销最低，读/写速度最快；其他 2 种方法基于它构建）
3:I see you in your code use getInt, getBoolean. getFloat... of ResultSet without wasNull. I expect this can yield problems.
3：我看到你在你的代码中使用了 getInt、getBoolean。getFloat... of ResultSet 没有wasNull。我希望这会产生问题。
4:I used arrays to cache thinks and to avoid to call getters each iteration. Although not a fan of the switch/case construct, I used it for that intSQL Types.
4：我使用数组来缓存思考并避免每次迭代都调用getter。虽然不是 switch/case 结构的粉丝，但我将它用于intSQL Types。
The answer:Not yet fully tested, it's based on Hymanson 2.2:
答案：尚未完全测试，它基于Hymanson 2.2：
<dependency>
<groupId>com.fasterxml.Hymanson.core</groupId>
<artifactId>Hymanson-databind</artifactId>
<version>2.2.2</version>
</dependency>
The ResultSetSerializerobject instructs Hymanson on how to serialize (tranform the object to JSON) a ResultSet. It uses the Hymanson Streaming API inside. Here the code of a test:
该ResultSetSerializer对象指示 Hymanson 如何序列化（将对象转换为 JSON）一个 ResultSet。它内部使用了 Hymanson Streaming API。这是测试的代码：
SimpleModule module = new SimpleModule();
module.addSerializer(new ResultSetSerializer());
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.registerModule(module);
[ . . . do the query . . . ]
ResultSet resultset = statement.executeQuery(query);
// Use the DataBind Api here
ObjectNode objectNode = objectMapper.createObjectNode();
// put the resultset in a containing structure
objectNode.putPOJO("results", resultset);
// generate all
objectMapper.writeValue(stringWriter, objectNode);
And, of course, the code of the ResultSetSerializer class:
当然，还有 ResultSetSerializer 类的代码：
public class ResultSetSerializer extends JsonSerializer<ResultSet> {
public static class ResultSetSerializerException extends JsonProcessingException{
private static final long serialVersionUID = -914957626413580734L;
public ResultSetSerializerException(Throwable cause){
super(cause);
}
}
@Override
public Class<ResultSet> handledType() {
return ResultSet.class;
}
@Override
public void serialize(ResultSet rs, JsonGenerator jgen, SerializerProvider provider) throws IOException, JsonProcessingException {
try {
ResultSetMetaData rsmd = rs.getMetaData();
int numColumns = rsmd.getColumnCount();
String[] columnNames = new String[numColumns];
int[] columnTypes = new int[numColumns];
for (int i = 0; i < columnNames.length; i++) {
columnNames[i] = rsmd.getColumnLabel(i + 1);
columnTypes[i] = rsmd.getColumnType(i + 1);
}
jgen.writeStartArray();
while (rs.next()) {
boolean b;
long l;
double d;
jgen.writeStartObject();
for (int i = 0; i < columnNames.length; i++) {
jgen.writeFieldName(columnNames[i]);
switch (columnTypes[i]) {
case Types.INTEGER:
l = rs.getInt(i + 1);
if (rs.wasNull()) {
jgen.writeNull();
} else {
jgen.writeNumber(l);
}
break;
case Types.BIGINT:
l = rs.getLong(i + 1);
if (rs.wasNull()) {
jgen.writeNull();
} else {
jgen.writeNumber(l);
}
break;
case Types.DECIMAL:
case Types.NUMERIC:
jgen.writeNumber(rs.getBigDecimal(i + 1));
break;
case Types.FLOAT:
case Types.REAL:
case Types.DOUBLE:
d = rs.getDouble(i + 1);
if (rs.wasNull()) {
jgen.writeNull();
} else {
jgen.writeNumber(d);
}
break;
case Types.NVARCHAR:
case Types.VARCHAR:
case Types.LONGNVARCHAR:
case Types.LONGVARCHAR:
jgen.writeString(rs.getString(i + 1));
break;
case Types.BOOLEAN:
case Types.BIT:
b = rs.getBoolean(i + 1);
if (rs.wasNull()) {
jgen.writeNull();
} else {
jgen.writeBoolean(b);
}
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
jgen.writeBinary(rs.getBytes(i + 1));
break;
case Types.TINYINT:
case Types.SMALLINT:
l = rs.getShort(i + 1);
if (rs.wasNull()) {
jgen.writeNull();
} else {
jgen.writeNumber(l);
}
break;
case Types.DATE:
provider.defaultSerializeDateValue(rs.getDate(i + 1), jgen);
break;
case Types.TIMESTAMP:
provider.defaultSerializeDateValue(rs.getTime(i + 1), jgen);
break;
case Types.BLOB:
Blob blob = rs.getBlob(i);
provider.defaultSerializeValue(blob.getBinaryStream(), jgen);
blob.free();
break;
case Types.CLOB:
Clob clob = rs.getClob(i);
provider.defaultSerializeValue(clob.getCharacterStream(), jgen);
clob.free();
break;
case Types.ARRAY:
throw new RuntimeException("ResultSetSerializer not yet implemented for SQL type ARRAY");
case Types.STRUCT:
throw new RuntimeException("ResultSetSerializer not yet implemented for SQL type STRUCT");
case Types.DISTINCT:
throw new RuntimeException("ResultSetSerializer not yet implemented for SQL type DISTINCT");
case Types.REF:
throw new RuntimeException("ResultSetSerializer not yet implemented for SQL type REF");
case Types.JAVA_OBJECT:
default:
provider.defaultSerializeValue(rs.getObject(i + 1), jgen);
break;
}
}
jgen.writeEndObject();
}
jgen.writeEndArray();
} catch (SQLException e) {
throw new ResultSetSerializerException(e);
}
}
}
回答by vanderwyst
In addition to suggestions made by @Jim Cook. One other thought is to use a switch instead of if-elses:
除了@Jim Cook 提出的建议。另一种想法是使用 switch 而不是 if-elses：
while(rs.next()) {
int numColumns = rsmd.getColumnCount();
JSONObject obj = new JSONObject();
for( int i=1; i<numColumns+1; i++) {
String column_name = rsmd.getColumnName(i);
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, rs.getDate(column_name)); break;
case java.sql.Types.TIMESTAMP:
obj.put(column_name, rs.getTimestamp(column_name)); break;
default:
obj.put(column_name, rs.getObject(column_name)); break;
}
}
json.put(obj);
}
回答by Marcus
Just as a heads up, the if/then loop is more efficient than the switch for enums. If you have the switch against the raw enum integer, then it's more efficient, but against the variable, if/then is more efficient, at least for Java 5, 6, and 7.
提醒一下，if/then 循环比枚举的 switch 更有效。如果您有针对原始枚举整数的开关，那么它的效率更高，但对于变量，if/then 的效率更高，至少对于 Java 5、6 和 7。
I.e., for some reason (after some performance tests)
即，出于某种原因（经过一些性能测试）
if (ordinalValue == 1) {
...
} else (ordinalValue == 2 {
...
}
is faster than
比
switch( myEnum.ordinal() ) {
case 1:
...
break;
case 2:
...
break;
}
I see that a few people are doubting me, so I'll post code here that you can run yourself to see the difference, along with output I have from Java 7. The results of the following code with 10 enum values are as follows. Note the key here is the if/then using an integer value comparing against ordinal constants of the enum, vs. the switch with an enum's ordinal value against the raw int ordinal values, vs. a switch with the enum against each enum name. The if/then with an integer value beat out both other switches, although the last switch was a little faster than the first switch, it was not faster than the if/else.
我看到有些人在怀疑我，所以我将在这里发布代码，您可以自己运行以查看差异，以及我从 Java 7 获得的输出。以下具有 10 个枚举值的代码的结果如下。请注意，这里的关键是 if/then 使用整数值与枚举的序数常量进行比较，与使用枚举的序数值与原始 int 序数值进行比较的开关，以及使用枚举与每个枚举名称进行比较的开关。带有整数值的 if/then 击败了其他两个开关，虽然最后一个开关比第一个开关快一点，但它并不比 if/else 快。
If / else took 23 ms
Switch took 45 ms
Switch 2 took 30 ms
Total matches: 3000000
If / else 耗时 23 ms
Switch 耗时 45 ms
Switch 2 耗时 30 ms
匹配总数：3000000
package testing;
import java.util.Random;
enum TestEnum {
FIRST,
SECOND,
THIRD,
FOURTH,
FIFTH,
SIXTH,
SEVENTH,
EIGHTH,
NINTH,
TENTH
}
public class SwitchTest {
private static int LOOP = 1000000;
private static Random r = new Random();
private static int SIZE = TestEnum.values().length;
public static void main(String[] args) {
long time = System.currentTimeMillis();
int matches = 0;
for (int i = 0; i < LOOP; i++) {
int j = r.nextInt(SIZE);
if (j == TestEnum.FIRST.ordinal()) {
matches++;
} else if (j == TestEnum.SECOND.ordinal()) {
matches++;
} else if (j == TestEnum.THIRD.ordinal()) {
matches++;
} else if (j == TestEnum.FOURTH.ordinal()) {
matches++;
} else if (j == TestEnum.FIFTH.ordinal()) {
matches++;
} else if (j == TestEnum.SIXTH.ordinal()) {
matches++;
} else if (j == TestEnum.SEVENTH.ordinal()) {
matches++;
} else if (j == TestEnum.EIGHTH.ordinal()) {
matches++;
} else if (j == TestEnum.NINTH.ordinal()) {
matches++;
} else {
matches++;
}
}
System.out.println("If / else took "+(System.currentTimeMillis() - time)+" ms");
time = System.currentTimeMillis();
for (int i = 0; i < LOOP; i++) {
TestEnum te = TestEnum.values()[r.nextInt(SIZE)];
switch (te.ordinal()) {
case 0:
matches++;
break;
case 1:
matches++;
break;
case 2:
matches++;
break;
case 3:
matches++;
break;
case 4:
matches++;
break;
case 5:
matches++;
break;
case 6:
matches++;
break;
case 7:
matches++;
break;
case 8:
matches++;
break;
case 9:
matches++;
break;
default:
matches++;
break;
}
}
System.out.println("Switch took "+(System.currentTimeMillis() - time)+" ms");
time = System.currentTimeMillis();
for (int i = 0; i < LOOP; i++) {
TestEnum te = TestEnum.values()[r.nextInt(SIZE)];
switch (te) {
case FIRST:
matches++;
break;
case SECOND:
matches++;
break;
case THIRD:
matches++;
break;
case FOURTH:
matches++;
break;
case FIFTH:
matches++;
break;
case SIXTH:
matches++;
break;
case SEVENTH:
matches++;
break;
case EIGHTH:
matches++;
break;
case NINTH:
matches++;
break;
default:
matches++;
break;
}
}
System.out.println("Switch 2 took "+(System.currentTimeMillis() - time)+" ms");
System.out.println("Total matches: "+matches);
}
}
回答by Li3ro
If anyone plan to use this implementation, You might wanna check this outand this
This is my version of that convertion code:
这是我的转换代码版本：
public class ResultSetConverter {
public static JSONArray convert(ResultSet rs) throws SQLException,
JSONException {
JSONArray json = new JSONArray();
ResultSetMetaData rsmd = rs.getMetaData();
int numColumns = rsmd.getColumnCount();
while (rs.next()) {
JSONObject obj = new JSONObject();
for (int i = 1; i < numColumns + 1; i++) {
String column_name = rsmd.getColumnName(i);
if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
obj.put(column_name, rs.getArray(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
obj.put(column_name, rs.getLong(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.REAL) {
obj.put(column_name, rs.getFloat(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
obj.put(column_name, rs.getBoolean(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
obj.put(column_name, rs.getBlob(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
obj.put(column_name, rs.getDouble(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
obj.put(column_name, rs.getDouble(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
obj.put(column_name, rs.getInt(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
obj.put(column_name, rs.getNString(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
obj.put(column_name, rs.getString(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.CHAR) {
obj.put(column_name, rs.getString(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.NCHAR) {
obj.put(column_name, rs.getNString(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.LONGNVARCHAR) {
obj.put(column_name, rs.getNString(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.LONGVARCHAR) {
obj.put(column_name, rs.getString(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
obj.put(column_name, rs.getByte(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
obj.put(column_name, rs.getShort(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
obj.put(column_name, rs.getDate(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.TIME) {
obj.put(column_name, rs.getTime(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
obj.put(column_name, rs.getTimestamp(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.BINARY) {
obj.put(column_name, rs.getBytes(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.VARBINARY) {
obj.put(column_name, rs.getBytes(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.LONGVARBINARY) {
obj.put(column_name, rs.getBinaryStream(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.BIT) {
obj.put(column_name, rs.getBoolean(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.CLOB) {
obj.put(column_name, rs.getClob(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) {
obj.put(column_name, rs.getBigDecimal(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.DECIMAL) {
obj.put(column_name, rs.getBigDecimal(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.DATALINK) {
obj.put(column_name, rs.getURL(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.REF) {
obj.put(column_name, rs.getRef(column_name));
} else if (rsmd.getColumnType(i) == java.sql.Types.STRUCT) {
obj.put(column_name, rs.getObject(column_name)); // must be a custom mapping consists of a class that implements the interface SQLData and an entry in a java.util.Map object.
} else if (rsmd.getColumnType(i) == java.sql.Types.DISTINCT) {
obj.put(column_name, rs.getObject(column_name)); // must be a custom mapping consists of a class that implements the interface SQLData and an entry in a java.util.Map object.
} else if (rsmd.getColumnType(i) == java.sql.Types.JAVA_OBJECT) {
obj.put(column_name, rs.getObject(column_name));
} else {
obj.put(column_name, rs.getString(i));
}
}
json.put(obj);
}
return json;
}
}
回答by Lukas Eder
You could use jOOQfor the job. You don't have to use all of jOOQ's features to take advantage of some useful JDBC extensions. In this case, simply write:
您可以使用jOOQ来完成这项工作。您不必使用 jOOQ 的所有功能来利用一些有用的 JDBC 扩展。在这种情况下，只需编写：
String json = DSL.using(connection).fetch(resultSet).formatJSON();
Relevant API methods used are:
使用的相关API方法有：
DSLContext.fetch(ResultSet)to convert a JDBC ResultSet into a jOOQ Result.Result.formatJSON()to format the jOOQ Result into a JSON String.
DSLContext.fetch(ResultSet)将 JDBC ResultSet 转换为 jOOQ 结果。Result.formatJSON()将 jOOQ 结果格式化为 JSON 字符串。
The resulting formatting will look like this:
生成的格式如下所示：
{"fields":[{"name":"field-1","type":"type-1"},
{"name":"field-2","type":"type-2"},
...,
{"name":"field-n","type":"type-n"}],
"records":[[value-1-1,value-1-2,...,value-1-n],
[value-2-1,value-2-2,...,value-2-n]]}
You could also create your own formatting rather easily, through Result.map(RecordMapper)
您还可以通过以下方式轻松创建自己的格式 Result.map(RecordMapper)
This essentially does the same as your code, circumventing the generation of JSON objects, "streaming" directly into a StringBuilder. I'd say that the performance overhead should be negligible in both cases, though.
这基本上与您的代码相同，绕过 JSON 对象的生成，直接“流式传输”到StringBuilder. 不过，我认为这两种情况下的性能开销都可以忽略不计。
(Disclaimer: I work for the company behind jOOQ)
（免责声明：我为 jOOQ 背后的公司工作）
回答by reil bhudaya
the other way , here I have used ArrayList and Map, so its not call json object row by row but after iteration of resultset finished :
另一种方式，在这里我使用了 ArrayList 和 Map，所以它不是逐行调用 json 对象，而是在结果集迭代完成后：
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
ResultSetMetaData rsMetaData = rs.getMetaData();
while(rs.next()){
Map map = new HashMap();
for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
String key = rsMetaData.getColumnName(i);
String value = null;
if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
value = rs.getString(key);
} else if(rsmd.getColumnType(i)==java.sql.Types.BIGINT)
value = rs.getLong(key);
}
map.put(key, value);
}
list.add(map);
}
json.put(list);
回答by coderz
First pre-generate column names, second use rs.getString(i)instead of rs.getString(column_name).
首先预先生成列名，第二次使用rs.getString(i)而不是rs.getString(column_name).
The following is an implementation of this:
下面是一个实现：
/*
* 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;
}
回答by Mr. Polywhirl
This answer may not be the most efficient, but it sure is dynamic. Pairing native JDBC with Google's Gson library, I easily can convert from an SQL result to a JSON stream.
这个答案可能不是最有效的，但它肯定是动态的。将本机 JDBC 与 Google 的 Gson 库配对，我可以轻松地将 SQL 结果转换为 JSON 流。
I have included the converter, example DB properties file, SQL table generation, and a Gradle build file (with dependencies used).
我已经包含了转换器、示例 DB 属性文件、SQL 表生成和 Gradle 构建文件（使用了依赖项）。
QueryApp.java
查询应用程序
import java.io.PrintWriter;
import com.oracle.jdbc.ResultSetConverter;
public class QueryApp {
public static void main(String[] args) {
PrintWriter writer = new PrintWriter(System.out);
String dbProps = "/database.properties";
String indent = " ";
writer.println("Basic SELECT:");
ResultSetConverter.queryToJson(writer, dbProps, "SELECT * FROM Beatles", indent, false);
writer.println("\n\nIntermediate SELECT:");
ResultSetConverter.queryToJson(writer, dbProps, "SELECT first_name, last_name, getAge(date_of_birth) as age FROM Beatles", indent, true);
}
}
ResultSetConverter.java
结果集转换器.java
package com.oracle.jdbc;
import java.io.*;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.*;
import com.google.common.reflect.TypeToken;
import com.google.gson.GsonBuilder;
import com.google.gson.stream.JsonWriter;
public class ResultSetConverter {
public static final Type RESULT_TYPE = new TypeToken<List<Map<String, Object>>>() {
private static final long serialVersionUID = -3467016635635320150L;
}.getType();
public static void queryToJson(Writer writer, String connectionProperties, String query, String indent, boolean closeWriter) {
Connection conn = null;
Statement stmt = null;
GsonBuilder gson = new GsonBuilder();
JsonWriter jsonWriter = new JsonWriter(writer);
if (indent != null) jsonWriter.setIndent(indent);
try {
Properties props = readConnectionInfo(connectionProperties);
Class.forName(props.getProperty("driver"));
conn = openConnection(props);
stmt = conn.createStatement();
gson.create().toJson(QueryHelper.select(stmt, query), RESULT_TYPE, jsonWriter);
if (closeWriter) jsonWriter.close();
stmt.close();
conn.close();
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
try {
if (stmt != null) stmt.close();
} catch (SQLException se2) {
}
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
try {
if (closeWriter && jsonWriter != null) jsonWriter.close();
} catch (IOException ioe) {
ioe.printStackTrace();
}
}
}
private static Properties readConnectionInfo(String resource) throws IOException {
Properties properties = new Properties();
InputStream in = ResultSetConverter.class.getResourceAsStream(resource);
properties.load(in);
in.close();
return properties;
}
private static Connection openConnection(Properties connectionProperties) throws IOException, SQLException {
String database = connectionProperties.getProperty("database");
String username = connectionProperties.getProperty("username");
String password = connectionProperties.getProperty("password");
return DriverManager.getConnection(database, username, password);
}
}
QueryHelper.java
QueryHelper.java
package com.oracle.jdbc;
import java.sql.*;
import java.text.*;
import java.util.*;
import com.google.common.base.CaseFormat;
public class QueryHelper {
static DateFormat DATE_FORMAT = new SimpleDateFormat("YYYY-MM-dd");
public static List<Map<String, Object>> select(Statement stmt, String query) throws SQLException {
ResultSet resultSet = stmt.executeQuery(query);
List<Map<String, Object>> records = mapRecords(resultSet);
resultSet.close();
return records;
}
public static List<Map<String, Object>> mapRecords(ResultSet resultSet) throws SQLException {
List<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
ResultSetMetaData metaData = resultSet.getMetaData();
while (resultSet.next()) {
records.add(mapRecord(resultSet, metaData));
}
return records;
}
public static Map<String, Object> mapRecord(ResultSet resultSet, ResultSetMetaData metaData) throws SQLException {
Map<String, Object> record = new HashMap<String, Object>();
for (int c = 1; c <= metaData.getColumnCount(); c++) {
String columnType = metaData.getColumnTypeName(c);
String columnName = formatPropertyName(metaData.getColumnName(c));
Object value = resultSet.getObject(c);
if (columnType.equals("DATE")) {
value = DATE_FORMAT.format(value);
}
record.put(columnName, value);
}
return record;
}
private static String formatPropertyName(String property) {
return CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, property);
}
}
database.properties
数据库.properties
driver=com.mysql.jdbc.Driver
database=jdbc:mysql://localhost/JDBC_Tutorial
username=root
password=
JDBC_Tutorial.sql
JDBC_Tutorial.sql
-- phpMyAdmin SQL Dump
-- version 4.5.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jan 12, 2016 at 07:40 PM
-- Server version: 10.1.8-MariaDB
-- PHP Version: 5.6.14
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `jdbc_tutorial`
--
CREATE DATABASE IF NOT EXISTS `jdbc_tutorial` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `jdbc_tutorial`;
DELIMITER $$
--
-- Functions
--
DROP FUNCTION IF EXISTS `getAge`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `getAge` (`in_dob` DATE) RETURNS INT(11) NO SQL
BEGIN
DECLARE l_age INT;
IF DATE_FORMAT(NOW(),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d') THEN
-- This person has had a birthday this year
SET l_age=DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(in_dob,'%Y');
ELSE
-- Yet to have a birthday this year
SET l_age=DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;
END IF;
RETURN(l_age);
END$$
DELIMITER ;
-- --------------------------------------------------------
--
-- Table structure for table `beatles`
--
DROP TABLE IF EXISTS `beatles`;
CREATE TABLE IF NOT EXISTS `beatles` (
`id` int(11) NOT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`date_of_birth` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Truncate table before insert `beatles`
--
TRUNCATE TABLE `beatles`;
--
-- Dumping data for table `beatles`
--
INSERT INTO `beatles` (`id`, `first_name`, `last_name`, `date_of_birth`) VALUES(100, 'John', 'Lennon', '1940-10-09');
INSERT INTO `beatles` (`id`, `first_name`, `last_name`, `date_of_birth`) VALUES(101, 'Paul', 'McCartney', '1942-06-18');
INSERT INTO `beatles` (`id`, `first_name`, `last_name`, `date_of_birth`) VALUES(102, 'George', 'Harrison', '1943-02-25');
INSERT INTO `beatles` (`id`, `first_name`, `last_name`, `date_of_birth`) VALUES(103, 'Ringo', 'Starr', '1940-07-07');
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
build.gradle
构建.gradle
apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'application'
mainClassName = 'com.oracle.jdbc.QueryApp'
repositories {
maven {
url "http://repo1.maven.org/maven2"
}
}
jar {
baseName = 'jdbc-tutorial'
version = '1.0.0'
}
sourceCompatibility = 1.7
targetCompatibility = 1.7
dependencies {
compile 'mysql:mysql-connector-java:5.1.16'
compile 'com.google.guava:guava:18.0'
compile 'com.google.code.gson:gson:1.7.2'
}
task wrapper(type: Wrapper) {
gradleVersion = '2.9'
}
Results
结果
Basic SELECT
基本选择
[
{
"firstName": "John",
"lastName": "Lennon",
"dateOfBirth": "1940-10-09",
"id": 100
},
{
"firstName": "Paul",
"lastName": "McCartney",
"dateOfBirth": "1942-06-18",
"id": 101
},
{
"firstName": "George",
"lastName": "Harrison",
"dateOfBirth": "1943-02-25",
"id": 102
},
{
"firstName": "Ringo",
"lastName": "Starr",
"dateOfBirth": "1940-07-07",
"id": 103
}
]
Intermediate SELECT
中级选择
[
{
"firstName": "John",
"lastName": "Lennon",
"age": 75
},
{
"firstName": "Paul",
"lastName": "McCartney",
"age": 73
},
{
"firstName": "George",
"lastName": "Harrison",
"age": 72
},
{
"firstName": "Ringo",
"lastName": "Starr",
"age": 75
}
]
