Why getting "unknown column" error when retrieving mysql view in Spring Boot?

Question

I'm trying to execute a mysql view within Spring Boot but getting the following error:

java.sql.SQLSyntaxErrorException: Unknown column 'appointmen0_.lab_collected' in 'field list'

But clearly the lab_collected variable exists in the domain bean. Does the column name have to be the same thing? I want it to be displayed like "LabCollected", not "lab_collected".

Here is the output from the view, just to show you that it works:

mysql> select * from vw_appointments;

+----+------------+-------------+---------------------+-------------------+
| id | Date       | Physician   | LabCollected        | Note              |
+----+------------+-------------+---------------------+-------------------+
|  1 | 10/29/2010 | CAMPBELL, J | 2010-10-29 11:09:00 | no note available |
+----+------------+-------------+---------------------+-------------------+

Here is the domain bean:

package net.tekknow.medaverter.domain;

import java.io.Serializable;
import java.sql.Timestamp;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.Size;

@Entity
@Table(name = "vw_appointments")
//Prevent changes from being applied by Hibernate
@org.hibernate.annotations.Immutable
public class AppointmentView implements Serializable {
    // Identifier. Has to be Integer as you implement JpaRepository<AppointmentView,Integer>
    @Id
    private Integer id;

    public Integer getId() {
        return this.id;
    }

    @Size(max = 32)
    @Column(name="Date")
    public String date;

    @Column(name="Physician")
    public String physician;

    @Column(name="LabCollected")
    public Timestamp lab_collected;

    @Column(name="Note")
    public String note;

    public String getDate() {
        return date;
    }
    public String getPhysician() {
        return physician;
    }
    public Timestamp getLabCollected() {
        return lab_collected;
    }
    public String getNote() {
        return note;
    }
}

Here is the controller:

@RestController
public class AppointmentViewController {

    @Autowired 
    AppointmentViewService appointmentViewService;

    @CrossOrigin
    @GetMapping("/appointment_view")
    public List<AppointmentView> viewAppointmentsPage(Model model) {
        List<AppointmentView> appointments = appointmentViewService.listAll();
        return appointments;
    }   
}

Here is the service:

@Service
@Transactional
public class AppointmentViewService {

    @Autowired
    AppointmentViewRepository repo;

    public List<AppointmentView> listAll() {
        return repo.findAll();
    }      
}

Here is the repository:

public interface AppointmentViewRepository extends JpaRepository<AppointmentView,Integer> {}

Any suggestions?

Rename field `lab_collected` to `labCollected`, so it fits with the getter method name. — Andreas, Apr 30 '20 at 03:15
That's the way I had it originally. I changed it to lab_collected in order to try to resolve the error message. — user3217883, Apr 30 '20 at 14:22

score 0 · Answer 1 · answered Apr 30 '20 at 05:18

0

If you provide @Column explicitly, that name should be taken. Try adding spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.EJB3NamingStrategy in application.properties file. Please refer answer : Spring Boot + JPA : Column name annotation ignored

answered Apr 30 '20 at 05:18

Jerin D Joy

750
6
11

spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.EJB3NamingStrategy had no effect. Same error. – user3217883 Apr 30 '20 at 19:41
I got the same error, but the wierd thing is... it just started to happen out of the blue. i have not changed the repository or the database. I was litterly going onto the page that has that error now, and then without changing anything *BOOM* unknown column t... Why would this happen? – Jp Silver Nov 18 '20 at 10:08

Why getting "unknown column" error when retrieving mysql view in Spring Boot?

1 Answers1