Spring Boot + Heroku Postgres

I struggled with the details, so I will write it down as a memo for myself.

It is assumed that Heroku Postgres has been added to the Heroku app.

Spring Boot is 2.0.4.

Install the psql command on your local machine

Looking at the Heroku Potgres Settings (above), it says "** Heroku CLI **" and the following command is written.

heroku pg:psql datastore name--app app name

I got an error when I tried it in my environment. You need to put ** psql ** on your local machine. It's natural w

-->Connecting to datastore name
 !    The local psql command could not be located. For help installing psql, see
 !    https://devcenter.heroku.com/articles/heroku-postgresql#local-setup

Refer to URL above and go to your Windows 10 environment Windows Setup I installed it with articles / heroku-postgresql # set-up-postgres-on-windows). Then, I set the environment variable to "C: \ Program Files \ PostgreSQL \ \ bin".

heroku pg:psql datastore name--app app name
-->Connecting to datastore name
psql (10.4)
SSL connection(protocol: TLSv1.2, encryption method: ECDHE-RSA-AES256-GCM-SHA384, bit length:256, compression:off)
"help"Get help with.

app name::DATABASE=>

You have successfully connected.

command

This is a part that has nothing to do with Spring Boot or Heroku, but I made a note because I didn't know the Postgres command too much.

Database list display

\l

When I did this, a lot of databases came out, but I can see the DBs of other owners ...? Is it multi-tenant?

Database selection

\c database name

SSL connection(protocol: TLSv1.2, encryption method: ECDHE-RSA-AES256-GCM-SHA384, bit length:256, compression:off)
Database"xxxx"To the user"xxxx"Connected as.

Table list display

After creating a table with create table, display the table.

\d

Relationship list
Schema|name|Mold|owner
----------+----------+----------+----------------
 public   | nogizaka |table| xxxxxxxxxxxxx

Detailed display of table

See details such as columns.

\d nogizaka

The schema is super omission.

table"public.nogizaka"
Column|Mold|Collation|Null value allowed|Default
------+-----------------------+----------+---------------+------------
 id   | integer               |          |               |
 name | character varying(64) |          |               |

Spring Boot

Spring Initializr

Create a project template with Spring Initializr. I chose the following three dependencies. You don't have to have a web, but I've included it to make the runtime endpoint a REST API.

• Web
• PostgreSQL
• JPA

application.properties

The contents of the connection to the database were defined and operated as follows based on the Database Credentials in the Settings of Heroku Postgres.

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://hostname:port number/Database name?sslmode=require
spring.datasource.username=User name
spring.datasource.password=password

When I first got hooked, the following two points were leaked.

--The URL "postgresql" was changed to "postgres" (I copied the URL written in the Heroku CLI) --I didn't add the sslmode parameter

code

All you have to do is write.

Entity

Nogizaka.java

package tech.kikutaro.herokupostgres;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="nogizaka")
public class Nogizaka {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private long id;

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

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

It may be better to use Lombok for setter and getter.

Repository

NogizakaRepository.java

package tech.kikutaro.herokupostgres;

import org.springframework.data.jpa.repository.JpaRepository;

public interface NogizakaRepository extends JpaRepository<Nogizaka, Long> {

}

Service

NogizakaService.java

package tech.kikutaro.herokupostgres;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class NogizakaService {
    @Autowired
    NogizakaRepository nogirepo;

    public long count() {
        return nogirepo.count();
    }

    public Nogizaka getMember(long id) {
        return nogirepo.getOne(id);
    }
}

For the time being, just count and get.

Main

HerokupostgresApplication.java

package tech.kikutaro.herokupostgres;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

@SpringBootApplication
@RestController
public class HerokupostgresApplication {

	@Autowired
	private NogizakaService nogiService;

	public static void main(String[] args) {
		SpringApplication.run(HerokupostgresApplication.class, args);
	}

	@GetMapping("/count")
	public String count() {
		System.out.println(nogiService.count());
		return Long.toString(nogiService.count());
	}

	@GetMapping("/select/{id}")
	public String getName(@PathVariable("id") long id) {
		return nogiService.getMember(id).getName();
	}
}

Execution result

It worked safely.

http://localhost:8080/nogizaka/1

http://localhost:8080/select/1