I'm trying to implement restful services which will send Json from ResultSet of PostgreSQL, I was able to send by mapping resultset to a POJO class using Jersey and I dont want to use any other library like Gson, but I would like to send resultset directly as Json without the intermediate POJOs as I'm not in need of POJOs for any other purpose, I'm not very much familiar with servlets or restfull or JAXB, etc
I was able to create a custom JsonArray(of Javax.json) from ResultSet without POJO class and convert toString and send String on service, as of now for numbers, strings, dates, timestamps(can even try and do for arrays using recursion, didn't write Custom MessageBodyWriter but can write simple ones, may be even pretend even to write for this) but didn't understand perfectly whether what i'm doing is right way of doing or not and how to handle all data types of PostgreSQL to Json and send over Rest.
Please give me suggestions of what are the ways to implement this, and Please comment little advance things and how they are inter related more clearly on Jersey, JAXB, JAX-RS, for e.g., like how a POJO is getting converted to Json internally, where is that MessageBodyWriter which is converting.
Kindly give me suggestion related to jersey and not other libraries.
Thank you.
Please find the below code I have written and suggest me even improvements.
Resource Class :
@Path("/message")
public class MessageResource {
MessageService msgService = new MessageService();
Test t = new Test();
@GET
@Produces(MediaType.TEXT_PLAIN)
public String getMessages() throws SQLException {
return t.getAllMessages();
}
}
Service Class :
public class Test {
private static ResultSetMetaData rsmd;
private static int numOfColumns;
PostgreHelper help = new PostgreHelper();
public String getAllMessages() throws SQLException {
RetrieveFromPostGre getData = new RetrieveFromPostGre();
Map<String, ?> config = null;
JsonBuilderFactory factory = Json.createBuilderFactory(config);
JsonObjectBuilder rowJsonObject = factory.createObjectBuilder();
JsonArrayBuilder tableJsonArray = factory.createArrayBuilder();
getData.selectRows("db1", "schema1", "table1");
ResultSet rs = getData.selectRows("db1", "schema1", "table1");
rsmd=rs.getMetaData();
numOfColumns = rsmd.getColumnCount();
try {
while(rs.next()) {
createJsonObjectOfSingleRow(rs, rowJsonObject);
tableJsonArray.add(rowJsonObject);
}
} catch (Exception e) {
System.out.println("error");
}
return tableJsonArray.build().toString();
}
public void createJsonObjectOfSingleRow(ResultSet rs, JsonObjectBuilder rowJsonObject) throws SQLException {
String numberType[] = {"BOOLEAN", "INTEGER", "FLOAT", "LONG", "DOUBLE"};
String stringType[] = {"STRING", "DATE"};
for (int i=1; i<=numOfColumns; i++) {
String column_name = rsmd.getColumnName(i);
System.out.println(help.getDataType(rsmd.getColumnType(i)));
if(Arrays.asList(numberType).contains(help.getDataType(rsmd.getColumnType(i)))) {
System.out.println("in num");
rowJsonObject.add(column_name, rs.getInt(column_name));
}
else if(Arrays.asList(stringType).contains(help.getDataType(rsmd.getColumnType(i)))) {
System.out.println("in stri");
rowJsonObject.add(column_name, rs.getString(column_name));
}
else if(help.getDataType(rsmd.getColumnType(i)).equalsIgnoreCase("ARRAY")) {
/* This is not working, and here I was thinking of doing recursion*/
//System.out.println("in arr");
//JsonArray jArray = (JsonArray) rs.getArray(column_name);
//rowJsonObject.add(column_name, jArray);
rowJsonObject.add("array", "flag");//just for the sake of running code successfully wrote this statement
}
}
}
}
PostgreServices :
public class RetrieveFromPostGre {
PostgreConnection postgreConnection = new PostgreConnection();
PostgreHelper getColumnsMeta = new PostgreHelper();
private String databaseName, schemaName, tableName;
private Map<String, String> columns = new HashMap<String, String>();
public String getDatabaseName() {
return databaseName;
}
public void setDatabaseName(String databaseName) {
this.databaseName = databaseName;
}
public String getSchemaName() {
return schemaName;
}
public void setSchemaName(String schemaName) {
this.schemaName = schemaName;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public Map<String, String> getColumns() {
return columns;
}
public void setColumns(Map<String, String> columns) {
this.columns = columns;
}
public ResultSet selectRows(String databaseName, String schemaName, String tableName) throws SQLException {
this.setDatabaseName(databaseName);
this.setSchemaName(schemaName);
this.setTableName(tableName);
Connection dbConnection = postgreConnection.getdbconnection(this.getDatabaseName());
ResultSet rs = this.retrieveRows(dbConnection);
return rs;
}
public ResultSet retrieveRows(Connection dbConnection) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
String sql;
sql = "select * from " + this.schemaName + "." + this.tableName;
try {
stmt = dbConnection.createStatement();
rs = stmt.executeQuery(sql);
return rs;
//stmt.close();
} catch (Exception e) {
System.err.println( e.getClass().getName()+": "+ e.getMessage() );
e.printStackTrace();
System.exit(0);
}
return rs;
}
}
DatabaseHelper :
public class PostgreHelper {
public String getDataType(int i) {
String type="";
switch(i) {
case java.sql.Types.INTEGER:
type="INTEGER"; break;
case java.sql.Types.BIGINT:
type="LONG"; break;
case java.sql.Types.BOOLEAN:
type="BOOLEAN"; break;
case java.sql.Types.BIT:
type="BOOLEAN"; break;
case java.sql.Types.FLOAT:
type="FLOAT"; break;
case java.sql.Types.REAL:
type="DOUBLE"; break;
case java.sql.Types.DOUBLE:
type="DOUBLE"; break;
case java.sql.Types.NUMERIC:
type="INTEGER"; break;
case java.sql.Types.DECIMAL:
type="DOUBLE"; break;
case java.sql.Types.CHAR:
type="STRING"; break;
case java.sql.Types.NCHAR:
type="STRING"; break;
case java.sql.Types.VARCHAR:
type="STRING"; break;
case java.sql.Types.NVARCHAR:
type="STRING"; break;
case java.sql.Types.LONGVARCHAR:
type="STRING"; break;
case java.sql.Types.DATE:
type="DATE"; break;
case java.sql.Types.TIME:
type="DATE"; break;
case java.sql.Types.TIMESTAMP:
type="DATE"; break;
case java.sql.Types.BINARY:
type="BINARY"; break;
case java.sql.Types.VARBINARY:
type="BINARY"; break;
case java.sql.Types.LONGVARBINARY:
type="BINARY"; break;
case java.sql.Types.NULL:
type="STRING"; break;
case java.sql.Types.OTHER:
type="STRING"; break;
case java.sql.Types.JAVA_OBJECT:
type="OBJECT"; break;
case java.sql.Types.DISTINCT:
type="STRING"; break;
case java.sql.Types.STRUCT:
type="STRING"; break;
case java.sql.Types.ARRAY:
type="ARRAY"; break;
case java.sql.Types.BLOB:
type="STRING"; break;
case java.sql.Types.CLOB:
type="STRING"; break;
case java.sql.Types.REF:
type="STRING"; break;
}
return type;
}
}
SerializableJSONArray Code, I'm writting this class code just for knowing whether its gonna work or not by the commenters :
public class SerializableJSONArray implements Serializable {
private transient JSONArray jsonArray;
public SerializableJSONArray(JSONArray jsonArray) {
this.jsonArray = jsonArray;
}
public JSONArray getJSONArray() {
return jsonArray;
}
private void writeObject(ObjectOutputStream oos) throws IOException {
oos.defaultWriteObject();
oos.writeObject(jsonArray.toString());
}
private void readObject(ObjectInputStream ois) throws ClassNotFoundException, IOException, JSONException {
ois.defaultReadObject();
jsonArray = new JSONArray((String) ois.readObject());
}
}
