golang : CRUD MySQL . mysql driver: https://github.com/go-sql-driver/mysql

screenshot.md

sqlku.go

package main

import (
	"database/sql"
	"fmt"
	"strings"

	_ "github.com/go-sql-driver/mysql"
)

type MySQLDB struct {
	db *sql.DB
}

func MySQLBuka(DSN string) (this *MySQLDB) {
	this = new(MySQLDB)
	var err error
	this.db, err = sql.Open("mysql", DSN)
	if err != nil {
		panic(err)
	}
	return
}
func (this *MySQLDB) Tutup() {
	err := this.db.Close()
	if err != nil {
		panic(err)
	}
}
func (this *MySQLDB) Kueri0(kueri string, errFns ...func(error)) (fn func(...interface{}) sql.Result) {
	var errFn func(error)
	if len(errFns) != 0 {
		errFn = errFns[0]
	} else {
		errFn = func(err error) {
			panic(err)
		}
	}
	fn = func(args ...interface{}) (hasil sql.Result) {
		var err error
		hasil, err = this.db.Exec(kueri, args...)
		if err != nil {
			errFn(err)
		}
		return
	}
	return
}
func (this *MySQLDB) Kueri1(kueri string, errFns ...func(error)) (fn func(...interface{}) func(...interface{})) {
	var errFn func(error)
	if len(errFns) != 0 {
		errFn = errFns[0]
	} else {
		errFn = func(err error) {
			panic(err)
		}
	}
	fn = func(args ...interface{}) (fn func(...interface{})) {
		row := this.db.QueryRow(kueri, args...)
		fn = func(hasil ...interface{}) {
			err := row.Scan(hasil...)
			if err != nil {
				errFn(err)
			}
			return
		}
		return
	}
	return
}

type tscan []interface{}

func (this *MySQLDB) KueriN(kueri string) (fn func(func() tscan, ...interface{})) {
	fn = func(cb func() tscan, args ...interface{}) {
		rows, err := this.db.Query(kueri, args...)
		if err != nil {
			panic(err)
		}
		defer rows.Close()
		for rows.Next() {
			err := rows.Scan(cb()...)
			if err != nil {
				panic(err)
			}
		}
		err = rows.Err()
		if err != nil {
			panic(err)
		}
	}
	return
}

const (
	duplicate = "1062"
	notFound  = "no rows"
)

func main() {
	defer func() {
		r := recover()
		if r != nil {
			fmt.Println(r)
		}
	}()

	dataSourceName := "root:@tcp(127.0.0.1:3306)/golang_db1"
	db := MySQLBuka(dataSourceName)
	defer db.Tutup()

	db.Kueri0("DROP TABLE IF EXISTS tbl1")()
	type tTbl1 struct {
		id     int
		unik   int
		nilai1 string
		nilai2 string
	}
	db.Kueri0(`
		CREATE TABLE tbl1 (
		id INT(9) UNSIGNED AUTO_INCREMENT,
		unik INT(9) UNSIGNED,
		nilai1 VARCHAR(30),
		nilai2 VARCHAR(30),
		PRIMARY KEY (id),
		UNIQUE INDEX unik (unik)
		)
	`)()

	//kueri: INSERT multi rows
	db.Kueri0(`
		INSERT INTO tbl1(unik, nilai1, nilai2)
		VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?)
	`)(
		1, "nama1", "nama2", 2, "nama3", "nama4",
		3, "nama5", "nama6", 4, "nama7", "nama8",
	)

	//kueri: INSERT 1 row
	errFn := func(err error) {
		if strings.Contains(err.Error(), duplicate) {
			fmt.Println("inputan sudah terpakai")
		} else {
			panic(err)
		}
	}
	newVal := db.Kueri0("INSERT INTO tbl1(unik, nilai1, nilai2)	VALUES (?, ?, ?)", errFn)
	unik, nilai1, nilai2 := 5, "nama9", "nama10"
	lastId, _ := newVal(unik, nilai1, nilai2).LastInsertId()
	fmt.Println("last insert id:", lastId)

	//INSERT dengan kasus: sudah terpakai
	unik, nilai1, nilai2 = 1, "nama1", "nama2"
	newVal(unik, nilai1, nilai2)

	unik, nilai1, nilai2 = 6, "nama11", "nama12"
	newVal(unik, nilai1, nilai2)
	//get1Row
	errFn = func(err error) {
		if strings.Contains(err.Error(), notFound) {
			fmt.Println("pencarian tidak ketemu")
		} else {
			panic(err)
		}
	}
	get1Row := db.Kueri1("SELECT * FROM tbl1 WHERE unik=?", errFn)
	vRow := tTbl1{}
	get1Row(unik)(&vRow.id, &vRow.unik, &vRow.nilai1, &vRow.nilai2)
	fmt.Println("get1Row:", vRow)

	//kueri: DELETE
	deleteRowWhereUnikIs := db.Kueri0("DELETE FROM tbl1 WHERE unik=?")
	deleteRowWhereUnikIs(unik)

	//contoh pencarian 1 row yang tidak ketemu
	get1Row(unik)(&vRow.id, &vRow.unik, &vRow.nilai1, &vRow.nilai2)

	//kueri: UPDATE
	updateNilai1Nilai2 := db.Kueri0("UPDATE tbl1 SET nilai1=?, nilai2=? WHERE unik=?")
	nilai1, nilai2, unik = "namaE", "namaF", 3
	updateNilai1Nilai2(nilai1, nilai2, unik)

	//getNRows
	getNRows := db.KueriN("SELECT * FROM tbl1 WHERE id>? LIMIT 3")
	vRows := make([]tTbl1, 3)
	i := 0
	id := 1
	getNRows(func() (ret tscan) {
		ret = tscan{&vRows[i].id, &vRows[i].unik, &vRows[i].nilai1, &vRows[i].nilai2}
		i++
		return
	}, id)
	fmt.Println("getNRows:", vRows)

	//contoh pencarian N rows yang tidak ketemu
	i = 0
	id = 99
	getNRows(func() (ret tscan) {
		ret = tscan{&vRows[i].id, &vRows[i].unik, &vRows[i].nilai1, &vRows[i].nilai2}
		i++
		return
	}, id)
	if i == 0 {
		fmt.Printf("cari N rows dengan id: %d, tidak ketemu\n", id)
	} else {
		fmt.Println("getNRows:", vRows)
	}
}