Postgres tips for a Spring Boot JPA Rest application

Spring Boot JPA Restful web service with Postgres

Spending some time developing a Spring Boot JPA example allows you to learn, and experiment :O)

There are loads of examples around.

But they sometimes suffer from configuration, and other “niggly” issues, which stop you from running the example in an hour (this is the time I allow myself to play with something new).

If you want to jump ahead, the project can be pulled from github (https://github.com/chocksaway/SpringBootJPAPostgres).

I set up the spring boot app using the following options in Intellij:

miles davenport

Here are some tips for setting up your environment, setting up Postgres, and getting the application working:

Install Postgres on a Mac:

https://www.moncefbelyamani.com/how-to-install-postgresql-on-a-mac-with-homebrew-and-lunchy/

To start Postgres after a reboot, use:

$ lunchy start postgres
/Users/milesd/Library/LaunchAgents/homebrew.mxcl.postgresql.plist: service already loaded
started homebrew.mxcl.postgresql

I use a database called testdb, with an owner of Postgres.

I used the following commands to create the postgres user, the database, and change the password:

CREATE USER postgres SUPERUSER;
CREATE DATABASE testdb WITH OWNER postgres;

ALTER USER postgres WITH PASSWORD 'password';

The Spring Boot App uses these values, declared in resources/application.properties:

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.datasource.url=jdbc:postgresql://localhost/testdb
spring.datasource.username=postgres
spring.datasource.password=password
spring.jpa.generate-ddl=true

Connect to Postgres (using the psql command), and find out the version of Postgres:

The -U option allows you to specify a user.

psql -U postgres
psql (11.2)
Type "help" for help.

postgres=# SELECT version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-apple-darwin18.2.0, compiled by Apple LLVM version 10.0.0 (clang-1000.11.45.5), 64-bit
(1 row)

Returning to the Spring Boot App

I am using the Spring Boot JPA Reference:

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation

To check a DOB (date of birth) before LocalDate (dob is an attribute in the Customer class):

package com.chocksaway.repo;

import com.chocksaway.model.Customer;
import org.springframework.data.repository.CrudRepository;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

/**
 * Author milesd on 22/03/2019.
 */
public interface CustomerRepository extends CrudRepository<Customer, Long> {
    List<Customer> findByLastName(String lastName);

    Optional<Customer> findById(Long id);

    List<Customer> findByDobBefore(LocalDate date);
}