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>