求助：sql转换iBatis

yao_ming_01 2010-11-08 03:36:08 


SELECT         

ndbptbl1.touroku_no AS "difd01tourokuNo",         

t1.department_cd AS "difd01departmentAreaCd",         

t2.department_cd AS "difd01departmentCityCd",           

t1.department_name AS "difd01departmentNm1",         

t2.department_name AS "difd01departmentNm2",         

TO_CHAR(ndbptbl1.status_haaku_dtime,'YYYY/MM/DD HH24:MI:SS') AS "difd01statushaakuDtime",         

ndbptbl1.report_element1 AS "difd01reportElement1",         

ndbptbl1.report_element2 AS "difd01reportElement2",         

ndbptbl1.report_element3 AS "difd01reportElement3",         

ndbptbl1.report_element4 AS "difd01reportElement4",         

ndbptbl1.report_element5 AS "difd01reportElement5",         

ndbptbl1.report_element6 AS "difd01reportElement6",         

ndbptbl1.report_element7 AS "difd01reportElement7",         

ndbptbl1.report_element8 AS "difd01reportElement8",         

ndbptbl1.report_element9 AS "difd01reportElement9",         

ndbptbl1.report_element10 AS "difd01reportElement10",         

ndbptbl1.notes AS "difd01Notes"         

FROM         ndbpdisokuhoreporttbl ndbptbl1,     

(SELECT         department_name,         department_cd         FROM b_m_department_t_i         WHERE             COMPANY_CD = '003'                   GROUP BY department_name,department_cd)  t1,         

(SELECT         department_name,         department_cd         FROM b_m_department_t_i         WHERE             COMPANY_CD = '002'                   GROUP BY department_name,department_cd)  t2         

WHERE   

 ndbptbl2.Area_Cd = t1.department_cd  

AND   

ndbptbl2.CITY_CD = t2.department_cd   

AND   

ndbptbl2.status_haaku_dtime = (SELECT   MAX(status_haaku_dtime)    FROM   ndbpdisokuhoreporttbl   WHERE   CITY_CD = t2.department_cd   GROUP BY CITY_CD)

AND

ndbptbl1.status_haaku_dtime >= TO_DATE(?,'YYYY/MM/DD')

AND

ndbptbl1.status_haaku_dtime <= TO_DATE(?,'YYYY/MM/DD')    

AND 

ndbptbl1.Area_Cd = ？

AND 

ndbptbl1.City_Cd = ？       

ORDER BY          ndbptbl1.Area_Cd ASC, ndbptbl1.City_Cd ASC,ndbptbl1.status_haaku_dtime DESC


按照以上为的sql语句，用ibatis写的如下


   <select id="selectCityDetails" parameterClass="jp.co.ntt_data.ndbp.ndbpdifd.vo.NdbpdifdSelectCityDetailsInput" resultClass="jp.co.ntt_data.ndbp.ndbpdifd.vo.NdbpdifdSelectCityDetailsOuput">

        SELECT

        ndbptbl1.touroku_no AS "difd01tourokuNo",

        t1.department_cd AS "difd01departmentAreaCd",

        t2.department_cd AS "difd01departmentCityCd",  

        t1.department_name AS "difd01departmentNm1",

        t2.department_name AS "difd01departmentNm2",

        TO_CHAR(ndbptbl1.status_haaku_dtime,'YYYY/MM/DD HH24:MI:SS') AS "difd01statushaakuDtime",

        ndbptbl1.report_element1 AS "difd01reportElement1",

        ndbptbl1.report_element2 AS "difd01reportElement2",

        ndbptbl1.report_element3 AS "difd01reportElement3",

        ndbptbl1.report_element4 AS "difd01reportElement4",

        ndbptbl1.report_element5 AS "difd01reportElement5",

        ndbptbl1.report_element6 AS "difd01reportElement6",

        ndbptbl1.report_element7 AS "difd01reportElement7",

        ndbptbl1.report_element8 AS "difd01reportElement8",

        ndbptbl1.report_element9 AS "difd01reportElement9",

        ndbptbl1.report_element10 AS "difd01reportElement10",

        ndbptbl1.notes AS "difd01Notes"

        FROM

        ndbpdisokuhoreporttbl ndbptbl1,

        (SELECT

        department_name,

        department_cd

        FROM b_m_department_t_i

        <dynamic prepend="WHERE">

            COMPANY_CD = '003' 

        </dynamic>

        GROUP BY department_name,department_cd) t1,

        (SELECT

        department_name,

        department_cd

        FROM b_m_department_t_i

        <dynamic prepend="WHERE">

            COMPANY_CD = '002' 

        </dynamic>

        GROUP BY department_name,department_cd) t2

        <dynamic prepend="WHERE"> 

              ndbptbl1.Area_Cd = t1.department_cd

            AND

              ndbptbl1.CITY_CD = t2.department_cd

            AND

              ndbptbl1.status_haaku_dtime = (SELECT 

                                               MAX(status_haaku_dtime) 

                                            FROM 

                                               ndbpdisokuhoreporttbl 

                                            WHERE

                                               CITY_CD = t2.department_cd

                                            GROUP BY CITY_CD)

            <isPropertyAvailable property="strat_time">                                

	            <isNotEmpty prepend="AND" property="strat_time">

	              ndbptbl1.status_haaku_dtime >= TO_DATE(#strat_time#,'YYYY/MM/DD')

	            </isNotEmpty>

	        </isPropertyAvailable>

            <isPropertyAvailable property="end_time">

	            <isNotEmpty prepend="AND" property="end_time">

	              ndbptbl1.status_haaku_dtime <= TO_DATE(#end_time#,'YYYY/MM/DD')

	            </isNotEmpty>

	        </isPropertyAvailable>

            <isPropertyAvailable property="city_cd">

	            <isNotEmpty prepend="AND" property="area_cd" removeFirstPrepend="false"> 

	              ndbptbl1.Area_Cd = #area_cd#

	            </isNotEmpty>

            </isPropertyAvailable>

            <isPropertyAvailable property="city_cd">

	            <isNotEmpty prepend="AND" property="city_cd" removeFirstPrepend="false" >

	              ndbptbl1.CITY_CD = #city_cd#

	            </isNotEmpty>

            </isPropertyAvailable>

            

        </dynamic>

        ORDER BY

         ndbptbl1.Area_Cd ASC, ndbptbl1.City_Cd ASC,ndbptbl1.status_haaku_dtime DESC

</select>


当不传任何参数的情况下，全显示是正常的，当传某个参数的时候,会少一个and，怎么写才可以呢，请大家忙帮！
...全文
90 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
「已注销」 2010-11-08
  • 打赏
  • 举报
 回复
iBatics其实很简单的，对应上表里的字段跟类的属性就可以了。
至于一些标签，也就那么几种。
yao_ming_01 2010-11-08
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 zsh0809 的回复:]
“<=”替换为：
<![CDATA[<=]]>

“>=”替换为：
<![CDATA[>=]]>
其他部分全部照写，不需要加任何标签。如果你sql没有问题，那么执行也不会有问题。
[/Quote]

谢谢
「已注销」 2010-11-08
  • 打赏
  • 举报
 回复
给你个小例子参考下：
<select id="SelectItemForPreview" parameterClass="TraTransitemView" resultMap="TraTransitemViewResult">

			select  a.serial,valid, d.display_value AS project_name, a.text_id,a.text_type,a.text_desc,a.text_length,

			a.application_type,a.usage,a.valid,b.item_english_content as english_content,a.export_path as export_path

			from tra_transitem a left join tra_transitem_content b on a.serial=b.transitem_serial

			left join qpcs_parameter d on d.enum_name = 'Project' and d.enum_value =a.project_name

			where 1=1



			<dynamic> 

			<isNotEmpty prepend="AND" property="Valid">valid=#Valid#</isNotEmpty>

			<isNotEmpty prepend="AND" property="ProjectName">project_name=#ProjectName#</isNotEmpty>

			<isNotEmpty prepend="AND" property="TextType">text_type like '%' + #TextType# + '%'</isNotEmpty>

			 

		</dynamic>

			ORDER BY project_name,application_type,usage,text_id

		</select>
「已注销」 2010-11-08
  • 打赏
  • 举报
 回复
“<=”替换为：
<![CDATA[<=]]>

“>=”替换为：
<![CDATA[>=]]>
其他部分全部照写，不需要加任何标签。如果你sql没有问题，那么执行也不会有问题。
「已注销」 2010-11-08
  • 打赏
  • 举报
 回复 
SELECT         

ndbptbl1.touroku_no AS "difd01tourokuNo",         

t1.department_cd AS "difd01departmentAreaCd",         

t2.department_cd AS "difd01departmentCityCd",           

t1.department_name AS "difd01departmentNm1",         

t2.department_name AS "difd01departmentNm2",         

TO_CHAR(ndbptbl1.status_haaku_dtime,'YYYY/MM/DD HH24:MI:SS') AS "difd01statushaakuDtime",         

ndbptbl1.report_element1 AS "difd01reportElement1",         

ndbptbl1.report_element2 AS "difd01reportElement2",         

ndbptbl1.report_element3 AS "difd01reportElement3",         

ndbptbl1.report_element4 AS "difd01reportElement4",         

ndbptbl1.report_element5 AS "difd01reportElement5",         

ndbptbl1.report_element6 AS "difd01reportElement6",         

ndbptbl1.report_element7 AS "difd01reportElement7",         

ndbptbl1.report_element8 AS "difd01reportElement8",         

ndbptbl1.report_element9 AS "difd01reportElement9",         

ndbptbl1.report_element10 AS "difd01reportElement10",         

ndbptbl1.notes AS "difd01Notes"         

FROM         ndbpdisokuhoreporttbl ndbptbl1,     

(SELECT         department_name,         department_cd         FROM b_m_department_t_i         WHERE             COMPANY_CD = '003'                   GROUP BY department_name,department_cd)  t1,         

(SELECT         department_name,         department_cd         FROM b_m_department_t_i         WHERE             COMPANY_CD = '002'                   GROUP BY department_name,department_cd)  t2         

WHERE   

 ndbptbl2.Area_Cd = t1.department_cd  

AND   

ndbptbl2.CITY_CD = t2.department_cd   

AND   

ndbptbl2.status_haaku_dtime = (SELECT   MAX(status_haaku_dtime)    FROM   ndbpdisokuhoreporttbl   WHERE   CITY_CD = t2.department_cd   GROUP BY CITY_CD)

AND

ndbptbl1.status_haaku_dtime >= TO_DATE(?,'YYYY/MM/DD')

AND

ndbptbl1.status_haaku_dtime <= TO_DATE(?,'YYYY/MM/DD')    

AND 

ndbptbl1.Area_Cd = ？

AND 

ndbptbl1.City_Cd = ？       

ORDER BY          ndbptbl1.Area_Cd ASC, ndbptbl1.City_Cd ASC,ndbptbl1.status_haaku_dtime DESC

上面这一段中全部照写，不需要加任何标签。

ndbptbl1.status_haaku_dtime >= TO_DATE(?,'YYYY/MM/DD')
AND
ndbptbl1.status_haaku_dtime <= TO_DATE(?,'YYYY/MM/DD')
这里面的>=和<=需要加上外套，要不会报错。
yao_ming_01 2010-11-08
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 maco_wang 的回复:]
判断参数个数如果不为0，就补充个and不行吗？不懂ibatis语法。
[/Quote]

虽然是xml格式，但是不能取到参数的个数，标签就是判断参数是否有，如果有加个and
可以我最开始这样判断，当没有的时候竟然少个and，所以只能用这种笨方法写了。
dawugui 2010-11-08
  • 打赏
  • 举报
 回复 
不懂，帮顶，学习，蹭分.
yao_ming_01 2010-11-08
  • 打赏
  • 举报
 回复 


<select id="selectCityDetails" parameterClass="jp.co.ntt_data.ndbp.ndbpdifd.vo.NdbpdifdSelectCityDetailsInput" resultClass="jp.co.ntt_data.ndbp.ndbpdifd.vo.NdbpdifdSelectCityDetailsOuput">

        SELECT

        ndbptbl1.touroku_no AS "difd01tourokuNo",

        t1.department_cd AS "difd01departmentAreaCd",

        t2.department_cd AS "difd01departmentCityCd",  

        t1.department_name AS "difd01departmentNm1",

        t2.department_name AS "difd01departmentNm2",

        TO_CHAR(ndbptbl1.status_haaku_dtime,'YYYY/MM/DD HH24:MI:SS') AS "difd01statushaakuDtime",

        ndbptbl1.report_element1 AS "difd01reportElement1",

        ndbptbl1.report_element2 AS "difd01reportElement2",

        ndbptbl1.report_element3 AS "difd01reportElement3",

        ndbptbl1.report_element4 AS "difd01reportElement4",

        ndbptbl1.report_element5 AS "difd01reportElement5",

        ndbptbl1.report_element6 AS "difd01reportElement6",

        ndbptbl1.report_element7 AS "difd01reportElement7",

        ndbptbl1.report_element8 AS "difd01reportElement8",

        ndbptbl1.report_element9 AS "difd01reportElement9",

        ndbptbl1.report_element10 AS "difd01reportElement10",

        ndbptbl1.notes AS "difd01Notes"

        FROM

        ndbpdisokuhoreporttbl ndbptbl1,

        (SELECT

        department_name,

        department_cd

        FROM b_m_department_t_i

        <dynamic prepend="WHERE">

            COMPANY_CD = '003' 

        </dynamic>

        GROUP BY department_name,department_cd) t1,

        (SELECT

        department_name,

        department_cd

        FROM b_m_department_t_i

        <dynamic prepend="WHERE">

            COMPANY_CD = '002' 

        </dynamic>

        GROUP BY department_name,department_cd) t2

        <dynamic prepend="WHERE">

            <isPropertyAvailable property="strat_time">                                

	            <isNotEmpty prepend="AND" property="strat_time">

	              ndbptbl1.status_haaku_dtime >= TO_DATE(#strat_time#,'YYYY/MM/DD')

	         AND   ndbptbl1.status_haaku_dtime = (SELECT 

                                               MAX(status_haaku_dtime) 

                                            FROM 

                                               ndbpdisokuhoreporttbl 

                                            WHERE

                                               CITY_CD = t2.department_cd

                                            GROUP BY CITY_CD)

            AND

              ndbptbl1.Area_Cd = t1.department_cd

            AND

              ndbptbl1.CITY_CD = t2.department_cd

	            </isNotEmpty>

	            <isEmpty prepend="AND" property="strat_time">

	            ndbptbl1.status_haaku_dtime = (SELECT 

                                               MAX(status_haaku_dtime) 

                                            FROM 

                                               ndbpdisokuhoreporttbl 

                                            WHERE

                                               CITY_CD = t2.department_cd

                                            GROUP BY CITY_CD)

            AND

              ndbptbl1.Area_Cd = t1.department_cd

            AND

              ndbptbl1.CITY_CD = t2.department_cd

	            </isEmpty>

	        </isPropertyAvailable>

            <isPropertyAvailable property="end_time">

	            <isNotEmpty prepend="AND" property="end_time">

	              ndbptbl1.status_haaku_dtime <= TO_DATE(#end_time#,'YYYY/MM/DD')

	            </isNotEmpty>

	        </isPropertyAvailable>

            <isPropertyAvailable property="city_cd">

	            <isNotEmpty prepend="AND" property="area_cd" removeFirstPrepend="false"> 

	              ndbptbl1.Area_Cd = #area_cd#

	            </isNotEmpty>

            </isPropertyAvailable>

            <isPropertyAvailable property="city_cd">

	            <isNotEmpty prepend="AND" property="city_cd" removeFirstPrepend="false" >

	              ndbptbl1.CITY_CD = #city_cd#

	            </isNotEmpty>

            </isPropertyAvailable>

            

        </dynamic>

        ORDER BY

         ndbptbl1.Area_Cd ASC, ndbptbl1.City_Cd ASC,ndbptbl1.status_haaku_dtime DESC

</select>


这样写勉强可以，我也是新学，如果大家有好的方法交流一下，谢谢
叶子 2010-11-08
  • 打赏
  • 举报
 回复
判断参数个数如果不为0，就补充个and不行吗？不懂ibatis语法。
SpringSpringMVCMybatis框架
Spring SSM项目 https://edu.csdn.net/course/detail/37962 Spring框架是由于软件开发的复杂性而创建的。Spring使用的是基本的JavaBean来完成以前只可能由EJB完成的事情。然而，Spring的用途不仅仅限于服务器端的开发。从简单性、可测试性和松耦合性角度而言，绝大部分Java应用都可以从Spring中受益。◆目的：解决企业应用开发的复杂性◆功能：使用基本的JavaBean代替EJB，并提供了更多的企业应用功能◆范围：任何Java应用Spring是一个轻量级控制反转(IoC)和面向切面(AOP)的容器框架。Spring MVC属于SpringFrameWork的后续产品，已经融合在Spring Web Flow里面。Spring 框架提供了构建 Web 应用程序的全功能 MVC 模块。使用 Spring 可插入的 MVC 架构，从而在使用Spring进行WEB开发时，可以选择使用Spring的Spring MVC框架或集成其他MVC开发框架，如Struts1(现在一般不用)，Struts 2(一般老项目使用)等等。MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code，并且改名为MyBatis 。2013年11月迁移到Github。iBATIS一词来源于“internet”和“abatis”的组合，是一个基于Java的持久层框架。iBATIS提供的持久层框架包括SQL Maps和Data Access Objects（DAOs）
SQL注入.rtf
http://www.05112.com 前言 鄙人今天心血来潮突然想写篇文章,鄙人从来没写过文章,如果有错误的地方请多多指教.本文需要有基础的SQL语句知识才可以更好的理解.建议想学习的人多去了解一下SQL语句和编程语言,知己知彼才能百战百胜. 我不希翼得到读者您的好评,尽管我尽力了;只希望本文能解决您学习过程的障碍,希望您早日掌握有关MYSQL注入方面的知识. 1.MYSQL ...
【Hibernate】hibernate原生sql利用transformers返回多表自定义类型对象
大致结构： Person（人）： id，name，age,bookId Book（书）：id，bookName Author（作者）：id，authorName，bookId 一个人 只有 一本书，一本书 有多个 作者，一个作者 只出 一本书；（可能举例不好，明白就行） Person ----> Book : OneToOne Book ---->...
基于SpringBoot的医院急诊管理系统
在管理员后台我们可以看到有用户管理、医生信息管理、科室信息管理、紧急预约管理、医院病房管理、病房费用管理等相关功能模块。在前台我们可以看到首页、医生信息、医院病房、医院论坛、医院资讯等功能模块。数据库：MySQL5.7和Navicat管理工具结合。前端：采用HTML和Vue相结合开发。Java开发工具：JDK1.8。后端框架：SpringBoot。服务器：Tomcat8.5。是否Maven项目：是。
基于JAVA+SpringBoot+Vue的前后端分离的公益慈善服务平台
随着网络技术的发展，公益慈善网站已经逐渐是公益行动的主要信息载体。随着社会的发展以及人们事业水平的提高，我们的事业越来越智能化，越来越方便[2]。利用互联网技术，设计与实现公益慈善捐助服务平台，让更多的人得到帮助，以及帮助他人，让我们成为慈善的主角，让需要帮助的人感受到这个世界的爱。同时公益组织也可以为求助者和捐赠者提供更好的服务，让每一次求助和帮助都变得公开透明[3]。
MS-SQL Server

34,593

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章