Home Backend Development Golang How to interact with JSON data using SQL in Golang?

How to interact with JSON data using SQL in Golang?

Jun 03, 2024 am 11:47 AM
sql golang

在 Golang 中通过 SQL 与 JSON 数据交互共有以下步骤:使用 json.Unmarshal 函数将 JSON 数据解析为 Go 结构体,实现 JSON 到结构体的转换。使用 database/sql 包访问和操作 SQL 数据库,执行诸如插入、查询等操作。结合上述步骤,可以在 Go 中构建基于 SQL 和 JSON 的应用,实现用户注册和登录等功能。

如何在 Golang 中使用 SQL 与 JSON 数据交互?

如何在 Golang 中使用 SQL 与 JSON 数据交互

Golang 提供了强大的库和包,使使用 SQL 与 JSON 数据交互变得轻而易举。以下是如何在 Golang 中实现 SQL 与 JSON 交互:

使用 json.Unmarshal

json.Unmarshal 函数可将 JSON 数据解析为 Golang 结构体。例如:

type User struct {
    ID      int64  `json:"id"`
    Name    string `json:"name"`
    Email   string `json:"email"`
    Created int64  `json:"created"`
}

var jsonString = `{
    "id": 1,
    "name": "John Doe",
    "email": "john@example.com",
    "created": 1587398123
}`

var user User
err := json.Unmarshal([]byte(jsonString), &user)
if err != nil {
    fmt.Println("Error:", err)
}
Copy after login

使用 database/sql

database/sql 包提供了访问和操作 SQL 数据库的功能。示例如下:

import (
    "database/sql"
    "log"
)

func main() {
    db, err := sql.Open("postgres", "user:password@host:port/dbname")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    jsonValue := "{ \"name\": \"Joe\", \"age\": 30 }"

    // 从 JSON 中创建 SQL struct
    type User struct {
        Name string
        Age  int
    }
    user := User{Name: "Joe", Age: 30}

    // 将 SQL struct 转换为 JSON
    userJSON, err := json.Marshal(user)
    if err != nil {
        log.Fatal(err)
    }

    // 使用 JSONB 类型插入 JSON 值
    _, err = db.Exec("INSERT INTO users (name, age) VALUES ($1, $2)", jsonValue, user.Age)
    if err != nil {
        log.Fatal(err)
    }

    // 查询 JSON 值
    rows, err := db.Query("SELECT * FROM users")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var name string
        var age int
        var jsonString string
        if err := rows.Scan(&name, &age, &jsonString); err != nil {
            log.Fatal(err)
        }

        println(name, age, jsonString)
    }
}
Copy after login

实战案例:用户注册与登录

我们可以利用 Go 的 SQL 与 JSON 交互能力构建一个简单的用户注册和登录 API:

注册 API

func register(w http.ResponseWriter, r *http.Request) {
    var user User

    if err := json.NewDecoder(r.Body).Decode(&user); err != nil {
        http.Error(w, "Invalid JSON", http.StatusBadRequest)
        return
    }

    // 将 JSON 转换为 SQL struct
    sqlUser := sqlx.Named("name", user.Name).Named("password", user.Password)

    // SQL 查询插入
    query := `INSERT INTO users (name, password) VALUES (:name, :password)`

    if _, err := db.NamedExec(query, sqlUser); err != nil {
        http.Error(w, "Could not register user", http.StatusInternalServerError)
        return
    }

    fmt.Fprintf(w, "User registered successfully")
}
Copy after login

登录 API

func login(w http.ResponseWriter, r *http.Request) {
    var user User

    if err := json.NewDecoder(r.Body).Decode(&user); err != nil {
        http.Error(w, "Invalid JSON", http.StatusBadRequest)
        return
    }

    // SQL 查询
    query := `SELECT * FROM users WHERE name = $1 AND password = $2`

    var result User
    if err := db.Get(&result, query, user.Name, user.Password); err != nil {
        http.Error(w, "Invalid user credentials", http.StatusUnauthorized)
        return
    }

    // 将 SQL struct 转换为 JWT 令牌
    tokenString, err := createJWT(result)
    if err != nil {
        http.Error(w, "Could not create JWT", http.StatusInternalServerError)
        return
    }

    fmt.Fprintf(w, "User authenticated. Token: %s", tokenString)
}
Copy after login

The above is the detailed content of How to interact with JSON data using SQL in Golang?. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to safely read and write files using Golang? How to safely read and write files using Golang? Jun 06, 2024 pm 05:14 PM

Reading and writing files safely in Go is crucial. Guidelines include: Checking file permissions Closing files using defer Validating file paths Using context timeouts Following these guidelines ensures the security of your data and the robustness of your application.

How to configure connection pool for Golang database connection? How to configure connection pool for Golang database connection? Jun 06, 2024 am 11:21 AM

How to configure connection pooling for Go database connections? Use the DB type in the database/sql package to create a database connection; set MaxOpenConns to control the maximum number of concurrent connections; set MaxIdleConns to set the maximum number of idle connections; set ConnMaxLifetime to control the maximum life cycle of the connection.

Similarities and Differences between Golang and C++ Similarities and Differences between Golang and C++ Jun 05, 2024 pm 06:12 PM

Golang and C++ are garbage collected and manual memory management programming languages ​​respectively, with different syntax and type systems. Golang implements concurrent programming through Goroutine, and C++ implements it through threads. Golang memory management is simple, and C++ has stronger performance. In practical cases, Golang code is simpler and C++ has obvious performance advantages.

How steep is the learning curve of golang framework architecture? How steep is the learning curve of golang framework architecture? Jun 05, 2024 pm 06:59 PM

The learning curve of the Go framework architecture depends on familiarity with the Go language and back-end development and the complexity of the chosen framework: a good understanding of the basics of the Go language. It helps to have backend development experience. Frameworks that differ in complexity lead to differences in learning curves.

How to generate random elements from list in Golang? How to generate random elements from list in Golang? Jun 05, 2024 pm 04:28 PM

How to generate random elements of a list in Golang: use rand.Intn(len(list)) to generate a random integer within the length range of the list; use the integer as an index to get the corresponding element from the list.

Comparison of advantages and disadvantages of golang framework Comparison of advantages and disadvantages of golang framework Jun 05, 2024 pm 09:32 PM

The Go framework stands out due to its high performance and concurrency advantages, but it also has some disadvantages, such as being relatively new, having a small developer ecosystem, and lacking some features. Additionally, rapid changes and learning curves can vary from framework to framework. The Gin framework is a popular choice for building RESTful APIs due to its efficient routing, built-in JSON support, and powerful error handling.

What are the best practices for error handling in Golang framework? What are the best practices for error handling in Golang framework? Jun 05, 2024 pm 10:39 PM

Best practices: Create custom errors using well-defined error types (errors package) Provide more details Log errors appropriately Propagate errors correctly and avoid hiding or suppressing Wrap errors as needed to add context

golang framework document usage instructions golang framework document usage instructions Jun 05, 2024 pm 06:04 PM

How to use Go framework documentation? Determine the document type: official website, GitHub repository, third-party resource. Understand the documentation structure: getting started, in-depth tutorials, reference manuals. Locate the information as needed: Use the organizational structure or the search function. Understand terms and concepts: Read carefully and understand new terms and concepts. Practical case: Use Beego to create a simple web server. Other Go framework documentation: Gin, Echo, Buffalo, Fiber.

See all articles