mybatis 高级映射 简单例子

1.建表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `gender` varchar(1) NOT NULL,
  `name` varchar(255) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `birthday` date DEFAULT NULL,
  `classesid` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `alias`;
CREATE TABLE `alias` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `userid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

2.po，vo类

package cn.edu.cqupt.mybatis.po;


import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable{
    private Integer id;

    private String gender;

    private String name;

    private Date birthday;
    
    private Classes classes;
    
    private List<Alias> aliasList;

}

package cn.edu.cqupt.mybatis.po;

import java.io.Serializable;


public class Alias implements Serializable{
    private Integer id;
    private String name;
    
}

package cn.edu.cqupt.mybatis.po;

public class Classes {
    private Integer id;

    private String cname;
    
}

 

目录结构

　　　　

3.单表操作    一对多    一对一

1）/testMybatis/src/cn/edu/cqupt/mybatis/mapper/UserMapper.xml
　　/testMybatis/src/cn/edu/cqupt/mybatis/mapper/UserMapper.java

package cn.edu.cqupt.mybatis.mapper;

import cn.edu.cqupt.mybatis.po.User;
import cn.edu.cqupt.mybatis.po.UserClasses;
import cn.edu.cqupt.mybatis.po.UserExample;

import java.util.List;

import org.apache.ibatis.annotations.Param;

public interface UserMapper {
    int countByExample(UserExample example);

    int deleteByExample(UserExample example);

    int deleteByPrimaryKey(Integer id);

    int insert(User record);

    int insertSelective(User record);

    List<User> selectByExample(UserExample example);

    User selectByPrimaryKey(Integer id);

    int updateByExampleSelective(@Param("record") User record, @Param("example") UserExample example);

    int updateByExample(@Param("record") User record, @Param("example") UserExample example);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);
    
    UserClasses selectUserByClasses(Integer classesid);
    
    User selectUserClass2(Integer classesid);
    
    User selectUserByAlias(Integer userid);
}

<?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="cn.edu.cqupt.mybatis.mapper.UserMapper" >
  <resultMap id="BaseResultMap" type="cn.edu.cqupt.mybatis.po.User" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="gender" property="gender" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="birthday" property="birthday" jdbcType="DATE" />
  </resultMap>
  <sql id="Example_Where_Clause" >
    <where >
      <foreach collection="oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
          <trim prefix="(" suffix=")" prefixOverrides="and" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause" >
    <where >
      <foreach collection="example.oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
          <trim prefix="(" suffix=")" prefixOverrides="and" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List" >
    id, gender, name, birthday
  </sql>
  <select id="selectByExample" resultMap="BaseResultMap" parameterType="cn.edu.cqupt.mybatis.po.UserExample" >
    select
    <if test="distinct" >
      distinct
    </if>
    <include refid="Base_Column_List" />
    from user
    <if test="_parameter != null" >
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null" >
      order by ${orderByClause}
    </if>
  </select>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from user
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from user
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <delete id="deleteByExample" parameterType="cn.edu.cqupt.mybatis.po.UserExample" >
    delete from user
    <if test="_parameter != null" >
      <include refid="Example_Where_Clause" />
    </if>
  </delete>
  <insert id="insert" parameterType="cn.edu.cqupt.mybatis.po.User" >
    insert into user (id, gender, name, 
      birthday)
    values (#{id,jdbcType=INTEGER}, #{gender,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, 
      #{birthday,jdbcType=DATE})
  </insert>
  <insert id="insertSelective" parameterType="cn.edu.cqupt.mybatis.po.User" >
    insert into user
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="gender != null" >
        gender,
      </if>
      <if test="name != null" >
        name,
      </if>
      <if test="birthday != null" >
        birthday,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="gender != null" >
        #{gender,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="birthday != null" >
        #{birthday,jdbcType=DATE},
      </if>
    </trim>
  </insert>
  <select id="countByExample" parameterType="cn.edu.cqupt.mybatis.po.UserExample" resultType="java.lang.Integer" >
    select count(*) from user
    <if test="_parameter != null" >
      <include refid="Example_Where_Clause" />
    </if>
  </select>
  <update id="updateByExampleSelective" parameterType="map" >
    update user
    <set >
      <if test="record.id != null" >
        id = #{record.id,jdbcType=INTEGER},
      </if>
      <if test="record.gender != null" >
        gender = #{record.gender,jdbcType=VARCHAR},
      </if>
      <if test="record.name != null" >
        name = #{record.name,jdbcType=VARCHAR},
      </if>
      <if test="record.birthday != null" >
        birthday = #{record.birthday,jdbcType=DATE},
      </if>
    </set>
    <if test="_parameter != null" >
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map" >
    update user
    set id = #{record.id,jdbcType=INTEGER},
      gender = #{record.gender,jdbcType=VARCHAR},
      name = #{record.name,jdbcType=VARCHAR},
      birthday = #{record.birthday,jdbcType=DATE}
    <if test="_parameter != null" >
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="cn.edu.cqupt.mybatis.po.User" >
    update user
    <set >
      <if test="gender != null" >
        gender = #{gender,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="birthday != null" >
        birthday = #{birthday,jdbcType=DATE},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="cn.edu.cqupt.mybatis.po.User" >
    update user
    set gender = #{gender,jdbcType=VARCHAR},
      name = #{name,jdbcType=VARCHAR},
      birthday = #{birthday,jdbcType=DATE}
    where id = #{id,jdbcType=INTEGER}
  </update>
  <resultMap type="cn.edu.cqupt.mybatis.po.UserClasses" id="user_classes_resultmap">
      <id column="id" property="id"/>
      <result column="gender" property="gender"/>
      <result column="name" property="name"/>
      <result column="birthday" property="birthday"/>
      
      <association property="classes" javaType="cn.edu.cqupt.mybatis.po.Classes">  
        <id column="classesid" property="id"/>  
        <result column="cname" property="cname"/>  
    </association> 
  </resultMap>
  <select id="selectUserByClasses" parameterType="java.lang.Integer" resultMap="user_classes_resultmap">
      select user.*,classes.cname from user
        left join classes on user.classesId=classes.id
        where user.classesId=#{classedid}
  </select>
  <resultMap type="cn.edu.cqupt.mybatis.po.User" id="user_class_resultMap2">
      <id column="id" property="id"/>
      <result column="gender" property="gender"/>
      <result column="name" property="name"/>
      <result column="birthday" property="birthday"/>
      <result column="classesid" property="classesId"/>
      
  </resultMap>
  
  <select id="selectUserClass2" parameterType="java.lang.Integer" resultMap="user_class_resultMap2">
      select id,name,gender,birthday,classesid from user
        where classesid=#{classedid}
  </select>
  
  <resultMap type="cn.edu.cqupt.mybatis.po.User" id="user_alias_resultMap">
      <id column="id" property="id"/>
      <result column="gender" property="gender"/>
      <result column="name" property="name"/>
      <result column="birthday" property="birthday"/>
      <collection property="aliasList" ofType="cn.edu.cqupt.mybatis.po.Alias">
          <id column="aid" property="id"/>
          <result column="aname" property="name"/>
      </collection>
  </resultMap>
  <select id="selectUserByAlias" parameterType="java.lang.Integer" resultMap="user_alias_resultMap">
      select u.*,a.id aid,a.name aname from user u
        left join alias a on a.userid=u.id
    where u.id=#{userid}
  </select>
  
  
  
</mapper>

 

2）配置  /testMybatis/src/config/SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <!-- 加载属性文件 -->
    <properties resource="config/db.properties">
        <!--properties中还可以配置一些属性名和属性值  -->
        <!-- <property name="jdbc.driver" value=""/> -->
    </properties>
    <environments default="development">
        <environment id="development">
        <!-- 使用jdbc事务管理，事务控制由mybatis-->
            <transactionManager type="JDBC" />
        <!-- 数据库连接池，由mybatis管理-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}" />
                <property name="password" value="${jdbc.password}" />
            </dataSource>
        </environment>
    </environments>
    <!-- 加载 映射文件 -->
    <mappers>
        <package name="cn.edu.cqupt.mybatis.mapper"/>
    </mappers>
</configuration>

 

3）测试

package cn.edu.cqupt.mybatis.test;



import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import cn.edu.cqupt.mybatis.mapper.UserMapper;
import cn.edu.cqupt.mybatis.po.User;
import cn.edu.cqupt.mybatis.po.UserClasses;
import cn.edu.cqupt.mybatis.po.UserExample;


public class UserMapperTest {

    private SqlSessionFactory sqlSessionFactory;

    
    @Before
    public void setUp() throws Exception {
        String resource = "config/SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(inputStream);
    }

    @Test
    public void testSelectByPrimaryKey() throws Exception {
        
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectByPrimaryKey(6);
        
        System.out.println(user);
        
        
    }
    @Test
    public void testSelectByExample()throws Exception{
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        UserExample example = new UserExample();
        example.createCriteria().andNameLike("cherry");
        List<User> lists = userMapper.selectByExample(example);
        System.out.println(lists.size());
        
    }
    @Test
    public void testUpdateByPrimaryKey()throws Exception{
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectByPrimaryKey(3);
        user.setName("aa");
        user.setBirthday(new Date());
        userMapper.updateByPrimaryKey(user);
        sqlSession.commit();
        sqlSession.close();
    }
    
    @Test 
    public void testInsert()throws Exception{
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setName("lll");
        user.setGender("2");
        user.setBirthday(new Date());
        int count = userMapper.insert(user);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void selectUserByClasses(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        UserClasses uc=userMapper.selectUserByClasses(new Integer(1));
        System.out.println(uc.toString());
        
        
    }
    
    @Test
    public void selectUserClass2(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.selectUserClass2(new Integer(1));
        
    }
    @Test
    public void selectUserByAlias(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectUserByAlias(new Integer(2));
        System.out.println(user.getAliasList().size());
    }
    

}