英文:
How to embed SQLX results in HTML using GO (golang)
问题
我正在尝试使用GO作为后端，将SQL查询结果嵌入到HTML表格中。在Go中，可以使用Rows.Next()函数来迭代行结果。这对于打印到控制台窗口是有效的，但对于HTML表格则不行。
以下是我的Go代码：
package main
// 导入所需的包
import (
"net/http"
"html/template"
"fmt"
"github.com/LukeMauldin/lodbc"
"github.com/jmoiron/sqlx"
"database/sql"
)
// 声明数据库类
var db *sqlx.DB
type webdata struct {
Title string
Heading string
GridTitle string
ColumnHeading [9]string
RowData [9]string
NumOfRows *sql.Rows
}
// 处理'/mbconsole'的函数处理器
func ConsoleHandler(w http.ResponseWriter, r *http.Request) {
// 声明webdata的实例
var wdata webdata
// 连接数据库
// 设置ODBC驱动级别
lodbc.SetODBCVersion(lodbc.ODBCVersion_3)
var err error
// 连接到Microsoft SQL Server
db, err = sqlx.Open("lodbc", "[connectionstring]")
if err == nil {
fmt.Println("Connection successful")
} else {
fmt.Println("SQL Connection error", err)
}
// 执行查询
rows, err := db.Query("[Select ...]")
if err != nil {
panic(err.Error())
}
// 获取列名
columns, err := rows.Columns()
if err != nil {
panic(err.Error())
}
// 为值创建一个切片
values := make([]interface{}, len(columns))
// rows.Scan需要'[]interface{}'作为参数，因此我们必须将引用复制到这样的切片中
// 详细信息请参见http://code.google.com/p/go-wiki/wiki/InterfaceSlice
scanArgs := make([]interface{}, len(values))
for i := range values {
scanArgs[i] = &values[i]
}
// 填充表头，表返回9列，因此我只是硬编码了它
for i := 0; i < 9; i++ {
wdata.ColumnHeading[i] = columns[i]
}
wdata.NumOfRows = rows
// 获取行数据
for rows.Next() {
err = rows.Scan(scanArgs...)
if err != nil {
panic(err.Error())
}
// 打印数据
for i, value := range values {
switch value.(type) {
case nil:
wdata.RowData[i] = "NULL"
case []byte:
wdata.RowData[i] = string(value.([]byte))
default:
wdata.RowData[i] = fmt.Sprint(value)
}
}
}
wdata.Title = "Page Title"
wdata.Heading = "My View"
wdata.GridTitle = "My Grid Title"
// 获取要加载数据的模板
t1, err := template.ParseFiles("template.html")
if t1 == nil {
fmt.Println("File Not Found: ", err)
}
// 使用数据加载模板并显示
terr := t1.Execute(w, &wdata)
if terr != nil {
fmt.Println("terr: ", terr)
}
db = db.Unsafe()
defer db.Close()
}
func main() {
http.HandleFunc("/", ConsoleHandler)
}
这是我的template.html：
<html>
<head><title>{{.Title}}</title></head><body>
...
<h1>{{.Heading}}</h1>
<div id="gridviewcontainer">
<br />
<div id="gridtitle">
<a href="{{.GridTitleLink}}" style="font-size:25px;">{{.GridTitle}}</a>
</div>
<table id="gridtable">
<tr>{{range $ColumnIdx, $colheading := .ColumnHeading}}
<th>{{$colheading}}</th>{{end}}</tr>
<!-- 这是导致问题的地方，.NumOfRows不是有效的字段，必须是数组、通道、管道或映射 -->
{{range $index, $rowval := .NumOfRows}}
<tr>
{{range $rowidx, $rowdat := .RowData}}<td>{{$rowdat}}</td>{{end}}
</tr>
{{end}}
</table>
...
</body>
</html>
我正确连接到数据库，并且使用"fmt"包可以正确打印。但是我无法弄清楚如何在HTML页面中循环遍历返回的行数。是否有一种方法可以将sql.Rows转换为正确的类型或在HTML中循环一定次数。
附注：
我尝试在HTML中使用{{ $index := 3}}...{end}}，但那没有起作用。
任何输入将不胜感激。
英文:
I am trying to embed results from a Sql Query into an html table using GO as the back end. To iterate row results in Go, the Rows.Next() function is used. This works for printing to the console window, but not for a html table.
Here is my Go Code:
package main
// Database connection Code for http://play.golang.org/p/njPBsg0JjD
import (
"net/http"
"html/template"
"fmt"
"github.com/LukeMauldin/lodbc"
"github.com/jmoiron/sqlx"
"database/sql"
)
//declare database class
var db *sqlx.DB
type webdata struct {
Title string
Heading string
GridTitle string
ColumnHeading [9]string
RowData [9]string
NumOfRows *sql.Rows
}
//this is the function handler to handle '/mbconsole'
func ConsoleHandler(w http.ResponseWriter, r *http.Request) {
//declare an instance of webdata
var wdata webdata
//connect to database
//Set ODBC driver level
lodbc.SetODBCVersion(lodbc.ODBCVersion_3)
var err error
//connect to a Microsoft SQL Server
db, err = sqlx.Open("lodbc", "[connectionstring]")
if err == nil {
fmt.Println("Connection successful")
}else{
fmt.Println("SQL Connection error", err)
}
// Execute the queries
rows, err := db.Query("[Select ...]")
if err != nil {
panic(err.Error())
}
// Get column names
columns, err := rows.Columns()
if err != nil {
panic(err.Error())
}
// Make a slice for the values
values := make([]interface{}, len(columns))
// rows.Scan wants '[]interface{}' as an argument, so we must copy the
// references into such a slice
// See http://code.google.com/p/go-wiki/wiki/InterfaceSlice for details
scanArgs := make([]interface{}, len(values))
for i := range values {
scanArgs[i] = &values[i]
}
//fill table headings, the table returns 9 columns so I just hard coded it
for i:=0;i<9;i++ {
wdata.ColumnHeading[i] = columns[i]
}
wdata.NumOfRows = rows
// Fetch rows
for rows.Next() {
err = rows.Scan(scanArgs...)
if err != nil {
panic(err.Error())
}
// Print data
for i, value := range values {
switch value.(type) {
case nil:
wdata.RowData[i] = "NULL"
case []byte:
wdata.RowData[i] = string(value.([]byte))
default:
wdata.RowData[i] = fmt.Sprint(value)
}
}
}
wdata.Title = "Page Title"
wdata.Heading = "My View"
wdata.GridTitle = "My Grid Title"
//get the template the data will be loaded into
t1, err := template.ParseFiles("template.html")
if t1 == nil {
fmt.Println("File Not Found: ", err)
}
//load the template with data and display
terr := t1.Execute(w, &wdata)
if terr != nil {
fmt.Println("terr: ", terr)
}
db = db.Unsafe()
defer db.Close()
}
func main() {
http.HandleFunc("/",ConsoleHandler)
}
Here is my template.html
<html>
<head><title>{{.Title}}</title></head><body>
...
<h1>{{.Heading}}</h1>
<div id="gridviewcontainer">
<br />
<div id="gridtitle">
<a href="{{.GridTitleLink}}" style="font-size:25px;">{{.GridTitle}}</a>
</div>
<table id="gridtable">
<tr>{{range $ColumnIdx, $colheading := .ColumnHeading}}
<th>{{$colheading}}</th>{{end}}</tr>
<<!---This is what is causing the issue, .NumOfRows is not a valid field, must be array, channel, pipeline, or map -->
{{range $index, $rowval := .NumOfRows}}
<tr>
{{range $rowidx, $rowdat := .RowData}}<td>{{$rowdat}}</td>{{end}}
</tr>
{{endfor}}
</table>
...
</body>
</html>
I connect to the database correctly and using the "fmt" package I can print correctly. But I can't figure out how to loop through for number of rows retured in the html page. Is there a way to cast sql.Rows to a correct type or loop for a set integer number of times in html.
ps.
I tried using {{ $index := 3}}...{end}} in the html, but that didn't work
Any input would be greatly appreciated
答案1
得分: 0
在新行的开始处，插入一个"NewRow"字符串作为HTML中的标志。然后在{{range $rowidx, $rowdat := .RowData}}循环中添加一个if语句，如果$rowdat == "NewRow"，则结束当前行并开始新的一行。
英文:
At the start of a new row, insert a "NewRow" string to serve as a flag in the html. Then in the {{range $rowidx, $rowdat := .RowData}} loop add an if statement that ends and starts a new row if $rowdat == "NewRow"
答案2
得分: 0
我使用了以下变体：
Go
func MainPageHandler(w http.ResponseWriter, r *http.Request) {
type User struct {
Name1 string
Name2 string
}
rows, err := database.Query("select .......;")
if err != nil {
log.Println(err)
}
defer rows.Close()
user_current := []User{}
for rows.Next() {
p := User{}
err := rows.Scan(&p.Name1, &p.Name2 )
if err != nil {
fmt.Println(err)
continue
}
user_current = append(user_current, p)
}
tmpl, _ := template.ParseFiles("main_page.html")
tmpl.Execute(w, user_current)
}
html
<table>
<thead><th>name1/th><th>name2</th></thead>
{{range . }}
<tr>
<td>{{.Name1}}</td>
<td>{{.Name2}}</td>
</tr>
{{end}}
</table>
英文:
I use this variant:
Go
func MainPageHandler(w http.ResponseWriter, r *http.Request) {
type User struct {
Name1 string
Name2 string
}
rows, err := database.Query("select .......;")
if err != nil {
log.Println(err)
}
defer rows.Close()
user_current := []User{}
for rows.Next() {
p := User{}
err := rows.Scan(&p.Name1, &p.Name2 )
if err != nil {
fmt.Println(err)
continue
}
user_current = append(user_current, p)
}
tmpl, _ := template.ParseFiles("main_page.html")
tmpl.Execute(w, user_current)
}
html
<table>
<thead><th>name1/th><th>name2</th></thead>
{{range . }}
<tr>
<td>{{.Name1}}</td>
<td>{{.Name2}}</td>
</tr>
{{end}}
</table>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论