如何插入多行

Question

I am actually very new to Go so wanted to know the best way to insert data like this

{
    "moduleId":"M101",
    "topicId":["tt","ee"]
}

in MySQL database using Go

type TopicModule struct {
    ModuleId string   `json:"moduleId" bson:"moduleId" form:"moduleId"`
    TopicId  []string `json:"topicId" bson:"topicId" form:"topicId"`
    AddedBy  string   `json:"addedBy" bson:"addedBy" form:"addedBy"`
}

func AddTopicModuleHandler(ctx iris.Context) {
    topicmodule := new(TopicModule)
    if err := ctx.ReadJSON(topicmodule); err != nil {
        panic(err)
        ctx.StatusCode(400)
        return
    }
    log.Println(topicmodule.TopicId)
    code, created := AddTopicModule(*topicmodule)

    if created {
        ctx.JSON(topicmodule)
        ctx.Redirect("/api/module/"+code, iris.StatusCreated)
    }
}

func AddTopicModule(atm TopicModule) (string, bool) {

    log.Println("the topic is ", atm.TopicId)
    db := DatabaseAccess()
    tx, _ := db.Begin()
    stmt, err := tx.Prepare("insert into ModuleTopic(module_id, topic_id, added_by) Values(?,?,?) ")
    res, err := stmt.Exec(atm.ModuleId, "Ricky")
    res1, err := stmt.Exec(atm.ModuleId, "Ric")

    if err != nil {
        tx.Rollback()
    }
    tx.Commit()
    log.Println(res, res1)
    return "aa", true
}

The expected result is to add the array of JSON into MySQL.

Answer 1

You can not simply insert an array into the database. Instead, you should loop around the TopicId and insert them one by one

func AddTopicModule(atm TopicModule) (string, bool) {
    log.Println("the topic is ", atm.TopicId)
    db := DatabaseAccess()
    tx, _ := db.Begin()
    for _, value = range(atm.TopicId){
        stmt, err := tx.Prepare("insert into ModuleTopic(module_id, topic_id, added_by) Values(?,?,?) ")
        if err != nil {
            return "", false
        }
        res, err := stmt.Exec(atm.ModuleId, value, "Harsh")
        if err != nil {
            tx.Rollback()
            return "", false
        }
        tx.Commit()
    }
    return "aa", true
}

This will create 2 entries in the database for the JSON provided by you.

|---------------------|------------------|------------------|
|      module_id      |     topic_id     |     added_by     |
|---------------------|------------------|------------------|
|          M101       |         tt       |     Harsh        |
|---------------------|------------------|------------------|
|          M101       |         ee       |     Harsh        |
|---------------------|------------------|------------------|

To get them, simply query your database:

SELECT * FROM ModuleTopic WHERE module_id = M101;