备案控制台
开发者社区 数据库 文章 正文

记录mybatis的sql语句应用

2015-10-26 639
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.
版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/u010741376/article/details/49420983

第一段增删改查:

   

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mgear.samering.dao.BascomcertMapper" >
  <resultMap id="BaseResultMap" type="com.mgear.samering.domain.Bascomcert" >
    <id column="CertificateId" property="certificateid" jdbcType="VARCHAR" />
    <result column="CompanyId" property="companyid" jdbcType="VARCHAR" />
    <result column="SerailNumber" property="serailnumber" jdbcType="INTEGER" />
    <result column="Owner" property="owner" jdbcType="VARCHAR" />
    <result column="CertSerialNumber" property="certserialnumber" jdbcType="VARCHAR" />
    <result column="PwdSerialNumber" property="pwdserialnumber" jdbcType="VARCHAR" />
    <result column="UniqueIdentifier" property="uniqueidentifier" jdbcType="VARCHAR" />
    <result column="KeySerialNumber" property="keyserialnumber" jdbcType="VARCHAR" />
    <result column="StartDate" property="startdate" jdbcType="DATE" />
    <result column="EndDate" property="enddate" jdbcType="DATE" />
    <result column="Award" property="award" jdbcType="VARCHAR" />
    <result column="CertType" property="certtype" jdbcType="VARCHAR" />
    <result column="IfValid" property="ifvalid" jdbcType="TINYINT" />
    <result column="SysCreated" property="syscreated" jdbcType="TIMESTAMP" />
    <result column="SysCreatedby" property="syscreatedby" jdbcType="VARCHAR" />
    <result column="SysOrg" property="sysorg" jdbcType="VARCHAR" />
    <result column="SysDept" property="sysdept" jdbcType="VARCHAR" />
    <result column="SysLastUpd" property="syslastupd" jdbcType="TIMESTAMP" />
    <result column="SysLastUpdBy" property="syslastupdby" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    CertificateId, CompanyId, SerailNumber, Owner, CertSerialNumber, PwdSerialNumber, 
    UniqueIdentifier, KeySerialNumber, StartDate, EndDate, Award, CertType, IfValid, 
    SysCreated, SysCreatedby, SysOrg, SysDept, SysLastUpd, SysLastUpdBy
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    from bas_comcert
    where CertificateId = #{certificateid,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    delete from bas_comcert
    where CertificateId = #{certificateid,jdbcType=VARCHAR}
  </delete>
  <insert id="insert" parameterType="com.mgear.samering.domain.Bascomcert" >
    insert into bas_comcert (CertificateId, CompanyId, SerailNumber, 
      Owner, CertSerialNumber, PwdSerialNumber, 
      UniqueIdentifier, KeySerialNumber, StartDate, 
      EndDate, Award, CertType, 
      IfValid, SysCreated, SysCreatedby, 
      SysOrg, SysDept, SysLastUpd, 
      SysLastUpdBy)
    values (#{certificateid,jdbcType=VARCHAR}, #{companyid,jdbcType=VARCHAR}, #{serailnumber,jdbcType=INTEGER}, 
      #{owner,jdbcType=VARCHAR}, #{certserialnumber,jdbcType=VARCHAR}, #{pwdserialnumber,jdbcType=VARCHAR}, 
      #{uniqueidentifier,jdbcType=VARCHAR}, #{keyserialnumber,jdbcType=VARCHAR}, #{startdate,jdbcType=DATE}, 
      #{enddate,jdbcType=DATE}, #{award,jdbcType=VARCHAR}, #{certtype,jdbcType=VARCHAR}, 
      #{ifvalid,jdbcType=TINYINT}, now(), #{syscreatedby,jdbcType=VARCHAR}, 
      #{sysorg,jdbcType=VARCHAR}, #{sysdept,jdbcType=VARCHAR}, now(), 
      #{syslastupdby,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.mgear.samering.domain.Bascomcert" >
    insert into bas_comcert
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="certificateid != null" >
        CertificateId,
      </if>
      <if test="companyid != null" >
        CompanyId,
      </if>
      <if test="serailnumber != null" >
        SerailNumber,
      </if>
      <if test="owner != null" >
        Owner,
      </if>
      <if test="certserialnumber != null" >
        CertSerialNumber,
      </if>
      <if test="pwdserialnumber != null" >
        PwdSerialNumber,
      </if>
      <if test="uniqueidentifier != null" >
        UniqueIdentifier,
      </if>
      <if test="keyserialnumber != null" >
        KeySerialNumber,
      </if>
      <if test="startdate != null" >
        StartDate,
      </if>
      <if test="enddate != null" >
        EndDate,
      </if>
      <if test="award != null" >
        Award,
      </if>
      <if test="certtype != null" >
        CertType,
      </if>
      <if test="ifvalid != null" >
        IfValid,
      </if>
      <if test="syscreated != null" >
        SysCreated,
      </if>
      <if test="syscreatedby != null" >
        SysCreatedby,
      </if>
      <if test="sysorg != null" >
        SysOrg,
      </if>
      <if test="sysdept != null" >
        SysDept,
      </if>
      <if test="syslastupd != null" >
        SysLastUpd,
      </if>
      <if test="syslastupdby != null" >
        SysLastUpdBy,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="certificateid != null" >
        #{certificateid,jdbcType=VARCHAR},
      </if>
      <if test="companyid != null" >
        #{companyid,jdbcType=VARCHAR},
      </if>
      <if test="serailnumber != null" >
        #{serailnumber,jdbcType=INTEGER},
      </if>
      <if test="owner != null" >
        #{owner,jdbcType=VARCHAR},
      </if>
      <if test="certserialnumber != null" >
        #{certserialnumber,jdbcType=VARCHAR},
      </if>
      <if test="pwdserialnumber != null" >
        #{pwdserialnumber,jdbcType=VARCHAR},
      </if>
      <if test="uniqueidentifier != null" >
        #{uniqueidentifier,jdbcType=VARCHAR},
      </if>
      <if test="keyserialnumber != null" >
        #{keyserialnumber,jdbcType=VARCHAR},
      </if>
      <if test="startdate != null" >
        #{startdate,jdbcType=DATE},
      </if>
      <if test="enddate != null" >
        #{enddate,jdbcType=DATE},
      </if>
      <if test="award != null" >
        #{award,jdbcType=VARCHAR},
      </if>
      <if test="certtype != null" >
        #{certtype,jdbcType=VARCHAR},
      </if>
      <if test="ifvalid != null" >
        #{ifvalid,jdbcType=TINYINT},
      </if>
      <if test="syscreated != null" >
        now(),
      </if>
      <if test="syscreatedby != null" >
        #{syscreatedby,jdbcType=VARCHAR},
      </if>
      <if test="sysorg != null" >
        #{sysorg,jdbcType=VARCHAR},
      </if>
      <if test="sysdept != null" >
        #{sysdept,jdbcType=VARCHAR},
      </if>
      <if test="syslastupd != null" >
        now(),
      </if>
      <if test="syslastupdby != null" >
        #{syslastupdby,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.mgear.samering.domain.Bascomcert" >
    update bas_comcert
    <set >
      <if test="companyid != null" >
        CompanyId = #{companyid,jdbcType=VARCHAR},
      </if>
      <if test="serailnumber != null" >
        SerailNumber = #{serailnumber,jdbcType=INTEGER},
      </if>
      <if test="owner != null" >
        Owner = #{owner,jdbcType=VARCHAR},
      </if>
      <if test="certserialnumber != null" >
        CertSerialNumber = #{certserialnumber,jdbcType=VARCHAR},
      </if>
      <if test="pwdserialnumber != null" >
        PwdSerialNumber = #{pwdserialnumber,jdbcType=VARCHAR},
      </if>
      <if test="uniqueidentifier != null" >
        UniqueIdentifier = #{uniqueidentifier,jdbcType=VARCHAR},
      </if>
      <if test="keyserialnumber != null" >
        KeySerialNumber = #{keyserialnumber,jdbcType=VARCHAR},
      </if>
      <if test="startdate != null" >
        StartDate = #{startdate,jdbcType=DATE},
      </if>
      <if test="enddate != null" >
        EndDate = #{enddate,jdbcType=DATE},
      </if>
      <if test="award != null" >
        Award = #{award,jdbcType=VARCHAR},
      </if>
      <if test="certtype != null" >
        CertType = #{certtype,jdbcType=VARCHAR},
      </if>
      <if test="ifvalid != null" >
        IfValid = #{ifvalid,jdbcType=TINYINT},
      </if>
      <if test="syscreated != null" >
        SysCreated = now(),
      </if>
      <if test="syscreatedby != null" >
        SysCreatedby = #{syscreatedby,jdbcType=VARCHAR},
      </if>
      <if test="sysorg != null" >
        SysOrg = #{sysorg,jdbcType=VARCHAR},
      </if>
      <if test="sysdept != null" >
        SysDept = #{sysdept,jdbcType=VARCHAR},
      </if>
      <if test="syslastupd != null" >
        SysLastUpd = now(),
      </if>
      <if test="syslastupdby != null" >
        SysLastUpdBy = #{syslastupdby,jdbcType=VARCHAR},
      </if>
    </set>
    where CertificateId = #{certificateid,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.mgear.samering.domain.Bascomcert" >
    update bas_comcert
    set CompanyId = #{companyid,jdbcType=VARCHAR},
      SerailNumber = #{serailnumber,jdbcType=INTEGER},
      Owner = #{owner,jdbcType=VARCHAR},
      CertSerialNumber = #{certserialnumber,jdbcType=VARCHAR},
      PwdSerialNumber = #{pwdserialnumber,jdbcType=VARCHAR},
      UniqueIdentifier = #{uniqueidentifier,jdbcType=VARCHAR},
      KeySerialNumber = #{keyserialnumber,jdbcType=VARCHAR},
      StartDate = #{startdate,jdbcType=DATE},
      EndDate = #{enddate,jdbcType=DATE},
      Award = #{award,jdbcType=VARCHAR},
      CertType = #{certtype,jdbcType=VARCHAR},
      IfValid = #{ifvalid,jdbcType=TINYINT},
      SysCreated = now(),
      SysCreatedby = #{syscreatedby,jdbcType=VARCHAR},
      SysOrg = #{sysorg,jdbcType=VARCHAR},
      SysDept = #{sysdept,jdbcType=VARCHAR},
      SysLastUpd = now(),
      SysLastUpdBy = #{syslastupdby,jdbcType=VARCHAR}
    where CertificateId = #{certificateid,jdbcType=VARCHAR}
  </update>
  
  <!-- 保存签名证书 -->
  <insert id="save" parameterType="java.util.Map">
  	insert into bas_comcert (CertificateId, CompanyId, SerailNumber, 
      Owner, CertSerialNumber, PwdSerialNumber, 
      UniqueIdentifier, KeySerialNumber, StartDate, 
      EndDate, Award, CertType, SysCreated, SysLastUpd)
    values (#{CertificateId,jdbcType=VARCHAR}, #{CompanyId,jdbcType=VARCHAR}, 
	    (
	    select IF(max(SerailNumber) IS NULL,1,max(SerailNumber)+1) from bas_comcert temp where CompanyId = #{CompanyId,jdbcType=VARCHAR}
	    ), 
      #{Owner,jdbcType=VARCHAR}, #{CertSerialNumber,jdbcType=VARCHAR}, #{PwdSerialNumber,jdbcType=VARCHAR}, 
      #{UniqueIdentifier,jdbcType=VARCHAR}, #{KeySerialNumber,jdbcType=VARCHAR}, #{StartDate,jdbcType=DATE}, 
      #{EndDate,jdbcType=DATE}, #{Award,jdbcType=VARCHAR}, #{CertType,jdbcType=VARCHAR}, NOW(), NOW())
  </insert>
  
  <!-- 获取数字证书列表 -->
  <select id="getList" parameterType="map" resultType="map">
  	SELECT
	temp.*,@i :=@i + 1 SerialNumber
	FROM
		(
			SELECT
				a.CertificateId,
				a.CompanyId,
				a.CertSerialNumber,
				DATE_FORMAT(a.StartDate, '%Y-%m-%d') StartDate,
				DATE_FORMAT(a.EndDate, '%Y-%m-%d') EndDate,
				b.CompanyName
			FROM
				Bas_ComCert a
			JOIN Bas_Company b ON a.CompanyId = b.CompanyId 
			WHERE 1=1 AND a.IfValid = 1 
			<if test="CompanyName != null">
				AND CompanyName LIKE CONCAT('%',#{CompanyName,jdbcType=VARCHAR},'%')
			</if>
			<if test="CertSerialNumber != null">
				AND CertSerialNumber= #{CertSerialNumber,jdbcType=VARCHAR}
			</if>
			ORDER BY
				a.SysCreated DESC
		) temp
	JOIN (SELECT @i := 0) ct
  </select>
  
  <!-- 获取数字证书详细 -->
  <select id="getInfo" parameterType="map" resultType="map">
  	select a.CertificateId,a.CompanyId,a.`Owner`,a.CertSerialNumber,a.PwdSerialNumber,a.UniqueIdentifier,a.KeySerialNumber,DATE_FORMAT(a.StartDate,'%Y-%m-%d') StartDate,DATE_FORMAT(a.EndDate,'%Y-%m-%d') EndDate,
	a.Award,a.CertType,a.IfValid,b.CompanyName from Bas_ComCert a join Bas_Company b on a.CompanyId = b.CompanyId where 1 = 1 
	<if test="CertSerialNumber != null">
		AND CertSerialNumber= #{CertSerialNumber,jdbcType=VARCHAR}
	</if>
	<if test="CertificateId != null">
		AND CertificateId = #{CertificateId,jdbcType=VARCHAR}
	</if>
	<if test="UniqueIdentifier != null">
		AND UniqueIdentifier = #{UniqueIdentifier,jdbcType=VARCHAR}
	</if>
  </select>

</mapper>
第二段： 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mgear.samering.dao.BastemplateMapper" >
  <resultMap id="BaseResultMap" type="com.mgear.samering.domain.Bastemplate" >
    <id column="TemplateId" property="templateid" jdbcType="VARCHAR" />
    <result column="TemplateName" property="templatename" jdbcType="VARCHAR" />
    <result column="SysCreated" property="syscreated" jdbcType="TIMESTAMP" />
    <result column="SysCreatedby" property="syscreatedby" jdbcType="VARCHAR" />
    <result column="SysOrg" property="sysorg" jdbcType="VARCHAR" />
    <result column="SysDept" property="sysdept" jdbcType="VARCHAR" />
    <result column="SysLastUpd" property="syslastupd" jdbcType="TIMESTAMP" />
    <result column="SysLastUpdBy" property="syslastupdby" jdbcType="VARCHAR" />
  </resultMap>
  <resultMap id="ResultMapWithBLOBs" type="com.mgear.samering.domain.Bastemplate" extends="BaseResultMap" >
    <result column="TemplateStyle" property="templatestyle" jdbcType="LONGVARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    TemplateId, TemplateName, SysCreated, SysCreatedby, SysOrg, SysDept, SysLastUpd, 
    SysLastUpdBy
  </sql>
  <sql id="Blob_Column_List" >
    TemplateStyle
  </sql>
  <select id="selectByPrimaryKey" resultMap="ResultMapWithBLOBs" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    ,
    <include refid="Blob_Column_List" />
    from bas_template
    where TemplateId = #{templateid,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    delete from bas_template
    where TemplateId = #{templateid,jdbcType=VARCHAR}
  </delete>
  <insert id="insert" parameterType="com.mgear.samering.domain.Bastemplate" >
    insert into bas_template (TemplateId, TemplateName, SysCreated, 
      SysCreatedby, SysOrg, SysDept, 
      SysLastUpd, SysLastUpdBy, TemplateStyle
      )
    values (#{templateid,jdbcType=VARCHAR}, #{templatename,jdbcType=VARCHAR}, now(), 
      #{syscreatedby,jdbcType=VARCHAR}, #{sysorg,jdbcType=VARCHAR}, #{sysdept,jdbcType=VARCHAR}, 
      now(), #{syslastupdby,jdbcType=VARCHAR}, #{templatestyle,jdbcType=LONGVARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.mgear.samering.domain.Bastemplate" >
    insert into bas_template
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="templateid != null" >
        TemplateId,
      </if>
      <if test="templatename != null" >
        TemplateName,
      </if>
      <if test="syscreated != null" >
        SysCreated,
      </if>
      <if test="syscreatedby != null" >
        SysCreatedby,
      </if>
      <if test="sysorg != null" >
        SysOrg,
      </if>
      <if test="sysdept != null" >
        SysDept,
      </if>
      <if test="syslastupd != null" >
        SysLastUpd,
      </if>
      <if test="syslastupdby != null" >
        SysLastUpdBy,
      </if>
      <if test="templatestyle != null" >
        TemplateStyle,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="templateid != null" >
        #{templateid,jdbcType=VARCHAR},
      </if>
      <if test="templatename != null" >
        #{templatename,jdbcType=VARCHAR},
      </if>
      <if test="syscreated != null" >
        now(),
      </if>
      <if test="syscreatedby != null" >
        #{syscreatedby,jdbcType=VARCHAR},
      </if>
      <if test="sysorg != null" >
        #{sysorg,jdbcType=VARCHAR},
      </if>
      <if test="sysdept != null" >
        #{sysdept,jdbcType=VARCHAR},
      </if>
      <if test="syslastupd != null" >
        now(),
      </if>
      <if test="syslastupdby != null" >
        #{syslastupdby,jdbcType=VARCHAR},
      </if>
      <if test="templatestyle != null" >
        #{templatestyle,jdbcType=LONGVARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.mgear.samering.domain.Bastemplate" >
    update bas_template
    <set >
      <if test="templatename != null" >
        TemplateName = #{templatename,jdbcType=VARCHAR},
      </if>
      <if test="syscreated != null" >
        SysCreated = now(),
      </if>
      <if test="syscreatedby != null" >
        SysCreatedby = #{syscreatedby,jdbcType=VARCHAR},
      </if>
      <if test="sysorg != null" >
        SysOrg = #{sysorg,jdbcType=VARCHAR},
      </if>
      <if test="sysdept != null" >
        SysDept = #{sysdept,jdbcType=VARCHAR},
      </if>
      <if test="syslastupd != null" >
        SysLastUpd = now(),
      </if>
      <if test="syslastupdby != null" >
        SysLastUpdBy = #{syslastupdby,jdbcType=VARCHAR},
      </if>
      <if test="templatestyle != null" >
        TemplateStyle = #{templatestyle,jdbcType=LONGVARCHAR},
      </if>
    </set>
    where TemplateId = #{templateid,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKeyWithBLOBs" parameterType="com.mgear.samering.domain.Bastemplate" >
    update bas_template
    set TemplateName = #{templatename,jdbcType=VARCHAR},
      SysCreated = now(),
      SysCreatedby = #{syscreatedby,jdbcType=VARCHAR},
      SysOrg = #{sysorg,jdbcType=VARCHAR},
      SysDept = #{sysdept,jdbcType=VARCHAR},
      SysLastUpd = now(),
      SysLastUpdBy = #{syslastupdby,jdbcType=VARCHAR},
      TemplateStyle = #{templatestyle,jdbcType=LONGVARCHAR}
    where TemplateId = #{templateid,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.mgear.samering.domain.Bastemplate" >
    update bas_template
    set TemplateName = #{templatename,jdbcType=VARCHAR},
      SysCreated = now(),
      SysCreatedby = #{syscreatedby,jdbcType=VARCHAR},
      SysOrg = #{sysorg,jdbcType=VARCHAR},
      SysDept = #{sysdept,jdbcType=VARCHAR},
      SysLastUpd = now(),
      SysLastUpdBy = #{syslastupdby,jdbcType=VARCHAR}
    where TemplateId = #{templateid,jdbcType=VARCHAR}
  </update>
  
  <!-- 获取借款合同模板详细 -->
  <select id="getInfo" resultType="java.util.Map" parameterType="java.lang.String">
  	SELECT
		TemplateId,
		TemplateName,
		TemplateStyle
	FROM
		bas_template
	WHERE 
		TemplateId= #{templateid,jdbcType=VARCHAR}
  </select>
  
  <!-- 获取借款合同模板列表 -->
  <select id="getList" resultType="java.util.Map" parameterType="java.util.Map">
  	SELECT
		temp.*,@i :=@i + 1 SerialNumber
	FROM
		(
			SELECT
				TemplateId,
				TemplateName
			FROM
				bas_template
			WHERE
				1 = 1 	
				<if test="TemplateName != null">
						AND TemplateName LIKE CONCAT('%',#{TemplateName,jdbcType=VARCHAR},'%')
				</if>
			ORDER BY
				CONVERT (TemplateName USING gbk) COLLATE gbk_chinese_ci
		) temp
	JOIN (SELECT @i := 0) ct
  </select>
  
  <!--  获取供应链合同模板详细 -->
  <select id="getChainInfo" resultType="java.util.Map" parameterType="java.lang.String">
  	SELECT
		TemplateId,
		TemplateName,
		TemplateStyle
	FROM
		bas_template a
	JOIN bas_company b ON a.TemplateId = b.ChainTemplateId
	WHERE
		IfCancel = 0
		AND CompanyId = #{CompanyId,jdbcType=VARCHAR}
  </select>
  
  <!--  获取内部贷合同模板详细 -->
  <select id="getLoanInfo" resultType="java.util.Map" parameterType="java.lang.String">
  	SELECT
		TemplateId,
		TemplateName,
		TemplateStyle
	FROM
		bas_template a
	JOIN bas_company b ON a.TemplateId = b.LoanTemplateId
	WHERE
		IfCancel = 0
		AND CompanyId = #{CompanyId,jdbcType=VARCHAR}
  </select>
  
  <!-- 获取供应链 主合同 数据 -->
  <select id="getConCompanyInfo" resultType="java.util.Map" parameterType="java.lang.String">
  	SELECT
  		a.BidId BidId,
  		a.BidNumber contract_number,
  		DATE_FORMAT(a.FullAuditTime,'%Y-%m-%d') contract_signTime,

		c.CompanyName contract_PartyAName,
		CONCAT(func_getarea(c.AreaCode),c.Address) contract_PartyAAddress,
		IFNULL(e.NameAndId,'') contract_PartyBName,
		b.CompanyName contract_PartyCName,
		CONCAT(func_getarea(b.AreaCode),b.Address) contract_PartyCAddress,

    	a.FundUse contract_PartyReson,
		a.FinancingAmount contract_PartyMoey,
		CAST(a.AnnualRate*100/12 AS DECIMAL(3,2)) contract_PartyAuu,
		DATE_FORMAT(a.FullAuditTime,'%Y-%m-%d') contract_PartyStartDate,
    	DATE_FORMAT(DATE_ADD(a.FullAuditTime,INTERVAL IF(a.TimeLimitUnit=0,a.TimeLimit-1,a.TimeLimit*30-1) DAY),'%Y-%m-%d') contract_PartyEndDate,
    	IF(a.TimeLimitUnit=0,a.TimeLimit,a.TimeLimit*30) contract_PartyNeDate,

		IFNULL(c.Postalcode,'') contract_PartyAPostalcode,
		IFNULL(c.Email,'') contract_PartyAEmail,
		IFNULL(c.MobilePhone,'') contract_PartyAMobilePhone,
		IFNULL(c.OfficePhone,'') contract_PartyAOfficePhone,

		IFNULL(e.PhoneAndEmail,'') contract_PartyBMobilePhone,

		IFNULL(b.Postalcode,'') contract_PartyCPostalcode,
		IFNULL(b.Email,'') contract_PartyCEmail,
		b.MobilePhone contract_PartyCMobilePhone,
		IFNULL(b.OfficePhone,'') contract_PartyCOfficePhone,
	
		CONCAT(b.CompanyName,'（本合同已使用数字证书签名）') contract_PartyMName,
		CONCAT(c.CompanyName,'（本合同已使用数字证书签名）') contract_PartyNName,
		DATE_FORMAT(NOW(),'%Y年%m月%d日') contract_PartyYTime,
		
		IFNULL(CONCAT(t1.FolderName,'/',t1.FullName),'') SignA,
		IFNULL(CONCAT(t2.FolderName,'/',t2.FullName),'') SignC
	FROM
		bis_bid a 
	  	join bas_company b ON a.EpsCompanyId = b.CompanyId
		join bas_company c ON a.BrwCompanyId =  c.CompanyId
	  	left join bis_bidcontract d on d.ContractId = a.Bidid
	  	join viw_getInvestUserList e on e.Bidid = a.Bidid
	  	left join pub_content t1 ON t1.ContentId = c.SignatureContentId
		left join pub_content t2 ON t2.ContentId = b.SignatureContentId
	WHERE 
	  	a.BidId = #{BidId,jdbcType=VARCHAR}
  </select>
  
  
  <!-- 获取供应链 子合同 数据 -->
  <select id="getConPersonalInfo" resultType="java.util.Map" parameterType="java.lang.String">
  	SELECT
  		e.InvestId InvestId,
  		f.PersonalId PersonalId,
  		IFNULL(e.InvestNumber,'') contract_number,
  		DATE_FORMAT(e.InvestTime,'%Y-%m-%d') contract_signTime,
  	    
		c.CompanyName contract_PartyAName,
		CONCAT(func_getarea(c.AreaCode),c.Address) contract_PartyAAddress,
		CONCAT(IFNULL(f.Name,''),CONCAT('【',f.IdNumber,'】')) contract_PartyBName,
		b.CompanyName contract_PartyCName,
		CONCAT(func_getarea(b.AreaCode),b.Address) contract_PartyCAddress,

		a.FundUse contract_PartyReson,
		e.InvestAmount contract_PartyMoey,
		CAST(a.AnnualRate*100/12 AS DECIMAL(3,2)) contract_PartyAuu,
		DATE_FORMAT(a.FullAuditTime,'%Y-%m-%d') contract_PartyStartDate,
    	DATE_FORMAT(DATE_ADD(a.FullAuditTime,INTERVAL IF(a.TimeLimitUnit=0,a.TimeLimit-1,a.TimeLimit*30-1) DAY),'%Y-%m-%d') contract_PartyEndDate,
    	IF(a.TimeLimitUnit=0,a.TimeLimit,a.TimeLimit*30) contract_PartyNeDate,
		
		IFNULL(c.Postalcode,'') contract_PartyAPostalcode,
		IFNULL(c.Email,'') contract_PartyAEmail,
		IFNULL(c.MobilePhone,'') contract_PartyAMobilePhone,
		IFNULL(c.OfficePhone,'') contract_PartyAOfficePhone,
		
		IFNULL(CONCAT(f.MobilePhone,CONCAT('【',f.Email,'】')),'') contract_PartyBMobilePhone,
		IFNULL(f.Email,'') contract_PartyBEmail,

		IFNULL(b.Postalcode,'') contract_PartyCPostalcode ,
		IFNULL(b.Email,'') contract_PartyCEmail ,
		IFNULL(b.MobilePhone,'') contract_PartyCMobilePhone ,
		IFNULL(b.OfficePhone,'') contract_PartyCOfficePhone ,

		CONCAT(b.CompanyName,'（本合同已使用数字证书签名）') contract_PartyMName,
		CONCAT(c.CompanyName,'（本合同已使用数字证书签名）') contract_PartyNName,
		DATE_FORMAT(NOW(),'%Y年%m月%d日') contract_PartyYTime,
		
		IFNULL(CONCAT(t1.FolderName,'/',t1.FullName),'') SignA,
		IFNULL(CONCAT(t2.FolderName,'/',t2.FullName),'') SignC
	
	FROM
		bis_bid a 
	  	join bas_company b ON a.EpsCompanyId = b.CompanyId
		join bas_company c ON a.BrwCompanyId =  c.CompanyId
	  	left join bis_bidcontract d on d.ContractId = a.Bidid
	  	join bis_bidinvest e on e.Bidid = a.Bidid
		join bas_personal f on f.PersonalId  = e.PersonalId
		left join pub_content t1 ON t1.ContentId = c.SignatureContentId
		left join pub_content t2 ON t2.ContentId = b.SignatureContentId	
	WHERE 
		e.InvestState=1
 		and a.BidId = #{BidId,jdbcType=VARCHAR}
  </select>

 <!-- 获取内部贷 主合同 数据 -->
  <select id="getRingMainContractData" resultType="java.util.Map" parameterType="java.lang.String">
	SELECT
  		a.BidId BidId,
  		a.BidNumber contract_number,
  		DATE_FORMAT(a.FullAuditTime,'%Y-%m-%d') contract_signTime,

		c.CompanyName contract_PartyAName,
		CONCAT(func_getarea(c.AreaCode),c.Address) contract_PartyAAddress,
		IFNULL(e.NameAndId,'') contract_PartyBName,
		b.CompanyName contract_PartyCName,
		CONCAT(func_getarea(b.AreaCode),b.Address) contract_PartyCAddress,

    	a.FundUse contract_PartyReson,
		a.FinancingAmount contract_PartyMoey,
		CAST(a.AnnualRate*100/12 AS DECIMAL(3,2)) contract_PartyAuu,
		DATE_FORMAT(a.FullAuditTime,'%Y-%m-%d') contract_PartyStartDate,
    	DATE_FORMAT(DATE_ADD(a.FullAuditTime,INTERVAL IF(a.TimeLimitUnit=0,a.TimeLimit-1,a.TimeLimit*30-1) DAY),'%Y-%m-%d') contract_PartyEndDate,
    	IF(a.TimeLimitUnit=0,a.TimeLimit,a.TimeLimit*30) contract_PartyNeDate,

		IFNULL(c.Postalcode,'') contract_PartyAPostalcode,
		IFNULL(c.Email,'') contract_PartyAEmail,
		IFNULL(c.MobilePhone,'') contract_PartyAMobilePhone,
		IFNULL(c.OfficePhone,'') contract_PartyAOfficePhone,

		IFNULL(e.PhoneAndEmail,'') contract_PartyBMobilePhone,

		IFNULL(b.Postalcode,'') contract_PartyCPostalcode,
		IFNULL(b.Email,'') contract_PartyCEmail,
		b.MobilePhone contract_PartyCMobilePhone,
		IFNULL(b.OfficePhone,'') contract_PartyCOfficePhone,
	
		CONCAT(b.CompanyName,'（本合同已使用数字证书签名）') contract_PartyMName,
		CONCAT(c.CompanyName,'（本合同已使用数字证书签名）') contract_PartyNName,
		DATE_FORMAT(NOW(),'%Y年%m月%d日') contract_PartyYTime,
		a.RepayMethodCode,
		b.CompanyName PartyName,
		
		IFNULL(CONCAT(t1.FolderName,'/',t1.FullName),'') SignA,
		IFNULL(CONCAT(t2.FolderName,'/',t2.FullName),'') SignC
	FROM
		bis_bid a 
	  	join bas_company b ON a.EpsCompanyId = b.CompanyId
		join bas_company c ON a.BrwCompanyId =  c.CompanyId
	  	left join bis_bidcontract d on d.ContractId = a.Bidid
	  	join viw_getInvestUserList e on e.Bidid = a.Bidid
	  	left join pub_content t1 ON t1.ContentId = c.SignatureContentId
		left join pub_content t2 ON t2.ContentId = b.SignatureContentId	
	WHERE
		a.BidType=1
 		and a.BidId = #{BidId,jdbcType=VARCHAR}
  </select>
  
  <!-- 获取内部贷 子合同 数据 -->
  <select id="getRingContractData" resultType="java.util.Map" parameterType="java.lang.String">
	SELECT
  		e.InvestId InvestId,
  		f.PersonalId PersonalId,
  		IFNULL(e.InvestNumber,'') contract_number,
  		DATE_FORMAT(e.InvestTime,'%Y-%m-%d') contract_signTime,
  	    
		c.CompanyName contract_PartyAName,
		CONCAT(func_getarea(c.AreaCode),c.Address) contract_PartyAAddress,
		CONCAT(IFNULL(f.Name,''),CONCAT('【',f.IdNumber,'】')) contract_PartyBName,
		b.CompanyName contract_PartyCName,
		CONCAT(func_getarea(b.AreaCode),b.Address) contract_PartyCAddress,

		a.FundUse contract_PartyReson,
		e.InvestAmount contract_PartyMoey,
		CAST(a.AnnualRate*100/12 AS DECIMAL(3,2)) contract_PartyAuu,
		DATE_FORMAT(a.FullAuditTime,'%Y-%m-%d') contract_PartyStartDate,
    	DATE_FORMAT(DATE_ADD(a.FullAuditTime,INTERVAL IF(a.TimeLimitUnit=0,a.TimeLimit-1,a.TimeLimit*30-1) DAY),'%Y-%m-%d') contract_PartyEndDate,
    	IF(a.TimeLimitUnit=0,a.TimeLimit,a.TimeLimit*30) contract_PartyNeDate,
		
		IFNULL(c.Postalcode,'') contract_PartyAPostalcode,
		IFNULL(c.Email,'') contract_PartyAEmail,
		IFNULL(c.MobilePhone,'') contract_PartyAMobilePhone,
		IFNULL(c.OfficePhone,'') contract_PartyAOfficePhone,
		
		IFNULL(CONCAT(f.MobilePhone,CONCAT('【',f.Email,'】')),'') contract_PartyBMobilePhone,
		IFNULL(f.Email,'') contract_PartyBEmail,

		IFNULL(b.Postalcode,'') contract_PartyCPostalcode ,
		IFNULL(b.Email,'') contract_PartyCEmail ,
		IFNULL(b.MobilePhone,'') contract_PartyCMobilePhone ,
		IFNULL(b.OfficePhone,'') contract_PartyCOfficePhone ,

		CONCAT(b.CompanyName,'（本合同已使用数字证书签名）') contract_PartyMName,
		CONCAT(c.CompanyName,'（本合同已使用数字证书签名）') contract_PartyNName,
		DATE_FORMAT(NOW(),'%Y年%m月%d日') contract_PartyYTime,
		a.RepayMethodCode,
		b.CompanyName PartyName,
		IFNULL(CONCAT(t1.FolderName,'/',t1.FullName),'') SignA,
		IFNULL(CONCAT(t2.FolderName,'/',t2.FullName),'') SignC
	FROM
		bis_bid a 
	  	join bas_company b ON a.EpsCompanyId = b.CompanyId
		join bas_company c ON a.BrwCompanyId =  c.CompanyId
	  	left join bis_bidcontract d on d.ContractId = a.Bidid
	  	join bis_bidinvest e on e.Bidid = a.Bidid
		join bas_personal f on f.PersonalId  = e.PersonalId
		left join pub_content t1 ON t1.ContentId = c.SignatureContentId
		left join pub_content t2 ON t2.ContentId = b.SignatureContentId	
	WHERE 
		e.InvestState=1
		and a.BidType=1
 		and a.BidId = #{BidId,jdbcType=VARCHAR}
  </select>
</mapper>



文章标签：
SQL
Java
mybatis
数据库连接
供应链
关键词：
SQL应用
SQL mybatis
MyBatis sql
MyBatis应用
mybatis SQL应用
程序界小强
目录
相关文章
飞川撸码
|
6天前
|
SQL Java 关系型数据库
Mybatis多表关联查询与动态SQL（下）
Mybatis多表关联查询与动态SQL
飞川撸码
18 0
飞川撸码
|
6天前
|
SQL Java 数据库连接
Mybatis多表关联查询与动态SQL（上）
Mybatis多表关联查询与动态SQL
飞川撸码
10 0
添馨科技
|
1天前
|
SQL 关系型数据库 MySQL
SQL基础开发与应用-课程及场景介绍
这是一门关于《SQL基础开发与应用》的课程介绍，主要针对数据库Clouder认证的第二阶段。课程以电商平台后端开发为背景，教授RDS for MySQL的SQL基础知识，包括存储过程、触发器和视图等高级特性，并指导学员使用Python进行数据库的增删改查操作。学习目标包括掌握SQL基础操作，了解RDS的高阶功能，并熟悉Python连接RDS进行数据处理。课程采用场景化教学，以跨境电商网站数据库搭建为例，帮助学员理解实际应用。
添馨科技
9 0
s:103
|
6天前
|
SQL Java 数据库连接
【JavaEE】懒人的福音-MyBatis框架—复杂的操作-动态SQL（下）
【JavaEE】懒人的福音-MyBatis框架—复杂的操作-动态
s:103
6 0
s:103
|
6天前
|
SQL Java 数据库连接
【JavaEE】懒人的福音-MyBatis框架—复杂的操作-动态SQL（上）
【JavaEE】懒人的福音-MyBatis框架—复杂的操作-动态SQL
s:103
6 0
1529723250799936
|
6天前
|
SQL Java 数据库连接
mybatis动态sql
mybatis动态sql
1529723250799936
13 2
qiuqiushuibx
|
6天前
|
SQL Java 数据库连接
MyBatis #与$的区别以及动态SQL
MyBatis #与$的区别以及动态SQL
qiuqiushuibx
14 0
渐暖
|
6天前
|
SQL Java 数据库连接
【mybatis】动态sql之批量增删改查
【mybatis】动态sql之批量增删改查
渐暖
13 0
游客kpg7vddhfmo3k
|
SQL 存储 缓存
一文搞懂MySQL中一条SQL语句是如何执行的
一文搞懂MySQL中一条SQL语句是如何执行的
游客kpg7vddhfmo3k
360 0
游客yno6xv7miabmw
|
SQL 关系型数据库 MySQL
MySQL SQL语句给当前日期加一天和减一天
MySQL SQL语句给当前日期加一天和减一天
游客yno6xv7miabmw
4048 0

热门文章

最新文章

  • 1
    一文解析 ODPS SQL 任务优化方法原理
  • 2
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
  • 3
    IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
  • 4
    NL2SQL进阶系列(4)：ConvAI、DIN-SQL等16个业界开源应用实践详解[Text2SQL]
  • 5
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 6
    【MySQL】SQL优化
  • 7
    sql 数据库优化
  • 8
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
  • 9
    Oracle的PL/SQL表达式：数据的魔法公式
  • 10
    Oracle的PL/SQL循环语句：数据的“旋转木马”与“无限之旅”
  • 1
    SpringBoot整合Mybatis连接Oracle数据库
    84
  • 2
    Mybatis-Plus学习小项目及详细教程
    40
  • 3
    Mybatis查询的时候BigDecimal类型的值查询失效的解决办法
    91
  • 4
    【SpringBoot系列】SpringBoot集成Fast Mybatis
    183
  • 5
    Mybatis拦截器实现带参数SQL语句打印
    49
  • 6
    Mybatis中Mapper.xml 文件使用注释遇到的坑
    21
  • 7
    MyBatis Plus 解决大数据量查询慢问题
    69
  • 8
    Mybatis-Flex框架初体验
    338
  • 9
    【Mybatis】XML映射文件
    30
  • 10
    【MyBatis】主配置文件
    30

    • 相关课程

    更多
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询
  • MyBatis持久层框架入门

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • SQL Server 2017
  • Java Spring Boot开发实战系列课程【第6讲】：Spring Boot 2.0实战MyBatis与优化(Java面试题)

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考