CODEWITHSUNDEEP
spring-bootspring-data-jpa
wallwalker
wallwalker

Reputation: 621

Spring Data JPA : java.sql.SQLException: Column 'id' not found

Trying to return list from column named make causes error in console where column id is not found. The column named id is on the table but not sure why it is not recognized.

Car.java

 package com.example.demo;


import javax.persistence.Entity;
import javax.persistence.Id;


@Entity
public class Car {

    @Id
    private String id;

    private String make;
    private String model;
    private String year;


    public Car() {
    }

    public Car(String id, String make, String model, String year) {
        this.id = id;
        this.make = make;
        this.model = model;
        this.year = year;
    }

    public String getId() {
        return id;
    }

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

    public String getMake() {
        return make;
    }

    public void setMake(String make) {
        this.make = make;
    }

    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }

    public String getYear() {
        return year;
    }

    public void setYear(String year) {
        this.year = year;
    }
}

CarRepository.java

package com.example.demo;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface CarRepository extends JpaRepository<Car, String> {
    @Query(value="SELECT make FROM car", nativeQuery=true)
    List<Car> getAllMakes();
}

CarController.java

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;


@Controller
public class CarController {



    @Autowired
    private CarService carService;

    @Autowired
    private CarRepository carRepository;



    @GetMapping("/car")
    public String carForm(Model model) {
        model.addAttribute("car", new Car());
        model.addAttribute("cars", carService.getAllCars());
        return "car";
    }

    @PostMapping("/car")
        carService.addCar(car);
        return "redirect:/car";
    }



    @GetMapping("/list")
    public String carMakeList(Model model){
        model.addAttribute("list", carRepository.getAllMakes());
        return "list";
    }


}

list.html

<!DOCTYPE HTML>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <title></title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
</head>
<body>
<h1>Car List</h1>

<table>
    <thead>
    <tr>
        <th>Id</th>
        <th>Make</th>
        <th>Model</th>
        <th>Year</th>
    </tr>
    </thead>
    <tbody>
    <tr th:if="${!list}">
        <td colspan="2"> No Cars Available</td>
    </tr>

    <tr th:each="car : ${list}">

        <td><span th:text="${car.make}">Make</span></td>

    </tbody>
</table>
</body>

2019-08-30 10:12:31.008 ERROR 26538 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column 'id' not found.
2019-08-30 10:12:31.026 ERROR 26538 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [SELECT make FROM car]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query] with root cause

java.sql.SQLException: Column 'id' not found.

Upvotes: 0

Views: 5933

Answers (1)

V&#252;sal
V&#252;sal

Reputation: 2706

You need to specify *(all) in your SQL query: 

public interface CarRepository extends JpaRepository<Car, String> {
    @Query(value="SELECT * FROM car", nativeQuery=true)
    List<Car> getAllMakes();
}

The problem is id field is required for Car entity, you can also try: 

public interface CarRepository extends JpaRepository<Car, String> {
    @Query(value="SELECT id,make FROM car", nativeQuery=true)
    List<Car> getAllMakes();
}

For your current query to work, you can use JPA projections and change your code to: 

public interface CarRepository extends JpaRepository<Car, String> {
    @Query(value="SELECT make FROM car", nativeQuery=true)
    List<String> getAllMakes();
}

Upvotes: 6

Related Questions