0

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> {

}

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.