mybatis框架主要工作于数据访问层,侧重于与数据库打交道的sql语句编写,对sql要求达到比较熟练的程度,本文将介绍以下几个方面的使用参数注入,返回对象,执行sql,include关键字
1.参数注入
1.1用#{0},#{1}的形式,0代表第一个参数,1代表第二个参数
public List<RecordVo> queryList(String workerId, Integer topNum); <select id="queryList" resultType="com.demo.RecordVo"> SELECT ID id, WORKER_ID workerId, UPDATE_DATE updateDate FROM USER_RECORDS t WHERE t.WORKER_ID = #{0} LIMIT #{1} </select>
1.2Map或者封装对象,workerId为map里面的键
public Integer queryCountByWorkerId(Map queryParam); <select id="queryCountByWorkerId" parameterType="java.util.Map" resultType="java.lang.Integer"> SELECT COUNT(1) FROM tableName F WHERE F.WORKER_ID = #{workerId} </select>
1.3注解
public Integer queryCountByWorkerId(@param(“workerId”)String workerId); <select id="queryCountByWorkerId" parameterType="java.util.Map" resultType="java.lang.Integer"> SELECT COUNT(1) FROM tableName F WHERE F.WORKER_ID = #{workerId} </select>
2.返回
2.1映射<!-- 实体类与表字段对应 -->
<resultMap type="com.demo.DataModule" id="dataModule"> <result column="ID" property="id" /> <result column="CREATE_DATE" property="createDate" /> <result column="WORKERID" property="workerId" /> <result column="UPDATE_DATE" property="updateDate" /> <result column="STATUS" property="status" /> </resultMap> <select id="queryAll" resultType="dataModule"> select ID,CREATE_DATE,WORKERID from tableName </select>
2.2直接返回对象
<select id="queryAll" resultType="com.demo.DataModule"> select ID id,CREATE_DATE createDate,WORKERID workerId from tableName </select>
3.执行sql
3.1sql参数:
public class ParamVo { private String sql; //getter setter 省略 }
3.2接口:
/** * @功能描述: 创建 * @param vo * @return */ public int excuteCreateSql(ParamVo vo); /** * @功能描述: 查询 * @param vo * @return */ public List<Map<String, Object>> excuteSelectSql(ParamVo vo);
3.3xml:
<update id="excuteCreateSql"> ${sql} </update>
${}不编译sql直接执行,如果用#{sql}可能报错
<select id="excuteSelectSql" resultType="java.util.Map"> ${sql} </select>
这里不知道返回类型用map或者hashmap作为返回
4.include
有时候两个方法要返回的字段都一样或者where子句一样,这样为了避免重复写代码,就抽出来用include
4.1定义子句相同部分
<sql id="queryChild"> FROM tableName1 F RIGHT JOIN tableName2 C ON F.WORKER_ID = C.WORKER_ID WHERE F.STATUS = 1 AND F.WORKER_ID = #{workerId} ORDER BY C.CREATE_DATE DESC </sql>
4.2引用
<select id="queryCountByWorkerId" parameterType="java.util.Map" resultType="java.lang.Integer"> SELECT COUNT(1) <include refid="queryChild"/> </select> <select id="queryListByWorkerId" parameterType="java.util.Map" resultType="com.demo.RecordVo"> SELECT ID id, WORKER_ID workerId, UPDATE_DATE updateDate,...... <include refid="queryChild"/> </select>
关注公众号“java工会”,获取更多技术干货!
加入qq群727096055,阿里大神和你聊技术!