使用Golang实现从数据库获取数据并插入特定日志集的REST API

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "log"
    "net"
    "net/http"
    "os"
    "strings"
    "time"

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

// 日志数据结构
type LogEntry struct {
    ID            int       `json:"id"`
    ClientIP      string    `json:"client_ip"`
    ClientHost    string    `json:"client_host"`
    RequestMethod string    `json:"request_method"`
    RequestJSON   string    `json:"request_json"`
    ResponseJSON  string    `json:"response_json"`
    Timestamp     time.Time `json:"timestamp"`
}

// 全局变量
var (
    db         *sql.DB
    logDB      *sql.DB // 用于日志的数据库连接
    logFile    *os.File
    dbUser     = "your_username"
    dbPass     = "your_password"
    dbHost     = "localhost"
    dbPort     = "3306"
    dbName     = "your_database"
    logDBName  = "log_database"
    query      = "SELECT * FROM your_table"
)

// 初始化日志系统
func initLogging() error {
    var err error
    
    // 打开日志文件
    logFile, err = os.OpenFile("api.log", os.O_APPEND|os.O_CREATE|os.O_WRONLY, 0644)
    if err != nil {
        return err
    }
    
    // 连接日志数据库
    logDB, err = sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", 
        dbUser, dbPass, dbHost, dbPort, logDBName))
    if err != nil {
        return err
    }
    
    // 创建日志表（如果不存在）
    createTableSQL := `
    CREATE TABLE IF NOT EXISTS api_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        client_ip VARCHAR(45),
        client_host VARCHAR(255),
        request_method VARCHAR(10),
        request_json TEXT,
        response_json TEXT,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    )`
    
    _, err = logDB.Exec(createTableSQL)
    return err
}

// 获取客户端信息
func getClientInfo(r *http.Request) (string, string) {
    clientIP := r.RemoteAddr
    if forwarded := r.Header.Get("X-Forwarded-For"); forwarded != "" {
        clientIP = strings.Split(forwarded, ",")[0]
    }
    
    host, _, _ := net.SplitHostPort(clientIP)
    if host == "" {
        host = clientIP
    }
    
    hostname, _ := net.LookupAddr(host)
    clientHost := host
    if len(hostname) > 0 {
        clientHost = hostname[0]
    }
    
    return host, clientHost
}

// 记录日志到数据库和文件
func logRequest(clientIP, clientHost, method, reqJSON, respJSON string) error {
    timestamp := time.Now()
    
    // 写入数据库
    insertSQL := `INSERT INTO api_logs 
        (client_ip, client_host, request_method, request_json, response_json, timestamp) 
        VALUES (?, ?, ?, ?, ?, ?)`
    
    _, err := logDB.Exec(insertSQL, clientIP, clientHost, method, reqJSON, respJSON, timestamp)
    if err != nil {
        return err
    }
    
    // 写入文件
    logEntry := LogEntry{
        ClientIP:      clientIP,
        ClientHost:    clientHost,
        RequestMethod: method,
        RequestJSON:   reqJSON,
        ResponseJSON:  respJSON,
        Timestamp:     timestamp,
    }
    
    logJSON, _ := json.Marshal(logEntry)
    logFile.WriteString(string(logJSON) + "\n")
    
    return nil
}

// 中间件：记录日志
func loggingMiddleware(next http.HandlerFunc) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        // 获取请求体
        var requestBody map[string]interface{}
        json.NewDecoder(r.Body).Decode(&requestBody)
        reqJSON, _ := json.Marshal(requestBody)
        
        // 创建自定义ResponseWriter来捕获响应
        rw := &responseWriter{w, http.StatusOK, []byte{}}
        
        // 处理请求
        next(rw, r)
        
        // 获取客户端信息
        clientIP, clientHost := getClientInfo(r)
        
        // 记录日志
        err := logRequest(clientIP, clientHost, r.Method, string(reqJSON), string(rw.body))
        if err != nil {
            log.Printf("Failed to log request: %v", err)
        }
    }
}

// 自定义ResponseWriter
type responseWriter struct {
    http.ResponseWriter
    statusCode int
    body       []byte
}

func (rw *responseWriter) WriteHeader(code int) {
    rw.statusCode = code
    rw.ResponseWriter.WriteHeader(code)
}

func (rw *responseWriter) Write(b []byte) (int, error) {
    rw.body = b
    return rw.ResponseWriter.Write(b)
}

// 主处理函数
func Getdetails(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Content-Type", "application/json")
    
    rows, err := db.Query(query)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
    defer rows.Close()
    
    var results []Readinfo
    for rows.Next() {
        var data Readinfo
        err := rows.Scan(&data.Dbsrv, &data.Db, &data.Dbtable, &data.Dbref, &data.Dbreftime, &data.Dbrefdur)
        if err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        results = append(results, data)
    }
    
    json.NewEncoder(w).Encode(results)
}

// 初始化数据库连接
func CreateConnection() {
    var err error
    
    // 连接主数据库
    db, err = sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", 
        dbUser, dbPass, dbHost, dbPort, dbName))
    if err != nil {
        log.Fatal(err)
    }
    
    // 初始化日志系统
    err = initLogging()
    if err != nil {
        log.Fatal(err)
    }
    
    // 设置路由
    router := mux.NewRouter()
    router.HandleFunc("/read", loggingMiddleware(Getdetails)).Methods("POST")
    
    log.Println("Server starting on :8000")
    log.Fatal(http.ListenAndServe(":8000", router))
}

func main() {
    CreateConnection()
}

需要安装的依赖：

go get github.com/gorilla/mux
go get github.com/go-sql-driver/mysql

日志表SQL：

CREATE TABLE api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_ip VARCHAR(45),
    client_host VARCHAR(255),
    request_method VARCHAR(10),
    request_json TEXT,
    response_json TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

这个实现提供了：

1. 客户端IP和主机名获取
2. 请求和响应JSON记录
3. 同时写入MySQL数据库和文件
4. 使用中间件模式进行日志记录
5. 自定义ResponseWriter捕获响应内容