In the previous section, we introduced the mapping rules and operations of xORM framework table structures. In this lesson, we will continue our in-depth study of basic table structure operations and advanced query functions.

Table structure basic operations

The most common operations on table structures are query and statistical correlation methods. Let’s start with correlation implementations:

  • Conditions of the query

    • Id value query: Parameter receives the value of the primary key field. Such as:

      var user User
engine.Id(1).Get(&user)Copy the code

      The above Id method is equivalent to the following SQL statement:

    select * from user where id = 1Copy the code

    If the database table structure is a compound primary key, specify the primary key separately when using ids, as in the following example:

    engine.Id(core.PK(1,"davie").Get(&user)Copy the code

    The order of the arguments passed in core.pk should be the same as the order of the primary keys in the structure definition, otherwise the match will fail. The corresponding SQL statement is as follows:

    select * from user where id = 1 and name = 'davie'Copy the code

    • Where Condition query THE Where condition query function is consistent with that in Sql statements. Let’s take a look at the use of Where as follows:

    engine.Where(" user_name = ? and pwd = ? ".'davie'.'123').Get(&admin)Copy the code

    The above xORM engine operation is the function of the following SQL statement:

    select * from admin where user_name = 'davie' and pwd = '123'Copy the code

    • And is used in a similar way to the Where statement to query results as parallel conditions And constraints. The usage is as follows:

    engine.Id(1).And(" user_name = ?".'davie").Get(&user)Copy the code

    The above And method is equivalent to the following SQL statement:

    select * from user where id = 1 and user_name = 'davie' Copy the code

    • An Or condition query Or query mean Or mean, and programming language of “| |” function is the same, in the query, if there are multiple conditions Or statements, is for the same data, as long as meet one of the conditions, it was queried. The specific usage method and corresponding SQL statements are as follows:

    engine.Id(1).Or("user_name = ?".'davie').Get(&user)Copy the code

    The above Or operation statement corresponds to the following SQL statement:

    select * from user where id = 1 or user_name = davieCopy the code

    • In addition to the Id, Where, And, Or methods mentioned above, XORm also supports the execution of native Sql statements, which is convenient for developers who prefer to use native Sql statements.

    engine.SQL(" select * from user where id = 1 nad user_name = 'davie')Copy the code

    • Sorting condition query involves the sorting of query results in database query. General operations support two sorts: forward sort and reverse sort. The implementation in XORM is basically the same as the two sorting methods in SQL statements, which are defined as Asc method and Desc method respectively. Or use the Order Derby method to specify custom sorted fields. Note: All three methods take strings to specify the table fields.

    • In Multi-value range Query The multi-value range query of the In method is used for the conditional query of a field. This method requires two parameters: the first parameter is the field to be queried, and the second parameter is the content of the multi-value field. We also give the following cases:

    engine.In('user_name'.'davie'.'pony'.'jack').Find()Copy the code

    • The Cols specific field query Cols method can accept one or more characteristic table field names that are used to denote operation-specific table fields. Still through the case to illustrate:

        engine.Cols("user_name"."status").Find(&admins)
  //select user_name, status from adminCopy the code

      The SQL statement represented by the above Cols operation is the SQL statement corresponding to the annotation. It means to query the specific user_name and status fields from the admin table and return the queried set.

        engine.Cols("user_name"."status").Update(&admin)
  //update admin set user_name = admin.User_name  and status = admin.StatusCopy the code

      As you can see, in addition to the Find method, you can also call the Update method, which updates a piece of data in the table structure with only the user_name and status fields, which are defined by the parameters of the Cols method.

    • In addition to the above Cols specified one or more fields, you can also use the AllCols method to operate on all fields of the table. The usage is consistent with the Cols method.

    • MustCols operations qualify fields MustCols means that an operation must act on certain fields, and is used more often in conjunction with the Update method.

Add, delete, change and check operation

With all this talk about conditional queries, we have yet to cover the basic operations of adding, deleting, modifying and querying table records. We introduce them one by one as follows:

  • Adding a record Operation Adding a record can be done using the Insert method. This method takes a parameter to pass in the structure object type of the actual data object we want to save. As follows:

    var user
...
engine.Insert(&user)Copy the code

  • Delete Delete record operation Delete data use the Delete method to operate, but we need to know the specific data to Delete when deleting, so before Delete operation, we need to use the Id operation we talked about before to locate the data to find out, let’s take the following example:

      user := new(User)
  count,err := engine.Id(id).Delete(user)Copy the code

    The first return value, count, is the number of records deleted. The second argument is the error return value. When deletion fails, err is not nil.

  • We’ve already seen that the MustCols operation is used to restrict operations that must affect certain table fields; Again we emphasize that we usually modify the data together with the Update operation.

      admin := new(Admin)
  admin.Status = "1"
  count,err := engine.Id(id).Update(user)Copy the code

    The above Update method is used to Update all data records, and can be used in conjunction with Cols if the Update is limited to one or more fields.

  • Querying records

    There are two query methods, including single data query and multiple data query. The Get method is used to query a single piece of data, and the Find method is used to query multiple pieces of data.

Count Table structure statistics function

In table data query, we need the number of statistical data, such as the total number of data, used for paging processing; For example, counting the number of users also needs to use the counting function. The framework provides us with the Count method to implement the data statistics function. Here is a simple example:

admin := new(Admin)
count,err := engine.Count(admin)Copy the code

Transaction processing

Like other database frameworks, XORM supports transactional operations. The use of transactions is often necessary when there is a need to batch manipulate data. In XORM, transaction operations are associated with sessions. The three steps are as follows: Create a session object. Begin indicates that the transaction starts. Commit indicates that the transaction is committed. In the middle are specific database operations.

Create a transaction
session := engine.NewSession()
defer session.Close()Copy the code
The transaction start
Err := session.begin () // Operations related to transactionsCopy the code
Transaction commit
err = session.Commit()
iferr ! = nil{ panic(err.Error()) }Copy the code
Transaction rollback

When any errors are encountered during the execution of a transaction, the transaction should be stopped in time to roll back what has already been executed. The Rollback method is the Rollback method, which only rolls back in the event of an error.

.iferr ! = nil { session.Rollback()return 
}Copy the code

The intermediate transaction-related operations, like our normal database operations, allow developers to perform multiple operations according to their own needs. Such as inserting data and changing input:

  • Transaction insert data

    admin := Admin{Name: "Davie", Status: "1"}
_, err = session.Insert(&admin)Copy the code

  • Transaction modification data

    admin := Admin{ Name:"Davie"}
count,err := session.Id(2).Update(&admin)
iferr ! = nil { seesion.Rollback()return
}Copy the code