mybatis 应用

利用mybatis的映射sql

<?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.qtbigdata.news.dao.newsinfo.NwInfoMapper">
    <resultMap id="BaseResultMap" type="com.qtbigdata.news.model.NewsInfo">
    <!-- 借用自动生成的mybatis Map -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="news_id" jdbcType="BIGINT" property="newsId" />
    <result column="articleType" jdbcType="TINYINT" property="articletype" />
    <result column="title" jdbcType="VARCHAR" property="title" />
    <result column="author" jdbcType="VARCHAR" property="author" />
    <result column="artKeyword" jdbcType="VARCHAR" property="artkeyword" />
    <result column="artAbstract" jdbcType="VARCHAR" property="artabstract" />
    <result column="pubSource" jdbcType="VARCHAR" property="pubsource" />
    <result column="copyrightSource" jdbcType="VARCHAR" property="copyrightsource" />
    <result column="url" jdbcType="VARCHAR" property="url" />
    <result column="urlMD5" jdbcType="VARCHAR" property="urlmd5" />
    <result column="pubTime" jdbcType="TIMESTAMP" property="pubtime" />
    <result column="webSiteId" jdbcType="INTEGER" property="websiteid" />
    <result column="childSiteId" jdbcType="INTEGER" property="childsiteid" />
    <result column="fingerprint" jdbcType="VARCHAR" property="fingerprint" />
    <result column="moduleId" jdbcType="INTEGER" property="moduleid" />
    <result column="sourceType" jdbcType="BIT" property="sourcetype" />
    <result column="delFlag" jdbcType="BIT" property="delflag" />
    <result column="createTime" jdbcType="BIGINT" property="createtime" />
    <result column="userId" jdbcType="INTEGER" property="userid" />
    <result column="lastUpEditor" jdbcType="INTEGER" property="lastupeditor" />
    <result column="lastUpTime" jdbcType="TIMESTAMP" property="lastuptime" />
    <result column="ext1" jdbcType="INTEGER" property="ext1" />
    <result column="ext2" jdbcType="INTEGER" property="ext2" />
    <result column="ext3" jdbcType="INTEGER" property="ext3" />
    <result column="ext4" jdbcType="VARCHAR" property="ext4" />
    <result column="ext5" jdbcType="VARCHAR" property="ext5" />
    <result column="ext6" jdbcType="VARCHAR" property="ext6" />
    <result column="ext7" jdbcType="VARCHAR" property="ext7" />
    <result column="ext8" jdbcType="VARCHAR" property="ext8" />
  </resultMap>
  <resultMap extends="BaseResultMap" id="ResultMapWithBLOBs" type="com.qtbigdata.news.model.NewsInfo">
    <!-- 扩展解决返回类容过大的问题 返回的resultMap 定义完成-->
    <result column="content" jdbcType="LONGVARCHAR" property="content" />
  </resultMap>
  <sql id="Base_Column_List">
    <!-- 需要查询的字段，不包含content -->
    id, news_id, articleType, title, author, artKeyword, artAbstract, pubSource, copyrightSource, 
    url, urlMD5, pubTime, webSiteId, childSiteId, fingerprint, moduleId, sourceType, 
    delFlag, createTime, userId, lastUpEditor, lastUpTime, ext1, ext2, ext3, ext4, ext5, 
    ext6, ext7, ext8
  </sql>
  <sql id="Blob_Column_List">
    <!-- 单独列出的blob内容 -->
    content
  </sql>

  <select id="selectByMapWithBLOBs" parameterType="java.util.Map" resultMap="ResultMapWithBLOBs">
    <!--通过moduleId查询分页列表 -->
    SELECT
    <include refid="Base_Column_List" />
    ,
    <include refid="Blob_Column_List" />
    FROM news_info 
    WHERE 1=1
    <if test="queryname != '' and queryname != null" >
        AND  title LIKE concat(concat('%',#{queryname}),'%')
    </if>
    <if test="beginDate !='' and beginDate != null" >
         AND pubTime &gt;=#{beginDate}
    </if>
    <if test="endDate !='' and endDate != null " >
         AND pubTime &lt;=#{endDate}
    </if>
    <if test="moduleId != null">
         AND  moduleId =#{moduleId,jdbcType=INTEGER}
    </if>
    <if test="articleType != null">
         AND  articleType =#{articleType,jdbcType=INTEGER}
    </if>
    GROUP BY news_id
    ORDER BY pubTime desc
    lIMIT #{pageOffset},#{pageSize}
  </select>

  <select id="selectCountByParam" parameterType="java.util.Map" resultType="java.lang.Integer">
  <!--通过moduleId查询去重的总记录 -->
    SELECT COUNT(distinct news_id)FROM news_info    
    WHERE 1=1
    <if  test="moduleId != null" >
        AND moduleId =#{moduleId}
    </if>
    <if test="articleType != null">
        AND  articleType =#{articleType,jdbcType=INTEGER}
    </if>
  </select>
</mapper>
另外一段配置mybatis数据的方法
<!--2. 查询基础表，撮合表关联信息集合列表，用于系统数据导入 -->

        <select id="queryImportList" resultType="java.util.Map" parameterType="java.util.Map">
            select tpbi.* ,
            tbei.id tid,
            tbei.content context,
            tbei.is_include_cost,
            tbei.between_way,
            tbei.cost_type,
            tbei.contacts_name,
            tbei.contacts_phone,
            tbei.contacts_email,
            tbei.contacts_QQ,
            tbei.contacts_address,
            tbei.price
        from trademark_project_base_info  tpbi
        left join trademark_between_entrust_info tbei
        on tpbi.id = tbei.project_base_info_id
            <where>
                <if test="designation != null and designation != ''">
                    tpbi.designation like CONCAT('%',#{designation},'%')
                </if>
                <if test="reg_no != null and reg_no != ''">
                    tpbi.reg_no like CONCAT('%',#{reg_no},'%')
                </if>
                <if test="contacts_name != null and contacts_name != ''">
                    tbei.contacts_name like CONCAT('%',#{contacts_name},'%')
                </if>
                <if test="contacts_phone != null and contacts_phone != ''">
                    tbei.contacts_phone like CONCAT('%',#{contacts_phone},'%')
                </if>
                <if test="starttime != null and starttime != ''">
                    and tpbi.addtime <![CDATA[>=]]> #{starttime}
                </if>
                <if test="endtime != null and endtime != ''">
                    and tpbi.addtime <![CDATA[<=]]> #{endtime}
                </if>
            </where>
             limit #{pageOffset},#{pageSize}
        </select>