xorm operation PostgreSQL database (add, delete, modify and query instances)

1, Introduction to xorm

Xorm is a simple and powerful ORM Library of Go language. It can make database operation very simple. The goal of xorm is not to let you not learn SQL at all. We don't think SQL will be replaced by ORM, but ORM will solve most of the simple SQL requirements. Xorm supports a mix of two styles.

characteristic

  • Support flexible mapping between Struct and database tables, and support automatic synchronization of table structure
  • Transaction support
  • Support mixed execution of original SQL statement and ORM operation
  • Use hyphenation to simplify calls
  • It supports the use of Id, In, Where, Limit, Join, Having, Table, Sql, Cols and other functions and structures as conditions
  • Support cascading load Struct
  • Support LRU cache (support memory, memcache, leveldb, redis cache Store) and Redis cache
  • Support inversion, i.e. automatically generate the structure of xorm according to the database
  • Support events
  • Supports created, updated, deleted and version record versions (i.e. optimistic locks)

Drive support

xorm currently supports the following drivers and databases:

2, Download

Install xorm

     go  get github.com/go-xorm/xorm      

Install the Postgres driver

     go get github.com/lib/pq                   

 

3, Examples

Note: to import postgreSQL driver first_ " github.com/lib/pq "

package main

import (
	"github.com/go-xorm/xorm"
	_ "github.com/lib/pq"
	"log"
	"fmt"
)

const (
	host = "localhost"
	port = 5432
	user = "postgres"
	password = "your_password"  
	dbName="your_db_name"
)

func main() {
	
	user := &UserTbl{
		Id:1,
		Username	:"Windows",
		Sex :1,
		Info  : "operating system",
	}
	
	SessionUserTest(user)
}

func getDBEngine() *xorm.Engine {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",host,port,user,password,dbName)
	//format
	engine, err := xorm.NewEngine("postgres", psqlInfo)
	if err != nil {
		log.Fatal(err)
		return nil
	}
	engine.ShowSQL()   //It's necessary for Rookies

	err = engine.Ping()
	if err != nil {
		log.Fatal(err)
		return nil
	}
	fmt.Println("connect postgresql success")
	return engine
}


//table name is user_tbl
type UserTbl struct {
	Id  						 int
	Username			string
	Sex   					int
	Info     				string
}

//Query all
func selectAll()  {
	var user []UserTbl
	engine := getDBEngine()
	engine.SQL("select * from user_tbl").Find(&user)
	fmt.Println(user)
}

//Condition query
func selectUser(name string)  {
	var user []UserTbl
	engine := getDBEngine()
	engine.Where("user_tbl.username=?",name).Find(&user)
	fmt.Println(user)
}

//You can query individual elements with Get
func selectOne(id int)  {
	var user UserTbl
	engine := getDBEngine()
	engine.Id(id).Get(&user)
	//engine.Alias("u").Where("u.id=?",id).Get(&user)
	fmt.Println(user)
}

//add to
func InsertUser(user *UserTbl) bool {
	engine := getDBEngine()
	rows,err := engine.Insert(user)
	if err != nil {
		log.Println(err)
		return false
	}
	if rows == 0 {
		return false
	}
	return true
}

//Delete (delete by name)
func DeleteUser(name string)  bool {
	user := UserTbl{
		Username:name,
	}
	engine := getDBEngine()
	rows,err := engine.Delete(&user)
	if err != nil {
		log.Println(err)
		return false
	}
	if rows == 0 {
		return false
	}
	return true
}

//Using sql to delete
func DeleteUserBySQL(name string) bool {
	engine := getDBEngine()
	result,err := engine.Exec("delete from user_tbl where username=?",name)
	if err != nil {
		log.Println(err)
		return false
	}
	rows,err :=result.RowsAffected()
	if err == nil && rows >0 {
		return true
	}
	return false
}

//to update
func UpdateUser(user *UserTbl) bool {
	engine := getDBEngine()
	//Update (bean interface {}, condibeans... Interface {}) bean is the bean to be updated, condibeans is the condition
	rows,err := engine.Update(user,UserTbl{Id:user.Id})
	if err != nil {
		log.Println(err)
		return false
	}
	if rows > 0 {
		return true
	}
	return false
}

//Using session to add, delete and modify
//The advantage of using session is that transaction processing is possible
func SessionUserTest(user *UserTbl) {
	engine := getDBEngine()
	session := engine.NewSession()
	session.Begin()
	_,err := session.Insert(user)
	if err != nil {
		session.Rollback()
		log.Fatal(err)
	}

	user.Username="windows"
	_,err = session.Update(user,UserTbl{Id:user.Id})
	if err != nil {
		session.Rollback()
		log.Fatal(err)
	}

	_,err = session.Delete(user)
	if err != nil {
		session.Rollback()
		log.Fatal(err)
	}

	err = session.Commit()
	if err != nil {
		log.Fatal(err)
	}

}




   

    

Tags: github Session SQL Database

Posted on Wed, 20 May 2020 12:10:59 -0400 by phpfreak