-1

I have a sql query, works fine, but I want to show a JSON response, with the result, like fetchall in python, I can the result with a "while" but, there a "fetchall" for java?

This is my code, I want to show the query result in a JSON response:

    @RequestMapping(value="/parity/{date}", method=RequestMethod.GET, 
produces = "application/json")
    public ResponseEntity parity(@PathVariable String date) throws SQLException {

        String[] a = date.split("-");
        date = a[0].substring(2) + a[1] + a[2];

        Connection connection = ConnectionS.getInstance().getConnection(env);

        String selectSql = "SELECT EX FROM TS1.EXC WHERE EXT = '"+ date +"' AND EXR=1";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(selectSql);
        //the fetchall here for translate a JSONresponse below

        return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(new JsonResponse("ok"));
}
Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
FranzSif
  • 113
  • 2
  • 11
  • Any reason why you're eschewing Spring Data JPA (which would give you 99% of this for free)? – Makoto Jun 28 '18 at 16:35
  • @Makoto sorry, I dont understant, how? – FranzSif Jun 28 '18 at 16:37
  • No need for [Spring Data JPA](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/). Just use [Spring JDBC](https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access.html#jdbc) (as shown in [my answer](https://stackoverflow.com/a/51087796/5221149)). – Andreas Jun 28 '18 at 16:43

1 Answers1

0

Since you're using Spring, use JdbcTemplate and the queryForList method.

String sql = "SELECT EX FROM TS1.EXC WHERE EXT = ? AND EXR=1";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, date);

To learn more, read the Spring Documentation.

Andreas
  • 154,647
  • 11
  • 152
  • 247
  • what do you mean with the dataSource? I'm sorry for the question, I dont knew this method, tks!! – FranzSif Jun 28 '18 at 17:00
  • @FranzSif If you use Spring, then you should do database access using Spring, so **read the Spring documentation** to learn how, and/or follow some of their tutorials. --- Summary: Instead of creating [`Connection`](https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html) directly, define a [`DataSource`](https://docs.oracle.com/javase/8/docs/api/javax/sql/DataSource.html). Spring Boot has facilities to help with that too. You should also use the Spring transaction management. – Andreas Jun 28 '18 at 17:03