重构之路 组合查询之传参+存储过程
来源:互联网 发布:二分搜索算法,c语言 编辑:程序博客网 时间:2024/05/16 01:14
上篇博文给大家一起讨论了实现组合查询的一种方法,即在U层将select语句的where子句部分组装好,赋给一个字符串变量,传到D层然后与select子句组成完整的sql语句,之后执行,返回查询结果,就是这么简单,但是博文的结尾也留下了一个疑问,这种方法的安全性有点欠佳,有没有相对好一点的办法呢?
答案是肯定的,这次我们一起来看看我实现的另一种方法。首先给大家简单介绍一下这种方法的思路,其实也比较简单,最初我是想在程序代码里写sql查询语句的,然后将组合查询的各个条件的值当做实体参数(现在实体层定义一个用于组合查询的实体)传进sql语句中,最后再执行sql语句,返回结果。但是后来怎么想也想不出如何组装sql语句,所以就询问别人有没有使用传递参数的组合查询办法,结果人家用的是存储过程,我一想:why not?
下面我们就以查询系统用户工作日志的组合查询功能来看看这种办法的具体实现代码:
首先我们要在实体层Entity定义一个用于组合查询的实体类:
Public Class QueryWorklog Private _field1 As String Private _field2 As String Private _field3 As String Private _operatorchar1 As String Private _operatorchar2 As String Private _operatorchar3 As String Private _content1 As String Private _content2 As String Private _content3 As String Private _relation1 As String Private _relation2 As String Public Property Field1 As String Get Return _field1 End Get Set(value As String) _field1 = value End Set End Property Public Property Field2 As String Get Return _field2 End Get Set(value As String) _field2 = value End Set End Property Public Property Field3 As String Get Return _field3 End Get Set(value As String) _field3 = value End Set End Property Public Property Operatorchar1 As String Get Return _operatorchar1 End Get Set(value As String) _operatorchar1 = value End Set End Property Public Property Operatorchar2 As String Get Return _operatorchar2 End Get Set(value As String) _operatorchar2 = value End Set End Property Public Property Operatorchar3 As String Get Return _operatorchar3 End Get Set(value As String) _operatorchar3 = value End Set End Property Public Property Content1 As String Get Return _content1 End Get Set(value As String) _content1 = value End Set End Property Public Property Content2 As String Get Return _content2 End Get Set(value As String) _content2 = value End Set End Property Public Property Content3 As String Get Return _content3 End Get Set(value As String) _content3 = value End Set End Property Public Property Relation1 As String Get Return _relation1 End Get Set(value As String) _relation1 = value End Set End Property Public Property Relation2 As String Get Return _relation2 End Get Set(value As String) _relation2 = value End Set End PropertyEnd Class
然后在SQL Server中编写实现组合查询的存储过程:
USE [ChargeSystemDB]GO/****** Object: StoredProcedure [dbo].[PROC_QueryWorklog] Script Date: 08/17/2014 22:39:39 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<连江伟>-- Create date: <2014年8月17号>-- Description:<用于系统用户工作日志的组合查询>-- =============================================ALTER PROCEDURE [dbo].[PROC_QueryWorklog] -- Add the parameters for the stored procedure here@field1 char(20),@field2 char(20),@field3 char(20),@operator1 char(20),@operator2 char(20),@operator3 char(20),@content1 char(20),@content2 char(20),@content3 char(20),@relation1 char(20),@relation2 char(20)ASdeclare @tempSql varchar(500)BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON; -- Insert statements for procedure hereif (@relation1 ='')set @relation1 =nullif (@relation2 ='')set @relation2 =null--char(32)是空格,char(39)是单引号set @tempSql ='select * from T_Worklog where '+@field1 +@operator1 + char(39) + @content1 +char(39)if (@relation1 is not null )set @tempSql =@tempSql +@relation1 +char(32)+@field2 +@operator2 +char(39) +@content2 +char(39)if (@relation2 is not null)set @tempSql =@tempSql +@relation2 +char(32) +@field3 +@operator3 +char(39)+@content3 +char(39)execute (@tempSql)END
之后在D层编写函数,调用和执行存储过程并将所需参数传进来:
Public Function QueryWorklog(worklog As QueryWorklog) As List(Of Entity.WorklogEntity) Implements IWorklog.QueryWorklog Dim mylist As List(Of Entity.WorklogEntity) Dim mydt As New DataTable Dim sql As String = "PROC_QueryWorklog" Dim paras As SqlParameter() = {New SqlParameter("@field1", worklog.Field1), New SqlParameter("@field2", worklog.Field2), New SqlParameter("@field3", worklog.Field3), New SqlParameter("@operator1", worklog.Operatorchar1), New SqlParameter("@operator2", worklog.Operatorchar2), New SqlParameter("@operator3", worklog.Operatorchar3), New SqlParameter("@content1", worklog.Content1), New SqlParameter("@content2", worklog.Content2), New SqlParameter("@content3", worklog.Content3), New SqlParameter("@relation1", worklog.Relation1), New SqlParameter("@relation2", worklog.Relation2)} mydt = workloghelper.ExecuteSelect(sql, CommandType.StoredProcedure, paras) mylist = Entity.ConvertTo.ConvertToList(Of Entity.WorklogEntity)(mydt) Return mylist End Function
而后在B层利用接口调用D层的组合查询函数:
Public Function QueryWorklog(ByVal worklog As Entity.QueryWorklog) As List(Of WorklogEntity) iworklog = fworklog.CreateWorklog Return iworklog.QueryWorklog(worklog) End Function
最后在U层将界面中用户的输入内容,赋给实体参数中相应的属性:
Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click Dim mylist As List(Of Entity.WorklogEntity) Dim worklog As New Entity.QueryWorklog Dim Bworklog As New BLL.WorklogBLL worklog.Field1 = ConvertField(ComboBox1.Text.Trim) worklog.Field2 = ConvertField(ComboBox2.Text.Trim) worklog.Field3 = ConvertField(ComboBox3.Text.Trim) worklog.Operatorchar1 = ComboBox4.Text.Trim worklog.Operatorchar2 = ComboBox5.Text.Trim worklog.Operatorchar3 = ComboBox6.Text.Trim worklog.Content1 = TextBox1.Text.Trim worklog.Content2 = TextBox2.Text.Trim worklog.Content3 = TextBox3.Text.Trim worklog.Relation1 = ComboBox7.Text.Trim worklog.Relation2 = ComboBox8.Text.Trim mylist = Bworklog.QueryWorklog(worklog) If mylist.Count > 0 Then DataGridView1.DataSource = mylist Else MsgBox("未检索到您需要的数据,请重新确认查询条件!", MsgBoxStyle.OkOnly, "提示") End If End Sub
最后总结一下组合查询的功能实现:单纯来看这个功能看起来很高级,可以随意组合你的筛选条件,但是它的实现其实非常简单,其核心就是将用户输入的限制条件进行拼接和组装,形成Select语句的where子句,然后调用系统函数执行这条SQL语句,将结果返回,难点就是在细节的处理上,即如何将用户输入的限制条件放入到SQL语句中。这就是我对组合查询的认识,其实代码实现并不是最重要的,重要的是思想上的思考,如何将问题简化,并且加以实现才是我们学习的核心。
- 重构之路 组合查询之传参+存储过程
- 重构(组合查询)-存储过程
- 重构之路 组合查询之传递SQL字符串
- 【机房重构】组合查询—存储过程
- 机房重构之组合查询
- 机房重构之组合查询
- 机房重构之组合查询
- 机房重构之组合查询
- 【机房重构】之组合查询
- C#个人重构之组合查询
- 机房重构之组合查询
- 重构机房组合查询之学生基本信息查询
- 【机房重构】SQl之存储过程
- 机房重构之存储过程
- 机房重构之存储过程
- 【c#机房重构】之存储过程
- 机房重构结账之存储过程
- 走在机房重构的路上之组合查询
- servlet的两种配置
- 2014.08.18 周一-JSP-div-css-Maven-web服务器
- Struts2.1权威指南里的一些不熟悉的概念
- linux常用命令
- Activiti5.9 流程实例中同一个流程businessKey重复的问题
- 重构之路 组合查询之传参+存储过程
- tar.gz 安装
- c语言的缓冲区
- 最大流相关概念及算法
- 【索引】Matrix & System of Linear Equations
- 属性与内存管理(属性与内存管理都是相互关联的)
- 浅谈GraphX
- HTTP代理,代理服务器
- String 和StringBuffer的区别