0

I'm new to Spring Framework and having troubles retrieving a native query result from my application. The application is supposed to retrieve some specific data from my PostgreSQL database and expose it as a REST resource. However, it seems not to be able to map the query results into the entity object i defined. The object is defined the same way the database is made: i've also used the same entity for the insert operation on my logger application.

I've created an Entity class:

package com.umeter.rest;

import ...

@Entity
@Table(name="dati_impianti")
public class DatiImpiantoEntity {
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Integer id_lettura;

    @Column(name="sigla_impianto")
    private String sigla_impianto;
    @Column(name="timestamp")
    private Date timestamp;
    @Column(name="lettura")
    private Float lettura;
    @Column(name="stato")
    private Integer stato;

    public Integer getId_lettura() {
        return id_lettura;
    }

    public void setId_lettura(Integer id_lettura) {
        this.id_lettura = id_lettura;
    }

    public String getSigla_impianto() {
        return sigla_impianto;
    }

//other Getters and Setters

A Repository class (with a random nativequery just to try it out).

EDIT: Yes, using the native query is needed because i will be using some native functionality from postgresql that are not available through the normal CRUD methods available. However, this seems not to be the issue as i tried with a "findAll()" method just to get the exact same [null] output

package com.umeter.rest;

import ...

@Repository
public interface DatiRepository extends CrudRepository<DatiImpiantoEntity, Integer> {

    @Query(nativeQuery = true, value = "SELECT * from dati_impianti LIMIT 10")
    public List<DatiImpiantoEntity> getIstantanea();
}

And a REST Controller:

package com.umeter.rest;

import ...

@RestController
@RequestMapping(path="/api")
public class DatiRestController {

    @Autowired
    private DatiRepository datiRepository;


    @GetMapping(path="/istantanea")
        public @ResponseBody List<DatiImpiantoEntity> getIstantanea() {
            return datiRepository.getIstantanea();
    }
}

However, the result i'm getting is a list of null values (i suppose Spring isn't able to map the query results into my Entity object):

[null,null,null,null,null,null,null,null,null,null]

I'm getting an output when i set the method with a generic "Object" return type (which is excluding a database connection or query problem). However, what i get is not a correct Json output format, and i know this is now how it's supposed to be:

[["MF1","2019-10-11T10:24:23.917+0000",3.814,6,null],["MF1","2019-10-11T10:24:33.867+0000",3.814,6,null],["MF1","2019-10-11T10:24:43.920+0000",3.814,6,null],["MF1","2019-10-11T10:24:53.884+0000",3.814,6,null],["MF1","2019-10-11T10:25:03.864+0000",3.814,6,null],["MF1","2019-10-11T10:25:13.869+0000",3.814,6,null],["MF1","2019-10-11T10:25:23.872+0000",3.814,6,null],["MF1","2019-11-08T13:17:35.175+0000",5511.1,0,null],["MF2","2019-11-08T13:17:35.174+0000",6567.025,0,null],["MF1","2019-11-08T13:25:25.655+0000",5548.027,0,null]]

For completeness purposes, i attach a picture of the database data structure (the id seems not to be really used but it was required by Spring for the insertion process): Database Structure

Mastarius
  • 305
  • 3
  • 13
  • Have you configured a JSON object mapper? Like [here](https://stackoverflow.com/a/32842962/1600632)? – Harihar Das Dec 26 '19 at 10:56
  • @HariharDas i haven't tried thoroughly (and it's not included on my current sources), but this does not seem to be an issue considering that it's working without on Spring docs examples. I can give it another try, but when i did, i only got the same "null" outputs but with {} brackets instead – Mastarius Dec 26 '19 at 11:01
  • Can you please also override the `toString` method of your `DatiImpiantoEntity`? e.g. return the `timestamp` field from your overridden `toString` method. – Harihar Das Dec 26 '19 at 11:05
  • I did that on a previous test, with no results. Also correct me if i am wrong, but i suppose what we want it's not a string representation of the "class" but more like a mapping into the object (where the json output will be made of the toString method from all the fields and not from the class itself) – Mastarius Dec 26 '19 at 11:07
  • Sorry, I was trying to see if it was a problem with logging to the console. But seems like you added the output of the controller method to the question. Which is indeed a list of null values. – Harihar Das Dec 26 '19 at 11:10
  • One more thing. Do you have an empty constructor in your entity class? – Harihar Das Dec 26 '19 at 11:11
  • I had the constructor, yes, with no changing: i tried both with the empty constructor and with a full constructor with all variables – Mastarius Dec 26 '19 at 11:21
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/204878/discussion-between-harihar-das-and-mastarius). – Harihar Das Dec 26 '19 at 11:24

2 Answers2

2

The database table dati_impianti has null id value for all records. This will not work with CrudRepository. The id field must have unique values across all records.

Harihar Das
  • 484
  • 3
  • 11
0

You can directly use the entity object rather using native query

 @Query(nativeQuery = true, value = "SELECT * from dati_impianti LIMIT 10")
    public List<DatiImpiantoEntity> getIstantanea();

replace this with 

@Query("SELECT * from DatiImpiantoEntity LIMIT 10")
    public List<DatiImpiantoEntity> getIstantanea();
Seshidhar G
  • 265
  • 1
  • 9
  • I am sorry, but this does not seem to work (it gives a compile-time error because of the query validation) – Mastarius Dec 26 '19 at 10:59
  • Ok, then try changing the query like `SELECT d from DatiImpiantoEntity d LIMIT 10` i mean to use aliases. You can refer [here](https://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-introduction-to-query-methods/) – Seshidhar G Dec 26 '19 at 11:06