This is the 27th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

preface

Above, the implementation of a single row of data query operation, now we will implement another common operation in another CURD — data insertion. This change is explained in a top-down fashion, from final invocation to SQL statement generation.

1. Use Insert

r := &models.Role {
   Name:         "Admin",
   Introduction: "Administrator",
}
err := models.Insert(r)
iferr ! =nil {
   fmt.Println(err)
}
Copy the code

Our ultimate goal is to build an Insert function that inserts data into the database using the database structure object we created earlier and returns any errors that might occur.

Write the Insert function

The Insert() function has two parts: generating SQL statements and delivering SQL statements to the database for execution and catching errors. The former is the most difficult part.

Here is a simple Insert function that does what our Insert function above (where the primary key ID is incremented by the serial data type) :

INSERT INTO Role(Name,Introduction)
VALUES ('Admin'.'Administrator')
Copy the code

As you already know from the previous two articles, we need to use reflect.typeof () to extract all fields of the Role object.

However, this is not enough, because many times the Role object has some field values that are empty, indicating that the user does not want these fields assigned.

Since the object value is retrieved using reflect.valueof (), we need to do further judgment after reading the value and discard the null value field.

The fields and values are exactly one-to-one, which can be easily stored using map and used to concatenate SQL statements in the subsequent process.

Deliver and execute SQL statements

The string concatenation section is relatively simple (but the main line ending comma needs to match), so it is not expanded. Finally, the SQL statement can be delivered to the database using DB.exec (SQL) to implement the insert operation.

Four, complete code implementation

/ / Insert Insert
func Insert(model interface{}) error {
   // Insert existing fields
   t := reflect.TypeOf(model)
   mp := make(map[string]string)
   for i := 0; i < t.Elem().NumField(); i++ {
      field := t.Elem().Field(i)
      name := field.Name
      value := reflect.ValueOf(model).Elem().FieldByName(name).String()
      if value == "" {
         continue
      }
      mp[name] = value
   }
   tableName := strings.Split(t.String(), ".") [1]
   var kStr string
   var vStr string
   for k, v := range mp {
      kStr += k + ","
      vStr += "'" + v + "',"
   }
   kStr = kStr[:len(kStr)- 1]
   vStr = vStr[:len(vStr)- 1]
   sql := fmt.Sprintf("INSERT INTO %s(%s)\nVALUES (%s)", tableName, kStr, vStr)
   logrus.Debugln(sql)
   _, err := db.Exec(sql)
   return err
}
Copy the code