CODEWITHSUNDEEP
javajsondatabasepostgresql
puja
puja

Reputation: 27

Retrieving json query from postgres and display in java

String selectTableSQL = "SELECT USER_ID, USERNAME from DBUSER";
Statement statement = dbConnection.createStatement();
ResultSet rs = statement.executeQuery(selectTableSQL);
while (rs.next()) {
String userid = rs.getString("USER_ID");
String username = rs.getString("USERNAME");
}

With the above code..I am able to establish connection with postgres and display values from the database table. But how to parse a JSON Select query -

select array_to_json(array_agg(row_to_json(t)))
from (
  select USER_ID, USERNAME from DBUSER
) t

The resultant json structure for the above Select query in postgres is -

[{user_id:"123",username:"abc"},{user_id:"234",username:"pqr"}]

Not understanding how to read the above json "select" query from postgres and display the values "USER_ID" and "USERNAME" separately in java.

I am just a beginner.Any Help would be appreciated.Thankyou in advance.

Upvotes: 1

Views: 4965

Answers (2)

Divyesh Kanzariya
Divyesh Kanzariya

Reputation: 3789

you should use Jackson - ObjectMapper Class like this

ObjectMapper mapper = new ObjectMapper();
List<User> userList = mapper.readValue(jsonString, typeFactory.constructCollectionType(List.class, User.class));

for(User val : userList){
    System.out.println("User id : "+val.getUserId);
    System.out.println("User id : "+val.getUserName);
}

it also work fine

List<User> userList= Arrays.asList(mapper.readValue(json, User[].class));

Upvotes: 3

hzitoun
hzitoun

Reputation: 5832

let me suppose your model is like below 

public class User {
 public int userId;
 public String userName; 
}

Use Gson to convert your JSON String (the return of your query) to a list of model (user in your case).

Gson gson = new Gson();
Type type = new TypeToken<List<User >>(){}.getType();
List<User> userList = gson.fromJson(jsonString, type); // where jsonString = "[{user_id:"123",username:"abc"},{user_id:"234",username:"pqr"}]" (the return of your query)
for (User user: userList ){
        System.out.println("user " + user);
}

Upvotes: 0

Related Questions