Mybatis 各种查询用法记录

最新推荐文章于 2023-08-22 11:05:12 发布

低调的hero

最新推荐文章于 2023-08-22 11:05:12 发布

阅读量1.4k

点赞数

本文链接：https://blog.csdn.net/u013466972/article/details/84960630

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1、中规中矩的配置XML sql语句类似于generator 自动生成的方式

dao接口

    User selectByPrimaryKey(Long id);

XML配置，只写了select

<resultMap id="BaseResultMap" type="com.lt.demo.model.User">
    <result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
  </resultMap>
  <sql id="Base_Column_List">
    id, name, age, create_date
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from user
    where id = #{id,jdbcType=BIGINT}
  </select>

2、参数用默认位置编号代替、用注解别名代替、参数传复杂对象（注意：param 编号从1开始，arg编号从0开始）

dao接口

    User selectByParamNumXml(User user, String name);

    User selectByParamNameXml(@Param("user") User user, @Param("name") String name);

    List<User> selectByListXml(List<Long> list, String name);

xml配置

<sql id="Model_Column_List">
    id, name, age, create_date as createDate
</sql>  

<select id="selectByParamNumXml" resultType="com.lt.cloud.model.User">
    select
    <include refid="Model_Column_List" />
    from user
    where id = #{param1.id} and name = #{param2}
  </select>
    <select id="selectByParamNameXml" resultType="com.lt.cloud.model.User">
        select
        <include refid="Model_Column_List" />
        from user
        where id = #{user.id} and name = #{name}
    </select>

    <select id="selectByListXml" resultType="com.lt.cloud.model.User">
        select
        <include refid="Model_Column_List" />
        from user
        where name != #{arg1} and id in
        <foreach collection="arg0" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

3、用mybatis注解代替xml文件，不仔细讲了，自己看吧，我这时间不多

dao接口

    @ResultMap("BaseResultMap")
    @Select("select id,name,age,create_date from user where id = #{param1} and name = #{param2}")
    User selectByParamNumXmlMapAnnotation(Long id, String name);

    @Select("select id,name,age,create_date as createDate from user where id = #{id} and name = #{name}")
    User selectByParamNameAnnotation(@Param("id") Long id, @Param("name") String name);

    @Results(id = "userResult", value = {
            @Result(property = "createDate", column = "create_date")
    })
    @Select("select id,name,create_date from user where id = #{id} and name = #{name}")
    User selectByParamNameResultAnnotation(@Param("id") Long id, @Param("name") String name);

    @ResultMap("userResult")
    @Select("select id,name,age,create_date from user where id = #{arg0} and name = #{arg1}")
    User selectByArgNumResultMapAnnotation(Long id, String name);

详细请看文档：http://www.mybatis.org/mybatis-3/zh/java-api.html#directoryStructure

4、用spring-data-jpa 不需要额外配置（复杂的连表查询没测试过）还有一个上级父类 PagingAndSortingRepository，带分页排序的没写

public interface UserRepository extends JpaRepository<User, Long> {

    User findByName(String name);

    User findByNameAndAge(String name, Short age);

    @Query("from User u where u.name like ?1")
    User findUser(String name);

    @Query("from User u where u.name like :name")
    User findUser2(@Param("name") String name);
}

详细请看文档：https://docs.spring.io/spring-data/data-jpa/docs/1.11.14.RELEASE/reference/html/#jpa.query-methods.at-query