CREATE TABLE `sale` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `sale_name` varchar(255) DEFAULT NULL,  `sale_age` int(11) DEFAULT NULL,  `user_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=gbk;

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `age` int(11) NOT NULL,  `name` varchar(255) NOT NULL,  `user_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=gbk;

 1 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 2   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> 3   <modelVersion>4.0.0</modelVersion> 4   <groupId>com</groupId> 5   <artifactId>batistest</artifactId> 6   <packaging>war</packaging> 7   <version>0.0.1-SNAPSHOT</version> 8   <name>batistest Maven Webapp</name> 9   <url>http://maven.apache.org</url>10   <dependencies>11     <dependency>12       <groupId>junit</groupId>13       <artifactId>junit</artifactId>14       <version>3.8.1</version>15       <scope>test</scope>16     </dependency>17     <dependency>18         <groupId>org.mybatis</groupId>19         <artifactId>mybatis</artifactId>20         <version>3.2.8</version>21     </dependency>22     <dependency>23         <groupId>mysql</groupId>24         <artifactId>mysql-connector-java</artifactId>25             <version>5.1.38</version>26     </dependency>27     <dependency>28         <groupId>log4j</groupId>29         <artifactId>log4j</artifactId>30         <version>1.2.17</version>31     </dependency>32   </dependencies>33   <build>34     <finalName>batistest</finalName>35   </build>36 </project>

 1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3 <configuration> 4     <environments default="development"> 5         <environment id="development"> 6             <transactionManager type="JDBC" /> 7             <!-- 配置数据库连接信息 --> 8             <dataSource type="POOLED"> 9                 <property name="driver" value="com.mysql.jdbc.Driver" />10                 <property name="url" value="jdbc:mysql://localhost:3308/rfid" />11                 <property name="username" value="admin" />12                 <property name="password" value="dps2010_admin" />13             </dataSource>14         </environment>15     </environments>16     17     <!-- 注册映射文件 -->18     <mappers>   19         <mapper resource="demo/mybatis/base/mapper/User.xml" />20         <mapper resource="demo/mybatis/base/mapper/IUser.xml" />21         <mapper resource="demo/mybatis/base/mapper/IRelate.xml" />22     </mappers>23 </configuration>

  1 <?xml version="1.0" encoding="UTF-8" ?>  2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  3 <!-- 唯一命名空间，通常定义规则为：所在包名+文件名(映射文件) -->  4 <mapper namespace="demo.mybatis.base.mapper.User">  5       6     <!--   7     缓存启用   8       9     缓存更新算法 10     LRU  － 最近最少使用的：移除最长时间不被使用的对象 11     FIFO － 先进先出：按对象进入缓存的时间顺序来移除 12     SOFT － 软引用：移除基于垃圾回收器状态和软引用规则的对象 13     WEAK － 弱引用：更积极地移除基于垃圾回收器状态和弱引用规则的对象 14      15     size:最大缓存对象或集合 16     flushInterval:缓存刷新间隔,60秒 17     readOnly:缓存是否为只读 18     --> 19     <cache eviction="FIFO" size="512" flushInterval="60000" readOnly="false"></cache> 20      21      22     <!-- SQL引用 --> 23     <sql id="column">id,age,name</sql> 24      25     <!--  26           查询标签 27     2、id为标签的唯一标识 28     3、parameterType为参数类型 29     4、resultType为返回结果类型 30      31           注意:include的refid为要引用的SQL标签的ID 32     --> 33     <select id="queryUser" parameterType="int" resultType="demo.modul.User" > 34         select <include refid="column"></include> from user where id=#{id} 35     </select> 36      37     <!--  38           以下演示基本的：插入标签、更新标签、删除标签 39     1、parameterType 参数类型,可以为基本数据类型，也可以为定义的对象 40     2、useGeneratedKeys 是否为自动生成主键 41     3、keyProperty 数据库主键对应的实体类的属性名 42      43           注意：若数据库不支持主键，则可以使用selectKey生成主键 44      --> 45     <insert id="insertUser" parameterType="demo.modul.User" useGeneratedKeys="true" keyProperty="id"> 46         insert into user(name,age) values(#{name},#{age}) 47     </insert> 48      49      50     <update id="updateUser" parameterType="demo.modul.User" > 51         update user set age=#{age} where name=#{name} 52     </update> 53      54     <delete id="deleteUser" parameterType="int"> 55         delete from user where id=#{id} 56     </delete> 57      58      59      60     <!--  61     MyBatis动态元素演示1： if条件语句 62      --> 63     <select id="testIf" parameterType="demo.modul.User" resultType="demo.modul.User"> 64         select <include refid="column"></include> from user  65         <where> 66             <if test="name!=null"> name like ‘%‘ #{name} ‘%‘</if> 67         </where>  68     </select> 69      70     <!--  71     MyBatis动态元素演示2： choose分支结构 72      --> 73     <select id="testChoose" parameterType="demo.modul.User" resultType="demo.modul.User"> 74         select <include refid="column"></include> from user WHERE 1=1 75         <choose> 76             <when test="name!=null"> AND name like ‘%‘ #{name} ‘%‘</when> 77             <when test="age!=null"> AND age>#{age}</when> 78             <otherwise>AND id=9</otherwise> 79         </choose> limit 0,1 80     </select> 81      82     <!--  83     MyBatis动态元素演示3： foreach遍历 84      --> 85     <delete id="testForeach" parameterType="java.util.List" > 86         delete from user where id in 87         <foreach collection="list" item="item" index="index" open="(" close=")" separator=","> 88             #{item} 89         </foreach> 90     </delete> 91      92     <!--  93     MyBatis动态元素演示4： where 94           注意： 95     1、当第一个if条件不成立时，mybatis会自动去除第一个条件的AND连接符 96     2、只有有任何一条条件成立，则自动添加WHERE关键字   97      --> 98     <select id="testWhere" parameterType="demo.modul.User" resultType="demo.modul.User"> 99         select <include refid="column"></include> from user 100         <where>101             <if test="name!=null">name like ‘%‘ #{name} ‘%‘</if>102             <if test="age!=null">AND age >10</if>103         </where>104     </select>105     106     <!-- 107     MyBatis动态元素演示5： set108      -->109     <update id="testSet" parameterType="demo.modul.User">110         update user 111         <set>112             <if test="name!=null">name = #{name},</if>113             <if test="age!=null">age = #{age}</if>114         </set>115         where id=#{id}116     </update>117 118 119 </mapper>

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!-- 唯一命名空间，必须定义为：所在包名+接口文件名 --><mapper namespace="demo.mybatis.base.IUser">    <!-- SQL引用 -->    <sql id="column">id,age,name</sql>        <!--           查询标签    2、id为标签的唯一标识    3、parameterType为参数类型    4、resultType为返回结果类型              注意:include的refid为要引用的SQL标签的ID    -->    <select id="getUser" parameterType="int" resultType="demo.modul.User" >        select <include refid="column"></include> from user where id=#{id}    </select>          <select id="getAllUser" parameterType="int" resultType="demo.modul.User" >        select <include refid="column"></include> from user where age>#{age}    </select></mapper>

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!-- 唯一命名空间，必须定义为：所在包名+接口文件名 --><mapper namespace="demo.mybatis.base.IRelate">    <!--         演示1：嵌套查询、多对一的关系         注意：此方式存在N+1性能的问题，意思就是每行记录对应的记录都会生产一个子查询          类似于这样的SQL: SELECT table1.id,table1.name,(select sum(table2.age) from table2 where table2.name = table1.name) from table1      -->    <resultMap type="demo.modul.Sale" id="userMap">        <id property="id" column="id"/>        <result property="name" column="sale_name"/>        <result property="age" column="sale_age"/>                <association property="user" column="user_id" select="queryOneUser" javaType="demo.modul.User">            <id property="id" column="id"/>            <result property="name" column="name"/>            <result property="age" column="age"/>        </association>    </resultMap>    <select id="queryOneUser" parameterType="int" resultType="demo.modul.User" >         select id,name,age from user where user_id = #{user_id}    </select>          <select id="querySale" parameterType="int" resultMap="userMap">        select id,sale_name,sale_age,user_id from sale where id = 1    </select>             <!--         演示2：嵌套结果、多对一的关系         注意：解决N+1的SQL查询性能问题     -->    <resultMap type="demo.modul.Sale" id="userMap1">        <id property="id" column="sale_id"/>        <result property="name" column="sale_name"/>        <result property="age" column="sale_age"/>        <association property="user"  resultMap="userResult" />    </resultMap>        <resultMap type="demo.modul.User" id="userResult" >        <result property="name" column="user_name"/>        <result property="age" column="user_age"/>    </resultMap>        <select id="querySale1" parameterType="int" resultMap="userMap1">        SELECT sale.id as sale_id,sale_age,sale_name,age as user_age,`name` as user_name FROM sale LEFT JOIN `user` on sale.user_id=user.id  WHERE sale.id= #{id}    </select>            <!--         演示3：嵌套查询、多对多的关系         注意：存在N+1的SQL查询性能问题     -->     <resultMap type="demo.modul.User" id="userQuery">         <result property="name" column="name"/>         <result property="age" column="age"/>         <result property="userId" column="user_id"/>         <collection property="sale" column="user_id" javaType="ArrayList" ofType="demo.modul.Sale"  select="querySaleList">             <result property="name" column="name"/>             <result property="age" column="age"/>         </collection>     </resultMap>          <select id="querySaleList" resultType="demo.modul.Sale">         SELECT sale_name as name,sale_age as age FROM sale WHERE user_id=#{user_id}     </select>          <select id="queryUser" parameterType="int" resultMap="userQuery">         SELECT name,age,user_id FROM user WHERE user_id=#{user_id}     </select>          <!--         演示3：嵌套结果、多对多的关系         注意：不存在N+1的SQL查询性能问题     -->     <resultMap type="demo.modul.User" id="userQuery1">         <result property="name" column="name"/>         <result property="age" column="age"/>         <result property="userId" column="user_id"/>         <collection property="sale"  ofType="demo.modul.Sale" columnPrefix="c_" >             <result property="name" column="sale_name"/>             <result property="age" column="sale_age"/>         </collection>     </resultMap>          <select id="queryUser1" parameterType="int" resultMap="userQuery1">         SELECT user.name,user.age,user.user_id,sale.sale_name as c_sale_name,sale.sale_age as c_sale_age FROM user left join sale on user.user_id = sale.user_id WHERE user.user_id=#{user_id}     </select></mapper>

package demo.mybatis.base;import java.util.List;import demo.modul.User;public interface IUser {        //获取某个用户    public User getUser(int id);            //获取多个用户    public List<User> getAllUser(int age);}

package demo.mybatis.base;import demo.modul.Sale;import demo.modul.User;public interface IRelate {    //嵌套查询、多对一    public Sale querySale(int id);        //嵌套结果、多对多    public Sale querySale1(int id);        //嵌套查询、一对多    public User queryUser(int id);        //嵌套结果、一对多    public User queryUser1(int id);}

package demo.modul;import java.util.List;public class User {    private int id;    private String name;    private int age;        private List<Sale> sale;    private int userId;            public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public int getAge() {        return age;    }    public void setAge(int age) {        this.age = age;    }    public List<Sale> getSale() {        return sale;    }    public void setSale(List<Sale> sale) {        this.sale = sale;    }    public int getUserId() {        return userId;    }    public void setUserId(int userId) {        this.userId = userId;    }    }

package demo.modul;public class Sale {    private int id;    private int age;    private String name;        private User user;    private int userId;        public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public int getAge() {        return age;    }    public void setAge(int age) {        this.age = age;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public User getUser() {        return user;    }    public void setUser(User user) {        this.user = user;    }    public int getUserId() {        return userId;    }    public void setUserId(int userId) {        this.userId = userId;    }    }

package demo.modul;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import demo.mybatis.base.IRelate;import demo.mybatis.base.IUser;public class test {    public static void main(String[] args) {        //mybatis的配置文件        String resource = "conf.xml";                //使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）        InputStream is = test.class.getClassLoader().getResourceAsStream(resource);                //构建SqlSession的工厂        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);               //创建能执行映射文件中sql的SqlSession        SqlSession session = sessionFactory.openSession();                //执行SQL        getRelateUserQuery1(session);    }        /****************************** 增删改查测试 *********************************/        //查询用户    private static void queryUser(SqlSession session)    {      //配置文件 命名空间+标签ID      String statement = "demo.mybatis.base.mapper.User.queryUser";            //执行查询，并根据配置文件返回结果到对象      User user = session.selectOne(statement,2);            //数据输出      System.out.println("ID："+user.getId());      System.out.println("姓名："+user.getName());      System.out.println("年龄："+user.getAge());    }        //插入用户    private static void insertUser(SqlSession session)    {        //参数设置        User u = new User();        u.setAge(15);        u.setName("测试插入");                //配置文件对应的命名空间+标签ID        String statement = "demo.mybatis.base.mapper.User.insertUser";                //解析SQL并替换参数        int cnt = session.insert(statement, u);                //提交SQL        session.commit();                //返回结果        System.out.println("受影响行数:"+cnt);            }        //删除用户    private static void deleteUser(SqlSession session)    {        //命名空间+标签ID        String statement = "demo.mybatis.base.mapper.User.deleteUser";                //解析并执行SQL        int cnt = session.update(statement, 6);                //提交SQL        session.commit();                //结果        System.out.println("受影响行数");    }        //更新用户    private static void updateUser(SqlSession session)    {        //设置参数        User u = new User();        u.setName("测试插入");        u.setAge(26);                //命名空间+标签ID        String statement = "demo.mybatis.base.mapper.User.updateUser";                //解析SQL并替换参数        int cnt = session.insert(statement,u);                //提交参数SQL        session.commit();                //返回结果        System.out.println("受影响行数:"+cnt);    }                        /********************* 条件测试 **************************/    //测试IF条件    private static void testIf(SqlSession session)    {        User condition = new User();        condition.setAge(2);        condition.setName("楷");        String statement = "demo.mybatis.base.mapper.User.testIf";        User u = session.selectOne(statement, condition);        System.out.println("年龄:"+u.getAge()+" 姓名:"+u.getName()+" ID:"+u.getId());    }        //测试choose分支结构    private static void testChoose(SqlSession session)    {        User condition = new User();        condition.setName("楷");        String statement = "demo.mybatis.base.mapper.User.testChoose";        User u = session.selectOne(statement, condition);        System.out.println("年龄:"+u.getAge()+" 姓名:"+u.getName()+" ID:"+u.getId());    }        //测试foreach遍历    private static void testForeach(SqlSession session)    {        List<Integer> list = new ArrayList<Integer>();        list.add(1);        list.add(2);        list.add(3);        list.add(4);                //命名空间+标签ID        String statement = "demo.mybatis.base.mapper.User.testForeach";                        //解析并执行SQL        int cnt = session.update(statement, list);                        //提交SQL        session.commit();                        //结果        System.out.println("受影响行数"+cnt);    }        //测试where条件    private static void testWhere(SqlSession session)    {        User condition = new User();        condition.setName("楷");        String statement = "demo.mybatis.base.mapper.User.testWhere";        User u = session.selectOne(statement, condition);        System.out.println("年龄:"+u.getAge()+" 姓名:"+u.getName()+" ID:"+u.getId());    }        //测试set条件    private static void testSet(SqlSession session)    {        User condition = new User();        condition.setName("楷");        condition.setId(1);        String statement = "demo.mybatis.base.mapper.User.testSet";        int cnt = session.update(statement, condition);        System.out.println("受影响的行数:"+cnt);    }            /***接口式映射测试***/    //查询某个用户    private static void getUser(SqlSession session)    {        try {            System.out.println(IUser.class);            IUser cp = session.getMapper(IUser.class);            User u = cp.getUser(2);            if (null == u) {                System.out.println("没有符合条件的记录");            } else {                System.out.println("年龄"+u.getAge());                System.out.println("姓名"+u.getName());                System.out.println("ID"+u.getId());            }        } finally {            session.close();        }    }    // 查询多个用户用户    private static void getAllUser(SqlSession session) {        try {            IUser cp = session.getMapper(IUser.class);            List<User> users = cp.getAllUser(2);            if (users.size()==0) {                System.out.println("没有符合条件的记录");            } else {                for(User u:users)                {                    System.out.println("年龄:"+u.getAge()+" 姓名:"+u.getName()+" ID:"+u.getId());                }                            }        } finally {            session.close();        }    }        /************************关联查询*****************************/    //关联查询测试，嵌套查询，多对一关系，存在N+1的SQL性能问题    private static void getRelateQuery(SqlSession session)    {        IRelate relate = session.getMapper(IRelate.class);        Sale sale = relate.querySale(1);        System.out.println("业务姓名:"+sale.getName()+" 业务年龄:"+sale.getAge()+" 业务id:"+sale.getId()+ " 客户姓名:"+sale.getUser().getName()+" 客户年龄:"+sale.getUser().getAge());    }    //关联查询测试，嵌套结果，多对一关系 ，解决N+1的SQL性能问题    private static void getRelateQuery1(SqlSession session)    {        IRelate relate = session.getMapper(IRelate.class);        Sale sale = relate.querySale1(1);        System.out.println("业务姓名:"+sale.getName()+" 业务年龄:"+sale.getAge()+" 业务id:"+sale.getId()+ " 客户姓名:"+sale.getUser().getName()+" 客户年龄:"+sale.getUser().getAge());    }        //关联查询集合，嵌套查询，多对多关系，存在N+1的性能问题    private static void getRelateUserQuery(SqlSession session)    {        IRelate relate = session.getMapper(IRelate.class);        User user = relate.queryUser(5);        System.out.println("姓名:"+user.getName()+" 年龄:"+user.getAge()+" 用户ID:"+user.getUserId());        for(Sale s:user.getSale())        {            System.out.println("销售名称:"+s.getName()+" 销售年龄:"+s.getAge());        }    }    //关联查询集合，嵌套结果，多对多关系，解决N+1的性能问题    private static void getRelateUserQuery1(SqlSession session)    {        IRelate relate = session.getMapper(IRelate.class);        User user = relate.queryUser1(5);        System.out.println("姓名:"+user.getName()+" 年龄:"+user.getAge()+" 用户ID:"+user.getUserId());        for(Sale s:user.getSale())        {            System.out.println("销售名称:"+s.getName()+" 销售年龄:"+s.getAge());        }    }    }