首页 > 代码库 > 机房重构之模版方法模式-组合查询
机房重构之模版方法模式-组合查询
机房收费系统中，一个比较让人纠结功能就是组合查询，不仅仅是代码比较多，而且大多都是重复的代码，也正是因为如此，才比较适合模版方法模式。
一、基本介绍
模版方式模式是定义一个操作中的算法的骨架，而将步骤延迟到子类中。
模板方法使得子类可以不改变一个算法的结构即可重定义算法的某些特定步骤。
类图
二、具体实现
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 Class2、建立子窗体
如下图建立子窗体，选择继承创建的父窗体模板，然后就可以得到一模一样的子窗体了。通过在子窗体里重写一些方法和类，以实现不同的功能就可以了。
实现上机学生查询的代码如下：
'*************************************************
'作者：崔晓光
'小组：
'说明：学生上机组合查询
'创建日期：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。
模版方法的核心就是将整体架构抽象到父类中，具体的时间情况由子类拓展。在我们学习生活中也是，学者去抽象，去总结，这样才能提升层次。
机房重构之模版方法模式-组合查询
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。