mybatis学习使用4动态sql

1.mybatis最强大的特性是它的动态sql，其中常见的动态sql元素主要如下：　　

　　if,choose(when,otherwise),where,set,foreach,bind

代码如下：

　sql语句

　　

create table bs_employee(
  id int  primary  key auto_increment,
  email varchar(18),
  password varchar(10),
  name varchar(18) default null,
  sex char(2) default null,
  age int(11) default null,
  phone varchar(21),
  sal double,
  state varchar(18)
 );
 insert into bs_employee(email,password,name,sex,age,phone,sal,state)
 values('[e-mail]','123456','zhangsan','男',26,'1292093323',32320,'active');
  insert into bs_employee(email,password,name,sex,age,phone,sal,state)
 values('[e-mail]','123456','lisi','女',21,'32424423',23230,'active');

view code

 

　employee.java

package com.rookie.bigdata.domain;

import lombok.data;

import java.io.serializable;

/**
 * @author
 * @date 2018/10/22
 */
@data
public class employee implements serializable{
    private integer id;
    private string email;
    private string password;
    private string name;
    private string sex;
    private integer age;
    private string phone;
    private double sal;
    private string state;
}

 

　employeemapper.java

package com.rookie.bigdata.mapper;

import com.rookie.bigdata.domain.employee;

import java.util.hashmap;
import java.util.list;

/**
 * @author
 * @date 2018/10/22
 */
public interface employeemapper {

    employee selectemployeebyid(integer id);

    list<employee> selectemployeebyidlike(hashmap<string,object> params);

    list<employee> selctemployeechose(hashmap<string,object> params);

    list<employee>  selecemployeebyidlikes(hashmap<string,object> params);

   void updateemployee(employee employee);

   list<employee> selectemployeein(list<integer> ids);

   list<employee> selectlikename(employee employee);

}

 

mapper.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="com.rookie.bigdata.mapper.employeemapper">

  <select id="selectemployeebyid" parametertype="int"
  resulttype="com.rookie.bigdata.domain.employee">
 select * from bs_employee where id=#{id};
  </select>

    <!--if的使用-->
    <select id="selectemployeebyidlike" resulttype="com.rookie.bigdata.domain.employee">
        select * from bs_employee where state='active'
        <!--这个代表可选条件，如果传入过来有id,就可以进入到该条件里面-->
<!--        <if test="id !=null">
            and id=#{id}
        </if>-->
        <if test="email !=null and password !=null">
            and email=#{email} and password=#{password}
        </if>
    </select>

    <!--choose的使用,用法和if的用法差不多-->
    <select id="selctemployeechose" parametertype="hashmap" resulttype="com.rookie.bigdata.domain.employee">
        select * from bs_employee where state='active'
        <choose>
            <when test="id !=null">
                and id=#{id}
            </when>
            <when test="email !=null and password !=null">
                and email=#{email} and password=#{password}
            </when>
            <otherwise>
                and sex='男'
            </otherwise>
        </choose>
    </select>

    <!--where动态语句-->
    <select id="selecemployeebyidlikes" resulttype="com.rookie.bigdata.domain.employee">
        select * from bs_employee where
        <if test="state !=null">
            state =#{state}
        </if>
        <if test="id !=null">
            and id=#{id}
        </if>
    </select>

    <!--动态更新语句-->
    <update id="updateemployee" parametertype="com.rookie.bigdata.domain.employee">
        update bs_employee
    <set>
        <if test="email != null">email=#{email},</if>
        <if test="password != null">password=#{password},</if>
        <if test="name != null">name=#{name},</if>
        <if test="sex != null">sex=#{sex},</if>
        <if test="age != null">age=#{age},</if>
        <if test="phone != null">phone=#{phone},</if>
        <if test="sal != null">sal=#{sal},</if>
        <if test="state != null">state=#{state}</if>
    </set>
    where id=#{id}
    </update>

    <!--foreach用法-->
    <select id="selectemployeein" resulttype="com.rookie.bigdata.domain.employee">
        select * from bs_employee where id in
        <foreach collection="list" item="item" index="index"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
    <!--bind-->
    <select id="selectlikename" resulttype="com.rookie.bigdata.domain.employee">
        <bind name="pattern" value="'%'+name+'%'"/>
        select * from bs_employee
        where name like #{pattern};
    </select>
</mapper>

测试代码：employeemappertest.java

package com.rookie.bigdata.mapper;

import com.rookie.bigdata.domain.employee;
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 java.io.ioexception;
import java.io.inputstream;
import java.util.arraylist;
import java.util.hashmap;
import java.util.list;

import static org.junit.assert.*;

/**
 * @author
 * @date 2018/10/22
 */
public class employeemappertest {


    public sqlsession session;

    @before
    public void before() throws ioexception {
        // 读取mybatis-config.xml文件
        inputstream inputstream = resources.getresourceasstream("mybatis-config.xml");
        // 初始化mybatis，创建sqlsessionfactory类的实例
        sqlsessionfactory sqlsessionfactory = new sqlsessionfactorybuilder()
                .build(inputstream);
        // 创建session实例
        session = sqlsessionfactory.opensession();
    }

    @test
    public void selectemployeebyid() throws exception {
    }

    @test
    public void selectemployeebyidlike() throws exception {
        employeemapper mapper = session.getmapper(employeemapper.class);
        hashmap<string, object> params = new hashmap<string, object>();
        // 设置id属性
        params.put("id", 1);
        list<employee> list = mapper.selectemployeebyidlike(params);
        // 查看返回结果
        list.foreach(employee -> system.out.println(employee));

    }

    @test
    public void selctemployeechose() throws exception {
        employeemapper mapper = session.getmapper(employeemapper.class);
        hashmap<string, object> params = new hashmap<string, object>();
        // 设置id属性
        // params.put("id", 1);

        list<employee> list = mapper.selctemployeechose(params);
        // 查看返回结果
        list.foreach(employee -> system.out.println(employee));

    }


    @test
    public void selecemployeebyidlikes() throws exception {
        employeemapper mapper = session.getmapper(employeemapper.class);

    }

    @test
    public void updateemployee() throws exception {
        employeemapper mapper = session.getmapper(employeemapper.class);
        employee employee = mapper.selectemployeebyid(1);
        employee.setage(40);
        mapper.updateemployee(employee);
        session.commit();
        session.close();

    }

    @test
    public void selectemployeein() throws exception {
        employeemapper mapper = session.getmapper(employeemapper.class);
        list<integer> ids = new arraylist<>();
        ids.add(1);
        ids.add(2);
        list<employee> employeelist = mapper.selectemployeein(ids);
        system.out.println(employeelist);

    }

    @test
    public void selectlikename() throws exception {
        employeemapper mapper = session.getmapper(employeemapper.class);

        employee employee = new employee();
        employee.setname("zh");
        list<employee> employeelist = mapper.selectlikename(employee);
        system.out.println(employeelist);
    }

}