golang与 postgresql简单的增删改查
程序员文章站
2022-03-28 20:15:13
创建数据库和表如下 创建表SQL语句 CREATE TABLE userinfo( uid serial NOT NULL, username character varying(100) NOT NULL, departname character varying(500) NOT NULL, C ......
创建数据库和表如下
创建表sql语句
create table userinfo
(
uid serial not null,
username character varying(100) not null,
departname character varying(500) not null,
created date,
constraint userinfo_pkey primary key (uid)
)
with (oids=false);
create table userdeatail
(
uid integer,
intro character varying(100),
profile character varying(100)
)
with(oids=false);
golang代码
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
"log"
"time"
)
const (
host = "localhost"
port = 5432
user = "postgres"
password = "atenobserve"
dbname = "testdb"
)
type userinfo struct {
uid int
username string
departname string
created string `sql:"type:timestamp"`
}
type appcontext struct {
db *sql.db
}
func main() {
c, err := connectdb()
defer c.db.close()
if err != "" {
print(err)
}
c.create()
fmt.println("add action done!")
c.read()
fmt.println("get action done!")
c.update()
fmt.println("update action done!")
c.delete()
fmt.println("delete action done!")
}
func connectdb() (c *appcontext, errormessage string) {
psqlinfo := fmt.sprintf("host=%s port=%d user=%s "+
"password=%s dbname=%s sslmode=disable",
host, port, user, password, dbname)
db, err := sql.open("postgres", psqlinfo)
if err != nil {
fmt.println("连接数据库错误:"+err.error())
}else {
fmt.println("successfully connected!")
}
err = db.ping()
if err != nil {
fmt.println("dbping错误:"+err.error())
}else
{
fmt.println("dbpingsuccess")
}
//db, err := sql.open(drivername, dbname)
//if err != nil {
// return nil, err.error()
//}
//if err = db.ping(); err != nil {
// return nil, err.error()
//}
return &appcontext{db}, ""
}
// create
func (c *appcontext) create() {
// get insert id
lastinsertid := 0
now_str:=time.now().format("2006-01-02 15:04:05")
err := c.db.queryrow("insert into userinfo(username,departname,created) values($1,$2,$3) returning uid", "cruise", "软件部",now_str).scan(&lastinsertid)
if err != nil {
log.fatal(err)
}
fmt.println("inserted id is ", lastinsertid)
}
// read
func (c *appcontext) read() {
rows, err := c.db.query("select * from userinfo")
if err != nil {
fmt.println(err.error())
return
}
defer rows.close()
for rows.next() {
p := new(userinfo)
err := rows.scan(&p.uid, &p.username, &p.departname,&p.created)
if err != nil {
fmt.println(err)
}
fmt.println(p.uid, p.username, p.departname,p.created)
}
}
// update
func (c *appcontext) update() {
stmt, err := c.db.prepare("update userinfo set username = $1 where uid = $2")
if err != nil {
log.fatal(err)
}
result, err := stmt.exec("jack", 1)
if err != nil {
log.fatal(err)
}
affectnum, err := result.rowsaffected()
if err != nil {
log.fatal(err)
}
fmt.println("update affect rows is ", affectnum)
}
// delete
func (c *appcontext) delete() {
stmt, err := c.db.prepare("delete from userinfo where uid = $1")
if err != nil {
log.fatal(err)
}
result, err := stmt.exec(1)
if err != nil {
log.fatal(err)
}
affectnum, err := result.rowsaffected()
if err != nil {
log.fatal(err)
}
fmt.println("delete affect rows is ", affectnum)
}
上一篇: Appium自动化环境安装
下一篇: A5创业项目春季招商 好项目招代理无忧