HN代码书写规范实例
WpBggimapper.xml

<?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.zhetang.dao.bggl.WpBgglMapper" > <resultMap id="BaseResultMap" type="com.zhetang.model.bggl.WpBggl" > <id column="id" property="id" jdbcType="CHAR" /> <result column="jbxx_bgszbm" property="jbxxBgszbm" jdbcType="VARCHAR" /> <result column="jbxxx_bgr" property="jbxxxBgr" jdbcType="VARCHAR" /> <result column="jbxx_bgzxbm" property="jbxxBgzxbm" jdbcType="VARCHAR" /> <result column="jbxx_bgzxr" property="jbxxBgzxr" jdbcType="VARCHAR" /> <result column="jbxx_bgsj_start" property="jbxxBgsjStart" jdbcType="TIMESTAMP" /> <result column="jbxx_bgsj_end" property="jbxxBgsjEnd" jdbcType="TIMESTAMP" /> <result column="jbxx_sqr" property="jbxxSqr" jdbcType="VARCHAR" /> <result column="jbxx_jyms" property="jbxxJyms" jdbcType="VARCHAR" /> <result column="jbxx_bgyj" property="jbxxBgyj" jdbcType="VARCHAR" /> <result column="fxfxjkz" property="fxfxjkz" jdbcType="VARCHAR" /> <result column="jbxx_djrq" property="jbxxDjrq" jdbcType="TIMESTAMP" /> <result column="jbxx_fj" property="jbxxFj" jdbcType="VARCHAR" /> <result column="jbxx_fjmz" property="jbxxFjmz" jdbcType="VARCHAR" /> <result column="jbxx_status" property="jbxxStatus" jdbcType="VARCHAR" /> <result column="lsgz_sjkssj" property="lsgzSjkssj" jdbcType="TIMESTAMP" /> <result column="lsgz_sjwcsj" property="lsgzSjwcsj" jdbcType="TIMESTAMP" /> <result column="lsgz_fj" property="lsgzFj" jdbcType="VARCHAR" /> <result column="lsgz_fjmz" property="lsgzFjmz" jdbcType="VARCHAR" /> <result column="lsgz_wcqksm" property="lsgzWcqksm" jdbcType="VARCHAR" /> <result column="cjsj" property="cjsj" jdbcType="TIMESTAMP" /> <result column="cjr" property="cjr" jdbcType="VARCHAR" /> <result column="xgsj" property="xgsj" jdbcType="TIMESTAMP" /> <result column="xgr" property="xgr" jdbcType="VARCHAR" /> <result column="bglb" property="bglb" jdbcType="VARCHAR" /> <result column="is_deleted" property="isDeleted" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List" > id, jbxx_bgszbm, jbxxx_bgr, jbxx_bgzxbm, jbxx_bgzxr, jbxx_bgsj_start, jbxx_bgsj_end, jbxx_sqr, jbxx_jyms, jbxx_bgyj, fxfxjkz, jbxx_djrq, jbxx_fj, jbxx_fjmz, jbxx_status, lsgz_sjkssj, lsgz_sjwcsj, lsgz_fj, lsgz_fjmz, lsgz_wcqksm, cjsj, cjr, xgsj, xgr, bglb, is_deleted </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" > select <include refid="Base_Column_List" /> from wp_bggl where id = #{id,jdbcType=CHAR} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.String" > delete from wp_bggl where id = #{id,jdbcType=CHAR} </delete> <insert id="insert" parameterType="com.zhetang.model.bggl.WpBggl" > insert into wp_bggl (id, jbxx_bgszbm, jbxxx_bgr, jbxx_bgzxbm, jbxx_bgzxr, jbxx_bgsj_start, jbxx_bgsj_end, jbxx_sqr, jbxx_jyms, jbxx_bgyj, fxfxjkz, jbxx_djrq, jbxx_fj, jbxx_fjmz, jbxx_status, lsgz_sjkssj, lsgz_sjwcsj, lsgz_fj, lsgz_fjmz, lsgz_wcqksm, cjsj, cjr, xgsj, xgr, bglb, is_deleted) values (#{id,jdbcType=CHAR}, #{jbxxBgszbm,jdbcType=VARCHAR}, #{jbxxxBgr,jdbcType=VARCHAR}, #{jbxxBgzxbm,jdbcType=VARCHAR}, #{jbxxBgzxr,jdbcType=VARCHAR}, #{jbxxBgsjStart,jdbcType=TIMESTAMP}, #{jbxxBgsjEnd,jdbcType=TIMESTAMP}, #{jbxxSqr,jdbcType=VARCHAR}, #{jbxxJyms,jdbcType=VARCHAR}, #{jbxxBgyj,jdbcType=VARCHAR}, #{fxfxjkz,jdbcType=VARCHAR}, #{jbxxDjrq,jdbcType=TIMESTAMP}, #{jbxxFj,jdbcType=VARCHAR}, #{jbxxFjmz,jdbcType=VARCHAR}, #{jbxxStatus,jdbcType=VARCHAR}, #{lsgzSjkssj,jdbcType=TIMESTAMP}, #{lsgzSjwcsj,jdbcType=TIMESTAMP}, #{lsgzFj,jdbcType=VARCHAR}, #{lsgzFjmz,jdbcType=VARCHAR}, #{lsgzWcqksm,jdbcType=VARCHAR}, #{cjsj,jdbcType=TIMESTAMP}, #{cjr,jdbcType=VARCHAR}, #{xgsj,jdbcType=TIMESTAMP}, #{xgr,jdbcType=VARCHAR}, #{bglb,jdbcType=VARCHAR}, #{isDeleted,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" parameterType="com.zhetang.model.bggl.WpBggl" > insert into wp_bggl <trim prefix="(" suffix=")" suffixOverrides="," > <if test="id != null" > id, </if> <if test="jbxxBgszbm != null" > jbxx_bgszbm, </if> <if test="jbxxxBgr != null" > jbxxx_bgr, </if> <if test="jbxxBgzxbm != null" > jbxx_bgzxbm, </if> <if test="jbxxBgzxr != null" > jbxx_bgzxr, </if> <if test="jbxxBgsjStart != null" > jbxx_bgsj_start, </if> <if test="jbxxBgsjEnd != null" > jbxx_bgsj_end, </if> <if test="jbxxSqr != null" > jbxx_sqr, </if> <if test="jbxxJyms != null" > jbxx_jyms, </if> <if test="jbxxBgyj != null" > jbxx_bgyj, </if> <if test="fxfxjkz != null" > fxfxjkz, </if> <if test="jbxxDjrq != null" > jbxx_djrq, </if> <if test="jbxxFj != null" > jbxx_fj, </if> <if test="jbxxFjmz != null" > jbxx_fjmz, </if> <if test="jbxxStatus != null" > jbxx_status, </if> <if test="lsgzSjkssj != null" > lsgz_sjkssj, </if> <if test="lsgzSjwcsj != null" > lsgz_sjwcsj, </if> <if test="lsgzFj != null" > lsgz_fj, </if> <if test="lsgzFjmz != null" > lsgz_fjmz, </if> <if test="lsgzWcqksm != null" > lsgz_wcqksm, </if> <if test="cjsj != null" > cjsj, </if> <if test="cjr != null" > cjr, </if> <if test="xgsj != null" > xgsj, </if> <if test="xgr != null" > xgr, </if> <if test="bglb != null" > bglb, </if> <if test="isDeleted != null" > is_deleted, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="id != null" > #{id,jdbcType=CHAR}, </if> <if test="jbxxBgszbm != null" > #{jbxxBgszbm,jdbcType=VARCHAR}, </if> <if test="jbxxxBgr != null" > #{jbxxxBgr,jdbcType=VARCHAR}, </if> <if test="jbxxBgzxbm != null" > #{jbxxBgzxbm,jdbcType=VARCHAR}, </if> <if test="jbxxBgzxr != null" > #{jbxxBgzxr,jdbcType=VARCHAR}, </if> <if test="jbxxBgsjStart != null" > #{jbxxBgsjStart,jdbcType=TIMESTAMP}, </if> <if test="jbxxBgsjEnd != null" > #{jbxxBgsjEnd,jdbcType=TIMESTAMP}, </if> <if test="jbxxSqr != null" > #{jbxxSqr,jdbcType=VARCHAR}, </if> <if test="jbxxJyms != null" > #{jbxxJyms,jdbcType=VARCHAR}, </if> <if test="jbxxBgyj != null" > #{jbxxBgyj,jdbcType=VARCHAR}, </if> <if test="fxfxjkz != null" > #{fxfxjkz,jdbcType=VARCHAR}, </if> <if test="jbxxDjrq != null" > #{jbxxDjrq,jdbcType=TIMESTAMP}, </if> <if test="jbxxFj != null" > #{jbxxFj,jdbcType=VARCHAR}, </if> <if test="jbxxFjmz != null" > #{jbxxFjmz,jdbcType=VARCHAR}, </if> <if test="jbxxStatus != null" > #{jbxxStatus,jdbcType=VARCHAR}, </if> <if test="lsgzSjkssj != null" > #{lsgzSjkssj,jdbcType=TIMESTAMP}, </if> <if test="lsgzSjwcsj != null" > #{lsgzSjwcsj,jdbcType=TIMESTAMP}, </if> <if test="lsgzFj != null" > #{lsgzFj,jdbcType=VARCHAR}, </if> <if test="lsgzFjmz != null" > #{lsgzFjmz,jdbcType=VARCHAR}, </if> <if test="lsgzWcqksm != null" > #{lsgzWcqksm,jdbcType=VARCHAR}, </if> <if test="cjsj != null" > #{cjsj,jdbcType=TIMESTAMP}, </if> <if test="cjr != null" > #{cjr,jdbcType=VARCHAR}, </if> <if test="xgsj != null" > #{xgsj,jdbcType=TIMESTAMP}, </if> <if test="xgr != null" > #{xgr,jdbcType=VARCHAR}, </if> <if test="bglb != null" > #{bglb,jdbcType=VARCHAR}, </if> <if test="isDeleted != null" > #{isDeleted,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.zhetang.model.bggl.WpBggl" > update wp_bggl <set > <if test="jbxxBgszbm != null" > jbxx_bgszbm = #{jbxxBgszbm,jdbcType=VARCHAR}, </if> <if test="jbxxxBgr != null" > jbxxx_bgr = #{jbxxxBgr,jdbcType=VARCHAR}, </if> <if test="jbxxBgzxbm != null" > jbxx_bgzxbm = #{jbxxBgzxbm,jdbcType=VARCHAR}, </if> <if test="jbxxBgzxr != null" > jbxx_bgzxr = #{jbxxBgzxr,jdbcType=VARCHAR}, </if> <if test="jbxxBgsjStart != null" > jbxx_bgsj_start = #{jbxxBgsjStart,jdbcType=TIMESTAMP}, </if> <if test="jbxxBgsjEnd != null" > jbxx_bgsj_end = #{jbxxBgsjEnd,jdbcType=TIMESTAMP}, </if> <if test="jbxxSqr != null" > jbxx_sqr = #{jbxxSqr,jdbcType=VARCHAR}, </if> <if test="jbxxJyms != null" > jbxx_jyms = #{jbxxJyms,jdbcType=VARCHAR}, </if> <if test="jbxxBgyj != null" > jbxx_bgyj = #{jbxxBgyj,jdbcType=VARCHAR}, </if> <if test="fxfxjkz != null" > fxfxjkz = #{fxfxjkz,jdbcType=VARCHAR}, </if> <if test="jbxxDjrq != null" > jbxx_djrq = #{jbxxDjrq,jdbcType=TIMESTAMP}, </if> <if test="jbxxFj != null" > jbxx_fj = #{jbxxFj,jdbcType=VARCHAR}, </if> <if test="jbxxFjmz != null" > jbxx_fjmz = #{jbxxFjmz,jdbcType=VARCHAR}, </if> <if test="jbxxStatus != null" > jbxx_status = #{jbxxStatus,jdbcType=VARCHAR}, </if> <if test="lsgzSjkssj != null" > lsgz_sjkssj = #{lsgzSjkssj,jdbcType=TIMESTAMP}, </if> <if test="lsgzSjwcsj != null" > lsgz_sjwcsj = #{lsgzSjwcsj,jdbcType=TIMESTAMP}, </if> <if test="lsgzFj != null" > lsgz_fj = #{lsgzFj,jdbcType=VARCHAR}, </if> <if test="lsgzFjmz != null" > lsgz_fjmz = #{lsgzFjmz,jdbcType=VARCHAR}, </if> <if test="lsgzWcqksm != null" > lsgz_wcqksm = #{lsgzWcqksm,jdbcType=VARCHAR}, </if> <if test="cjsj != null" > cjsj = #{cjsj,jdbcType=TIMESTAMP}, </if> <if test="cjr != null" > cjr = #{cjr,jdbcType=VARCHAR}, </if> <if test="xgsj != null" > xgsj = #{xgsj,jdbcType=TIMESTAMP}, </if> <if test="xgr != null" > xgr = #{xgr,jdbcType=VARCHAR}, </if> <if test="bglb != null" > bglb = #{bglb,jdbcType=VARCHAR}, </if> <if test="isDeleted != null" > is_deleted = #{isDeleted,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=CHAR} </update> <update id="updateByPrimaryKey" parameterType="com.zhetang.model.bggl.WpBggl" > update wp_bggl set jbxx_bgszbm = #{jbxxBgszbm,jdbcType=VARCHAR}, jbxxx_bgr = #{jbxxxBgr,jdbcType=VARCHAR}, jbxx_bgzxbm = #{jbxxBgzxbm,jdbcType=VARCHAR}, jbxx_bgzxr = #{jbxxBgzxr,jdbcType=VARCHAR}, jbxx_bgsj_start = #{jbxxBgsjStart,jdbcType=TIMESTAMP}, jbxx_bgsj_end = #{jbxxBgsjEnd,jdbcType=TIMESTAMP}, jbxx_sqr = #{jbxxSqr,jdbcType=VARCHAR}, jbxx_jyms = #{jbxxJyms,jdbcType=VARCHAR}, jbxx_bgyj = #{jbxxBgyj,jdbcType=VARCHAR}, fxfxjkz = #{fxfxjkz,jdbcType=VARCHAR}, jbxx_djrq = #{jbxxDjrq,jdbcType=TIMESTAMP}, jbxx_fj = #{jbxxFj,jdbcType=VARCHAR}, jbxx_fjmz = #{jbxxFjmz,jdbcType=VARCHAR}, jbxx_status = #{jbxxStatus,jdbcType=VARCHAR}, lsgz_sjkssj = #{lsgzSjkssj,jdbcType=TIMESTAMP}, lsgz_sjwcsj = #{lsgzSjwcsj,jdbcType=TIMESTAMP}, lsgz_fj = #{lsgzFj,jdbcType=VARCHAR}, lsgz_fjmz = #{lsgzFjmz,jdbcType=VARCHAR}, lsgz_wcqksm = #{lsgzWcqksm,jdbcType=VARCHAR}, cjsj = #{cjsj,jdbcType=TIMESTAMP}, cjr = #{cjr,jdbcType=VARCHAR}, xgsj = #{xgsj,jdbcType=TIMESTAMP}, xgr = #{xgr,jdbcType=VARCHAR}, bglb = #{bglb,jdbcType=VARCHAR}, is_deleted = #{isDeleted,jdbcType=VARCHAR} where id = #{id,jdbcType=CHAR} </update> <select id="listAll" resultType="java.util.HashMap" parameterType="com.zhetang.dto.WpBgglQueryDto"> select distinct b.id, b.jbxx_bgszbm, b.jbxxx_bgr, b.jbxx_bgzxbm, b.jbxx_bgzxr , DATE_FORMAT(jbxx_bgsj_start,'%Y-%m-%d') as jbxx_bgsj_start , DATE_FORMAT(jbxx_bgsj_end,'%Y-%m-%d') as jbxx_bgsj_end,jbxx_sqr, b.jbxx_jyms, b.jbxx_bgyj, b.fxfxjkz, DATE_FORMAT(jbxx_djrq,'%Y-%m-%d') as jbxx_djrq, b.jbxx_fj, b.jbxx_fjmz, DATE_FORMAT(lsgz_sjkssj,'%Y-%m-%d') as lsgz_sjkssj,DATE_FORMAT(lsgz_sjwcsj,'%Y-%m-%d') as lsgz_sjwcsj , b.lsgz_fj, b.lsgz_fjmz, b.lsgz_wcqksm, b.cjsj, b.cjr, b.xgsj, b.xgr, b.bglb, b.is_deleted, b.jbxx_status ,w.id as processinstId,w.`status` as wfStatus ,o1.name as jbxx_bgszbm_name,o2.name as jbxx_bgzxbm_name from wp_bggl b left JOIN wf_processinst w on b.id = w.businessId left join base_organization o1 on b.jbxx_bgszbm = o1.id left join base_organization o2 on b.jbxx_bgzxbm = o2.id join base_data_table bdt on bdt.tableid=b.id <where> <if test="jbxxBgszbm!=null and jbxxBgszbm!=''"> and o1.name like concat('%',concat(#{jbxxBgszbm},'%')) </if> <if test="jbxxxBgr!=null and jbxxxBgr!=''"> and jbxxx_bgr like concat('%',concat(#{jbxxxBgr},'%')) </if> <if test="jbxxBgzxbm!=null and jbxxBgzxbm!=''"> and o2.name like concat('%',concat(#{jbxxBgzxbm},'%')) </if> <if test="jbxxBgzxr!=null and jbxxBgzxr!=''"> and jbxx_sqr like concat('%',concat(#{jbxxBgzxr},'%')) </if> <if test="jbxxBgsjStart!=null "> and jbxx_bgsj_start >=#{jbxxBgsjStart} </if> <if test="jbxxBgsjEnd!=null "> and jbxx_bgsj_end <=DATE_ADD(#{jbxxBgsjEnd},INTERVAL 1 DAY) </if> <if test="jbxxSqr!=null and jbxxSqr!=''"> and jbxx_sqr like concat('%',concat(#{jbxxSqr},'%')) </if> <if test="djrqStart!=null "> and jbxx_djrq >=#{djrqStart} </if> <if test="djrqEnd!=null "> and jbxx_djrq <=#{djrqEnd} </if> <if test="isDeleted!=null"> and is_deleted=#{isDeleted} </if> <if test="bglb!=null and bglb!=''"> and bglb=#{bglb} </if> <if test="allColumn!=null and allColumn!=''"> and concat(o1.name,jbxxx_bgr,o2.name,jbxx_bgzxr) like concat('%',concat(#{allColumn},'%')) </if> <if test="jbxxStatus!=null "> and b.jbxx_status=#{jbxxStatus} </if> <if test="wfStatus!=null"> and w.`status`=#{wfStatus} </if> <if test="oidStr != null"> AND bdt.oid in (${oidStr}) </if> </where> order by b.cjsj desc </select> <select id="selectByIdWithWorkflow" resultType="java.util.HashMap"> select b.id, b.jbxx_bgszbm, b.jbxxx_bgr, b.jbxx_bgzxbm, b.jbxx_bgzxr , DATE_FORMAT(jbxx_bgsj_start,'%Y-%m-%d') as jbxx_bgsj_start , DATE_FORMAT(jbxx_bgsj_end,'%Y-%m-%d') as jbxx_bgsj_end,jbxx_sqr, b.jbxx_jyms, b.jbxx_bgyj, b.fxfxjkz, DATE_FORMAT(jbxx_djrq,'%Y-%m-%d') as jbxx_djrq, b.jbxx_fj, b.jbxx_fjmz, DATE_FORMAT(lsgz_sjkssj,'%Y-%m-%d') as lsgz_sjkssj,DATE_FORMAT(lsgz_sjwcsj,'%Y-%m-%d') as lsgz_sjwcsj , b.lsgz_fj, b.lsgz_fjmz, b.lsgz_wcqksm, b.cjsj, b.cjr, b.xgsj, b.xgr, b.bglb, b.is_deleted, b.jbxx_status ,w.id as processinstId,w.`status` as wfStatus ,o1.name as jbxx_bgszbm_name,o2.name as jbxx_bgzxbm_name from wp_bggl b left JOIN wf_processinst w on b.id = w.businessId left join base_organization o1 on b.jbxx_bgszbm = o1.id left join base_organization o2 on b.jbxx_bgzxbm = o2.id where b.id=#{id} </select> </mapper>
EoCollectMapper.xml

<?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.hainei.mapper.economic.EoCollectMapper"> <resultMap id="BaseResultMap" type="com.hainei.pojo.model.economic.EoCollect"> <!--@mbg.generated generated on Wed Apr 13 11:04:54 CST 2022.--> <!--@Table eo_collect--> <id column="id" jdbcType="VARCHAR" property="id" /> <result column="company_auth" jdbcType="VARCHAR" property="companyAuth" /> <result column="company" jdbcType="VARCHAR" property="company" /> <result column="year" jdbcType="VARCHAR" property="year" /> <result column="year_revenue" jdbcType="DECIMAL" property="yearRevenue" /> <result column="year_produce_value" jdbcType="DECIMAL" property="yearProduceValue" /> <result column="year_profit_rate" jdbcType="DECIMAL" property="yearProfitRate" /> <result column="year_person_num" jdbcType="INTEGER" property="yearPersonNum" /> <result column="year_research_person_num" jdbcType="INTEGER" property="yearResearchPersonNum" /> <result column="year_research_put" jdbcType="VARCHAR" property="yearResearchPut" /> <result column="gmt_created_on" jdbcType="TIMESTAMP" property="gmtCreatedOn" /> <result column="gmt_created_by" jdbcType="VARCHAR" property="gmtCreatedBy" /> <result column="gmt_updated_by" jdbcType="VARCHAR" property="gmtUpdatedBy" /> <result column="gmt_updated_on" jdbcType="TIMESTAMP" property="gmtUpdatedOn" /> <result column="is_deleted" jdbcType="TINYINT" property="isDeleted" /> <result column="version" jdbcType="INTEGER" property="version" /> </resultMap> <sql id="Base_Column_List"> <!--@mbg.generated generated on Wed Apr 13 11:04:54 CST 2022.--> id, company_auth, company, `year`, year_revenue, year_produce_value, year_profit_rate, year_person_num, year_research_person_num, year_research_put, gmt_created_on, gmt_created_by, gmt_updated_by, gmt_updated_on, is_deleted, version </sql> <resultMap id="listResultMap" type="com.hainei.pojo.vo.economic.EoCollectVO"> <id column="id" jdbcType="VARCHAR" property="id" /> <result column="company_auth" jdbcType="VARCHAR" property="companyAuth" /> <result column="company" jdbcType="VARCHAR" property="company" /> <result column="year" jdbcType="VARCHAR" property="year" /> <result column="year_revenue" jdbcType="DECIMAL" property="yearRevenue" /> <result column="year_produce_value" jdbcType="DECIMAL" property="yearProduceValue" /> <result column="year_profit_rate" jdbcType="VARCHAR" property="yearProfitRate" /> <result column="year_person_num" jdbcType="INTEGER" property="yearPersonNum" /> <result column="year_research_person_num" jdbcType="INTEGER" property="yearResearchPersonNum" /> <result column="year_research_put" jdbcType="VARCHAR" property="yearResearchPut" /> </resultMap> <resultMap id="listEoCollectAndQuarter" type="com.hainei.pojo.vo.economic.EoCollectAndQuarterVO"> <id column="id" jdbcType="VARCHAR" property="id" /> <result column="company_auth" jdbcType="VARCHAR" property="companyAuth" /> <result column="company" jdbcType="VARCHAR" property="company" /> <result column="year" jdbcType="VARCHAR" property="year" /> <result column="year_revenue" jdbcType="DECIMAL" property="yearRevenue" /> <result column="year_produce_value" jdbcType="DECIMAL" property="yearProduceValue" /> <result column="quarter" jdbcType="INTEGER" property="quarter" /> <result column="revenve" jdbcType="DECIMAL" property="revenue" /> <result column="product_value" jdbcType="DECIMAL" property="productValue" /> <result column="year_profit_rate" jdbcType="VARCHAR" property="yearProfitRate" /> <result column="year_person_num" jdbcType="INTEGER" property="yearPersonNum" /> <result column="year_research_person_num" jdbcType="INTEGER" property="yearResearchPersonNum" /> <result column="year_research_put" jdbcType="VARCHAR" property="yearResearchPut" /> </resultMap> <select id="listEoCollect" resultMap="listResultMap"> select id, company_auth, company, `year`, year_revenue, year_produce_value, gmt_created_on, gmt_created_by, gmt_updated_by, gmt_updated_on, is_deleted, version, year_profit_rate, year_person_num, year_research_person_num, year_research_put from eo_collect d where 1=1 <if test="company != null and company != ''"> and d.company like concat('%',concat(#{company,jdbcType=VARCHAR},'%')) </if> <if test="year != null and year != ''"> and d.year = #{year,jdbcType=VARCHAR} </if> <foreach close=")" collection="olists" index="index" item="oitem" open="and( 1!=1 " separator=""> or d.company_auth = #{oitem} </foreach> <if test="olists.size == 0"> and d.company_auth= "11111111111111111111111111111111111111111" </if> and d.is_deleted =0 order by d.gmt_created_on desc </select> <select id="listEoCollectAndQuarter" resultMap="listEoCollectAndQuarter"> select distinct d.id,q.`quarter`,q.revenve,q.product_value, d.company_auth, d.company, d.`year`, d.year_revenue, d.year_produce_value, year_profit_rate,year_person_num, year_research_person_num, year_research_put from eo_collect d left join eo_collect_quarter q on q.collect_id = d.id where 1=1 <if test="company != null and company != ''"> and d.company like concat('%',concat(#{company,jdbcType=VARCHAR},'%')) </if> <foreach close=")" collection="olists" index="index" item="oitem" open="and( 1!=1 " separator=""> or d.company_auth = #{oitem} </foreach> <if test="olists.size == 0"> and d.company_auth= "11111111111111111111111111111111111111111" </if> <if test="yearList != null and yearList.size() != 0"> <foreach close=")" collection="yearList" index="yearIndex" item="yearItem" open="and( 1!=1" separator=""> or d.`year` = #{yearItem} </foreach> </if> <if test="companyList != null and companyList.size() != 0"> <foreach close=")" collection="companyList" index="companyIndex" item="companyItem" open="and( 1!=1" separator=""> or d.company = #{ companyItem,jdbcType=VARCHAR} </foreach> </if> <if test="quarterList != null and quarterList.size() != 0"> <foreach close=")" collection="quarterList" index="quarterIndex" item="quarterItem" open="and( 1!=1" separator=""> or q.quarter = #{ quarterItem,jdbcType=INTEGER} </foreach> </if> and d.is_deleted =0 order by d.gmt_created_on desc </select> <select id="listByCompanyAndYear" resultMap="listResultMap"> select id, company_auth, company, `year`, year_revenue, year_produce_value, gmt_created_on, gmt_created_by, gmt_updated_by, gmt_updated_on, is_deleted, version from eo_collect d where 1=1 <if test="company != null and company != ''"> and d.company like concat('%',concat(#{company,jdbcType=VARCHAR},'%')) </if> <if test="year != null and year != ''"> and d.year = #{year,jdbcType=VARCHAR} </if> and d.is_deleted =0 order by d.gmt_created_on desc </select> </mapper>
WpBggIMapper.java

package com.zhetang.dao.bggl; import com.zhetang.dto.WpBgglQueryDto; import com.zhetang.model.bggl.WpBggl; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.HashMap; import java.util.List; @Mapper public interface WpBgglMapper { int deleteByPrimaryKey(String id); int insert(WpBggl record); int insertSelective(WpBggl record); WpBggl selectByPrimaryKey(String id); int updateByPrimaryKeySelective(WpBggl record); int updateByPrimaryKey(WpBggl record); List<HashMap<String, Object>> listAll(WpBgglQueryDto dto); HashMap<String, Object> selectByIdWithWorkflow(@Param("id") String id); }
wpCbsDtzy.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.hainei.mapper.wp.zyaqgl.WpCbsDtzyMapper" > <resultMap id="BaseResultMap" type="com.hainei.pojo.model.wp.zyaqgl.WpCbsDtzy" > <!-- WARNING - @mbg.generated --> <id column="id" property="id" jdbcType="CHAR" /> <result column="zyzmc" property="zyzmc" jdbcType="VARCHAR" /> <result column="aqcs" property="aqcs" jdbcType="VARCHAR" /> <result column="sqr" property="sqr" jdbcType="VARCHAR" /> <result column="zydw" property="zydw" jdbcType="VARCHAR" /> <result column="zykssj" property="zykssj" jdbcType="BIGINT" /> <result column="zyjssj" property="zyjssj" jdbcType="BIGINT" /> <result column="status" property="status" jdbcType="INTEGER" /> <result column="zyzbh" property="zyzbh" jdbcType="VARCHAR" /> <result column="whbs" property="whbs" jdbcType="VARCHAR" /> <result column="jhr" property="jhr" jdbcType="VARCHAR" /> <result column="ssaqjyr" property="ssaqjyr" jdbcType="VARCHAR" /> <result column="is_deleted" property="isDeleted" jdbcType="TINYINT" /> <result column="gmt_created_on" property="gmtCreatedOn" jdbcType="TIMESTAMP" /> <result column="gmt_updated_on" property="gmtUpdatedOn" jdbcType="TIMESTAMP" /> <result column="gmt_created_by" property="gmtCreatedBy" jdbcType="VARCHAR" /> <result column="gmt_updated_by" property="gmtUpdatedBy" jdbcType="VARCHAR" /> <result column="sqbm" property="sqbm" jdbcType="VARCHAR" /> <result column="zydd" property="zydd" jdbcType="VARCHAR" /> <result column="zyfw" property="zyfw" jdbcType="VARCHAR" /> <result column="zyfwjt" property="zyfwjt" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List"> id, sqr ,jhr, zykssj, zyjssj ,zydd ,zydw ,zyfw, whbs ,ssaqjyr ,status </sql> <select id="getById" resultType="map"> select DISTINCT id,zyzmc,zyzbh,sqbm,qttszy, sqr ,jhr,FROM_UNIXTIME(d.zykssj/1000,"%Y-%m-%d %H:%i:%s") zykssj,FROM_UNIXTIME(d.zyjssj/1000,"%Y-%m-%d %H:%i:%s") zyjssj ,zydd ,zydw ,zyfw, whbs ,ssaqjyr ,status,DATE_FORMAT(d.gmt_created_on,"%Y-%m-%d %H:%i:%s") gmtCreatedOn FROM wp_cbs_dtzy d where d.id = #{id,jdbcType=VARCHAR} </select> <select id="selectBySelective" resultType="map"> select DISTINCT id,zyzmc,zyzbh,sqbm,qttszy,sqr ,jhr,FROM_UNIXTIME(d.zykssj/1000,"%Y-%m-%d %H:%i:%s") zykssj,FROM_UNIXTIME(d.zyjssj/1000,"%Y-%m-%d %H:%i:%s") zyjssj ,zydd ,zydw ,zyfw, whbs ,ssaqjyr ,status ,DATE_FORMAT(d.gmt_created_on,"%Y-%m-%d %H:%i:%s") gmtCreatedOn FROM wp_cbs_dtzy d <if test="gmtCreatedBy!='superAdmin'"> INNER JOIN view_user_data_permission view_udp ON view_udp.table_id=d.id </if> WHERE 1=1 <if test="sqr != null and sqr != ''" > AND d.sqr LIKE CONCAT(CONCAT('%',#{sqr,jdbcType=VARCHAR},'%')) </if> <if test="zyzmc != null and zyzmc != ''" > AND d.zyzmc LIKE CONCAT(CONCAT('%',#{zyzmc,jdbcType=VARCHAR},'%')) </if> <if test="zyzbh != null and zyzbh != ''" > AND d.zyzbh LIKE CONCAT(CONCAT('%',#{zyzbh,jdbcType=VARCHAR},'%')) </if> <if test="sqbm != null and sqbm != ''" > AND d.sqbm LIKE CONCAT(CONCAT('%',#{sqbm,jdbcType=VARCHAR},'%')) </if> <if test="jhr != null and jhr != ''" > AND d.jhr LIKE CONCAT(CONCAT('%',#{jhr,jdbcType=VARCHAR},'%')) </if> <if test="zydd != null and zydd != ''" > AND d.zydd LIKE CONCAT(CONCAT('%',#{zydd,jdbcType=VARCHAR},'%')) </if> <if test="zydw != null and zydw != ''" > AND d.zydw LIKE CONCAT(CONCAT('%',#{zydw,jdbcType=VARCHAR},'%')) </if> <if test="zyfw != null and zyfw != ''" > AND d.zyfw LIKE CONCAT(CONCAT('%',#{zyfw,jdbcType=VARCHAR},'%')) </if> <if test="whbs != null and whbs != ''" > AND d.whbs LIKE CONCAT(CONCAT('%',#{whbs,jdbcType=VARCHAR},'%')) </if> <if test="ssaqjyr != null and ssaqjyr != ''" > AND d.ssaqjyr LIKE CONCAT(CONCAT('%',#{ssaqjyr,jdbcType=VARCHAR},'%')) </if> <if test="status != null " > AND d.status = #{status} </if> <if test="zykssj != null and zyjssj != null"> AND d.zykssj >= #{zykssj,jdbcType=INTEGER} and d.zyjssj <= #{zyjssj,jdbcType=INTEGER} </if> <if test="all != null and all != ''"> AND CONCAT(d.zyzmc, d.zyzbh, d.sqr, d.ssaqjyr) LIKE CONCAT(CONCAT('%',#{all},'%')) </if> <choose> <when test="gmtCreatedBy=='superAdmin'"> and 1=1 </when> <otherwise> and view_udp.user_id=#{gmtCreatedBy} and IF(view_udp.is_view_yourself=0,1=1,d.gmt_created_by=#{gmtCreatedBy}) </otherwise> </choose> AND d.is_deleted= 0 order by d.gmt_created_on desc </select> <select id="totalNums" resultType="map"> select DISTINCT id,zyzmc,zyzbh,sqbm,qttszy,sqr ,jhr,FROM_UNIXTIME(d.zykssj/1000,"%Y-%m-%d %H:%i:%s") zykssj,FROM_UNIXTIME(d.zyjssj/1000,"%Y-%m-%d %H:%i:%s") zyjssj ,zydd ,zydw ,zyfw, whbs ,ssaqjyr ,status ,DATE_FORMAT(d.gmt_created_on,"%m") gmtCreatedOn,d.is_deleted isDeleted FROM wp_cbs_dtzy d WHERE d.is_deleted= 0 </select> </mapper>
WpFileServiceImpl.java

package com.zhetang.service.aqzd.impl; import com.zhetang.admin.permission.model.BaseUser; import com.zhetang.dao.aqzd.WpFileMapper; import com.zhetang.model.aqzd.WpFile; import com.zhetang.service.aqzd.WpFileService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletRequest; import java.util.Date; import java.util.List; import java.util.UUID; /** * @Auther: GUJUANJUAN * @Date: 2019/11/19 13:55 * @Description: */ @Service public class WpFileServiceImpl implements WpFileService { @Autowired private WpFileMapper fileMapper; @Override public void addFiles(List<WpFile> fileList,String uId,Byte type, HttpServletRequest request) { fileMapper.updateByUid(uId,type); BaseUser baseUser = (BaseUser) request.getSession().getAttribute("user"); int sort = 1; for (WpFile file:fileList){ file.setType(type); file.setSort(sort++); if (file.getId() == null || "".equals(file.getId())) { file.setuId(uId); String id = UUID.randomUUID().toString().replace("-", ""); file.setCjr(baseUser.getId()); file.setCjsj(new Date()); file.setId(id); fileMapper.insertSelective(file); }else { //将假删除的状态改了 file.setDeleteStatus(0); file.setXgr(baseUser.getId()); file.setXgsj(new Date()); fileMapper.updateByPrimaryKeySelective(file); } } } }
数据库建表规范
TemplateController

package com.zhetang.controller; import com.zhetang.core.vo.JsonResult; import com.zhetang.dao.BaseTemplateDisposeMapper; import com.zhetang.dao.BaseTemplateFieldMapper; import com.zhetang.dao.ExportMapper; import com.zhetang.model.BaseTemplateDispose; import com.zhetang.model.BaseTemplateField; import com.zhetang.util.ExportExcel; import com.zhetang.util.ParseExcel; import com.zhetang.utils.upload.FileModel; import com.zhetang.utils.upload.FileUtil; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.PropertySource; import org.springframework.mock.web.MockMultipartFile; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import static com.zhetang.util.ExportWord.exportDoc; import static com.zhetang.util.ExportWord.exportDocx; import static com.zhetang.util.ParseWord.parseDoc; import static com.zhetang.util.ParseWord.parseDocx; /** * @Auther: GUJUANJUAN * @Date: 2019/12/20 11:58 * @Description: */ @Controller @RequestMapping("template") @PropertySource({"classpath:application.properties"}) public class TemplateController { @Value("${uploadPath}") private String uploadPath; @Value("${readPath}") private String readPath; @Autowired private ExportMapper exportMapper; @Autowired private BaseTemplateDisposeMapper disposeMapper; @Autowired private BaseTemplateFieldMapper fieldMapper; /** * 导入模板 * * @param file * @return */ @RequestMapping("import") @ResponseBody public JsonResult getListByMes(@RequestParam("file") MultipartFile file, HttpServletRequest request) { List<String> list = new ArrayList<>(); String fileName = file.getOriginalFilename(); File wordFile = null; try { if (fileName.endsWith("docx") || fileName.endsWith("doc")) { wordFile = new File(uploadPath + fileName); file.transferTo(wordFile); } if (fileName.endsWith("docx")) { list = parseDocx(wordFile); wordFile.delete(); } else if (fileName.endsWith("doc")) { list = parseDoc(wordFile); wordFile.delete(); } else if (fileName.endsWith("xls")) { list = ParseExcel.parseXls(file); } else if (fileName.endsWith("xlsx")) { list = ParseExcel.parseXlsx(file); } else { return JsonResult.build(400, "格式不正确"); } } catch (Exception e) { e.printStackTrace(); return JsonResult.build(400, e.getMessage()); } return JsonResult.ok(list); } /** * 检索字段导出 /template/export * * @param fileName 导出的文件名 * @param id 导出配置id * @param field 检索字段名 * @param value 检索值 * @param response * @param request * @return */ @RequestMapping(value = "/export") @ResponseBody public JsonResult export(String fileName, String id, String field, String value, HttpServletResponse response, HttpServletRequest request) { BaseTemplateDispose dispose = disposeMapper.selectByPrimaryKey(id); if (dispose == null) { return JsonResult.build(400, "模版不存在!"); } List<Map<String, Object>> list = exportMapper.selectByKey(dispose.getTableName(), field, value); if (list.size() > 1) { return JsonResult.build(400, "检索数据超过一条,无法导出!"); } else if (list.size() == 0) { return JsonResult.build(400, "没有检索到数据,无法导出!"); } List<BaseTemplateField> fieldList = fieldMapper.selectByDisposeId(dispose.getId()); Map<String, Object> map = list.get(0); //替换值 Map<String, Object> params = new HashMap<>(); for (BaseTemplateField field1 : fieldList) { params.put(field1.getName(), map.get(field1.getMatchField())); } File file = new File(uploadPath + dispose.getFileAddress().replace(readPath, "")); return exportFile(response, request, params, file, fileName,id); } /** * 文件导出 * * @param response * @param request * @param params * @param file * @param newName * @return */ private JsonResult exportFile(HttpServletResponse response, HttpServletRequest request, Map<String, Object> params, File file, String newName,String id) { InputStream inputStream = null; try { inputStream = new FileInputStream(file); MultipartFile multipartFile = new MockMultipartFile(file.getName(), inputStream); String fileName = multipartFile.getName(); if (fileName.endsWith("docx")) { exportDocx(uploadPath +"word"+File.separator+ fileName, uploadPath, request, response, newName + ".docx", params); } else if (fileName.endsWith("doc")) { exportDoc(file, uploadPath, response, newName + ".doc", params,request); } else if (fileName.endsWith("xls")) { ExportExcel.exportXls(multipartFile, response, newName + ".xls", params); } else if (fileName.endsWith("xlsx")) { ExportExcel.exportXlsx(multipartFile, response, newName + ".xlsx", params); } else { return JsonResult.build(400, "格式不正确"); } } catch (Exception e) { e.printStackTrace(); return JsonResult.build(400, "导出失败"); } finally { try { inputStream.close(); disposeMapper.updateExortCount(id); } catch (IOException e) { e.printStackTrace(); } } return JsonResult.ok(); } /** * 获取所有非基础表 /template/getAllTables * * @return */ @RequestMapping("getAllTables") @ResponseBody public JsonResult getAllTables() { return JsonResult.ok(exportMapper.getAllTables()); } /** * 获取表所有字段 /template/getAllColumn * * @param tableName * @return */ @RequestMapping("getAllColumn") @ResponseBody public JsonResult getAllColumn(String tableName) { System.out.println("tableName = " + tableName); return JsonResult.ok(exportMapper.getAllColumn(tableName)); } }
个人学习笔记,记录日常学习,便于查阅及加深,仅为方便个人使用。