Detailed explanation of Go database query and structure mapping examples

The following is a tutorial on how to use Go to query databases and map data to structures, focusing on the explanationStructure field exportanddb tagUse of

Go database query and structure mapping tutorial

In Go, we can usedatabase/sqlorsqlxThe database interacts with the database. To facilitate mapping of database query results into structures, Go usesStructure field exportand**dbThe mechanism of label**.

This tutorial will explain in detail how to use these mechanisms correctly to perform database queries and avoid common errors.

1. Why do you need to export structure fields?

GoReflection mechanismTo access the fields of the structure. onlyExport fields(i.e., fields with capital letters) can only be accessed through reflection. If the field starts with lowercase letters, Go thinks it isPrivate, therefore cannot be used in database queries.

Error example: Structure field not exported

type AppEntry struct {
    key       string `db:"key"`       // Error: Field "key" is lowercase and will not be exported    appTypeId int64  `db:"app_type_id"` // Correct: The field "AppTypeId" is capitalized}

In the above code,keyFields start with lowercase letters, and database queries cannot map query results to this field.

2. Correct usage of export fields and db tags

In order for Go to correctly fill the query results into the structure through reflection mechanism, we need to ensure:

• The structure field isStart with capital letters(Right nowExport fields）。
• usedbTags to specify the mapping relationship between structure fields and database fields.

Correct example: Export the structure field and use the db tag

type AppEntry struct {
    Key       string `db:"key"`       // Correct: field "Key" is capitalized, and the db tag matches the database field "key"    AppTypeId int64  `db:"app_type_id"` // Correct: The field "AppTypeId" is capitalized, and the db tag matches the database field "app_type_id"}

In this example:

• Fields of structuresKeyandAppTypeIdIt starts with capital letters and meets the export field requirements of Go.
• dbTag tells Go database library, fieldsKeyMap to database tableskeyFields, fieldsAppTypeIdMap to database tablesapp_type_idField.

3. How to query the database and map the results to the structure

Once the structure is defined correctly, we can use Go's database library (e.g.sqlx) to execute the query and map the query results to the structure. Suppose we have a name calledsys_app_listWe will query the database table in the tablekeyandapp_type_idfield and map the result to the structureAppEntrymiddle.

Sample code: Query the database and map the data

package main
import (
    "database/sql"
    "fmt"
    "log"
    _ "/go-sql-driver/mysql" // Introduce MySQL driver)
type AppEntry struct {
    Key       string `db:"key"`
    AppTypeId int64  `db:"app_type_id"`
}
func main() {
    // Connect to the database    db, err := ("mysql", "user:password@tcp(localhost:3306)/your_database")
    if err != nil {
        (err)
    }
    defer ()
    // Execute query    rows, err := ("SELECT `key`, app_type_id FROM sys_app_list")
    if err != nil {
        (err)
    }
    defer ()
    // Read query results    var appEntries []AppEntry
    for () {
        var entry AppEntry
        if err := (&, &); err != nil {
            (err)
        }
        appEntries = append(appEntries, entry)
    }
    // Check if there is an error in the query    if err := (); err != nil {
        (err)
    }
    // Print query results    for _, entry := range appEntries {
        ("Key: %s, AppTypeId: %d\n", , )
    }
}

Code parsing:

• Database connection:useConnect to the MySQL database and usedefer ()Make sure to close the database connection when the program exits.
• Execute a query:passExecute SQL query, querysys_app_listThe tablekeyandapp_type_idField.
• Read the result:usePopulate the query result into the structureAppEntryofKeyandAppTypeIdin the field.
• Process query results: Store the query results inappEntriesSlice and print the output at the end of the program.

4. Common errors and troubleshooting

Common errors when using database queries include:

• Field not exported: As mentioned earlier, Go cannot access fields starting with lowercase letters, and you must make sure that the fields start with uppercase letters.
• db Label error: Ensure that the database field name and structure field name passdbThe label matches correctly. If the database field name and the structure field name are inconsistent, the tag must be specified explicitly.

Error Example: Field not exported or label error

type AppEntry struct {
    key       string `db:"key"` // Error: Field "key" is not exported and cannot be filled    appTypeId int64  `db:"app_type_id"` // Error: The field "appTypeId" is lowercase}

Correct example: field export and use db tags

type AppEntry struct {
    Key       string `db:"key"`       // Correct: field "Key" is the export field    AppTypeId int64  `db:"app_type_id"` // Correct: field "AppTypeId" is the export field}

5. Summary

• The structure field must be exported(i.e. capitalize the initial letter), otherwise the database library cannot access this field.
• usedbTags The relationship between the map structure field and the database field name.
• Query the database and map the resultsUsing Go's database library (e.g.sqlorsqlx) to ensure that the query results are processed correctly.

By following these rules, we can effectively query the database and map the results into the Go structure, thereby enabling data operation and processing.

This tutorial should help you understand how to use Go correctly for database queries and structure mapping.

This is the end of this article about Go database query and structure mapping. For more related contents of Go database query and structure mapping, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!