'Springboot could not extract ResultSet

Currently I am getting a problem with fetching mysql data for my springboot project:

There was an unexpected error (type=Internal Server Error, status=500). could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

TestEntity.java

@Entity
public class TestEntity implements Serializable {

    @Id
    private int id;
    private String p1;
    private String p2;
    private String p3;

    public TestEntity() {
    }

    public TestEntity(int id, String p1, String p2, String p3){
        this.id = id;
        this.p1 = p1;
        this.p2 = p2;
        this.p3 = p3;
    }

    public int getId() {
        return id;
    }

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

    public String getP1() {
        return p1;
    }

    public void setP1(String p1) {
        this.p1 = p1;
    }

    public String getP2() {
        return p2;
    }

    public void setP2(String p2) {
        this.p2 = p2;
    }

    public String getP3() {
        return p3;
    }

    public void setP3(String p3) {
        this.p3 = p3;
    }



}

TestService.java

@Service
public class TestService {

    @Autowired
    private TestRepository testRepository;

    public ArrayList<TestEntity> getAllTestEntities(){
       ArrayList<TestEntity> list = new ArrayList();
       testRepository.findAll().forEach(list::add);
       return list;
    }

    public Optional getTestEntity(int id){
       return testRepository.findById(id);
    }
    public void addTestEntity(TestEntity t){
        testRepository.save(t);
    }
    public void removeTestEntity(int index){

        testRepository.deleteById(index);

    }

}

TestRepository.java

@Repository("mysql")
public interface TestRepository extends CrudRepository<TestEntity,Integer> {



}

TestController.java

@RestController
public class TestController {

    @Autowired
    private TestService testService;

    @RequestMapping("/test/AllUnits")
    public ArrayList<TestEntity> getAllTestUnits(){
        return testService.getAllTestEntities();
    }

    @RequestMapping("/test/{id}")
    public Optional getAllTestUnit(@PathVariable int id){
        return testService.getTestEntity(id);
    }

    @RequestMapping(method=RequestMethod.POST,value = "/test" )
    public void addTestUnit(@RequestBody TestEntity t){
        testService.addTestEntity(t);
    }

    @RequestMapping(method=RequestMethod.DELETE,value = "/test/{id}" )
    public void deleteTestUnit(@RequestBody Integer id){
        testService.removeTestEntity(id);
    }

    @RequestMapping("/test/welcome")
    public String welcome(){

        return "welcome to springboot";

    }


}

Edit: application.properties

cloud.aws.region.auto=true
cloud.aws.region.static=us-east-2

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://alyxdev.czcdgqfkwsnr.us-east-2.rds.amazonaws.com:3306/CryptoCurrency
spring.datasource.username=*******
spring.datasource.password=*******

I am able to get the /test/welcome mapping working so I believe my implementation of the service and controller is correct. So I am wondering if I made a mistake for accessing my database in my repository or should I use a JpaRepository instead of a CrudRepository and use an explicit query?

Edit Stack Trace: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'CryptoCurrency.test_entity' doesn't exist

javaspring-bootjpa


Solution 1:[1]

In you Entity class i.e. TestEntity.java, you need to specify which table that your referring to

@Entity
@Table(name="tbl_something")
public class TestEntity implements Serializable {

And use of CrudRepository would be fine for excessing the database. The application.properties file looks good to me.

Solution 2:[2]

I found the solution to the problem I was having apparently by renaming the table to all lowercase characters (test_table) in SQL and then using that table instead of Test_table Springboot was able to find that table and link map it to my entity class. I have no idea why it works this way. Maybe something to do with the Netbeans IDE I am using perhaps?

Solution 3:[3]

If annotate your entity class with @Table(name = "EmplyeeSalary") then JPA generates employee_salary as the table name. According to the naming convention at every uppercase found in the name, a new word will be generated with all lower case and will be separated from previous using _.

If you annotate your entity class like @Table(name = "AbcDefGhi") then JPA will look for abc_def_ghi table.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 suman heuju
Solution 2 Sinlesssc
Solution 3 Chinmay Biswal

Related Questions