2

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.

enter image description here

Kuroi
  • 63
  • 7

3 Answers3

2
  1. use getter/setter in your POJO class

  2. 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.)

  1. 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.

priyranjan
  • 674
  • 6
  • 15
  • 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. – Kuroi Nov 16 '20 at 19:08
  • 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 – priyranjan Nov 16 '20 at 20:58
  • company politics, the company say must be sql with JPA and no spring JDBCTemplate – Kuroi Nov 17 '20 at 18:50
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.

Sathiamoorthy
  • 8,831
  • 9
  • 65
  • 77
Luth Sanches
  • 191
  • 1
  • 3
0

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

Kuroi
  • 63
  • 7