golang连接数据库

config

var DnsDevConfInstance *DnsDevConf

var DnsDevConfInstanceOnceDo sync.Once
// 
func SingleNewDnsDevConf() *DnsDevConf {
	if DnsDevConfInstance == nil {
		DnsDevConfInstanceOnceDo.Do(func() {
			DnsDevConfInstance = NewDnsDevConf()
		})
	}
	return DnsDevConfInstance
}

func NewDnsDevConf() *DnsDevConf {
	// 加载配置文件
	conf, _ := load()
	return &DnsDevConf{
		DnsSever: conf.DnsSever,
		Db:       conf.Db,
	}
}

type DnsDevConf struct {
	DnsSever *DnsSeverConf `json:"dns_conf"`
	Db       *DatabaseConf `json:"db"`
}

type DatabaseConf struct {
	Host   string `json:"host"`
	Driver string `json:"driver"`
	DbName string `json:"db_name"`
	User   string `json:"user"`
	Passwd string `json:"passwd"`
}

//连接

package mysqlconn

import (
	"database/sql"
	"config"
	"fmt"
	// mysql 驱动
	_ "github.com/go-sql-driver/mysql"
)

type DbConn struct {
	user   string
	passwd string
	host   string
	dbName string
	driver string
	*sql.DB
}

// 读配置文件
func NewMysqlDb() *DbConn {
	
	dbconf := config.SingleNewDnsDevConf().GetConf().Db
	return &DbConn{user: dbconf.User,
		passwd: dbconf.Passwd,
		host:   dbconf.Host,
		dbName: dbconf.DbName,
		driver: dbconf.Driver}
}

// 传值
func NewMysqlDbPrams(conf *config.DatabaseConf) *DbConn {
	return &DbConn{user: conf.User,
		passwd: conf.Passwd,
		host:   conf.Host,
		dbName: conf.DbName,
		driver: conf.Driver}
}

func (d *DbConn) Connect() error {
	mysqldns := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8&parseTime=true",
		d.user, d.passwd, d.host, d.dbName)
	fmt.Println(mysqldns)
	db, err := sql.Open(d.driver, mysqldns)
	if err != nil {
		return err
	} else {
		d.DB = db
		return nil
	}
}

func (d *DbConn) Close() error {
	return d.DB.Close()
}

封装

func GetMdb() *MysqlDb {
	if mdb == nil {
		mdbOnce.Do(func() {
		    // 读配置文件
			d := config.GetRConf().GetMysqlDb()
			mysqldns := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8&parseTime=true",
				d.User, d.Passwd, d.Host, d.DbName)
			db, err := sql.Open(d.Driver, mysqldns)
			if err != nil {
				panic(err)
			}
			mdb = &MysqlDb{
				Mysql: db,
			}

		})
	}

	return mdb
}

测试

 package test

import (
	"db/mysqlconn"
	"fmt"
	"testing"
)

func TestSqlConn(t *testing.T) {
	db := mysqlconn.NewMysqlDb()
	if err := db.Connect(); err != nil {
		panic(err)
	}
	if err := db.Ping(); err != nil {
		fmt.Println(err)
	} else {
		fmt.Println("conn success")
	}

}

插入数据：
package sql

import (
	"dnsdao.resolver/db/conn"
	"errors"
	"github.com/ethereum/go-ethereum/core/types"
)

func InsertBuydomain(conn *conn.DbConn, seller string, buyer string, namehash string, log *types.Log) error {
	if res, err := conn.Exec("Insert into buydomain (seller,buyer,domain) VALUES (?,?,?)",
		seller,
		buyer,
		namehash,
	); err != nil {
		return err
	} else if res == nil {
		return errors.New("no data insert or insert failed")
	}

	return nil
}

func InsertManyBuydomain(conn *conn.DbConn, sql_ string) error {
	if res, err := conn.Exec("Insert into buydomain (seller,buyer,domain) VALUES " + sql_[:len(sql_)-1] + ";"); err != nil {
		return err
	} else if res == nil {
		return errors.New("no data insert or insert failed")
	}

	return nil
}

Mongo

package ldb

import (
	"context"
	"fmt"
	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo"
	"go.mongodb.org/mongo-driver/mongo/options"
	"log"
	"sync"
)

type Mdb struct {
	db *mongo.Database
}

var (
	mdb     *Mdb
	mdbOnce sync.Once
)

func GetMdb() *Mdb {
	if mdb == nil {
		mdbOnce.Do(func() {
			var err error
			clientOptions := options.Client().ApplyURI("mongodb://localhost:27017")
			// 连接到MongoDB
			mgoCli, err := mongo.Connect(context.TODO(), clientOptions)
			if err != nil {
				log.Fatal(err)
			}
			// 检查连接
			err = mgoCli.Ping(context.TODO(), nil)
			if err != nil {
				log.Fatal(err)
			}
			db := mgoCli.Database("mintsign")
			mdb = &Mdb{
				db: db,
			}
			// 建索引

		})
	}

	return mdb
}
func SELECT(Table string, Filter bson.M, OptionsFind *options.FindOptions) ([]map[string]string, error) {
	var err error
	db := GetMdb().db
	coll := db.Collection(Table)
	var data []map[string]string
	findres, err := coll.Find(context.TODO(), Filter, OptionsFind)
	defer findres.Close(context.TODO())
	if err != nil {
		log.Println(fmt.Sprintf("Find table:%s Filter %s  err:%s", Table, Filter, err))
		return nil, err
	}
	defer findres.Close(context.TODO())
	err = findres.All(context.TODO(), &data)
	if err != nil {
		log.Println("mongo decode err", err)
		return nil, err
	} else {
		return data, nil
	}
}

func UPDATEOne(Table string, Filter bson.M, UpdateData bson.M) (bool, error) {
	var err error
	db := GetMdb().db
	coll := db.Collection(Table)
	var upres *mongo.UpdateResult
	up := bson.M{"$set": UpdateData}
	upres, err = coll.UpdateOne(context.TODO(), Filter, up)
	if err != nil {
		log.Println("Mongo Update failed", err)
		return false, err
	} else {
		log.Println("Mongo Update success", upres.UpsertedID)
		return true, nil
	}
}

func INSERTOne(Table string, InsertData bson.M) {
	var err error
	db := GetMdb().db
	coll := db.Collection(Table)
	var res *mongo.InsertOneResult
	res, err = coll.InsertOne(context.TODO(), InsertData)
	if err != nil {
		log.Println(fmt.Sprintf("Table %s InsertData %v err %v", Table, InsertData, err))
	} else {
		log.Println(fmt.Sprintf("Table %s InsertData %v success %v", Table, InsertData, res.InsertedID))
	}
}

test

package ldb

import (
	"context"
	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo"
	"go.mongodb.org/mongo-driver/mongo/options"
	"go.mongodb.org/mongo-driver/x/bsonx"
	"log"
	"testing"
)

func createIndexes() error {
	var err error
	db := GetMdb().db
	coll := db.Collection("signcoll")
	indexes := map[string]mongo.IndexModel{
		//"_tokenid_name_": {
		//	Keys: bsonx.Doc{
		//		// 联合索引
		//		// 1 为指定按升序创建索引
		//		{"tokenid", bsonx.Int32(1)},
		//		{"name", bsonx.Int32(1)},
		//	},
		//	//_tokenid_name_ 索引名称
		//	Options: options.Index().SetBackground(true).SetName("_tokenid_name_"),
		//},
		"_name_": {
			Keys: bsonx.Doc{
				{"name", bsonx.Int32(1)},
			},
			//_tokenid_name_ 索引名称
			Options: options.Index().SetBackground(true).SetName("_name_"),
		},
	}
	//查询已经存在的索引
	//cursor, err := coll.Indexes().List(context.Background())
	//if err != nil {
	//	log.Fatalln(err)
	//}
	//for cursor.Next(context.Background()) {
	//	var idx []mongo.IndexModel
	//	err = cursor.All(context.TODO(), &idx)
	//	//err = cursor.Decode(&idx)
	//	if err != nil {
	//		log.Println("Decode Index error", err)
	//		continue
	//	}
	//	log.Println(idx)
	//	//if idx != nil {
	//	//	// 删除添加的重复索引
	//	//	//delete(indexes, *idx.Options.Name)
	//	//}
	//}
	// 添加索引
	models := []mongo.IndexModel{}
	for _, v := range indexes {
		models = append(models, v)
	}
	if len(models) > 0 {
		var names []string
		names, err = coll.Indexes().CreateMany(context.Background(), models)
		if err != nil {
			log.Fatalln(err)
		}
		log.Println("Create Indexes", names)
	}
	return err
}

func TestIndex(t *testing.T) {
	createIndexes()
}
func TestMongo(t *testing.T) {
	var err error
	db := GetMdb().db
	log.Println(db.Name())
	coll := db.Collection("signcoll")
	filter := bson.M{"name": "x.did", "msg_sender": "0x000"}
	var data []map[string]string
	// options.Find().SetSkip(0), options.Find().SetLimit(1) 0页  1每页数据量
	findres, _ := coll.Find(context.TODO(), filter, options.Find().SetSkip(0), options.Find().SetLimit(1))
	defer findres.Close(context.TODO())
	err = findres.All(context.TODO(), &data)
	if err != nil {
		log.Println("mongo decode err", err)
	}
	if data == nil {
		res, _ := coll.InsertOne(context.TODO(), map[string]string{"name": "x.did", "msg_sender": "0x000", "tokenid": "1"})
		log.Println("InsertOne", res.InsertedID)
	} else {
		up := bson.M{"$set": bson.M{"tokenid": "2"}}
		upres, err := coll.UpdateOne(context.TODO(), filter, up)
		if err != nil {
			log.Println("Mongo Update failed", err)
		} else {
			log.Println("Mongo Update ", upres.UpsertedID)
		}
	}
	// coll.Indexes().CreateOne(context.TODO())

}

func TestSelect(t *testing.T) {
	filter := bson.M{"name": "x.did", "msg_sender": "0x000"}
	options.Find().SetSkip(0)
	options.Find().SetLimit(1)
	op := &options.FindOptions{Skip: func(i int64) *int64 { return &i }(0), Limit: func(i int64) *int64 { return &i }(10)}
	res, err := SELECT("signcoll", filter, op)
	if err != nil {
		log.Println(err)
	} else {
		log.Println(res)
	}
}
func TestInsertOne(t *testing.T) {
	insert := bson.M{"name": "x.did", "msg_sender": "0x000", "tokenid": "6"}
	INSERTOne("signcoll", insert)
}

func TestUpdate(t *testing.T) {
	filter := bson.M{"name": "x.did", "msg_sender": "0x0002"}
	up := bson.M{"tokenid": "2"}
	state, _ := UPDATEOne("signcoll", filter, up)
	log.Println(state)
}