java.sql.SQLSyntaxErrorException：未知列“street1”在“字段列表”中

Question

我有一个名为 tower 的 mysql 表，其中有一个字段 street1。我还有一个带有 street1 字段的 POJO。但是，我执行 JPA 查询时，出现异常“字段列表”中的未知列“street1”。我发现这个异常有趣的是 street1 存在于 POJO 和数据库表中。

异常

Exception [EclipseLink-4002] (Eclipse Persistence Services - 3.0.2.v202107160933): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Unknown column 'street1' in 'field list'
Error Code: 1054
Call: SELECT towerNumber, latitude, longitude, state, street1, street2, suburb, towerName, cityNumber, countryNumber, provinceNumber, siteNumber FROM tower ORDER BY towerName
Query: ReadAllQuery(referenceClass=Tower sql="SELECT towerNumber, latitude, longitude, state, street1, street2, suburb, towerName, cityNumber, countryNumber, provinceNumber, siteNumber FROM tower ORDER BY towerName")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:343)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:691)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2051)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:611)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:282)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:268)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:341)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:773)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2848)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2801)
    at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:584)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1236)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:912)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1195)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:485)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1283)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3008)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1844)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1826)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1791)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:262)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:482)
    at za.co.ezimax.dashboard.Database.findTowers(Database.java:82)
    at za.co.ezimax.dashboard.Map.<init>(Map.java:15)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
    at org.apache.webbeans.inject.InjectableConstructor.doInjection(InjectableConstructor.java:72)
    at org.apache.webbeans.portable.InjectionTargetImpl.newInstance(InjectionTargetImpl.java:201)
    at org.apache.webbeans.portable.InjectionTargetImpl.produce(InjectionTargetImpl.java:184)
    at org.apache.webbeans.portable.AbstractProducer.produce(AbstractProducer.java:134)
    at org.apache.webbeans.component.AbstractOwbBean.create(AbstractOwbBean.java:124)
    at org.apache.webbeans.component.ManagedBean.create(ManagedBean.java:66)
    at org.apache.webbeans.context.creational.BeanInstanceBag.create(BeanInstanceBag.java:76)
    at org.apache.webbeans.context.AbstractContext.getInstance(AbstractContext.java:159)
    at org.apache.webbeans.context.AbstractContext.get(AbstractContext.java:125)
    at org.apache.webbeans.intercept.NormalScopedBeanInterceptorHandler.getContextualInstance(NormalScopedBeanInterceptorHandler.java:101)
    at org.apache.webbeans.intercept.RequestScopedBeanInterceptorHandler.getContextualInstance(RequestScopedBeanInterceptorHandler.java:76)
    at org.apache.webbeans.intercept.NormalScopedBeanInterceptorHandler.get(NormalScopedBeanInterceptorHandler.java:71)
    at za.co.ezimax.dashboard.Map$$OwbNormalScopeProxy0.getTowers(za/co/ezimax/dashboard/Map.java)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at jakarta.el.BeanELResolver.getValue(BeanELResolver.java:88)
    at jakarta.el.CompositeELResolver.getValue(CompositeELResolver.java:62)
    at org.apache.myfaces.el.unified.resolver.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:179)
    at org.apache.el.parser.AstValue.getValue(AstValue.java:169)
    at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:190)
    at org.apache.webbeans.el22.WrappedValueExpression.getValue(WrappedValueExpression.java:68)
    at org.apache.myfaces.view.facelets.el.ContextAwareTagValueExpression.getValue(ContextAwareTagValueExpression.java:93)
    at jakarta.faces.component._DeltaStateHelper.eval(_DeltaStateHelper.java:357)
    at org.apache.myfaces.view.facelets.component.UIRepeat.getValue(UIRepeat.java:303)
    at org.apache.myfaces.view.facelets.component.UIRepeat.createDataModel(UIRepeat.java:226)
    at org.apache.myfaces.view.facelets.component.UIRepeat.getDataModel(UIRepeat.java:218)
    at org.apache.myfaces.view.facelets.component.UIRepeat._validateAttributes(UIRepeat.java:892)
    at org.apache.myfaces.view.facelets.component.UIRepeat.process(UIRepeat.java:1022)
    at org.apache.myfaces.view.facelets.component.UIRepeat.encodeChildren(UIRepeat.java:1816)
    at jakarta.faces.component.UIComponentBase.encodeAll(UIComponentBase.java:545)
    at jakarta.faces.component.UIComponentBase.encodeAll(UIComponentBase.java:554)
    at jakarta.faces.component.UIComponentBase.encodeAll(UIComponentBase.java:554)
    at org.apache.myfaces.view.facelets.FaceletViewDeclarationLanguage.renderView(FaceletViewDeclarationLanguage.java:1897)
    at org.apache.myfaces.application.ViewHandlerImpl.renderView(ViewHandlerImpl.java:315)
    at jakarta.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:73)
    at org.apache.myfaces.lifecycle.RenderResponseExecutor.execute(RenderResponseExecutor.java:122)
    at org.apache.myfaces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:266)
    at jakarta.faces.webapp.FacesServlet.service(FacesServlet.java:206)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:223)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
    at org.apache.openejb.server.httpd.EEFilter.doFilter(EEFilter.java:67)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.tomee.catalina.OpenEJBValve.invoke(OpenEJBValve.java:45)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:543)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.tomee.catalina.OpenEJBSecurityListener$RequestCapturer.invoke(OpenEJBSecurityListener.java:97)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:353)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:870)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1696)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'street1' in 'field list'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at jdk.proxy3/jdk.proxy3.$Proxy120.executeQuery(Unknown Source)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1015)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:651)
    ... 95 more

数据库 SQL

CREATE TABLE `tower` (
  `towerNumber` bigint NOT NULL AUTO_INCREMENT,
  `towerName` varchar(80) NOT NULL,
  `longitude` decimal(13,8) NOT NULL,
  `latitude` decimal(13,8) NOT NULL,
  `siteNumber` bigint NOT NULL,
  `street1` varchar(80) DEFAULT NULL,
  `street2` varchar(80) DEFAULT NULL,
  `suburb` varchar(80) DEFAULT NULL,
  `provinceNumber` bigint NOT NULL,
  `countryNumber` bigint NOT NULL,
  `cityNumber` bigint NOT NULL,
  `state` enum('planned','planted','installed','connected') DEFAULT 'planned',
  PRIMARY KEY (`towerNumber`),
  UNIQUE KEY `towerName` (`towerName`),
  KEY `siteNumber` (`siteNumber`),
  KEY `countryNumber` (`countryNumber`),
  KEY `provinceNumber` (`provinceNumber`),
  KEY `cityNumber` (`cityNumber`),
  CONSTRAINT `tower_ibfk_1` FOREIGN KEY (`siteNumber`) REFERENCES `site` (`siteNumber`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `tower_ibfk_2` FOREIGN KEY (`countryNumber`) REFERENCES `country` (`countryNumber`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `tower_ibfk_3` FOREIGN KEY (`provinceNumber`) REFERENCES `province` (`provinceNumber`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `tower_ibfk_4` FOREIGN KEY (`cityNumber`) REFERENCES `city` (`cityNumber`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

POJO 类

package za.co.ezimax.dashboard;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;

@Entity(name = "Tower")
@Table(name = "tower")
public class Tower {

    public enum State {
        planned, planted, installed, connected
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "towerNumber")
    private long towerNumber;

    @Column(name = "towerName")
    private String towerName;

    @Column(name = "longitude")
    private double longitude;

    @Column(name = "latitude")
    private double latitude;

    @Enumerated(EnumType.STRING)
    @Column(name = "state")
    private State state;

    @ManyToOne
    @JoinColumn(name = "countryNumber")
    private Country country;

    @ManyToOne
    @JoinColumn(name = "provinceNumber")
    private Province province;

    @ManyToOne
    @JoinColumn(name = "cityNumber")
    private City city;

    @ManyToOne
    @JoinColumn(name = "siteNumber")
    private Site site;

    @Column(name = "street1")
    private String street1;

    @Column(name = "street2")
    private String street2;

    @Column(name = "suburb")
    private String suburb;  
    
    public Tower() {
    }

    public long getTowerNumber() {
        return towerNumber;
    }

    public void setTowerNumber(long towerNumber) {
        this.towerNumber = towerNumber;
    }

    public String getTowerName() {
        return towerName;
    }

    public void setTowerName(String towerName) {
        this.towerName = towerName;
    }

    public double getLongitude() {
        return longitude;
    }

    public void setLongitude(double longitude) {
        this.longitude = longitude;
    }

    public double getLatitude() {
        return latitude;
    }

    public void setLatitude(double latitude) {
        this.latitude = latitude;
    }

    public State getState() {
        return state;
    }

    public void setState(State state) {
        this.state = state;
    }

    public Country getCountry() {
        return country;
    }

    public void setCountry(Country country) {
        this.country = country;
    }

    public Province getProvince() {
        return province;
    }

    public void setProvince(Province province) {
        this.province = province;
    }

    public City getCity() {
        return city;
    }

    public void setCity(City city) {
        this.city = city;
    }

    public Site getSite() {
        return site;
    }

    public void setSite(Site site) {
        this.site = site;
    }

    public String getStreet1() {
        return street1;
    }

    public void setStreet1(String street1) {
        this.street1 = street1;
    }

    public String getStreet2() {
        return street2;
    }

    public void setStreet2(String street2) {
        this.street2 = street2;
    }

    public String getSuburb() {
        return suburb;
    }

    public void setSuburb(String suburb) {
        this.suburb = suburb;
    }
    
}

来自 select * from information_schema.columns where table_name = 'tower' 的输出

TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_KEY,EXTRA,PRIVILEGES,COLUMN_COMMENT,GENERATION_EXPRESSION,SRS_ID
def,dashboard,tower,towerNumber,1,NULL,NO,bigint,NULL,NULL,19,0,NULL,NULL,NULL,bigint,PRI,auto_increment,"select,insert,update,references",,,NULL
def,dashboard,tower,towerName,2,NULL,NO,varchar,80,320,NULL,NULL,NULL,utf8mb4,utf8mb4_0900_ai_ci,varchar(80),UNI,,"select,insert,update,references",,,NULL
def,dashboard,tower,longitude,3,NULL,NO,decimal,NULL,NULL,13,8,NULL,NULL,NULL,"decimal(13,8)",,,"select,insert,update,references",,,NULL
def,dashboard,tower,latitude,4,NULL,NO,decimal,NULL,NULL,13,8,NULL,NULL,NULL,"decimal(13,8)",,,"select,insert,update,references",,,NULL
def,dashboard,tower,siteNumber,5,NULL,NO,bigint,NULL,NULL,19,0,NULL,NULL,NULL,bigint,MUL,,"select,insert,update,references",,,NULL
def,dashboard,tower,street1,6,NULL,YES,varchar,80,320,NULL,NULL,NULL,utf8mb4,utf8mb4_0900_ai_ci,varchar(80),,,"select,insert,update,references",,,NULL
def,dashboard,tower,street2,7,NULL,YES,varchar,80,320,NULL,NULL,NULL,utf8mb4,utf8mb4_0900_ai_ci,varchar(80),,,"select,insert,update,references",,,NULL
def,dashboard,tower,suburb,8,NULL,YES,varchar,80,320,NULL,NULL,NULL,utf8mb4,utf8mb4_0900_ai_ci,varchar(80),,,"select,insert,update,references",,,NULL
def,dashboard,tower,provinceNumber,9,NULL,NO,bigint,NULL,NULL,19,0,NULL,NULL,NULL,bigint,MUL,,"select,insert,update,references",,,NULL
def,dashboard,tower,countryNumber,10,NULL,NO,bigint,NULL,NULL,19,0,NULL,NULL,NULL,bigint,MUL,,"select,insert,update,references",,,NULL
def,dashboard,tower,cityNumber,11,NULL,NO,bigint,NULL,NULL,19,0,NULL,NULL,NULL,bigint,MUL,,"select,insert,update,references",,,NULL
def,dashboard,tower,state,12,planned,YES,enum,9,36,NULL,NULL,NULL,utf8mb4,utf8mb4_0900_ai_ci,"enum('planned','planted','installed','connected')",,,"select,insert,update,references",,,NULL
def,dashboard,tower,standNumber,13,NULL,YES,varchar,50,200,NULL,NULL,NULL,utf8mb4,utf8mb4_0900_ai_ci,varchar(50),,,"select,insert,update,references",,,NULL