【SSM】Mybatis_01基本使用

1、环境配置

官网：Mybatis官网-中文

1.1导入依赖

<dependencies>
    <!--mysql 8-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.22</version>
    </dependency>
    <!--单元测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
        <scope>test</scope>
    </dependency>
    <!--mybatis-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.6</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/log4j/log4j -->
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
</dependencies>

1.2数据库配置 db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username=root
password=123456

mysql5 和msql8是些区别

1. jar mysql.jdbc.Driver(5的) mysql.cj.jdbc.Driver（8的）
2. mysql8需要 设置时区。

1.3 mybatis-config.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="db.properties"/>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--mysql 8的-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
</configuration>    

1.4 编写连接数据库的 工具类

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 java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {

    static  SqlSessionFactory sqlSessionFactory;

    static {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }

}

1.5 单元测试

public class UserDaoTest {
    @Test
    public void  test(){
        //得到sqlSession
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //获取的Mapper
        UserMapper mapper=sqlSession.getMapper(UserMapper.class);
        
        List<User> user=mapper.getUser();
        
        for (User u:user){
            System.out.println(u.toString());
        }
        //关闭数据连接
        sqlSession.close();
    }
}

2、基本使用步骤

2.1 创建好数据库 中的数据表

2.2 创建数据表对应的实体类

public class User {
    private int id;
    private String name;
    private String pwd;
    private String sex;
    private String address;
    private String telephone;
    private int gradeId;
    
    ....
}

2.3 定义操作实体类的接口

package com.jsd.dao;
import java.util.List;

public interface UserMapper {
    //查询所有用户
    List<User> getUser();
}

2.4 编写对应接口的 Mapper.xml

由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">
<!--通过命名空间和User接口 绑定。这个xml文件来实现这个接口-->
<mapper namespace="com.jsd.dao.UserMapper">
    <!--id:方法名，resultType:方法的类型-->
    <select id="getUser" resultType="com.jsd.dao.User">
    	select * from test.js_user
     </select>
</mapper>

2.5 在mybatis-config.xml中映射Mapper文件

<!--注册 -->
<mappers>
    <mapper resource="mapper/userMapper.xml"/>
</mappers>

• 注意事项--资源过滤

  <build>
      <resources>
          <resource>
              <directory>src/main/java</directory>
              <includes>
                  <include>**/*.xml</include>
                  <include>**/*.properties</include>
              </includes>
              <filtering>true</filtering>
          </resource>
          <resource>
              <directory>src/main/resources</directory>
              <includes>
                  <include>**/*.xml</include>
                  <include>**/*.properties</include>
              </includes>
              <filtering>true</filtering>
          </resource>
      </resources>
  </build>
  

3、CRUD 操作

对应接口

public interface UserMapper {
    List<User> getUser();
    int  addUser(User user);
    int delUser(int id);
    int updateUser(User user);
    User getUserById(int id);
}

3.1 Create

<insert id="" parameterType=""></insert>

<!--用户插入-->
<insert id="addUser" parameterType="com.jsd.dao.User">
    insert into js_user (name,pwd,sex,address,telephone,gradeId) values (#{name },md5(#{pwd}),#{sex},#{address},#{telephone},#{gradeId});
</insert>

3.2 Retrieve

<select id="" parameterType="" resultType=""></select>
        
<!--查找-->
<select id="getUserById" parameterType="int" resultType="com.jsd.dao.User">
    select * from js_user  where id = #{id};
</select>

3.3 Update

<update id="" parameterType=""></update>

<!--修改-->
<update id="updateUser" parameterType="com.jsd.dao.User">
    update js_user set name =#{name}  where id=#{id} ;
</update>

3.4 Delete

<delete id="" parameterType=""></delete>

<!--删除-->
<delete id="delUser" parameterType="int">
    delete from js_user where id = #{id}
</delete>

4 日志的使用

4.1 默认 STDOUT_LOGGING

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

4.2 LOG4J的简单使用

1, 依赖

<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

2、log4j.properties

# 将等级为DEBUG的日志信息输出到console和file这两个目的地中，console和file的定义在下面的代码中
log4j.rootLogger = DEBUG,console ,file

#控制台输出的相关配置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold = DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern = [%c]-%m%n

#文件输出的相关配置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File = ./log/log.log
log4j.appender.file.MaxFileSize = 10mb
log4j.appender.file.Threshold = DEBUG
log4j.appender.file.layout = org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern = [%p][%d{yy-MM-dd}][%c]-%m%n

#日志输出级别
log4j.logger.org.mybatis = DEBUG
log4j.logger.java.sql = DEBUG
log4j.logger.java.sql.Statement = DEBUG
log4j.logger.java.sql.ResultSet = DEBUG
log4j.logger.java.sql.PreparedStatement = DEBUG

3、配置

<settings>
    <setting name="logImpl" value="LOG4J"/>
</settings>

4、使用 (junit 单元测试)

//LOG4J的使用方法
    static Logger logger = Logger.getLogger(UserDaoTest.class);
    @Test
    public  void testLog4j(){
        logger.info("info:测试LOG4J");
        logger.debug("debug:测试log4j");
        logger.error("error:log4j");
    }

5 常用的一些配置

以下的这些标签 需要按照顺序排列

顺序：

(properties?,

settings?,

typeAliases?,、typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,、environments?,databaseIdProvider?,

mappers?)".

<?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"/>
    <!--&lt;!&ndash;单独给一个类起名字&ndash;&gt;-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <!--<typeAliases>-->
    <!--扫描包-->
    <!--别名就是包内的 类名（小写开头）-->
    <typeAliases>
        <package name="com.jsd.pojo"/>
        <package name="com.jsd.dao"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--mysql 8的-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--注册 -->
    <mappers>
        <mapper resource="mapper/userMapper.xml"/>
    </mappers>
</configuration>    

6、一对多 和 多对一

6.1 环境准备

• 实体类

public class Grade {
    private int gradeId;
    private String gradeName;
    //非基本类型
    private List<User> userList;
}


public class User {
    private int id;
    private String name;
    private String pwd;
    private String sex;
    private String address;
    private String telephone;
    private int gradeId;
    //非基本类型
    private  Grade grade;
}

6.2多对一：查询 多名用户所处的年级段

//接口
public interface UserMapper {
    List<User> getUser();
}

-- SQL 语句
select u.id as uid,u.name as uname,g.gradename as gname
     from js_user u,grade g
     where u.gradeid= g.gradeid;

<!--按照结果嵌套处理

利用 resultMap 进行结果集的映射
-->
<select id="getUser" resultMap="ST">
  select u.id as uid,u.name as uname,g.gradename as gname
  from js_user u,grade g
  where u.gradeid= g.gradeid;
</select>

<resultMap id="ST" type="User">
  <!--property：user中的属性 column：sql语句中的字段名（别名也行）-->
  <result property="id" column="uid"/>
  <result property="name" column="uname"/>

  <!--property：user中的属性，javaType：属性的自定义类型-->

  <association property="grade" javaType="Grade">
      <result property="gradeName" column="gname"/>
  </association>
</resultMap>

6.3一对多：查询 一个年级段中有多少人

//接口
public interface GradeMapper {
    List<Grade>getGradeById(@Param("gradeId") int gradeId );
}

-- sql 语句
select u.id uid,u.name uname,g.gradeid gid,g.gradename gname
        from js_user u ,grade g
        where  g.gradeid=u.gradeid and g.gradeid = #{gradeId}

<select id="getGradeById" resultMap="TG">
    select u.id uid,u.name uname,g.gradeid gid,g.gradename gname
    from js_user u ,grade g
    where  g.gradeid=u.gradeid and g.gradeid = #{gradeId}
</select>
<resultMap id="TG" type="Grade">
    <result property="gradeId" column="gid"/>
    <result property="gradeName" column="gname"/>
    <!--集合中的 泛型的类型 使用oftype  -->
    <collection property="userList" ofType="User">
        <result property="id" column="uid"/>
        <result property="name" column="uname"/>
    </collection>
</resultMap>

6.4总结

• 对于复杂的属性得特殊处理

  • 对象使用：association标签 多对一

    private  Grade grade; //一个对象
    

    <association property="grade" javaType="Grade">
        <result property="gradeName" column="gname"/>
    </association>
    
    <!-- property 是属性名 javatType 是标识 类型-->
    

  • 集合使用：collection 标签 一对多

     private List<User> userList;
    

    <collection property="userList" ofType="User">
        <result property="id" column="uid"/>
        <result property="name" column="uname"/>
    </collection>
    
    <!--集合中的 泛型的类型 使用oftype  -->
    

7、动态SQL

动态SQL 就是根据不同的条件生成不同的SQL语句。可以方便我们在SQL层面生成SQL的代码

1、IF

<select id="getUser" resultType="user" parameterType="map">
    select  * from js_user where status='active'
    <if test="address!=null">
        and address=#{address}
    </if>
    <if test="sex!=null">
        and sex=#{sex}
    </if>
</select>

2、where

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。 也就说后面有where条件的 才会使用where 元素

而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。 也就说如果第一 where and xxx=xxx 这样的情况 会剔除and

<select id="getUser" resultType="user" parameterType="map">
    select * from js_user
    <where>
        <if test="address!=null">
            address=#{address}
        </if>
        <if test="sex!=null">
            and sex=#{sex}
        </if>
    </where>;
</select>

3、choose(when,otherwise)

choose 有些类似 switch 的作用。

其中when是条件分支。otherwise 是在没有执行when分支 时使用的。

select * from js_user
<where>
    <choose>
        <when test="sex!=null">
            sex=#{sex}
        </when>
        <when test="address!=null and sex!=null">
            address=#{address} and sex=#{sex}
        </when>
        <otherwise>
            and gradeId = #{gradeId}
        </otherwise>
    </choose>
</where>;
</select>

4、set

update的时候 使用：set

where一样 会除去多余的东西

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号

<update id="updateUser" parameterType="map" >
    update js_user
    <set>
        <if test="name!=null">
            name =#{name},
        </if>
        <if test="address!=null">
            address=#{address},
        </if>
        <if test="sex!=null">
            sex=#{sex},
        </if>
        <if test="telephone!=null">
            telephone = #{telephone}
        </if>
    </set>
    where id = #{id};
</update>

@Test
public void testSet(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    HashMap<String, Object> map = new HashMap<String, Object>();
    map.put("id",16);
    map.put("sex","女");
    map.put("address","邢台");
    map.put("name","alias");
    int num = mapper.updateUser(map);
    sqlSession.commit();
    System.out.println(mapper.getUserById(16));
    sqlSession.close();
}

5、foreach

主要用来 构造 in 中的集合()

<select id="getUserInfo" parameterType="map" resultType="User">
    select * from js_user
    <where>
        <!--collection ：集合 ，item： 集合内的元素，  open： 起始 ， close： 结束 ，separator:分隔符-->
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id=#{id}
        </foreach>
    </where>;
</select>

@Test
public  void  testForEach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    HashMap map = new HashMap();
    ArrayList<Integer> ids = new ArrayList<Integer>();
    ids.add(2);
    ids.add(18);
    ids.add(8);
    ids.add(6);
    ids.add(9);
    map.put("ids",ids);
    List<User> userInfo = mapper.getUserInfo(map);
    for (User user : userInfo) {
        System.out.println(user);
    }
    sqlSession.close();
}

注意事项 关键字（and or …）写在“”内时 注意要和其他字符 使用空格 隔开。