How to convert SQL query to JSON Array

Question

i need to convert the result of a query like this:

SELECT * FROM table WHERE column1=X

to a JSONArray in java. In my table i have the following columns

id: int PRIMARY KEY AUTO_INCREMENT
string
string
string
string
string
int
timeStamp CURRENT_TIMESTAMP

I need to maintain the same structure except the id field that i don't need.

public static ArrayList<Ticket> lookForATicket(String fermataDiscesa) throws Exception {
    Connection dbConn = null;
    ArrayList<Ticket> list = null;
    try {
        try {
            dbConn = DBConnection.createConnection();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Statement stmt = dbConn.createStatement();
        String query = "SELECT * FROM offer WHERE fermataDiscesa = '" + fermataDiscesa
                + "'";
        //test
        System.out.println(query);
        ResultSet rs = stmt.executeQuery(query);
        list = new ArrayList<Ticket>();
        Ticket tickets = null;
        while (rs.next()) {
            //per test
            System.out.println(rs.getString(1) + rs.getString(2) + rs.getString(3) + rs.getString(4) + rs.getString(5) + rs.getString(6) + rs.getInt(7));
            tickets = new Ticket();
            //setto i campi
            tickets.setIdGooglePlus(rs.getString("IDGOOGLEPLUS"));
            tickets.setIdFacebook(rs.getString("IDFACEBOOK"));
            tickets.setFermataSalita(rs.getString("FERMATASALITA"));
            tickets.setFermataDiscesa(rs.getString("FERMATADISCESA"));
            tickets.setBus(rs.getString("BUS"));
            tickets.setTempoRimasto(rs.getInt("TEMPORIMASTO"));
            tickets.setTimeStamp(rs.getTimestamp("TIMESTAMP"));
            //aggiungo alla lista
            list.add(tickets);
        }
    } catch (SQLException sqle) {
        throw sqle;
    } catch (Exception e) {
        // TODO Auto-generated catch block
        if (dbConn != null) {
            dbConn.close();
        }
        throw e;
    } finally {
        if (dbConn != null) {
            dbConn.close();
        }
    }
    return list;
}

Ticket is my custom class. And that is the code for "printing" my JSON object:

    //Path: http://localhost:8080/RESTscambio/search
@Path("/search")
public class Search {
    // HTTP Get Method
    @GET 
    // Path: http://localhost:8080/RESTscambio/search/dosearch
    @Path("/dosearch")
    // Produces JSON as response
    @Produces(MediaType.APPLICATION_JSON) 

public String doSearch(@QueryParam("fermataDiscesa") String fermataDiscesa) throws Exception{
    System.out.println("Cerco biglietti..");
    ArrayList<Ticket> list = null;
    String response = null;

    list = DBConnection.lookForATicket(fermataDiscesa);
    JSONArray jArray = new JSONArray(list);

    if(list != null){
        //System.out.println(jObj.put("list_of_ticket", list).toString());
        //jObj.put("list_of_ticket", list);
        response = Utility.constructJSON("Biglietti disponibili", true);
    }else{
        response = Utility.constructJSON("Nessun biglietto disponibile", false);
    }
return response;        
}

I need to use response. I can define a new function to print. Can someone help me? Thank you

Which DBMS are you using? Postgres as various functions to convert data into JSON. — user330315
– user330315, Commented Jul 16, 2014 at 8:47

Paul · Accepted Answer · 2014-07-16 14:44:16Z

1

You're going to have to write some code but not too much, assuming you're already able to retrieve the results of your query. I've found Json-lib easy to use - it's straightforward to create and populate a JSON object or to convert to/from Java objects (either custom or collections and maps).

There are plenty of examples at the linked site to guide you.

In light of your updated question (you initially didn't say you had a bean) I recommend using Jackson. This example shows how to use Jackson to output a bean to a file and to a String.

I've answered your initial question about converting output of a query to JSON. For answers to questions on other topics (like the response) you'll need to ask a new StackOverflow question.

edited Jul 16, 2014 at 14:44

answered Jul 15, 2014 at 23:09

Paul

20.2k15 gold badges84 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert SQL query to JSON Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related