mybatis常用查询SQL总结

1、普通查询

<select id="selectAll" resultMap="BaseResultMap">
  select * from chart
</select>

2、通过参数查询

（1）一个参数

<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select * from chart
    where id = #{id,jdbcType=INTEGER}
  </select>

对应mapper

Chart selectByPrimaryKey(Integer id);

（2）多个参数

分页查询的例子：

<select id="findByPage" resultMap="BaseResultMap" parameterType="java.lang.Integer">
    select * from chart order by id asc limit #{startIndex},#{pageSize}
  </select>

对应mapper，需要加上@Param注解

List<Chart> findByPage(@Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize);

3、通过对象查询

多条件查询的例子：

<select id="selectByCondition" resultMap="BaseResultMap" parameterType="com.it.pojo.ChartCondition" >
    select * from chart_type
    <where>
        <if test="typeId !=null and typeId!=">
        type_id= #{typeId}
      </if>
        <if test="typeName !=null and typeName!=">
          OR type_name= #{typeName}
        </if>
        <if test="createDate !=null and createDate !=">
          OR create_date like concat(#{createDate}, %)
        </if>
    </where>
  </select>

对应mapper

public List<Chart> selectByCondition(ChartCondition chartCondition);

4、通过对象和参数一起查询

多条件分页查询的例子：

<select id="selectByConditionAndPage" resultMap="BaseResultMap">
    select * from chart_type
    <where>
      <if test="chartCondition.typeId !=null and chartCondition.typeId !=">
        type_id= #{chartCondition.typeId}
      </if>
      <if test="chartCondition.typeName !=null and chartCondition.typeName !=">
        and type_name= #{chartCondition.typeName}
      </if>
      <if test="chartCondition.createDate !=null and chartCondition.createDate !=">
        and create_date like concat(#{chartCondition.createDate}, %)
      </if>
    </where>
    order by id desc limit #{startIndex},#{pageSize}
  </select>

对应mapper如下，其中对象和参数都是用注解@Param

public List<ChartType> selectByConditionAndPage(@Param("chartCondition") ChartCondition chartCondition, @Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize);