mybatis - MyBatis - 《Java学习 | 土衣》

基础
高级
- 多表查询
- 注解

基础

mybatis.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>
      <settings>
          <setting name="logImpl" value="LOG4J"/>
      </settings>
      <environments default="dev">
          <environment id="dev">
              <transactionManager type="JDBC"></transactionManager>
              <dataSource type="POOLED">
                  <property name="driver" value="com.mysql.jdbc.Driver"/>
                  <property name="url" value="jdbc:mysql://192.168.0.105:3306/test"/>
                <property name="username" value="root"/>
                <property name="password" value="q1q1q1"/>
              </dataSource>
          </environment>
      </environments>
      <mappers>
          <mapper resource="com/niliv/mapper/UserMapper.xml" />
      </mappers>
  </configuration>

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="com.niliv.mapper.UserMapper">
      <select id="selAll" resultType="com.niliv.pojo.Users">
          select * from users
      </select>
      <select id="selOne" resultType="com.niliv.pojo.Users">
          select * from users where userid='1'
      </select>
  </mapper>

三种查询

package com.niliv.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import java.util.Set;

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.jupiter.api.Test;

import com.niliv.pojo.Users;

public class TestMyBatis {

    @Test
    public void testSelAll() throws IOException {

        InputStream is =  Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();

        //查询不到返回空集合
        List<Users> list = session.selectList("com.niliv.mapper.UserMapper.selAll");
        for (Users users : list) {
            System.out.println(users.getUserid() + " " + users.getNickname());
        }

        session.close();
    }

    @Test
    public void testSelOne() throws IOException {

        InputStream is =  Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();

        //查询不到返回空集合
        Users users = session.selectOne("com.niliv.mapper.UserMapper.selOne");

        System.out.println(users.getUserid()+" " + users.getNickname());
        session.close();
    }

    @Test
    public void testSelMap() throws IOException {

        InputStream is =  Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();

        //查询不到返回空集合
        Map<String, Users> map = session.selectMap("com.niliv.mapper.UserMapper.selAll", "userid");
        Set<String> set = map.keySet();
        for (String k : set) {

            System.out.println(k+" "+map.get(k));
        }

        session.close();
    }
}

log4j

# Set root category priority to INFO and its only appender to CONSOLE.
 log4j.rootCategory=ERROR, CONSOLE
# log4j.rootCategory=DEBUG, CONSOLE, LOGFILE

# 单独设置SQL语句的级别为DEBUG
# 方法级别
# log4j.logger.com.niliv.mapper.UserMapper.selAll=DEBUG
#类级别
# log4j.logger.com.niliv.mapper.UserMapper=DEBUG
#包级别
log4j.logger.com.niliv.mapper=DEBUG

# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=- %m %d{yyyy-MM-dd HH:mm:ss}%n

# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=d:/test.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=- %m %l %d{yyyy-MM-dd HH:mm:ss}%n

使用日志定义包定义properties

<?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="db.properties" />
      <settings>
          <setting name="logImpl" value="LOG4J"/>
      </settings>
      <typeAliases>
          <package name="com.niliv.pojo"/>
      </typeAliases>
      <environments default="dev">
          <environment id="dev">
              <transactionManager type="JDBC"></transactionManager>
              <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>
          <mapper resource="com/niliv/mapper/UserMapper.xml" />
      </mappers>
  </configuration>

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://192.168.0.105:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8
jdbc.username = root
jdbc.password = q1q1q1

由于定义了包，所以mapper返回类型只用写Users可以识别

参数类型可以是对象，map，类型

参数一个 #{0} #{param1}

参数多个最好放入对象传入

<?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.niliv.mapper.UserMapper">
      <select id="selAll" resultType="Users">
          select * from users
      </select>
      <select id="selOne" resultType="Users">
          select * from users where userid='1'
      </select>
      <select id="selById" resultType="Users" parameterType="string">
          select * from users where userid=#{0}
      </select>
      <select id="sel" resultType="Users" parameterType="Users">
          select * from users where userid=#{userid} and nickname=#{nickname}
      </select>
      <select id="selMap" resultType="Users" parameterType="map">
          select * from users where userid=#{id} and nickname=#{name}
      </select>
      <insert id="insUser" parameterType="Users">
          insert into users values(#{userid},#{nickname},#{score},#{sex},#{createtime})
      </insert>
      <update id="updUser" parameterType="Users">
          update Users set nickname=#{nickname} where userid=#{userid}
      </update>
      <delete id="delUser" parameterType="string">
          delete from Users where userid=#{0}
      </delete>
  </mapper>

@Test
    public void testSelById() throws IOException {

        InputStream is =  Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();

        //查询不到返回空集合
        Users users = session.selectOne("com.niliv.mapper.UserMapper.selById","2");

        System.out.println(users.getUserid()+" " + users.getNickname());
        session.close();
    }

    @Test
    public void testSel() throws IOException {

        InputStream is =  Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();

        //多个参数 对象方式
//        Users u = new Users();
//        u.setUserid("2");
//        u.setNickname("aaw");
//        Users users = session.selectOne("com.niliv.mapper.UserMapper.sel",u);
        //map方式
        Map<String, String> map = new HashMap<String, String>();
        map.put("id", "2");
        map.put("name","aaw");

        Users users = session.selectOne("com.niliv.mapper.UserMapper.selMap",map);

        System.out.println(users.getUserid()+" " + users.getNickname());
        session.close();
    }

DML

封装UTIL

package com.niliv.test;

import java.io.IOException;
import java.io.InputStream;

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

public class MyBatisUtil {

    private static SqlSessionFactory factory=null;

    static {

        try {
            InputStream is = Resources.getResourceAsStream("mybatis.xml");
            factory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {

            e.printStackTrace();
        }

    }

    public static SqlSession getSession() {
        SqlSession session=null;
        if(factory!=null) {
            session = factory.openSession(true); //true 打开自动提交
        }
        return session;
    }
}

package com.niliv.test;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.niliv.pojo.Users;

public class TestMyBatisDQL {

    @Test
    public void testIns() {
        SqlSession session = MyBatisUtil.getSession();

        Users users = new Users();
        users.setUserid("1001");
        users.setNickname("ddd");
        users.setScore(196);
        users.setSex("男");
        users.setCreatetime("2018-02-01 00:00:00");

        int flag = session.insert("com.niliv.mapper.UserMapper.insUser", users);
        if(flag>0) {
            //session.commit();
            System.out.println("success!");
        }else {
            //session.rollback();
            System.out.println("fail!");
        }

        session.close();
    }

    @Test
    public void testUpd() {
        SqlSession session = MyBatisUtil.getSession();

        Users users = new Users();
        users.setUserid("1001");
        users.setNickname("ccc");


        int flag = session.update("com.niliv.mapper.UserMapper.updUser", users);
        if(flag>0) {
            System.out.println("success!");
        }else {
            System.out.println("fail!");
        }

        session.close();
    }

    @Test
    public void testDel() {
        SqlSession session = MyBatisUtil.getSession();


        int flag = session.delete("com.niliv.mapper.UserMapper.delUser", "1001");
        if(flag>0) {
            //session.commit();
            System.out.println("success!");
        }else {
            //session.rollback();
            System.out.println("fail!");
        }

        session.close();
    }
}

高级

接口绑定

建立mapper接口

public interface UserMapper {

    List<Users> selAll();
    //@Param注解让参数名字可以在xml中使用，如果不定义这个，xml参数要用0 1
    Users selByUN(@Param("userid")String userid, @Param("nickname")String nickname);
}

建立接口对应的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="com.niliv.mapper.UserMapper">
      <select id="selAll" resultType="Users">
          select * from users
      </select>
      <select id="selByUN" resultType="Users">
          select * from Users where userid=#{userid} and nickname=#{nickname}
      </select>
  </mapper>

让mybatis能扫描到接口

<?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="db.properties" />
      <settings>
          <setting name="logImpl" value="LOG4J"/>
      </settings>
      <typeAliases>
          <package name="com.niliv.pojo"/>
      </typeAliases>
      <environments default="dev">
          <environment id="dev">
              <transactionManager type="JDBC"></transactionManager>
              <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="com.niliv.mapper"/>
      </mappers>
  </configuration>

使用接口，解决session的执行函数不能传多个参数的问题，注意接口参数的格式

@Test
public void testBind() {
    SqlSession session = MyBatisUtil.getSession();

    UserMapper userMapper = session.getMapper(UserMapper.class);
    List<Users> list = userMapper.selAll();
    for (Users users : list) {
        System.out.println(users.getUserid() + " "+ users.getNickname());
    }


    session.close();
}

@Test
public void testSelParams() {
    SqlSession session = MyBatisUtil.getSession();

    UserMapper userMapper = session.getMapper(UserMapper.class);
    Users users = userMapper.selByUN("1000", "zzz");

    System.out.println(users.getUserid() + " "+ users.getNickname());



    session.close();
}

动态SQL

<?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.niliv.mapper.UserMapper">
      <!-- 复用的查询列 -->
      <sql id="users">
          userid,nickname,score
      </sql>
      <!-- 查询所有 -->
      <select id="selAll" resultType="Users">
          select 
          <include refid="users"></include>
           from users
      </select>
      <!-- 查询条件参数 -->
      <select id="selByUN" resultType="Users">
          select 
          <include refid="users"></include> 
          from Users where userid=#{userid} and nickname=#{nickname}
      </select>
      <!-- where自动去掉and if -->
      <select id="sel" resultType="Users">
          select 
          <include refid="users"></include> 
          from Users 
          <where>
              <if test="userid != null and userid!=''">
              and userid = #{userid}
              </if>
              <if test="nickname != null and nickname!=''">
                  and nickname = #{nickname}
              </if>
          </where>
      </select>
      <!-- choose 类似switch -->
      <select id="sel1" resultType="Users">
          select 
          <include refid="users"></include> 
          from Users 
          <where>
              <choose>
                  <when test="userid != null and userid!=''">
                      and userid = #{userid}
                  </when>
                  <when test="nickname != null and nickname!=''">
                      and nickname = #{nickname}
                  </when>
                  <otherwise>
                      and 1=1
                  </otherwise>
              </choose>
          </where>
      </select>
      <!-- bind 对参数操作 -->
      <select id="sel2" resultType="Users">
          select 
          <include refid="users"></include> 
          from Users 
          <where>
              <if test="nickname != null and nickname!=''">
                  <bind name="nickname" value="'%'+nickname+'%'"/>
                  and nickname like #{nickname}
              </if>
          </where>
      </select>
      <!-- foreach 转换集合成数组 -->
      <select id="selIn" parameterType="list" resultType="Users">
          select 
          <include refid="users"></include>
           from Users where score in 
          <foreach collection="list" open="(" separator="," close=")" item="item">
              #{item}
          </foreach>
      </select>
      <!-- set 类似于where -->
      <update id="updUser" parameterType="Users">
          update Users 
          <set>
              userid=#{userid},
              <if test="nickname != null and nickname!=''">
                  nickname=#{nickname},
              </if>
              <if test="score != null and score>=0">
                  score=#{score},
              </if>
              <if test="sex != null and sex!=''">
                  sex=#{sex},
              </if>
              <if test="createtime != null and createtime!=''">
                  createtime=#{createtime},
              </if>
          </set>
          where userid=#{userid}
      </update>
      <!-- trim -->
      <update id="updUserTrim" parameterType="Users">
          update Users 
          <trim prefix="set" prefixOverrides="" suffix="" suffixOverrides=",">
              nickname=#{nickname},
          </trim>
          where userid=#{userid}
      </update>
  </mapper>

List<Users> selAll();

//@Param注解让参数名字可以在xml中使用，如果不定义这个，xml参数要用0 1
Users selByUN(@Param("userid")String userid, @Param("nickname")String nickname);

//动态sql
//where if
List<Users> sel(@Param("userid")String userid, @Param("nickname")String nickname);
//choose
List<Users> sel1(@Param("userid")String userid, @Param("nickname")String nickname);
//bind
List<Users> sel2(@Param("userid")String userid, @Param("nickname")String nickname);
//foreach
List<Users> selIn(@Param("list")List<Integer> list);
//set
int updUser(Users users);
//trim
int updUserTrim(Users users);

@Test
public void testDynamicSql() {
    SqlSession session = MyBatisUtil.getSession();

    UserMapper userMapper = session.getMapper(UserMapper.class);
    //if类似and
    //List<Users> list = userMapper.sel("1000", "zzz");
    //choose 条件了类似或的关系，一个成立就不看后面
    //List<Users> list = userMapper.sel1("1000", "zzz");
    //like
    List<Users> list = userMapper.sel2("", "h");

    for (Users users : list) {
        System.out.println(users.getUserid() + " "+ users.getNickname());
    }



    session.close();
}

@Test
public void testDSUpd() {
    SqlSession session = MyBatisUtil.getSession();

    UserMapper userMapper = session.getMapper(UserMapper.class);
    Users users = new Users();
    users.setUserid("1000");
    users.setNickname("hhh");
    //        users.setScore(100);
    //        users.setSex("女");
    //        users.setCreatetime("2018-10-31 18:35:11");

    //int flag = userMapper.updUser(users);
    int flag = userMapper.updUserTrim(users);

    System.out.println(flag);



    session.close();
}

@Test
public void testSelIn() {
    SqlSession session = MyBatisUtil.getSession();

    UserMapper userMapper = session.getMapper(UserMapper.class);

    List<Integer> list = new ArrayList<Integer>();
    list.add(45);
    list.add(200);
    list.add(144);
    list.add(16);
    list.add(100);
    List<Users> users = userMapper.selIn(list);

    for (Users user : users) {
        System.out.println(user.getUserid() + " "+ user.getNickname()+ " " + user.getScore());
    }



        session.close();
    }

缓存

一级缓存：缓存到id，缓存到session

二级缓存：缓存到id，缓存到factory

 <mapper namespace="com.niliv.mapper.UserMapper">
     <!-- 实体类如果没有序列化，这里要加readOnly -->
      <cache readOnly="true" />   
      <select id="selAll" resultType="Users">
          select * from Users 
      </select>
  </mapper>

修改实体列名

  <mapper namespace="com.niliv.mapper.UserMapper">
      <resultMap type="Users" id="umap">
        <id column="userid" property="id" />
        <result column="nickname" property="name" />
        <result column="createtime" property="time" />
      </resultMap>
      <select id="selAll" resultMap="umap">
          select * from Users 
      </select>
  </mapper>

public class Users implements Serializable {

    private String id;
    private String name;
    private int score;
    private String sex;
    private String time;
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getScore() {
        return score;
    }
    public void setScore(int score) {
        this.score = score;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getTime() {
        return time;
    }
    public void setTime(String time) {
        this.time = time;
    }
    @Override
    public String toString() {
        return "Users [id=" + id + ", name=" + name + ", score=" + score + ", sex=" + sex + ", time=" + time + "]";
    }
}

多表查询

注解

@Select("select * from t_student")
    List<Student> selAll();

@Insert("insert into t_student values(default,#{name},#{age},#{gender},#{cid})")
int insStu(Student student);

@Update("update t_student set age=#{1} where id=#{0}")
int updStu(int id, int age);

@Delete("delete from t_student where id=#{0}")
int delStu(int id);

@Select("select * from t_student")
@Results(value= {
    @Result(column="id",property="id",id=true),
    @Result(column="name",property="name"),
    @Result(column="age",property="age"),
    @Result(column="gender",property="gender"),
    @Result(column="cid",property="cid"),
        @Result(property="clazz",one=@One(select="com.niliv.mapper.ClazzMapper.selById"),column="cid")
    })
    List<Student> sel();

注解