Mysql Oracle Mybatis Annotation Sql
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插件快速开发 流程