[JAVA] 3. Create a database to access from the web module


This article is a continuation of 2 below.

  1. Quickly run Java web module with Google Kubernetes Engine (GKE)
  2. Create Docker image and register registry
  3. Create a database to access from the Web module
  4. Create Manifest and Run Web Module (https://qiita.com/Turtle-child-No2/items/23982059d188e44618df)

3-1. Creating a database

Step 3-1.1

Create a database to access from the web module. Click SQL in the side menu. When the details screen appears, click "Create Instance". 020.png 021.png

Step 3-1.2

Then click "PostgreSQL". 022.png

Step 3-1.3

On the details screen, enter "sample-app-ist" for the instance ID and "123456" for the default user password. 023.png

Step 3-1.4

Then click Show Configuration Options. 024.png

Step 3-1.5

Check the private IP. 025.png

Step 3-1.6

If the details screen is displayed, click "Enable API". 026.png

Step 3-1.7

Click "Create". 027.png

Step 3-1.8

After a while, the database instance was created. 028.png

Step 3-1.9

Then click the database instance and then click the database on the details screen. Then click "Create Database". 029.png

Step 3-1.10

Enter "sample-app-db" as the database name and click "Create". 030.png

Step 3-1.11

The database has been created. 031.png

Step 3-1.12

Immediately connect to the database instance. Enter "123456" when prompted for a password.

[userid]@cloudshell:~ ([project_id])$ sudo gcloud sql connect sample-app-ist --user=postgres
Whitelisting your IP for incoming connection for 5 minutes...done.
Connecting to database with SQL user [postgres].Password for user postgres:
psql (9.6.11, server 9.6.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

Step 3-1.13

Since the database to be operated is postgres, switch to the sample-app-db created earlier. Enter "123456" when prompted for a password.

postgres=> \connect sample-app-db
Password for user postgres:
psql (9.6.11, server 9.6.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
You are now connected to database "sample-app-db" as user "postgres".

Step 3-1.14

Then create a proxyuser for later use and give it connection and permissions to the database. After switching to proxyuser (enter "123456" when prompted for password), create a t_sample table and insert one test data.

sample-app-db=> CREATE ROLE proxyuser WITH LOGIN PASSWORD '123456';
sample-app-db=> GRANT CONNECT ON DATABASE "sample-app-db" TO proxyuser;
sample-app-db=> \connect - proxyuser
Password for user proxyuser:
psql (9.6.11, server 9.6.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
You are now connected to database "sample-app-db" as user "proxyuser".
sample-app-db=> CREATE TABLE t_sample (order_no BIGINT NOT NULL, cre_dt TIMESTAMP NOT NULL default CURRENT_TIMESTAMP, nickname VARCHAR(30), order_id VARCHAR(5000));
sample-app-db=> INSERT INTO t_sample (order_no, cre_dt, nickname, order_id) VALUES (1, '1999-01-08 04:05:06', 'test', 'ABC');

Step 3-1.15

Check the created table.

sample-app-db=> \dt
           List of relations
 Schema |   Name   | Type  |   Owner
 public | t_sample | table | proxyuser
(1 row)

3-2. Settings required to connect to the database instance

Step 3-2.1

Make the necessary settings to connect GKE to the database instance. First, click Enable Cloud SQL Administration API (https://console.cloud.google.com/flows/enableapi?apiid=sqladmin&hl=ja&_ga=2.250044038.-1219564708.1545700047).

Step 3-2.2

Enter "Select a project to register the application" and click "Continue". 034.png

Step 3-2.3

The Cloud SQL Administration API is now enabled. Click Cancel to add credentials to the project. 036.png

Step 3-2.4

Then click IAM & Administration-> Service Accounts.

Step 3-2.5

Click Create Service Account. 033.png

Step 3-2.6

Enter "sample-app-db-client" as the service account name and click "Create". 037.png

Step 3-2.7

Enter Cloud SQL Client for the role and click Continue. 038.png

Step 3-2.8

Finally, click "Create Key". 040.png

Step 3-2.9

Make sure "JSON" is selected on the details screen and click "Create". 041.png

Step 3-2.10

The JSON file will be downloaded to your browser. 042.png

Step 3-2.11

Upload the downloaded JSON file to your browser. 043.png

Step 3-2.12

Check if the file has been uploaded.

[userid]@cloudshell:~ ([project_id])$ ls -Fal
-rw-r--r-- 1 [userid] xxxxxxx      2361 Mar 10 09:25 [project_id]-xxxxxxxxxxxx.json

Step 3-2.13

Create a secret by specifying the JSON you uploaded earlier.

[userid]@cloudshell:~ ([project_id])$ kubectl create secret generic cloudsql-instance-credentials --from-file=credentials.json=[project_id]-xxxxxxxxxxxx.json
secret "cloudsql-instance-credentials" created

Step 3-2.14

Create a secret by specifying the user and password that Cloud Sql Proxy will use to access the database.

[userid]@cloudshell:~ ([project_id])$ kubectl create secret generic cloudsql-db-credentials --from-literal=username=proxyuser --from-literal=password=123456
secret "cloudsql-db-credentials" created

that's all

Recommended Posts

3. Create a database to access from the web module
[JDBC] I tried to access the SQLite3 database from Java.
How to create a form to select a date from the calendar
[Rails] rails new to create a database with PostgreSQL
[Java / PostgreSQL] Connect the WEB application to the database
The road to creating a Web service (Part 1)
You cannot change the project facet version from a dynamic web module to x.x. How to deal with
How to create a route directly from the URL you want to specify + α
Learn while making a WEB server Introduction to WEB application development from the basics
How to create a JDBC URL (Oracle Database, Thin)
Using the database (SQL Server 2014) from a Java program 2018/01/04
Kotlin may take the world from App to Web
How to create a method
From studying programming for 2 months to releasing a web application
How to create a placeholder part to use in the IN clause
I want to create a form to select the [Rails] category
Create a method to return the tax rate in Java
[Docker] How to access the host from inside the container. http://host.docker.internal:
How to create a jar file or war file using the jar command
Dynamically switch the database to connect to
I want to create a chat screen for the Swift chat app!
Preparing to create a Rails application
How to create a registration / update function where the table crosses
Create a data source (connection pool) that connects from WLS (WebLogic) to Autonomous Database (ATP / ADW).
Create a database in a production environment
Connecting to a database with Java (Part 1) Maybe the basic method
Access Teradata from a Java application
Access the in-memory data grid Apache Ignite from a Java client
[Java] How to create a folder
Easy way to create a mapping class when using the API
How to run a Kotlin Coroutine sample from the command line
Check H2 database on the web
I am studying to change jobs from inexperienced to a web engineer.
Try to create a server-client app
Try to make a CS 3D tile from the Geographical Survey tile
How to create a web server on an EC2 instance on AWS
The road from JavaScript to Java
[Output] Learn Ruby from what you want to do at the fastest speed-Part 2 "Create a flowchart"
Create a calendar from the Calendar class by specifying the year and month
(Ruby on Rails6) Create a function to edit the posted content
Three Reasons to Frustrate Before the Release of a Web Service
[JDBC] I tried to make SQLite3 database access from Java into a method for each SQL statement.
A story about changing skills from COBOL cultivated for 5 years in the late 20s to a Web language
Update JAVA to the latest version to 1.8.0_144 (when downloading from the web and updating)
A story about changing jobs from a Christian minister (apprentice) to a web engineer
How to return a value from Model to Controller using the [Swift5] protocol
How to run a GIF file from the Linux command line (Ubuntu)
Create a Tokyo subway map from the CSV file of station data.jp
To create a Zip file while grouping database search results in Java
I want to create a dark web SNS with Jakarta EE 8 with Java 11
Run x11 apps in a Docker container (supports network access from the container)
How to get the setting value (property value) from the database in Spring Framework
Gorigori SIer SE tried to create a Web service by personal development
I thought about the best way to create a ValueObject in Ruby
[Rails] How to create a table, add a column, and change the column type
Volume of trying to create a Java Web application on Windows Server 2016
I want to develop a web application!
Create a web environment quickly using Docker
4. Creating a manifest and running a web module
Create a jar file with the command
Investigate the replacement from Docker to Podman.