Mybatis(下)

Mybatis(下)

一、MaBatis核心配置文件

Mybatis 中文文档

Mybatis 中文文档

1. properties

定义属性及读取属性文件,取的时候用 $(name) ,name 为之前定义的name

定义属性

SqlMappingConfig.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>
        <property name="jdbc.driver" value="com.mysql.jdbc.Driver" />
        <property name="jdbc.url" value="jdbc:mysql://localhost:3307/mybatis?characterEncoding=utf-8" />
        <property name="jdbc.username" value="root" />
        <property name="jdbc.password" value="123456" />
    </properties>
    
    <!--配置sql打印-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <!-- spring整合后 environments配置将废除 使用spring中的连接池 -->
    <environments default="development">
        <environment id="development">
            <!-- 使用jdbc事务管理 -->
            <transactionManager type="JDBC"/>
            <!-- 数据库连接池 -->
            <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/mybatis/domain/Customer.xml"></mapper>
    </mappers>
</configuration>

读取属性文件

db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3307/mybatis?characterEncoding=utf-8
jdbc.username=root
jdbc.password=123456

SqlMappingConfig.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"/>

    <!--配置sql打印-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <!-- spring整合后 environments配置将废除 使用spring中的连接池 -->
    <environments default="development">
        <environment id="development">
            <!-- 使用jdbc事务管理 -->
            <transactionManager type="JDBC"/>
            <!-- 数据库连接池 -->
            <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/mybatis/domain/Customer.xml"></mapper>
    </mappers>
</configuration>

读取属性优先级

<!--定义属性及读取属性文件-->
<properties resource="db.properties">
    <!--如果外部配置文件有该属性,则内部定义的同名属性被外部属性覆盖-->
    <property name="jdbc.username" value="user" />
    <property name="jdbc.password" value="1234" />
</properties>

2. settings

这是 MyBatis 中极为重要的调整设置,它们会改变 MyBatis 的运行时行。

settings 中的设置名可以查看文档。

这里举个驼峰命名法例子。

不开启驼峰命名法时,数据库里用下划线,domain里也要用下划线。

CREATE TABLE `customer` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_name` varchar(255) DEFAULT NULL,
  `cust_profession` varchar(255) DEFAULT NULL,
  `cust_phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
@Setter@Getter
public class Customer {
    private Integer cust_id;
    private String cust_name;
    private String cust_profession;
    private String cust_phone;
    private String email;

    @Override
    public String toString() {
        return "Customer{" +
                "cust_id=" + cust_id +
                ", cust_name='" + cust_name + '\'' +
                ", cust_profession='" + cust_profession + '\'' +
                ", cust_phone='" + cust_phone + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

如果数据库里用下划线,domain里用驼峰命名法。不开启驼峰命名法的情况下是取不到值的。

@Setter@Getter
public class Customer {
    private Integer custId;
    private String custName;
    private String custProfession;
    private String custPhone;
    private String email;

    @Override
    public String toString() {
        return "Customer{" +
                "custId=" + custId +
                ", custName='" + custName + '\'' +
                ", custProfession='" + custProfession + '\'' +
                ", custPhone='" + custPhone + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}
@Test
public void test2(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
    Customer customerbyId = mapper.getCustomerById(2);
    System.out.println(customerbyId);
}

上面代码会输出 Customer{custId=null, custName='null', custProfession='null', custPhone='null', email='libai@163.com'}

开启驼峰命名法之后就能取到值

<settings>
    <!--配置sql打印-->
    <setting name="logImpl" value="STDOUT_LOGGING"/>
    <!--开启驼峰命名法-->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

会输出 Customer{custId=2, custName='李白', custProfession='刺客', custPhone='18977665521', email='libai@163.com'}

3. typeAliases

类型别名是为 Java 类型设置一个短的名字。

定义单个别名

<!--定义别名-->
<typeAliases>
    <!--单个别名定义-->
    <typeAlias alias="Customer" type="com.mybatis.domain.Customer"/>
</typeAliases>

定义别名后,domain.xml中的,resultType 可以由全限定名称改为一个短的别名。

<!--根据cust_id查询客户-->
<!--<select id="getCustomerById" parameterType="Integer" resultType="com.mybatis.domain.Customer">-->
<select id="getCustomerById" parameterType="Integer" resultType="Customer">
    SELECT * FROM `customer` WHERE cust_id  = #{cust_id}
</select>

批量别名定义

<!--定义别名-->
<typeAliases>
    <!--批量定义别名, 别名为类名-->
    <package name="com.mybatis.domain"/>
</typeAliases>

批量定义包下类的别名,将他们的别名设为类名。定义别名后,domain.xml中的,resultType 可以由全限定名称改为一个短的别名。

注意

如果当前包类与子包类重名,别名相同,会有异常。

这时候可以在类上使用注解 @Alias("别名"),起不同的别名。

4. typeHandlers

无论是 MyBatis 在预处理语句(PreparedStatement)中设置一个参数时,还是从结果集中取出一个值时, 都会用类型处理器将获取的值以合适的方式转换成 Java 类型。

JDK1.8之后实现全部的JSR310规范,日期时间处理上,我们可以使用MyBatis基于JSR310(Date and Time API)
编写的各种日期时间类型处理器。

MyBatis3.4以前的版本需要我们手动注册这些处理器,以后的版本都是自动注册的。

5. Plugins

插件是MyBatis提供的一个非常强大的机制,MyBatis 允许你在已映射语句执行过程中的某一点进行拦截调用。

通过插件来修改MyBatis的一些核心行为。

6. Environments

MyBatis可以配置多种环境,比如开发、测试和生产环境需要有不同的配置。

每种环境使用一个environment标签进行配置并指定唯一标识符,可以通过environments标签中的default属性指定一个环境的标识符来快速的切换环境

Environment子标签

(1)transactionManager 事务管理

type有以下取值:

① JDBC :使用JDBC 的提交和回滚设置,依赖于从数据源得到的连接来管理事务范围

② MANAGED :不提交或回滚一个连接、让容器来管理事务的整个生命周期;ManagedTransactionFactory

③ 自定义 :实现TransactionFactory接口 ;type=全类名/别名

(2)dataSource 数据源

type有以下取值:

① UNPOOLED :不使用连接池UnpooledDataSourceFactory

② POOLED :使用连接池PooledDataSourceFactory

③ JNDI :在EJB 或应用服务器这类容器中查找指定的数据源

④ 自定义 : 实现DataSourceFactory接口,定义数据源的获取方式

实际开发

实际开发中我们使用Spring管理数据源,并进行事务控制的配置来覆盖上述配置

7. databaseIDProvider

MyBatis 可以根据不同的数据库厂商执行不同的语句。

可以能过databaseIDProvider标签来进行设置。

<!--定义数据库厂商-->
<databaseIdProvider type="DB_VENDOR">
    <property name="MySQL" value="mysql"/>
    <property name="DB2" value="db2"/>
    <property name="Oracle" value="oracle" />
    <property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>

databaseId 选择数据库厂商

<!--根据cust_id查询客户-->
<select id="getCustomerById" parameterType="Integer" resultType="Customer" databaseId="mysql">
    SELECT * FROM `customer` WHERE cust_id  = #{cust_id}
</select>

8. mappers

(1)<mapper resource=" ">

使用相对于类路径的资源。

<!--加载映射文件-->
<mappers>
    <mapper resource="com/mybatis/domain/Customer.xml"></mapper>
</mappers>

(2)<mapper class=" " />

使用mapper接口类路径。此种方法要求:

① mapper接口名称和mapper映射文件名称相同

② mapper接口和mapper映射文件放在同一个目录中(同包下)

<!--加载映射文件-->
<mappers>
    <mapper class="com.mybatis.mapper.CustomerMapper"/>
</mappers>

(3)指定包下的所有mapper接口。此种方法要求:

① mapper接口名称和mapper映射文件名称相同

② mapper接口和mapper映射文件放在同一个目录中(同包下)

<!--加载映射文件-->
<mappers>
    <package name="com.mybatis.mapper"/>
</mappers>

二、输出类型

1. 输出简单类型

<select id="getCount" resultType="Integer">
    select count(*) from customer;
</select>

2. Map

(1)第一种形式

key:是列名,value:是列名对应的值

CustomerMapper

public interface CustomerMapper {
    Integer getCount();
    Map<String,Object> getbyId(Integer id);
}

CustomerMapper.xml

<select id="getbyId" resultType="java.util.Map">
    select * from customer where cust_id=#{id}
</select>

Test

@Test
public void test(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
    Map<String, Object> map = mapper.getbyId(1);
    System.out.println(map);
    sqlSession.close();
}

output(key:是列名,value:是列名对应的值)

{cust_profession=射手, cust_name=鲁班, cust_id=1, cust_phone=13499887733, email=12341241@qq.com}

(2)第二种形式

Map<key, 自定义对象> :key为自己指定的列

CustomerMapper

public interface CustomerMapper {
    Integer getCount();
    Map<String,Object> getbyId(Integer id);

    @MapKey("cust_id")
    Map<Integer,Customer> getAll();
}

CustomerMapper.xml

<select id="getAll" resultType="java.util.Map">
    select * from customer;
</select>

Test

@Test
public void test() {
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
    Map<Integer, Customer> map = mapper.getAll();
    for (Integer integer : map.keySet()) {
        System.out.println("Key:" + integer + "; Value:" + map.get(integer));
    }
    sqlSession.close();
}

output(key为自己指定的列:@MapKey("cust_id"))

Key:1; Value:{cust_profession=射手, cust_name=鲁班, cust_id=1, cust_phone=13499887733, email=12341241@qq.com}
Key:2; Value:{cust_profession=刺客, cust_name=李白, cust_id=2, cust_phone=18977665521, email=libai@163.com}
Key:3; Value:{cust_profession=刺客, cust_name=阿轲, cust_id=3, cust_phone=18977665997, email=aike@qq.com}
Key:4; Value:{cust_profession=肉盾, cust_name=德玛西亚, cust_id=4, cust_phone=13700997665, email=demaxiya.126.com6}
Key:5; Value:{cust_profession=战士, cust_name=李信, cust_id=5, cust_phone=13586878987, email=yasuo@qq.com}
Key:6; Value:{cust_profession=辅助, cust_name=奶妈, cust_id=6, cust_phone=13398909089, email=nama@qq.com}
…………

3. resultMap

之有在写输出时使用的都是resultType,但是resultType要求必须得要字段名称和数据库当中的名称一致时才能有值,否则为null。

Customer

@Setter@Getter@ToString
public class Customer {
    private Integer cust_id;
    private String cust_name;
    private String cust_profession;
    private String cust_phone;
    private String email;
}

CustomerMapper

public interface CustomerMapper {
    Integer getCount();
    Customer getbyId(Integer id);
    List<Customer> getAll();
}

CustomerMapper.xml

<select id="getbyId" resultType="Customer">
    select * from customer where cust_id=#{id}
</select>

<select id="getAll" resultType="Customer">
    select * from customer;
</select>

Test

@Test
public void testOne() {
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
    Customer customer = mapper.getbyId(2);
    System.out.println(customer);
    sqlSession.close();
}

@Test
public void testAll() {
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
    List<Customer> list = mapper.getAll();
    for (Customer customer : list) {
        System.out.println(customer);
    }
    sqlSession.close();
}

如果sql查询字段名和pojo的属性名不一致,可以通过resultMap将字段名和属性名作一个对应关系。

Customer

@Setter@Getter@ToString
public class Customer {
    private Integer id;
    private String name;
    private String profession;
    private String phone;
    private String email;
}

CustomerMapper

public interface CustomerMapper {
    Integer getCount();
    Customer getbyId(Integer id);
    List<Customer> getAll();
}

CustomerMapper.xml

<resultMap id="customerMap" type="Customer">
    <!--id 定义的是主键-->
    <id column="cust_id" property="id"/>
    <result column="cust_name" property="name"/>
    <result column="cust_profession" property="profession"/>
    <result column="cust_phone" property="phone"/>
    <result column="email" property="email"/>
</resultMap>

<select id="getbyId" resultMap="customerMap">
    select * from customer where cust_id=#{id}
</select>

<select id="getAll" resultMap="customerMap">
    select * from customer;
</select>

Test

@Test
public void testOne() {
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
    Customer customer = mapper.getbyId(2);
    System.out.println(customer);
    sqlSession.close();
}

@Test
public void testAll() {
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
    List<Customer> list = mapper.getAll();
    for (Customer customer : list) {
        System.out.println(customer);
    }
    sqlSession.close();
}

三、多表操作

1. 表之间关系

(1)一对一

一夫一妻

(2)一对多

一个部门有多个员工,一个员工只能属于某一个部门。

一个班级有多个学生,一个学生只能属于某一个班级。

一个客户有多个订单,一个订单只能属于某一个客户。

(3)多对多

一个老师教多个学生,一个学生可以被多个老师教。

一个学生可以先择多门课程,一门课程可以被多个学生选择。

一个用户可以选择多个角色,一个角色也可以被多个用户选择。

2. 表之间关系建表原则

(1)一对多

在多的一方创建一个外键,指向的一方的主键

(2)多对多

创建一个中间表,中间表至少有两个字段,分别作为外键指向多对多双方的主键

3. ManyToOne 多对一

一个客户有多个订单,一个订单只能属于某一个客户。

order 订单表

-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_name` varchar(255) DEFAULT NULL,
  `order_num` varchar(20) DEFAULT NULL,
  `cust_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `fk_order` (`cust_id`),
  CONSTRAINT `fk_order` FOREIGN KEY (`cust_id`) REFERENCES `customer` (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES ('1', '订单名称1', '10001', '2');
INSERT INTO `order` VALUES ('2', '订单名称2', '10002', '3');
INSERT INTO `order` VALUES ('3', '订单名称3', '10003', '2');
INSERT INTO `order` VALUES ('4', '订单名称4', '10004', '4');

(1)查询

Ⅰ)左连接查询

查询所有的订单及订单所对应的客户。

左连接:把左边表的数据全部查出,右边表只查出满足条件的记录。

sql:

SELECT * FROM `order` AS o LEFT JOIN customer AS c ON o.cust_id = c.cust_id 
① 建立domain

Customer

@Setter@Getter@ToString
public class Customer {
    private Integer cust_id;
    private String cust_name;
    private String cust_profession;
    private String cust_phone;
    private String email;
}

Order

@Setter@Getter@ToString
public class Order {
    private Integer order_id;
    private String order_name;
    private String order_num;
    private Customer customer;
}
② 建立Mapping映射

OrderMapper

public interface OrderMapper {
    /*查询所有的订单*/
    public List<Order> getAllOrders();
}

OrderMapper.xml

(a)不使用 association,同名的列(property和column相同)可以省略

<?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.mybatis.mapper.OrderMapper">

    <resultMap id="orderMape" type="Order">
        <id property="order_id" column="order_id"/>
        <result property="order_name" column="order_name"/>
        <result property="order_num" column="order_name"/>
        <!--级联属性赋值-->
        <result property="customer.cust_id" column="cust_id"/>
        <result property="customer.email" column="email"/>
        <result property="customer.cust_phone" column="cust_phone"/>
        <result property="customer.cust_profession" column="cust_profession"/>
        <result property="customer.cust_name" column="cust_name"/>
    </resultMap>

    <select id="getAllOrders" resultMap="orderMape">
        SELECT * FROM `order` AS o LEFT JOIN customer AS c ON o.cust_id = c.cust_id
    </select>
</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.mybatis.mapper.OrderMapper">

    <resultMap id="orderMape" type="Order">
        <!--级联属性赋值-->
        <result property="customer.cust_id" column="cust_id"/>
        <result property="customer.email" column="email"/>
        <result property="customer.cust_phone" column="cust_phone"/>
        <result property="customer.cust_profession" column="cust_profession"/>
        <result property="customer.cust_name" column="cust_name"/>
    </resultMap>

    <select id="getAllOrders" resultMap="orderMape">
        SELECT * FROM `order` AS o LEFT JOIN customer AS c ON o.cust_id = c.cust_id
    </select>
</mapper>

(b)使用 association(推荐,可以用分布查询),都不能省略

<?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.mybatis.mapper.OrderMapper">

    <resultMap id="orderMape" type="Order">
        <id property="order_id" column="order_id"/>
        <result property="order_name" column="order_name"/>
        <result property="order_num" column="order_name"/>
        <!--关联对象赋值-->
        <association property="customer" javaType="Customer">
            <id property="cust_id" column="cust_id"/>
            <result property="cust_name" column="cust_name"/>
            <result property="cust_profession" column="cust_profession"/>
            <result property="cust_phone" column="cust_phone"/>
            <result property="email" column="email"/>
        </association>
    </resultMap>

    <select id="getAllOrders" resultMap="orderMape">
        SELECT * FROM `order` AS o LEFT JOIN customer AS c ON o.cust_id = c.cust_id
    </select>
</mapper>
③ 测试类

Test

@Test
public void test(){
    SqlSession sqlSession = MybatisUtils.openSession();
    OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    List<Order> list = mapper.getAllOrders();
    for (Order order : list) {
        System.out.println(order);
    }
    sqlSession.close();
}
Ⅱ)分步查询
① 第一步 先查出所有的订单

OrderMapper.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.mybatis.mapper.OrderMapper">

    <resultMap id="orderMape" type="Order">
        <id property="order_id" column="order_id"/>
        <result property="order_name" column="order_name"/>
        <result property="order_num" column="order_name"/>
        <!--关联对象赋值-->
        <association property="customer" javaType="Customer">
            <id property="cust_id" column="cust_id"/>
            <result property="cust_name" column="cust_name"/>
            <result property="cust_profession" column="cust_profession"/>
            <result property="cust_phone" column="cust_phone"/>
            <result property="email" column="email"/>
        </association>
    </resultMap>

    <select id="getAllOrders" resultMap="orderMape">
        SELECT * FROM `order` AS o LEFT JOIN customer AS c ON o.cust_id = c.cust_id
    </select>

    <resultMap id="resultMap" type="Order">
        <id property="order_id" column="order_id"/>
        <result property="order_name" column="order_name"/>
        <result property="order_num" column="order_name"/>
        <!--分步查询-->
        <association property="customer" javaType="Customer"
                     select="com.mybatis.mapper.CustomerMapper.getbyId"
                     column="cust_id">
        </association>
    </resultMap>

    <select id="getOrderbyId" resultMap="resultMap">
        SELECT * FROM `order` WHERE order_id = #{id};
    </select>
</mapper>
② 第二步 根据id查出对应客户

CustomerMapper.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.mybatis.mapper.CustomerMapper">

    <select id="getCount" resultType="Integer">
        select count(*) from customer;
    </select>

    <select id="getbyId" resultType="Customer">
        select * from customer where cust_id=#{id}
    </select>

    <select id="getAll" resultType="Customer">
        select * from customer;
    </select>

</mapper>
③ 测试类
@Test
public void test(){
    SqlSession sqlSession = MybatisUtils.openSession();
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    Order order = orderMapper.getOrderbyId(1);
    System.out.println(order);
    sqlSession.close();
}
Ⅲ )分部查询懒加载
<settings>
    <!--配置sql打印-->
    <setting name="logImpl" value="STDOUT_LOGGING"/>
    <!--延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。-->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!--当开启时,任何方法的调用都会加载该对象的所有属性。否则,每个属性会按需加载-->
    <setting name="aggressiveLazyLoading" value="false"/>
    <!--指定哪个对象的方法触发一次延迟加载。-->
    <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>

(2)添加

先添加客户,获取客户生成的id,再去添加订单

① 添加客户
public interface CustomerMapper {
    Integer getCount();
    Customer getbyId(Integer id);
    List<Customer> getAll();
    /* 保存客户 */
    public void insertCustomer(Customer customer);
}

CustomerMapper.xml

<!--保存客户-->
<!--useGeneratedKeys 使用生成的key-->
<insert id="insertCustomer" parameterType="Customer"
        useGeneratedKeys="true" keyColumn="cust_id" keyProperty="cust_id">
    insert into `customer`(cust_name,cust_profession,cust_phone,email)
    values (#{cust_name},#{cust_profession},#{cust_phone},#{email})
</insert>
② 添加订单
public interface OrderMapper {

    /*查询所有的订单*/
    public List<Order> getAllOrders();

    /*根据id查询订单*/
    public Order getOrderbyId(Integer id);

    /*保存订单*/
    public void insertOrder(Order order);
}

OrderMapper.xml

<!--保存订单-->
<insert id="insertOrder" parameterType="Order">
    insert into `order`(order_name,order_num,cust_id)
    values (#{order_name},#{order_num},#{customer.cust_id})
</insert>
③ 测试
@Test
public void test(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

    Order order = new Order();
    order.setOrder_name("新订单001");
    order.setOrder_num("20000001001");

    Customer customer = new Customer();
    customer.setCust_name("新客户001");
    customer.setCust_phone("137090909090");
    customer.setEmail("123123@163.com");
    customer.setCust_profession("新职业001");
    /* 设置关系 */
    order.setCustomer(customer);
    /* 先添加客户  获取客户生成的id  再去添加订单*/
    customerMapper.insertCustomer(customer);
    System.out.println(customer);
    /* 保存订单 */
    orderMapper.insertOrder(order);
    sqlSession.commit();
    sqlSession.close();
}

4. OnToMany 一对多

(1)查询

Ⅰ)左连接查询

Customer

@Setter@Getter@ToString
public class Customer {
    private Integer cust_id;
    private String cust_name;
    private String cust_profession;
    private String cust_phone;
    private String email;
    private List<Order> orders = new ArrayList<>();
}

CustomerMapper

public interface CustomerMapper {
    Integer getCount();
    Customer getbyId(Integer id);
    /*保存客户*/
    void insertCustomer(Customer customer);

    /*查询所有客户*/
    List<Customer> getAllCustomers();
}

CustomerMapper.xml

<!--查询所有客户-->
<select id="getAllCustomers" resultMap="custMap">
    SELECT * FROM `customer` AS c LEFT JOIN `order` AS o ON c.cust_id = o.cust_id;
</select>
<resultMap id="custMap" type="Customer">
    <id column="cust_id" property="cust_id"/>
    <result column="cust_name" property="cust_name"/>
    <result column="cust_profession" property="cust_profession"/>
    <result column="cust_phone" property="cust_phone"/>
    <result column="email" property="email"/>

    <collection property="orders" ofType="Order">
        <id column="order_id" property="order_id"/>
        <id column="order_name" property="order_name"/>
        <id column="order_num" property="order_num"/>
    </collection>
</resultMap>

测试

@Test
public void test4(){
    /*查询所有客户*/
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    List<Customer> allCustomers = customerMapper.getAllCustomers();
    for (Customer allCustomer : allCustomers) {
        System.out.println(allCustomer);
    }
    sqlSession.close();
}
Ⅱ)分步查询

CustomerMapper.xml

<!--分步查询-->
<select id="getAllCustomers" resultMap="custMap">
    SELECT * FROM `customer`;
</select>
<resultMap id="custMap" type="Customer">
    <id column="cust_id" property="cust_id"/>
    <result column="cust_name" property="cust_name"/>
    <result column="cust_profession" property="cust_profession"/>
    <result column="cust_phone" property="cust_phone"/>
    <result column="email" property="email"/>

    <collection property="orders" javaType="list" ofType="Order"
                select="com.mybatis.mapper.OrderMapper.getOrderbyCustId" column="cust_id">
    </collection>
</resultMap>

OrderMapper

public interface OrderMapper {

    /*查询所有的订单*/
    List<Order> getAllOrders();

    /*根据id查询订单*/
    Order getOrderbyId(Integer id);

    /*保存订单*/
    void insertOrder(Order order);

    /*根据cust_id查询订单*/
    Order getOrderbyCustId(Integer id);
}

OrderMapper.xml

<select id="getOrderbyCustId" resultType="Order">
    SELECT * FROM `order` WHERE cust_id = #{id}
</select>

测试和 左连接查询一样。

(2)添加

CustomerMapper.xml

<!--保存客户-->
<!--useGeneratedKeys 使用生成的key-->
<insert id="insertCustomer" parameterType="Customer"
        useGeneratedKeys="true" keyColumn="cust_id" keyProperty="cust_id">
    insert into `customer`(cust_name,cust_profession,cust_phone,email)
    values (#{cust_name},#{cust_profession},#{cust_phone},#{email})
</insert>

OrderMapper

public interface OrderMapper {

    /*查询所有的订单*/
    List<Order> getAllOrders();

    /*根据id查询订单*/
    Order getOrderbyId(Integer id);

    /*保存订单*/
    void insertOrder(Order order);

    /*根据cust_id查询订单*/
    Order getOrderbyCustId(Integer id);

    /*更新与客户的关系*/
    void updateCustId(@Param("orderId") Integer orderId, @Param("custId") Integer custId);
}

CustomerMapper.xml

<!--保存订单-->
<insert id="insertOrder" parameterType="Order"
        useGeneratedKeys="true" keyColumn="order_id" keyProperty="order_id">
    INSERT INTO `order` (order_name, order_num, cust_id)
    VALUES (#{order_name}, #{order_num}, #{customer.cust_id})
</insert>

<update id="updateCustId">
    UPDATE `order` SET `cust_id` = #{custId} WHERE `order_id` = #{orderId}
</update>

测试

@Test
public void test5(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    Customer customer = new Customer();
    customer.setCust_name("新客户");

    Order order1 = new Order();
    order1.setOrder_name("订单2");

    Order order2 = new Order();
    order2.setOrder_name("订单2");

    customer.getOrders().add(order1);
    customer.getOrders().add(order2);

    /*保存数据*/
    customerMapper.insertCustomer(customer);
    orderMapper.insertOrder(order1);
    orderMapper.insertOrder(order2);

    /*更新关系*/
    for (Order order : customer.getOrders()) {
        orderMapper.updateCustId(order.getOrder_id(),customer.getCust_id());
    }
    sqlSession.commit();
    sqlSession.close();
}

(3)删除

删除时一定要先打破关系再做删除操作

CustomerMapper

public interface CustomerMapper {
    Integer getCount();
    Customer getbyId(Integer id);
    /*保存客户*/
    void insertCustomer(Customer customer);

    /*查询所有客户*/
    List<Customer> getAllCustomers();

    /*根据id删除客户*/
    public void deleteCustomer(Integer id);
}

CustomerMapper.xml

<delete id="deleteCustomer">
    DELETE FROM `customer` WHERE cust_id = #{id}
</delete>

OrderMapper

public interface OrderMapper {

    /*查询所有的订单*/
    List<Order> getAllOrders();

    /*根据id查询订单*/
    Order getOrderbyId(Integer id);

    /*保存订单*/
    void insertOrder(Order order);

    /*根据cust_id查询订单*/
    Order getOrderbyCustId(Integer id);

    /*更新与客户的关系*/
    void updateCustId(@Param("orderId") Integer orderId, @Param("custId") Integer custId);

    /*打破跟客户关系*/
    void updateRelation(Integer custId);
}

OrderMapper.xml

<update id="updateRelation">
    UPDATE `order` SET cust_id = NULL WHERE cust_id = #{custId}
</update>

测试

@Test
public void test6(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    // 一对多删除之前  要先打破关系
    orderMapper.updateRelation(17);
    /*删除客户*/
    customerMapper.deleteCustomer(17);
    sqlSession.commit();
    sqlSession.close();
}

5. ManyToMany 多对多

一个老师教多个学生,一个学生可以被多个老师教。

(0)建表和建实体类

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- 学生表
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stu_id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '学生1');
INSERT INTO `student` VALUES ('2', '学生2');
INSERT INTO `student` VALUES ('3', '学生3');
INSERT INTO `student` VALUES ('4', '学生4');
INSERT INTO `student` VALUES ('5', '学生5');

-- ----------------------------
-- 老师表
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '高老师');
INSERT INTO `teacher` VALUES ('2', '叶老师');
INSERT INTO `teacher` VALUES ('3', '王老师');

-- ----------------------------
-- 学生老师关系表
-- ----------------------------
DROP TABLE IF EXISTS `stu_teacher_rel`;
CREATE TABLE `stu_teacher_rel` (
  `stu_id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`stu_id`,`teacher_id`),
  KEY `fk_teacher_rel` (`teacher_id`),
  CONSTRAINT `fk_stu_rel` FOREIGN KEY (`stu_id`) REFERENCES `student` (`stu_id`),
  CONSTRAINT `fk_teacher_rel` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stu_teacher_rel
-- ----------------------------
INSERT INTO `stu_teacher_rel` VALUES ('1', '1');
INSERT INTO `stu_teacher_rel` VALUES ('2', '1');
INSERT INTO `stu_teacher_rel` VALUES ('1', '2');
INSERT INTO `stu_teacher_rel` VALUES ('3', '2');
INSERT INTO `stu_teacher_rel` VALUES ('4', '2');
INSERT INTO `stu_teacher_rel` VALUES ('5', '3');

Student

@Setter@Getter @ToString
public class Student {
    private Integer stu_id;
    private String stu_name;
}

Teacher

@Setter@Getter@ToString
public class Teacher {
    private Integer teacher_id;
    private String teacher_name;
}

(1)查询

Ⅰ)左连接查询

Teacher

@Setter@Getter@ToString
public class Teacher {
    private Integer teacher_id;
    private String teacher_name;
    private List<Student> students = new ArrayList<>();
}

TeacherMapper

public interface TeacherMapper {
    /*查询老师  并且把关联的学生也查出来*/
    public List<Teacher> getAllTeachers();
}

TeacherMapper.xml

<select id="getAllTeachers" resultMap="teacherMap">
    SELECT*FROM teacher as t
    LEFT JOIN stu_teacher_rel as r on t.teacher_id = r.teacher_id
    LEFT JOIN student as s ON r.stu_id = s.stu_id
</select>

<resultMap id="teacherMap" type="Teacher">
    <id column="teacher_id" property="teacher_id"/>
    <result column="teacher_name" property="teacher_name"/>
    <collection property="students" javaType="list" ofType="Student">
        <id column="stu_id" property="stu_id"/>
        <result column="stu_name" property="stu_name"/>
    </collection>
</resultMap>

测试

@Test
public void test(){
    SqlSession sqlSession = MybatisUtils.openSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    List<Teacher> allTeachers = teacherMapper.getAllTeachers();
    for (Teacher teacher : allTeachers) {
        System.out.println(teacher);
    }
    sqlSession.close();
}
Ⅱ)分步查询

TeacherMapper

public interface TeacherMapper {
    /*查询老师  并且把关联的学生也查出来*/
    public List<Teacher> getAllTeachers();

    /*查询指定的老师*/
    public Teacher getTeacherWithId(Integer id);
}

TeacherMapper.xml

<resultMap id="teacherMap2" type="Teacher">
    <id column="teacher_id" property="teacher_id"/>
    <result column="teacher_name" property="teacher_name"/>
    <collection property="students" ofType="Student"
                select="com.mybatis.mapper.StudentMapper.getStuByTeach"
                column="teacher_id"/>
</resultMap>
<select id="getTeacherWithId" resultMap="teacherMap2">
    SELECT * from teacher WHERE teacher_id = #{id};
</select>

StudentMapper

public interface StudentMapper {
    /*根据老师id查询学生*/
    public List<Student> getStuByTeach(Integer id);
}

StudentMapper.xml

<select id="getStuByTeach" resultType="Student">
    SELECT * from student where stu_id in(
    SELECT stu_id from stu_teacher_rel where teacher_id = #{id})
</select>

测试

@Test
public void test2(){
    SqlSession sqlSession = MybatisUtils.openSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = teacherMapper.getTeacherWithId(2);
    System.out.println(teacher);
    sqlSession.close();
}

(2)添加

添加老师,添加学生,添加中间关系

TeacherMapper

public interface TeacherMapper {
    /*查询老师  并且把关联的学生也查出来*/
    public List<Teacher> getAllTeachers();

    /*查询指定的老师*/
    public Teacher getTeacherWithId(Integer id);

    /*保存老师*/
    void insertTeacher(Teacher teacher);

    /*插入关系表*/
    void insertRelation(@Param("stuId") Integer stuId, @Param("teacherId") Integer teacherId);
}

TeacherMapper.xml

<!--保存老师-->
<insert id="insertTeacher" parameterType="Teacher"
        useGeneratedKeys="true" keyProperty="teacher_id" keyColumn="teacher_id">
    INSERT INTO `teacher` (teacher_name) VALUES (#{teacher_name})
</insert>

<!--插入关系表-->
<insert id="insertRelation">
    INSERT INTO stu_teacher_rel (stu_id, teacher_id) VALUES (#{stuId}, #{teacherId})
</insert>

StudentMapper

public interface StudentMapper {
    /*根据老师id查询学生*/
    public List<Student> getStuByTeach(Integer id);

    /*保存学生*/
    void insertStudent(Student student1);
}

StudentMapper.xml

<!--保存学生-->
<insert id="insertStudent" parameterType="Student"
        useGeneratedKeys="true" keyProperty="stu_id" keyColumn="stu_id">
    INSERT INTO `student` (stu_name) VALUES (#{stu_name})
</insert>

测试

@Test
public void test3(){
    SqlSession sqlSession = MybatisUtils.openSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

    Teacher teacher = new Teacher();
    teacher.setTeacher_name("新老师");

    Student student1 = new Student();
    student1.setStu_name("新学生2");

    Student student2 = new Student();
    student2.setStu_name("新学生2");

    teacher.getStudents().add(student1);
    teacher.getStudents().add(student2);

    /*保存老师*/
    teacherMapper.insertTeacher(teacher);
    /*保存学生*/
    studentMapper.insertStudent(student1);
    studentMapper.insertStudent(student2);

    /*插入关系表*/
    for (Student student : teacher.getStudents()) {
        teacherMapper.insertRelation(student.getStu_id(),teacher.getTeacher_id());
    }

    sqlSession.commit();
    sqlSession.close();
}

四、动态sql

1. 什么是动态sql

通过mybatis提供的各种标签方法实现动态拼接sql。

2. if 标签

需求:根据客户名称和职业来查询客户

public interface CustomerMapper {
    /*根据客户名称和职业来查询*/
    List<Customer> getCustomer (@Param("name") String name, @Param("profession") String profession);
}

CustomerMapper.xml

<select id="getCustomer" resultType="Customer">
    SELECT * FROM `customer` WHERE `cust_name` = #{name} AND `cust_profession` = #{profession}
</select>

测试

@Test
public void test(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    List<Customer> customers = customerMapper.getCustomer("李白","刺客");
    for (Customer customer : customers) {
        System.out.println(customer);
    }
    sqlSession.close();
}

原始查询存在问题

有可能传入的名称或职业为空,那时就找不到记录了,可以使用 if 标签来进行判断。

CustomerMapper.xml

<select id="getCustomer" resultType="Customer">
    SELECT * FROM `customer` WHERE
    <if test="name != null and name != ''">
        `cust_name` = #{name}
    </if>
    <if test="profession != null and profession!=''">
        AND `cust_profession` = #{profession}
    </if>
</select>

这样,能够解决:

List<Customer> customers = customerMapper.getCustomer("李白","刺客");

Preparing: SELECT * FROM customer WHERE cust_name = ? AND cust_profession = ?

Parameters: 李白(String), 刺客(String)

List<Customer> customers = customerMapper.getCustomer("李白",null);

Preparing: SELECT * FROM customer WHERE cust_name = ?

Parameters: 李白(String)

存在的问题

List<Customer> customers = customerMapper.getCustomer(null,"刺客");

Preparing: SELECT * FROM customer WHERE AND cust_profession = ?

Parameters: 刺客(String)

如果第一个条件为null,后面就会多一个and执行就会报错

② List<Customer> customers = customerMapper.getCustomer(null,null);

Preparing: SELECT * FROM customer WHERE

如果所有条件为null,后面就会多一个WHERE执行就会报错

3. Where 标签

解决 if 标签留下的两个问题

① 会自动去掉 where 后面第一个前And

② 如果没有条件就不会加 where

<select id="getCustomer" resultType="Customer">
    SELECT * FROM `customer`
    <where>
        <if test="name != null and name != ''">
            `cust_name` = #{name}
        </if>
        <if test="profession != null and profession!=''">
            AND `cust_profession` = #{profession}
        </if>
    </where>
</select>

4. trim 标签

① prefix:设置前缀,在第一个条件之前加一个前缀。

② prefixOverrides:前缀条件覆盖,把第一个条件之前的值覆盖(变成空)

③ suffix:设置后缀,在最后一个条件之后加一个后缀。

④ suffixOverrides::后缀条件覆盖,把最后一个条件之后的值覆盖(变成空)

<select id="getCustomer" resultType="Customer">
    select * from `customer`
    <trim prefix="where" prefixOverrides="and" suffixOverrides="and" >
        <if test="name != null and name != ''">
            and `cust_name` = #{name}
        </if>
        <if test="profession != null and profession!=''">
            and `cust_profession` = #{profession} and
        </if>
    </trim>
</select>

5. choose 标签

① choose:选择一个条件,只要第一个条件满足,后面条件都不执行

② when:写条件

③ otherwise:除此以外

<select id="getCustomer" resultType="Customer">
    select * from `customer`
    <where>
        <choose>
            <when test="profession != null and profession!=''">
                `cust_profession`=#{profession}
            </when>
            <when test="name != null and name != ''">
                `cust_name`=#{name}
            </when>
            <otherwise>1=1</otherwise>
        </choose>
    </where>
</select>

6. set 标签

① 会自动去掉最后一个设置的逗号

② 会添加update语句中 set

CustomerMapper

public interface CustomerMapper {
    /*根基客户名称和职业来查询*/
    List<Customer> getCustomer (@Param("name") String name, @Param("profession") String profession);

    /*更新客户*/
    public void updateCustomer(Customer customer);
}

CustomerMapper.xml

<update id="updateCustomer">
    update `customer`
    <set>
    <if test="cust_name != null and cust_name !='' ">
        cust_name=#{cust_name},
</if>
    <if test="cust_profession != null and cust_profession !='' ">
        cust_profession=#{cust_profession},
</if>
    </set>
    where cust_id = #{cust_id}
</update>

测试

@Test
public void test2(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    Customer customer = new Customer();
    customer.setCust_id(2);
    customer.setCust_name("李白白");
    customer.setCust_profession("战士");
    customerMapper.updateCustomer(customer);
    sqlSession.commit();
    sqlSession.close();
}

7. foreach 标签

查询条件值为指定的值当中

public interface CustomerMapper {
    /*根据id查询指定的客户 多个客户*/
    List<Customer> getCustomers();
}

CustomerMapper.xml

<select id="getCustomers" resultType="Customer">
    select * from `customer` where `cust_id` in (2,3,4,5,6);
</select>

测试

@Test
public void test3(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    List<Customer> customers = customerMapper.getCustomers();
    for (Customer customer : customers) {
        System.out.println(customer);
    }
    sqlSession.close();
}

给定的值可以以三种形式给出

(1)数组

查询条件值为指定的值当中

public interface CustomerMapper {
    /*根据id查询指定的客户 多个客户*/
    List<Customer> getCustomers(Integer[] ids);
}

CustomerMapper.xml

<select id="getCustomers" parameterType="Integer[]" resultType="Customer">
    select * from `customer` where `cust_id` in
    <foreach collection="array" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</select>

测试

@Test
public void test3(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    List<Customer> customers = customerMapper.getCustomers(new Integer[]{2,3,4,5});
    for (Customer customer : customers) {
        System.out.println(customer);
    }
    sqlSession.close();
}
(2)List

查询条件值为指定的值当中

public interface CustomerMapper {
    /*根据id查询指定的客户 多个客户*/
    List<Customer> getCustomers(List<Integer> ids);
}

CustomerMapper.xml

<select id="getCustomers" parameterType="List" resultType="Customer">
    select * from `customer` where `cust_id` in
    <foreach collection="list" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</select>

测试

@Test
public void test3(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    ArrayList<Integer> arrayList = new ArrayList<>();
    arrayList.add(2);
    arrayList.add(3);
    arrayList.add(4);
    arrayList.add(6);
    List<Customer> customers = customerMapper.getCustomers(arrayList);
    for (Customer customer : customers) {
        System.out.println(customer);
    }
    sqlSession.close();
}
(3)VO

创建 VO (VO = Value Object 数据包装类)

@Setter@Getter
public class QueryVo {
    private Integer[] ids;
    private List<Integer> idList;
}

CustomerMapper

public interface CustomerMapper {
    /*根据id查询指定的客户 多个客户*/
    List<Customer> getCustomers(QueryVo queryVo);
}

CustomerMapper.xml(第一种)

<select id="getCustomers" parameterType="QueryVo" resultType="Customer">
    select * from `customer` where `cust_id` in
    <foreach collection="ids" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</select>

CustomerMapper.xml(第二种)

<select id="getCustomers" parameterType="QueryVo" resultType="Customer">
    select * from `customer` where `cust_id` in
    <foreach collection="idList" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</select>

测试(第一种)

@Test
    public void test3(){
        SqlSession sqlSession = MybatisUtils.openSession();
        CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
        QueryVo queryVo = new QueryVo();
        queryVo.setIds(new Integer[]{2,3,4,5});
        List<Customer> customers = customerMapper.getCustomers(queryVo);
        for (Customer customer : customers) {
            System.out.println(customer);
        }
        sqlSession.close();
    }

测试(第二种)

@Test
public void test3(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    QueryVo queryVo = new QueryVo();
    ArrayList<Integer> arrayList = new ArrayList<>();
    arrayList.add(2);
    arrayList.add(3);
    arrayList.add(4);
    arrayList.add(6);
    queryVo.setIdList(arrayList);
    List<Customer> customers = customerMapper.getCustomers(queryVo);
    for (Customer customer : customers) {
        System.out.println(customer);
    }
    sqlSession.close();
}

8. bind 标签

bind标签:声明一个变量,绑定值;可以取出传入的值,重新处理,赋值给别外一个值

public interface CustomerMapper {
    Customer getCustomerbyId(@Param("id") Integer id);
}

① 声明一个变量,绑定值

CustomerMapper.xml

<select id="getCustomerbyId" resultType="Customer">
    <bind name="id" value="6"></bind>
    select * from `customer` where `cust_id` = #{id}
</select>

测试

@Test
public void test4(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    Customer customer = customerMapper.getCustomerbyId(3);
    System.out.println(customer);
    sqlSession.close();
}

之后查询结果是查id为6的记录。即查询的参数是以bind标签绑定的数据为准

② 可以取出传入的值,重新处理,赋值给别外一个值

CustomerMapper.xml

<select id="getCustomerbyId" resultType="Customer">
    <bind name="id" value="id+2"></bind>
    select * from `customer` where `cust_id` = #{id}
</select>

测试同上,查询结果是查id为5的记录。

9. Sql 片段

Sql中可将重复的sql提取出来,使用时用include引用即可,最终达到sql重用的目的。

sql 片段中还能使用动态标签

<select id="getCustomerbyId" resultType="Customer">
    <include refid="selectID"/> where `cust_id` = #{id}
</select>

<sql id="selectID">
    <choose>
        <when test="id == 2">
            select cust_name from `customer`
        </when>
        <otherwise>
            select * from `customer`
        </otherwise>
    </choose>
</sql>

include 中还能添加属性

<select id="getCustomerbyId" resultType="Customer">
    <include refid="selectID">
        <property name="id" value="3"/>
    </include> where `cust_id` = #{id}
</select>

<!--注意 在include当中定义的property 取的时候 要使用${} -->
<sql id="selectID">
    <choose>
        <when test="${id} == 3">
            select cust_profession from `customer`
        </when>
        <when test="id == 2">
            select cust_name from `customer`
        </when>
        <otherwise>
            select * from `customer`
        </otherwise>
    </choose>
</sql>

五、缓存

1. 缓存介绍

MyBatis中使用缓存来提高其性能。当查询数据时,会先从缓存中取出数据,如果缓存中没有,再到数据库当中查询。

MyBatis中的缓存分为两种:一级缓存和二级缓存,一级缓存是sqlSession级别的,二级缓存是mapper级别的。

2. 一级缓存

(1)一级缓存

本地缓存 (默认开启),在sqlSession没有关闭之前,再去查询时,会从缓存当中取出数据,不会重新发送新的sql。

(2)一级缓存失效

① 如果在查询之前,执行了增/删/改操作,缓存就会失效。

② 手动清空缓存 :sqlSession.clearCache();

③ 如果两次的查询条件不一样,缓存也会失效。

④ 如果两个查询在不同的sqlsession当中。

3. 二级缓存

(1)二级缓存介绍

全局作用域缓存,一个namespace对应一个缓存。

如果会话关闭,一级缓存的数据会被保存到二级缓存中。

不同namespace查出的数据,会放到自己对应的缓存中,现在默认也是打开的。

(2)二级缓存使用步骤

Ⅰ)确保在配置文件当中开启二级缓存
<!--配置sql打印-->
<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
    <!--二级缓存开关-->
    <setting name="cacheEnabled" value="true"/>
</settings>
Ⅱ)在对应的mapper中添加cache标签

cache标签的属性:

① eviction:回收策略

​ a)LRU (默认):最近最少使用,移除最长时间不使用的对象。

​ b)FIFO :先进先出,按对象进入缓存的顺序移除对象。

​ c)SOFT :软引用,移除基本垃圾回收器状态和软引用规则的对象。

​ 没有对象引用了,但是空间足够,暂时不回收。

​ d)WEAK :弱引用,移除基本垃圾回收器状态和弱引用规则的对象。

​ 当没有对象引用了,立即回收。

② flushInterval

刷新间隔,单位毫秒,默认情况下是不设置,没有刷新间隔缓存调用语句时刷新。

③ readOnly :是否只读

​ a)true :告诉Mybatis是只读操作,不去修改数据
​ Mybatis为了加快获取速度,会直接将缓存的引用将给用,不安全,,速度快。
​ b)false :非只读,有可能修改数据
​ Mybatis会利用序列化和反序列化复制一份给你,安全,速度慢。
④ size :可以存放多少个元素

⑤ type :可以用来指定自定义的缓存,可以使用Mybatis自己的缓存,也可以使用第三方的缓存。

Ⅲ )POJO需要实现Serializable接口
@Setter@Getter@ToString
public class Customer implements Serializable {
    private Integer cust_id;
    private String cust_name;
    private String cust_profession;
    private String cust_phone;
    private String email;
}

(3)注意事项

查询的数据都会先放到一级缓存当中,只有会话关闭,一级缓存中的数据才会转称到二级缓存中。

(4)缓存相关属性

① cacheEnabled:只能控制二级缓存的开关

<!--配置sql打印-->
<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
    <!--二级缓存开关-->
    <setting name="cacheEnabled" value="true"/>
</settings>

② select标签中useCache:控制的也是二级缓存是否使用,默认是true

<select id="getCustomerbyId" resultType="Customer" useCache="true">
    select * from `customer` where `cust_id` = #{id}
</select>

③ 增删改标签中flushCache

设置为true, 语句被调用,一级和二级都会被清空。

增删改flushCache默认为true;查询flushCache默认为false

sqlSession.clearCache() :只清除当前session的一级缓存

⑤ localCacheScope :本地缓存作用域

取值:SESSION 和 STATEMENT,默认是 SESSION

可以使用STATEMENT,禁用缓存

<!--配置sql打印-->
<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
    <!--二级缓存开关-->
    <setting name="cacheEnabled" value="true"/>
    <!--关闭一级缓存  不使用缓存-->
    <setting name="localCacheScope" value="STATEMENT"/>
</settings>

4. 缓存使用顺序

先到二级缓存当中查找;如果二级缓存中没有,就去找一级缓存;如果一级缓存中也没有,就去到数据库当中查询。

六、逆向工程(代码生成器)

MyBatis Generator

代码生成器,可以根据指定的表快速生成对应的映射文件,接口,以及Bean类。

支持基本的增删改查,以及QBC风格的条件查询,但是一些复杂的表连接还是需要我们自己来去编写。

使用

1. 下载

下载地址

2. 把相关jar导入到工程当中

3. 创建generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <!--
        context:生成一组对象的环境
            id:必选,上下文id,用于在生成错误时提示
            defaultModelType: 指定生成对象的样式
                1,conditional:类似hierarchical;
                2,flat:所有内容(主键,blob)等全部生成在一个对象中;
                3,hierarchical:主键生成一个XXKey对象(key class),Blob等单独生成一个对象,
                    其他简单属性在一个对象中(record class)
            targetRuntime: 设置自动生成的版本
                1,MyBatis3: 默认的值,生成基于MyBatis3.x以上版本的内容,包括XXXBySample;
                2,MyBatis3Simple: 类似MyBatis3,只是不生成XXXBySample;
            introspectedColumnImpl: 类全限定名,用于扩展MBG
    -->
    <context id="mysqlTables" targetRuntime="MyBatis3Simple">
        <!--
            自动识别数据库关键字,默认false,如果设置为true,根据SqlReservedWords中定义的关键字列表;
            一般保留默认值,遇到数据库关键字(Java关键字),使用columnOverride覆盖
        -->
        <property name="autoDelimitKeywords" value="false"/>

        <!-- 生成的Java文件的编码-->
        <property name="javaFileEncoding" value="UTF-8"/>

        <!-- 格式化java代码-->
        <property name="javaFormatter" value="org.mybatis.generator.api.dom.DefaultJavaFormatter"/>

        <!-- 格式化XML代码-->
        <property name="xmlFormatter" value="org.mybatis.generator.api.dom.DefaultXmlFormatter"/>

        <!--指明数据库的用于标记数据库对象名的符号,比如ORACLE就是双引号,MYSQL默认是`反引号-->
        <property name="beginningDelimiter" value="`"/>
        <property name="endingDelimiter" value="`"/>

        <!-- 不要生成日期和备注 -->
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>

        <!-- 必须要有的,使用这个配置链接数据库 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3307/mybatis?characterEncoding=utf-8"
                        userId="root" password="123456">
        </jdbcConnection>

        <!--
            java模型创建器,是必须要的元素
            负责:1,key类(见context的defaultModelType);2,java类;3,查询类
            targetPackage:生成的类要放的包,真实的包受enableSubPackages属性控制;
            targetProject:目标项目,指定一个存在的目录下,生成的内容会放到指定目录中,如果目录不存在,MBG不会自动建目录
        -->
        <javaModelGenerator targetPackage="com.mybatis.domain" targetProject=".\src">
            <!--  该属性只对MyBatis3有效,
                如果true就会使用构造方法入参,如果false就会使用setter方式。默认为false。
            -->
            <!--<property name="constructorBased" value="false"/>-->

            <!--
                在targetPackage的基础上,根据数据库的schema再生成一层package,
                最终生成的类放在这个package下,默认为false
            -->
            <property name="enableSubPackages" value="true"/>

            <!--
                该属性用来配置实体类属性是否可变,
                如果设置为true,那么constructorBased不管设置成什么,都会使用构造方法入参,并且不会生成setter方法。
                如果为false,实体类属性就可以改变。默认为false。
            -->
            <property name="immutable" value="false"/>

            <!--
                设置所有实体类的基类。
                如果设置,需要使用类的全限定名称。并且如果MBG能够加载rootClass,
                那么MBG不会覆盖和父类中完全匹配的属性。
             -->
            <!--<property name="rootClass" value="cn.admin.sms.core.BaseDomain"/>-->

            <!-- 设置是否在getter方法中,对String类型字段调用trim()方法-->
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

        <!--
            生成SQL map的XML文件生成器,
            注意,在Mybatis3之后,我们可以使用mapper.xml文件+Mapper接口(或者不用mapper接口),
            或者只使用Mapper接口+Annotation,所以,如果 javaClientGenerator配置中配置了需要生成XML的话,
            这个元素就必须配置targetPackage/targetProject:同javaModelGenerator
        -->
        <sqlMapGenerator targetPackage="com.mybatis.mapper" targetProject=".\src">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>

        <!--
            对于mybatis来说,即生成Mapper接口,注意,如果没有配置该元素,那么默认不会生成Mapper接口
            targetPackage/targetProject:同javaModelGenerator
            type:选择怎么生成mapper接口(在MyBatis3/MyBatis3Simple下):
                1,ANNOTATEDMAPPER:会生成使用Mapper接口+Annotation的方式创建(SQL生成在annotation中),不会生成对应的XML;
                2,MIXEDMAPPER:使用混合配置,会生成Mapper接口,并适当添加合适的Annotation,但是XML会生成在XML中;
                3,XMLMAPPER:会生成Mapper接口,接口完全依赖XML;
            注意,如果context是MyBatis3Simple:只支持ANNOTATEDMAPPER和XMLMAPPER
        -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.mybatis.mapper" targetProject=".\src">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>


        <table tableName="customer" domainObjectName="Customer"></table>
        <table tableName="teacher" domainObjectName="Teacher"></table>
        <table tableName="student" domainObjectName="Student"></table>

    </context>
</generatorConfiguration>

4. 编写生成代码

public void test() throws Exception {
    List<String> warnings = new ArrayList<String>();
    boolean overwrite = true;
    File configFile = new File("./resource/generatorConfig.xml");
    ConfigurationParser cp = new ConfigurationParser(warnings);
    Configuration config = cp.parseConfiguration(configFile);
    DefaultShellCallback callback = new DefaultShellCallback(overwrite);
    MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
    myBatisGenerator.generate(null);
}

5. 测试

@Test
public void test2(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    List<Customer> customers = customerMapper.selectAll();
    for (Customer customer : customers) {
        System.out.println(customer);
    }
    sqlSession.close();
}

七、分页插件

1. 下载分页插件

下载地址

  - jsqlparser-2.0.jar
  - pagehelper-5.1.10.jar

2. 配置分页插件

SqlMappingConfig.xml

<!-- 配置分页插件 -->
<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>

3. 使用分页插件

@Test
public void test2(){
    SqlSession sqlSession = MybatisUtils.openSession();
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);

    Page<Object> page = PageHelper.startPage(5, 2);
    List<Customer> customers = customerMapper.selectAll();
    PageInfo<Customer> pageInfo = new PageInfo<>(customers, 4);

    for (Customer customer : pageInfo.getList()) {
        System.out.println(customer);
    }

    System.out.println("当前页:"+pageInfo.getPageNum());
    System.out.println("每页显示记录数:"+pageInfo.getPageSize());
    System.out.println("总页数:"+pageInfo.getPages());
    System.out.println("总记录数:"+pageInfo.getTotal());
    System.out.println("是否有上一页:"+pageInfo.isHasPreviousPage());
    System.out.println("是否有下一页:"+pageInfo.isHasNextPage());
    System.out.println("导航页面:"+ Arrays.toString(pageInfo.getNavigatepageNums()));
    sqlSession.close();
}
posted @ 2019-10-26 09:38  Lomen~  阅读(653)  评论(0编辑  收藏  举报