MyBatis的学习

以下讲到的数据库操作需要提前建好数据表。

一、Springboot和MyBatis的结合使用：

一、maven依赖，一定要手写：

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.6.1</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.6.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.14</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.46</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

    </dependencies>

二、application.yml文件中的配置信息：

server:
  port: 8084  //端口号
logging:
  path: logs  //日志路径
  file: mylog.log

#数据库信息,数据库和数据库表要提前建好，
spring:
  application:
    name: mytest
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/dbgirl?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
    username: username
    password: password

#mybatis信息，com.course.model是要引用model的包实现mybatis连接数据库
#  mapper-locations:
#  - mapper/*
#作用于mapper文件夹下的所有的文件

mybatis:
  type-aliases-package: com.course.model
  mapper-locations:
  - mapper/*

三、在java包下创建com.course.model的包

image.png

四、创建mapper文件夹和mysql.xml、logback.xml、mybatis-config.xml文件

image.png

logback.xml中的内容,内容可以不用改：

<?xml version="1.0" encoding="UTF-8" ?>
<configuration>

    <include resource="org/springframework/boot/logging/logback/defaults.xml"/>
    <property name="FILE_LOG_PATTERN" value="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{80} - %msg%n"/>
    <property name="LOG_PATH" value="${LOG_PATH:-${LOG_TEMP:-${java.io.tmpdir:-/tmp}}}"/>
    <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${LOG_PATH}/${LOG_FILE}</file>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${LOG_PATH}/${LOG_FILE}.%d{yyyy-MM-dd}</fileNamePattern>
        </rollingPolicy>
        <encoder charset="UTF-8">
            <pattern>${FILE_LOG_PATTERN}</pattern>
        </encoder>
    </appender>


    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>${FILE_LOG_PATTERN}</pattern>
        </encoder>
    </appender>


    <appender name="CRAWLER_LOG" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${LOG_PATH}/event.log</file>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${LOG_PATH}/event.%d{yyyy-MM-dd}.log</fileNamePattern>
            <maxHistory>30</maxHistory>
        </rollingPolicy>
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <pattern>%msg%n</pattern>
        </encoder>
    </appender>


    <logger name="com.business.intelligence.util.CrawlerLogger" level="INFO" additivity="false">
        <appender-ref ref="CRAWLER_LOG"/>
    </logger>

    <root level="INFO">
        <appender-ref ref="STDOUT"/>
        <appender-ref ref="FILE"/>
    </root>


</configuration>

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>
    <typeAliases>
        <package name="com.course.model"/>
    </typeAliases>
    <mappers>
        <mapper resource="mapper/mysql.xml"/>
    </mappers>
</configuration>

com.course.model是新建的包
mapper/mysql.xml 是新建的mysql.xml文件

mysql.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.course">
        <select id="getUserCount" resultType="Integer">
            select count(*) from girls
        </select>
</mapper>

namespace="com.course" 命名空间，是作用与com.course包下的所有类
<select id="getUserCount" resultType="Integer">
select count() from girls
</select>
命名id=getUserCount，返回内容类型是resultType="Integer"
sql语句是select count() from girls

五、因为创建model时没有直接选spring框架，需要要新写一个Application的类，内容如下：

package com.course;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.scheduling.annotation.EnableScheduling;

import javax.annotation.PreDestroy;

@EnableScheduling
@SpringBootApplication
public class Application {
    private static ConfigurableApplicationContext context;

    public static void main(String[] args) {
        Application.context = SpringApplication.run(Application.class,args);
    }
    @PreDestroy
    public void close(){
        Application.context.close();
    }
}

注意路径是在com.course目录下

image.png

扩展1：
application如果写的路径不在根目录下，可以加@ComponentScan(value = "com.course")，去相应的目录下去找controller类
比如application启动类在Application文件夹下，controller类在com.course包下：

image.png

application类中这些写：

image.png

扩展2：
创建项目时如果选择spring框架，则会自动创建启动类，核心语句就一句话：

SpringApplication.run(Chapter11Application.class, args);

image.png

六、创建controller类

package com.course.controller;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class Demo {

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    
    @RequestMapping(value = "/getuserlist",method = RequestMethod.GET)
    public int getuserlist(){
        //getUserCount就是在msql.xml中设置的id值
        return sqlSessionTemplate.selectOne("getUserCount");
    }

}

启动服务，在浏览器中执行http://localhost:8084/getuserlist就能返回girls表中的行数。

七、实现增、删、改操作
mysql.xml中写对应的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.course">
        <select id="getUserCount" resultType="Integer">
            select count(*) from girls
        </select>
    <insert id="addUserUseMybatis" parameterType="com.course.model.Girls">
        insert into girls(id,age,cupsize)
        values (#{id},#{age},#{cupsize})
    </insert>
    <update id="updataUsers" parameterType="com.course.model.Girls">
    update girls set age=#{age},cupsize=#{cupsize}
    where id=#{id}
    </update>
    <delete id="deleteUsers" parameterType="Integer">
    delete from girls where id=#{id}
    </delete>
</mapper>

每个sql语句中的id=“”，一定要跟controller类中的statment对应上
resultType="Integer" 返回类型是整型
parameterType="com.course.model.Girls" 请求参数类型是model中的实体类
parameterType="Integer" 请求类型是整型
参数的格式要这样写：#{id}

对应的controller类中的代码如下：

package com.course.controller;
import com.course.model.Girls;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.mybatis.spring.SqlSessionTemplate;
import org.omg.CORBA.PUBLIC_MEMBER;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RequestMapping("/demo")
@RestController
@Api(value = "/",description = "mybatis和mysql请求接口")
public class Demo {


    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;


    //获取表中数据总数
    @RequestMapping(value = "/getuserlist",method = RequestMethod.GET)
    @ApiOperation(value = "获取用户数量接口",httpMethod = "GET")
    public int getuserlist(){

        //getUserCount就是在mysql.xml中设置的id值
        return sqlSessionTemplate.selectOne("getUserCount");
    }

    //使用mybatis插入数据，post请求，传参为girl实体类中的参数值
    @RequestMapping(value = "/addUserUseMybatis",method = RequestMethod.POST)
    @ApiOperation(value = "用mybatis添加用户信息",httpMethod = "POST")
    public int addUserUseMybatis(@RequestBody Girls girls){
        //addUserUseMybatis就是在mysql.xml中设置的id值
        int result = sqlSessionTemplate.insert("addUserUseMybatis",girls);
        return result;
    }

    //使用mybatis修改数据，post请求，传参为girl实体类中的参数值
    @RequestMapping(value = "/updataUsers",method = RequestMethod.POST)
    @ApiOperation(value = "修改用户信息",httpMethod = "POST")
    public int updataUsers(@RequestBody Girls girls){
        //updataUsers就是在mysql.xml中设置的id值
        return sqlSessionTemplate.update("updataUsers",girls);
    }

    //使用mybatis删除数据,接口中输入id值，删除该id中的数据
    @RequestMapping(value = "/deleteUsers",method = RequestMethod.GET)
    @ApiOperation(value = "删除用户",httpMethod = "GET")
    public int deleteUsers(@RequestParam("id") Integer id){
        //deleteUsers就是在mysql.xml中设置的id值
        return sqlSessionTemplate.delete("deleteUsers",id);
    }



}

启动服务，在postman中请求接口
比如更新接口：更新id为26中的参数值

image.png

比如删除接口：

image.png

调接口操作之后记得看数据库中数据是否有变化。

注：mybatis是基于sql语句来完成的映射，没有自动创建表的功能。可以使用springboot中的Spring-Data-JPA组件来自动创建表。

二、testng和mybatis的使用

1、添加pom文件依赖

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.4</version>
        </dependency>
        <dependency>
            <groupId>org.testng</groupId>
            <artifactId>testng</artifactId>
            <version>6.9.10</version>
        </dependency>

2、在resources中创建databaseConfig.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>
    <typeAliases>
       <typeAlias type="com.course.model.ImageUrl" />
    </typeAliases>
    <!-- 注册对象的空间命名 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!-- 1.加载数据库驱动 -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <!-- 2.数据库连接地址 -->
                <property name="url" value="jdbc:mysql://localhost:3306/dbgirl?serverTimezone=GMT%2B8&amp;useUnicode=true&amp;characterEncoding=utf8&amp;characterSetResults=utf8"/>
                <!-- 数据库用户... -->
                <property name="username" value="rootname"/>
                <!-- 数据库密码... -->
                <property name="password" value="passwordname"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 注册映射文件：java对象与数据库之间的xml文件路径！ -->

    <mappers>
        <mapper resource="mapper/SQLMapper.xml"/>
    </mappers>
</configuration>

image.png image.png

3、SQLMapper.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.course.model">
    <!--获取ImageUrl表中数据-->
    <select id="GetImageUrlcase" parameterType="Integer" resultType="com.course.model.ImageUrl">
            select url from image_url where id=#{id}
    </select>

</mapper>

image.png

4、在Util包中创建DatabaseUtil类，从数据库中获取数据信息，测试类中调用时可以减少一些代码,代码如下：

package com.course.utils;

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.Reader;

public class DatabaseUtil {
    public static SqlSession getsqlsession() throws IOException {
            Reader reader = Resources.getResourceAsReader("databaseConfig.xml");
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession sqlSession = factory.openSession();
            return sqlSession;
    }
}

写该类时注意Resources来源于import org.apache.ibatis.io.Resources;
5、测试类中写一个TestGetImageUrl的类

package com.course.cases;

import com.course.model.ImageUrl;
import com.course.utils.DatabaseUtil;
import org.apache.ibatis.session.SqlSession;
import org.testng.annotations.Test;

import java.io.IOException;

public class TestGetImageUrl {

    @Test
    public void getImageurl() throws IOException {
        //调DatabaseUtil的getsqlsession()方法，返回类型是sqlsession
        SqlSession sqlSession = DatabaseUtil.getsqlsession();
        //调sqlsession的selectone方法，参数值是在SQLMapper.xml中写的sql语句的id,37是数据库中记录id
        ImageUrl imageUrl = sqlSession.selectOne("GetImageUrlcase",37);
        //打印结果
        System.out.println(imageUrl.toString());
    }
}

SQLMapper.xml中写的sql语句id为：

image.png

纠正错误，该sql语句应该这样写，查询id、url，返回结果中才会有id和url

image.png

查看数据库表中内容：

image.png

执行该类，查看结果：

image.png

mybatis扩展：

在SQLMapper.xml中可以这些写：

    <select id="GetImageUrlList" parameterType="Map" resultType="Map">
            select * from image_url limit #{pageNumber}, #{pageSize};
    </select>

参数类型是map，返回类型也是map
limit是sql语句的限制条件，#{pageNumber} 参数是行数，从第几行开始查询， #{pageSize}是一页显示几条

image.png

在测试类中这些建map对象，传值，调sql语句查询数据库,代码如下：

package com.course.cases;

import com.course.model.BadImageUrl;
import com.course.model.ImageUrl;
import com.course.utils.DatabaseUtil;
import org.apache.http.HttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.util.EntityUtils;
import org.apache.ibatis.session.SqlSession;
import org.testng.annotations.Test;
import org.testng.collections.CollectionUtils;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestGetImageUrl {


    @Test
    public void getImageurl() throws IOException {
        //调DatabaseUtil的getsqlsession()方法，返回类型是sqlsession
        SqlSession sqlSession = DatabaseUtil.getsqlsession();
        //调sqlsession的selectone方法，参数值是在SQLMapper.xml中写的sql语句的id,37是数据库中记录id
//


        //获取url总数
        int imageUrlnums = sqlSession.selectOne("GetImageUrlnums");
        //分页，每页2条，总共pagenum页
        int pageSize = 3;//每页处理2条
        //如果pagenum是小数，则处理页数要加1
        int count = imageUrlnums/pageSize+1;
        System.out.println(count);
        //(int)Math.ceil((double)imageUrlnums/pageSize);//总共pagenum页
        //最后一页条数
//        int lastpagenum = imageUrlnums-(pagenum-1)*pageSize;

//        System.out.println(pagenum);//打印总页数
//        System.out.println(lastpagenum);//打印最后一页的条数

        Map<String,Object> param = new HashMap<>();
        param.put("pageSize",pageSize);
        for(int i = 0;i<imageUrlnums;i=i+3) {
            param.put("pageNumber",i);
            List<Map<String,Object>> resultList = sqlSession.selectList("GetImageUrlList",param);

            if(resultList!=null) {

                for(Map<String,Object> m : resultList){
                    try {
                        DefaultHttpClient httpClient = new DefaultHttpClient();
                        HttpGet httpGet = new HttpGet(m.get("url").toString());
                        HttpResponse response = httpClient.execute(httpGet);
                        if (response.getStatusLine().getStatusCode() == 200) {
                            System.out.println("图片存在,id为：" + m.get("id")+" ,图片url: "+ m.get("url").toString());
                        } else {
                            System.out.println("图片不存在,id为：" +m.get("id")+" ,图片url: "+ m.get("url").toString());
                        }
                    }catch (Exception e){
                        System.out.println("网络异常,id为: " +m.get("id")+" ,图片url: "+ m.get("url").toString());
//                        BadImageUrl badImageUrl = new BadImageUrl(m.get("id").toString(),m.get("url").toString());
                        Map<String,Object> map = new HashMap<>();
                        map.put("id",m.get("id"));
                        map.put("badImageurl",m.get("url"));
                        sqlSession.insert("addImageUrltwo",map);
                    }
                }
            }

        }


//        int secondpageid = 37;//初始id值，外层循环会用到
//        int initialid = 37;//初始id值，内存循环会用到
//        for (int j=pagenum;j>0;j--){
//            for (int k=initialid;k<secondpageid+pageSize;k++){
//                ImageUrl imageUrl = sqlSession.selectOne("GetImageUrlcase",k);
//                System.out.println(imageUrl.toString());
//                DefaultHttpClient httpClient = new DefaultHttpClient();
//                HttpGet httpGet = new HttpGet(imageUrl.getUrl());
//                HttpResponse response = httpClient.execute(httpGet);
//                if (response.getStatusLine().getStatusCode() != 200){
//                    System.out.println("url访问不成功");
//                    sqlSession.insert("addImageUrl");
//                    //会报连接失败，怎么处理
//
//                }else {
//                    System.out.println("url访问成功");
//                }
//
//                initialid++;
//            }
//            System.out.println("j的值是"+j);
//
//            if (j == 2){
//                secondpageid = secondpageid+lastpagenum;
//                System.out.println("1的值是"+secondpageid);
//            }else {
//                secondpageid = secondpageid+pageSize;
//                System.out.println("2的值是"+secondpageid);
//            }


//        }
    }
}