java - jpa 2.1 和新的 Date APi : YearMonth to Date Converter, MySQL 异常

Question

我是堆栈，我需要在 Java SE 8 客户端应用程序中使用 JPA 2.1 获取表 (MySQL) 中的条目，但我收到此异常:

[EL Warning]: 2015-05-18 13:57:15.882--UnitOfWork(974306870)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'YEAR_MONTH FROM app.INCOME WHERE (YEAR_MONTH = '2015-05-01 00:00:00')' at line 1
Error Code: 1064
Call: SELECT ID, AMOUNT, PERIOD, SOURCE, YEAR_MONTH FROM app.INCOME WHERE (YEAR_MONTH = ?)
bind => [1 parameter bound]
Query: ReadAllQuery(referenceClass=Income sql="SELECT ID, AMOUNT, PERIOD, SOURCE, YEAR_MONTH FROM app.INCOME WHERE (YEAR_MONTH = ?)")
Exception in thread "JavaFX Application Thread" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse     Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'YEAR_MONTH FROM app.INCOME WHERE (YEAR_MONTH = '2015-05-01 00:00:00')' at line 1
Error Code: 1064
Call: SELECT ID, AMOUNT, PERIOD, SOURCE, YEAR_MONTH FROM app.INCOME WHERE (YEAR_MONTH = ?)
bind => [1 parameter bound]
Query: ReadAllQuery(referenceClass=Income sql="SELECT ID, AMOUNT, PERIOD, SOURCE, YEAR_MONTH FROM app.INCOME WHERE (YEAR_MONTH = ?)")
at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:382)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)

这是代码

@Entity
@Table(schema="app")
public class Income implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;

    //partial: 2015-02      AUTO
    @Column(name="YEAR_MONTH")
    private YearMonth ymonth;

    private String source;

    private int amount;

使用此 JPQL 查询:

private static final String MONTH_INCOMES = "SELECT i FROM Income i WHERE i.ymonth = :month";
List<Income> sources= em.createQuery(MONTH_INCOMES, Income.class).setParameter("month", YearMonth.now()).getResultList();

我实现了这个转换器:YearMonth <-> Date

@Converter(autoApply = true)
public class YearMonthDateJPAConverter implements AttributeConverter<YearMonth, Date>{

@Override
public Date convertToDatabaseColumn(YearMonth entityValue) {
    return Date.from(entityValue.atDay(1).atStartOfDay(ZoneId.systemDefault()).toInstant());
}

@Override
public YearMonth convertToEntityAttribute(Date dbValue) {
    return YearMonth.from(dbValue.toInstant().atZone(ZoneId.systemDefault()).toLocalDate());
}

数据库为MySQL 5.6，表定义为:

CREATE TABLE `income` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `YEAR_MONTH` date NOT NULL,
  `SOURCE` varchar(100) NOT NULL,
  `AMOUNT` int(11) NOT NULL,
  `PERIOD` enum('once','daily','weekly','twoWeeks','twiceAmonth','fourweeks','monthly','twoMonths','ev3month','ev4month','twiceAyear','yearly','twoYear') NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

并且表是空的。这是怎么回事？？？？我什至不明白异常

Answer 1

解决方案:更改两列的名称:SOURCE、YEAR_MONTH。因为它们是MySQL的保留字。没有 Java 问题，只是列命名