Trouble Fetching Data from Database Using Spring Boot and Hibernate

Question

Question: I'm facing an issue while trying to fetch data from a MySQL database using Spring Boot and Hibernate. Despite configuring the application properties and repository interfaces, I'm receiving empty entities as results when I query the database using the findAll() method. Here's a summary of my setup:

Problem: I have a Spring Boot application that uses Hibernate for JPA. I've configured the application.properties file with the necessary database connection details and dialect. I've also created the entity class and the corresponding repository interface. However, when I use the findAll() method, I'm getting an array of empty entities.

Details:

1. I have a question entity class annotated with @Entity.
2. I've created the QuestionDao interface that extends JpaRepository<question, Integer>.
3. In application.properties, I've set the database connection properties including the URL, username, and password.
4. I'm using Hibernate with the MySQL dialect org.hibernate.dialect.MySQL8Dialect.
5. I've enabled DEBUG-level logging for Hibernate and Spring Boot to get more information.

`

# Entity

package com.boot.quizapp;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;

@Data
@Entity
public class Question {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE)
    private Integer id;
    
    private String questionTitle;
    private String option1;
    private String option2;
    private String option3;
    private String option4;
    private String rightAnswer;
    private String difficultylevel;
    private String category;

}

# DAO

package com.boot.quizapp.dao;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.boot.quizapp.Question;

@Repository
public interface QuestionDao extends JpaRepository<Question, Integer> {
}

# Service

package com.boot.quizapp.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.boot.quizapp.Question;
import com.boot.quizapp.dao.QuestionDao;

@Service
public class QuestionService {

    QuestionDao questionDao;
    
    @Autowired
    public QuestionService(QuestionDao questionDao) {
     this.questionDao = questionDao;
    }
    
    public List<Question> getAllQuestions() {
        return questionDao.findAll();
         
    }

}

# Controller

package com.boot.quizapp.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.boot.quizapp.Question;
import com.boot.quizapp.service.QuestionService;

@RestController
@RequestMapping("/question")
public class QuestionController {

    @Autowired
    QuestionService questionService;
    
    @GetMapping("/allQuestions")
    public List<Question> getAllQuestions() {
        return questionService.getAllQuestions();
        
    }

}

# application.properties

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/quizapp
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.show-sql=true

## Hibernate Properties

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
logging.level.org.hibernate.SQL=DEBUG

# Hibernate properties

spring.jpa.hibernate.ddl-auto=update

# Database Schema

-- Table: question
CREATE TABLE question (
    id INT PRIMARY KEY,
    question_title VARCHAR(255),
    option1 VARCHAR(255),
    option2 VARCHAR(255),
    option3 VARCHAR(255),
    option4 VARCHAR(255),
    right_answer VARCHAR(255),
    difficultylevel VARCHAR(255),
    category VARCHAR(255)
);


-- Sample Data
INSERT INTO question (id, category, difficultylevel, option1, option2, option3, option4, question_title, right_answer )
VALUES
    (1, 'Science', 'Easy', 'Option A', 'Option B', 'Option C', 'Option D', 'Sample Question 1', 'Option A');

Troubleshooting Steps Taken:

1. I've verified that there are records in the database table.
2. I've checked that the column names in the entity class match the table's column names.
3. I've tried changing the dialect to different versions of MySQL dialect.

Expected Outcome: I expect the findAll() method to retrieve data from the database and return a list of populated entity objects. However, I'm only getting an array of empty entities.

Console Output: When I enable DEBUG-level logging, I see a query being executed, but it seems that no data is being fetched.

Hibernate: select q1_0.id,q1_0.category,q1_0.difficultylevel,q1_0.option1,q1_0.option2,q1_0.option3,q1_0.option4,q1_0.question_title,q1_0.right_answer from question q1_0

[ {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {} ]

Could anyone please help me identify what might be causing this issue? Any suggestions for troubleshooting or correcting my configuration would be greatly appreciated.

Answer 1

I tried to replicate your code on my system and it worked.
github.com/AjinkyaKarode10/QuizzAppDemo/tree/main.

This looks like a Lombok plugin installation issue.
Normally arises when we use IDE's like Eclipse and SpringToolSuite.

It's better if we can manually add getters/setters in our Entity class.
OR Install the Lombok plugin by referring the site below.
Lombok is not generating getter and setter.

Question.class

import jakarta.persistence.*;
import lombok.Data;


@Entity
public class Question {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE)
    private Integer id;

    private String questionTitle;
    private String option1;
    private String option2;
    private String option3;
    private String option4;
    private String rightAnswer;
    private String difficultylevel;
    private String category;

    public Integer getId() {
        return id;
    }

    public String getQuestionTitle() {
        return questionTitle;
    }

    public String getOption1() {
        return option1;
    }

    public String getOption2() {
        return option2;
    }

    public String getOption3() {
        return option3;
    }

    public String getOption4() {
        return option4;
    }

    public String getRightAnswer() {
        return rightAnswer;
    }

    public String getDifficultylevel() {
        return difficultylevel;
    }

    public String getCategory() {
        return category;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public void setQuestionTitle(String questionTitle) {
        this.questionTitle = questionTitle;
    }

    public void setOption1(String option1) {
        this.option1 = option1;
    }

    public void setOption2(String option2) {
        this.option2 = option2;
    }

    public void setOption3(String option3) {
        this.option3 = option3;
    }

    public void setOption4(String option4) {
        this.option4 = option4;
    }

    public void setRightAnswer(String rightAnswer) {
        this.rightAnswer = rightAnswer;
    }

    public void setDifficultylevel(String difficultylevel) {
        this.difficultylevel = difficultylevel;
    }

    public void setCategory(String category) {
        this.category = category;
    }
}

Also, directly returning the results returned from database in Response is considered a bad practice.

Please map the DB results to an identical DTO object and return DTO object in Response.
Try mapstruct.

Thank you