如何修复"java.sql.SQLException:找不到列'id'." Spring Boot中的错误

问题描述

有2个实体:卡片组和卡片.每张纸牌包含100张纸牌中的8张.由于每个Deck包含多个Card，并且Card也可以是许多不同Decks的一部分，因此我通过创建一个名为deck_cards的新表来加入它们.

There are 2 entities: Deck and Card. Each Deck contains 8 out of 100 possible Cards. Since each Deck contains more than one Card and the Card can also be part of many different Decks, I joined them by creating a new table called deck_cards.

@Data
@Entity
@Table(name = "cards")
public class Card {
    @Id
    private Integer id;

    @NotBlank
    private String icon;

    @NotBlank
    private String name;

    @ManyToMany(fetch = FetchType.LAZY,
                cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
                },
                mappedBy = "cards")
    private Set<Deck> decks;
// setters and getters
}

@Data
@Entity
@Table(name="decks")
public class Deck {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @ManyToMany(fetch = FetchType.LAZY,
                cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
                })
    @JoinTable(name = "deck_cards",
            joinColumns = { @JoinColumn(name = "deck_id",
referencedColumnName = "id")},
            inverseJoinColumns = { @JoinColumn(name = "card_id",
referencedColumnName = "id")})
    private Set<Card> cards = new HashSet<>();
// setters and getters
}

@Repository
@Transactional(readOnly = true)
public class DeckRepositoryImpl implements DeckRepositoryCustom {

    @PersistenceContext
    EntityManager entityManager;

    @Override
    public Deck getDeckContaining(List<String> cardIds) {
        String queryStr =
                "select deck_id\n" +
                "from deck_cards\n" +
                // Hard-coding card_ids of interest for now to
                // see if the query itself works
                "where card_id in (1, 2, 3, 4, 5, 6, 7, 8)\n" +
                "group by deck_id\n" +
                "having count(*) = 8";
        Query query = entityManager.createNativeQuery(queryStr,
Deck.class);
    return (Deck) query.getSingleResult();
}

我有一个名为getDeckContaining()的自定义方法，该方法最初旨在接收List cardIds，并查看是否有一个包含存储在列表中的ID的卡片组.现在，我对卡的ID进行了硬编码，以在查询字符串中查找.即使那样，当我调用这样的方法时:

I have a custom-defined method named getDeckContaining(), which was originally designed to take in List cardIds and see if there's a deck containing the ids stored in the list. For now, I hard-coded the ids of cards to look for in the query string. Even then, when I invoke the method like this:

    List<String> teamCardIdsInString = new ArrayList<>();
    //....
    Deck deckOfInterest =
    deckRepository.getDeckContaining(teamCardIdsInString);

我收到以下错误消息:

2019-07-29 19:00:06.663  WARN 3409 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: S0022
2019-07-29 19:00:06.664 ERROR 3409 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column 'id' not found.
2019-07-29 19:00:06.690 ERROR 3409 --- [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 deck_id
from deck_cards
where card_id in (26000060, 28000017, 26000044, 26000039, 26000027, 28000015, 26000033, 28000000)
group by deck_id
having count(*) = 8]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query] with root cause

java.sql.SQLException: Column 'id' not found.
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.16.jar:8.0.16]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.16.jar:8.0.16]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.16.jar:8.0.16]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.16.jar:8.0.16]
        at com.mysql.cj.jdbc.result.ResultSetImpl.findColumn(ResultSetImpl.java:561) ~[mysql-connector-java-8.0.16.jar:8.0.16]
        at com.mysql.cj.jdbc.result.ResultSetImpl.getInt(ResultSetImpl.java:825) ~[mysql-connector-java-8.0.16.jar:8.0.16]
        at com.zaxxer.hikari.pool.HikariProxyResultSet.getInt(HikariProxyResultSet.java) ~[HikariCP-3.2.0.jar:na]
        at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:62) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
        at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
        at

我很困惑，因为我以为我使用了实体中定义的字段名称.有什么我可能会错过的东西吗?

I am confused since I thought I used the field names as defined in the entities. Is there something that I might have missed?

推荐答案

java.sql.SQLException:找不到列"id".问题出在列名.referencedColumnName = "id"是这里的可能犯罪嫌疑人(下)

java.sql.SQLException: Column 'id' not found. The issue is with the name of the column.referencedColumnName = "id" is the possible suspect here (below)

可能的解决方案:

• 删除referencedColumnName = "id"
• 由于您尚未按照数据库方案发布名称(并且也没有@Column)，因此请确保数据库中的id字段存在

• Remove referencedColumnName = "id"
• As you haven't posted name as per your DB scheme (and also there is no @Column) make sure that id field in your DB exist

这篇关于如何修复"java.sql.SQLException:找不到列'id'." Spring Boot中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！