MySQL Collection Index
Connecting to MySQL and making a Database
Making a Desk and Inserting Rows
Deciding on Single Row and A number of rows
Ready statements – WIP
Updating rows – WIP
Deleting rows – WIP
Welcome to tutorial no. 3 in our MySQL tutorial sequence. Within the earlier tutorial, we mentioned making a desk and inserting rows into the desk. On this tutorial, we are going to discover ways to choose a single row in addition to a number of rows from a desk.
Deciding on a single row
The product desk we created within the earlier tutorial has three rows. Working choose * from product; on the desk returns the next rows.
On this part of the tutorial, we are going to write code to pick a single row from this desk. We’ll write a operate that may return the product value when the product identify is given because the enter. The question to do that is supplied under.
choose product_price from product the place product_name = "iphone";
The above question will return one row and can return the product_price column from that row. The above question will return 950
Now that we have now the question prepared, let’s go forward and code it.
Step one is to create a ready assertion. Ready statements are used to parametrize a SQL question in order that the identical question might be run with totally different arguments effectively. It additionally prevents sql injection.
The template for the ready assertion is supplied under.
choose product_price from product the place product_name = ?
We’ve got changed iphone with ? to create the ready assertion template.
The subsequent step is to create the ready assertion. That is carried out utilizing the PrepareContext methodology.
func selectPrice(db *sql.DB, productName string) (int, error) {
question := `choose product_price from product the place product_name = ?`
ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
defer cancelfunc()
stmt, err := db.PrepareContext(ctx, question)
if err != nil {
log.Printf("Error %s when making ready SQL assertion", err)
return 0, err
}
defer stmt.Shut()
...
}
We move a context with a 5 second timeout to the PrepareContext methodology in line no. 5. This methodology returns a ready assertion. We defer closing the assertion in line no. 10.
The subsequent step is to execute this ready assertion. That is carried out utilizing the QueryRowContext methodology.
var value int
row := stmt.QueryRowContext(ctx, productName)
if err := row.Scan(&value); err != nil {
return 0, err
}
The QueryRowContext methodology is named on the ready assertion stmt. This methodology takes a context and a variadic record of arguments as parameter. In our case, we have now just one argument within the question which is the product identify and we move this because the argument to this methodology.
The QueryRowContext returns a single row. We then have to name Scan on the row to repeat the columns returned by the question to the pointer handed to the Scan methodology. Right here we move an integer pointer in line no. 3 of the above code snippet to which the worth might be saved. If the question returns multiple row, the Scan methodology will return solely the primary row and there might be no error. If there are not any rows returned, Scan will return an error. We’ll focus on error dealing with within the subsequent part.
The entire operate is supplied under.
func selectPrice(db *sql.DB, productName string) (int, error) {
log.Printf("Getting product value")
question := `choose product_price from product the place product_name = ?`
ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
defer cancelfunc()
stmt, err := db.PrepareContext(ctx, question)
if err != nil {
log.Printf("Error %s when making ready SQL assertion", err)
return 0, err
}
defer stmt.Shut()
var value int
row := stmt.QueryRowContext(ctx, productName)
if err := row.Scan(&value); err != nil {
return 0, err
}
return value, nil
}
Error dealing with
The Scan methodology utilized in line no. 14 of the above operate will return error ErrNoRows when no rows are returned by the question. Let’s name our selectPrice operate from important the place will deal with errors.
func important() {
...
productName := "iphone"
value, err := selectPrice(db, productName)
change {
case err == sql.ErrNoRows:
log.Printf("Product %s not present in DB", productName)
case err != nil:
log.Printf("Encountered err %s when fetching value from DB", err)
default:
log.Printf("Worth of %s is %d", productName, value)
}
}
We move product identify as iphone to selectPrice after which change case on the error. If the error is of sort ErrNoRows we print a message saying that the product will not be discovered. In case of another error, we print the error message. And within the default case we print the end result.
This program will print
Worth of iphone is 950
Deciding on a number of rows
On this part, we are going to discover ways to choose a number of rows from a desk. We’ll write a operate that may take minimal value and most value as parameters and return the identify and value of the merchandise that match the question.
The question that does that’s supplied under.
choose product_name, product_price from product the place product_price >= 900 && product_price <= 1000;
The above question will return the identify and value of all merchandise whose costs fall between 900 and 1000.
A lot of the steps for choosing a number of rows are just like choosing a single row which we mentioned within the earlier part. I’ve supplied the entire operate to pick a number of rows under.
func selectProductsByPrice(db *sql.DB, minPrice int, maxPrice int) ([]product, error) {
log.Printf("Getting merchandise by value")
question := `choose product_name, product_price from product the place product_price >= ? && product_price <= ?;`
ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
defer cancelfunc()
stmt, err := db.PrepareContext(ctx, question)
if err != nil {
log.Printf("Error %s when making ready SQL assertion", err)
return []product{}, err
}
defer stmt.Shut()
rows, err := stmt.QueryContext(ctx, minPrice, maxPrice)
if err != nil {
return []product{}, err
}
defer rows.Shut()
var merchandise = []product{}
for rows.Subsequent() {
var prd product
if err := rows.Scan(&prd.identify, &prd.value); err != nil {
return []product{}, err
}
merchandise = append(merchandise, prd)
}
if err := rows.Err(); err != nil {
return []product{}, err
}
return merchandise, nil
}
Just like the earlier part, we first created a ready assertion in line no. 6 of the above operate. After that we name QueryContext methodology on the ready assertion stmt in line no. 12. This methodology will return the record of rows chosen by the question. We defer closing the rows in line no. 16.
After getting the row record, we name Subsequent methodology on rows in line no. 18. This prepares the end result for studying utilizing the Scan methodology. If there’s any error when calling Subsequent(), it’s going to return false and the for loop will terminate. In case of error, Err() methodology on rows will return a non nil worth and we have to name that to search out out if there was any error when Subsequent() was known as. That is carried out in line no. 25.
If there isn’t any error when fetching the rows, rows is closed mechanically by the Subsequent() methodology name in line no. 18. If there’s any error, it’s essential to name Shut explicitly. For the reason that name to Shut() is idempotent, it is alright to name it twice i.e as soon as through the efficiently Subsequent() name and once more throughout defer in line no. 16. Therefore we have now deferred the rows shut in line no. 16.
In line no. 19 we create prd of sort product to retailer the end result.
In case you do not bear in mind, product struct has the next fields.
sort product struct {
identify string
value int
}
In line no. 20, the end result columns are copied to the identify and value fields of the prd struct and in line no. 23, prd is appended to the merchandise slice.
merchandise is returned in line no. 28.
The subsequent step is to name this operate from important.
func important() {
...
minPrice := 900
maxPrice := 1000
merchandise, err := selectProductsByPrice(db, minPrice, maxPrice)
if err != nil {
log.Printf("Error %s when choosing product by value", err)
return
}
for _, product := vary merchandise {
log.Printf("Title: %s Worth: %d", product.identify, product.value)
}
We move 900 and 1000 because the minimal and most value to the selectProductsByPrice operate in line no. 5.
If there’s any error, we return after printing the error.
If there isn’t any error, the identify and value of the merchandise matching the question are printed in line no. 11.
This program will print,
2021/10/16 21:10:52 Getting merchandise by value
2021/10/16 21:10:52 Title: iphone Worth: 950
2021/10/16 21:10:52 Title: Galaxy Worth: 990
This brings us to the top of this tutorial. Please share your priceless suggestions within the feedback.
The whole code is out there at https://github.com/golangbot/mysqltutorial/blob/grasp/choose/important.go
If you want to promote on this web site, rent me, or when you have another improvement necessities, please e mail to naveen[at]golangbot[dot]com.
Like my tutorials? Please present your assist by donating. Your donations will assist me create extra superior tutorials.
Earlier tutorial – Making a Desk and Inserting Rows
