mybatis基礎之二

UserMapper.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">
<!-- namespace名稱空間，就是對sql進行分類化管理 理解sql隔離 -->
<mapper namespace="com.liu.mybatis.mapper.UserMapper">
   <!-- 定義一個resultMap  -->
   <resultMap type="user" id="userResultMap">
     <id column="id_" property="id"/>
     <result column="address_" property="address"/>
   </resultMap>
   <!-- 定義sql片段 -->
   <sql id="user_query_where">
       <if test="userCustom!=null">
           <if test="userCustom.sex!=null and userCustom.sex!=''">
               and user.sex=#{userCustom.sex}
           </if>
           <if test="userCustom.username!=null and userCustom.username!=''">
               and user.username like '%${userCustom.username}%'
           </if>
        </if>
   </sql>
   <!-- 在對映檔案中配置sql語句 -->
   <!-- 通過ID查詢sql記錄 -->
   <!-- 通過select來執行查詢
   id: 標識對映檔案中的sql 稱為statement的id
           將sql語句封裝到mappedStatement中
   parameterType:指定輸入引數的型別 ，這裡指定int型
   #{}表示一個佔位符
   #{id} 表示接收輸入的引數  id就是輸入的引數名稱
   resultTypeL: 指定sql輸出結果的javadui型別
     -->
   <select id="findUserById" parameterType="int" resultType="com.liu.mybatis.po.User">
      SELECT * FROM USER WHERE id = #{id}
   </select>
   <!-- 使用resultMap輸出對映 -->
   <select id="findUserByIdResultMap" parameterType="int" resultMap="userResultMap">
      SELECT id id_,address address_ FROM USER WHERE id = #{id}
   </select>
   <!-- ${}是一個拼接符號 -->
   <select id="findUserByName" parameterType="java.lang.String" resultType="user">
      SELECT * FROM USER WHERE username like '%${value}%'
   </select>
      <!-- 使用者資訊綜合查詢 -->
   <select id="findUserList" parameterType="com.liu.mybatis.po.UserQueryVo"
           resultType="com.liu.mybatis.po.UserCustom">
      select * from user
     <where>
        <if test="userCustom!=null">
           <if test="userCustom.sex!=null and userCustom.sex!=''">
               and user.sex=#{userCustom.sex}
           </if>
           <if test="userCustom.username!=null and userCustom.username!=''">
               and user.username like '%${userCustom.username}%'
           </if>
        </if>
        <if test="ids!=null">
          <!-- foreach實現sql的拼接 and id=1 or id=3 or id=5  -->
          <foreach collection="ids" item="user_id" open="AND (" close=")" separator="or">
            id=#{user_id}
          </foreach>
          <!-- foreach實現sql的拼接 and id in (1,3,5)  -->
          <!-- <foreach collection="ids" item="user_id" open="and id in (" close=")" separater=",">
            #{user_id}
          </foreach>    -->
        </if>
     </where>
   </select>
   <!-- 使用者資訊總數查詢 -->
   <select id="findUserCount" parameterType="com.liu.mybatis.po.UserQueryVo" resultType="int">
      select * from user
      <where>
        <include refid="user_query_where"></include>
     </where>
   </select>
   <!-- selectkey獲取自增主鍵 -->
   <insert id="insertUser" parameterType="com.liu.mybatis.po.User">
      <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
        SELECT LAST_INSERT_ID()
      </selectKey>
      insert into user(username,birthday,sex,address) value(#{username},#{birthday},#{sex},#{address})
   </insert>
   <delete id="deleteUser" parameterType="java.lang.Integer">
      delete from user where id = #{id}
   </delete>
   <update id="updateUser" parameterType="com.liu.mybatis.po.User">
      update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
   </update>
  
</mapper>

UserMapper.java

package com.liu.mybatis.mapper;

import java.util.List;

import com.liu.mybatis.po.User;
import com.liu.mybatis.po.UserCustom;
import com.liu.mybatis.po.UserQueryVo;

public interface UserMapper {
    public List<UserCustom> findUserList(UserQueryVo userQueryVo) throws Exception;
    
    public int findUserCount(UserQueryVo userQueryVo) throws Exception;
    
    public User findUserById(int id) throws Exception;
    
    public User findUserByIdResultMap(int id) throws Exception;
    
    public List<User> findUserByName(String username) throws Exception;
    
    public void insertUser(User user) throws Exception;
    
    public void deleteUser(int id) throws Exception;

}

UserQueryVo.java

package com.liu.mybatis.po;

import java.util.List;

/*
 * 包裝型別
 */
public class UserQueryVo {
    private UserCustom userCustom;
    
    private List<Integer> ids;

    public UserCustom getUserCustom() {
        return userCustom;
    }

    public void setUserCustom(UserCustom userCustom) {
        this.userCustom = userCustom;
    }

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }

}

UserCustom.java

package com.liu.mybatis.po;

/*
 * 使用者的擴充套件類
 */
public class UserCustom extends User{

}

測試程式

package com.liu.mybatis.mapper;

import static org.junit.Assert.*;

import java.io.InputStream;
import java.util.ArrayList;
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 com.liu.mybatis.po.User;
import com.liu.mybatis.po.UserCustom;
import com.liu.mybatis.po.UserQueryVo;

public class UserMapperTest {
    private SqlSessionFactory sqlSessionFactory;
    
    @Before
    public void setUp() throws Exception {
        String resource = "SqlMapConfig.xml";
        //得到配置檔案流
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //建立會話工廠
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testFindUserById() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //建立UserMapper物件，mybatis自動生成mapper代理物件
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        
        System.out.println(UserMapper.class);
        
        User user = userMapper.findUserById(28);
        
        System.out.println("20170902 =" + user);
    }
    
    @Test
    public void testFindUserByName() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //建立UserMapper物件，mybatis自動生成mapper代理物件
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        
        
        List<User> list = userMapper.findUserByName("曉春");
        
        System.out.println("20170902 =" + list);
    }
    //使用者綜合資訊查詢
    @Test
    public void testFindUserList() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //建立UserMapper物件，mybatis自動生成mapper代理物件
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        System.out.println("11111111111111111===================");
        UserQueryVo userQueryVo =new UserQueryVo();
        
        UserCustom userCustom = new UserCustom();
        userCustom.setUsername("王曉春");
        //userCustom.setSex("1");
        List<Integer> ids= new ArrayList<Integer>();
        ids.add(1);
        ids.add(28);
        ids.add(30);
        userQueryVo.setIds(ids);
        userQueryVo.setUserCustom(userCustom);
        
        List<UserCustom> list = userMapper.findUserList(userQueryVo);
        
        System.out.println("20170903  10:15 =" + list);
    }
    
    @Test
    public void testFindUserCount() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //建立UserMapper物件，mybatis自動生成mapper代理物件
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        UserQueryVo userQueryVo =new UserQueryVo();
        
        UserCustom userCustom = new UserCustom();
        userCustom.setUsername("王曉春");
        userCustom.setSex("1");
        userQueryVo.setUserCustom(userCustom);
        
        int count = userMapper.findUserCount(userQueryVo);
        
        System.out.println("20170902  23:15 =" + count);
    }
    
    @Test
    public void testFindUserByIdResultMap() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //建立UserMapper物件，mybatis自動生成mapper代理物件
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        
        User user = userMapper.findUserByIdResultMap(28);
        
        System.out.println("20170902 ===" + user);
    }
}