Install MySQL with Homebrew and Create Spring JPA Application

Install MySQL and run a script file to create and populate the database.

$ brew install mysql
$ brew tap homebrew/services
$ brew services start mysql
$ mysqladmin -u root password 'secret'
$ mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20
mysql> source ~/dev/src/mysql/bookstore.sql
mysql> use bookstore;
mysql> alter table books add column createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
mysql> alter table books add column updatedAt TIMESTAMP NULL ON UPDATE NOW();
mysql> quit
Bye
$

Create your Maven project by running the following command.

$ mvn -B archetype:generate /
 -DarchetypeGroupId=org.apache.maven.archetypes /
 -DgroupId=com.bookstore /
 -DartifactId=hellosql
$ cd hellosql

Edit the pom.xml file.

<project xmlns="http://maven.apache.org/POM/4.0.0" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
  http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.bookstore</groupId>
  <artifactId>hellosql</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>hellosql</name>
  <url>http://maven.apache.org</url>

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.8.RELEASE</version>
  </parent>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <properties>
    <java.version>1.8</java.version>
  </properties>

  <build>
    <plugins>
    <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
</project>

Create the Java class that JPA will populate.

package com.bookstore.book;

import org.hibernate.validator.constraints.NotBlank;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "books")
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties(value = {"createdAt", "updatedAt"}, allowGetters = true)
public class Note implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @NotBlank
    private String title;

    @NotBlank
    private String content;

    @Column(nullable = false, updatable = false)
    @Temporal(TemporalType.TIMESTAMP)
    @CreatedDate
    private Date createdAt;

    @Column(nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    @LastModifiedDate
    private Date updatedAt;
}

Create the repository

package com.bookstore.book;

import org.springframework.data.repository.CrudRepository;
// will be autoimplemented by the framework
public interface BookRepository extends CrudRepository {}

Create the Controller

package com.bookstore.book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
@RequestMapping(path="/book")
public class BookController {
 @Autowired
 private BookRepository bookRepository;

 @GetMapping(path="/add")
 public @ResponseBody String addNewBook (@RequestParam String title
   , @RequestParam int pages
      , @RequestParam String isbn) {

  Book book = new Book();
  book.title = title;
  book.pages = pages;
  book.isbn = isbn;
  bookRepository.save(book);
  return "Saved";
 }

 @GetMapping(path="/all")
 public @ResponseBody Iterable getAllBooks() {
  System.out.println("Controller.getAllBooks()");
  return bookRepository.findAll();
 }
}

Create the app

package com.bookstore.book;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@SpringBootApplication
@EnableJpaAuditing
public class App {
    public static void main( String[] args ) {
        SpringApplication.run(App.class, args);
    }
}

Create the application.properties file in src/main/resources

spring.datasource.url = jdbc:mysql://localhost:3306/bookstore?useSSL=false
spring.datasource.username = databaseuserid
spring.datasource.password = databasepassword
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.ddl-auto = create-drop

Compile and run

$ mvn clean compile
$ mvn spring-boot:run

In the browser create a record by using the following URL:

http://localhost:8080/book/add?title=Star%20Wars&pages=333&isbn=987654321

and the browser responds by printing "Saved". Then view the data by entering the following URL:

http://localhost:8080/book/all

and the browser prints:
[{"id":1,"title":"Star Wars","pages":333,"isbn":"987654321","createdAt":1509937707000,"updatedAt":1509937707000}]