2021-08-25-mybatis记忆

1.父工程的pom.xml文件要导入的依赖

<dependencies>
//MySQL依赖
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.16</version>
    </dependency>
    //mybatis依赖
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.3</version>
    </dependency>
//junit测试
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>
</dependencies>
//处理配置时导入资源找不到的问题
<build>
    <resources>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>

2.resource资源下的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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTime=Asia/shanghai"/>
                <property name="username" value="root"/>
                <property name="password" value="1234567"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/ke/dao/UserMapper.xml"/>
    </mappers>
</configuration>    

3.工具类（utils）-工厂模式获取连接

 private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            //获取SQLsessionMybatis对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

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

4.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">
<mapper namespace="com.ke.dao.UserDao">
<!--使用resultMap查询-->
<resultMap id="resultMap" type="Admin">
        <association property="pwd" column="apwd"/>
    </resultMap>
    <select id="selectAllAdmin" resultMap="resultMap">
         select * from test.Admin
    </select>
    <!--正常查询-->
    <select id="selectAllAdmin" resultType="Admin">
        select * from test.Admin
    </select>
</mapper>

5.起别名

1.给整个包起别名，别名就是包里面的实体类的名称

 <typeAliases>
        <package name="com.ke.pojo"/>
 </typeAliases>

2.给具体的类起别名

<typeAliases>
        <typeAlias type="com.ke.pojo.Admin" alias="Admin"></typeAlias>
</typeAliases>

以上两种是要在mybatis-config.xml中加入的代码
3.也可以直接在实体类上面使用@Alias("User")的方式起别名

6.外部配置文件

 <properties resource="db.properties"></properties>

以上的一句代码加在mybatis-config.xml中
以下是db.properties里面的代码

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTime=Asia/shanghai
username=root
password=1234567

如果不用配置文件，url中的&要使用&来代替
如果要使用外部配置文件,<environment></environment>就要使用以下代码，可以创建多个<environment></environment>

 <environment id="test">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
 </environment>

7.日志配置

1.标准的日志工厂实现
以下是mybatis-config.xml默认的日志文件

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

2.Log4j日志实现
（1）导入依赖

  <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/ke.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）配置log4j为日志的实现

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

(4)使用
1.在要使用的类中导入包import org.apache.log4j.Logger;注意别导错了。
2.生成log4j的对象 static Logger logger = Logger.getLogger(UserDaoTest.class);里面的参数为当前类的反射。
3.日志级别

logger.info("进入了模糊查询");
    logger.debug("debug进入了log4j");
    logger.error("erroe进入了log4j");

8.mybatis分页

1.使用Map的方式传入参数

 //分页查询
    @Test
    public void limitSelect(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        Map<String,Integer> map = new HashMap<String, Integer>();
        map.put("startsize",2);
        map.put("pagesize",4);
        List<User> list = mapper.limitSelect(map);
        for (User user : list) {
            System.out.println(user);
        }
    }

2.具体的SQL语句

<select id="limitSelect" resultType="User" parameterType="map">
        select * from mybatis.user limit #{startsize},#{pagesize}
    </select>

9.lombok的使用

1.在idea中安装Lombok插件
2.在项目中导入jar包

 <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
 </dependency>

3.实体类上使用注解

10.一对一，一对多处理

1.一对一处理（多对一）
先在学生类中创建一个老师对象
StudentMapper.xml中的代码为

<select id="getStudent" resultMap="StudentTeacher2">
        select s.sid,s.sname,s.sid,t.tid,t.tname
        from student s,teacher t
        where s.tid = t.tid
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <id property="sid" column="sid"></id>
        <result property="sname" column="sname"></result>
        <result property="tid" column="tid"></result>
       	<--property是老师类的实例，javaType是实体类老师-->
        <association property="teacher" javaType="Teacher">
            <id property="tid" column="tid"/>
            <result property="tname" column="tname"></result>
        </association>
    </resultMap>

2.一对多处理
先在老师类中创建一个学生类型的集合

<select id="getTeacherAndStudent" resultMap="StudentTeacher">
        select * from student s,teacher t where s.tid = t.tid
    </select>
<!---->
    <resultMap id="StudentTeacher" type="Teacher">
        <result property="tid" column="tid"/>
        <result property="tname" column="tname"/>
        <--property是集合的实例，ofType是集合的泛型-->
        <collection property="students" ofType="Student">
            <result property="sid" column="sid"/>
            <result property="sname" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

11.动态SQL

1.if标签
测试类中的代码

 BlogDao mapper = sqlSession.getMapper(BlogDao.class);
        HashMap map = new HashMap();
        map.put("title","");
        map.put("author","千木枫");
        List<Blog> blogs = mapper.selectBlogByX(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

BlogMapper.xml中的代码

 select * from blog
        <where>
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>

2.where标签
可以用来代替where 1 = 1
如何where里面的条件不成立，那么就会去除and以及后面的语句（ select * from blog）
成立则拼接上（select * from blog WHERE author = ?）
3.模糊查询
（1）直接传带%%的字符串过去

 blog.setAuthor("%千%");
        List<Blog> blogs = mapper.selectBlogLike(blog);
        for (Blog blog1 : blogs) {
            System.out.println(blog1);
        }
 //BlogMapper.xml中的代码
        <select id="selectBlogLike" resultType="Blog" parameterType="Blog">
        select * from blog
        <where>
            <if test="title != null">
                and title like #{title}
            </if>
            <if test="author != null">
                and author like #{author}
            </if>
        </where>
    </select>

(2)方法二：在写SQL时使用concat函数拼接sql语句–and author like concat(concat("%",#{author},"%"))

@Test
    public void selectBlogLike2(){
        BlogDao mapper = sqlSession.getMapper(BlogDao.class);
        HashMap map = new HashMap();
        map.put("name","view");
        map.put("author","千");
        List<Blog> blogs = mapper.selectBlogLike2(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
    }
//BlogMapper.xml中的代码
<select id="selectBlogLike2" resultType="Blog" parameterType="map">
        select * from blog
        <where>
            <if test="author != null">
                and author like concat(concat("%",#{author},"%"))
            </if>
        </where>
        order by ${name} desc
    </select>

注意使用order by排序时传入的参数使用${参数名}的方式，#{可能会出错}

(3)方法三：使用bind标签将传入的值‘改变’一下

@Test
    public void selectBlogLike3(){
        BlogDao mapper = sqlSession.getMapper(BlogDao.class);
        HashMap map = new HashMap();
        map.put("title","spr");
        map.put("author","千");
        List<Blog> blogs = mapper.selectBlogLike3(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
    }
    //BlogMapper.xml中的代码
<select id="selectBlogByList" resultType="Blog">
        select * from blog where id in
        <foreach collection="list" item="blog"  index="index" open="(" separator="," close=")">
            #{blog.id}
        </foreach>
    </select>

4.foreach批量查询和批量插入
（1）批量插入

@Test
    public void selectBlogByList(){
        BlogDao mapper = sqlSession.getMapper(BlogDao.class);
        List<Blog> list = new ArrayList<Blog>();
        list.add(new Blog(1));
        list.add(new Blog(3));
        list.add(new Blog(5));
        List<Blog> blogs1 = mapper.selectBlogByList(list);
        for (Blog blog : blogs1) {
            System.out.println(blog);
        }
    }
     //BlogMapper.xml中的代码
 <select id="selectBlogByList" resultType="Blog">
        select * from blog where id in
        <foreach collection="list" item="blog"  index="index" open="(" separator="," close=")">
            #{blog.id}
        </foreach>
    </select>

（2）批量插入

 @Test
    public void insertBlogList(){
        BlogDao mapper = sqlSession.getMapper(BlogDao.class);
        List<Blog> list = new ArrayList<Blog>();
        list.add(new Blog(7,"aaa","bbb",800));
        list.add(new Blog(8,"qqq","www",900));
        list.add(new Blog(9,"rrr","eee",1000));
        list.add(new Blog(10,"ttt","bbb",2000));
        list.add(new Blog(11,"yyy","bbb",3000));
        int i = mapper.insertBlogList(list);
        sqlSession.commit();
        System.out.println(i);
    }
     //BlogMapper.xml中的代码
      <insert id="insertBlogList" >
        insert into blog values
        <foreach collection="list" item="blog" separator=",">
            (#{blog.id},#{blog.title},#{blog.author},#{blog.view})
        </foreach>
    </insert>

5.set动态更新语句

@Test
    public void updateBlog(){
        BlogDao mapper = sqlSession.getMapper(BlogDao.class);
        Blog blog = new Blog(7,"学生卡","千木",1111);
        Blog blog1 = new Blog(1);
        int i = mapper.updateBlog(blog1);
        sqlSession.commit();
        System.out.println(i);
    }
     //BlogMapper.xml中的代码
      <update id="updateBlog" parameterType="Blog">
        update blog
            <set>
                <if test="title != null">
                    title = #{title},
                </if>
                <if test="author != null">
                    author = #{author}
                </if>
            </set>
        where id = #{id}
    </update>