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

preface

Above, we realized automatic table building on OpenGauss database through go language structure. In this paper, we will realize single data query operation on the basis of the above structure.

Generate SQL statements

The basis of a query is to construct a correct SQL statement. For the query operation, we directly use SELECT * to obtain the row fields. We need to construct the following statement:

SELECT * FROM table WHERE id = 1;
Copy the code

As you can see, we need to implement the query operation on all tables. We need two variables:

  1. Query the table name
  2. The constraint

For table names, we use reflect.typeof (model) directly to reflect the table name:

   t := reflect.TypeOf(model)
   tableName := strings.Split(t.String(), ".") [1]
Copy the code

For constraints, it can be directly output as a parameter, and the completion code is as follows:

// First Queries a single field by name
// odds => WHERE id = 1 || LIKE name = '%cj%' || ......
func First(model interface{}, odds string) error {
   // reflection gets the table name
   t := reflect.TypeOf(model)
   tableName := strings.Split(t.String(), ".") [1]
   // Build the query and deliver the database query
   sql := "SELECT * FROM " + tableName + "" + odds
   return getFirst(model, sql)
}
Copy the code

Read returned data

It is easy to build the SQL statement above, but difficult to implement the returned Model object that will be assigned to the output. Here are several parts to implement:

First, the SQL statement to the database query

   logrus.Debugln(sql)
   rows, err := db.Query(sql)
   iferr ! =nil {
      return err
   }
   defer rows.Close()
Copy the code

First, we use db.query (SQL), deliver the SQL to the openGauss database, and then return a Rows object. At this point, if no errors are reported, the data returned by the database will be stored in Rows.

2. Store returned data

To read data from rows, we need to execute rows.next (), which returns a bool, true if it can be read, false otherwise.

Also, line feeds are wrapped in rows.next (), so you need to execute rows.next () once before you can read the data.

The build code is as follows:

   if! rows.Next() {return errors.New("sql: Scan called without calling Next")}Copy the code

Here we read only the first line. Multi-line reads can be done through a for loop, but there is a big difference in assignment, which we’ll discuss later.

Rows.Columns (len()) reads the number of Columns, and constructs a slice with that length:

   columns, err := rows.Columns()
   iferr ! =nil {
      return err
   }
Copy the code

The reason for this data is that we need to use rows.scan () to read the data. The Scan function takes the following parameters:

The number of arguments passed in must be the same as the number returned by SELECT, otherwise an error will be reported, so we use a feature of slice that uses… Also pass a mutable input parameter.

The code implementation is as follows. Since Scan input is a pointer type, values need to be initialized before input:

   values := make([]interface{}, len(columns))
   for i := range values {
      values[i] = new(string)}iferr := rows.Scan(values...) ; err ! =nil {
      return err
   }
Copy the code

And then, and then the return value will be stored in the values value.

3. Build map and transfer data

Since the data in slice has no field data, we need to use the columns and values above to build a map to store the read data, as shown in the code.

The code implementation is as follows:

   // create a map and transfer the array data to the map cache
   m := make(map[string]interface{})
   for i, column := range columns {
      m[strings.ToUpper(column[:1]) + column[1:]] = *values[i].(*string)}Copy the code

4. Reflect data to the original object

Then comes the most important part of the query operation: reflecting the data back into the original object.

The reflect.valueof () function is used here to get the Value of the original object and pass the Value below it. The Elem().fieldbyname function, which uses the field name to get the value position of the field we want to change, and the SetString() function to do the assignment. The field name used is the same as before, obtained via reflect.typeof () and then iterated.

The code implementation is as follows:

func getFirst(model interface{}, sql string) error {
   // use reflection to map the map to the original structure
   t := reflect.TypeOf(model)
   for i := 0; i < t.Elem().NumField(); i++ {
      field := t.Elem().Field(i)
      ifm[field.Name] ! =nil {
         v := reflect.ValueOf(model).Elem().FieldByName(field.Name)
         v.SetString(m[field.Name].(string))}}return nil
}
Copy the code

Call a function

Construct an empty structure and call the function alignment assignment:

r := &models.Role{}
err := models.First(r, "WHERE id = 1")
iferr ! =nil {
   fmt.Println(err)
}
fmt.Println(r)
Copy the code

The output is as follows: