记录mybatis的sql语句应用

最新推荐文章于 2020-09-23 08:51:31 发布
最新推荐文章于 2020-09-23 08:51:31 发布
阅读量1.7k 收藏
点赞数
分类专栏： Mybatis 文章标签： mybatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/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>



JAVA UUID 生成(1)
2401_84046545的博客
04-14 1056
一次偶然，从朋友那里得到一份“java高分面试指南”，里面涵盖了25个分类的面试题以及详细的解析：JavaOOP、Java集合/泛型、Java中的IO与NIO、Java反射、Java序列化、Java注解、多线程&并发、JVM、Mysql、Redis、Memcached、MongoDB、Spring、Spring Boot、Spring Cloud、RabbitMQ、Dubbo 、MyBatis 、ZooKeeper 、数据结构、算法、Elasticsearch 、Kafka 、微服务、Linux。
sql语句整理工具
03-31
很实用的sql语句整理工具，把杂乱的语句变得规范和易读。
Mybatis中的动态SQL记录
wh柒八九的博客
09-23 174
if判断 <mapper namespace="cn.wideth.mapper.CarMapper"> <select id="selectByCarInfo" resultType="cn.wideth.entity.table.CarInfo" parameterType="cn.wideth.entity.entrance.QueryCar"> SELECT A.DISTRICTNAME,T.*,B.DICTNAME FROM (T_TI_CAR_
数据库工具sql语句记录
gaoke966的专栏
06-28 132
--查看sqlserver 2005端口号 exec sys.sp_readerrorlog 0, 1, 'listening'
P6Spy sql语句记录器
qq_25072517的博客
11-30 266
P6Spy是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架。 通过P6Spy我们可以对SQL语句进行拦截，相当于一个SQL语句的记录器，这样我们可以用它来作相关的分析，比如性能分析。 P6SPY提供了如下几个功能： 记录SQL语句的执行时间戳。  记录SQL语句类型  记录SQL填入参数的和没有填入参数的SQL语句  根据配置的时间控制SQL语句的执行时间，对超出时间的
在mybatis执行SQL语句之前进行拦击处理实例
08-30
MyBatis拦截器实践 - 在MyBatis执行SQL语句之前进行拦截处理实例 MyBatis作为一个流行的持久层框架，提供了强大的数据库交互能力，但是在某些特殊场景下，我们需要在MyBatis执行SQL语句之前进行拦截处理，这便需要...
mybatis动态SQL语句
04-28
if 、where、set、trim、choose 、foreach等在mybatis中的具体用法，有具体实例可供参考，玩转mybatis
Mybatis日志SQL解析工具
03-22
该工具可以将mybatis输出的sql日志提取出来，并将其格式化为可以直接执行的sql语句，节约开发人员时间
在IDEA中安装MyBatis Log Plugin插件,执行mybatis的sql语句（推荐）
08-18
3. 当你的应用执行到含有MyBatis SQL的地方时，点击`Tools` -> `MyBatis Log Plugin`，此时插件会自动收集并展示每一步执行的SQL语句，包括动态SQL的完整形式。 通过MyBatis Log Plugin，你可以得到以下关键信息： ...
mybatis-log-plugin:将Mybatis SQL日志还原到原始的整个可执行SQL
05-12
还原MyBatis输出的日志为完整的SQL语句。 把SQL日志里面的?替换为真正的参数值。 选中要还原的MyBatis日志，右键点击菜单Restore Sql，还原SQL语句. Java接口方法与Mapper xml文件互相跳转。 按钮作用 Text: 从文本...
sql跟踪语句整理工具
11-24
SQL跟踪后，用该工具能够快速整理出整洁的SQL，减少人工的复制、帖贴
创建 uniqueidentifier 类型的唯一值
06-17
下面的示例使用 NEWID 对声明为 uniqueidentifier 数据类型的变量赋值。在测试该值前，将先打印 uniqueidentifier 数据类型变量的值。
SQLServer2005中的uniqueidentifier数据类型与NEWID（）函数
weixin_30512785的博客
03-07 524
uniqueidentifier中文含义“唯一的标识符”。 uniqueidentifier数据类型是16个字节的二进制值，应具有唯一性，必须与NEWID（）函数配合使用。 uniqueidentifier数据类型与identity自增不同，不会为插入的新行自动生成新的ID，新值由NEWID（）函数指定。 NEWID（）函数值会生成全球唯一的标识，标识由网卡号和CPU时钟组成，如：6F961...
记录一次mybatis动态新增拼接sql语句出现的异常(主要是数据库SQL Server与jdbcType数据类型之间的对应关系)
hello world！
12-21 1494
源码如下: 因为前端页面并不是所有的项均为必填项, 故insert 新增sql语句需动态拼接, 刚开始通过tomcat后台发现报如下错误:   经过查阅mybaits官网才知道以下事实: 1、mybatis insert语句中 jdbcType  后面跟的类型字母必须全部都大写; 2、JdbcType所拥有的数据类型和Java类型以及SQL Server语言所拥有的数据类型存...
Mybatis与SQL Server类型转换遇到的坑
b55000458515493的博客
09-01 1169
一. MyBatis SQL语句遇到的性能问题 1. 场景还原 　　假设我们有一张User表，其中包含userId、userName、gender字段，其中userId的数据类型为char(20)，此时我们想通过userId获得这个人的姓名， 　　这段SQL很简单： SELECT userName FROM dbo.User (nolock) WHERE userId = '100...
通用 Mapper UUID 简单示例
热门推荐
偶尔记一下 - mybatis.io
11-02 2万+
通用 Mapper UUID 简单示例不可回写的 UUID通用 Mapper 中对 UUID 的用法主要提到了一种专有的写法，如下写法：@GeneratedValue(generator = "UUID")这种方式实现很容易理解，就是在你 insert 之前，调用 UUID 的公共方法在 <bind> 标签中生成了一个值，插入到了数据库，由于这个值是临时的，并没有 set 到对象，因此这种方式是不支
Mybatis-plus自动生成mysql的UUID AUTO
掘金者说
03-29 1万+
/** * ID */ @TableId(type = IdType.UUID) private String id; /** * 编号 */ @TableId(value = "id", type = IdType.AUTO) private Long id; /** * 所属系统 */ @TableField("`group`") private String gr...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值