Hibernate says non-existent column is unknown...obviously

Question

Im starting to adapt my spring boot project to use jpa / hibernate. at this stage i simply want to retrieve the id of a table in mysql database.

Here are the relevant classes:

@SpringBootApplication
@ComponentScan(basePackages = {"rest.api.controller", "dao",     rest.api.model", "rest.api.config"})

public class Application {

public static void main(String[] args) {
    SpringApplication.run(Application.class, args);

}

@Bean
public CommandLineRunner demo(PostRepository repository) {
    return (args) -> {
        // save a couple of customers
        repository.findAll();
    };
}

}

@Entity
@Table(name="post")
public class Post {

    private  String text;

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    @Column(name="id")
    private long id;

    @Column(name="sender_id")
    private @JsonIgnore  long senderId;
    private @JsonIgnore  long eventId;
    private @JsonIgnore final String selectSql = " text, sender_id, event_id";

    protected Post() {}

    public Post(long id, float latitude, float longitude, Date created, String ip,
                String text, long senderId, long eventId) {
        this.text = text;
        this.senderId = senderId;
        this.eventId = eventId;
    }

    public Post(String text, long senderId, long eventId) {
        this.text = text;
        this.senderId = senderId;
        this.eventId = eventId;
    }

    public String getText() {
        return text;
    }

    public long getSenderId() {
        return senderId;
    }

    public long getEventId() {
        return eventId;
    }

    public void setId(long id) {
        this.id = id;
    }
}

public interface PostRepository extends CrudRepository<Post,  Long> {

}

And mysql for the post table:

CREATE TABLE `post` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `text` varchar(4096) NOT NULL,
  `sender_id` bigint(20) NOT NULL,
  `event_id` bigint(20) DEFAULT NULL,
  `created` datetime NOT NULL,
  `ip` varchar(20) NOT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `post_sent_by_user` (`sender_id`),
  CONSTRAINT `post_sent_by_user` FOREIGN KEY

I get the following error

2016-06-23 20:39:06.739  WARN 6895 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1054, SQLState: 42S22
2016-06-23 20:39:06.740 ERROR 6895 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Unknown column 'post0_.select_sql' in 'field list'
2016-06-23 20:39:06.750  WARN 6895 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Warning Code: 1054, SQLState: 42S22
2016-06-23 20:39:06.751  WARN 6895 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Unknown column 'post0_.select_sql' in 'field list'
2016-06-23 20:39:06.770 ERROR 6895 --- [           main] o.s.boot.SpringApplication               : Application startup failed

java.lang.IllegalStateException: Failed to execute CommandLineRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:809) [spring-boot-1.3.5.RELEASE.jar:1.3.5.RELEASE]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:790) [spring-boot-1.3.5.RELEASE.jar:1.3.5.RELEASE]
    at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:777) [spring-boot-1.3.5.RELEASE.jar:1.3.5.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:308) [spring-boot-1.3.5.RELEASE.jar:1.3.5.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1191) [spring-boot-1.3.5.RELEASE.jar:1.3.5.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1180) [spring-boot-1.3.5.RELEASE.jar:1.3.5.RELEASE]
    at rest.api.Application.main(Application.java:16) [main/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_91]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_91]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_91]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_91]
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144) [idea_rt.jar:na]
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:242) ~[spring-orm-4.2.6.RELEASE.jar:4.2.6.RELEASE]

For the life of me, i cant work out why it is looking for a column with such a bizzare name as "post0_.select_sql" - doesnt seem at all related to my table or column.

I have googled arround for a while, but could not find anything to explain this.

Id be very grateful if somebody could help me here. (Im sure it should be simple as im not really attempting anything complex yet)

Thanks

Enable the query logs and see the complete query which is failing. That might be helpful: http://stackoverflow.com/questions/30118683/how-to-log-sql-statements-in-spring-boot — Aritz, Jun 23 '16 at 20:09

score 0 · Accepted Answer · answered Jun 23 '16 at 20:10

0

Because you told Hibernate that your entity had a persistent field named that way:

private @JsonIgnore final String selectSql

I have no idea what this field is for and why it is in your entity, but it should probably be elsewhere, or at least defined as a constant (i.e. made static) or at the very least annotated by @Transient so that Hibernate knows it's not part of the persistent properties.

answered Jun 23 '16 at 20:10

JB Nizet

678,734
91
1,224
1,255

thanks so much. i was being totally blind there. I am really not sure why this was there either. works fine now though – apostrophedottilde Jun 23 '16 at 20:19

Hibernate says non-existent column is unknown...obviously

1 Answers1