mybatis示例集合

本文适合对mybatis有简单的入门了解的读者。转载请注明出处！

环境：

Mysql5.6、navicat for mysql 11、jdk8、maven3.3.9、mybatis3.2.8

MySQL表

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;

maven依赖配置(pom.xml)

 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>

mybatis配置文件（conf.xml）

 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>

mybatis映射文件(demo/mybatis/base/mapper/User.xml)

  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： set
108      -->
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>

demo/mybatis/base/mapper/IUser.xml

<?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>

demo/mybatis/base/mapper/IRelate.xml

<?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>

JAVA接口定义

demo.mybatis.base.IUser

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);
}

demo.mybatis.base.IRelate

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);
}

javabean

demo.modul.User

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;
    }

    
}

demo.modul.Sale

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());
        }
    }
    
}

项目结构：