机房重构之模版方法模式-组合查询

前端之家收集整理的这篇文章主要介绍了机房重构之模版方法模式-组合查询前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

机房收费系统中,一个比较让人纠结功能就是组合查询,不仅仅是代码比较多,而且大多都是重复的代码,也正是因为如此,才比较适合模版方法模式。

一、基本介绍

模版方式模式是定义一个操作中的算法的骨架,而将步骤延迟到子类中。

模板方法使得子类可以不改变一个算法的结构即可重定义算法的某些特定步骤。

类图

  

二、具体实现

1、建立模板父窗体

添加Windows窗体,设计模板界面(如下图),并在模板窗体里写入抽象出来的类和方法代码



  1. '*************************************************
  2. '作者:崔晓光
  3. '小组:
  4. '说明:组合查询模板
  5. '创建日期:2014.9.9
  6. '版本号:
  7. '**********************************************/
  8. Imports Entity
  9. Imports Microsoft.Office.Interop.Excel
  10. Imports Microsoft.Office.Interop
  11. Imports System.Data
  12. Imports System.IO
  13. '组合查询父窗体
  14. Public Class FrmComQueryParent
  15. '实例化一个组合查询的实体
  16. Protected comQueryEntity As New ComQueryEntity
  17. ’加载
  18. Protected Sub FrmComQueryParent_Load(sender As Object,e As EventArgs) Handles MyBase.Load
  19.  
  20. '将参数传递给实体,赋初值
  21.  
  22. '字段名
  23. comQueryEntity.CmbName1 = ""
  24. comQueryEntity.CmbName2 = ""
  25. comQueryEntity.CmbName3 = ""
  26.  
  27. '操作符
  28. cmbMark1.Items.Add(">")
  29. cmbMark1.Items.Add("<")
  30. cmbMark1.Items.Add("=")
  31. cmbMark1.Items.Add("<>")
  32.  
  33. cmbMark2.Items.Add(">")
  34. cmbMark2.Items.Add("<")
  35. cmbMark2.Items.Add("=")
  36. cmbMark2.Items.Add("<>")
  37.  
  38. cmbMark3.Items.Add(">")
  39. cmbMark3.Items.Add("<")
  40. cmbMark3.Items.Add("=")
  41. cmbMark3.Items.Add("<>")
  42.  
  43. '关系
  44. cmbRelation1.Items.Add("与")
  45. cmbRelation1.Items.Add("或")
  46.  
  47. cmbRelation2.Items.Add("与")
  48. cmbRelation2.Items.Add("或")
  49.  
  50. '窗体加载后,后两组查询默认不能用
  51. cmbName2.Enabled = False
  52. cmbName3.Enabled = False
  53. cmbMark2.Enabled = False
  54. cmbMark3.Enabled = False
  55.  
  56. cmbRelation2.Enabled = False
  57. txtContent2.Enabled = False
  58. txtContent3.Enabled = False
  59.  
  60. Dim i As Integer
  61. For i = 0 To dgvRecord.Columns.Count - 1
  62. dgvRecord.Columns(i).Width = DataGridViewAutoSizeColumnMode.AllCells '调整列宽为根据内容自动调整
  63. Next
  64. End Sub
  65. '查询
  66. Private Sub btQuery_Click(sender As Object,e As EventArgs) Handles btQuery.Click
  67.  
  68. Try
  69. '判断组合框不为空
  70. If cmbRelation1.Text = "" Then
  71. If cmbName1.Text = "" Or cmbMark1.Text = "" Or txtContent1.Text = "" Then
  72. MsgBox("第一行查询条件不能为空,请完善查询信息!",CType(vbOKOnly + MsgBoxStyle.Exclamation,MsgBoxStyle),"提示")
  73. Exit Sub
  74. End If
  75. End If
  76.  
  77. If cmbRelation1.Text <> "" Then
  78. If cmbName1.Text = "" Or cmbMark1.Text = "" Or txtContent1.Text = "" Or cmbName2.Text = "" Or cmbMark2.Text = "" Or txtContent2.Text = "" Then
  79. MsgBox("所输入的查询条件不能为空,请完善查询信息!","提示")
  80. Exit Sub
  81. End If
  82. End If
  83.  
  84. If cmbRelation2.Text <> "" Then
  85. 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
  86. MsgBox("所输入的查询条件不能为空,请完善查询信息!","提示")
  87. Exit Sub
  88. End If
  89. End If
  90.  
  91. '将参数传给实体
  92. comQueryEntity.DbName = GetdbName()
  93. comQueryEntity.CmbName1 = ToEnglish(cmbName1.Text)
  94. comQueryEntity.CmbName2 = ToEnglish(cmbName2.Text)
  95. comQueryEntity.CmbName3 = ToEnglish(cmbName3.Text)
  96.  
  97. comQueryEntity.CmbMark1 = cmbMark1.Text.Trim
  98. comQueryEntity.CmbMark2 = cmbMark2.Text.Trim
  99. comQueryEntity.CmbMark3 = cmbMark3.Text.Trim
  100.  
  101. '在查询时非数字要加上''
  102. If IsNumeric(txtContent1.Text) Then
  103. comQueryEntity.TxtContent1 = txtContent1.Text.Trim
  104. Else
  105. comQueryEntity.TxtContent1 = "'" & txtContent1.Text.Trim & "'"
  106. End If
  107. If IsNumeric(txtContent2.Text) Then
  108. comQueryEntity.TxtContent2 = txtContent2.Text.Trim
  109. Else
  110. comQueryEntity.TxtContent2 = "'" & txtContent2.Text.Trim & "'"
  111. End If
  112. If IsNumeric(txtContent3.Text) Then
  113. comQueryEntity.TxtContent3 = txtContent3.Text.Trim
  114. Else
  115. comQueryEntity.TxtContent3 = "'" & txtContent3.Text.Trim & "'"
  116. End If
  117.  
  118. '前者还是后者
  119. comQueryEntity.CmbRelation1 = ToEnglish(cmbRelation1.Text)
  120. comQueryEntity.CmbRelation2 = ToEnglish(cmbRelation2.Text)
  121.  
  122.  
  123. Dim dt As New Data.DataTable
  124. Dim facadeGeneral As New Facade.Facade.FacadeGeneral
  125.  
  126. ' 把表显示到datagridview中
  127. Call Todgv(comQueryEntity)
  128.  
  129. Catch ex As Exception
  130. MsgBox(ex.Message)
  131. End Try
  132. End Sub
  133.  
  134. ''' <summary>
  135. ''' 模板方法,定义虚函数ToEnglish,查询字段转化为数据库字段
  136. ''' </summary>
  137. ''' <param name="cmbName"></param>
  138. ''' <returns></returns>
  139. ''' <remarks></remarks>
  140. Public Overridable Function ToEnglish(cmbName As String) As String
  141. Return ""
  142.  
  143. End Function
  144. ''' <summary>
  145. ''' 获得数据库表名
  146. ''' </summary>
  147. ''' <returns></returns>
  148. ''' <remarks></remarks>
  149. Protected Overridable Function GetdbName() As String
  150. Return ""
  151. End Function
  152.  
  153. ''' <summary>
  154. ''' 把表显示datagridview
  155. ''' </summary>
  156. ''' <remarks></remarks>
  157. Protected Overridable Sub Todgv(ByVal comQueryEntity As ComQueryEntity)
  158.  
  159. End Sub
  160.  
  161. ''' <summary>
  162. ''' 拼接字符串
  163. ''' </summary>
  164. ''' <param name="frm"></param>
  165. ''' <returns></returns>
  166. ''' <remarks></remarks>
  167. Public Function Query(frm As FrmComQueryParent,ByVal comQueryEntity As ComQueryEntity) As String
  168.  
  169. Dim cmdText As String = "" & frm.ToEnglish(frm.cmbName1.Text) & frm.cmbMark1.Text & "" & comQueryEntity.TxtContent1 & ""
  170.  
  171. '非组合查询
  172. If frm.cmbRelation1.Text = "" Then
  173. cmdText = cmdText
  174. '关系2为空,关系1不为空
  175. ElseIf frm.cmbRelation2.Text = "" Then
  176. cmdText = cmdText & frm.ToEnglish(frm.cmbRelation1.Text) & "" & frm.ToEnglish(frm.cmbName2.Text) & frm.cmbMark2.Text & "" & comQueryEntity.TxtContent2 & ""
  177. Else
  178. '关系1,2都不为空
  179. cmdText = cmdText & frm.ToEnglish(frm.cmbRelation1.Text) & "" & _
  180. frm.ToEnglish(frm.cmbName2.Text) & frm.cmbMark2.Text & "'" & comQueryEntity.TxtContent2 & "'" & "" & _
  181. frm.ToEnglish(frm.cmbRelation2.Text) & "" & _
  182. frm.ToEnglish(frm.cmbName3.Text) & frm.cmbMark3.Text & "'" & comQueryEntity.TxtContent3 & "'"
  183. End If
  184.  
  185. Return cmdText
  186.  
  187. End Function
  188.  
  189.  
  190. ''' <summary>
  191. ''' 第一个组合关系是否为空
  192. ''' </summary>
  193. ''' <param name="sender"></param>
  194. ''' <param name="e"></param>
  195. ''' <remarks></remarks>
  196.  
  197. Private Sub cmbRelation1_SelectedIndexChanged(sender As Object,e As EventArgs) Handles cmbRelation1.SelectedIndexChanged
  198.  
  199. If cmbRelation1.Text = "" Then
  200. cmbName2.Enabled = False
  201. cmbName3.Enabled = False
  202. cmbMark2.Enabled = False
  203. cmbMark3.Enabled = False
  204.  
  205. cmbRelation2.Enabled = False
  206. txtContent2.Enabled = False
  207. txtContent3.Enabled = False
  208. Else
  209. cmbName2.Enabled = True
  210. cmbMark2.Enabled = True
  211. cmbRelation2.Enabled = True
  212. txtContent2.Enabled = True
  213. End If
  214. End Sub
  215.  
  216. ''' <summary>
  217. ''' 第二个组合关系是否为空
  218. ''' </summary>
  219. ''' <param name="sender"></param>
  220. ''' <param name="e"></param>
  221. ''' <remarks></remarks>
  222. Private Sub cmbRelation2_SelectedIndexChanged(sender As Object,e As EventArgs) Handles cmbRelation2.SelectedIndexChanged
  223.  
  224. If cmbRelation2.Text = "" Then
  225. cmbName3.Enabled = False
  226. cmbMark3.Enabled = False
  227. txtContent3.Enabled = False
  228. Else
  229. cmbName3.Enabled = True
  230. cmbMark3.Enabled = True
  231. txtContent3.Enabled = True
  232. End If
  233.  
  234. End Sub
  235. ''' <summary>
  236. ''' 关闭该窗体
  237. ''' </summary>
  238. ''' <param name="sender"></param>
  239. ''' <param name="e"></param>
  240. ''' <remarks></remarks>
  241. Private Sub btCancel_Click(sender As Object,e As EventArgs) Handles btCancel.Click
  242. Me.Close()
  243. End Sub
  244.  
  245.  
  246. '导出为Excel
  247. Private Sub Button1_Click(sender As Object,e As EventArgs) Handles Button1.Click
  248.  
  249. '要先添加引用才能用到 Microsoft.Office.Interop.Excel.Application()
  250. Dim MyExcel As New Microsoft.Office.Interop.Excel.Application()
  251. MyExcel.Application.Workbooks.Add(True)
  252. MyExcel.Visible = True
  253.  
  254. '去除dgvRecord的编号列(这里也可以不要)
  255. Dim m As Integer
  256. For m = 0 To dgvRecord.ColumnCount - 1
  257. MyExcel.Cells(1,m + 1) = Me.dgvRecord.Columns(m).HeaderText
  258. Next m
  259.  
  260. '往excel表里添加数据
  261. Dim i As Integer
  262. For i = 0 To Me.dgvRecord.RowCount - 1
  263. Dim j As Integer
  264. For j = 0 To dgvRecord.ColumnCount - 1
  265. If Me.dgvRecord(j,i).Value Is System.DBNull.Value Then
  266.  
  267. MyExcel.Cells(i + 2,j + 1) = ""
  268. Else
  269. MyExcel.Cells(i + 2,j + 1) = dgvRecord(j,i).Value.ToString
  270.  
  271. End If
  272. Next j
  273. Next i
  274. End Sub
  275.  
  276. End Class

2、建立子窗体

如下图建立子窗体,选择继承创建的父窗体模板,然后就可以得到一模一样的子窗体了。通过在子窗体里重写一些方法和类,以实现不同的功能就可以了。

实现上机学生查询代码如下:

  1. '*************************************************
  2. '作者:崔晓光
  3. '小组:
  4. '说明:学生上机组合查询
  5. '创建日期:2014.9.9
  6. '版本号:
  7. '**********************************************/
  8.  
  9. Imports Entity.Entity
  10. Imports Entity
  11.  
  12. '学生正在上机查询
  13. Public Class FrmComQueryStudentOn
  14.  
  15. ''' <summary>
  16. ''' 加载comboitem
  17. ''' </summary>
  18. ''' <param name="sender"></param>
  19. ''' <param name="e"></param>
  20. ''' <remarks></remarks>
  21. Private Sub FrmComQueryStudentOn_Load(sender As Object,e As EventArgs) Handles MyBase.Load
  22.  
  23. cmbName1.Items.Add("卡号")
  24. cmbName1.Items.Add("上机日期")
  25. cmbName1.Items.Add("上机时间")
  26. cmbName1.Items.Add("机器名")
  27.  
  28. cmbName2.Items.Add("卡号")
  29. cmbName2.Items.Add("上机日期")
  30. cmbName2.Items.Add("上机时间")
  31. cmbName2.Items.Add("机器名")
  32.  
  33. cmbName3.Items.Add("卡号")
  34. cmbName3.Items.Add("上机日期")
  35. cmbName3.Items.Add("上机时间")
  36. cmbName3.Items.Add("机器名")
  37.  
  38. End Sub
  39.  
  40. ''' <summary>
  41. ''' 把加载的汉字转换成数据库的字段
  42. ''' </summary>
  43. ''' <param name="cmbName"></param>
  44. ''' <returns></returns>
  45. ''' <remarks></remarks>
  46. Public Overrides Function ToEnglish(cmbName As String) As String
  47.  
  48. Select Case cmbName
  49. Case "卡号"
  50. ToEnglish = "cardId"
  51. Case "上机日期"
  52. ToEnglish = "onDate"
  53. Case "上机时间"
  54. ToEnglish = "onTime"
  55. Case "机器名"
  56. ToEnglish = "local"
  57. Case "与"
  58. ToEnglish = " and "
  59. Case "或"
  60. ToEnglish = " or "
  61. Case Else
  62. ToEnglish = ""
  63. End Select
  64.  
  65. End Function
  66.  
  67. ''' <summary>
  68. ''' 数据库表名
  69. ''' </summary>
  70. ''' <returns></returns>
  71. ''' <remarks></remarks>
  72. Protected Overrides Function GetdbName() As String
  73. Return "LineRecord_Info"
  74. End Function
  75.  
  76. ''' <summary>
  77. ''' 查询并把数据显示到datagridview中
  78. ''' </summary>
  79. ''' <remarks></remarks>
  80. Protected Overrides Sub Todgv(ByVal comQueryEntity As ComQueryEntity)
  81.  
  82. Dim returnList As New List(Of LineRecordEntity) '实例化集合,用来返回实体类
  83. Dim frmComQueryParent As New FrmComQueryParent '实例化父窗体
  84. Dim facadeComQuery As New Facade.FacadeComQuery '实例化外观
  85.  
  86. Try
  87. comQueryEntity.sqlString = frmComQueryParent.Query(Me,comQueryEntity) '获得拼接字符串
  88. returnList = facadeComQuery.QueryStudentOn(comQueryEntity) '调用外观进行查询
  89. '取出返回的实体
  90. Dim lineRecordEntity As LineRecordEntity
  91. Dim dataTable As New Data.DataTable
  92.  
  93. dataTable.Columns.Add("卡号") '自动创建列
  94. dataTable.Columns.Add("上机日期")
  95. dataTable.Columns.Add("上机时间")
  96. dataTable.Columns.Add("机器名")
  97. Dim datanewRow As DataRow '声明一个新行
  98. For i = 0 To returnList.Count - 1
  99. lineRecordEntity = returnList.Item(i)
  100. datanewRow = dataTable.NewRow()
  101. '显示数据
  102. datanewRow.Item(0) = lineRecordEntity.CardId
  103. datanewRow.Item(1) = lineRecordEntity.OnDate
  104. datanewRow.Item(2) = lineRecordEntity.OnTime
  105. datanewRow.Item(3) = lineRecordEntity.Local
  106.  
  107. dataTable.Rows.Add(datanewRow) '将新行插入到表中
  108. Next
  109.  
  110. '绑定数据源
  111. dgvRecord.AutoGenerateColumns = True '自动创建列
  112. dgvRecord.AllowUserToAddRows = False
  113. Me.dgvRecord.DataSource = dataTable '显示信息
  114. Me.dgvRecord.Refresh()
  115.  
  116. Catch ex As Exception
  117. MsgBox(ex.Message)
  118. End Try
  119. End Sub
  120. End Class

3.DAL层,具体的查询

我们虽然传的是实体,但实际上是一个字符串,所以在D层,只要将字符串拼接起来就行

  1. ''' <summary>
  2. ''' 从表中查询学生上机信息,组合查询
  3. ''' </summary>
  4. ''' <param name="comQueryEntity">上机记录实体</param>
  5. Public Function QueryOn(ByVal comQueryEntity As Entity.ComQueryEntity) As List(Of Entity.Entity.LineRecordEntity) Implements ILineRecord.QueryOn
  6.  
  7. Try
  8. strsql = "select * from (select * from LineRecord_Info where offStatus='正在上机')as LineRecord_Info where " & comQueryEntity.sqlString.Trim
  9.  
  10. dataTable = sqlHelper.ExecSelectNo(CommandType.Text,strsql)
  11.  
  12. Dim returnList As New List(Of LineRecordEntity)
  13. Dim lineRecordEntity As New LineRecordEntity
  14.  
  15. '封装查到的实体
  16. For i = 0 To dataTable.Rows.Count - 1
  17. lineRecordEntity.CardId = dataTable.Rows(i).Item(0).ToString
  18. lineRecordEntity.OnDate = dataTable.Rows(i).Item(1).ToString
  19. lineRecordEntity.OnTime = dataTable.Rows(i).Item(2).ToString
  20. lineRecordEntity.Local = dataTable.Rows(i).Item(8).ToString
  21.  
  22. returnList.Add(lineRecordEntity)
  23. Next
  24. Return returnList
  25. Catch ex As Exception
  26. Throw
  27. End Try
  28. End Function

三、总结

自此,模版方法模式已经做完。这里注意的是组合查询查询语句的方式,详见

机房收费系统 之 组合查询BUG

模版方法的核心就是将整体架构抽象到父类中,具体的时间情况由子类拓展。在我们学习生活中也是,学者去抽象,去总结,这样才能提升层次。

猜你在找的VB相关文章