Getting Null Values in Spring Native Query Project

Question

This is my Data Repository file and i used native query to retrieve all data address(Locations) of Data. I called the function using Postman and I got null outputs of locations. This my first time of using Native query and its really impossible to solve these errors

DataRepository

public interface DataRepository extends JpaRepository<Data, Long> {

    @Query(value = "SELECT dataAddress FROM Data")
    List<DataProject> getDataAddress();
}

DataServiceImpl

    public List<DataProject> getDataAddress() {
        return dataRepository.getDataAddress();
    }

DataService

List<DataProject> getDataAddress();

DataModel

@Entity
@Table(name = "CCCData")
public class Data {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long dataId;

    @Column(name = "DATA_NAME")
    private String dataName;

    @Column(name ="DATA_ADDRESS")
    private String dataAddress;

    @Column(name = "DATA_DESC")
    private String dataDesc;

    @CreationTimestamp
    private Date dateOfCreated;

    @CreationTimestamp
    private Date dateOfUpdated;

    public long getDataId() {
        return dataId;
    }

    public void setDataId(long dataId) {
        this.dataId = dataId;
    }

    public String getDataName() {
        return dataName;
    }

    public void setDataName(String dataName) {
        this.dataName = dataName;
    }

    public String getDataAddress() {
        return dataAddress;
    }

    public void setDataAddress(String dataAddress) {
        this.dataAddress = dataAddress;
    }

    public String getDataDesc() {
        return dataDesc;
    }

    public void setDataDesc(String dataDesc) {
        this.dataDesc = dataDesc;
    }

    public Date getDateOfCreated() {
        return dateOfCreated;
    }

    public void setDateOfCreated(Date dateOfCreated) {
        this.dateOfCreated = dateOfCreated;
    }

    public Date getDateOfUpdated() {
        return dateOfUpdated;
    }

    public void setDateOfUpdated(Date dateOfUpdated) {
        this.dateOfUpdated = dateOfUpdated;
    }

DataProjection

public interface DataProject {

    String getDataAddress();
}

DataController

@GetMapping("/data/locations")
    public List<DataProject> getDataAddress() {
        return dataService.getDataAddress();
    }

Postman Output

[
    {
        "dataAddress": null
    },
    {
        "dataAddress": null
    },
    {
        "dataAddress": null
    },
    {
        "dataAddress": null
    }
]

score 1 · Accepted Answer · 2020-07-08T06:38:38.940

1

Spring won't return you only address using below query. It still return you DATA object

public interface DataRepository extends JpaRepository<Data, Long> {

    @Query(value = "SELECT dataAddress FROM Data")
    List<DataProject> getDataAddress();
}

for fetching only DataAddress you need to create a constructor inside Data model for DataAddress only

public Data(String dataAddress) {
    this.dataAddress = dataAddress;
}

and your query will look like this:

public interface DataRepository extends JpaRepository<Data, Long> {

    @Query(value = "SELECT new Data(dataAddress) FROM Data")
    List<DataProject> getDataAddress();
}

Update 1 :

if you need this for other fields with same datatype and then above 'constructor' based method fails. There are some other alternatives:

You can fetch DATA object and use java stream map function to extract only 1 field. data.stream().map((data) -> data.getDataAddress()).collect(Collectors.toList())

You can use native SQL query to fetch only required fields.
@Query(value = "SELECT d.data_address FROM CCCData d", nativeQuery=true)

edited Jul 08 '20 at 06:38

answered Jul 07 '20 at 09:16

you can create as many constructor in `DATA` class. Make sure to create a blank constructor also. – Jul 07 '20 at 09:17
Thank you for your great advises and my problem is solved. Thank You again!! – chamikamac Jul 07 '20 at 09:58
Hello Aman Garg, What I need to do when i wanted to get only another value like "dataName". I need two functions. 1). to get dataAddress, 2). to get dataName – chamikamac Jul 08 '20 at 04:00
that's not possible, your constructor will conflict. create a different constructor. – Jul 08 '20 at 04:07
Thank you and again i'm getting null values – chamikamac Jul 08 '20 at 05:57
`public CCCData(String dataAddressProvince) { this.dataAddressProvince = dataAddressProvince; } public CCCData() { } ` – chamikamac Jul 08 '20 at 05:57
` @Query(value = "SELECT d.DATA_ADDRESS_PROVINCE FROM CCCData d", nativeQuery = true) List getDataAddressProvince(); ` – chamikamac Jul 08 '20 at 05:58
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/217430/discussion-between-chamikamac-and-aman-garg). – chamikamac Jul 08 '20 at 06:00

Getting Null Values in Spring Native Query Project

1 Answers1