博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
重构之路 组合查询之传參+存储过程
阅读量:5869 次
发布时间:2019-06-19

本文共 7169 字,大约阅读时间需要 23 分钟。

         上篇博文给大家一起讨论了实现组合查询的一种方法,即在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 here if (@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

         最后总结一下组合查询的功能实现:单纯来看这个功能看起来非常高级,能够任意组合你的筛选条件,可是它的实现事实上非常easy,其核心就是将用户输入的限制条件进行拼接和组装,形成Select语句的where子句,然后调用系统函数运行这条SQL语句,将结果返回。难点就是在细节的处理上。即怎样将用户输入的限制条件放入到SQL语句中。这就是我对组合查询的认识。事实上代码实现并非最重要的。重要的是思想上的思考,怎样将问题简化。而且加以实现才是我们学习的核心。

转载于:https://www.cnblogs.com/gavanwanggw/p/6708122.html

你可能感兴趣的文章
Mysql列类型:日期时间型
查看>>
java 编程中的非空判断怎么加才优雅?
查看>>
zookeeper的原理及应用
查看>>
如何阅读一本书(记录)
查看>>
BGP选路方法
查看>>
2011年度十大杰出IT博客
查看>>
SAS硬盘指示灯状态对照表
查看>>
java并发编程系列阅读笔记
查看>>
Configuring Hive On Spark
查看>>
spring boot中实现响应图片的方法以及改进
查看>>
Leetcode日记8
查看>>
Java多线程技能
查看>>
从 Project Professional 中登录 Project Server
查看>>
单链表的一些经典面试题
查看>>
frameset iframe frame之间的区别
查看>>
Python转义字符
查看>>
Kubernetes平台中日志收集
查看>>
ElasticSearch学习(8)-Restful接口查询操作
查看>>
URL—base64加密
查看>>
打包审核的那些个坑 (含swift)
查看>>