Problems with native query spring data JPA returns empty JSON array with empty objects

Question

I'm learning spring 5 MVC with spring data JPA and sql queries

I'm trying to use a native query in my spring api restful example but when I run the project, my method returns an empty json object.

My Model class

@Entity
@Table(name = "bicycle")
public class Bicycle
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    
    @Column(name="name")
    private String name;
    
    @Column(name="year")
    private int year;
}

My repository class

@Repository
public interface BicycleRepository extends CrudRepository<Bicycle, Long>
{
    @Query(value = "SELECT * from db_bicycles.bicycle", nativeQuery = true)
    public List<Bicycle> obtenerTodos();
}

My controller class

@RestController
@RequestMapping("/api")
public class BicycleController
{
    @Autowired
    private BicycleRepository bicycleRepository;
    
    //create get all bicycles
    @GetMapping("/bicycles")
    public List<Bicycle> getAllBicycles()
    {
        return bicycleRepository.obtenerTodos();
    }   
}

My application properties

spring.datasource.url=jdbc:mysql://localhost/db_bicycles?useUnicode=true&useJDBCCompilantTimeZoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql=true

This table have 2 rows.

In your Bicycle class , is there any getter and setter ? If not try adding getter and setter and try again. — Shawrup
– Shawrup, Commented Nov 13, 2020 at 4:25
Yes, for some reason STS only make me setters and no getters, thanks. — Kuroi
– Kuroi, Commented Nov 13, 2020 at 17:07

priyranjan · Accepted Answer · 2020-11-15 18:04:03Z

2

use getter/setter in your POJO class
you need not to write query to retrieve all record. Simply you can make use of findAll() method which is available in crudeRepository.

bicycleRepository.findAll(); (will return list of your POJO class.)

make sure, there is data present in table. if not then make use of Setter/Construction

Injection to populate data. OR you can make a POST call to populate data in table using save() method.

answered Nov 15, 2020 at 18:04

priyranjan

7146 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kuroi Over a year ago

I cant use hibernate like orm (mapping foreign key, etc). The tables often changes and a native query helps to avoid crashes, simply replace the sql query and the result is the same like the class I create.

priyranjan Over a year ago

Not agree, what you had shared here a/c that I explained. If table often changes then we will have lots of approach. Anyways it's up to you how you gonna deal with your code

Kuroi Over a year ago

company politics, the company say must be sql with JPA and no spring JDBCTemplate

Sathiamoorthy · Accepted Answer · 2021-05-15 11:14:31Z

1

If you are using lombok and Spring Tools Suite in your project, then try running Maven Clean and Maven Install.

mvn clean install

After that, you can run the project. It should work.

edited May 15, 2021 at 11:14

Sathiamoorthy

12.1k12 gold badges78 silver badges92 bronze badges

answered May 15, 2021 at 1:57

Luth Sanches

4014 silver badges3 bronze badges

Comments

Kuroi · Accepted Answer · 2020-11-13 17:10:08Z

0

The solution is to add the getter and setters, eclipse (STS) only add me the setters

answered Nov 13, 2020 at 17:10

Kuroi

631 silver badge7 bronze badges

Collectives™ on Stack Overflow

Problems with native query spring data JPA returns empty JSON array with empty objects

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related