CODEWITHSUNDEEP
javaspringhibernatejpaspring-boot
Doronz
Doronz

Reputation: 704

Hibernate @OneToMany / @ManyToOne Column not found

I am getting a Column Not Found error when trying to call the REST API for my web app using Spring-Boot, Hibernate and the CrudRepository interface.

I have two entities, a News Source and a News Item. The News Source (e.g.: Abc News) has many News Items.

@Entity
@Table(name="newsitem")
public class NewsItem

@ManyToOne
private NewsSource newsSource;

and

@JsonIgnoreProperties(ignoreUnknown=true)
@Entity
@Table(name="newssource")
public class NewsSource

@OneToMany(mappedBy = "newsSource", cascade = CascadeType.ALL)
private List<NewsItem> newsItems;

Both my tables are populated correctly as can be verified in my H2 Console.

The relationship annotations added a column in my NewsItem table called Newssource_id. However, it was not populated automatically. So I added code to assign it when the items are fetched from external API.

 public Future<List<NewsItem>> fetchAllNewsItemsFromApi(){
    List<NewsSource> sources = newsSourceDao.fetchAllNewsSources();
    List<NewsItem> newsItems = new ArrayList<>();
    for (NewsSource source : sources) {
        String request = "https://newsapi.org/v1/articles?apikey=" + NEWSAPI_API_KEY + "&source=" + source.getId();
        ResponseEntity<NewsItemResponse> newsItemResponse =
                restTemplate.exchange(request,
                        HttpMethod.GET, null, NewsItemResponse.class);
        NewsItemResponse response = newsItemResponse.getBody();
        List<NewsItem> itemsFromSource = response.getNewsItemList();
        for (NewsItem item : itemsFromSource){
            item.setNewsSource(source);
        }
        newsItems.addAll(itemsFromSource);
    }
    return new AsyncResult<>(newsItems);
}

I had to create two additional response classes as the NewsApi JSON returns metadata in addition to just the articles / sources.

public class NewsItemResponse {
    @JsonProperty("articles")
    private List<NewsItem> newsItemList;

and

public class NewsSourceResponse {
    @JsonProperty("sources")
    private List<NewsSource> sourceList;

I've set up a NewsItemRepository like so:

public interface NewsItemRepository extends CrudRepository<NewsItem, Long>{}

And my http://localhost:8080/api/v1 looks like this: 

{
  "_links" : {
    "newsItems" : {
      "href" : "http://localhost:8080/api/v1/newsItems"
    },
    "profile" : {
      "href" : "http://localhost:8080/api/v1/profile"
    }
  }
}

When I navigate to http://localhost:8080/api/v1/newsItems I get a 500 Error.

In browser: 

could not prepare statement; SQL [select newsitem0_.id as id1_0_, newsitem0_.version as version2_0_, newsitem0_.author as author3_0_, newsitem0_.date as date4_0_, newsitem0_.news_source_id as news_sou9_0_, newsitem0_.summary as summary5_0_, newsitem0_.title as title6_0_, newsitem0_.url as url7_0_, newsitem0_.url_to_image as url_to_i8_0_ from newsitem newsitem0_]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

In IntelliJ:

org.h2.jdbc.JdbcSQLException: Column "NEWSITEM0_.NEWS_SOURCE_ID" not found; SQL statement:
select newsitem0_.id as id1_0_, newsitem0_.version as version2_0_, newsitem0_.author as author3_0_, newsitem0_.date as date4_0_, newsitem0_.news_source_id as news_sou9_0_, newsitem0_.summary as summary5_0_, newsitem0_.title as title6_0_, newsitem0_.url as url7_0_, newsitem0_.url_to_image as url_to_i8_0_ from newsitem newsitem0_ [42122-191]

Sample row from my NewsItem table

Sample row from my NewsSource table

NewsApi.org Api

Upvotes: 1

Views: 2493

Answers (2)

SkyWalker
SkyWalker

Reputation: 29168

NEWSITEM0_.NEWS_SOURCE_ID and Given table's last item NEWSSOURCE_ID is different. Make it same.

Hope it will solve your issue.

Upvotes: 0

Maciej Kowalski
Maciej Kowalski

Reputation: 26572

It seems that the framework tries to add additional underscore based on the camelcase. This seems to be against the default JPA behaviour, which should only add the underscore between the relationship name and foreign id.

Anyway.. try to explicitly define the join column:

@ManyToOne
@JoinColumn(name="newssource_id")
private NewsSource newsSource;

Upvotes: 1

Related Questions