Go -⑩基本(DB)-

◼︎sqlite3の環境構築

dockerでGoの実行環境とsqlite3の環境を作成する。

docker-compose.yml

version: '2'

services:
  go:
    restart: always
    build: .
    container_name: 'go_dev'
    working_dir: '/app/'
    ports:
      - 7473:7473
      - 7474:7474
      - 6477:6477
      - 7687:7687
    tty: true
    volumes:
      - ../../:/app

Dockerfile

FROM golang:1.12.1-alpine3.9

# SQLite
RUN apk update && \
    apk upgrade && \
    apk add sqlite && \
    apk add socat

◼︎go - sqlite3のCRUD

感覚的にはJavaのJDBCを利用している感じと一緒。

◼︎出力

SELECT1------------
TOM 30
TOM 30
TOM 30
SELECT2------------
TOM 30

◼︎コード

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

type Person struct {
	Name string
	Age int
}

func main() {

	// sqlite3 example.sql -> .table
	DbConnection, _ := sql.Open("sqlite3","./example.sql")
	defer DbConnection.Close()
	
	// CREATE TABLE -------------
	if err := CreateTable(DbConnection); err != nil {
		log.Fatal(err)
	}

	// INSERT -------------
	if err := InsertQuery(DbConnection); err != nil {
		log.Fatalln(err)
	}
	if err := InsertQuery(DbConnection); err != nil {
		log.Fatalln(err)
	}
	if err := InsertQuery(DbConnection); err != nil {
		log.Fatalln(err)
	}

	// UPDATE -------------
	if err := UpdateQuery(DbConnection); err != nil {
		log.Fatalln(err)
	}

	fmt.Println("SELECT1------------")

	// SELECT -------------
	if pp, err := SelectQuery(DbConnection); err != nil{
		log.Fatalln(err)
	} else {
		for _, p := range pp {
			fmt.Println(p.Name, p.Age)
		}
	}

	fmt.Println("SELECT2------------")

	// SELECT -------------
	p,err := SelectQueryOne(DbConnection)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Println("No row")
		} else {
			log.Println(err)
		}
	}
	fmt.Println(p.Name, p.Age)

	// DELETE -------------
	if err := DeleteQuery(err, DbConnection); err != nil {
		log.Fatalln(err)
	}

}

func DeleteQuery(err error, DbConnection *sql.DB) error {
	cmd := "DELETE FROM person WHERE name = ?"
	_, err = DbConnection.Exec(cmd, "TOM")
	return err
}

func SelectQueryOne(DbConnection *sql.DB) (Person, error) {
	cmd := "SELECT * FROM person where age = ?"
	row := DbConnection.QueryRow(cmd, 30)
	var p Person
	err := row.Scan(&p.Name, &p.Age);
	return p, err
}

func SelectQuery(DbConnection *sql.DB) ([]Person, error){
	cmd := "SELECT * FROM person"
	rows, _ := DbConnection.Query(cmd)
	defer rows.Close()
	var pp []Person
	for rows.Next() {
		var p Person
		if err := rows.Scan(&p.Name, &p.Age); err != nil {
			log.Println(err)
		}
		pp = append(pp, p)
	}

	return pp, rows.Err()
}

func UpdateQuery(DbConnection *sql.DB) error {
	cmd := "UPDATE person SET age =? WHERE name = ?"
	_, err := DbConnection.Exec(cmd, 30, "TOM")
	return err
}

func InsertQuery(DbConnection *sql.DB) error {
	cmd := "INSERT INTO person (name, age) VALUES (?,?)"
	_, err := DbConnection.Exec(cmd, "TOM", 33)
	return err
}

func CreateTable(DbConnection *sql.DB) error {
	cmd := `CREATE TABLE IF NOT EXISTS person(
				name STRING,
				age  INT)`
	_, err := DbConnection.Exec(cmd)
	return err
}