Install Libraries
Make sure Git is installed on your machine and in your system’s PATH. Install the package to your $GOPATH with the go tool from shell:
$ go get github.com/go-sql-driver/mysql
Create Database
Create a database with the name is learngodb. This database have 1 tables: Product table.
--
-- Table structure for table `invoice`
--
CREATE TABLE `invoice` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(250) NOT NULL,
`orderDate` date NOT NULL,
`payment` varchar(250) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `invoice`
--
INSERT INTO `invoice` (`id`, `name`, `orderDate`, `payment`) VALUES
(1, 'Invoice 1', '2019-03-13', 'Paypal'),
(2, 'Invoice 2', '2019-03-21', 'Paypal'),
(3, 'Invoice 3', '2019-03-31', 'Cash'),
(4, 'Invoice 4', '2019-03-09', 'Cash');
Structure of Invoice Table
Data of Invoice Table
Create Entity
Create new folder named src. In src folder, create new folder named entities. In this folder, create new file named invoice_entity.go as below:
package entities
type Invoice struct {
Id int64
Name string
OrderDate string
Payment string
}
Create Config
In src folder, create new folder named config. In this folder, create new file named config.go, this file is used to connect mysql database:
package config
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func GetMySQLDB() (db *sql.DB, err error) {
dbDriver := "mysql"
dbUser := "root"
dbPass := "123456"
dbName := "learngodb"
db, err = sql.Open(dbDriver, dbUser + ":" + dbPass + "@/" + dbName)
return
}
Create Model
In src folder, create new folder named models. In models folder, create new file named invoice_model.go. This file contains methods to interact with the database.
package models
import (
"database/sql"
"entities"
"time"
)
type InvoiceModel struct {
Db *sql.DB
}
func (invoiceModel InvoiceModel) FindInvoicesBetween(from, to time.Time) ([]entities.Invoice, error) {
rows, err := invoiceModel.Db.Query("select * from invoice where orderDate between ? and ?", from.Format("2006-01-02"), to.Format("2006-01-02"))
if err != nil {
return nil, err
} else {
invoices := []entities.Invoice{}
for rows.Next() {
var id int64
var name string
var orderDate string
var status string
err2 := rows.Scan(&id, &name, &orderDate, &status)
if err2 != nil {
return nil, err2
} else {
invoice := entities.Invoice{id, name, orderDate, status}
invoices = append(invoices, invoice)
}
}
return invoices, nil
}
}
Structure of Project
Run Application
In src folder, create new file named main.go as below:
package main
import (
"config"
"fmt"
"models"
"time"
)
func main() {
db, err := config.GetMySQLDB()
if err != nil {
fmt.Println(err)
} else {
invoiceModel := models.InvoiceModel{
Db: db,
}
startDate, _ := time.Parse("2006-01-02", "2019-03-13")
endDate, _ := time.Parse("2006-01-02", "2019-03-25")
invoices, err2 := invoiceModel.FindInvoicesBetween(startDate, endDate)
if err2 != nil {
fmt.Println(err2)
} else {
for _, invoice := range invoices {
fmt.Println("Id:", invoice.Id)
fmt.Println("Name:", invoice.Name)
fmt.Println("Order Date:", invoice.OrderDate)
fmt.Println("Payment:", invoice.Payment)
fmt.Println("----------------------------")
}
}
}
}
Output
Id: 1
Name: Invoice 1
Order Date: 2019-03-13
Payment: Paypal
----------------------------
Id: 2
Name: Invoice 2
Order Date: 2019-03-21
Payment: Paypal
----------------------------