[FIXED] Hibernate JPA "findAll()" Returning only One Row (Spring Boot 2.3.7)

Issue

I am trying to fetch some data from a table and perform some operations on it. But somehow it just returns one row over and over again.

Here's my Repository class:

package com.gtt.wcas.device.db.repository;

import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.gtt.wcas.device.db.jpa.DeviceDetails;


@Repository
public interface DeviceDetailsRepository extends PagingAndSortingRepository<DeviceDetails, String> {

}

My Entity Class is like:

package com.gtt.wcas.device.db.jpa;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "DEVICE_DETAILS")
public class DeviceDetails {

    @Column(name = "ConnectionType", nullable = false)
    private Integer connectionType = -1;
    
    @Column(name = "CustomerGRUA", nullable = false)
    private String customerGRUA;

    @Id
    @Column(name = "DeviceName", nullable = false)
    private String deviceName;

    @Column(name = "DeviceState", nullable = false)
    private Integer deviceState = -1;

    @Column(name = "Vendor", nullable = false)
    private String vendor;


    /**
     * Default Constructor
     */
    public DeviceDetails() {

    }


    /**
     * @return the connectionType
     */
    public Integer getConnectionType() {
        return connectionType;
    }


    /**
     * @param connectionType the connectionType to set
     */
    public void setConnectionType(Integer connectionType) {

        if (connectionType == null) {
            this.connectionType = -1;
        } else {
            this.connectionType = connectionType;
        }
    }
    
    .
    .
    .
    Other Getters and Setters.
    .
    .
    .
}

My Service Class is like:

package com.gtt.wcas.device.db.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import java.util.stream.StreamSupport;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import com.gtt.wcas.device.db.jpa.DeviceDetails;
import com.gtt.wcas.device.db.repository.DeviceDetailsRepository;

@Service("deviceDetailsService")
public class DeviceDetailsService {

    Logger logger = LoggerFactory.getLogger(DeviceDetailsService.class.getName());

    private DeviceDetailsRepository deviceDetailsRepository;

    /**
     * Constructor
     * @param deviceDetailsRepository
     */
    @Autowired
    public DeviceDetailsService (DeviceDetailsRepository deviceDetailsRepository) {
        this.deviceDetailsRepository = deviceDetailsRepository;
    }


    /**
     * Fetch all the columns in DEVICE_DETAILS table.
     * @return List<DeviceDetails>
     */
    public List<DeviceDetails> getAllDeviceDetails() {

        List<DeviceDetails> deviceDetails = new ArrayList<DeviceDetails>();

        logger.debug("Searching for all device in the table.");

        Iterable<DeviceDetails> deviceDetailsIterator = deviceDetailsRepository.findAll(Sort.by("customerGRUA"));

        deviceDetails = StreamSupport.stream(deviceDetailsIterator.spliterator(), false).collect(Collectors.toList());

        logger.debug("Fetched all device in the table.");

        return deviceDetails;
    }
}

My Controller Class:

@RestController
public class DeviceConnectionController {

    @Autowired
    @Resource(name = "deviceDetailsService")
    DeviceDetailsService deviceDetailsService;

    Logger logger = LoggerFactory.getLogger(DeviceConnectionController.class.getName());


    @RequestMapping(value = "/deviceConnection", method = RequestMethod.GET)
    public ResponseEntity<String> deviceConnection(@RequestParam(value = "verify", defaultValue = "false") String verify, @RequestParam(value = "deviceName") String deviceName) {

        boolean verification = Boolean.valueOf(verify);

        deviceConnectionDetails = DeviceDetailsDB.getDeviceDetails(deviceName, deviceDetailsService);
            
        .
        .
        .
    }
    
    .
    .
    .
    .
}

And finally the class that the problem shows up:

public class DeviceDetailsDB {

    /**
     * Get details stored for the specified devicename
     *
     * @param deviceName
     * @param deviceDetailsService
     */
    public static DeviceConnectionDetails getDeviceDetails(String deviceName, DeviceDetailsService deviceDetailsService) {

        DeviceConnectionDetails deviceConnectionDetails = new DeviceConnectionDetails(deviceName);

        List<DeviceDetails> deviceDetailsList = deviceDetailsService.getAllDeviceDetails();

        for (DeviceDetails deviceDetails : deviceDetailsList) {
            System.out.println("Device Details: " + deviceDetails.getDeviceName() + "\t GRUA: " + deviceDetails.getCustomerGRUA());
        }

        DeviceDetails deviceDetails = deviceDetailsService.getDeviceDetailsbyID(deviceName);
        
        .
        .
        .
        .
    }
    
    .
    .
    .
}

And the application.properties file:

#datasource configurations
spring.datasource.url=THE URL
spring.datasource.username=USERNAME
spring.datasource.password=PASSWORD
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
# Enabling H2 Console
spring.h2.console.enabled=true
# Custom H2 Console URL
spring.h2.console.path=/h2-console
spring.jpa.hibernate.ddl-auto=update
# DDL generation
spring.jpa.generate-ddl=true

But the for loop above just regurgitates only one line over and over again, rather than printing all the rows in the table. The table has more than 200 rows in it.

The output is like:

Device Details:      GRUA: 0070
Device Details:      GRUA: 0070
Device Details:      GRUA: 0070
Device Details:      GRUA: 0070
Device Details:      GRUA: 0070
Device Details:      GRUA: 0070
.
.
.
.
.
and so on.

Is it something I am doing wrong? I am guessing its some problem with Service class. But since this is the first time for me, not sure where to look.

Thanks in advance.

Solution

I believe that there could be a problem with the DEVICE_DETAILS table.

According to the Entity Class, "DeviceName" is the primary key of DeviceDetails. Therefore, it must be unique among the devices. I wonder if this constraint is satisfied in your data. Is the primary key condition defined in the schema?

It should look something like this:

create table device_details (
    connection_type int not null,
    customergrua varchar not null,
    device_name varchar primary key not null,
    device_state int not null,
    vendor varchar not null
);

In case the device names are not unique, you should choose another primary key (@Id) or create a dummy one.

Ref: Spring Data JPA query return repeated row instead of actual data, why?

Answered By - paulfischer
Answer Checked By - Robin (JavaFixing Admin)