关于MyBatis会用到的SQL整理

最近参加的项目，用到的数据库是pgSql，

之前一般使用的是oracle，发现两者还是有一些要特别注意的，就写在这记一下。（个人觉得的，不喜勿喷，欢迎指点新的，谢谢！）

1.关于工具方面，之前用的PLSql，现在换成了pdAdmin，感觉这个工具不好用。也许是PLSql用习惯了吧。

 可以下个辅助工具dbserver。

2.pgSql建表时，主键id serial，会自动生成序列。数字类型有int8。

会碰到一个情况，数据插入时，若表的主键被占用，会失败，执行下面SQL，刷新占用主键。

select nextval(序列名) from 表名。

3.Mybaits，SQL语句有一些常用的，如下：

a.if语句：

<select id="findActiveBlogWithTitleLike" resultType="Blog">
  SELECT * FROM BLOG 
  WHERE state = 'ACTIVE' 
  <if test="title != null AND title != '' ">
    AND title like #{title}
  </if>
</select>

b.choose（when, otherwise）语句：

<select id="findActiveBlogLike" 
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

c. foreach:

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

d. bind

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

e.where

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG 
  <where> 
    <if test="state != null">
         state = #{state}
    </if> 
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like '%${author.name}%'
    </if>
  </where>
</select>

f.set

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

4.递归查询出树形数据

第一步：创建树形数据Bean

public class DeptTree {
private String id;
private String name;
private List<DeptTree> childrenList;//子节点
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<DeptTree> getChildrenList() {
return childrenList;
}
public void setChildrenList(List<DeptTree> childrenList) {
this.childrenList = childrenList;
}
}

第二步：mybatis相应的sql.xml文件配置

<!-- 初始化部门树 -->
<resultMap type="com.lilosoft.cospace.sys.bean.DeptTree" id="deptTree">
<result column="DEPT_ID" property="id" javaType="Java.lang.String" />
<result column="DEPT_NAME" property="name" javaType="java.lang.String" />
<collection column="DEPT_ID" property="childrenList" ofType="DeptTree" javaType="java.util.ArrayList" select="selectDeptChildrenById"/>
</resultMap>
<!-- 根据parent_id，先查出所有一级部门 -->
<select id="queryDeptTreeList" resultMap="deptTree">
select dept_id,dept_name from sys_dept  where is_use='0' and parent_id= (select dept_id from sys_dept where parent_id='-1' and is_use='0')
</select>
<!-- 再递归查询出一级部门下的所有子部门 -->
<select id="selectDeptChildrenById" resultMap="deptTree" parameterType="string">
select dept_id,dept_name from sys_dept  where is_use='0' and parent_id= #{DEPT_ID}
</select>

第三步：（dao和service层代码略）控制器调用

List<DeptTree> dList=deptService.queryDeptTreeList();