Mysql Oracle Mybatis Annotation Sql

database

Use Annotation writes Mysql and Oracle’ sql in Mybatis

前言

     因为经常用Mybatis注解方式写sql，平时经常用到Mysql，Oracle的sql，这两种数据库的sql又稍有不同，在这里总结一下，以后忘了还可以来这里参考一下。也给大伙一个参考。

Mysql数据库

单表批量查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Select(
    "<script>"
        + "SELECT id, column1, column2 FROM tb_name "
        + "WHERE column_id in "
        + "<foreach collection = 'ids' item ='id' open='(' close=')' separator=','>"
        + "#{id,jdbcType=INTEGER}"
        + "</foreach>"
        + "</script>")
@Results(
    id = "resultMap",
    value = {
      @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
      @Result(column = "column1", property = "property1", jdbcType = JdbcType.VARCHAR),
      @Result(column = "column2", property = "property2", jdbcType = JdbcType.VARCHAR)
    })
List<Object> selectByIds(@Param("ids") List<Integer> ids);

单表根据条件查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Select(
    "<script>  "
        + "SELECT id, column1, column2 FROM tb_name "
        + " <where> AND 1=1 "
        + "<if test='property1 != null '>"
        + " AND `column1`  <![CDATA[ >= ]]> #{property1,jdbcType=VARCHAR} "
        + "</if>"
        + "<if test='property2 != null '>"
        + " AND `column2`  <![CDATA[ <= ]]> #{property2,jdbcType=VARCHAR}  "
        + "</if>"
        + " </where> "
        + " </script> ")
@Results(
    id = "resultMap",
    value = {
      @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
      @Result(column = "column1", property = "property1", jdbcType = JdbcType.VARCHAR),
      @Result(column = "column2", property = "property2", jdbcType = JdbcType.VARCHAR)
    })
List<Object> selectByCondition(@Param("property1") String property1, @Param("property2") String property2);

多表一对一查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Select("SELECT column1, column2,per_id FROM tb_name WHERE id=#{id,jdbcType=INTEGER}")
@Results(
    id = "resultMap",
    value = {
      @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
      @Result(column = "column1", property = "property1", jdbcType = JdbcType.VARCHAR),
      @Result(column = "column2", property = "property2", jdbcType = JdbcType.VARCHAR),
      @Result(column = "per_id", property = "person", one = @One(fetchType=FetchType.LAZY,select = "com.mapper.selectPerson"))
    })
Object selectObject(@Param("id") Integer id);

  @Select("SELECT id, column3, column4 FROM tb_name WHERE id=#{pid,jdbcType=INTEGER}")
  @Results(
          id = "personMap",
          value = {
                  @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
                  @Result(column = "column3", property = "property3", jdbcType = JdbcType.VARCHAR),
                  @Result(column = "column4", property = "property4", jdbcType = JdbcType.VARCHAR)
          })
Person selectPerson(@Param("pid") Integer pid);

多表一对多查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Select("SELECT column1, column2,per_id FROM tb_name WHERE id=#{id,jdbcType=INTEGER}")
@Results(
    id = "resultMap",
    value = {
      @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
      @Result(column = "column1", property = "property1", jdbcType = JdbcType.VARCHAR),
      @Result(column = "column2", property = "property2", jdbcType = JdbcType.VARCHAR),
      @Result(column = "per_id", property = "persons", many = @Many(fetchType=FetchType.LAZY,select = "com.mapper.selectListPerson"))
    })
Object selectObject(@Param("id") Integer id);

  @Select("SELECT id, column3, column4 FROM tb_name ")
  @Results(
          id = "resultMap",
          value = {
                  @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
                  @Result(column = "column3", property = "property3", jdbcType = JdbcType.VARCHAR),
                  @Result(column = "column4", property = "property4", jdbcType = JdbcType.VARCHAR)
          })
List<Person> selectListPerson();

批量插入数据

1
2
3
4
5
6
7
8
@Insert(
    " <script> "
        + " INSERT INTO tb_name(column1, column2) VALUES "
        + " <foreach collection='list' item='item' index='index' separator=','> "
        + " (#{item.property1,jdbcType=VARCHAR}, #{item.property2,jdbcType=VARCHAR})"
        + " </foreach> "
        + " </script>")
int insertObject(@Param("list") List<Object> list);

批量删除数据方法

1
2
3
4
5
6
7
8
@Delete(
    " <script> "
        + " DELETE FROM tb_name WHERE id IN "
        + " <foreach collection='ids' item='id' index='index' open='(' close=')' separator=','>"
        + " (#{id,jdbcType=INTEGER})"
        + " </foreach>"
        + " </script>")
int deleteObject(@Param("ids") List<Integer> ids);

批量更新数据方法（1）

1
2
3
4
5
6
7
8
9
 @Update(
 "<script>"
     + "<foreach collection = 'obj' item ='item' open='' close='' separator=';'>"
     + "UPDATE tb_name SET db_filed_name =#{item.entity_name,jdbcType=VARCHAR} "
     + "WHERE  db_filed_name =#{item.entity_name,jdbcType=VARCHAR} "
     + "AND db_filed_name=#{item.entity_name,jdbcType=VARCHAR}"
     + "</foreach>"
     + "</script>")
int updateBatch(@Param("obj") List<Object> objs);

批量更新数据方法（2）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Update({
    "<script> UPDATE tb_name  "
        + "<trim prefix ='set' prefixOverrides=',' > "
        + "<trim prefix ='db_filed_name = case' suffix='end'>"
        + "<foreach collection ='objs' item ='item' index = 'index'> "
        + "WHEN db_filed_name = #{item.entity_name,jdbcType=VARCHAR} THEN #{item.entity_name,jdbcType=VARCHAR} "
        + "</foreach>"
        + "</trim> "
        + "</trim> "
        + "WHERE  db_filed_name IN "
        + "<foreach collection ='objs' item ='items' index ='index' separator=',' open='(' close=')'  > "
        + "#{items.entity_name,jdbcType=VARCHAR} "
        + "</foreach> "
        + "</script>"})
  int updateBatchName(@Param("objs") List<Object> objs);

Oracle数据库

单表查询所有

1
2
3
4
5
6
7
8
9
10
11
12
@Select(
    "<script>"
        + "SELECT ID, COLUMN1, COLUMN2 FROM TB_NAME "
        + "</script>")
@Results(
    id = "resultMap",
    value = {
      @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
      @Result(column = "COLUMN1", property = "property1", jdbcType = JdbcType.VARCHAR),
      @Result(column = "COLUMN2", property = "property2", jdbcType = JdbcType.VARCHAR)
    })
List<Object> queryObjectList();

单表批量查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Select(
    "<script>"
        + "SELECT COLUMN1, COLUMN2 FROM TB_NAME "
        + "WHERE COLUMN_ID in "
        + "<foreach collection = 'ids' item ='item' open='(' close=')' separator=','>"
        + "#{item}"
        + "</foreach>"
        + "</script>")
@Results(
    id = "resultMap",
    value = {
      @Result(column = "ID", property = "id", jdbcType = JdbcType.INTEGER, id = true),
      @Result(column = "COLUMN1", property = "property1", jdbcType = JdbcType.VARCHAR),
      @Result(column = "COLUMN2", property = "property2", jdbcType = JdbcType.VARCHAR)
    })
List<Object> selectByIds(@Param("ids") List<Integer> ids);

单表分页查询

1
2
3
4
5
6
7
8
9
10
11
12
13
@Select(
    "SELECT ID,COLUMN1,COLUMN2 FROM "
        + "(SELECT ROWNUM AS rowno,ID,COLUMN1,COLUMN2"
        + " FROM TB_NAME WHERE rownum<=${rows} ) t "
        + " WHERE t.rowno >= ${page} ")
@Results(
    id = "resultMap",
    value = {
      @Result(column = "ID", property = "id"),
      @Result(column = "COLUMN1", property = "property1"),
      @Result(column = "COLUMN2", property = "property2"),
    })
List<Object> selectPageObject(@Param("page") int page, @Param("rows") int rows);

查询oracle表对象

1
2
@Select("SELECT table_name FROM user_tables WHERE table_name LIKE 'TB_NAME%' ")
String[] selectTables();

单表批量插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
@Insert(
    "<script>"
        + "INSERT INTO TB_NAME(ID,COLUMN1,COLUMN2)"
        + " SELECT S_TB_NAME,A.* FROM ( "
        + " <foreach collection='list' item='item' index='index' separator='UNION ALL'>"
        + " SELECT "
        + " #{item.property1,jdbcType=VARCHAR},"
        + " #{item.property2,jdbcType=VARCHAR}"
        + " FROM dual"
        + " </foreach>"
        + "  ) A"
        + "</script>")
int insertObject1(@Param("list") List<Object> list);

批量删除数据方法

1
2
3
4
5
6
7
8
@Delete(
    " <script> "
        + " DELETE FROM TB_NAME WHERE id IN "
        + " <foreach collection='ids' item='id' index='index' open='(' close=')' separator=','>"
        + " (#{id,jdbcType=INTEGER})"
        + " </foreach>"
        + " </script>")
int deleteObject(@Param("ids") List<Integer> ids);

批量更新数据

1
2
3
4
5
6
7
8
9
@Update(
    "<script>"
        + " <foreach collection='list' item='item' index='index' open='begin' close=';end;' separator=';'>"
        + "UPDATE tb_name SET db_filed_name =#{item.property1} "
        + "WHERE  db_filed_name =#{item.property2} "
        + "AND db_filed_name=#{item.property3}"
        + " </foreach>"
        + " </script>")
int updateBatch(@Param("list") List<Object> list);

延伸

    如果使用IDEA开发工具，可以使用MybatisCodeHelperPro这款插件生成常用sql，提高开发效率，不过目前只能生成下xml格式的sql。暂时还不支持生成注解的sql。涉及分页的推荐使用pagehelper分页插件。这个支持多种数据库，而且使用简单。
    如何使用分页插件
    MyBatis 注解方式批量插入数据库
    mybatis 注解的方式批量插入，更新数据
    Mybatis 使用 MybatisCodeHelperPro插件快速开发 流程

Article Title:Mysql Oracle Mybatis Annotation Sql

Article Author:Funing Ma

Release Time:2019-12-06 18:18:18

Last Update:2020-03-30 10:29:03

Original Link:https://mafuning.github.io/2019/12/06/2019-12-06-Mysql-Oracle-Mybatis-Annotation-Sql/

License Agreement: "CC-BY-SA-4.0"

Content
  1. 1. 前言
  2. 2. Mysql数据库
    1. 2.1. 单表批量查询
    2. 2.2. 单表根据条件查询
    3. 2.3. 多表一对一查询
    4. 2.4. 多表一对多查询
    5. 2.5. 批量插入数据
    6. 2.6. 批量删除数据方法
    7. 2.7. 批量更新数据方法（1）
    8. 2.8. 批量更新数据方法（2）
  3. 3. Oracle数据库
    1. 3.1. 单表查询所有
    2. 3.2. 单表批量查询
    3. 3.3. 单表分页查询
    4. 3.4. 查询oracle表对象
    5. 3.5. 单表批量插入数据
    6. 3.6. 批量删除数据方法
    7. 3.7. 批量更新数据
  4. 4. 延伸