Spring Boot, JPA and MySQL returns empty results

Question

Ok I'm new to Java Spring and I am simply trying to access some data from a MYSQL database. Unfortunately I can't seem to load any data - I'm sure it's a silly mistake. (I've followed a number of sources, including this one)

I am using: java spring-boot, mysql, jpa, gradle

Here is my build.gradle:

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:1.3.2.RELEASE")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'idea'
apply plugin: 'spring-boot'

jar {
    baseName = 'gs-serving-web-content'
    version =  '0.1.0'
}

repositories {
    mavenCentral()
}

sourceCompatibility = 1.8
targetCompatibility = 1.8

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web")
    compile("org.springframework.boot:spring-boot-starter-thymeleaf")
    compile("org.springframework.boot:spring-boot-starter-data-jpa")

    compile("com.h2database:h2") //I get embedded db error if I take this out
    testCompile("junit:junit")
    compile('mysql:mysql-connector-java:5.1.35')
}

task wrapper(type: Wrapper) {
    gradleVersion = '2.3'
}

Controller:

@Controller
public class PersonController {
    @Autowired
    private PersonRepository personRepository;

    @RequestMapping(value = "/hello", method = RequestMethod.GET)
public String greeting(...){
    List<Person> allPeople = personRepository.findAll(); //this comes back with 0 values
    ...
}

PersonRepository:

public interface PersonRepository extends JpaRepository<Person, Long> {
List<Person> findAll(); //returns empty list

//Person findByAge(int Age); //throws an exception if I leave this on: "...nested exception is java.lang.IllegalArgumentException: Could not create query metamodel for method public abstract hello.model.Person hello.repository.PersonRepository.findByAge(int)!"
}

Person:

@Entity
@Table(name="person")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long PersonID;
    private String Firstname;
    private String Lastname;
    private int Age;
    //getters and setters
}

Application.yaml

spring:
 datasource:
  url: jdbc:mysql://localhost:3306/datatest
  username: root
  password: ***
  driverClassName: com.mysql.jdbc.Driver

I've connected intelliJ successfully to MySQL database (under database tab) and I can query it fine from the tab, but I can't load up the data through spring. There's got to be something wrong with my setup considering I can't seem to remove the embedded h2 database without an exception, nor can I add findByAge, findByFirstname etc combinations (all throw exceptions).

Update 1: I've applied hd1's changes to the Person class but I'm still not getting any data back:

@Entity
@Table(name="person")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long personID;
    private String firstName;
    private String lastName;
    private int age;
    //getters and setters updated

PersonRepository:

List<Person> findAll();
Person findByAge(int age); //no more errors here, but returns null
Person findByLastName(String lastName);  //no more errors here, but returns null

Full Runlog file

Update 2: the controller method:

@RequestMapping(value = "/hello", method = RequestMethod.GET)
public String greeting(@RequestParam(value = "name", required = false, defaultValue = "world") String name, Model model) {
    List<Person> allPeople = personRepository.findAll();
        ...
    System.out.print(allPeople.size()); //always 0, but I have many entries in db

    //if I try to create and save new entry in the db: 
    Person person = new Person();
    person.setFirstName("New");
    person.setLastName("Person");
    person.setAge(99);
    personRepository.save(person); //does not show up in DB, why?
    return "greeting";
}

hd1 · Accepted Answer · 2016-03-15T15:57:33.900

2

Apparently, you've not followed your linked tutorial, because property names are camelCase there and TitleCase here. Spring wants camelCase for properties, because the entity scanner uses camelCase for accessor/mutator generation.

[per comment]

In PersonRepository, try uncommenting the commented line. If you still have problems, in the next edit, post the complete stacktrace and the runlog.

edited Mar 15 '16 at 15:57

answered Mar 15 '16 at 15:43

hd1

33,938
5
80
91

thanks for the answer. You're correct totally missed the camel case. I've updated my answer and the class, and it's actually allowing me to use `findByAge` and `findByLastName` (rather than getting an exception like before), but both are returning null. – benscabbia Mar 15 '16 at 15:54
Still waiting for the run log – hd1 Mar 15 '16 at 15:59
Yes, you have got tomcat listening on the port, now you'll need to invoke your controller method. Issue an HTTP GET request to the http://localhost:8000/hello and then your record should be created. – hd1 Mar 15 '16 at 17:36
I have invoked the GET request numerous times, but for some reason it's not grabbing any of the values from the db. I've added my controller method to show how I'm trying to do this. I assume I should be able to simply get all my 'people' in database via: `personRepository.findAll();`, but this returns 0 entries – benscabbia Mar 15 '16 at 20:02
I made a silly mistake with the properties file so it wasn't loading the url property correctly! It's all working fine now, thank for your perseverance and spotting my other problem! – benscabbia Mar 15 '16 at 20:54
1

No problem, buddy, just helping where I can – hd1 Mar 15 '16 at 21:20

score 0 · Answer 2 · answered Mar 15 '16 at 15:24

You should use lower case properties

private long personID;
private String firstname;
private String lastname;
private int age;

I've tested it and get the same exception. But there is a nested exception which sais:

Unable to locate Attribute  with the the given name [attributename]

Spring Boot, JPA and MySQL returns empty results

2 Answers2

Linked