mybatis基础学习综合

mybatis学习之省市联动练习

步骤一：创建项目

步骤二：导包

commons-dbcp-1.4.jar
commons-io-1.4.jar
commons-logging-1.2.jar
commons-pool-1.5.4.jar
hamcrest-2.2.jar
hamcrest-core-2.1.jar
junit-4.12.jar
log4j-1.2.17.jar
mybatis-3.4.6.jar
mysql-connector-java-5.1.48.jar

步骤三：实体类

cities实体类

import java.io.Serializable;

public class Cities implements Serializable {
    private int id;
    private String cityid;
    private String city;
    private String provinceid;

    private Provinces province;
    //setter
    //getter
}

provinces实体类

import java.io.Serializable;
import java.util.List;

public class Provinces implements Serializable{
    private int id;
    private String provinceid;
    private String province;
    private List<Cities> citiesList;
    //setter
    //getter
}

核心配置文件mybatis-config.xml

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybaits
username=root
password=123456

log4j.properties

# 全局日志配置
log4j.rootLogger=TRACE, stdout
# MyBatis 日志配置
log4j.logger.com.lanou.mapper=TRACE
# 控制台输出
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--mybaits核心配置-->
<configuration>
    <!--加载.properties配置文件-->
    <properties resource="db.properties"></properties>
    
    <settings>
        <!--开启下划线到驼峰式命名法的自动映射 默认是false-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--是否启用缓存 默认是true-->
        <setting name="cacheEnabled" value="true"/>
        <!--是否启用延迟加载功能 多对象关联 默认是false-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--是否积极加载所有属性-->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!--是否使用自动生成主键 默认false-->
        <setting name="useGeneratedKeys" value="true"/>
        <!--配置mybatis日志输出-->
        <setting name="logImpl" value="LOG4J"/>

    </settings>
    <typeAliases>
        <!--类起别名！-->
       <!-- 如果起别名太多了
        <typeAlias type="com.lanou.pojo.Product" alias="Product"></typeAlias>
        <typeAlias type="com.lanou.pojo.Product" alias="Product"></typeAlias>
        <typeAlias type="com.lanou.pojo.Product" alias="Product"></typeAlias>-->
        <package name="com.lanou.pojo"></package>
    </typeAliases>
    <!-- 引入 pageHelper插件 -->
    <!--注意这里要写成PageInterceptor, 5.0之前的版本都是写PageHelper, 5.0之后要换成PageInterceptor-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!--reasonable: 分页合理化参数，默认值是false,直接根据参数进行查询。
                当该参数设置为true时，pageNum<=0时会查询第一页 pageNum>pages（超过总数时），会查询最后一页。
            -->
            <!--<property name="reasonable" value="true"/>-->
        </plugin>
    </plugins>

    <!--环境 连接数据库的环境 default：默认使用的数据库id-->
    <environments default="development">
        <!--开发库-->
        <!--id:当前连接数据库的唯一标识-->
        <environment id="development">
            <!--增删改 默认使用事务管理 需要手动commit-->
            <transactionManager type="JDBC"/>
            <!--数据源：连接池-->
            <dataSource type="POOLED">
                <!--驱动-->
                <property name="driver" value="${driver}"/>
                <!--url-->
                <property name="url" value="${url}"/>
                <!--用户名-->
                <property name="username" value="${username}"/>
                <!--密码-->
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>

        <!--测试库-->
        <environment id="test">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <!--数据库是写死的-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybaits"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
        <!--mysql oracle-->

    </environments>
    <!--加载所有相关的sql mapper 映射文件-->
    <mappers>
        <mapper resource="com/lanou/mapper/CitiesMapper.xml"/>
        <mapper resource="com/lanou/mapper/ProvincesMapper.xml"/>
    </mappers>
</configuration>

创建对应接口XXXMapper

CitiesMapper接口

import com.lanou.pojo.Cities;

import java.util.List;

public interface CitiesMapper {
    public List<Cities> list();

    //多对一查询
    public List<Cities> listCityToProvince();

    //懒加载
    public List<Cities> listLazyLoad();

}

ProvincesMapper接口

import com.lanou.pojo.Provinces;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ProvincesMapper {
    //只能写一些简单的查询
   // @Select("select * from provinces")
    public List<Provinces> list();

    public List<Provinces> listProvinceToCity();

    //懒加载
    public List<Provinces> listLazyLoadCity();

}

创建对应的sql映射文件

CitiesMapper,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">
<!--sql mapper映射文件-->
<!--namespace:命名空间 唯一的，不能重复并且要求接口的全名（包名.类名）保持一致-->
<mapper namespace="com.lanou.mapper.CitiesMapper">
    <select id="list" resultType="Cities">
        select * from cities
    </select>


    <select id="listCityToProvince" resultMap="myCityToProvice">
      select c.id as cid,c.cityid,c.city,c.provinceid as cprovinceid,p.* from
      cities c join provinces p on c.provinceid = p.provinceid
    </select>
    <resultMap id="myCityToProvice" type="Cities" autoMapping="true">
        <id column="cid" property="id"></id>
        <result column="cprovinceid" property="provinceid"></result>
        <association property="province" javaType="Provinces" column="provinceid" autoMapping="true">
            <id column="id" property="id"></id>
        </association>
    </resultMap>

    <select id="listLazyLoad" resultMap="myCities">
        select * from cities
    </select>
    <resultMap id="myCities" type="Cities">
        <id property="id" column="id"></id>
        <!--给provinces属性赋值，通过select getProvinceById查询赋值
        通过column指定的列传入值道select语句
        -->
        <association property="province" javaType="Provinces" column="provinceid" select="getProvinceBypid">

        </association>
    </resultMap>
    <select id="getProvinceBypid" resultType="Provinces">
        select * from provinces where provinceid = #{provinceid}
    </select>
</mapper>

ProvincesMapper.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">
<!--sql mapper映射文件-->
<!--namespace:命名空间 唯一的，不能重复并且要求接口的全名（包名.类名）保持一致-->
<mapper namespace="com.lanou.mapper.ProvincesMapper">
    <!--
   eviction="FIFO" 缓存策略 先进先出
            LRU – 最近最少使用：移除最长时间不被使用的对象。
            flushInterval="60000"   （刷新间隔）属性可以被设置为任意的正整数，设置的值应该是一个以毫秒为单位的合理时间量。 默认情况是不设置，也就是没有刷新间隔，缓存仅仅会在调用语句时刷新。
            size="512"  缓存区中引用对象的数量
            readOnly="true"  只读
   -->

    <cache
            eviction="FIFO"
            flushInterval="60000"
            size="512"
            readOnly="true"/>
    <!--
    useCache="true": 缓存当前查询结果得返回值
    flushCache="true": 清除缓存 增删改默认是true 查询默认是false
    但是当二级缓存时把flushCaChe="false"
    -->
    <select id="list" resultType="Provinces" useCache="true" flushCache="false">
      select * from provinces
    </select>

    <select id="listProvinceToCity" resultMap="myProvinceToCity">
        select c.id as cid,c.cityid,c.city,c.provinceid,p.* from
        cities c join provinces p on c.provinceid = p.provinceid
    </select>
    <resultMap id="myProvinceToCity" type="Provinces" autoMapping="true">
        <id column="id" property="id"></id>
        <collection property="citiesList" column="provinceid" ofType="Cities" autoMapping="true">
            <id column="cid" property="id"></id>
        </collection>
    </resultMap>
    <select id="listLazyLoadCity" resultMap="myLazyLoadCity">
        select * from provinces
    </select>
    <!--fetchType:抓取策略
        eager：使用理解加载
        lazy：使用懒加载
    -->
    <resultMap id="myLazyLoadCity" type="Provinces">
        <collection property="citiesList"
                    column="provinceid"
                    ofType="Cities"
                    select="getCityByPid"
                    fetchType="eager">
        </collection>
    </resultMap>
    <select id="getCityByPid" resultType="Cities">
        select * from cities where provinceid = #{provinceid}
    </select>
</mapper>

测试

注意：测试时，打断点使用debug测试从输出的日志内容很容易看出来。
简单查询TestSimpleSelect.java

import com.lanou.mapper.CitiesMapper;
import com.lanou.mapper.ProvincesMapper;
import com.lanou.pojo.Cities;
import com.lanou.pojo.Provinces;
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.List;

public class TestSimpleSelect {
    SqlSession sqlSession = null;
    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }
    @Test
    public void list(){
        CitiesMapper citiesMapper = sqlSession.getMapper(CitiesMapper.class);
        List<Cities> citiesList = citiesMapper.list();
        System.out.println(citiesList);
        sqlSession.close();
    }

}

一对多TestOneToMany测试

import com.lanou.mapper.CitiesMapper;
import com.lanou.mapper.ProvincesMapper;
import com.lanou.pojo.Cities;
import com.lanou.pojo.Provinces;
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.List;

public class TestOneToMany {
    SqlSession sqlSession = null;
    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }
    @Test
    public void list(){
        ProvincesMapper provincesMapper = sqlSession.getMapper(ProvincesMapper.class);
        List<Provinces> provincesList = provincesMapper.listProvinceToCity();
        System.out.println(provincesList);
        sqlSession.close();

    }
}

多对一测试TestManyToOne

import com.lanou.mapper.CitiesMapper;
import com.lanou.pojo.Cities;
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.List;

public class TestManyToOne {
    SqlSession sqlSession = null;
    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }
    @Test
    public void list(){
        CitiesMapper citiesMapper = sqlSession.getMapper(CitiesMapper.class);
        List<Cities> citiesList = citiesMapper.listCityToProvince();
        System.out.println(citiesList);
        sqlSession.close();

    }
}

测试一级缓存TestFistLevelCache
显示一个sql语句

import com.lanou.mapper.ProvincesMapper;
import com.lanou.pojo.Provinces;
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.List;

public class TestFistLevelCache {
    SqlSession sqlSession = null;
    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }


    @Test
    public void listPro(){
        ProvincesMapper provincesMapper = sqlSession.getMapper(ProvincesMapper.class);
        List<Provinces> provincesList = provincesMapper.list();
        //一级缓存： 同一个 sqlSession中执行多次相同查询，sql日志只输出一次
        System.out.println(provincesList);
        //清除缓存
        //sqlSession.clearCache();
        List<Provinces> provincesList2 = provincesMapper.list();
        System.out.println(provincesList2);
        sqlSession.close();
    }
}

TestSencondLevelCache二级缓存测试
还是只显示一个sql语句

import com.lanou.mapper.ProvincesMapper;
import com.lanou.pojo.Provinces;
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.List;

public class TestSencondLevelCache {
    SqlSession sqlSession = null;
    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }


    @Test
    public void listPro(){
        ProvincesMapper provincesMapper = sqlSession.getMapper(ProvincesMapper.class);
        List<Provinces> provincesList = provincesMapper.list();
        System.out.println(provincesList);

        //一级缓存： 同一个 sqlSession中执行多次相同查询，sql日志只输出一次
        //二级缓存两个sqlSession
        ProvincesMapper provincesMapper2 = sqlSession.getMapper(ProvincesMapper.class);
        List<Provinces> provincesList2 = provincesMapper2.list();
        System.out.println(provincesList2);
        sqlSession.close();
    }
}

懒加载测试就是不用不显示，但是会去查数据库

import com.lanou.mapper.CitiesMapper;
import com.lanou.mapper.ProvincesMapper;
import com.lanou.pojo.Cities;
import com.lanou.pojo.Provinces;
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.List;

public class TestLazyLoad {
    SqlSession sqlSession = null;
    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }
    @Test
    public void list(){
//        CitiesMapper citiesMapper = sqlSession.getMapper(CitiesMapper.class);
//        List<Cities> citiesList = citiesMapper.listLazyLoad();
//        System.out.println(citiesList);

        ProvincesMapper provincesMapper = sqlSession.getMapper(ProvincesMapper.class);
        List<Provinces> provincesList = provincesMapper.listLazyLoadCity();
        System.out.println(provincesList);
        sqlSession.close();

    }

}

分页测试需要导入包

guava-28.2-jre.jar
jsqlparser-3.1.jar
pagehelper-5.1.10.jar
在核心配置文件中配置插件，已经配过

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.lanou.mapper.CitiesMapper;
import com.lanou.pojo.Cities;
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.List;

public class TestPage {
    SqlSession sqlSession = null;
    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }
    @Test
    public void list(){
        CitiesMapper citiesMapper = sqlSession.getMapper(CitiesMapper.class);
        PageHelper.startPage(2,10);
        List<Cities> list = citiesMapper.list();
        PageInfo<Cities> pageInfo = new PageInfo<>(list);

        sqlSession.close();

    }
}

建表的数据库sql语句在我上传的资源里面