Gin go learning note 3: JS paging of gin Web Framework

JS paging

1> JS paging, business logic

(1) Paging uses a jQuery plug-in called jquery.pagination.js

(2) The support of jquery is required. jquery-2.1.1.min.js is used in this project

(3) The parameters required for paging are: total number of records, number of displays per page, and page number

(4) Add search criteria to use as a query

2> Write a new model named person.go. The code is as follows:

   

package models

import (
	"log"
	"fmt"
 db "GinLearn/GinLearn/database"
)
//Table structure
type Person struct {
   Id        int    `json:"id" form:"id"`
   FirstName string `json:"first_name" form:"first_name"`
   LastName  string `json:"last_name" form:"last_name"`
}

//adding record
func (p *Person) AddPerson() bool {
   rs, err := db.SqlDB.Exec("INSERT INTO person(first_name, last_name) VALUES (?, ?)", p.FirstName, p.LastName)
   if err != nil {
     return false
   }
   id, err := rs.LastInsertId()
   fmt.Println(id)
   if err!=nil{
     return false
   }else{
     return true
   }
}

//Modification record
func (p *Person) EditPerson() bool {
  rs, err := db.SqlDB.Exec("UPDATE person set first_name=?,last_name=? where id=?", p.FirstName, p.LastName,p.Id)
  if err != nil {
    return false
  }
  id, err := rs.RowsAffected()
  fmt.Println(id)
  if err!=nil{
    return false
  }else{
    return true
  }
}

//Delete record
func DeletePerson(Id int) bool {
  rs, err := db.SqlDB.Exec("Delete From person where id=?", Id)
  if err != nil {
    return false
  }
  id, err := rs.RowsAffected()
  fmt.Println(id)
  if err!=nil{
    return false
  }else{
    return true
  }
}

//Get a list of records
func GetPersonList(pageno,pagesize int,search string) (persons []Person) {

    fmt.Println("Search parameters:"+search)
    persons = make([]Person, 0)
    //SQL query paging statement
    if search!=""{
      rows, err := db.SqlDB.Query("SELECT id, first_name, last_name FROM person where 1=1 and last_name like '%"+search+"%' or first_name like '%"+search+"%' limit ?,?",(pageno-1)*pagesize,pagesize)
      if err != nil {
        return nil
       }
       defer rows.Close()
    
       //Data adding to dataset
       for rows.Next() {
         var person Person
         rows.Scan(&person.Id, &person.FirstName, &person.LastName)
         persons = append(persons, person)
       }
       if err = rows.Err(); err != nil {
         return nil
       }
     
    }else{
      rows, err := db.SqlDB.Query("SELECT id, first_name, last_name FROM person where 1=1  limit ?,?",(pageno-1)*pagesize,pagesize)
      if err != nil {
        return nil
      }
      defer rows.Close()

    
     //Data adding to dataset
     for rows.Next() {
       var person Person
       rows.Scan(&person.Id, &person.FirstName, &person.LastName)
       persons = append(persons, person)
      }
      if err = rows.Err(); err != nil {
       return nil
     }
  }
  return persons
}
//Number of records obtained
func GetRecordNum(search string) int {
   num:=0;

  //SQL query paging statement
  if search!=""{
    rows, err := db.SqlDB.Query("SELECT id, first_name, last_name FROM person where 1=1 and first_name like '%?%' or last_name '%?%'",search,search)
    if err != nil {
      return 0
     }
     defer rows.Close()
  
     //Data adding to dataset
     for rows.Next() {
       num++;
     }
   
  }else{
    rows, err := db.SqlDB.Query("SELECT id, first_name, last_name FROM person where 1=1")
    if err != nil {
      return 0
    }
    defer rows.Close()

  
   //Data adding to dataset
  //Data adding to dataset
  for rows.Next() {
    num++;
  }
  
}
return num
}
//Get user data
func  GetPersonById(Id int) (p *Person) {
 
  var person Person
  //Get object by ID query
  err := db.SqlDB.QueryRow("SELECT id, first_name, last_name FROM person WHERE id=?", Id).Scan(
    &person.Id, &person.FirstName, &person.LastName,
   )
 
   //Printing error
   if err != nil {
    log.Println(err)
   }
   //Return object
   return &person
}

  

3> The code of the controller person.go is as follows:

    

package apis

import (
	"fmt"
	"strconv"
  "net/http"
  "log"
  "github.com/gin-gonic/gin"
 . "GinLearn/GinLearn/models"
)
//Initial page
func IndexApi(c *gin.Context) {
  c.String(http.StatusOK, "Hello World!")
}
//Rendering html pages
func ShowHtmlPage(c *gin.Context) {
  c.HTML(http.StatusOK, "index.html", gin.H{
      "title": "GIN: HTML page",
  })
}


//List page
func ListHtml(c *gin.Context) {  
  c.HTML(http.StatusOK, "list.html", gin.H{
      "title": "GIN: User list page",
  })
}
//List page data
func GetDataList(c *gin.Context) {  
  //Get requested parameters
  search:=c.PostForm("search")
  num:=c.PostForm("pageno")
  pageno,err:= strconv.Atoi(num) 
  if err!=nil{
    log.Fatalln(err)
  }
  //Get data set
  datalist:=GetPersonList(pageno,3,search)

  //Total number of records obtained
  count:=GetRecordNum(search)
 //Return results
 c.JSON(http.StatusOK, gin.H{
  "datalist": datalist,
  "count":count,
  "pagesize":3,
  "pageno":pageno,
 })
}
//List page data
func PageNextData(c *gin.Context) {  
  //Get requested parameters
  search:=c.PostForm("search")
  num:=c.PostForm("pageno")
  pageno,err:= strconv.Atoi(num) 
  if err!=nil{
    log.Fatalln(err)
  }
  //Get data set
  datalist:=GetPersonList(pageno,3,search)

  //Total number of records obtained
  count:=GetRecordNum(search)
 //Return results
 c.JSON(http.StatusOK, gin.H{
  "datalist": datalist,
  "count":count,
  "pagesize":3,
  "pageno":pageno,
 })
}
//New page
func AddHtml(c *gin.Context){
  c.HTML(http.StatusOK, "add.html", gin.H{
    "title": "GIN: New user page",
  })
}
//adding record
func AddPersonApi(c *gin.Context) {
 
   //Get requested parameters
   firstName := c.PostForm("first_name")
   lastName := c.PostForm("last_name")
   fmt.Println("Execute here A")
   //assignment
   p := Person{FirstName: firstName, LastName: lastName}
   //Call the new method in the model
   ra:= p.AddPerson()
   //Return results
   c.JSON(http.StatusOK, gin.H{
    "success": ra,
  })
  c.Redirect(http.StatusOK,"/home/list")
}
//Edit page
func EditHtml(c *gin.Context){
  //Get parameters for URL request
  num:=c.Query("id")

  id,err:= strconv.Atoi(num) 
 
  if err!=nil{
    log.Fatalln(err)
  }

  p:=GetPersonById(id)
   if p==nil{
    fmt.Println("Get data error")
   }else{
    fmt.Println(p)
    fmt.Println("Get the data right")
   }

  c.HTML(http.StatusOK, "edit.html", gin.H{
    "title": "GIN: Edit user page",
    "id":p.Id,
    "firstname":p.FirstName,
    "lastname":p.LastName,
  })
}
//Edit record
func EditPersonApi(c *gin.Context) {
  fmt.Println("Execute here")
  //Get requested parameters
  num:=c.PostForm("id")
  fmt.Println(num)
  id,err:= strconv.Atoi(num) 
  if err!=nil{
    log.Fatalln(err)
  }
  firstName := c.PostForm("first_name")
  lastName := c.PostForm("last_name")
  //assignment
  p := GetPersonById(id)
  p.FirstName=firstName
  p.LastName=lastName
  //Call the edit method in the model
  ra:= p.EditPerson()
  //Return results
  c.JSON(http.StatusOK, gin.H{
    "success": ra,
  })
  c.Redirect(http.StatusOK,"/home/list")
}

//Delete record
func DeletePersonApi(c *gin.Context) {
 
  //Get requested parameters
   num:=c.PostForm("id")
   fmt.Println(num)
   id,err:= strconv.Atoi(num) 
   if err!=nil{
    log.Fatalln(err)
  }
  //Call the deleted method in the model
  ra:= DeletePerson(id)
  if ra == false {
   log.Fatalln("Delete failed")
  }
  //Return results
  c.JSON(http.StatusOK, gin.H{
    "success": ra,
  })
}

  

4> The routing configuration is as follows:

   

package routers

import (
  "github.com/gin-gonic/gin"
  . "GinLearn/GinLearn/apis"
 )
 
func InitRouter() *gin.Engine{
  router := gin.Default()
  //Hello World
  router.GET("/", IndexApi)
  //Rendering html pages
  router.LoadHTMLGlob("views/*")
  router.GET("/home/index", ShowHtmlPage)
  //List page
  router.GET("/home/list", ListHtml)
  router.POST("/home/PageData", GetDataList)
  router.POST("/home/PageNextData", PageNextData)

  //New page
  router.GET("/home/add", AddHtml)
  router.POST("/home/saveadd", AddPersonApi)
  
   //Edit page
   router.GET("/home/edit", EditHtml)
   router.POST("/home/saveedit", EditPersonApi)

    //delete
    router.POST("/home/delete", DeletePersonApi)
  return router
 }
 

  

 

5> Create a new list.html page for pagination display, and add.html and edit.html pages for data addition and editing. The specific code is as follows:

1 - > list.html page code is as follows:

   

<!DOCTYPE html>
 
<html>
      <head>
        <title>home page - User list page</title>
        <script type="text/javascript" src="/static/js/jquery-2.1.1.min.js"></script> 
        <script type="text/javascript" src="/static/js/jquery.paginationNew.js"></script> 
        <style>
          .am-cf{
            height: 50px;          
            margin-top: 30px;
            line-height: 50px;
            text-align: center;
            vertical-align: middle;
            margin-left: 40%;
          }
          .am-fr{
              float: left;
              line-height: 50px;
              text-align: center;
              vertical-align: middle;
              height: 50px;
              margin-top: -15px;
          }
          .am-pagination{
            list-style:none;
            height: 50px;    
            line-height: 50px;
            text-align: center;
            vertical-align: middle;
          }
          .am-pagination li{
            float:left;
            margin-left: 10px;
          }
          .am-pagination li a{
            text-decoration:none;
          }
          .am-jl{
              float: left;
              margin-left: 20px;
          }
          .am-active{
              color: #f00;
          }
        </style>
      </head>
       
    <body>
     
        <div class="row pull-right" style="margin-bottom: 20px;margin-right: 5px;text-align:right;margin-right: 40px;">
      
          <input type="text" placeholder="Please enter a name" id="txt_search"/>
          <button class="" onclick="search()">search</button>

          <button class="" onclick="adddata()">Newly added</button>
        </div>
            
        <table class="table table-striped table-hover table-bordered ">
          <thead>
           <th style="text-align: center">ID</th>
           <th style="text-align: center">Surname</th>
           <th style="text-align: center">Name</th>
           <th style="text-align: center">operation</th>
         </thead>
        
         <tbody id="sortable">
         </tbody>
        </table> <!--Paging section-->
        <div style="margin: 20px 0px 10px 0;">
         <table style="margin: 0 auto;">
             <tr>
                 <td>
                     <div id="pagination" class="pagination"></div>
                 </td>
             </tr>
         </table>
        </div>
          
        <script type="text/javascript">
            //Page initialization
            $(function () {            
              //Paging data
              InitData();
            })
            //Search page
            function search(){
                var search = $("#txt_search").val(); / / name
                InitData();
            }
            //Pagination display 1 using pagination plug-in
        function InitData() {
            var search = $("#txt_search").val(); / / name

            $.ajax({
                async: false,
                type: "post",
                url: "/home/PageData",
                data: {
                    search: search,
                    pageno:0
                },
                success: function (data) {
                    console.log('Home data')
                    console.log(data)      
                    var Count = data.count
                    var PageSize = data.pagesize;
                    var Page =data.pageno;
                    $("#pagination").pagination(Count, {
                        callback: pageselectCallback,
                        num_edge_entries: 1,
                        prev_text: "previous page",
                        prev_show_always: true,
                        next_text: "next page",
                        next_show_always: true,
                        items_per_page: PageSize,
                        current_page: Page,
                        link_to: '#__aurl=!/home/PageData',
                        num_display_entries: 4
                    });
                }
            });
        }

        //Callback function 2 after paging with paging plug-in
        function pageselectCallback(page_id, jq) {
            var search = $("#txt_search").val(); / / name

            $.ajax({
                async: false,
                type: "post",
                url: "/home/PageNextData",
                data: {
                    search: search,
                    pageno: (parseInt(page_id) + parseInt(1)),
                },
                success: function (data) {
                    console.log('Data on next page')
                    console.log(data)
                    console.log(data.datalist)
                    htmlData(data.datalist)
                }
            });
        }
        function htmlData(data){
             var html='';
             for(var i=0;i<data.length;i++){
                html+='<tr class="sort-item"  id="module_'+data[i].id+'" value="'+data[i].id+'">';  
                html+='   <td style="text-align: center;width: 350px;"><span class="label label-default" >'+data[i].id+'</span></td>';  
                html+='   <td style="text-align: center;width: 350px;" ><strong>'+data[i].first_name+'</strong></td>';  
                html+='   <td style="text-align: center;width: 350px;" ><strong>'+data[i].last_name+'</strong></td>';  
                html+='   <td style="text-align: center;width: 350px;"><button onclick="editdata('+data[i].id+')">edit</button><button onclick="deletedata('+data[i].id+')">delete</button></td>';                   
                html+='</tr>';  
             }
              
             $("#sortable").html(html);
        }

        //New data
        function adddata(){
            window.location.href="/home/add";  
        }  

        //Edit data
        function editdata(id){
            window.location.href="/home/edit?id="+id; 
        }

        //Delete data
        function deletedata(id){
            $.ajax({
                async: false,
                type: "post",
                url: "/home/delete",
                data: {
                    id: id
                },
                success: function (data) {
                    if(data){
                        alert("Delete succeeded")
                        //Initialization data
                        InitData();
                    }else{
                        alert("Delete failed")
                    }
                }
            });
        }
        </script>
    </body>
</html>

  

 

2 - > add.html page code is as follows:

 

<!DOCTYPE html>
 
<html>
    <head>
      <title>home page - New user page</title>
      <link rel="shortcut icon" href="/static/img/favicon.png" />  
      <link rel="stylesheet" href="/static/bootstrap/css/bootstrap.css"/>
      <script type="text/javascript" src="/static/js/jquery-2.1.1.min.js"></script> 
      <script type="text/javascript" src="/static/bootstrap/js/bootstrap.min.js"></script>    
    </head>
       
    <body>
      <div class="container">
         <form>
           <div class="form-group">
               <label for="text">Surname:</label>
               <input type="text" class="form-control" id="firstname" placeholder="Surname" />
           </div>
           <div class="form-group">
               <label for="number">Name:</label>
               <input type="text" class="form-control" id="lastname" placeholder="Name" />
            </div>
            <button class="btn btn-default" onclick="save()">Submission</button>
            <button type="reset" class="btn btn-primary">Reset</button>
         </form>
       </div>
    <!--Js Part-->   
    <script type="text/javascript">
       //Preservation
       function save(){
        $.ajax({
            type: "post",
            url: "/home/saveadd",
            data: {
                "first_name":$("#firstname").val(),
                "last_name":$("#lastname").val(),
            },
            success: function (data) {
                console.log(data)
                if(data.success){
                    alert("Added successfully")
                    window.location.href="/home/list";
                }else{
                    alert("Failed to add")
                    return false;
                }
            }
        });
       }
    </script>
    </body>
</html>

  

 

3 - > edit.html page code is as follows:

 

<!DOCTYPE html>
 
<html>
    <head>
      <title>home page - New user page</title>
      <link rel="shortcut icon" href="/static/img/favicon.png" />  
      <link rel="stylesheet" href="/static/bootstrap/css/bootstrap.css"/>
      <script type="text/javascript" src="/static/js/jquery-2.1.1.min.js"></script> 
      <script type="text/javascript" src="/static/bootstrap/js/bootstrap.min.js"></script>    
    </head>
       
    <body>
      <div class="container">
         <form>
           <div class="form-group">
               <label for="text">Surname:</label>
               <input type="text" class="form-control" id="firstname" placeholder="Surname" value="{{.firstname}}"/>
           </div>
           <div class="form-group">
               <label for="number">Name:</label>
               <input type="text" class="form-control" id="lastname" placeholder="Name" value="{{.lastname}}"/>
            </div>
            <input type="hidden" id="idval" value="{{.id}}"/>
            <button class="btn btn-default" onclick="save()">Submission</button>
            <button type="reset" class="btn btn-primary">Reset</button>
         </form>
       </div>
    <!--Js Part-->   
    <script type="text/javascript">
       //Preservation
       function save(){
        $.ajax({
            type: "post",
            url: "/home/saveedit",
            data: {
                "id": $("#idval").val(),
                "first_name":$("#firstname").val(),
                "last_name":$("#lastname").val(),
            },
            success: function (data) {
                console.log(data)
                if(data.success){
                    alert("Saved successfully")
                    window.location.href="/home/list";
                }else{
                    alert("Save failed")
                }
            }
        });
       }
    </script>
    </body>
</html>

  

6> The code of Main.go is as follows:

 

package main

import (
 db "GinLearn/GinLearn/database"
 router "GinLearn/GinLearn/routers"
)

func main() {
   //data base
   defer db.SqlDB.Close()

   //Routing part
   router:=router.InitRouter()

   //Static resources
   router.Static("/static", "./static")

   //Running port
   router.Run(":8000")

}

  

7> The code of mysql.go is as follows:

 

 

package database

import (
 "database/sql"
 _ "github.com/go-sql-driver/mysql"
 "log"
)

var SqlDB *sql.DB

func init() {
 var err error
 SqlDB, err = sql.Open("mysql", "root:123456@tcp(192.168.1.87:3306)/test?parseTime=true")
 if err != nil {
  log.Fatal(err.Error())
 }
 err = SqlDB.Ping()
 if err != nil {
  log.Fatal(err.Error())
 }
}

  

8> The effect is as follows:

 

 

 

 

 

 

9> The next chapter is about bootstrap layout

Tags: Go Javascript JQuery JSON SQL

Posted on Fri, 01 May 2020 21:04:02 -0700 by saurabhdutta