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

1. id: int PRIMARY KEY AUTO_INCREMENT
2. string
3. string
4. string
5. string
6. string
7. int
8. 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

Answer 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.