Mybatis框架

Dumpling_skin

已于 2022-06-05 20:07:42 修改

阅读量249

点赞数

分类专栏： Java之mybatis框架文章标签： java sql 开发语言

于 2022-06-01 21:32:12 首次发布

本文链接：https://blog.csdn.net/Dumpling_skin/article/details/125067690

版权

Java之mybatis框架专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1、mybatis框架：提高开发效率

MyBatis 是一款优秀的持久层Dao框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的POJOs(Java实体类)映射成数据库中的记录.

2、mybatis框架的使用

目录结构

(1)引入mybatis依赖和数据库连接依赖

mybatis依赖
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.6</version>
</dependency>
jdbc依赖：连接数据库
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>
lombok依赖
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.24</version>
</dependency>
log4j依赖
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>
单元测试junit依赖，scope是生效范围，不加是项目下都生效
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope>
</dependency>

(2)写 mybatis的配置文件mybatis.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--为实体类定别名-->         
    <typeAliases>
        <!--为某个实体类起别名-->
        <typeAlias type="com.gjx.entity.User" alias="User"></typeAlias>
        <!--为所有实体类起别名，别名为实体类的名称-->
        <package name="com.gjx.entity"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/vue01?serverTimezone=Asia/Shanghai" />
                <property name="username" value="数据库用户名" />
                <property name="password" value="用户名密码" />
            </dataSource>
        </environment>
    </environments>
    <!-- 把映射文件放到配置文件里 -->
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
        <mapper resource="mapper/StuMapper.xml"/>
    </mappers>
</configuration>

log4j的配置文件（方便我们查看执行的sql语句）

log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

(3)写数据库表和实体类之间的映射文件

resultMap解决列名和数据库名不对应的问题，resultType和resultMap两者存在其一即可

<?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为你的dao接口的路径-->
<mapper namespace="com.gjx.dao.StuDao">
    <resultMap id="StuMapper" type="com.gjx.entity.Stu">
        <id column="stu_id" property="id"></id>
        <result column="stu_name" property="name"></result>
        <result column="stu_sex" property="sex"></result>
        <result column="stu_age" property="age"></result>
        <result column="teacher_id" property="teacherId"></result>
    </resultMap>
    <select id="getStu" parameterType="int" resultMap="StuMapper">
        select stu_id,stu_name,stu_sex,stu_age,teacher_id from t_stu where stu_id=#{id}
    </select>
    <insert id="insertStu" parameterType="Stu" useGeneratedKeys="true" keyProperty="id">
        insert into t_stu(stu_name,stu_sex,stu_age,teacher_id) values(#{name},#{sex},#{age},#{teacherId})
    </insert>
    <delete id="deleteStu" parameterType="Integer">
        delete from t_stu where id=#{id}
    </delete>
    <update id="updateStu" parameterType="Stu">
        update t_stu set stu_name=#{name},stu_sex=#{sex},stu_age=#{age},teacher_id=#{teacherId} where id=#{id}
    </update>
    <select id="selectAllStus" resultMap="StuMapper">
        select * from t_stu
    </select>
</mapper>

（4）lombok依赖的使用

lombok帮我们省去实体类的全部参数构造方法，和无参构造方法，如果是不定参数的构造方法，可以自己手动在实体类中添加构造方法

在项目的pom.xml文件中引入Lombok依赖

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.24</version>
</dependency>

如果依赖引入不成功的话可能是你的idea没有安装Lombok插件

安装之后在实体类中就可使用Lombok的注解

package com.gjx.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Stu {
    private Integer id;
    private String name;
    private Integer sex;
    private Integer age;
    private Integer teacherId;

    public Stu(String name, Integer sex, Integer age, Integer teacherId) {
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.teacherId = teacherId;
    }
}

(5)测试类方法

public class TestStu {

    @Test
    public void testGetStu() throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        StuDao stuDao = session.getMapper(StuDao.class);
        Stu stu = stuDao.getStu(1);
        System.out.println(stu);
        session.close();
    }

    @Test
    public void testInsert() throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        StuDao stuDao = session.getMapper(StuDao.class);
        Stu stu = new Stu("张三",1,22,2);
        int row = stuDao.insertStu(stu);
        System.out.println(row);
        session.commit();
        session.close();
    }

}

3、动态sql

where、if(条件判断)、choose...when...otherwise(多条件判断，如果if条件都不满足，执行otherwise标签)

满足条件时执行if标签中的sql语句，不满足条件则查询所有

如果不使用where标签，查询所有的时候需要加上一个恒成立的条件

使用where标签省去的第一行sql语句的where关键字和恒成立条件，而且会自动省略第一个if标签中的关键字，比如and 和 or

    <select id="findCondition" resultMap="StuMapper">
        select * from t_stu
        <where>
            <if test="name!=null and name!=''">
                and stu_name=#{name}
            </if>
            <if test="age!=null">
                and stu_age=#{age}
            </if>
        </where>
    </select>
    <select id="findConditions" resultMap="StuMapper">
        select * from t_stu
        <where>
            <choose>
                <when test="name!=null and name!=''">
                    and stu_name=#{name}
                </when>
                <when test="age!=null">
                    and stu_age=#{age}
                </when>
                <otherwise>
                    and stu_sex=#{sex}
                </otherwise>
            </choose>
        </where>
    </select>

set(修改时使用)、foreach(用于循环)、sql片段(在映射文件中，查询时不建议使用*进行查询，使用sql标签将需要查询的列提取出来)

set标签配合if使用，修改数据时，当传入的数据为空时不修改数据

<update id="updateStu">
    update t_stu
    <set>
        <if test="name!=null and name!=''">
            stu_name = #{name},
        </if>
        <if test="sex!=null">
            stu_sex = #{sex},
        </if>
        <if test="age!=null">
            stu_age = #{age},
        </if>
        <if test="teacherId!=null">
            teacherId = #{teacherId},
        </if>
    </set>
        where stu_id = #{id}
</update>

foreach循环，常用于批量删除，collection：如果是数组使用array，如果是集合使用list，open：以什么为开头，close：以什么为结尾，separator：以什么为分隔符

<select id="findByIds" resultMap="StuMapper">
    select <include refid="baseColumn2" /> from t_stu where stu_id in
    <foreach collection="array" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

sql片段：在SQL语句中使用include的注入，refid的属性值为sql片段的id值

    <sql id="baseColumn2">
        stu_id,stu_name,stu_sex,stu_age,teacher_id
    </sql>

    <select id="findAll" resultMap="StuMapper">
        select <include refid="baseColumn2" /> from t_stu 
    </select>

4、mybatis映射文件处理特殊字符

（1）使用转义字符   <

（2）<![CDATA[sql]]>(推荐)

映射文件处理特殊字符的例子如下

    <select id="findByMinMax" resultMap="StuMapper">
        <![CDATA[select * from t_stu where stu_age>#{min} and stu_age<#{max}]]>
    </select>

5、mybatis完成模糊查询

有两种方法:

第一种：使用concat函数拼接字符串 concat('%',#{param},'%')

第二种：使用'%${param}%'，实际上是字符串拼接，不过不能防止sql注入的问题，而#{}可以防止sql注入，相当于预编译，即PreparedStatement；${}相当于Statement

<select id="searchByLike" resultMap="StuMapper">
    select <include refid="baseColumn2"/> from t_stu where stu_name like concat('%',#{param},'%')
</select>

<select id="searchByLike2" resultMap="StuMapper">
    select <include refid="baseColumn2"/> from t_stu where stu_name like '%${param}%'
</select>

6、联表查询（重难点）

关系模式有一对多，多对一

以学生表和教师表为例，根据学生表的id 查询学生信息，并携带教师名称

sql语句如下：select * from t_stu s join t_teacher t on s.teacher_id=t.teacher_id where stu_id=1

修改Stu的实体类，添加teacher对象属性，将联表查询的教师信息封装到teacher对象中

package com.gjx.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Stu {
    private Integer id;
    private String name;
    private Integer sex;
    private Integer age;
    private Integer teacherId;

    private Teacher teacher;
}

新建Teacher 实体类

package com.gjx.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private Integer teacherId;
    private String teacherName;
    private Integer teacherSex;
    private Integer teacherAge;
    private Double teacherSalary;
}

写StuMapper.xml

<!--sql片段，需要查询的列-->
<sql id="baseColumn">
    stu_id,stu_name,stu_sex,stu_age,teacher_id,teacher_name,teacher_sex,teacher_age,teacher_salary
</sql>

<!--返回结果集，association表示1的一方，property为属性名，javaType为该属性的数据类型-->
<resultMap id="baseMap" type="com.gjx.entity.Stu">
    <id column="stu_id" property="id"></id>
    <result column="stu_name" property="name"></result>
    <result column="stu_sex" property="sex"></result>
    <result column="stu_age" property="age"></result>
    <result column="teacher_id" property="teacherId"></result>
    <association property="teacher" javaType="com.gjx.entity.Teacher" autoMapping="true">
        <id column="teacher_id" property="teacherId"/>
        <result column="teacher_name" property="teacherName"/>
        <result column="teacher_sex" property="teacherSex"/>
        <result column="teacher_age" property="teacherAge"/>
        <result column="teacher_salary" property="teacherSalary"/>
    </association>
</resultMap>

<!--联表查询的查询语句-->
<select id="findCross" resultMap="baseMap">
    select <include refid="baseColumn"/> from t_stu s join t_teacher t on s.stu_id=t.teacher_id where s.stu_id=#{id}
</select>

还可以使用map工具类实现，将返回的结果封装到map中，sql语句的返回值类型为java.util.Map

也可以实现联表查询

Dumpling_skin

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis框架

mybatis框架的基本使用，mybatis框架动态sql的使用，联表查询，模糊查询，#{}和${}的区别，#{}预编译PreparedStatement，${}模糊查询Statement，不能防止sql注入问题
复制链接

扫一扫

专栏目录