模板方法模式的房间改造-组合查询
室计费系统。更纠结的特点是组合查询。但代码做更多，且多为重复的代码，正是由于这个，它比较适合的模板方法模式。
一、基本介绍
模版方式模式是定义一个操作中的算法的骨架，而将步骤延迟到子类中。
模板方法使得子类能够不改变一个算法的结构就可以重定义算法的某些特定步骤。
类图
二、详细实现
1、建立模板父窗口
加入Windows窗口，设计模板界面（例如以下图），并在模板窗口里写入抽象出来的类和方法的代码。
'*************************************************
'作者：崔晓光
'小组：
'说明：组合查询模板
'创建日期：2014.9.9
'版本：
'**********************************************/
Imports Entity
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Imports System.Data
Imports System.IO
'组合查询父窗口
Public Class FrmComQueryParent
'实例化一个组合查询的实体
Protected comQueryEntity As New ComQueryEntity
’载入
Protected Sub FrmComQueryParent_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'将參数传递给实体，赋初值
'字段名
comQueryEntity.CmbName1 = ""
comQueryEntity.CmbName2 = ""
comQueryEntity.CmbName3 = ""
'操作符
cmbMark1.Items.Add(">")
cmbMark1.Items.Add("<")
cmbMark1.Items.Add("=")
cmbMark1.Items.Add("<>")
cmbMark2.Items.Add(">")
cmbMark2.Items.Add("<")
cmbMark2.Items.Add("=")
cmbMark2.Items.Add("<>")
cmbMark3.Items.Add(">")
cmbMark3.Items.Add("<")
cmbMark3.Items.Add("=")
cmbMark3.Items.Add("<>")
'关系
cmbRelation1.Items.Add("与")
cmbRelation1.Items.Add("或")
cmbRelation2.Items.Add("与")
cmbRelation2.Items.Add("或")
'窗口载入后，后两组查询默认不能用
cmbName2.Enabled = False
cmbName3.Enabled = False
cmbMark2.Enabled = False
cmbMark3.Enabled = False
cmbRelation2.Enabled = False
txtContent2.Enabled = False
txtContent3.Enabled = False
Dim i As Integer
For i = 0 To dgvRecord.Columns.Count - 1
dgvRecord.Columns(i).Width = DataGridViewAutoSizeColumnMode.AllCells '调整列宽为依据内容自己主动调整
Next
End Sub
'查询
Private Sub btQuery_Click(sender As Object, e As EventArgs) Handles btQuery.Click
Try
'推断组合框不为空
If cmbRelation1.Text = "" Then
If cmbName1.Text = "" Or cmbMark1.Text = "" Or txtContent1.Text = "" Then
MsgBox("第一行查询条件不能为空，请完好查询信息！", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "提示")
Exit Sub
End If
End If
If cmbRelation1.Text <> "" Then
If cmbName1.Text = "" Or cmbMark1.Text = "" Or txtContent1.Text = "" Or cmbName2.Text = "" Or cmbMark2.Text = "" Or txtContent2.Text = "" Then
MsgBox("所输入的查询条件不能为空。请完好查询信息。", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "提示")
Exit Sub
End If
End If
If cmbRelation2.Text <> "" Then
If cmbName1.Text = "" Or cmbMark1.Text = "" Or txtContent1.Text = "" Or cmbName2.Text = "" Or cmbMark2.Text = "" Or txtContent2.Text = "" Or cmbName3.Text = "" Or cmbMark3.Text = "" Or txtContent3.Text = "" Then
MsgBox("所输入的查询条件不能为空，请完好查询信息！", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "提示")
Exit Sub
End If
End If
'将參数传给实体
comQueryEntity.DbName = GetdbName()
comQueryEntity.CmbName1 = ToEnglish(cmbName1.Text)
comQueryEntity.CmbName2 = ToEnglish(cmbName2.Text)
comQueryEntity.CmbName3 = ToEnglish(cmbName3.Text)
comQueryEntity.CmbMark1 = cmbMark1.Text.Trim
comQueryEntity.CmbMark2 = cmbMark2.Text.Trim
comQueryEntity.CmbMark3 = cmbMark3.Text.Trim
'在查询时非数字要加上''
If IsNumeric(txtContent1.Text) Then
comQueryEntity.TxtContent1 = txtContent1.Text.Trim
Else
comQueryEntity.TxtContent1 = "'" & txtContent1.Text.Trim & "'"
End If
If IsNumeric(txtContent2.Text) Then
comQueryEntity.TxtContent2 = txtContent2.Text.Trim
Else
comQueryEntity.TxtContent2 = "'" & txtContent2.Text.Trim & "'"
End If
If IsNumeric(txtContent3.Text) Then
comQueryEntity.TxtContent3 = txtContent3.Text.Trim
Else
comQueryEntity.TxtContent3 = "'" & txtContent3.Text.Trim & "'"
End If
'前者还是后者
comQueryEntity.CmbRelation1 = ToEnglish(cmbRelation1.Text)
comQueryEntity.CmbRelation2 = ToEnglish(cmbRelation2.Text)
Dim dt As New Data.DataTable
Dim facadeGeneral As New Facade.Facade.FacadeGeneral
' 把表显示到datagridview中
Call Todgv(comQueryEntity)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
''' <summary>
''' 模板方法，定义虚函数ToEnglish，查询字段转化为数据库字段
''' </summary>
''' <param name="cmbName"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Overridable Function ToEnglish(cmbName As String) As String
Return ""
End Function
''' <summary>
''' 获得数据库表名
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Protected Overridable Function GetdbName() As String
Return ""
End Function
''' <summary>
''' 把表显示到datagridview中
''' </summary>
''' <remarks></remarks>
Protected Overridable Sub Todgv(ByVal comQueryEntity As ComQueryEntity)
End Sub
''' <summary>
''' 拼接字符串
''' </summary>
''' <param name="frm"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function Query(frm As FrmComQueryParent, ByVal comQueryEntity As ComQueryEntity) As String
Dim cmdText As String = "" & frm.ToEnglish(frm.cmbName1.Text) & frm.cmbMark1.Text & "" & comQueryEntity.TxtContent1 & ""
'非组合查询
If frm.cmbRelation1.Text = "" Then
cmdText = cmdText
'关系2为空，关系1不为空
ElseIf frm.cmbRelation2.Text = "" Then
cmdText = cmdText & frm.ToEnglish(frm.cmbRelation1.Text) & "" & frm.ToEnglish(frm.cmbName2.Text) & frm.cmbMark2.Text & "" & comQueryEntity.TxtContent2 & ""
Else
'关系1，2都不为空
cmdText = cmdText & frm.ToEnglish(frm.cmbRelation1.Text) & "" & _
frm.ToEnglish(frm.cmbName2.Text) & frm.cmbMark2.Text & "'" & comQueryEntity.TxtContent2 & "'" & "" & _
frm.ToEnglish(frm.cmbRelation2.Text) & "" & _
frm.ToEnglish(frm.cmbName3.Text) & frm.cmbMark3.Text & "'" & comQueryEntity.TxtContent3 & "'"
End If
Return cmdText
End Function
''' <summary>
''' 第一个组合关系是否为空
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub cmbRelation1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelation1.SelectedIndexChanged
If cmbRelation1.Text = "" Then
cmbName2.Enabled = False
cmbName3.Enabled = False
cmbMark2.Enabled = False
cmbMark3.Enabled = False
cmbRelation2.Enabled = False
txtContent2.Enabled = False
txtContent3.Enabled = False
Else
cmbName2.Enabled = True
cmbMark2.Enabled = True
cmbRelation2.Enabled = True
txtContent2.Enabled = True
End If
End Sub
''' <summary>
''' 第二个组合关系是否为空
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub cmbRelation2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelation2.SelectedIndexChanged
If cmbRelation2.Text = "" Then
cmbName3.Enabled = False
cmbMark3.Enabled = False
txtContent3.Enabled = False
Else
cmbName3.Enabled = True
cmbMark3.Enabled = True
txtContent3.Enabled = True
End If
End Sub
''' <summary>
''' 关闭该窗口
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub btCancel_Click(sender As Object, e As EventArgs) Handles btCancel.Click
Me.Close()
End Sub
'导出为Excel
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'要先加入引用才干用到 Microsoft.Office.Interop.Excel.Application()
Dim MyExcel As New Microsoft.Office.Interop.Excel.Application()
MyExcel.Application.Workbooks.Add(True)
MyExcel.Visible = True
'去除dgvRecord的编号列（这里也能够不要）
Dim m As Integer
For m = 0 To dgvRecord.ColumnCount - 1
MyExcel.Cells(1, m + 1) = Me.dgvRecord.Columns(m).HeaderText
Next m
'往excel表里加入数据
Dim i As Integer
For i = 0 To Me.dgvRecord.RowCount - 1
Dim j As Integer
For j = 0 To dgvRecord.ColumnCount - 1
If Me.dgvRecord(j, i).Value Is System.DBNull.Value Then
MyExcel.Cells(i + 2, j + 1) = ""
Else
MyExcel.Cells(i + 2, j + 1) = dgvRecord(j, i).Value.ToString
End If
Next j
Next i
End Sub
End Class
2、建立子窗口
例如以下图建立子窗口，选择继承创建的父窗口模板，然后就能够得到一模一样的子窗口了。
通过在子窗口里重写一些方法和类，以实现不同的功能就能够了。
实现上机学生查询的代码例如以下：
'*************************************************
'作者：崔晓光
'小组：
'说明：学生上机组合查询
'创建日期：2014.9.9
'版本：
'**********************************************/
Imports Entity.Entity
Imports Entity
'学生正在上机查询
Public Class FrmComQueryStudentOn
''' <summary>
''' 载入combo的item
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub FrmComQueryStudentOn_Load(sender As Object, e As EventArgs) Handles MyBase.Load
cmbName1.Items.Add("卡号")
cmbName1.Items.Add("上机日期")
cmbName1.Items.Add("上机时间")
cmbName1.Items.Add("机器名")
cmbName2.Items.Add("卡号")
cmbName2.Items.Add("上机日期")
cmbName2.Items.Add("上机时间")
cmbName2.Items.Add("机器名")
cmbName3.Items.Add("卡号")
cmbName3.Items.Add("上机日期")
cmbName3.Items.Add("上机时间")
cmbName3.Items.Add("机器名")
End Sub
''' <summary>
''' 把载入的汉字转换成数据库的字段
''' </summary>
''' <param name="cmbName"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Overrides Function ToEnglish(cmbName As String) As String
Select Case cmbName
Case "卡号"
ToEnglish = "cardId"
Case "上机日期"
ToEnglish = "onDate"
Case "上机时间"
ToEnglish = "onTime"
Case "机器名"
ToEnglish = "local"
Case "与"
ToEnglish = " and "
Case "或"
ToEnglish = " or "
Case Else
ToEnglish = ""
End Select
End Function
''' <summary>
''' 传数据库表名
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Protected Overrides Function GetdbName() As String
Return "LineRecord_Info"
End Function
''' <summary>
''' 查询并把数据显示到datagridview中
''' </summary>
''' <remarks></remarks>
Protected Overrides Sub Todgv(ByVal comQueryEntity As ComQueryEntity)
Dim returnList As New List(Of LineRecordEntity) '实例化集合，用来返回实体类
Dim frmComQueryParent As New FrmComQueryParent '实例化父窗口
Dim facadeComQuery As New Facade.FacadeComQuery '实例化外观
Try
comQueryEntity.SqlString = frmComQueryParent.Query(Me, comQueryEntity) '获得拼接字符串
returnList = facadeComQuery.QueryStudentOn(comQueryEntity) '调用外观进行查询
'取出返回的实体
Dim lineRecordEntity As LineRecordEntity
Dim dataTable As New Data.DataTable
dataTable.Columns.Add("卡号") '自己主动创建列
dataTable.Columns.Add("上机日期")
dataTable.Columns.Add("上机时间")
dataTable.Columns.Add("机器名")
Dim dataNewRow As DataRow '声明一个新行
For i = 0 To returnList.Count - 1
lineRecordEntity = returnList.Item(i)
dataNewRow = dataTable.NewRow()
'显示数据
dataNewRow.Item(0) = lineRecordEntity.CardId
dataNewRow.Item(1) = lineRecordEntity.OnDate
dataNewRow.Item(2) = lineRecordEntity.OnTime
dataNewRow.Item(3) = lineRecordEntity.Local
dataTable.Rows.Add(dataNewRow) '将新行插入到表中
Next
'绑定数据源
dgvRecord.AutoGenerateColumns = True '自己主动创建列
dgvRecord.AllowUserToAddRows = False
Me.dgvRecord.DataSource = dataTable '显示信息
Me.dgvRecord.Refresh()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
3.DAL层。详细的查询
我们尽管传的是实体。但实际上是一个字符串。所以在D层，仅仅要将字符串拼接起来即可
''' <summary>
''' 从表中查询学生上机信息,组合查询
''' </summary>
''' <param name="comQueryEntity">上机记录实体</param>
Public Function QueryOn(ByVal comQueryEntity As Entity.ComQueryEntity) As List(Of Entity.Entity.LineRecordEntity) Implements ILineRecord.QueryOn
Try
strSql = "select * from (select * from LineRecord_Info where offStatus='正在上机')as LineRecord_Info where " & comQueryEntity.SqlString.Trim
dataTable = sqlHelper.ExecSelectNo(CommandType.Text, strSql)
Dim returnList As New List(Of LineRecordEntity)
Dim lineRecordEntity As New LineRecordEntity
'封装查到的实体
For i = 0 To dataTable.Rows.Count - 1
lineRecordEntity.CardId = dataTable.Rows(i).Item(0).ToString
lineRecordEntity.OnDate = dataTable.Rows(i).Item(1).ToString
lineRecordEntity.OnTime = dataTable.Rows(i).Item(2).ToString
lineRecordEntity.Local = dataTable.Rows(i).Item(8).ToString
returnList.Add(lineRecordEntity)
Next
Return returnList
Catch ex As Exception
Throw
End Try
End Function
三、总结
自此，模版方法模式已经做完。
机房收费系统 之 组合查询BUG。
模版方法的核心就是将总体架构抽象到父类中，详细的时间情况由子类拓展。在我们学习生活中也是，学者去抽象，去总结。因此，提升能力水平。
版权声明：本文博客原创文章，博客，未经同意，不得转载。
