Spring Boot not loading entities from local MySQL database

Question

I am trying to configure Spring Boot (version 1.4.2) to use my localhost MySQL database but there is something wrong with my configuration. Could you please take a look at this very simple example and tell me what is going on?

I have all the required dependencies in place:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

My entity:

@Entity
@Table(name = "greetings")
public class Greeting {

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

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

    // getters and setters here
}

Repository:

@Repository
public interface GreetingRepository extends JpaRepository<Greeting, Long> {
}

Controller:

@RestController
@RequestMapping("/hello")
public class GreetingsController {

    @Autowired
    private GreetingRepository repo;

    @RequestMapping(method = RequestMethod.GET)
    public Collection<Greeting> getGreetings() {
        return repo.findAll();
    }

    @RequestMapping(value = "/{id}", method = RequestMethod.GET)
    public Greeting getGreeting(@PathVariable Long id) {
        return repo.findOne(id);
    }
}

application.properties:

spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.properties.hibernate.globally_quoted_identifiers=true

spring.datasource.url=jdbc:mysql://localhost/example
spring.datasource.username=root
spring.datasource.password=secret
spring.datasource.driverClassName=com.mysql.jdbc.Driver

There is a MySQL database running on localhost called 'example' with the following table (and couple of records I inserted in there):

mysql> describe greetings;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| greeting_id | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| text        | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

EDIT: I corrected my controller and updated my question. The problem is that my application uses in-memory datastore instead of my MySQL database. IF I create a POST endpoint and insert new entity into the database, I am able to retrieve it but it seems that it is different than my existing database since I am not getting any data from there.

EDIT2: resolved by spring.jpa.hibernate.ddl-auto=update

Nikolay Rusev · Accepted Answer · 2016-11-22T08:29:05.263

1

I'm seeing multiple errors on your code. The first one is with @RestController annotation. Check out the doc.

The value that you put ("/hello") is not what you are expecting...It's not the request mapping handler name as in @RequestMapping annotation. Instead of this use:

@RestController
@RequestMapping("/hello")
public class GreetingsController {

    @Autowired
    private GreetingRepository repo;

    @RequestMapping(method = RequestMethod.GET)
    public Collection<Greeting> getGreetings() {
        return repo.findAll();
    }

    @RequestMapping(value = "/{id}", method = RequestMethod.GET)
    public Greeting getGreeting(@PathVariable long id) {
        return repo.findOne(id);
    }
}

Actually I think this will resolve your problem . The second one is suggestion for id field - use Long Wrapper type instead of long primitive.

Second one problem related to your data is the property: spring.jpa.hibernate.ddl-auto=create-drop this will drop your schema at end of session. Use spring.jpa.hibernate.ddl-auto=update

edited Nov 22 '16 at 08:29

answered Nov 22 '16 at 08:21

Nikolay Rusev

4,060
3
19
29

1

True, I had these errors in there. Now I am not getting any exceptions, but it is not loading any data at all. Returns only empty set even though the database is populated. – Smajl Nov 22 '16 at 08:24
Fully agree with the primitive wrapper object for the id. Primitives should be avoided, see here for an explanation: http://stackoverflow.com/questions/3535791/primitive-or-wrapper-for-hibernate-primary-keys – s.meissner Nov 22 '16 at 08:24
1

That was the mistake! Thank you for taking the time and explaining these basic things. It is difficult to spot the error on the first glance. – Smajl Nov 22 '16 at 08:36
you are welcome. I'm happy that I helped to resolve your problem. – Nikolay Rusev Nov 22 '16 at 08:37

Spring Boot not loading entities from local MySQL database

1 Answers1