go使用sqlite例子

package main

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

	_ "modernc.org/sqlite"
)

func main() {
	// 连接到 SQLite 数据库（如果文件不存在则会创建）
	db, err := sql.Open("sqlite", "./test.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 创建表
	createTableSQL := `
	CREATE TABLE IF NOT EXISTS orders (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		orderId TEXT NOT NULL UNIQUE,
		tradeName TEXT NOT NULL,
		CommodityPicture TEXT,
		notes TEXT,
		waybillAddress TEXT NOT NULL,
		isPrinted BOOLEAN NOT NULL DEFAULT 0,
		printCount INTEGER NOT NULL DEFAULT 0
	);`
	_, err = db.Exec(createTableSQL)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("表创建成功!")

	// CRUD 操作
	// 1. 插入数据
	insertOrder(db, "ORD001", "TradeName1", "pic1.jpg", "Note1", "Address1")

	// 2. 读取数据
	readOrders(db)

	// 3. 更新数据
	updateOrder(db, "ORD001", "Updated TradeName", "Updated Address")

	// 4. 删除数据
	deleteOrder(db, "ORD001")
}

// 插入新订单的函数
func insertOrder(db *sql.DB, orderId, tradeName, CommodityPicture, notes, waybillAddress string) {
	insertSQL := `INSERT INTO orders(orderId, tradeName, CommodityPicture, notes, waybillAddress) VALUES (?, ?, ?, ?, ?)`
	_, err := db.Exec(insertSQL, orderId, tradeName, CommodityPicture, notes, waybillAddress)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("插入订单成功，订单 ID:", orderId)
}

// 读取所有订单的函数
func readOrders(db *sql.DB) {
	readSQL := `SELECT id, orderId, tradeName, CommodityPicture, notes, waybillAddress, isPrinted, printCount FROM orders`
	rows, err := db.Query(readSQL)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id, printCount int
		var orderId, tradeName, CommodityPicture, notes, waybillAddress string
		var isPrinted bool

		err := rows.Scan(&id, &orderId, &tradeName, &CommodityPicture, &notes, &waybillAddress, &isPrinted, &printCount)
		if err != nil {
			log.Fatal(err)
		}

		fmt.Printf("订单: %d | %s | %s | %s | %s | %s | %t | %d\n", id, orderId, tradeName, CommodityPicture, notes, waybillAddress, isPrinted, printCount)
	}
}

// 更新订单的函数
func updateOrder(db *sql.DB, orderId, tradeName, waybillAddress string) {
	updateSQL := `UPDATE orders SET tradeName = ?, waybillAddress = ? WHERE orderId = ?`
	_, err := db.Exec(updateSQL, tradeName, waybillAddress, orderId)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("更新订单成功，订单 ID:", orderId)
}

// 删除订单的函数
func deleteOrder(db *sql.DB, orderId string) {
	deleteSQL := `DELETE FROM orders WHERE orderId = ?`
	_, err := db.Exec(deleteSQL, orderId)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("删除订单成功，订单 ID:", orderId)
}