The following code converts a ResultSet to a JSON string using JSONArray and 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;
}
}
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.
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?
For all who've opted for the if-else mesh solution, please use:
String columnName = metadata.getColumnName(
String displayName = metadata.getColumnLabel(i);
switch (metadata.getColumnType(i)) {
case Types.ARRAY:
obj.put(displayName, resultSet.getArray(columnName));
break;
...
Because in case of aliases in your query, the column name and column label are two different things. For example if you execute:
select col1, col2 as my_alias from table
You will get
[
{ "col1": 1, "col2": 2 },
{ "col1": 1, "col2": 2 }
]
Rather than:
[
{ "col1": 1, "my_alias": 2 },
{ "col1": 1, "my_alias": 2 }
]
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.
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));
In addition to suggestions made by @Jim Cook. One other thought is to use a switch instead of 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);
}
package com.idal.cib;
import java.io.FileWriter;
import java.io.IOException;
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 org.json.simple.JSONArray;
import org.json.simple.JSONObject;
public class DBJsonConverter {
static ArrayList<String> data = new ArrayList<String>();
static Connection conn = null;
static PreparedStatement ps = null;
static ResultSet rs = null;
static String path = "";
static String driver="";
static String url="";
static String username="";
static String password="";
static String query="";
@SuppressWarnings({ "unchecked" })
public static void dataLoad(String path) {
JSONObject obj1 = new JSONObject();
JSONArray jsonArray = new JSONArray();
conn = DatabaseConnector.getDbConnection(driver, url, username,
password);
try {
ps = conn.prepareStatement(query);
rs = ps.executeQuery();
ArrayList<String> columnNames = new ArrayList<String>();
if (rs != null) {
ResultSetMetaData columns = rs.getMetaData();
int i = 0;
while (i < columns.getColumnCount()) {
i++;
columnNames.add(columns.getColumnName(i));
}
while (rs.next()) {
JSONObject obj = new JSONObject();
for (i = 0; i < columnNames.size(); i++) {
data.add(rs.getString(columnNames.get(i)));
{
for (int j = 0; j < data.size(); j++) {
if (data.get(j) != null) {
obj.put(columnNames.get(i), data.get(j));
}else {
obj.put(columnNames.get(i), "");
}
}
}
}
jsonArray.add(obj);
obj1.put("header", jsonArray);
FileWriter file = new FileWriter(path);
file.write(obj1.toJSONString());
file.flush();
file.close();
}
ps.close();
} else {
JSONObject obj2 = new JSONObject();
obj2.put(null, null);
jsonArray.add(obj2);
obj1.put("header", jsonArray);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
rs.close();
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
@SuppressWarnings("static-access")
public static void main(String[] args) {
// TODO Auto-generated method stub
driver = "oracle.jdbc.driver.OracleDriver";
url = "jdbc:oracle:thin:@localhost:1521:database";
username = "user";
password = "password";
path = "path of file";
query = "select * from temp_employee";
DatabaseConnector dc = new DatabaseConnector();
dc.getDbConnection(driver,url,username,password);
DBJsonConverter formatter = new DBJsonConverter();
formatter.dataLoad(path);
}
}
package com.idal.cib;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnector {
static Connection conn1 = null;
public static Connection getDbConnection(String driver, String url,
String username, String password) {
// TODO Auto-generated constructor stub
try {
Class.forName(driver);
conn1 = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn1;
}
}
If anyone plan to use this implementation, You might wanna check this out and 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;
}
}
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 :
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);
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.
I have included the converter, example DB properties file, SQL table generation, and a Gradle build file (with dependencies used).
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);
}
}
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);
}
}
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);
}
}
driver=com.mysql.jdbc.Driver
database=jdbc:mysql://localhost/JDBC_Tutorial
username=root
password=
-- 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 */;
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'
}
[
{
"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
}
]
[
{
"firstName": "John",
"lastName": "Lennon",
"age": 75
},
{
"firstName": "Paul",
"lastName": "McCartney",
"age": 73
},
{
"firstName": "George",
"lastName": "Harrison",
"age": 72
},
{
"firstName": "Ringo",
"lastName": "Starr",
"age": 75
}
]
A simpler solution (based on code in question):
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; i++) {
String column_name = rsmd.getColumnName(i);
obj.put(column_name, rs.getObject(column_name));
}
json.put(obj);
}
return json;
First pre-generate column names, second use rs.getString(i) instead of 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;
}
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.
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.
If / else took 23 ms
Switch took 45 ms
Switch 2 took 30 ms
Total matches: 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);
}
}
You could use jOOQ for 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:
String json = DSL.using(connection).fetch(resultSet).formatJSON();
Relevant API methods used are:
DSLContext.fetch(ResultSet) to convert a JDBC ResultSet into a jOOQ Result.Result.formatJSON() to format the jOOQ Result into a JSON String.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)
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.
(Disclaimer: I work for the company behind jOOQ)
Of course, you don't have to use your middleware to map JDBC ResultSets to JSON. The question doesn't mention for which SQL dialect this needs to be done, but many support standard SQL/JSON syntax, or something similar, e.g.
Oracle
SELECT json_arrayagg(json_object(*))
FROM t
SQL Server
SELECT *
FROM t
FOR JSON AUTO
PostgreSQL
SELECT to_jsonb(array_agg(t))
FROM t
public static JSONArray GetJSONDataFromResultSet(ResultSet rs) throws SQLException {
ResultSetMetaData metaData = rs.getMetaData();
int count = metaData.getColumnCount();
String[] columnName = new String[count];
JSONArray jsonArray = new JSONArray();
while(rs.next()) {
JSONObject jsonObject = new JSONObject();
for (int i = 1; i <= count; i++){
columnName[i-1] = metaData.getColumnLabel(i);
jsonObject.put(columnName[i-1], rs.getObject(i));
}
jsonArray.put(jsonObject);
}
return jsonArray;
}
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.
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.
As promised I'm back but not with Gson, instead with Jackson 2. Sorry to be late (of 2 years).
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.
1: So to use less memory we must:
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. 2: As Jackson Documentation says:
Streaming API is best performing (lowest overhead, fastest read/write; other 2 methods build on it)
3: I see you in your code use getInt, getBoolean. getFloat... of ResultSet without wasNull. I expect this can yield problems.
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 int SQL Types.
The answer: Not yet fully tested, it's based on Jackson 2.2:
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.2.2</version>
</dependency>
The ResultSetSerializer object instructs Jackson on how to serialize (tranform the object to JSON) a ResultSet. It uses the Jackson Streaming API inside. Here the code of a test:
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:
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);
}
}
}
Source: Stackoverflow.com