将复杂查询写到SQL配置文件--SOD框架的SQL-MAP技术简介

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

今天看到一片热门的博客, .NET高级工程师面试题之SQL篇 ,要求找出每一个系的最高分,并且按系编号,学生编号升序排列。这个查询比较复杂,也比较典型,自从用了ORM后,很久没有写过SQL语句了,于是我研究了下,自己也写了一个:

复制代码
 WITH cte1 as (
 select stu.deptID,
        D.depName, 
        stu.stuid ,
        stu.stuName,
        score_sum.AllScore
    from dbo.Student stu
      inner join (select stuid ,SUM(score) as AllScore from dbo.Score group by stuid) score_sum      
          on  stu.stuid =score_sum.stuid 
      inner join dbo.Department D on stu.deptID= D.depID
)
 select cte1.* from cte1 
      inner join (select deptID, max(AllScore) maxScore from cte1  group by deptID) M
            on cte1.AllScore = M.maxScore and cte1.deptID=M.deptID
 order by cte1.deptID,
          cte1.stuid
复制代码

 

我们工作中常常遇到类似的复杂查询,但常见的做法是把它写到BLL程序中,也有同学喜欢写在存储过程中,它有什么问题呢?

  • 写到程序中,
    • 容易养成拼接SQL的习惯,
    • 并且SQL语句零散的分布到程序的各个地方,不好维护,
    • 并有可能发生安全问题;
  • 写到存储过程,
    • 如果多了也不好维护,
    • 并且程序调用不如直接调用SQL语句方便,
    • 部署的时候还得部署这些存储过程,系统上线复杂的时候,常常忘记更新一两个存储过程。

除此之外,SQL语句不论写到程序中或者写在存储过程中,都有一个很大的缺点,程序无法跨数据库平台!

如果把所有的SQL语句都集中管理在一个配置文件中,那么程序编写和维护、发布都要方便很多,也容易跨数据库平台,甚至可以根据这个SQL配置文件,写个代码生成器,自动生成DAL层代码。 大名鼎鼎的 iBatis,MyBaits.Net 就是这样的功能,但它定义了一套复杂的规则,配置起来很复杂,项目可能80%的时间都在写查询配置,如果没有代码工具开发工作量很大的。

当然,与MyBatis不一样之处,SOD框架并不主张将项目的查询都用SQL写到SqlMap文件,建议大家仅将复杂的SQL查询写到SqlMap配置文件,一般项目这类复杂查询也就20%左右,而对于80%的一般查询,使用ORM即可,框架同时支持ORM与SqlMap技术,这是SOD框架的一大特点

SQL-MAP--集中管理SQL查询

PDF.NET SOD框架SQL-MAP功能借鉴了iBaits的思想,但是大力简化它的配置并提供代码生成工具,可以自动化的完成DAL代码生成,下面是它的工作过程:

SQL-MAP工作过程

SOD框架提供了SqlMap配置文件管理器来管理和编写SQL配置文件,然后通过代码生成器生成DAL层代码,最后交给SODk框架执行。

编写SQL配置文件,如果通过上面说的配置工具来做,可以参考这篇文章:

PDF.NET之SQL-MAP使用图解

但这个程序太老,一直没有更新,工具的下载地址是:

http://ft.codeplex.com/releases/view/65308

工具包含在这个“集成开发工具” 内,通过菜单或者图标即可操作。下面是集成开发工具打开数据库并执行本文的查询的界面:

 

 

SQL-MAP配置管理工具不太好用,初学者学习概念可以试一试,但是要想熟练应用,推荐使用VS来写这个SQL配置文件更方便。本文将向大家详细介绍这个过程。

一、编写SQL-MAP配置文件

1.1,建立SQL-MAP应用项目

如下图所示,新建一个 SqlMapDemo项目,在它里面先添加一个XML文件,文件命名为 SqlMap.config ,然后,找到上面的“集成开发工具”的安装目录,添加 SqlMap.xsd 文件。该文件很重要,它是我们在VS里面编写XML文件能够有 智能提示 的关键。

 

添加了这个XSD文件到项目后,还需要把下面的内容

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xsi:noNamespaceSchemaLocation="SqlMap.xsd"  

注意:有关此详细内容,请参见《PDF.NET(PWMIS数据开发框架)之SQL-MAP目标和规范》。

1.2 使用XML智能提示

将上面的内容添加到刚才的SqlMap.config 文件中,这样,输入尖括号,或者输入空格,智能提示内容就出来,如下所示:

 

这是添加完整的SqlMap.config 文件,该文件完全手写,在智能提示的帮助下,编写还是很方便的。请注意配置文件内各个节点属性的内容,详细内容请看上面链接的文件。注意,这里要用 CDATA 来写SQL查询。

注意:如果要执行获得结果集的查询,需要在Sql-Map文件的命令节点里面使用 Select 节点,并且需要指明 ResultClass 的类型,它有下面几种可选值:

  1. ValueType --结果为值对象,比如Int,String之类的查询单值结果
  2. DataSet --数据集,默认
  3. EntityObject --单个实体类对象
  4. EntityList --实体类列表

注意:如果指定 ResultClass="EntityObject" / ResultClass="EntityList" ,那么需要同时申明 ResultMap 属性,表示结果要映射到的具体类型,比如:

ResultClass="EntityObject" ResultMap="XXXNameSpace.YYYEntityClass"

有关此详细内容,请参见《PDF.NET(PWMIS数据开发框架)之SQL-MAP目标和规范》。

注意:<Script > 配置节,出了有Type表面数据库类型之外,还必须有 ConnectionString ="" Version="" 这样的内容,否则,下面的代码生成不能生成代码

二、使用代码生成器

2.1 配置代码生成器

打开PDF.NET集成开发工具,选择菜单 “配置”--SQL-MAP代码生成器配置,出现下面的界面:

在编辑区域,鼠标右键菜单,启用用编辑,如果是第一次配置,请一定删除配置文件内的中文注释,否则配置文件格式无法识别。

这里主要配置SqlMap.config文件所在的完整路径,输出代码的目录,以及命名空间之类。

编辑完成,在编辑区的鼠标右键菜单上,启用“保存文件”的功能

2.2 生成代码

如果上面配置正确,点击第二个工具栏的图标,就可以自动调用处 PDFCodeMaker.exe 程序,读取配置文件信息,自动生成DAL代码文件,如下图:

6

之后,我们添加刚才生成的DAL代码文件到项目中,如上图。

三、使用SqlMap配置文件

3.1 SqlMap配置文件的使用方式

SqlMap配置文件有2种使用方式,

  • 在应用程序配置文件的AppSeting 配置节里面指定文件路径
  • 嵌入到程序集文件作为资源文件使用

在本示例中,采用嵌入式编译的方式,这样SQL语句的安全性更高,并且支持一个项目中使用多个SqlMap配置文件,但是不如作为独立文件使用修改方便。如下图:

这种方式编译,该资源文件的名字就是 “程序集默认名字空间.目录名字.文件名字” ,这里的例子是 SqlMapDemo.SqlMap.config ,由于在跟目录下面,没有目录名字。

注意,要正确使用嵌入式SQL配置文件,还必须在该文件中指出"嵌入文件的程序集名称,资源文件名字“,如本例如下图红色边框标记的部分所示:

 

SqMap.config 文件修改完成后,在此启动 PDFCodeMaker.exe 程序,重新生成DAL文件,如下所示,增加了签入式文件的设置:

3.2 生成SqlMapDAL文件

检查发现,DAL类文件的名字空间不正确,这里修改下配置文件的 RootNameSpace 部分,如下图:

重新生成DAL类文件,我们看看最后生成的SqlMapDAL类的内容:

  SqlMapDemo.SqlMapDAL

3.3 团队开发

团队开发常常涉及到文件修改冲突,SqlMap配置文件也会遇到,如果大家不去修改同一个Command配置,用源代码管理器的自动合并功能是没有问题的。

如果是独占式签出管理,那么一个开发人员使用SqlMap文件,另外一个就只有等待了,但框架提供了变通的方式来解决这个问题。

  • 按功能模块划分DAL程序集,每个DAL程序集一个SqlMap.config文件,选择嵌入式编译;
  • 在一个DAL程序项目上建立多个SqlMap.config文件,选择嵌入式编译 。

注意:建议每一个开发人员负责一个SqlMap.config文件的管理,然后各自配置自己的PDFCodeMaker.exe.config

 

3.4 切换数据库

在同一个SqlMap.config 文件里面添加新的 <Script Type="" 节点,比如Oracle ,然后将原来数据库类型的SQL查询改写成当前类型的SQL查询语句 ,程序会根据DBMapper的AdoHelper的实际类型自动选择正确的<Script Type="" 节点,从而完成数据库切换,实现你的应用系统跨数据库的功能。

另外,也可以另外建立一个SqlMap.config 文件,里面专门写新的数据库下面的SQL语句,我们以前一个银行的项目,就是这样从SqlServer移植到PostgreSql的。


四、运行SQL-MAP程序

4.1 配置连接字符串

SQL-MAP DAL类程序仍然通过AdoHelper 对象来执行,这里添加一个应用程序配置文件:

复制代码
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name ="default" connectionString="Data Source=.;Initial Catalog=TestDB;Integrated Security=True" providerName="SqlServer"/>
  </connectionStrings>
</configuration>
复制代码

4.2 运行程序

然后,就可以写如下的代码来测试SQL-MAP程序了:

复制代码
 class Program
 {
     static void Main(string[] args)
     {
         Console.WriteLine("PDF.NET SOD框架 SqlMap示例程序--2015.5.12-------");
         Console.WriteLine("http://www.pwmis.com/sqlmap ---------");
        
         SqlMapDemo.SqlMapDAL.TestSqlMapClass test = new SqlMapDAL.TestSqlMapClass();

         //AdoHelper db = new SqlServer();
         //取最后一个连接配置
         AdoHelper db = MyDB.GetDBHelper();
         //SQL-MAP DAL 默认也会取最后一个连接配置,所以下面一行代码可以注释
         //test.CurrentDataBase = db;
         DataSet data = test.QueryStudentSores();

         Console.WriteLine("查询到记录数量:{0}",data.Tables[0].Rows.Count);
         Console.WriteLine("测试完成。");
         Console.Read();
     }
 }
复制代码

结果运行如下:

设置一个断点,我们来看看 ”SQL-MAP DAL对象“的Mapper对象有哪些内容:

在及时窗口输入 test.Mapper ,回车,就可看到上面的内容了。这里显示了刚才配置的SQL-MAP嵌入式文件的位置和文件独特的路径:SqlMapFile 属性。

 下面是详细的结果:

复制代码
test.Mapper
{PWMIS.DataMap.SqlMap.SqlMapper}
    _CommandClassName: "TestGroup"
    _DataBase: {PWMIS.DataProvider.Data.SqlServer}
    _dataBaseType: SqlServer
    _embedAssemblySource: "SqlMapDemo,SqlMapDemo.SqlMap.config"
    _ParaChar: "@"
    _ParamsMap: {PWMIS.Common.ParamMapType[0]}
    _ParasLenth: 0
    _ResultClass: ValueType
    _ResultMap: ""
    _SqlMapFile: "@R://SqlMapDemo,SqlMapDemo.SqlMap.config"
复制代码

 结语

测试完全正确,使用 SOD框架的SQL-MAP 技术是不是很简单?

后续都只需要修改下文件里面的SQL语句就行了,

一次配置,一键生成!

这就是,SOD框架的SQL-MAP 技术的特点!结合SOD框架的ORM功能,它完全可以代替MyBatis.Net 。

感谢大家关注PDF.NET SOD框架,这是一个开源免费的框架,详细信息请看官网 http://www.pwmis.com/sqlmap

框架开源项目地址:http://pwmis.codeplex.com ,本项目示例代码已经签入到codeplex 上,大家可以通过SVN下载使用。

本篇程序已经提供了独立的下载:Example     Sql-Map Demo

本文相关视频播放和下载点击这里》》



    本文转自深蓝医生博客园博客,原文链接:http://www.cnblogs.com/bluedoctor/p/4498066.html,如需转载请自行联系原作者



相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
1天前
|
SQL 数据挖掘 关系型数据库
SQL查询次数大于1的记录:高效技巧与方法
在数据库管理中,经常需要统计某些操作的次数,特别是当需要找出哪些记录或值出现的次数超过一定阈值(如大于1次)时
|
22小时前
|
SQL 存储 安全
SQL查询数据库:基础概念与操作指南
在数字化时代,数据库已成为信息管理的重要工具之一。作为管理和操作数据库的核心语言,SQL(结构化查询语言)已成为数据管理和查询的关键技能。本文将全面介绍SQL查询数据库的基本概念、语句和操作指南,以帮助初学者快速上手,同时为进阶用户提供有价值的参考。一、数据库与SQL简介数据库是一种存储、管理和检索
13 3
|
1天前
|
SQL 数据库
SQL查询中排除空值列的技巧与方法
在数据库查询中,经常需要处理包含空值(NULL)的数据列
|
1天前
|
SQL Oracle 关系型数据库
SQL查询默认表空间的技巧与方法
在数据库管理中,表空间是存储数据库对象(如表、索引等)的逻辑空间单元
|
2天前
|
SQL 存储 缓存
SQL数据库查询详解
数据库是现代信息社会的基石,它们存储和管理着大量的数据。而SQL(StructuredQueryLanguage)作为一种强大的数据库查询语言,广泛应用于各种数据库系统中。本文将详细介绍SQL数据库查询的基本概念、语法、常用操作以及优化策略。一、SQL数据库查询概述SQL是一种用于管理关系数据库的标
20 3
|
2天前
|
SQL 存储 数据挖掘
SQL Server 日期格式查询详解
SQL Server 日期格式查询详解
21 2
|
1天前
|
SQL 存储 数据库
SQL查询100以内数值的技巧与方法
在数据库操作中,经常需要查询特定范围内的数据,比如查询某个数值字段在100以内的记录
|
1天前
|
SQL 数据处理 数据库
SQL语句优化与查询结果优化:提升数据库性能的实战技巧
在数据库管理和应用中,SQL语句的编写和查询结果的优化是提升数据库性能的关键环节
|
1天前
|
SQL 数据库管理 索引
SQL语句查询教师表:高效构建与技巧分享
在数据库管理中,查询操作是最基础也是最重要的功能之一
|
1天前
|
SQL 关系型数据库 MySQL
如何确认SQL查询是否使用了索引:详细步骤与技巧
在数据库管理和优化中,确认SQL查询是否有效利用了索引是提升性能的关键步骤