I have a RESTful application that connects to MySQL database (raw paste here: https://pastebin.com/raw/3fBp3j0B) and prints out table data in JSON format.
If I System.out.println()
some data from ResultSet
, everything shows up correctly, but with the JSON API only the last row in the table is printed out, twice.
import java.sql.*;
import javax.json.*;
public class Tietokanta {
protected Connection yhteys = null;
protected Statement kysely = null;
protected ResultSet tulosjoukko = null;
public boolean avaaYhteys() {
boolean ok = true;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
yhteys = DriverManager.getConnection("jdbc:mysql://localhost/savukelaskuri?serverTimezone=UTC", "root", "");
} catch (Exception e) {
e.printStackTrace();
ok = false;
}
return ok;
}
public boolean suljeYhteys() {
boolean ok = true;
try {
this.yhteys.close();
} catch (Exception e) {
ok = false;
}
return ok;
}
}
...
import java.math.BigDecimal;
import javax.json.Json;
import javax.json.JsonArray;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.UriInfo;
import javax.ws.rs.Produces;
import javax.ws.rs.Consumes;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.PUT;
import static javax.ws.rs.client.Entity.json;
import javax.ws.rs.core.MediaType;
import org.json.JSONArray;
import org.json.JSONObject;
@Path("savukkeet")
public class ApiResource extends Tietokanta {
JSONObject jsonolio = new JSONObject();
JSONArray jsontaulu = new JSONArray();
@Context
private UriInfo context;
public ApiResource() {
this.avaaYhteys();
}
@GET
@Produces(MediaType.APPLICATION_JSON)
public String getJson() {
try {
kysely = yhteys.createStatement();
String sql = "SELECT * FROM kulutus";
tulosjoukko = kysely.executeQuery(sql);
while (tulosjoukko.next()) {
System.out.println(tulosjoukko.getString("pvm"));
jsonolio.put("id", tulosjoukko.getInt("id"));
jsonolio.put("pvm", tulosjoukko.getString("pvm"));
jsonolio.put("kulutus", tulosjoukko.getInt("kulutus"));
jsontaulu.put(jsonolio);
}
} catch (Exception e) {
e.printStackTrace();
}
return jsontaulu.toString(4);
}
}
I expect the result to be
[
{
"kulutus": 9,
"pvm": "2019-01-14 16:46:00",
"id": 1
},
{
"kulutus": 8,
"pvm": "2019-01-15 21:18:00",
"id": 2
}
]
but instead I get this
[
{
"kulutus": 8,
"pvm": "2019-01-15 21:18:00",
"id": 2
},
{
"kulutus": 8,
"pvm": "2019-01-15 21:18:00",
"id": 2
}
]
It should work if you create a new JSONObject
for each iteration in the while loop:
while (tulosjoukko.next()) {
System.out.println(tulosjoukko.getString("pvm"));
jsonolio = new JSONObject();
jsonolio.put("id", tulosjoukko.getInt("id"));
jsonolio.put("pvm", tulosjoukko.getString("pvm"));
jsonolio.put("kulutus", tulosjoukko.getInt("kulutus"));
jsontaulu.put(jsonolio);
}
Java is pass-by-reference, so when you put a property to jsonolio
it overrides the previous value even inside the JSONArray because it is still the same object