mybatis高级查询和注解开发

发布时间：2024年01月22日

mybatis高级查询和注解开发

一.mybatis高级查询

1.环境准境

1.1sql:

CREATE TABLE `tb_item`(
	`id` int(11) not null AUTO_INCREMENT,
	`item_name` varchar(32) NOT NULL COMMENT '商品名称',
	`item_price` float(6,1) NOT NULL COMMENT '商品价格',
	`item_detail` text COMMENT '商品描述',
 PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `tb_item` VALUES('1','iPhone 6','5288.0','苹果公司新发布的手机产品。');
INSERT INTO `tb_item` VALUES('2','iPhone 6 Plus','6288.0','苹果公司发布的大屏手机。');

CREATE TABLE `tb_user`(
	`id` bigint(20) NOT NULL AUTO_INCREMENT,
	`user_name` varchar(100) DEFAULT NULL COMMENT '用户名',
	`password` varchar(100) DEFAULT NULL COMMENT '密码',
	 `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `age` int(10) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别，1男性，2女性',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `tb_user`VALUES('1','zhangsan','123456','张三','30','1');
INSERT INTO `tb_user`VALUES('2','lishi','123456','李四','21','0');
INSERT INTO `tb_user`VALUES('3','wangwu','123456','王五','22','1');
INSERT INTO `tb_user`VALUES('4','zhangwei','123456','张伟','20','1');
INSERT INTO `tb_user`VALUES ('5', 'lina', '123456', '李娜','28', '0');
INSERT INTO `tb_user`VALUES ('6', '蔡徐坤', '123456', '小菜','18', '1');

CREATE TABLE `tb_order`(
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`user_id` bigint(20) NOT NULL,
	`order_number` varchar(20) NOT NULL COMMENT '订单号',
	PRIMARY KEY (`id`),
	KEY `FK_orders_1` (`user_id`),
	CONSTRAINT `FK_orders_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE NO	ACTION ON UPDATE NO ACTION
)ENGINE=INNODB CHARSET=utf8;

INSERT INTO `tb_order` VALUES('1','1','20140921001');
INSERT INTO `tb_order` VALUES('2','2','20140921002');
INSERT INTO `tb_order` VALUES('3','1','20140921003');

CREATE TABLE `tb_orderdetail`(
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`order_id` int(32) DEFAULT NULL COMMENT '订单号',
	`item_id` int(32) DEFAULT NULL COMMENT '商品id',
	`total_price` double(20,0) DEFAULT NULL COMMENT '商品总价',
	`status` int(11) DEFAULT NULL COMMENT '状态',
	PRIMARY KEY (`id`),
	KEY `FK_orderdetail_1` (`order_id`),
	KEY `FK_orderdetail_2` (`item_id`),
	CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`order_id`) REFERENCES `tb_order` (`id`),
	CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`item_id`) REFERENCES `tb_order` (`id`)
)ENGINE=INNODB CHARSET=utf8;

INSERT INTO `tb_orderdetail` VALUES('1','1','1','5288','1');
INSERT INTO `tb_orderdetail` VALUES('2','1','2','6288','1');
INSERT INTO `tb_orderdetail` VALUES('3','2','2','6288','1');
INSERT INTO `tb_orderdetail` VALUES('4','3','1','5288','1');

1.2pojo:

//Item
package com.itheima.pojo;

public class Item {
    private Integer id;
    private String itemName;
    private Float itemPrice;
    private String itemDetail;

    public Item() {
    }

    public Item(Integer id, String itemName, Float itemPrice, String itemDetail) {
        this.id = id;
        this.itemName = itemName;
        this.itemPrice = itemPrice;
        this.itemDetail = itemDetail;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getItemName() {
        return itemName;
    }

    public void setItemName(String itemName) {
        this.itemName = itemName;
    }

    public Float getItemPrice() {
        return itemPrice;
    }

    public void setItemPrice(Float itemPrice) {
        this.itemPrice = itemPrice;
    }

    public String getItemDetail() {
        return itemDetail;
    }

    public void setItemDetail(String itemDetail) {
        this.itemDetail = itemDetail;
    }

    @Override
    public String toString() {
        return "Item{" +
                "id=" + id +
                ", itemName='" + itemName + '\'' +
                ", itemPrice=" + itemPrice +
                ", itemDetail='" + itemDetail + '\'' +
                '}';
    }
}

//order
package com.itheima.pojo;

public class Order {
    private Integer id;
    private String orderNumber;

    public Order() {
    }

    public Order(Integer id, String orderNumber) {
        this.id = id;
        this.orderNumber = orderNumber;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getOrderNumber() {
        return orderNumber;
    }

    public void setOrderNumber(String orderNumber) {
        this.orderNumber = orderNumber;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", orderNumber='" + orderNumber + '\'' +
                '}';
    }
}

//orderDetail
package com.itheima.pojo;

public class Orderdetail {
    private Integer id;
    private Double totalPrice;
    private Integer status;

    public Orderdetail() {
    }

    public Orderdetail(Integer id, Double totalPrice, Integer status) {
        this.id = id;
        this.totalPrice = totalPrice;
        this.status = status;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Double getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(Double totalPrice) {
        this.totalPrice = totalPrice;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Orderdetail{" +
                "id=" + id +
                ", totalPrice=" + totalPrice +
                ", status=" + status +
                '}';
    }
}

//user
package com.itheima.pojo;

import java.io.Serializable;

public class User {
    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private Integer sex;

    public User() {
    }

    public User(Long id, String userName, String password, String name, Integer age, Integer sex) {
        this.id = id;
        this.userName = userName;
        this.password = password;
        this.name = name;
        this.age = age;
        this.sex = sex;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex=" + sex +
                '}';
    }
}

1.3mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--根元素-->
<configuration>
    <!--加载外部配置文件-->
    <properties resource="jdbc.properties"></properties>
    <!--开启驼峰映射：把数据表中的带有下划线的字段，变为java的驼峰命名方式。user_name->username userName-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--设置别名-->
    <typeAliases>
        <!--扫描指定的名,所包下的所有的类，都起了别名，别名格式：全部小写或驼峰命名-->
        <package name="com.itheima.pojo"/>
        <!--        <typeAlias type="com.itheima.pojo.User" alias="User"></typeAlias>-->
    </typeAliases>
    <!--配置数据源（数据库连接）-->
    <environments default="dev">
        <!--开发环境-->
        <environment id="dev">
            <!--事务管理器：按照jdbc默信的事务-->
            <transactionManager type="JDBC"/>
            <!--数据源(数据库连接池) mybatis有自带数据库连接池-->
            <dataSource type="POOLED">
                <!--连接数据库需要的4个参数：驱动、URL、登录名、密码-->
                <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>
        <!--测试环境-->
        <environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/hsp_db02?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!--映射映射相关文件-->
    <mappers>

        <!--方案1：指定关联的映射文件，通过映射文件在关联mapper接口-->
        <!--        <mapper resource="com/itheima/dao/UserMapper.xml"/>-->
        <!--方案1：指定关联的mapper接口（通过mapper接口关联映射文件）
            要求：
                1.映射文件名和mapper接口必须相同
                2.映射文件和mapper接口存储在相同的路径下
        -->
        <!--指定要扫描的所有mapper接口的包路径-->
        <package name="com.itheima.dao"/>
    </mappers>
</configuration>

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=root
jdbc.password=123456

1.4MybatisUtils工具类

package com.itheima.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.InputStream;

//工具类：私有化构造器+静态方 法
public class MybatisUtil {
    //私有化构造方法
    private MybatisUtil(){}

    //SqlSessionFactory工厂类对象
    private static SqlSessionFactory sqlSessionFactory=null;
    //随着类的加载，仅执行一次
    static {
        try {
            //读取配置文件
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            //创建SqlSessionFactory工厂类
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //静态方法返回SqlSession对象
    public static SqlSession openSession(){
        //使用SqlSessionFactory工厂类，创建SqlSession对象
        return sqlSessionFactory.openSession(); //默认：手动事务
    }

    public static SqlSession openSession(boolean flag){
        //使用SqlSessionFactory工厂类，创建SqlSession对象
        return sqlSessionFactory.openSession(flag);//根据参数指定事务
    }

    //静态方法关闭sqlSession
    public static void closeSqlSession(SqlSession sqlSession){
        if(sqlSession!=null){
            sqlSession.close();
        }
    }
}

1.5userMapper接口

public interface UserMapper {
    /**
     * 根据用户ID查询用户信息
     * @param id 用户ID
     * @return  返回对象
     */
    public User findUserById(Integer id);
}

1.6userMapper.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">
<!--利用namespace属性，绑定当前映射文件和mapper接口映射关联-->
<mapper namespace="com.itheima.dao.UserMapper">
    <select id="findUserById" resultType="com.itheima.pojo.User">
        select id,user_name as userName,password,name,age,sex from tb_user
        where id=#{id}
    </select>
</mapper>

1.7测试代码

package com.itheima;

import com.itheima.dao.UserMapper;
import com.itheima.pojo.User;
import com.itheima.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class UserMapperTest {
    @Test
    public void testFindUserById(){
        //使用SqlSessionFactory工厂类，创建SqlSession
        SqlSession sqlSession = MybatisUtil.openSession();

        //利用sqlSession，创建一个代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = userMapper.findUserById(1);
        System.out.println(user);
        //释放资源
        MybatisUtil.closeSqlSession(sqlSession);

    }
}

2.一对一查询

mybatis多表查询的套路：

1.基于需求编写SQ语句

2.基于SQL语句的查询结果，分析类与类之间关联（建立实体类和实体类的关联）

1对1查询结果：在Order类添加新属性:User 对象

3.在映射文年中，基于SQL查询结果（配置关联）

2.1SQL

select tb_order.id as order_id,
 tb_order.order_number,
 tb_user.id as user_id,
 tb_user.user_name,
 tb_user.password,
 tb_user.name,
 tb_user.sex,
 tb_user.age
from tb_order inner join tb_user
on tb_user.id=tb_order.user_id
where tb_order.order_number='20140921003'

2.2orderMapper.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">
<!--利用namespace属性，绑定当前映射文件和mapper接口映射关联-->
<mapper namespace="com.itheima.dao.OrderMapper">
    <!--resultMap标签：解决查询结果字段名和实体类属性不一致的问题，解决多表关联映射-->
    <resultMap id="orderMap" type="com.itheima.pojo.Order" autoMapping="true">
        <!--配置；Order查询结果和order类的映射关联-->
        <id column="order_id" property="id"></id>
        <result column="order_number" property="orderNumber"></result>
        <!--配置：1对1查询
            配置：查询结果和User类的映射

        -->
        <association property="orderUser" javaType="com.itheima.pojo.User">
            <id column="user_id" property="id"></id>
            <result column="user_name" property="userName"></result>
        </association>


    </resultMap>
    <!--根据订单编号，查询下单信息及下单人员信息-->
    <select id="findOrderByNumber" resultMap="orderMap">
        select tb_order.id as order_id,
               tb_order.order_number,
               tb_user.id as user_id,
               tb_user.user_name,
               tb_user.password,
               tb_user.name,
               tb_user.sex,
               tb_user.age
        from tb_order inner join tb_user
                                 on tb_user.id=tb_order.user_id
        where tb_order.order_number=#{orderNumber}
    </select>
</mapper>

2.3orderMapper接口

package com.itheima.dao;

import com.itheima.pojo.Order;

public interface OrderMapper {
    /**
     * 根成订单号查询订单信息
     * @param orderNumber
     * @return
     */
    public Order findOrderByNumber(String orderNumber);
}

2.4pojo

order

package com.itheima.pojo;

public class Order {
    private Integer id; //订单ID
    private String orderNumber;//订单编号

    public User getOrderUser() {
        return orderUser;
    }

    public void setOrderUser(User orderUser) {
        this.orderUser = orderUser;
    }

    //下单人
    private User orderUser; //用户对象

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getOrderNumber() {
        return orderNumber;
    }

    public void setOrderNumber(String orderNumber) {
        this.orderNumber = orderNumber;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", orderNumber='" + orderNumber + '\'' +
                '}';
    }


}

user

package com.itheima.pojo;

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

public class User {
    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private Integer sex;


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex=" + sex +
                '}';
    }
}

2.5测试代码

package com.itheima;

import com.itheima.dao.OrderMapper;
import com.itheima.dao.UserMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.User;
import com.itheima.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class OrderMapperTest {
    @Test
    public void testFindOrderByNumber(){
        //使用SqlSessionFactory工厂类，创建SqlSession
        SqlSession sqlSession = MybatisUtil.openSession();

        //利用sqlSession，创建一个代理对象
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

        Order order = orderMapper.findOrderByNumber("20140921003");
        System.out.println("订单信息"+order);
        User user=order.getOrderUser();
        System.out.println("订单人信息:"+user);
        //释放资源
        MybatisUtil.closeSqlSession(sqlSession);

    }
}

2.6运行结果

2024-01-19 14:30:50.512 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 14:30:51.219 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 1267149311.
2024-01-19 14:30:51.220 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4b8729ff]
2024-01-19 14:30:51.226 [DEBUG] com.itheima.dao.OrderMapper.findOrderByNumber [main] : > Preparing: select tb_order.id as order_id, tb_order.order_number, tb_user.id as user_id, tb_user.user_name, tb_user.password, tb_user.name, tb_user.sex, tb_user.age from tb_order inner join tb_user on tb_user.id=tb_order.user_id where tb_order.order_number=?
2024-01-19 14:30:51.312 [DEBUG] com.itheima.dao.OrderMapper.findOrderByNumber [main] : > Parameters: 20140921003(String)
2024-01-19 14:30:51.350 [TRACE] com.itheima.dao.OrderMapper.findOrderByNumber [main] : < Columns: order_id, order_number, user_id, user_name, password, name, sex, age
2024-01-19 14:30:51.351 [TRACE] com.itheima.dao.OrderMapper.findOrderByNumber [main] : < Row: 3, 20140921003, 1, zhangsan, 123456, 张三, 1, 30
2024-01-19 14:30:51.355 [DEBUG] com.itheima.dao.OrderMapper.findOrderByNumber [main] : <== Total: 1
订单信息Order{id=3, orderNumber=‘20140921003’}
订单人信息:User{id=1, userName=‘zhangsan’, password=‘null’, name=‘null’, age=null, sex=null}
2024-01-19 14:30:51.380 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4b8729ff]
2024-01-19 14:30:51.381 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4b8729ff]
2024-01-19 14:30:51.382 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 1267149311 to pool.

Process finished with exit code 0

3.一对多查询

1对多查询结果：在User类中添加新属性：List 集合

3.1SQL

select 
 tb_user.id as user_id,
 tb_user.user_name,
 tb_user.password,
 tb_user.name,
 tb_user.sex,
 tb_user.age,
 tb_order.id as order_id,
 tb_order.order_number
from tb_order inner join tb_user
on tb_user.id=tb_order.user_id
where tb_user.id=1

3.2userMapperXml

<?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">
<!--利用namespace属性，绑定当前映射文件和mapper接口映射关联-->
<mapper namespace="com.itheima.dao.UserMapper">
    <!--配置多表查询映射关系-->
    <resultMap id="userMap" type="com.itheima.pojo.User" autoMapping="true">
        <!--配置：查询结果和User类的映射-->
        <id column="user_id" property="id"></id>
        <result column="user_name" property="userName"></result>

        <!--配置一对多-->
        <collection property="orders"
                    javaType="java.util.List"
                    ofType="com.itheima.pojo.Order">
            <id column="order_id" property="id"></id>
            <result column="order_number" property="orderNumber"></result>
        </collection>
        <!--配置：查询结果和Order类的映射-->

    </resultMap>
    <!--根据用户ID查询用户信息和下单信息-->
    <select id="findUserById" resultMap="userMap">
        select
            tb_user.id as user_id,
            tb_user.user_name,
            tb_user.password,
            tb_user.name,
            tb_user.sex,
            tb_user.age,
            tb_order.id as order_id,
            tb_order.order_number
        from tb_order inner join tb_user
                                 on tb_user.id=tb_order.user_id
        where tb_user.id=#{id}
    </select>
</mapper>

3.3userMapper接口

package com.itheima.dao;

import com.itheima.pojo.User;

public interface UserMapper {
    /**
     * 根据用户ID查询用户信息及下单信息
     * @param id 用户ID
     * @return  返回对象
     */
    public User findUserById(Integer id);
}

3.4pojo

user

package com.itheima.pojo;

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

public class User {
    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private Integer sex;

    public List<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }

    //一个用户多个订单(1:多)
    private List<Order> orders;


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex=" + sex +
                '}';
    }
}

order

package com.itheima.pojo;

public class Order {
    private Integer id; //订单ID
    private String orderNumber;//订单编号

    public User getOrderUser() {
        return orderUser;
    }

    public void setOrderUser(User orderUser) {
        this.orderUser = orderUser;
    }

    //下单人
    private User orderUser; //用户对象

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getOrderNumber() {
        return orderNumber;
    }

    public void setOrderNumber(String orderNumber) {
        this.orderNumber = orderNumber;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", orderNumber='" + orderNumber + '\'' +
                '}';
    }


}

3.5测试代码

package com.itheima;

import com.itheima.dao.UserMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.User;
import com.itheima.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserMapperTest {
    @Test
    public void testFindUserById(){
        //使用SqlSessionFactory工厂类，创建SqlSession
        SqlSession sqlSession = MybatisUtil.openSession();

        //利用sqlSession，创建一个代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = userMapper.findUserById(1);
        MybatisUtil.closeSqlSession(sqlSession);

        System.out.println("用户信息："+user);
        List<Order> orderList=user.getOrders();
        for(Order order:orderList){
            System.out.println(order);
        }
        //释放资源


    }
}

3.6运行结果

2024-01-19 16:21:25.700 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4b8729ff]
2024-01-19 16:21:25.703 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4b8729ff]
2024-01-19 16:21:25.703 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 1267149311 to pool.
用户信息：User{id=1, userName=‘zhangsan’, password=‘123456’, name=‘张三’, age=30, sex=1}
Order{id=1, orderNumber=‘20140921001’}
Order{id=3, orderNumber=‘20140921003’}

Process finished with exit code 0

二.mybatis注解开发

1.CRUD详解

@Insert:保存

? Value: sql语句(和xml的配置方式一模一样)

@Update:更新

? Value: sql语句

@Delete:语句

? Value: sql语句

@ Select 语句

? Value: sql语句

@Options:可选配置（获取主键）

? userGenerateKeys:开关值为true表示可以获取主键相当于select last_insert_id()

? keyProperty:对象属性

? keyColumn:列名

步骤：
?	1.将mybatis全局配置文件mybatis-config.xml中的mapper的路径改为包扫描或者class路径
?	2.编写接口和注解
?	3.测试

2.insert

2.1userMapper接口

package com.itheima.dao;

import com.itheima.pojo.User;
import org.apache.ibatis.annotations.Insert;

public interface UserMapper {
    /**
     * 添加用户
     * @param user 用户对象
     * @return 有影响的行数
     */
    @Insert("INSERT INTO tb_user(user_name,password,name,age,sex) " +
            "VALUES(#{userName},#{password},#{name},#{age},#{sex})")
    public int addUser(User user);

}

2.2测试代码

package com.itheima;

import com.itheima.dao.UserMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.User;
import com.itheima.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserMapperTest {
    @Test
    public void testAddUser(){
        //使用SqlSessionFactory工厂类，创建SqlSession
        SqlSession sqlSession = MybatisUtil.openSession();

        //利用sqlSession，创建一个代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = new User();
        user.setUserName("测试注解");
        user.setPassword("123456");
        user.setName("注解");
        user.setSex(1);
        user.setAge(14);
        int count=userMapper.addUser(user);
        if(count>0){
            System.out.println("添加成功");
        }
        //释放资源
        MybatisUtil.closeSqlSession(sqlSession);

    }
}

2.3测试结果

2024-01-19 16:53:34.672 [DEBUG]  org.apache.ibatis.io.ResolverUtil [main] : Checking to see if class com.itheima.dao.UserMapper matches criteria [is assignable to Object]
2024-01-19 16:53:34.848 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 16:53:35.435 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 771418758.
2024-01-19 16:53:35.437 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2dfaea86]
2024-01-19 16:53:35.444 [DEBUG]  com.itheima.dao.UserMapper.addUser [main] : ==>  Preparing: INSERT INTO tb_user(user_name,password,name,age,sex) VALUES(?,?,?,?,?) 
2024-01-19 16:53:35.584 [DEBUG]  com.itheima.dao.UserMapper.addUser [main] : ==> Parameters: 测试注解(String), 123456(String), 注解(String), 14(Integer), 1(Integer)
2024-01-19 16:53:35.604 [DEBUG]  com.itheima.dao.UserMapper.addUser [main] : <==    Updates: 1
添加成功
2024-01-19 16:53:35.605 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Rolling back JDBC Connection [com.mysql.jdbc.JDBC4Connection@2dfaea86]
2024-01-19 16:53:35.610 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2dfaea86]
2024-01-19 16:53:35.612 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@2dfaea86]
2024-01-19 16:53:35.612 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 771418758 to pool.

Process finished with exit code 0

3.select

3.1 userMapper接口

package com.itheima.dao;

import com.itheima.pojo.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface UserMapper {

    @Update("update tb_user set password=#{password} where user_name=#{username}")
   public int updateUser(@Param("username") String name,
                          @Param("password") String password);

    @Select("SELECT user_name,password,name,age,sex from tb_user")
    public List<User> findAll();
}

3.2测试代码

@Test
public void testUpdateUser(){
    //使用SqlSessionFactory工厂类，创建SqlSession
    SqlSession sqlSession = MybatisUtil.openSession(true);

    //利用sqlSession，创建一个代理对象
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    int Count=userMapper.updateUser("蔡徐坤","12345678");
    if(Count>0){
        System.out.println("更新成功");
    }
    //释放资源
    MybatisUtil.closeSqlSession(sqlSession);

}

3.3运行结果

2024-01-19 17:11:18.153 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 17:11:18.832 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 944140566.
2024-01-19 17:11:18.840 [DEBUG]  com.itheima.dao.UserMapper.updateUser [main] : ==>  Preparing: update tb_user set password=? where user_name=? 
2024-01-19 17:11:18.934 [DEBUG]  com.itheima.dao.UserMapper.updateUser [main] : ==> Parameters: 12345678(String), 蔡徐坤(String)
2024-01-19 17:11:18.943 [DEBUG]  com.itheima.dao.UserMapper.updateUser [main] : <==    Updates: 1
更新成功
2024-01-19 17:11:18.944 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@38467116]
2024-01-19 17:11:18.945 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 944140566 to pool.

4.主键回填

主键回填技术：在向数据表中插入一行数据时，会返回所插入行的主键列表数据，主键列表数据会回填到实体类的属性中（和主键关联的属性）

4.1userMapper接口

package com.itheima.dao;

import com.itheima.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper {
    /**
     * 添加用户
     * @param user 用户对象
     * @return 有影响的行数
     */

    @Insert("INSERT INTO tb_user(user_name,password,name,age,sex) " +
            "VALUES(#{userName},#{password},#{name},#{age},#{sex})")
    //useGeneratedKeys 开启主键回填，keyColumn="表中的主键字段名" keyProperty="实体类中的属性名"
    @Options(useGeneratedKeys = true,keyColumn ="id",keyProperty = "id")
    public int addUserGetPk(User user);
}

4.2测试代码

    @Test
    public void testAaddUser(){
        //使用SqlSessionFactory工厂类，创建SqlSession
        SqlSession sqlSession = MybatisUtil.openSession(true);

        //利用sqlSession，创建一个代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = new User();
        user.setUserName("测试注解");
        user.setPassword("123456");
        user.setName("注解");
        user.setSex(1);
        user.setAge(14);
        int count=userMapper.addUserGetPk(user);
        if(count>0){
            System.out.println("添加成功");
        }
        //释放资源
        MybatisUtil.closeSqlSession(sqlSession);

    }

4.3运行结果

2024-01-19 17:21:56.939 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 17:21:57.846 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 859654796.
2024-01-19 17:21:57.852 [DEBUG]  com.itheima.dao.UserMapper.addUserGetPk [main] : ==>  Preparing: INSERT INTO tb_user(user_name,password,name,age,sex) VALUES(?,?,?,?,?) 
2024-01-19 17:21:57.953 [DEBUG]  com.itheima.dao.UserMapper.addUserGetPk [main] : ==> Parameters: 测试注解(String), 123456(String), 注解(String), 14(Integer), 1(Integer)
2024-01-19 17:21:57.961 [DEBUG]  com.itheima.dao.UserMapper.addUserGetPk [main] : <==    Updates: 1
添加成功
2024-01-19 17:21:57.965 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@333d4a8c]
2024-01-19 17:21:57.965 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 859654796 to pool.

5.Results结果集映射

当查询结果中的列名和实体类的属性名不一致时：

1.在SQL语句中，添加别名（别名和实体类中的属性类一致）

2.在核心配置文件中，配置驼峰映射

3.使用resultMap标签，设置查询后结果和实体类的映射关系

5.1接口

@Select("SELECT id,user_name,password,name,age,sex from tb_user")
@Results(id = "userMap",value={
    @Result(column = "id",property = "id",id = true),
    @Result(column = "user_name",property = "userName")
})
public List<User> findAll();

5.2测试代码

@Test
public void testFindAllUser(){
    //使用SqlSessionFactory工厂类，创建SqlSession
    SqlSession sqlSession = MybatisUtil.openSession();

    //利用sqlSession，创建一个代理对象
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList=userMapper.findAll();
    //释放资源
    MybatisUtil.closeSqlSession(sqlSession);
    for (User user:userList){
        System.out.println(user);
    }
}

5.3运行结果

2024-01-19 17:40:13.684 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 17:40:14.339 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 1326393666.
2024-01-19 17:40:14.340 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4f0f2942]
2024-01-19 17:40:14.346 [DEBUG]  com.itheima.dao.UserMapper.findAll [main] : ==>  Preparing: SELECT id,user_name,password,name,age,sex from tb_user 
2024-01-19 17:40:14.566 [DEBUG]  com.itheima.dao.UserMapper.findAll [main] : ==> Parameters: 
2024-01-19 17:40:14.858 [TRACE]  com.itheima.dao.UserMapper.findAll [main] : <==    Columns: id, user_name, password, name, age, sex
2024-01-19 17:40:14.859 [TRACE]  com.itheima.dao.UserMapper.findAll [main] : <==        Row: 1, zhangsan, 123456, 张三, 30, 1
2024-01-19 17:40:14.865 [TRACE]  com.itheima.dao.UserMapper.findAll [main] : <==        Row: 2, lishi, 123456, 李四, 21, 0
2024-01-19 17:40:14.870 [TRACE]  com.itheima.dao.UserMapper.findAll [main] : <==        Row: 3, wangwu, 123456, 王五, 22, 1
2024-01-19 17:40:14.872 [TRACE]  com.itheima.dao.UserMapper.findAll [main] : <==        Row: 4, zhangwei, 123456, 张伟, 20, 1
2024-01-19 17:40:14.873 [TRACE]  com.itheima.dao.UserMapper.findAll [main] : <==        Row: 5, lina, 123456, 李娜, 28, 0
2024-01-19 17:40:14.875 [TRACE]  com.itheima.dao.UserMapper.findAll [main] : <==        Row: 6, 蔡徐坤, 12345678, 小菜, 18, 1
2024-01-19 17:40:14.910 [TRACE]  com.itheima.dao.UserMapper.findAll [main] : <==        Row: 8, 测试注解, 123456, 注解, 14, 1
2024-01-19 17:40:14.938 [DEBUG]  com.itheima.dao.UserMapper.findAll [main] : <==      Total: 7
2024-01-19 17:40:14.940 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4f0f2942]
2024-01-19 17:40:14.942 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4f0f2942]
2024-01-19 17:40:14.944 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 1326393666 to pool.
User{id=1, userName='zhangsan', password='123456', name='张三', age=30, sex=1}
User{id=2, userName='lishi', password='123456', name='李四', age=21, sex=0}
User{id=3, userName='wangwu', password='123456', name='王五', age=22, sex=1}
User{id=4, userName='zhangwei', password='123456', name='张伟', age=20, sex=1}
User{id=5, userName='lina', password='123456', name='李娜', age=28, sex=0}
User{id=6, userName='蔡徐坤', password='12345678', name='小菜', age=18, sex=1}
User{id=8, userName='测试注解', password='123456', name='注解', age=14, sex=1}

6.模糊查询

6.1userMapper接口

package com.itheima.dao;

import com.itheima.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper {
    /**
     * 根据给定名字，模糊查询用户数据
     * @param name 名字
     * @return 存储用户对象的集合
     */
    @Select("select id,user_name AS userName,password,name,age,sex" +
            " from tb_user" +
            " where sex=1 AND name like concat('%',#{name},'%')")
    public List<User> findUserByLike(String name);
}

6.2测试代码

@Test
public void testFindUserByLike(){
    //使用SqlSessionFactory工厂类，创建SqlSession
    SqlSession sqlSession = MybatisUtil.openSession();

    //利用sqlSession，创建一个代理对象
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList=userMapper.findUserByLike("小");
    //释放资源
    MybatisUtil.closeSqlSession(sqlSession);
    for (User user:userList){
        System.out.println(user);
    }
}

6.3运行结果

2024-01-19 18:02:51.596 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 18:02:52.614 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 308433917.
2024-01-19 18:02:52.615 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@126253fd]
2024-01-19 18:02:52.622 [DEBUG]  com.itheima.dao.UserMapper.findUserByLike [main] : ==>  Preparing: select id,user_name AS userName,password,name,age,sex from tb_user where sex=1 AND name like concat('%',?,'%') 
2024-01-19 18:02:52.724 [DEBUG]  com.itheima.dao.UserMapper.findUserByLike [main] : ==> Parameters: 小(String)
2024-01-19 18:02:52.773 [TRACE]  com.itheima.dao.UserMapper.findUserByLike [main] : <==    Columns: id, userName, password, name, age, sex
2024-01-19 18:02:52.774 [TRACE]  com.itheima.dao.UserMapper.findUserByLike [main] : <==        Row: 6, 蔡徐坤, 12345678, 小菜, 18, 1
2024-01-19 18:02:52.780 [DEBUG]  com.itheima.dao.UserMapper.findUserByLike [main] : <==      Total: 1
2024-01-19 18:02:52.784 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@126253fd]
2024-01-19 18:02:52.787 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@126253fd]
2024-01-19 18:02:52.787 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 308433917 to pool.
User{id=6, userName='蔡徐坤', password='12345678', name='小菜', age=18, sex=1}

Process finished with exit code 0

7.动态sql

方式1：

使用@SelectProvider(type=SqlProvider.class,method=“findUserByName”)method是构建SQL的方法

构建SQL的方法的参数要和接口的参数一致，并且多个参数使用@Param命名参数

方式2：

@SelectProvider+SQL类

1.定义接口

2.定义提供的类

7.1.1接口（方式1）

SqlProvider类

package com.itheima.dao;

import org.apache.ibatis.annotations.Param;

//拼接SQL语句
public class SqlProvider {
    //返回拼接后的SQL语句
    public String findUserByName(@Param("name") String name) {
        String sql="select id,user_name AS userName,password,name,age,sex from tb_user where sex=1 ";


        if(name!=null && !"".equals(name)){
            sql+=" AND name like concat('%',#{name},'%')";
        }
        return sql;
    }
}

    /**
     * 根据给定名字，模糊查询用户数据
     * @param name 名字
     * @return 存储用户对象的集合
     */
//    @Select("select id,user_name AS userName,password,name,age,sex" +
//            " from tb_user" +
//            " where sex=1 AND name like concat('%',#{name},'%')")
    @SelectProvider(type = SqlProvider.class,method = "findUserByName")
    public List<User> findUserByLike(@Param("name") String name);

7.1.2测试代码（方式1）

    @Test
    public void testFindUserByLike(){
        //使用SqlSessionFactory工厂类，创建SqlSession
        SqlSession sqlSession = MybatisUtil.openSession();

        //利用sqlSession，创建一个代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList=userMapper.findUserByLike("小");
        //释放资源
        MybatisUtil.closeSqlSession(sqlSession);
        for (User user:userList){
            System.out.println(user);
        }
    }

7.1.3运行结果（方式1）

2024-01-19 18:23:31.416 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 18:23:32.363 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 1304589447.
2024-01-19 18:23:32.364 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4dc27487]
2024-01-19 18:23:32.372 [DEBUG]  com.itheima.dao.UserMapper.findUserByLike [main] : ==>  Preparing: select id,user_name AS userName,password,name,age,sex from tb_user where sex=1 AND name like concat('%',?,'%') 
2024-01-19 18:23:32.505 [DEBUG]  com.itheima.dao.UserMapper.findUserByLike [main] : ==> Parameters: 小(String)
2024-01-19 18:23:32.665 [TRACE]  com.itheima.dao.UserMapper.findUserByLike [main] : <==    Columns: id, userName, password, name, age, sex
2024-01-19 18:23:32.666 [TRACE]  com.itheima.dao.UserMapper.findUserByLike [main] : <==        Row: 6, 蔡徐坤, 12345678, 小菜, 18, 1
2024-01-19 18:23:32.669 [DEBUG]  com.itheima.dao.UserMapper.findUserByLike [main] : <==      Total: 1
2024-01-19 18:23:32.672 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4dc27487]
2024-01-19 18:23:32.673 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4dc27487]
2024-01-19 18:23:32.673 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 1304589447 to pool.
User{id=6, userName='蔡徐坤', password='12345678', name='小菜', age=18, sex=1}

7.2.1接口（方式2）

sqlProvider

public class SqlProvider {

    public String findUserByName2(@Param("name") String name){
        //Mybatis提供了一个SQL类
        SQL sql=new SQL();
        //利用SQL类中提供的API方法,拼接了SQL语句
        sql=sql.SELECT(" id,user_name AS userName,password,name,age,sex").FROM("tb_user").WHERE("sex=1");
        if(name!=null){
            sql=sql.AND().WHERE("name like concat('%',#{name},'%')");
        }
        return sql.toString();
    }
}

userMapper.java
public interface UserMapper {
    @SelectProvider(type = SqlProvider.class,method = "findUserByName2")
    public List<User> findUserByLike(@Param("name") String name);
}

7.2.2测试代码

    @Test
    public void testFindUserByLike(){
        //使用SqlSessionFactory工厂类，创建SqlSession
        SqlSession sqlSession = MybatisUtil.openSession();

        //利用sqlSession，创建一个代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList=userMapper.findUserByLike("小");
        //释放资源
        MybatisUtil.closeSqlSession(sqlSession);
        for (User user:userList){
            System.out.println(user);
        }
    }

7.2.3运行结果

2024-01-22 11:08:52.107 [DEBUG]  com.itheima.dao.UserMapper.findUserByLike [main] : ==>  Preparing: SELECT id,user_name AS userName,password,name,age,sex FROM tb_user WHERE (sex=1) AND (name like concat('%',?,'%')) 
2024-01-22 11:08:52.195 [DEBUG]  com.itheima.dao.UserMapper.findUserByLike [main] : ==> Parameters: 小(String)
2024-01-22 11:08:52.229 [TRACE]  com.itheima.dao.UserMapper.findUserByLike [main] : <==    Columns: id, userName, password, name, age, sex
2024-01-22 11:08:52.229 [TRACE]  com.itheima.dao.UserMapper.findUserByLike [main] : <==        Row: 6, 蔡徐坤, 12345678, 小菜, 18, 1
2024-01-22 11:08:52.233 [DEBUG]  com.itheima.dao.UserMapper.findUserByLike [main] : <==      Total: 1
2024-01-22 11:08:52.235 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2254127a]
2024-01-22 11:08:52.235 [DEBUG]  org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@2254127a]
2024-01-22 11:08:52.236 [DEBUG]  org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 575935098 to pool.
User{id=6, userName='蔡徐坤', password='12345678', name='小菜', age=18, sex=1}

Process finished with exit code 0

文章来源:https://blog.csdn.net/lisus2007/article/details/135743795
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！

mybatis高级查询和注解开发

mybatis高级查询和注解开发

一.mybatis高级查询

1.环境准境

1.1sql:

1.2pojo:

1.3mybatis-config.xml

1.4MybatisUtils工具类

1.5userMapper接口

1.6userMapper.xml

1.7测试代码

2.一对一查询

2.1SQL

2.2orderMapper.xml

2.3orderMapper接口

2.4pojo

2.5测试代码

2.6运行结果

3.一对多查询

3.1SQL

3.2userMapperXml

3.3userMapper接口

3.4pojo

3.5测试代 码

3.6运行结果

二.mybatis注解开发

1.CRUD详解

2.insert

2.1userMapper接口

2.2测试代码

3.select

3.1 userMapper接口

3.2测试代码

3.3运行结果

4.主键回填

4.1userMapper接口

4.2测试代码

4.3运行结果

5.Results结果集映射

5.1接口

5.2测试代码

5.3运行结果

6.模糊查询

6.1userMapper接口

6.2测试代码

6.3运行结果

7.动态sql

7.1.1接口（方式1）

7.1.2测试代码（方式1）

7.1.3运行结果（方式1）

7.2.1接口（方式2）

7.2.2测试代码

7.2.3运行结果

3.5测试代码