当前位置: 首页 > article >正文

MyBatis·下

一、properties 属性

加载配置文件,实现连接数据库的信息,和mybatis主配置文件分离,实现连接数据库的数据独立维护。 注意:properties标记加载优先级高.

1.外部的属性文件

地址:src/main/resources/db.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis
jdbc.username=root
jdbc.password=888888

2.MyBatis 配置文件

文件地址:src/main/resources/SqlMapConfig.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"></properties>👈
    <!--数据库连接相关配置-->
    <environments default="development">
        <environment id="development">
            <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="UserMapper.xml"></mapper>
    </mappers>
</configuration>

二、Mybatis主配置文件

1.MyBatis 配置文件

文件地址:src/main/resources/SqlMapConfig.xml

typeAliases :类型命名

配置别名,在映射文件中多次出现重复的类地址,可以通过别名设置来简化写法,实现复用。

<configuration>
    <properties resource="db.properties"></properties>
    <typeAliases>
        <!--为单个类设置别名-->
        <typeAlias type="org.example.pojo.User" alias="user"></typeAlias>-->
        <!--批量设置别名-->
        <package name="org.example.pojo"/>
    </typeAliases>
    <!--数据库连接相关配置-->
    <!--...-->
<!--关联映射配置文件-->
<!--...-->
</configuration>

2.映射文件

文件地址:src/main/resources/UserMapper.xml

替换原org,example.jojo.User。

三、MyBatis映射配置文件 

1.配置依赖

打开maven主配置文件,地址:pom.xml

<build>
    <resources>
        <!--编译mapper的xml文件-->
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.xml</include>
                <include>**/*.properties</include>
            </includes>
        </resource>
    </resources>
</build>

2.MyBatis配置文件 

文件地址:src/main/resources/SqlMapConfig.xml

<!--关联映射配置文件-->
<mappers>
    <!--此时UserMapper.xml文件在resource下-->
    <!--UserMapper文件在org.example.mapper下-->
    <mapper resource="UserMapper.xml"></mapper>
    <!--配置映射接口,要求映射文件和映射接口在同一目录下-->
    <!--此时UserMapper和UserMapper.xml同在org.example.mapper下-->
    <mapper class="org.example.mapper.UserMapper"></mapper>
    <!--批量配置接口-->
    <package name="org.example.mapper"/>
</mappers>

四、条件查询

PO(持久对象) 在数据访问层(DAO 层)中与数据库表进行映射和持久化。

public class User {
    private int id;
    private String username;
    private String sex;
    private Date birthday;
    private String address;
    👆get、set、toString方法
}
//以继承父类的方法添加个人属性
public class UserCustom extends User{...}

VO(值对象) 在表示层(Controller 层)中用于展示数据。

//前端数据交互
package org.example.pojo;
public class UserQueryVO {
    private UserCustom userCustom;
    public UserCustom getUserCustom() {
        return userCustom;
    }
    public void setUserCustom(UserCustom userCustom) {
        this.userCustom = userCustom;
    }
}

1.添加条件

文件地址:src/main/java/org/example/mapper/UserMapper.xml

<!--用户信息综合查询-->
<select id="findUserList" parameterType="org.example.pojo.UserQueryVO" resultType="org.example.pojo.UserCustom">
    select * from user where user.sex=#{userCustom.sex} and user.username like '%${userCustom.username}%'
</select>
👇
<select id="findUserList" parameterType="org.example.pojo.UserQueryVO" resultType="org.example.pojo.UserCustom">
    select * from user
     <where>
         <if test="userCustom!=null">
             <if test="userCustom.sex!=null and userCustom.sex!=''">
                 and user.sex=#{userCustom.sex}
             </if>
         <if test="userCustom.username!=null and userCustom.username!=''">
             and user.username like '%${userCustom.username}%'
         </if>
         </if>
     </where>
</select>
👇
<mapper namespace="org.example.mapper.UserMapper">
    <sql id ='query_user_where'>
        <if test="userCustom!=null">
            <if test="userCustom.sex!=null and userCustom.sex!=''">
                and user.sex=#{userCustom.sex}
            </if>
            <if test="userCustom.username!=null and userCustom.username!=''">
                and user.username like '%${userCustom.username}%'
            </if>
        </if>
    </sql>
    <select id="findUserList" parameterType="org.example.pojo.UserQueryVO" resultType="org.example.pojo.UserCustom">
        select
        <include refid="selectColumn"></include>
        from user
        <where>
            <include refid="query_user_where"></include>
        </where>
    </select>
</mapper>

2.替换条件

<select id="selectUserById" parameterType="int" resultType="user">
    select id,username,sex,birthday,address from user where id=#{id}
</select>
👇
<sql id="selectColumn">
    id,username,sex,birthday,address
</sql>
<select id="selectUserById" parameterType="int" resultType="user">
    select
    <include refid="selectColumn"></include>
    from user where id=#{id}
</select>

 3.属性名和列明不一致处理

<!--    resultMap,当对象的属性名和列明不一致时,使用resultMap输出映射进行配置-->
<resultMap id="userResultMap" type="org.example.pojo.User">
<!--        id用于匹配主键,result用于匹配非主键,column表示查询的列名,property表示匹配的对象的属性名-->
    <id column="id_" property="id"></id>
    <result column="username_" property="username"></result>
    <result column="sex_" property="sex"></result>
</resultMap>
<select id="findUserByIdResultMap" parameterType="int" resultMap="userResultMap">
    select id id_,username username_,sex sex_ from user where id=#{id}
</select>

4.接口与测试 

接口:src/main/java/org/example/mapper/UserMapper.java

public User findUserByIdResultMap(int id);

测试: src/test/java/UserMapperTest.java

@Test
public void findUserByIdResultMapTest() throws IOException {
    String resource = "SqlMapConfig.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    User user = sqlSession.getMapper(UserMapper.class).findUserByIdResultMap(1);
    System.out.println(user);
}

 五、多表关联

1.一对一关联查询

需求:查询订单信息,关联查询创建订单的用户信息

主表:订单表

关联表:用户表

public class Orders {
    private int id;
    private int user_id;
    private String number;
    private Date create_time;
    private String note;
    private User user;
}

public class OrdersCustom extends Orders{
    private String username;
    private String sex;
    private String address;
}

1.resultType

输出映射,可把结果集封装到某个对象/类上

映射文件src/main/java/org/example/mapper/UserMapper.xml

<!--查询订单信息,关联查询创建订单的用户信息-->
<select id="findOrdersUser" resultType="org.example.pojo.OrdersCustom">
    select orders.*,user.username,user.sex,user.birthday,user.address
    from orders,user
    where orders.user_id = user.id
</select>

接口src/main/java/org/example/mapper/UserMapper.java 

List<OrdersCustom> findOrdersUser();

测试src/test/java/UserMapperTest.java

@Test
public void findOrdersUserTest() throws IOException {
    String resource = "SqlMapConfig.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    List<OrdersCustom> list = sqlSession.getMapper(UserMapper.class).findOrdersUser();
    System.out.println(list);
}

2.resultMap

映射文件src/main/java/org/example/mapper/UserMapper.xml

<!--    查询订单信息,关联查询创建订单的用户信息,使用resultMap输出映射实现-->
<resultMap id="OrdersUserResultMap" type="org.example.pojo.Orders">
    <!--先匹配订单信息-->
    <id column="id" property="id"></id>
    <result column="user_id" property="user_id"></result>
    <result column="number" property="number"></result>
    <result column="createtime" property="createtime"></result>
    <result column="note" property="note"></result>
    <!--后匹配用户信息-->
    <association property="user" javaType="org.example.pojo.User">
        <id column="user_id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
    </association>
</resultMap>
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
    select orders.*,user.username,user.sex,user.birthday,user.address
    from orders,user
    where orders.user_id = user.id
</select>

接口src/main/java/org/example/mapper/UserMapper.java 

List<Orders> findOrdersUserResultMap();

测试src/test/java/UserMapperTest.java

@Test
public void findOrdersUserResultMapTest() throws IOException {
    String resource = "SqlMapConfig.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    List<Orders> orders = sqlSession.getMapper(UserMapper.class).findOrdersUserResultMap();
    System.out.println(orders);
}

2.一对多关联查询

需求:查询订单及订单明细信息

主表:订单表

关联表:订单明细表

resultMap

1.SQL语句

-- 查询订单及订单明细信息
select orders.*,user.username,user.sex,user.address,
orderdetail.id orderdetail_id,orderdetail.items_id,orderdetail.items_num
from orders,user,orderdetail
where orders.user_id=user.id
and orderdetail.orders_id=orders.id

2.映射类

public class Orderdetail {
    private int id;
    private int orders_id;
    private int items_id;
    private int items_num;
}
public class Orders {
    private int id;
    private int user_id;
    private String number;
    private Date createtime;
    private String note;
    private User user;
    private List<Orderdetail> orderdetails;👈//添加get、set、toString方法
}

3.映射文件

<!--查询订单及订单明细信息-->
<resultMap id="OrdersUserAndOrderdetailResultMap" type="org.example.pojo.Orders" extends="OrdersUserResultMap">
    <!--一对多匹配-->
    <collection property="orderdetails" ofType="org.example.pojo.Orderdetail">
        <id column="orderdetail_id" property="id"></id>
        <result column="items_id" property="items_id"></result>
        <result column="items_num" property="items_num"></result>
    </collection>
</resultMap>
<select id="findOrdersUserAndOrderdetailResultMap" resultMap="OrdersUserAndOrderdetailResultMap">
    select orders.*,user.username,user.sex,user.address,
           orderdetail.id orderdetail_id,orderdetail.items_id,orderdetail.items_num
    from orders,user,orderdetail
    where orders.user_id=user.id
      and orderdetail.orders_id=orders.id
</select>

4.接口

List<Orders> findOrdersUserAndOrderdetailResultMap();

5. 测试

@Test
public void findOrdersUserAndOrderdetailResultMapTest() throws IOException {
    String resource = "SqlMapConfig.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    List<Orders> orders = sqlSession.getMapper(UserMapper.class).findOrdersUserAndOrderdetailResultMap();
    System.out.println(orders);
}

http://www.kler.cn/a/585136.html

相关文章:

  • AGI大模型(3):大模型生成内容
  • Vi/Vim命令详解:高效文本编辑的利器
  • C语言【数据结构】:理解什么是数据结构和算法(启航)
  • 51c大模型~合集7
  • 架构师论文《论云原生架构及其应用》
  • G-Star 公益行起航,挥动开源技术点亮公益!
  • C#中通过Response.Headers设置自定义参数
  • 万字讲清大模型的发展,按时间排序(1950年到2025年)
  • Python - 爬虫;爬虫-网页抓取数据-工具curl
  • 银河麒麟V10ServerSP3中Redis7源码编译与安装详细教程
  • SpringDataRedis存储Redis的数据序列化
  • 【C++标准库类型】深入理解string类型:从基础到实践
  • 【VSCODE 插件 可视化】:SVG 编辑插件 SVG Editor
  • 如何通过折扣话费接口来吸引用户?
  • CTF--Web安全--SQL注入之报错注入
  • 事件总线EventBus原理剖析
  • TCP/IP 协议精讲-精华总结版本
  • 内网穿透的应用-全流程解析如何通过VNC实现Windows与MacOS远程桌面的无缝连接
  • Windows11使用CMD命令行从零开始创建一个Flask项目并使用虚拟环境
  • 工作效率提升的原因