com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'day0_.calendar_id' in 'field list'
23,136
Your entity model does not match the database schema.
The entity model defines an association Day -> Calendar like this:
@Entity
@Table(name = "days")
public class Day implements Serializable {
@ManyToOne
@JoinColumn(name="calendar_id")
private Calendar calendar;
}
but in the database, the foreign key column is called calendars_calendar_id, not calendar_id.
Author by
Flojoe
Updated on January 07, 2020Comments
-
Flojoe over 4 years
I already found multiple questions that were very similar to mine, but sadly none of the solutions helped me.
I need a many to one relation between the table Calendar and Day. A calendar can have multiple days.
here my code, thanks for looking at it:
Calendar class:
@Entity @Table(name="calendars") @NamedQuery(name="Calendar.findAll", query="SELECT c FROM Calendar c") public class Calendar implements Serializable { private static final long serialVersionUID = 1L; @Id @Column(name="calendar_id") @GeneratedValue(strategy=GenerationType.IDENTITY) private int calendarId; private String background; @Temporal(TemporalType.DATE) private Date begindate;CalendarBean:
@EJB CalendarEJB calendarEJB; private static final long serialVersionUID = 1L; private String calendarName; private int calendarID; public String getCalendarName() { return calendarName; } public void setCalendarName(String calendarName) { this.calendarName = calendarName; } public int getCalendarID() { return calendarID; } public void setCalendarID(int calendarID) { this.calendarID = calendarID; }Day class:
@Entity @Table(name="days") @NamedQuery(name="Day.findAll", query="SELECT d FROM Day d") public class Day implements Serializable { private static final long serialVersionUID = 1L; @Id private int iddays; private int calendars_calendar_id; //bi-directional many-to-one association to Calendar @ManyToOne @JoinColumn(name="calendar_id") private Calendar calendar; public int getCalendars_calendar_id() { return calendars_calendar_id; } public void setCalendars_calendar_id(int calendars_calendar_id) { this.calendars_calendar_id = calendars_calendar_id; }DayBean:
@EJB DayEJB dayEJB; private static final long serialVersionUID = 1L; private int iddays; private int calendars_calendar_id; private CalendarBean calendarb = new CalendarBean(); public int getIddays() { return iddays; } public void setIddays(int iddays) { this.iddays = iddays; } public int getCalendars_calendar_id() { return calendars_calendar_id; } public void setCalendars_calendar_id(int calendars_calendar_id) { this.calendars_calendar_id = calendars_calendar_id; } public List<Day> allDays(){ return dayEJB.listAllDays(); }mysql:
CREATE TABLE IF NOT EXISTS `mydb`.`calendars` ( `calendar_id` INT(5) NOT NULL AUTO_INCREMENT, `calendarname` VARCHAR(45) NULL DEFAULT NULL, `background` BLOB NULL DEFAULT NULL, `begindate` DATE NULL DEFAULT NULL, `public` TINYINT(1) NULL DEFAULT NULL, PRIMARY KEY (`calendar_id`), UNIQUE INDEX `calender_id_UNIQUE` (`calendar_id` ASC)) ENGINE = InnoDB AUTO_INCREMENT = 121 DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `mydb`.`days` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`days` ( `iddays` INT(10) NOT NULL AUTO_INCREMENT, `date` VARCHAR(45) NULL DEFAULT NULL, `text` VARCHAR(450) NULL DEFAULT NULL, `link` VARCHAR(45) NULL DEFAULT NULL, `calendars_calendar_id` INT(5) NOT NULL, PRIMARY KEY (`iddays`), INDEX `calendar_id_idx` (`calendars_calendar_id` ASC), CONSTRAINT `calendar_id` FOREIGN KEY (`calendars_calendar_id`) REFERENCES `mydb`.`calendars` (`calendar_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB AUTO_INCREMENT = 8 DEFAULT CHARACTER SET = utf8;i changed @JoinColumn(name="calendar_id") to the foreign key name but error stayed so i renamed the foreign key to this value also no change. If i rename the @JoinColumn(name="calendar_id") to calendars_calendar_id wildfly can not deploy at all.
console:
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:89) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.loader.Loader.getResultSet(Loader.java:2065) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.loader.Loader.doQuery(Loader.java:909) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.loader.Loader.doList(Loader.java:2553) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.loader.Loader.doList(Loader.java:2539) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.loader.Loader.list(Loader.java:2364) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:496) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:231) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573) [hibernate-entitymanager-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449) [hibernate-entitymanager-4.3.5.Final.jar:4.3.5.Final] ... 133 more Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'day0_.calendar_id' in 'field list' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.8.0_25] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) [rt.jar:1.8.0_25] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.8.0_25] at java.lang.reflect.Constructor.newInstance(Constructor.java:408) [rt.jar:1.8.0_25] at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) at com.mysql.jdbc.Util.getInstance(Util.java:360) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2030) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80) [hibernate-core-4.3.5.Final.jar:4.3.5.Final] ... 149 morehere the wildfly console when renaming @joincolumn:
12:29:45,739 WARN [org.jboss.as.jpa] (DeploymentScanner-threads - 1) JBAS011411: Unexpected problem gathering statistics: java.lang.IllegalStateException: JBAS011477: Persistence unit 'Calendar-2dv012.war#Calendar-2dv012' is not available at org.jboss.as.jpa.management.EntityManagerFactoryLookup.entityManagerFactory(EntityManagerFactoryLookup.java:44) at org.jboss.as.jpa.hibernate4.management.HibernateEntityStatistics.getDynamicChildrenNames(HibernateEntityStatistics.java:145) at org.jboss.as.jpa.management.DynamicManagementStatisticsResource.getChildren(DynamicManagementStatisticsResource.java:204) at org.jboss.as.controller.registry.AbstractModelResource$DelegateResource.getChildren(AbstractModelResource.java:254) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:252) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:239) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:225) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:254) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:239) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:225) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:254) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:239) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:225) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:254) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:239) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:225) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.registry.Resource$Tools.readModel(Resource.java:213) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.ModelControllerImpl.writeModel(ModelControllerImpl.java:575) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.OperationContextImpl.createPersistenceResource(OperationContextImpl.java:224) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:526) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.AbstractOperationContext.completeStepInternal(AbstractOperationContext.java:281) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.AbstractOperationContext.finishStep(AbstractOperationContext.java:698) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:673) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:484) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.AbstractOperationContext.completeStepInternal(AbstractOperationContext.java:281) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.AbstractOperationContext.executeOperation(AbstractOperationContext.java:276) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.ModelControllerImpl.internalExecute(ModelControllerImpl.java:271) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.ModelControllerImpl.execute(ModelControllerImpl.java:145) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.ModelControllerImpl$3$1$1.run(ModelControllerImpl.java:555) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at org.jboss.as.controller.ModelControllerImpl$3$1$1.run(ModelControllerImpl.java:551) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at java.security.AccessController.doPrivileged(Native Method) [rt.jar:1.8.0_25] at org.jboss.as.controller.ModelControllerImpl$3$1.run(ModelControllerImpl.java:551) [wildfly-controller-8.1.0.Final.jar:8.1.0.Final] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [rt.jar:1.8.0_25] at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_25] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [rt.jar:1.8.0_25] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [rt.jar:1.8.0_25] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_25] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_25] at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_25] at org.jboss.threads.JBossThread.run(JBossThread.java:122) [jboss-threads-2.1.1.Final.jar:2.1.1.Final] 12:29:45,790 INFO [org.jboss.as.server] (DeploymentScanner-threads - 1) JBAS018565: Replaced deployment "Calendar-2dv012.war" with deployment "Calendar-2dv012.war" 12:29:45,790 INFO [org.jboss.as.controller] (DeploymentScanner-threads - 1) JBAS014774: Service status report JBAS014777: Services which failed to start: service jboss.persistenceunit."Calendar-2dv012.war#Calendar-2dv012": org.jboss.msc.service.StartException in service jboss.persistenceunit."Calendar-2dv012.war#Calendar-2dv012": javax.persistence.PersistenceException: [PersistenceUnit: Calendar-2dv012] Unable to build Hibernate SessionFactoryCalendarEjb:
@Stateless @LocalBean public class CalendarEJB { @PersistenceContext (name = "CalendarEJB") EntityManager em; public List<Calendar> listAllCalendars(){ TypedQuery<Calendar> theQuery = em.createQuery("SELECT c FROM Calendar c", Calendar.class); List<Calendar> result = theQuery.getResultList(); return result; } public Calendar getCalendarById(int id){ TypedQuery<Calendar> theQuery = em.createQuery("SELECT c FROM Calendar c", Calendar.class); List<Calendar> result = theQuery.getResultList(); for(int i=0;i<=result.size()-1;i++){ if(result.get(i).getCalendarId()== id){ return result.get(i); } } return null; }DayEjb:
@Stateless @LocalBean public class DayEJB { @PersistenceContext (name = "DayEJB") EntityManager em; public List<Day> listAllDays(){ TypedQuery<Day> theQuery = em.createQuery("SELECT d FROM Day d", Day.class); List<Day> result = theQuery.getResultList(); return result; } public Day getDayById(int id){ TypedQuery<Day> theQuery = em.createQuery("SELECT d FROM Day d", Day.class); List<Day> result = theQuery.getResultList(); for(int i=0;i<=result.size()-1;i++){ if(result.get(i).getIddays()== id){ return result.get(i); } } return null; } public void createDay(Day d){ em.persist(d); System.out.println("Data Added Successfully"); }This is the Html:
<h:form> <h3>Days:</h3> <h:dataTable value="#{dayBean.allDays()}" var="day"> <h:column> <f:facet name="header">ID</f:facet> #{day.iddays} </h:column> <h:column> <f:facet name="header">Date</f:facet> #{day.date} </h:column> <h:column> <f:facet name="header">Text</f:facet> #{day.text} </h:column> <h:column> <f:facet name="header">Link</f:facet> #{day.link} </h:column> <h:column> <f:facet name="header">Calendar ID</f:facet> #{day.calendars_calendar_id} </h:column> </h:dataTable> </h:form> -
Flojoe over 9 yearsI tried that earlier but wildfly could not start anymore afterwards. Just found out that (insertable = false, updatable = false) was missing then. Its working now. Thanks!