Mybatis笔记

最新推荐文章于 2024-08-30 18:40:43 发布

凌波丽海苔

最新推荐文章于 2024-08-30 18:40:43 发布

阅读量38

点赞数

文章标签： mybatis 笔记 java

本文链接：https://blog.csdn.net/qq_57789915/article/details/131223826

版权

Mybaits

依赖文件

 <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
            <scope>test</scope>
        </dependency>
        <!-- log4j日志 -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
 <dependencies>

屏幕截图 2023-03-20 170147

接口方式编写

SessionFactroy类

package com.mybatis.factroy;

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 Session {
    public static SqlSession getFactory() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        //获取sqlFactory对象
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //获取sqlSessionFactoryBuilder
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
        //获取SqlSession
        //设置自动提交
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        return sqlSession;
        
/*      使用：
        SqlSession sqlSession = Session.getFactory();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
*/
    }
}

接口文件

package com.mybatis.mapper;

import com.mybatis.pojo.User;
import java.util.List;

public interface UserMapper {
    /**
     * 保持两个一致
     * 映射文件的namespace和mapper接口的全类名保持一致
     * sql语句的id和mapper接口中的方法名一致
     *
     */
    int insertUser();
    List<User> findAll();
}

实体类

public class User {

    private Integer id;
    private String name;
    private Integer score;
    private  Integer age;
    private String sex;
}

设置类型别名

pom.xml中

<!--设置类型别名  不区分大小写-->
<typeAliases>
    <typeAlias type="com.mybatis.pojo.User" alias="User"></typeAlias>
<!--以包为单位，设置默认类名-->
    <package name="com.mybatis.pojo"/>
</typeAliases>

以包为单位映射文件

在resources目录下

创建com/mybatis/mapper

mybatis_config中

    <mappers>
        <!-- 加载前面编写的SQL语句的文件 -->
<!--        <mapper resource="mappers/UserMapper.xml"/>-->
<!--        以包为单位设置映射文件-->
        <package name="com.mybatis.mapper"/>
    </mappers>

获取参数

${} 和 #{}

${}本质为字符串拼接

<!-- void seleteByName(String name);;-->
    <select id="seleteByName" resultType="User">
        select * from student where name = #{username}
    </select>

根据多个参数查找

<!--    User checkLogin(Integer uid,String pwd);-->
    <select id="seleteByNameASex" resultType="User">
        select * from student where name = #{arg0} and sex = #{arg1}
    </select>

自定义参数map

<!--    User seleteByMap(Map<String,Object> map);-->
    <select id="seleteByMap" resultType="User">
        select * from student where name = #{username} and sex = #{sex}
    </select>

Map<String,Object> map = new HashMap<>();
map.put("username","张三");
map.put("sex","F");
User user = mapper.seleteByMap(map);

实体类对象参数【浏览器】

<!--    Int insertByObj(User user);-->
    <insert id="insertByObj">
        insert into student values (null,#{name},#{score},#{age},#{sex})
    </insert>

public void insertByObj() throws IOException {
    SqlSession sqlSession = Session.getFactory();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int result =  mapper.insertByObj(new User(null,"bob",250,10,"M"));
    System.out.println(result);
}

命名参数

@param

<!--    User seleteByParam(@Param("name")String name, @Param("sex") String sex);-->
    <select id="seleteByParam" resultType="User">
        select * from student where name = #{name} and sex = #{sex}
    </select>

若查询结果有多条，不能用实体类对象接受，只能用list接受

查询结果转化为map集合

<!--    Map<String,Object> seleteByIdToMap(@Param("id") String id);-->
    <select id="seleteByIdToMap" resultType="map">
        select * from student where id = #{id}
    </select>

result

{score=336, sex=F, name=范秀英, id=3, age=91}

通过@MapKey()声明键值

模糊查询

需要用${}

<!--    List<User> seleteByLikeName(@Param("name") String name);-->
    <select id="seleteByLikeName" resultType="User">
        //select * from student where name like '%${name}%'
     常用:select * from student where name like "%"#{name}"%"
    </select>

批量删除

使用${}

<!--    void deleteMore(@Param("ids") String ids);-->
    <delete id="deleteMore">
        delete from student where id in (${ids})
    </delete>

动态查询表

使用${}

<!--    List<User> seleteByTableName(@Param("tablename") String tablename);-->
    <select id="seleteByTableName" resultType="User">
        select * from ${tablename}
    </select>

添加功能获取自增的主键

<!--    void insertUser(User user);
        useGeneratedKeys="true"设置允许自增
        keyProperty将自增的主键映射到id
-->
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
        insert into student values (null,#{name},#{score},#{age},#{sex})
    </insert>

自定义resultMap映射

字段名和实体类中的属性不一致

不管是多对一还是一对多，需要在多的一方设置一的主键

解决字段名和属性名不一致

将下划线自动映射为驼峰

<!--设置全局，将下划线映射为驼峰 -->
<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

通过resultMap标签设置映射

<resultMap id="empResultMap" type="Emp">
    <!--id设置唯一主键-->
    <id property="eid" column="eid"></id>
    <result property="empName" column="emp_name"></result>
    <result property="age" column="age"></result>
    <result property="email" column="email"></result>
    <result property="sex" column="sex"></result>
    <result property="did" column="did"></result>
</resultMap>
<select id="getAllEmp" resultType="Emp">
    select * from t_emp
</select>

处理多对一`association`

在员工里查公司

级联属性赋值

<!-- Emp getEmpAndDep(@Param("eid") Integer eid);-->
    <resultMap id="EmpAndDeptByEid" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <result property="dept.did" column="did"></result>
        <result property="dept.deptName" column="dept_name"></result>
    </resultMap>
    <select id="getEmpAndDep" resultMap="EmpAndDeptByEid">
        select * from t_emp left join t_dept on t_emp.did = t_dept.did where t_emp.eid = #{eid}
    </select>

通过association赋值

<!-- Emp getEmpAndDep(@Param("eid") Integer eid);-->
    <resultMap id="EmpAndDeptByEid" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <association property="dept" javaType="Dept">
            <id property="did" column="did"></id>
            <result property="deptName" column="dept_name"></result>
        </association>
    </resultMap>
    <select id="getEmpAndDep" resultMap="EmpAndDeptByEid">
        select * from t_emp left join t_dept on t_emp.did = t_dept.did where t_emp.eid = #{eid}
    </select>

分布查询[重要]

通过写两张mapper来实现

可以实现延迟加载，默认不开启

<setting name="lazyLoadingEnabled" value="true"/>

开启全局加载后，fetchType = "lazy|eager"可以单独设置

表一中：

<select id="getEmpByStepOne" resultMap="EmpAndDeptByStep">
    select * from t_emp where eid = #{eid}
</select>
<resultMap id="EmpAndDeptByStep" type="Emp">
    <id property="eid" column="eid"></id>
    <result property="empName" column="emp_name"></result>
    <result property="age" column="age"></result>
    <result property="sex" column="sex"></result>
    <result property="email" column="email"></result>
    <!--
            select:分布查询的唯一标识，接口方法的全类名
            column：分布查询的条件
    -->
    <association property="dept"
    select="com.mybatis.mapper.DeptMapper.getEmpAndDeptByStep"
    column="did"></association>
</resultMap>

表二

<!--    Dept getEmpAndDeptByStep();-->
    <select id="getEmpAndDeptByStep" resultType="Dept">
        select * from t_dept where did = #{did}
    </select>

一对多`collection`

在公司里查员工

<!--    Dept getEmp(@Param("did") Integer did);-->
    <select id="getEmp" resultMap="getEmpResultMap" >
        select * from t_dept left join t_emp on t_dept.did = t_emp.did where t_dept.did = #{did}
    </select>
    <resultMap id="getEmpResultMap" type="Dept">
        <id property="did" column="did"></id>
        <result property="deptName" column="dept_name"></result>
<!--        cpllection 在实体类中的属性名
            ofType = 该属性对应的集合中的存储类型
-->
        <collection property="emps" ofType="Emp">
            <id property="eid" column="eid"></id>
            <result property="empName" column="emp_name"></result>
            <result property="age" column="age"></result>
            <result property="sex" column="sex"></result>
            <result property="email" column="email"></result>
        </collection>

分布查询：

StepOne:

<!--    Dept getEmpsByStepOne(@Param("did") Integer did);
        分布查询，先查询部门
		部门中
-->
    <select id="getEmpsByStepOne" resultMap="EmpsByStep">
        select * from t_dept where did = #{did}
    </select>
    <resultMap id="EmpsByStep" type="Dept">
        <id property="did" column="did"></id>
        <result property="deptName" column="dept_name"></result>
       <!--
                property:实体类中另一个表的属性
                select:分布查询的唯一标识，接口方法的全类名
                column：分布查询的条件
        -->
        <collection property="emps"
                   select="com.mybatis.mapper.EmpMapper.getEmpsByStepTwo"
                   column="did">
        </collection>
    </resultMap>

StepTwo:

<!--    Emp getEmpsByStepTwo(@Param("did") Integer did);
		员工中
-->
        <select id="getEmpsByStepTwo" resultType="Emp">
            select * from t_emp where did = #{did}
        </select>

动态sql

if 可以根据标签中test属性对应表示决定标签内的内容是否拼接在sql中

select * from t_emp where 1=1
<if test="empName != null and empName !=''">
       and emp_name = #{empName}
</if>

where

where中有内容时，自动生成where关键字，并去掉内容前多余的and or

不能将内容后的and or去掉

    <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp
       <where>
           <if test="empName != null and empName !=''">
               and emp_name = #{empName}
           </if>
           <if test="age != null and age !=''">
               and age = #{age}
           </if>
           <if test="sex != null and sex !=''">
               and sex = #{sex}
           </if>
           <if test="email != null and email !=''">
               and email = #{email}
           </if>
       </where>
    </select>

<where>
    <if test="empName != null and empName !=''">
        and emp_name = #{empName}
    </if>
    <if test="age != null and age !=''">
        and age = #{age}
    </if>
    <if test="sex != null and sex !=''">
        and sex = #{sex}
    </if>
    <if test="email != null and email !=''">
        and email = #{email}
    </if>
</where>

trim

choose when otherwise

相当于 if else

<!--    List<Student> getStudentByChoose(@Param("student") Student student);-->
    <select id="getStudentByChoose" resultType="Student">
        select * from t_student
            <where>
                <choose>
                    <when test="id != null and id !=''">
                        id = #{id}
                    </when>
                    <when test="name != null and name !=''">
                        name = #{name}
                    </when>
                    <when test="major != null and major !=''">
                        and major = #{major}
                    </when>
                    <otherwise>
                        and id is not null
                    </otherwise>
                </choose>
            </where>
    </select>

foreach

批量删除，添加

<!--    Integer deleteMoreByArry(@Param("eid") Integer[] eid);-->
    <delete id="deleteMoreByArry" >
        delete from t_emp where eid in
            <foreach collection="eids" item="eid" separator="," open="(" close=")">
                #{eid}
            </foreach>
    </delete>

<!--    int insertBylist(List<Emp> emps);-->
    <insert id="insertBylist" >
        insert into t_emp values
            <foreach collection="emps" item="emp" separator=",">
                (null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
            </foreach>
    </insert>

sql语句

凌波丽海苔

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis笔记

Mybaits依赖文件 <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.10</version> </dependency&g
复制链接

扫一扫