com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '____' in 'field list'

i done simple spring web app which looks like blog (have notes and user). Firstly when i used data kept in my ***inMemoryRepository.java files, front-end and back-end works perfectly. Now i'm trying change this staff to working with mysql database and i'm stuck. Generally i want to load all data from this two databases.

Error from console:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'note0_.author_id' in 'field list'

Console Hibernate log:

Hibernate: 
    select
        note0_.id as id1_0_,
        note0_.author_id as author_i6_0_,
        note0_.body as body2_0_,
        note0_.date as date3_0_,
        note0_.is_done as is_done4_0_,
        note0_.title as title5_0_ 
    from
        notes note0_

Error from browser:

There was an unexpected error (type=Internal Server Error, status=500).
could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

application.properties -> i was trying with auto mode and without this line(#)

#spring.jpa.properties.hibernate.hbm2ddl.auto = create-drop

Sql script 'users' table:

USE stickyNotes_db;
DROP TABLE users;
CREATE TABLE users 
(
id BIGINT(20),
username VARCHAR(50),
firstName VARCHAR(50),
lastName VARCHAR(50),
password VARCHAR(50),
role VARCHAR(50)
);

INSERT INTO `users` (`id`, `username`, `firstName`, `lastName`, `password`, `role`) VALUES
    (1, 'user1',    'Adam1',    'Mock1',    'pass1',    'USER'),
    ...... ;

Sql script 'notes' table:

USE stickyNotes_db;
DROP TABLE notes;
CREATE TABLE notes 
(
id BIGINT(20),
author VARCHAR(50),
title VARCHAR(100),
body VARCHAR(500),
date DATETIME(0),
isDone BOOLEAN not null default 0
);

INSERT INTO `notes` (`id`, `author`, `title`, `body`, `date`, `isDone`) VALUES
    (1, 'user1',    'Title1',   'Lorem ipsum.', '2017-02-08 00:00:00',  0),
    ...... ;

User.java

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "username")
    private String userName;

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

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

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

    @OneToMany(mappedBy = "author")
    private Set<Note> notes = new HashSet<>();

    ...constructors, getters/setters

Note.java

@Entity
@Table(name = "notes")
public class Note {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    private User author;

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

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

    @Column(name = "date")
    private Date date = new Date();

    @Column(name = "isDone")
    private boolean isDone;

    ...constructors, getters/setters

NoteDAO.java

@Repository
public interface NoteDAO extends JpaRepository<Note, Long> {

    @Query("FROM Note")
    Page<Note> findAll(Pageable pageable);

}

UserDAO.java

@Repository
public interface UserDAO extends JpaRepository<User, Long> {

}

Solution

Your Hibernate query says that

note0_.author_id as author_i6_0_,

Which means it is trying to set author_id and your database script has not defined any foreign key constraint to define a relation ship between them.

Also your database schema is not correct and does not match your hibernate configurations.

INSERT INTO `notes` (`id`, `author`, `title`, `body`, `date`, `isDone`) VALUES
    (1, 'user1',    'Title1',   'Lorem ipsum.', '2017-02-08 00:00:00',  0),
    ...... ;
INSERT INTO `users` (`id`, `username`, `firstName`, `lastName`, `password`, `role`) VALUES
    (1, 'user1',    'Adam1',    'Mock1',    'pass1',    'USER'),
    ...... ;

In above, your author should be author_id and should contain the id of the users table.