当前位置：Gxlcms > 数据库问题 > Mybatis学习（4）输入映射、输出映射、动态sql

Mybatis学习（4）输入映射、输出映射、动态sql

时间：2021-07-01 10:21:17 帮助过：15人阅读

com.cy.po; /** *用户的扩展类 * @author chengyu * */ public class UserCustom extends User{ }

　　视图层面的用户包装类型：

技术分享 
  1. <span style="color: #008080"> 1</span> <span style="color: #0000ff">package</span><span style="color: #000000"> com.cy.po;
  2. </span><span style="color: #008080"> 2</span> 
  3. <span style="color: #008080"> 3</span> <span style="color: #008000">/**</span>
  4. <span style="color: #008080"> 4</span> <span style="color: #008000"> * 用户的包装类型
  5. </span><span style="color: #008080"> 5</span> <span style="color: #008000"> * </span><span style="color: #808080">@author</span><span style="color: #008000"> chengyu
  6. </span><span style="color: #008080"> 6</span> <span style="color: #008000"> *
  7. </span><span style="color: #008080"> 7</span>  <span style="color: #008000">*/</span>
  8. <span style="color: #008080"> 8</span> <span style="color: #0000ff">public</span> <span style="color: #0000ff">class</span><span style="color: #000000"> UserQueryVo {
  9. </span><span style="color: #008080"> 9</span>     <span style="color: #008000">//</span><span style="color: #008000">在这里包装所需要的查询条件
  10. </span><span style="color: #008080">10</span>     
  11. <span style="color: #008080">11</span>     <span style="color: #008000">//</span><span style="color: #008000">用户查询条件</span>
  12. <span style="color: #008080">12</span>     <span style="color: #0000ff">private</span><span style="color: #000000"> UserCustom userCustom;
  13. </span><span style="color: #008080">13</span>     
  14. <span style="color: #008080">14</span>     <span style="color: #008000">//</span><span style="color: #008000">可以包装其它的查询条件，订单、商品
  15. </span><span style="color: #008080">15</span>     <span style="color: #008000">//</span><span style="color: #008000">....</span>
  16. <span style="color: #008080">16</span>         
  17. <span style="color: #008080">17</span>     <span style="color: #0000ff">public</span><span style="color: #000000"> UserCustom getUserCustom() {
  18. </span><span style="color: #008080">18</span>         <span style="color: #0000ff">return</span><span style="color: #000000"> userCustom;
  19. </span><span style="color: #008080">19</span> <span style="color: #000000">    }
  20. </span><span style="color: #008080">20</span> 
  21. <span style="color: #008080">21</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span><span style="color: #000000"> setUserCustom(UserCustom userCustom) {
  22. </span><span style="color: #008080">22</span>         <span style="color: #0000ff">this</span>.userCustom =<span style="color: #000000"> userCustom;
  23. </span><span style="color: #008080">23</span> <span style="color: #000000">    }
  24. </span><span style="color: #008080">24</span> }
View Code

mapper.xml:

  1. <span style="color: #008000"><!--</span><span style="color: #008000"> 用户信息综合查询
  2.     #{userCustom.sex}:取出pojo包装对象中性别值
  3.     ${userCustom.username}：取出pojo包装对象中用户名称
  4.      </span><span style="color: #008000">--></span>
  5.     <span style="color: #0000ff"><</span><span style="color: #800000">select </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="findUserList"</span><span style="color: #ff0000"> parameterType</span><span style="color: #0000ff">="com.cy.po.UserQueryVo"</span><span style="color: #ff0000"> resultType</span><span style="color: #0000ff">="com.cy.po.UserCustom"</span><span style="color: #0000ff">></span><span style="color: #000000">
  6.         SELECT * FROM USER where sex = #{userCustom.sex} and username like ‘%${userCustom.username}%‘
  7.     </span><span style="color: #0000ff"></</span><span style="color: #800000">select</span><span style="color: #0000ff">></span>

mapper.接口：

  1. <span style="color: #0000ff">public</span> <span style="color: #0000ff">interface</span><span style="color: #000000"> UserMapper {
  2.     </span><span style="color: #008000">//</span><span style="color: #008000">用户信息综合查询</span>
  3.     <span style="color: #0000ff">public</span> List<UserCustom> findUserList(UserQueryVo userQueryVo) <span style="color: #0000ff">throws</span> Exception;

测试代码：

技术分享 
  1. <span style="color: #008080"> 1</span> <span style="color: #000000">@Test
  2. </span><span style="color: #008080"> 2</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> testFindUserList() <span style="color: #0000ff">throws</span><span style="color: #000000"> Exception {
  3. </span><span style="color: #008080"> 3</span>         SqlSession sqlSession =<span style="color: #000000"> sqlSessionFactory.openSession();
  4. </span><span style="color: #008080"> 4</span>         UserMapper userMapper = sqlSession.getMapper(UserMapper.<span style="color: #0000ff">class</span><span style="color: #000000">);
  5. </span><span style="color: #008080"> 5</span>         
  6. <span style="color: #008080"> 6</span>         <span style="color: #008000">//</span><span style="color: #008000">创建包装对象，设置查询条件</span>
  7. <span style="color: #008080"> 7</span>         UserQueryVo userQueryVo = <span style="color: #0000ff">new</span><span style="color: #000000"> UserQueryVo();
  8. </span><span style="color: #008080"> 8</span>         UserCustom userCustom = <span style="color: #0000ff">new</span><span style="color: #000000"> UserCustom();
  9. </span><span style="color: #008080"> 9</span>         userCustom.setSex("1"<span style="color: #000000">);
  10. </span><span style="color: #008080">10</span>         userCustom.setUsername("张三丰"<span style="color: #000000">);
  11. </span><span style="color: #008080">11</span> <span style="color: #000000">        userQueryVo.setUserCustom(userCustom);
  12. </span><span style="color: #008080">12</span>         <span style="color: #008000">//</span><span style="color: #008000">调用userMapper的方法</span>
  13. <span style="color: #008080">13</span>         
  14. <span style="color: #008080">14</span>         List<UserCustom> list =<span style="color: #000000"> userMapper.findUserList(userQueryVo);
  15. </span><span style="color: #008080">15</span>         
  16. <span style="color: #008080">16</span> <span style="color: #000000">        System.out.println(list);
  17. </span><span style="color: #008080">17</span>     }
View Code

 

二、输出映射、

 1、resultType:

使用resultType进行输出映射，只有查询出来的列名和pojo中的属性名一致，该列才可以映射成功。

如果查询出来的列名和pojo中的属性名全部不一致，没有创建pojo对象。

只要查询出来的列名和pojo中的属性有一个一致，就会创建pojo对象。

1)  输出简单类型： ----》》查询出来的结果集只有一行且一列，可以使用简单类型进行输出映射。

需求是，用户信息的综合查询列表总数，通过查询总数和上边用户综合查询列表才可以实现分页。

mapper.xml：

  1. <span style="color: #008080">1</span> <span style="color: #008000"><!--</span><span style="color: #008000"> 用户信息综合查询总数
  2. </span><span style="color: #008080">2</span> <span style="color: #008000">    parameterType：指定输入类型和findUserList一样
  3. </span><span style="color: #008080">3</span> <span style="color: #008000">    resultType：输出结果类型
  4. </span><span style="color: #008080">4</span>      <span style="color: #008000">--></span>
  5. <span style="color: #008080">5</span>     <span style="color: #0000ff"><</span><span style="color: #800000">select </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="findUserCount"</span><span style="color: #ff0000"> parameterType</span><span style="color: #0000ff">="com.cy.po.UserQueryVo"</span><span style="color: #ff0000"> resultType</span><span style="color: #0000ff">="int"</span><span style="color: #0000ff">></span>
  6. <span style="color: #008080">6</span> <span style="color: #000000">       SELECT count(*) FROM USER  where sex = #{userCustom.sex} and username like ‘%${userCustom.username}%‘
  7. </span><span style="color: #008080">7</span>     <span style="color: #0000ff"></</span><span style="color: #800000">select</span><span style="color: #0000ff">></span>

mapper.java接口：

  1. <span style="color: #008000">//</span><span style="color: #008000">用户信息综合查询总数</span>
  2.     <span style="color: #0000ff">public</span> <span style="color: #0000ff">int</span> findUserCount(UserQueryVo userQueryVo) <span style="color: #0000ff">throws</span> Exception;

juit测试代码：

技术分享 
  1. <span style="color: #008080"> 1</span> <span style="color: #008000">//</span><span style="color: #008000">测试查询用户数</span>
  2. <span style="color: #008080"> 2</span> <span style="color: #000000">    @Test
  3. </span><span style="color: #008080"> 3</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> testFindUserCount() <span style="color: #0000ff">throws</span><span style="color: #000000"> Exception {
  4. </span><span style="color: #008080"> 4</span>         SqlSession sqlSession =<span style="color: #000000"> sqlSessionFactory.openSession();
  5. </span><span style="color: #008080"> 5</span>         UserMapper userMapper = sqlSession.getMapper(UserMapper.<span style="color: #0000ff">class</span><span style="color: #000000">);
  6. </span><span style="color: #008080"> 6</span>         
  7. <span style="color: #008080"> 7</span>         <span style="color: #008000">//</span><span style="color: #008000">创建包装对象，设置查询条件</span>
  8. <span style="color: #008080"> 8</span>         UserQueryVo userQueryVo = <span style="color: #0000ff">new</span><span style="color: #000000"> UserQueryVo();
  9. </span><span style="color: #008080"> 9</span>         UserCustom userCustom = <span style="color: #0000ff">new</span><span style="color: #000000"> UserCustom();
  10. </span><span style="color: #008080">10</span>         userCustom.setSex("1"<span style="color: #000000">);
  11. </span><span style="color: #008080">11</span>         userCustom.setUsername("张三丰"<span style="color: #000000">);
  12. </span><span style="color: #008080">12</span> <span style="color: #000000">        userQueryVo.setUserCustom(userCustom);
  13. </span><span style="color: #008080">13</span>         <span style="color: #0000ff">int</span> count =<span style="color: #000000"> userMapper.findUserCount(userQueryVo);
  14. </span><span style="color: #008080">14</span> <span style="color: #000000">        System.out.println(count);
  15. </span><span style="color: #008080">15</span>     }
View Code

 

2）输出pojo对象，和pojo对象列表：

不管是输出的pojo单个对象还是一个列表，在mapper.xml中resultType指定的类型是一样的。在mapper.java指定的方法返回值类型不一样；

输出单个pojo，返回值是这个pojo对象类型

输出pojo列表，返回值是List<pojo>

 

2.resultMap

mybatis中使用resultMap完成高级输出结果映射。如果查询出来的列名和pojo的属性名不一致，可以通过定义一个resultMap对列名和pojo属性名之间作一个映射关系。

使用方法啊：

1）定义resultMap

2）使用resultMap作为statement的输出映射类型

mapper.xml:

  1. <span style="color: #008080"> 1</span> <span style="color: #008000"><!--</span><span style="color: #008000">使用mapper代理方法开发，namespace有特殊重要的作用,namespace等于mapper接口地址</span><span style="color: #008000">--></span>
  2. <span style="color: #008080"> 2</span> <span style="color: #0000ff"><</span><span style="color: #800000">mapper </span><span style="color: #ff0000">namespace</span><span style="color: #0000ff">="com.cy.mapper.UserMapper"</span><span style="color: #0000ff">></span>
  3. <span style="color: #008080"> 3</span>     
  4. <span style="color: #008080"> 4</span>     <span style="color: #008000"><!--</span><span style="color: #008000"> 定义resultMap 将SELECT id id_,username username_ FROM USER 和User类中的属性作一个映射关系
  5. </span><span style="color: #008080"> 5</span> <span style="color: #008000">    type：resultMap最终映射的java对象类型,可以使用别名
  6. </span><span style="color: #008080"> 6</span> <span style="color: #008000">    id：对resultMap的唯一标识
  7. </span><span style="color: #008080"> 7</span>      <span style="color: #008000">--></span>
  8. <span style="color: #008080"> 8</span>      <span style="color: #0000ff"><</span><span style="color: #800000">resultMap </span><span style="color: #ff0000">type</span><span style="color: #0000ff">="com.cy.po.User"</span><span style="color: #ff0000"> id</span><span style="color: #0000ff">="userResultMap"</span><span style="color: #0000ff">></span>
  9. <span style="color: #008080"> 9</span>          <span style="color: #008000"><!--</span><span style="color: #008000"> id表示查询结果集中唯一标识  column：查询出来的列名 property：type指定的pojo类型中的属性名</span><span style="color: #008000">--></span>
  10. <span style="color: #008080">10</span>          <span style="color: #0000ff"><</span><span style="color: #800000">id </span><span style="color: #ff0000">column</span><span style="color: #0000ff">="id_"</span><span style="color: #ff0000"> property</span><span style="color: #0000ff">="id"</span><span style="color: #0000ff">/></span>
  11. <span style="color: #008080">11</span>          
  12. <span style="color: #008080">12</span>          <span style="color: #008000"><!--</span><span style="color: #008000"> result：对普通名映射定义 column：查询出来的列名 property：type指定的pojo类型中的属性名 </span><span style="color: #008000">--></span>
  13. <span style="color: #008080">13</span>          <span style="color: #0000ff"><</span><span style="color: #800000">result </span><span style="color: #ff0000">column</span><span style="color: #0000ff">="username_"</span><span style="color: #ff0000"> property</span><span style="color: #0000ff">="username"</span><span style="color: #0000ff">/></span>
  14. <span style="color: #008080">14</span>      <span style="color: #0000ff"></</span><span style="color: #800000">resultMap</span><span style="color: #0000ff">></span>
  15. <span style="color: #008080">15</span>      
  16. <span style="color: #008080">16</span>      <span style="color: #008000"><!--</span><span style="color: #008000"> 使用resultMap进行输出映射
  17. </span><span style="color: #008080">17</span> <span style="color: #008000">    resultMap：指定定义的resultMap的id，如果这个resultMap在其它的mapper文件，前边需要加namespace
  18. </span><span style="color: #008080">18</span>     <span style="color: #008000">--></span>
  19. <span style="color: #008080">19</span>     <span style="color: #0000ff"><</span><span style="color: #800000">select </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="findUserByIdResultMap"</span><span style="color: #ff0000"> parameterType</span><span style="color: #0000ff">="int"</span><span style="color: #ff0000"> resultMap</span><span style="color: #0000ff">="userResultMap"</span><span style="color: #0000ff">></span>
  20. <span style="color: #008080">20</span> <span style="color: #000000">        SELECT id id_,username username_ FROM USER WHERE id=#{value}
  21. </span><span style="color: #008080">21</span>     <span style="color: #0000ff"></</span><span style="color: #800000">select</span><span style="color: #0000ff">></span>
  22. <span style="color: #008080">22</span> <span style="color: #0000ff"></</span><span style="color: #800000">mapper</span><span style="color: #0000ff">></span>

mapper接口：

  1. <span style="color: #008000">//</span><span style="color: #008000">根据id查询用户信息，使用resultMap输出</span>
  2. <span style="color: #0000ff">public</span> User findUserByIdResultMap(<span style="color: #0000ff">int</span> id) <span style="color: #0000ff">throws</span> Exception;

juittest代码：

  1. <span style="color: #000000">@Test
  2.     </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> testFindUserByIdResultMap() <span style="color: #0000ff">throws</span><span style="color: #000000"> Exception {
  3.         SqlSession sqlSession </span>=<span style="color: #000000"> sqlSessionFactory.openSession();
  4.         UserMapper userMapper </span>= sqlSession.getMapper(UserMapper.<span style="color: #0000ff">class</span><span style="color: #000000">);
  5.         
  6.         User user </span>= userMapper.findUserByIdResultMap(1<span style="color: #000000">);
  7.         System.out.println(user);
  8.     }</span>

 3.自己做的实验，输入类型和传出类型都为java.util.Map:

  1. <span style="color: #008000"><!--</span><span style="color: #008000"> 查看resultMap的使用 java.util.Map 
  2.         传入参数为hashmap id和username都为map的key
  3.     </span><span style="color: #008000">--></span>
  4.     <span style="color: #0000ff"><</span><span style="color: #800000">select </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="findUserByIdDefaultMap"</span><span style="color: #ff0000"> parameterType</span><span style="color: #0000ff">="java.util.Map"</span><span style="color: #ff0000"> resultType</span><span style="color: #0000ff">="java.util.Map"</span><span style="color: #0000ff">></span><span style="color: #000000">
  5.         SELECT * FROM USER where id=#{id} and username like ‘%${username}%‘
  6.     </span><span style="color: #0000ff"></</span><span style="color: #800000">select</span><span style="color: #0000ff">></span>

并且java.util.Map在mybatis中的别名是hashmap，写成这样，效果一样的：

  1. <span style="color: #0000ff"><</span><span style="color: #800000">select </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="findUserByIdDefaultMap"</span><span style="color: #ff0000"> parameterType</span><span style="color: #0000ff">="hashmap"</span><span style="color: #ff0000"> resultType</span><span style="color: #0000ff">="hashmap"</span><span style="color: #0000ff">></span><span style="color: #000000">
  2.         SELECT * FROM USER where id=#{id} and username like ‘%${username}%‘
  3.     </span><span style="color: #0000ff"></</span><span style="color: #800000">select</span><span style="color: #0000ff">></span>

mapper接口：

  1. <span style="color: #008000">//</span><span style="color: #008000">find user parameterMap and resultMap both are java.uti.Map</span>
  2. <span style="color: #0000ff">public</span> Map<String, String> findUserByIdDefaultMap(Map<String, String> parmas) <span style="color: #0000ff">throws</span> Exception;

测试代码：

  1. <span style="color: #000000">@Test 
  2.     </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> testfindUserByIdDefaultMap() <span style="color: #0000ff">throws</span><span style="color: #000000"> Exception{
  3.         SqlSession sqlSession </span>=<span style="color: #000000"> sqlSessionFactory.openSession();
  4.         UserMapper userMapper </span>= sqlSession.getMapper(UserMapper.<span style="color: #0000ff">class</span><span style="color: #000000">);
  5.         
  6.         Map</span><String ,String> params = <span style="color: #0000ff">new</span> HashMap<String, String><span style="color: #000000">();
  7.         params.put(</span>"id", "25"<span style="color: #000000">);
  8.         params.put(</span>"username", "小明"<span style="color: #000000">);
  9.         Map</span><String, String> user =<span style="color: #000000"> userMapper.findUserByIdDefaultMap(params);
  10.         System.out.println(user);
  11.     }</span>

打印结果：

  1. DEBUG [main] - ==>  Preparing: SELECT * FROM USER where id=? and username like ‘%小明%‘ 
  2. DEBUG [main] - ==> Parameters: 25(String)
  3. DEBUG [main] - <==      Total: 1
  4. {id=25, sex=1, username=陈小明, address=河南郑州}

　　

三、动态sql

mybatis核心 对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接、组装。

例子1：

　　需求: 对上面程序中的  用户信息综合查询列表这个statement使用动态sql，对查询条件进行判断，如果输入参数不为空才进行查询条件拼接。

mapper.xml:

  1. <span style="color: #008000"><!--</span><span style="color: #008000"> 用户信息综合查询
  2.     #{userCustom.sex}:取出pojo包装对象中性别值
  3.     ${userCustom.username}：取出pojo包装对象中用户名称
  4.      </span><span style="color: #008000">--></span>
  5.     <span style="color: #0000ff"><</span><span style="color: #800000">select </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="findUserList"</span><span style="color: #ff0000"> parameterType</span><span style="color: #0000ff">="com.cy.po.UserQueryVo"</span><span style="color: #ff0000"> resultType</span><span style="color: #0000ff">="com.cy.po.UserCustom"</span><span style="color: #0000ff">></span><span style="color: #000000">
  6.         SELECT * FROM USER
  7.         
  8.         </span><span style="color: #008000"><!--</span><span style="color: #008000"> where可以自动去掉条件中的第一个and </span><span style="color: #008000">--></span>
  9.         <span style="color: #0000ff"><</span><span style="color: #800000">where</span><span style="color: #0000ff">></span>
  10.             <span style="color: #0000ff"><</span><span style="color: #800000">if </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="userCustom!=null"</span><span style="color: #0000ff">></span>
  11.                 <span style="color: #0000ff"><</span><span style="color: #800000">if </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="userCustom.sex!=null and userCustom.sex!=‘‘"</span><span style="color: #0000ff">></span><span style="color: #000000">
  12.                     and sex = #{userCustom.sex}
  13.                 </span><span style="color: #0000ff"></</span><span style="color: #800000">if</span><span style="color: #0000ff">></span>
  14.                 <span style="color: #0000ff"><</span><span style="color: #800000">if </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="userCustom.username!=null and userCustom.username!=‘‘"</span><span style="color: #0000ff">></span><span style="color: #000000">
  15.                     and username like ‘%${userCustom.username}%‘
  16.                 </span><span style="color: #0000ff"></</span><span style="color: #800000">if</span><span style="color: #0000ff">></span>
  17.             <span style="color: #0000ff"></</span><span style="color: #800000">if</span><span style="color: #0000ff">></span>
  18.         <span style="color: #0000ff"></</span><span style="color: #800000">where</span><span style="color: #0000ff">></span>
  19.     <span style="color: #0000ff"></</span><span style="color: #800000">select</span><span style="color: #0000ff">></span>

测试代码：

技术分享 
  1. <span style="color: #008080"> 1</span> <span style="color: #000000">@Test
  2. </span><span style="color: #008080"> 2</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> testFindUserList() <span style="color: #0000ff">throws</span><span style="color: #000000"> Exception {
  3. </span><span style="color: #008080"> 3</span>         SqlSession sqlSession =<span style="color: #000000"> sqlSessionFactory.openSession();
  4. </span><span style="color: #008080"> 4</span>         UserMapper userMapper = sqlSession.getMapper(UserMapper.<span style="color: #0000ff">class</span><span style="color: #000000">);
  5. </span><span style="color: #008080"> 5</span>         
  6. <span style="color: #008080"> 6</span>         <span style="color: #008000">//</span><span style="color: #008000">创建包装对象，设置查询条件</span>
  7. <span style="color: #008080"> 7</span>         UserQueryVo userQueryVo = <span style="color: #0000ff">new</span><span style="color: #000000"> UserQueryVo();
  8. </span><span style="color: #008080"> 8</span>         UserCustom userCustom = <span style="color: #0000ff">new</span><span style="color: #000000"> UserCustom();
  9. </span><span style="color: #008080"> 9</span>         
  10. <span style="color: #008080">10</span>         <span style="color: #008000">//</span><span style="color: #008000">动态sql的存在，不设置某个值，条件不会拼接在sql中
  11. </span><span style="color: #008080">11</span>         <span style="color: #008000">//</span><span style="color: #008000">userCustom.setSex("1");</span>
  12. <span style="color: #008080">12</span>         userCustom.setUsername("小明"<span style="color: #000000">);
  13. </span><span style="color: #008080">13</span> <span style="color: #000000">        userQueryVo.setUserCustom(userCustom);
  14. </span><span style="color: #008080">14</span>         
  15. <span style="color: #008080">15</span>         List<UserCustom> list =<span style="color: #000000"> userMapper.findUserList(userQueryVo);
  16. </span><span style="color: #008080">16</span> <span style="color: #000000">        System.out.println(list);
  17. </span><span style="color: #008080">17</span>     }
View Code

打印：

DEBUG [main] - ==> Preparing: SELECT * FROM USER WHERE username like ‘%小明%‘
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 3
[------->> User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州], ------->> User [id=22, username=陈小明, sex=1, birthday=null, address=河南郑州], ------->> User [id=25, username=陈小明, sex=1, birthday=null, address=河南郑州]]

 

2）sql片段：

将上边实现的动态sql判断代码块抽取出来，组成一个sql片段。其它的statement中就可以引用sql片段。

mapper.xml：----定义sql片段、引用sql片段：

  1. <span style="color: #0000ff"><</span><span style="color: #800000">mapper </span><span style="color: #ff0000">namespace</span><span style="color: #0000ff">="com.cy.mapper.UserMapper"</span><span style="color: #0000ff">></span>
  2.     
  3.     <span style="color: #008000"><!--</span><span style="color: #008000"> 定义sql片段id：sql片段的唯 一标识
  4.          经验：是基于单表来定义sql片段，这样话这个sql片段可重用性才高;在sql片段中不要包括 where
  5.     </span><span style="color: #008000">--></span>
  6.     <span style="color: #0000ff"><</span><span style="color: #800000">sql </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="query_user_where"</span><span style="color: #0000ff">></span>
  7.         <span style="color: #0000ff"><</span><span style="color: #800000">if </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="userCustom!=null"</span><span style="color: #0000ff">></span>
  8.             <span style="color: #0000ff"><</span><span style="color: #800000">if </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="userCustom.sex!=null and userCustom.sex!=‘‘"</span><span style="color: #0000ff">></span><span style="color: #000000">
  9.                 and sex = #{userCustom.sex}
  10.             </span><span style="color: #0000ff"></</span><span style="color: #800000">if</span><span style="color: #0000ff">></span>
  11.             <span style="color: #0000ff"><</span><span style="color: #800000">if </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="userCustom.username!=null and userCustom.username!=‘‘"</span><span style="color: #0000ff">></span><span style="color: #000000">
  12.                 and username like ‘%${userCustom.username}%‘
  13.             </span><span style="color: #0000ff"></</span><span style="color: #800000">if</span><span style="color: #0000ff">></span>
  14.         <span style="color: #0000ff"></</span><span style="color: #800000">if</span><span style="color: #0000ff">></span>
  15.     <span style="color: #0000ff"></</span><span style="color: #800000">sql</span><span style="color: #0000ff">></span>
  16.     <span style="color: #008000"><!--</span><span style="color: #008000"> 用户信息综合查询
  17.     #{userCustom.sex}:取出pojo包装对象中性别值
  18.     ${userCustom.username}：取出pojo包装对象中用户名称
  19.      </span><span style="color: #008000">--></span>
  20.     <span style="color: #0000ff"><</span><span style="color: #800000">select </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="findUserList"</span><span style="color: #ff0000"> parameterType</span><span style="color: #0000ff">="com.cy.po.UserQueryVo"</span><span style="color: #ff0000"> resultType</span><span style="color: #0000ff">="com.cy.po.UserCustom"</span><span style="color: #0000ff">></span><span style="color: #000000">
  21.         SELECT * FROM USER
  22.         </span><span style="color: #0000ff"><</span><span style="color: #800000">where</span><span style="color: #0000ff">></span>
  23.             <span style="color: #008000"><!--</span><span style="color: #008000"> 引用sql片段 的id，如果refid指定的id不在本mapper文件中，需要前边加namespace </span><span style="color: #008000">--></span>
  24.             <span style="color: #0000ff"><</span><span style="color: #800000">include </span><span style="color: #ff0000">refid</span><span style="color: #0000ff">="query_user_where"</span><span style="color: #0000ff">></</span><span style="color: #800000">include</span><span style="color: #0000ff">></span>
  25.         <span style="color: #0000ff"></</span><span style="color: #800000">where</span><span style="color: #0000ff">></span>
  26.     <span style="color: #0000ff"></</span><span style="color: #800000">select</span><span style="color: #0000ff">></span>
  27. <span style="color: #0000ff"></</span><span style="color: #800000">mapper</span><span style="color: #0000ff">></span>

 3)使用foreach标签：

向sql传递数组或List，mybatis使用foreach解析；

需求：在用户查询列表的statement中增加多个id输入查询。

输入参数类型UserQueryVo中添加ids List，根据这个list查：

技术分享 
  1. <span style="color: #008080"> 1</span> <span style="color: #0000ff">package</span><span style="color: #000000"> com.cy.po;
  2. </span><span style="color: #008080"> 2</span> 
  3. <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> java.util.List;
  4. </span><span style="color: #008080"> 4</span> 
  5. <span style="color: #008080"> 5</span> <span style="color: #008000">/**</span>
  6. <span style="color: #008080"> 6</span> <span style="color: #008000"> * 用户的包装类型
  7. </span><span style="color: #008080"> 7</span> <span style="color: #008000"> * </span><span style="color: #808080">@author</span><span style="color: #008000"> chengyu
  8. </span><span style="color: #008080"> 8</span> <span style="color: #008000"> *
  9. </span><span style="color: #008080"> 9</span>  <span style="color: #008000">*/</span>
  10. <span style="color: #008080">10</span> <span style="color: #0000ff">public</span> <span style="color: #0000ff">class</span><span style="color: #000000"> UserQueryVo {
  11. </span><span style="color: #008080">11</span>     <span style="color: #008000">//</span><span style="color: #008000">在这里包装所需要的查询条件</span>
  12. <span style="color: #008080">12</span>     
  13. <span style="color: #008080">13</span>     <span style="color: #0000ff">private</span> List<Integer><span style="color: #000000"> ids;
  14. </span><span style="color: #008080">14</span>     
  15. <span style="color: #008080">15</span>     <span style="color: #008000">//</span><span style="color: #008000">用户查询条件</span>
  16. <span style="color: #008080">16</span>     <span style="color: #0000ff">private</span><span style="color: #000000"> UserCustom userCustom;
  17. </span><span style="color: #008080">17</span>     
  18. <span style="color: #008080">18</span>     <span style="color: #008000">//</span><span style="color: #008000">可以包装其它的查询条件，订单、商品
  19. </span><span style="color: #008080">19</span>     <span style="color: #008000">//</span><span style="color: #008000">....</span>
  20. <span style="color: #008080">20</span>     
  21. <span style="color: #008080">21</span>     <span style="color: #0000ff">public</span> List<Integer><span style="color: #000000"> getIds() {
  22. </span><span style="color: #008080">22</span>         <span style="color: #0000ff">return</span><span style="color: #000000"> ids;
  23. </span><span style="color: #008080">23</span> <span style="color: #000000">    }
  24. </span><span style="color: #008080">24</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> setIds(List<Integer><span style="color: #000000"> ids) {
  25. </span><span style="color: #008080">25</span>         <span style="color: #0000ff">this</span>.ids =<span style="color: #000000"> ids;
  26. </span><span style="color: #008080">26</span> <span style="color: #000000">    }
  27. </span><span style="color: #008080">27</span>     <span style="color: #0000ff">public</span><span style="color: #000000"> UserCustom getUserCustom() {
  28. </span><span style="color: #008080">28</span>         <span style="color: #0000ff">return</span><span style="color: #000000"> userCustom;
  29. </span><span style="color: #008080">29</span> <span style="color: #000000">    }
  30. </span><span style="color: #008080">30</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span><span style="color: #000000"> setUserCustom(UserCustom userCustom) {
  31. </span><span style="color: #008080">31</span>         <span style="color: #0000ff">this</span>.userCustom =<span style="color: #000000"> userCustom;
  32. </span><span style="color: #008080">32</span> <span style="color: #000000">    }
  33. </span><span style="color: #008080">33</span> }
View Code

mapper.xml:

  1.     <span style="color: #0000ff"><</span><span style="color: #800000">sql </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="query_user_where"</span><span style="color: #0000ff">></span>
  2.         <span style="color: #0000ff"><</span><span style="color: #800000">if </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="userCustom!=null"</span><span style="color: #0000ff">></span>
  3.             <span style="color: #0000ff"><</span><span style="color: #800000">if </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="userCustom.sex!=null and userCustom.sex!=‘‘"</span><span style="color: #0000ff">></span><span style="color: #000000">
  4.                 and sex = #{userCustom.sex}
  5.             </span><span style="color: #0000ff"></</span><span style="color: #800000">if</span><span style="color: #0000ff">></span>
  6.             <span style="color: #0000ff"><</span><span style="color: #800000">if </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="userCustom.username!=null and userCustom.username!=‘‘"</span><span style="color: #0000ff">></span><span style="color: #000000">
  7.                 and username like ‘%${userCustom.username}%‘
  8.             </span><span style="color: #0000ff"></</span><span style="color: #800000">if</span><span style="color: #0000ff">></span>
  9.             <span style="color: #0000ff"><</span><span style="color: #800000">if </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="ids!=null"</span><span style="color: #0000ff">></span>
  10.                 <span style="color: #008000"><!--</span><span style="color: #008000"> 使用 foreach遍历传入ids
  11.                 collection：指定输入 对象中集合属性
  12.                 item：每个遍历生成对象中
  13.                 open：开始遍历时拼接的串
  14.                 close：结束遍历时拼接的串
  15.                 separator：遍历的两个对象中需要拼接的串
  16.                  </span><span style="color: #008000">--></span>
  17.                  
  18.                 <span style="color: #008000"><!--</span><span style="color: #008000"> AND (id=1 OR id=10 OR id=16)</span><span style="color: #008000">--></span>
  19.                 <span style="color: #0000ff"><</span><span style="color: #800000">foreach </span><span style="color: #ff0000">collection</span><span style="color: #0000ff">="ids"</span><span style="color: #ff0000"> item</span><span style="color: #0000ff">="user_id"</span><span style="color: #ff0000"> open</span><span style="color: #0000ff">="and ("</span><span style="color: #ff0000"> close</span><span style="color: #0000ff">=")"</span><span style="color: #ff0000"> separator</span><span style="color: #0000ff">="or"</span><span style="color: #0000ff">></span><span style="color: #000000">
  20.                     id=#{user_id}
  21.                 </span><span style="color: #0000ff"></</span><span style="color: #800000">foreach</span><span style="color: #0000ff">></span>
  22.                 
  23.                 <span style="color: #008000"><!--</span><span style="color: #008000"> and id IN(1,10,16) </span><span style="color: #008000">--></span>
  24.                 <span style="color: #008000"><!--</span><span style="color: #008000"> <foreach collection="ids" item="user_id" open="and id in(" close=")" separator=",">
  25.                     #{user_id}
  26.                 </foreach> </span><span style="color: #008000">--></span>
  27.             <span style="color: #0000ff"></</span><span style="color: #800000">if</span><span style="color: #0000ff">></span>
  28.         <span style="color: #0000ff"></</span><span style="color: #800000">if</span><span style="color: #0000ff">></span>
  29.     <span style="color: #0000ff"></</span><span style="color: #800000">sql</span><span style="color: #0000ff">></span>
  30.     <span style="color: #008000"><!--</span><span style="color: #008000"> 用户信息综合查询
  31.     #{userCustom.sex}:取出pojo包装对象中性别值
  32.     ${userCustom.username}：取出pojo包装对象中用户名称
  33.      </span><span style="color: #008000">--></span>
  34.     <span style="color: #0000ff"><</span><span style="color: #800000">select </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="findUserList"</span><span style="color: #ff0000"> parameterType</span><span style="color: #0000ff">="com.cy.po.UserQueryVo"</span><span style="color: #ff0000"> resultType</span><span style="color: #0000ff">="com.cy.po.UserCustom"</span><span style="color: #0000ff">></span><span style="color: #000000">
  35.         SELECT * FROM USER
  36.         </span><span style="color: #0000ff"><</span><span style="color: #800000">where</span><span style="color: #0000ff">></span>
  37.             <span style="color: #008000"><!--</span><span style="color: #008000"> 引用sql片段 的id，如果refid指定的id不在本mapper文件中，需要前边加namespace </span><span style="color: #008000">--></span>
  38.             <span style="color: #0000ff"><</span><span style="color: #800000">include </span><span style="color: #ff0000">refid</span><span style="color: #0000ff">="query_user_where"</span><span style="color: #0000ff">></</span><span style="color: #800000">include</span><span style="color: #0000ff">></span>
  39.         <span style="color: #0000ff"></</span><span style="color: #800000">where</span><span style="color: #0000ff">></span>
  40.     <span style="color: #0000ff"></</span><span style="color: #800000">select</span><span style="color: #0000ff">></span>

测试代码：

技术分享 
  1. <span style="color: #008080"> 1</span> <span style="color: #000000">@Test
  2. </span><span style="color: #008080"> 2</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> testFindUserList() <span style="color: #0000ff">throws</span><span style="color: #000000"> Exception {
  3. </span><span style="color: #008080"> 3</span>         SqlSession sqlSession =<span style="color: #000000"> sqlSessionFactory.openSession();
  4. </span><span style="color: #008080"> 4</span>         UserMapper userMapper = sqlSession.getMapper(UserMapper.<span style="color: #0000ff">class</span><span style="color: #000000">);
  5. </span><span style="color: #008080"> 5</span>         
  6. <span style="color: #008080"> 6</span>         <span style="color: #008000">//</span><span style="color: #008000">创建包装对象，设置查询条件</span>
  7. <span style="color: #008080"> 7</span>         UserQueryVo userQueryVo = <span style="color: #0000ff">new</span><span style="color: #000000"> UserQueryVo();
  8. </span><span style="color: #008080"> 8</span>         UserCustom userCustom = <span style="color: #0000ff">new</span><span style="color: #000000"> UserCustom();
  9. </span><span style="color: #008080"> 9</span>         
  10. <span style="color: #008080">10</span>         <span style="color: #008000">//</span><span style="color: #008000">动态sql的存在，不设置某个值，条件不会拼接在sql中
  11. </span><span style="color: #008080">11</span>         <span style="color: #008000">//</span><span style="color: #008000">userCustom.setSex("1");</span>
  12. <span style="color: #008080">12</span>         userCustom.setUsername("小明"<span style="color: #000000">);
  13. </span><span style="color: #008080">13</span>         
  14. <span style="color: #008080">14</span>         <span style="color: #008000">//</span><span style="color: #008000">传入多个id</span>
  15. <span style="color: #008080">15</span>         List<Integer> ids = <span style="color: #0000ff">new</span> ArrayList<Integer><span style="color: #000000">();
  16. </span><span style="color: #008080">16</span>         ids.add(1<span style="color: #000000">);
  17. </span><span style="color: #008080">17</span>         ids.add(10<span style="color: #000000">);
  18. </span><span style="color: #008080">18</span>         ids.add(16<span style="color: #000000">);
  19. </span><span style="color: #008080">19</span> <span style="color: #000000">        userQueryVo.setIds(ids);
  20. </span><span style="color: #008080">20</span> <span style="color: #000000">        userQueryVo.setUserCustom(userCustom);
  21. </span><span style="color: #008080">21</span>         
  22. <span style="color: #008080">22</span>         List<UserCustom> list =<span style="color: #000000"> userMapper.findUserList(userQueryVo);
  23. </span><span style="color: #008080">23</span> <span style="color: #000000">        System.out.println(list);
  24. </span><span style="color: #008080">24</span>     }
View Code

可以看打印结果拼接的sql:

DEBUG [main] - ==> Preparing: SELECT * FROM USER WHERE username like ‘%小明%‘ and ( id=? or id=? or id=? )
DEBUG [main] - ==> Parameters: 1(Integer), 10(Integer), 16(Integer)
DEBUG [main] - <== Total: 1
[------->> User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州]]

 

Mybatis学习（4）输入映射、输出映射、动态sql

标签：port   start   需要   add   表达式   核心   pre   就会   动态sql   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！