简述

MyBatis是一种ORM框架，即：对象关系映射。是指数据库和Java对象可相互转化，这是基于JDBC的二次开发。与JDBC不同的是，MyBatis在完成一些配置之后，需要做得仅仅是在一个文件中写sql语句。通过反向代理可大大减轻开发过程中代码冗余的编写。MyBatis的主要内容包括输入映射、输出映射、动态标签库、多表操作语句、MyBatis缓存。

前提

image.png

导包

导入需要用到的依赖。pom.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5.     <modelVersion>4.0.0</modelVersion>
  7.     <groupId>com.simon</groupId>
  8.     <artifactId>my-batis-qucik-start</artifactId>
  9.     <version>1.0-SNAPSHOT</version>
  11.     <dependencies>
  12.         <dependency>
  13.             <groupId>mysql</groupId>
  14.             <artifactId>mysql-connector-java</artifactId>
  15.             <version>5.1.47</version>
  16.         </dependency>
  17.         <dependency>
  18.             <groupId>org.mybatis</groupId>
  19.             <artifactId>mybatis</artifactId>
  20.             <version>3.5.7</version>
  21.         </dependency>
  22.         <dependency>
  23.             <groupId>junit</groupId>
  24.             <artifactId>junit</artifactId>
  25.             <version>4.12</version>
  26.             <scope>test</scope>
  27.         </dependency>
  28.     </dependencies>
  30.     <!--Maven增强，是为了让Mapper.xml和Mapper接口位于src的同一目录下，当然编译之后还是在同一目录下-->
  31.     <build>
  32.         <resources>
  33.             <resource>
  34.                 <directory>src/main/resources</directory>
  35.                 <includes>
  36.                     <include>**</include>
  37.                 </includes>
  38.             </resource>
  40.             <resource>
  41.                 <directory>src/main/java</directory>
  42.                 <includes>
  43.                     <include>
  44.                         **/*.xml
  45.                     </include>
  46.                 </includes>
  47.             </resource>
  48.         </resources>
  49.     </build>
  50. </project>

配置

MyBatis.xml，MyBatis接口

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE configuration
  3.         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  5. <configuration>
  6.     <properties resource="jdbc.properties"/>
  7.     <settings>
  8.         <setting name="logImpl" value="STDOUT_LOGGING"/>
  9.     </settings>
  10.     <typeAliases>
  11.         <!--<package name="com.simon.bean"/>-->
  12.         <typeAlias type="com.simon.bean.User" alias="user"/>
  13.     </typeAliases>
  14.     <environments default="development">
  15.         <environment id="development">
  16.             <transactionManager type="JDBC"/>
  17.             <dataSource type="POOLED">
  18.                 <property name="driver" value="${driverClassName}"/>
  19.                 <property name="url" value="${url}"/>
  20.                 <property name="username" value="${username}"/>
  21.                 <property name="password" value="${password}"/>
  22.             </dataSource>
  23.         </environment>
  24.     </environments>
  25.     <mappers>
  26.         <mapper resource="com/simon/Mapper.xml"/>
  27.     </mappers>
  28. </configuration>
  1. driverClassName=com.mysql.jdbc.Driver
  2. url=jdbc:mysql://localhost:3306/29_db1?useSSL=false&characterEncoding=utf8
  3. username=root
  4. password=123456
  1. package com.simon.bean;
  3. public class User {
  4.     private Integer id;
  5.     private String username;
  6.     private String password;
  7.     private Integer age;
  9.     @Override
  10.     public String toString() {
  11.         return "User{" +
  12.                 "id=" + id +
  13.                 ", username='" + username + '\'' +
  14.                 ", password='" + password + '\'' +
  15.                 ", age=" + age +
  16.                 '}';
  17.     }
  19.     public Integer getId() {
  20.         return id;
  21.     }
  23.     public void setId(Integer id) {
  24.         this.id = id;
  25.     }
  27.     public String getUsername() {
  28.         return username;
  29.     }
  31.     public void setUsername(String username) {
  32.         this.username = username;
  33.     }
  35.     public String getPassword() {
  36.         return password;
  37.     }
  39.     public void setPassword(String password) {
  40.         this.password = password;
  41.     }
  43.     public Integer getAge() {
  44.         return age;
  45.     }
  47.     public void setAge(Integer age) {
  48.         this.age = age;
  49.     }
  50. }

简单的增删改查

  1. package com.simon;
  3. import com.simon.bean.User;
  5. import java.util.List;
  7. public interface Mapper {
  8.     User selectUser();
  9.     void insertUser(User user);
  10.     void deleteUserById(Integer id1,Integer id2,Integer id3);
  11.     void updateUserById(Integer id);
  12.     List<User> selectUserList();
  13. }
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.simon.Mapper">
  7.     <select id="selectUser" resultType="user">
  8.     select * from user where id = 1
  9.   </select>
  11.     <insert id="insertUser" parameterType="user">
  12.         insert into user values(#{id},#{username},#{password},#{age})
  13.     </insert>
  15.     <delete id="deleteUserById">
  16.         delete  from user where id = #{param1} or id = #{param2} or id = #{param3}
  17.     </delete>
  19.     <update id="updateUserById">
  20.         update user set age = 99 where id = #{id}
  21.     </update>
  23.     <select id="selectUserList" resultType="user">
  24.         select * from user
  25.     </select>
  27. </mapper>

主要内容

输入映射

image.png

  • pom.xml
  • mybatis-config.xml
  • jdbc.properties
  • User类添加注解别名：”user” ```java package com.simon;

import com.simon.bean.User; import org.apache.ibatis.annotations.Param;

import java.util.List; import java.util.Map;

public interface Mapper { User selectUserById(Integer id); User selectUserById2(@Param(“uid”) Integer id);

  1. User selectUserByIdAndUsername(@Param("id") Integer id,
  2.                                @Param("name") String username);
  4. Integer insertUserByObject(@Param("user") User user);
  6. List<User> selectUserByIdOrUsername(@Param("map") Map map);
  8. List<User> selectUserByIndex(Integer id,String username);
  10. List<User> selectUserByTableName(@Param("tableName") String tableName);
  12. List<User> selectUserByColumnName(@Param("columnName") String columnName);

}

  1. ```xml
  2. <?xml version="1.0" encoding="UTF-8" ?>
  3. <!DOCTYPE mapper
  4.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  5.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  6. <mapper namespace="com.simon.Mapper">
  8.     <select id="selectUserById" resultType="user">
  9.         select * from user where id = #{id}
  10.     </select>
  12.     <select id="selectUserById2" resultType="user">
  13.         select * from user where id = #{uid}
  14.     </select>
  16.     <select id="selectUserByIdAndUsername" resultType="user">
  17.         select * from user where id = #{id} and username = #{name}
  18.     </select>
  20.     <insert id="insertUserByObject" parameterType="user">
  21.         insert into user values(#{user.id},#{user.username},#{user.password},#{user.age})
  22.     </insert>
  24.     <select id="selectUserByIdOrUsername" resultType="user">
  25.         select * from user where id = #{map.id} or username = #{map.username}
  26.     </select>
  28.     <select id="selectUserByIndex"  resultType="user">
  29.         select * from user where id = #{param1} or username = #{param2}
  30.     </select>
  32.     <select id="selectUserByTableName" resultType="user">
  33.         select * from ${tableName} limit 10
  34.     </select>
  36.     <select id="selectUserByColumnName" resultType="user">
  37.         select * from user group by ${columnName}
  38.     </select>
  40. </mapper>
  1. package com.simon;
  3. import com.simon.bean.User;
  4. import org.apache.ibatis.io.Resources;
  5. import org.apache.ibatis.session.SqlSession;
  6. import org.apache.ibatis.session.SqlSessionFactory;
  7. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  8. import org.junit.AfterClass;
  9. import org.junit.BeforeClass;
  10. import org.junit.Test;
  12. import java.io.IOException;
  13. import java.io.InputStream;
  14. import java.util.HashMap;
  15. import java.util.List;
  17. public class ParamTest {
  19.     public static SqlSession sqlSession = null;
  20.     static Mapper mapper;
  22.     @BeforeClass
  23.     public static void init() throws IOException {
  24.         InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  25.         SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
  26.         SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
  27.         sqlSession = sqlSessionFactory.openSession();
  28.         mapper = sqlSession.getMapper(Mapper.class);
  29.     }
  31.     @AfterClass
  32.     public static void destory(){
  33.         sqlSession.commit();
  34.         sqlSession.close();
  36.     }
  40.     //一个不带别名简单类型的入参
  41.     @Test
  42.     public void testSelectUserById(){
  43.         User user = mapper.selectUserById(1);
  44.         System.out.println(user);
  45.     }
  47.     //一个带别名的简单类型的入参
  48.     @Test
  49.     public void testSelectUserById2(){
  50.         User user = mapper.selectUserById2(2);
  51.         System.out.println(user);
  52.     }
  54.     //多个带别名的简单类型的入参
  55.     @Test
  56.     public void testSelectUserByIdAndUsername(){
  57.         User user = mapper.selectUserByIdAndUsername(2,"兰钊");
  58.         System.out.println(user);
  59.     }
  61.     //带别名的Object类型的入参
  62.     @Test
  63.     public void testinsertUserByObject(){
  64.         User user = new User();
  65.         user.setId(7);
  66.         user.setUsername("张三");
  67.         user.setPassword("废话转");
  68.         user.setAge(78);
  69.         Integer affectedRows = mapper.insertUserByObject(user);
  70.         System.out.println("affectedRows:" + affectedRows);
  71.     }
  73.     //通过Map作为入参(不推荐)
  74.     @Test
  75.     public void testselectUserByIdOrUsername(){
  76.         HashMap<String, Object> map = new HashMap<>();
  77.         map.put("id",1);
  78.         map.put("username","张三");
  79.         List<User> userList = mapper.selectUserByIdOrUsername(map);
  80.     }
  82.     //通过位置作为入参（不推荐）
  83.     @Test
  84.     public void testselectUserByIndex(){
  85.         List<User> userList = mapper.selectUserByIndex(1, "天明");
  86.         System.out.println(userList);
  87.     }
  89.     //$和#：关于$的优势：传递表名或者列名的时候必须使用$，分库分表的时候用得多
  90.     @Test
  91.     public void testselectUserByTableName(){
  92.         List<User> userList = mapper.selectUserByTableName("user");
  93.         System.out.println(userList);
  94.     }
  95.     @Test
  96.     public void testselectUserByColumnName(){
  97.         List<User> userList = mapper.selectUserByColumnName("age");
  98.         System.out.println(userList);
  99.     }
  101. }

输出映射

image.png
输出映射，包含单个字段、单个字段列表的输出映射，对象、对象列表的输出映射、ResuletMap输出映射。

单个字段

字段列表，by list

字段列表，by array

单个对象

单个对象列表
单个对象，解决对应不上的问题:Result

对象列表，解决对应不上的问题:Result

  1. package com.simon;
  3. import com.simon.bean.User;
  4. import com.simon.bean.UserVO;
  5. import org.apache.ibatis.annotations.Param;
  7. import java.util.List;
  8. import java.util.Map;
  10. public interface Mapper {
  11.     Integer selectUserAgeById(Integer id);
  13.     List<Integer> selectUserAges();
  15.     Integer[] selectUserAges2();
  17.     User selectUserById(Integer id);
  19.     UserVO selectUserById2(Integer id);
  21.     List<User> selectUserList();
  24.     UserVO selectUserVOById3(Integer id);
  26.     List<UserVO> selectUserVOList();
  27. }
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.simon.Mapper">
  7.     <select id="selectUserAgeById"  resultType="int">
  8.         select age from user where id = #{id}
  9.     </select>
  11.     <select id="selectUserAges" resultType="int">
  12.         select age from user
  13.     </select>
  15.     <select id="selectUserAges2"    resultType="int">
  16.         select age from user
  17.     </select>
  19.     <select id="selectUserById" resultType="com.simon.bean.User">
  20.         select * from user where id = #{id}
  21.     </select>
  23.     <select id="selectUserById2"    resultType="com.simon.bean.UserVO">
  24.     SELECT
  25.         id AS id,
  26.         username AS name,
  27.         PASSWORD AS passwd,
  28.         age AS age
  29.     FROM
  30.     USER
  31.     WHERE
  32.         id = #{id}
  33.     </select>
  35.     <select id="selectUserList" resultType="user">
  36.         select * from user
  37.     </select>
  39.     <!--ResultMap-->
  40.     <resultMap id="selectUserVO" type="com.simon.bean.UserVO">
  41.         <id column="id" property="id"/>
  42.         <result column="username"   property="name"/>
  43.         <result column="password"   property="passwd"/>
  44.         <result column="age"    property="age"/>
  45.     </resultMap>
  46.     <select id="selectUserVOById3"    resultMap="selectUserVO">
  47.         select * from user where id = #{id}
  48.     </select>
  50.     <select id="selectUserVOList"   resultMap="selectUserVO">
  51.         select * from user
  52.     </select>
  54. </mapper>

image.png

  1. package com.simon;
  3. import com.simon.bean.User;
  4. import com.simon.bean.UserVO;
  5. import org.apache.ibatis.io.Resources;
  6. import org.apache.ibatis.session.SqlSession;
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  9. import org.junit.AfterClass;
  10. import org.junit.BeforeClass;
  11. import org.junit.Test;
  13. import java.io.IOException;
  14. import java.io.InputStream;
  15. import java.util.HashMap;
  16. import java.util.List;
  18. public class OutPutMappingTest {
  20.     public static SqlSession sqlSession = null;
  21.     static Mapper mapper;
  23.     @BeforeClass
  24.     public static void init() throws IOException {
  25.         InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  26.         SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
  27.         SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
  28.         sqlSession = sqlSessionFactory.openSession();
  29.         mapper = sqlSession.getMapper(Mapper.class);
  30.     }
  32.     @AfterClass
  33.     public static void destory(){
  34.         sqlSession.commit();
  35.         sqlSession.close();
  37.     }
  40.     //输出映射，包含单个字段、单个字段列表的输出映射，
  41.     //对象、对象列表的输出映射
  42.     //ResuletMap输出映射
  44.     //单个字段
  45.     @Test
  46.     public void testSelectUserAgeById(){
  47.         Integer age = mapper.selectUserAgeById(2);
  48.         System.out.println("age:" + age);
  49.     }
  51.     //字段列表，by list
  52.     @Test
  53.     public void testSelectUserAges(){
  54.         List<Integer> ages = mapper.selectUserAges();
  55.         System.out.println(ages);
  56.     }
  58.     //字段列表，by array
  59.     @Test
  60.     public void testSelectUserAges2(){
  61.         Integer[] ages = mapper.selectUserAges2();
  62.         for (Integer age : ages) {
  63.             System.out.println("age:" + age);
  64.         }
  65.     }
  67.     //单个对象
  68.     @Test
  69.     public void testSelectUserById(){
  70.         User user = mapper.selectUserById(2);
  71.         System.out.println(user);
  72.     }
  74.     //单个对象，解决对应不上的问题:起别名
  75.     @Test
  76.     public void testSelectUserById2(){
  77.         UserVO user = mapper.selectUserById2(3);
  78.         System.out.println(user);
  79.     }
  81.     //单个对象列表
  82.     @Test
  83.     public void testSelectUserList(){
  84.         List<User> userList = mapper.selectUserList();
  85.         System.out.println(userList);
  86.     }
  89.     //单个对象，解决对应不上的问题:Result
  90.     @Test
  91.     public void testSelectUserById3(){
  92.         UserVO userVO = mapper.selectUserVOById3(5);
  93.         System.out.println(userVO);
  94.     }
  96.     //对象列表，解决对应不上的问题:Result
  97.     @Test
  98.     public void testSelectUserVO(){
  99.         List<UserVO> userVOList = mapper.selectUserVOList();
  100.         for (UserVO userVO : userVOList) {
  101.             System.out.println(userVO);
  102.         }
  103.     }
  105. }

动态标签库

image.png image.png

User类、Mapper接口、Mapper.xml实际在用。

  1. package com.simon;
  3. import com.simon.bean.User;
  4. import com.simon.bean.UserVO;
  5. import org.apache.ibatis.io.Resources;
  6. import org.apache.ibatis.session.SqlSession;
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  9. import org.junit.AfterClass;
  10. import org.junit.BeforeClass;
  11. import org.junit.Test;
  13. import java.io.IOException;
  14. import java.io.InputStream;
  15. import java.util.ArrayList;
  16. import java.util.HashMap;
  17. import java.util.List;
  19. public class OutPutMappingTest {
  21.     public static SqlSession sqlSession = null;
  22.     static Mapper mapper;
  24.     @BeforeClass
  25.     public static void init() throws IOException {
  26.         InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  27.         SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
  28.         SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
  29.         sqlSession = sqlSessionFactory.openSession();
  30.         mapper = sqlSession.getMapper(Mapper.class);
  31.     }
  33.     @AfterClass
  34.     public static void destory(){
  35.         sqlSession.commit();
  36.         sqlSession.close();
  38.     }
  41.     //输出映射，包含单个字段、单个字段列表的输出映射，
  42.     //对象、对象列表的输出映射
  43.     //ResuletMap输出映射
  45.     //where...if
  46.     @Test
  47.     public void testSelectUserBySelf(){
  48.         User user = new User();
  49.         user.setId(2);
  50.         user = mapper.selectUserBySelf(user);
  51.         System.out.println(user);
  52.     }
  54.     //if...else...---->choose..when..otherwise..
  55.     @Test
  56.     public void testSelectUserListByAge(){
  57.         List<User> users = mapper.selectUserListByAge(28);
  58.         System.out.println(users);
  59.     }
  61.     //传什么条件，改什么条件。trim   set，set更简洁
  62.     @Test
  63.     public void testUpdateUserWithTrim(){
  64.         User user = new User();
  65.         user.setPassword("我的快餐名！");
  66.         user.setId(7);
  67.         Integer affectedRows = mapper.updateUserWithTrim(user);
  68.         System.out.println("affectedRows:" + affectedRows);
  69.     }
  70.     @Test
  71.     public void testUpdateUserWithSet(){
  72.         User user = new User();
  73.         user.setPassword("我的第二个快餐名！");
  74.         user.setId(7);
  75.         Integer affectedRows = mapper.updateUserWithSet(user);
  76.         System.out.println("affectedRows:" + affectedRows);
  77.     }
  80.     //sql...include   抽取公共sql片段，注意怎样抽取
  81.     @Test
  82.     public void testSelectUserWithSqlIncludes(){
  83.         List<User> userList = mapper.selectUserWithSqlIncludes();
  84.         System.out.println(userList);
  85.     }
  87.     //foreach....循环 ：批量插入、通过in查找
  88.     //注意属性collection，若未指定注解，则以list或array为默认
  89.     @Test
  90.     public void testInsertUserForeach(){
  91.         User user1 = new User();
  92.         user1.setId(8);
  93.         user1.setUsername("张阿发");
  94.         user1.setPassword("恭喜发财");
  95.         user1.setAge(37);
  97.         User user2 = new User();
  98.         user2.setId(9);
  99.         user2.setUsername("李孝九");
  100.         user2.setPassword("999,感冒灵");
  101.         user2.setAge(89);
  103.         ArrayList<User> users = new ArrayList<>();
  104.         users.add(user1);
  105.         users.add(user2);
  107.         Integer affectedRows = mapper.insertUserWithForeach(users);
  108.         System.out.println("affectedRows:" + affectedRows);
  109.     }
  110.     @Test
  111.     public void testSelectUserWithForeach(){
  112.         ArrayList<Integer> list = new ArrayList<>();
  113.         for (int i = 0; i < 6; i++) {
  114.             list.add(1+i);
  115.         }
  116.         List<User> userList = mapper.selectUserByIdListWithForeach(list);
  117.         System.out.println(userList);
  118.     }
  121.     //主键自增，一般要先插入后查询。其优化之道是selectKey
  122.     @Test
  123.     public void testInsertUserThenSelectIdWithSelectKey(){
  124.         User user = new User();
  125.         user.setId(null);
  126.         user.setUsername("金十顺");
  127.         user.setPassword("十全十美");
  128.         user.setAge(47);
  129.         Integer affectedRows = mapper.insertUserThenSelectIdWithSelectKey(user);
  130.         System.out.println("affectedRows:" + affectedRows);
  131.         System.out.println("获取到的用户id:" + user.getId());
  132.     }
  133.     //使用useGeneratedKeys比SelectKey更简单
  134.     @Test
  135.     public void testInsertUserThenSelectIdWithGeneratedKeys(){
  136.         User user = new User();
  137.         user.setId(null);
  138.         user.setUsername("张良");
  139.         user.setPassword("足智多谋");
  140.         user.setAge(43);
  141.         Integer affectedRows = mapper.insertUserThenSelectIdWithGeneratedKeys(user);
  142.         System.out.println("affectedRows:" + affectedRows);
  143.         System.out.println("获取到的用户id:" + user.getId());
  144.     }
  146. }
  1. package com.simon;
  3. import com.simon.bean.User;
  4. import com.simon.bean.UserVO;
  5. import org.apache.ibatis.annotations.Param;
  7. import java.util.ArrayList;
  8. import java.util.List;
  9. import java.util.Map;
  11. public interface Mapper {
  12.     //动态sql标签库，where--if--choose...when...otherwise
  13.     //where，if，20
  14.     //where的作用：动态拼接，首个自动取出and/or，不满足则去除where，直接select * from user
  15.     User selectUserBySelf(@Param("user") User user);
  17.     //if...else...---->choose..when..otherwise..
  18.     List<User> selectUserListByAge(@Param("age") Integer age);
  20.     //传什么条件，改什么条件。trim   set，set更简洁
  21.     Integer updateUserWithTrim(@Param("user") User user);
  23.     Integer updateUserWithSet(@Param("user") User user);
  25.     List<User> selectUserWithSqlIncludes();
  27.     Integer insertUserWithForeach(@Param("users") ArrayList<User> users);
  29.     List<User> selectUserByIdListWithForeach(@Param("idList") List<Integer> idList);
  31.     Integer insertUserThenSelectIdWithSelectKey(@Param("user") User user);
  33.     Integer insertUserThenSelectIdWithGeneratedKeys(@Param("user") User user);
  34. }
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.simon.Mapper">
  7.     <select id="selectUserBySelf" resultType="com.simon.bean.User">
  8.         select * from user
  9.         <where>
  10.             <if test="user.id != null">
  11.                 and id = #{user.id}
  12.             </if>
  13.             <if test="user.username != null">
  14.                 and username = #{user.username}
  15.             </if>
  16.             <if test="user.password != null">
  17.                 and password = #{user.password}
  18.             </if>
  19.             <if test="user.age != null">
  20.                 and age = #{user.age}
  21.             </if>
  22.     </where>
  23.     </select>
  25.     <select id="selectUserListByAge" resultType="com.simon.bean.User">
  26.         select * from user
  27.         <where>
  28.             <choose >
  29.                 <when test="age gt 30">
  30.                     age &gt;= 30
  31.                 </when>
  32.                 <otherwise>
  33.                     age &lt; 30
  34.                 </otherwise>
  35.             </choose>
  36.         </where>
  37.     </select>
  39.     <update id="updateUserWithTrim" parameterType="com.simon.bean.User">
  40.         update user
  41.         <trim prefix="set" suffixOverrides=",">
  42.             <if test="user.username != null">
  43.                 username = #{user.username},
  44.             </if>
  45.             <if test="user.password != null">
  46.                 password = #{user.password},
  47.             </if>
  48.             <if test="user.age != null">
  49.                 age = #{user.age},
  50.             </if>
  51.         </trim>
  52.         <where>
  53.             id = #{user.id}
  54.         </where>
  55.     </update>
  57.     <update id="updateUserWithSet" parameterType="com.simon.bean.User">
  58.         update user
  59.         <set>
  60.             <if test="user.username != null">
  61.                 username = #{user.username},
  62.             </if>
  63.             <if test="user.password != null">
  64.                 password = #{user.password},
  65.             </if>
  66.             <if test="user.age != null">
  67.                 age = #{user.age},
  68.             </if>
  69.         </set>
  70.         <where>
  71.             id = #{user.id}
  72.         </where>
  73.     </update>
  75.     <sql id="column_lists">
  76.         id,username,password,age
  77.     </sql>
  78.     <select id="selectUserWithSqlIncludes" resultType="com.simon.bean.User">
  79.         select <include refid="column_lists"/> from user
  80.     </select>
  82.     <insert id="insertUserWithForeach" parameterType="com.simon.bean.User">
  83.         insert into user
  84.         values <foreach collection="users" item="user" separator=",">
  85.         (#{user.id},#{user.username},#{user.password},#{user.age},now)
  86.     </foreach>
  87.     </insert>
  89.     <!--注意此处，是#{id}而不是#{id.id}，separator也要写-->
  90.     <!--foreach是检验动态sql学得好不好的一个重要方面-->
  91.     <select id="selectUserByIdListWithForeach" resultType="com.simon.bean.User">
  92.         select <include refid="column_lists"/>
  93.         from user
  94.           <where>
  95.             id in
  96.             <foreach collection="idList" separator="," item="id" open="(" close=")">
  97.                 #{id}
  98.             </foreach>
  99.           </where>
  100.     </select>
  102.     <insert id="insertUserThenSelectIdWithSelectKey" parameterType="com.simon.bean.User">
  103.         <selectKey order="AFTER" keyColumn="id" keyProperty="user.id" resultType="integer">
  104.             select LAST_INSERT_ID()
  105.         </selectKey>
  106.         insert into user values (#{user.id},#{user.username},#{user.password},#{user.age})
  107.     </insert>
  109.     <insert id="insertUserThenSelectIdWithGeneratedKeys" parameterType="com.simon.bean.User"
  110.             useGeneratedKeys="true" keyProperty="user.id" keyColumn="id">
  111.         insert into user values (#{user.id},#{user.username},#{user.password},#{user.age})
  112.     </insert>
  113. </mapper>