Mybatis学习笔记完整版

最新推荐文章于 2024-03-25 23:17:51 发布

浪去爪哇岛喝咖啡

最新推荐文章于 2024-03-25 23:17:51 发布

阅读量251

点赞数

分类专栏：尚学堂2000集笔记日更文章标签： mysql

本文链接：https://blog.csdn.net/qq_32695257/article/details/118340269

版权

尚学堂2000集笔记日更专栏收录该内容

20 篇文章 5 订阅

订阅专栏

423 花卉管理系统查询信息

1.创建项目结构和导包

在这里插入图片描述

2.创建实体类Flower

package com.bjsxt.entity;

import java.io.Serializable;

public class Flower implements Serializable {

    private Integer id;
    private String name;
    private Integer price;
    private String production;

    @Override
    public String toString() {
        return "Flower{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", price=" + price +
                ", production='" + production + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getPrice() {
        return price;
    }

    public void setPrice(Integer price) {
        this.price = price;
    }

    public String getProduction() {
        return production;
    }

    public void setProduction(String production) {
        this.production = production;
    }

    public Flower(Integer id, String name, Integer price, String production) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.production = production;
    }

    public Flower() {
    }
}

3.dao层和dao.impl层书写

package com.bjsxt.dao;

import com.bjsxt.entity.Flower;

import java.util.ArrayList;
import java.util.List;

public interface FlowerDao {
    //查询所有花卉信息-->如果是查询操作，返回值一般不是对象就是集合

    List<Flower> selectAll();

}

package com.bjsxt.dao.impl;

import com.bjsxt.dao.FlowerDao;
import com.bjsxt.entity.Flower;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FlowerDaoImpl implements FlowerDao {

    @Override
    public List<Flower> selectAll() {
        //使用JDBC连接数据库
        Connection conn  = null;
        PreparedStatement pstmt =null;
        ResultSet rs =null;
        List<Flower> list = new ArrayList<>();
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取数据库连接
             conn  = DriverManager.getConnection("jdbc:mysql://localhost:3306" +
                             "/mybatis?characterEncoding=utf-8&useSSL=false" +
                             "&serverTimezone=UTC"
                    ,"root","123456");
            //3.创建命令发送器（手枪）
             pstmt = conn.prepareStatement("Select * from " +
                    "flower");
            //4.执行sql语句（发射子弹）
            rs = pstmt.executeQuery();

            //5.返回结果
            while(rs.next()){
                int id = rs.getInt("id");
                String  name = rs.getString("name");
                int price = rs.getInt("price");
                String production =rs.getString("production");

                Flower flower = new Flower(id,name,price,production);
                list.add(flower);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.关闭资源
            try {
                if(rs!=null)
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(pstmt!=null)
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn!=null)
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return list;
    }
}

4.测试

package com.bjsxt.test;

import com.bjsxt.dao.FlowerDao;
import com.bjsxt.dao.impl.FlowerDaoImpl;
import com.bjsxt.entity.Flower;

import java.util.List;

public class TestA {
    public static void main(String[] args) {
        FlowerDao flowerDao =new FlowerDaoImpl();
        List<Flower> list = flowerDao.selectAll();
        for(Flower flower : list){
            System.out.println(flower);
        }
    }
}

425 Mybatis 的框架环境搭建

1.创建项目结构和导包

在这里插入图片描述

2.书写mybatis.xml文件和FlowerMapper.xml

在这里插入图片描述

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>
    <environments default="mysql">
        <!--Mysql数据库连接-->
        <environment id="mysql">
            <!--Mybatis中的事务管理，目前和JDBC的事务保持一致-->
            <transactionManager type="JDBC"></transactionManager>
            <!--底层使用连接池连接数据库-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
        <!--oral数据库连接-->
<!--        <environment id="orcl">-->
<!--            <transactionManager type=""></transactionManager>-->
<!--            <dataSource type=""></dataSource>-->
<!--        </environment>-->

    </environments>
  <!--进行mapper文件扫描-->
    <mappers>
        <!--resource:书写的是xml所在的目录-->
        <mapper resource="com/bjsxt/mapper/FlowerMapper.xml"></mapper>
    </mappers>
</configuration>

FlowerMapper.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="a.b">
    <!--

    List<Flower> selectAll()
    id:方法名称
    resultType:返回值类型
        查询返回的时候一般不是对象就是集合
        如果返回值是一个对象，就写对象所在包的全路径
        如果返回值是一个集合，这个时候书写集合的泛型
    -->
    <select id="selectAll" resultType="com.bjsxt.entity.Flower">
        select * from flower
    </select>
</mapper>

3.编写测试类

package com.bjsxt.test;

import com.bjsxt.entity.Flower;
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;
import java.util.List;

public class TestA {

    public static void main(String[] args) throws IOException {
        //1.解析myBatis.xml
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");

        //2.获取session工厂
        SqlSessionFactory factory =
                new SqlSessionFactoryBuilder().build(inputStream);

        //3.获取session对象
        SqlSession sqlSession = factory.openSession();

        //4.调用方法
        List<Flower> list = sqlSession.selectList("a.b.selectAll");

        System.out.println(list);
        //5.关闭sqlsession
        sqlSession.close();


    }

}



/*log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
[Flower{id=1, name

='玫瑰花', price=15, production='中国'}, Flower{id=2, name='夕颜', price=10, production='中国'}, Flower{id=3, name='西兰花', price=16, production='菜市场'}]
*/

CREATE TABLE `flower` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(55) DEFAULT NULL,
  `price` int(5) DEFAULT NULL,
  `production` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

INSERT INTO `flower`(`id`, `name`, `price`, `production`) VALUES (1, '玫瑰花', 15, '中国');
INSERT INTO `flower`(`id`, `name`, `price`, `production`) VALUES (2, '夕颜', 10, '中国');
INSERT INTO `flower`(`id`, `name`, `price`, `production`) VALUES (3, '西兰花', 16, '菜市场');
INSERT INTO `flower`(`id`, `name`, `price`, `production`) VALUES (6, '韭菜花', 5, '中国');
INSERT INTO `flower`(`id`, `name`, `price`, `production`) VALUES (7, '韭菜花', 5, '中国');
INSERT INTO `flower`(`id`, `name`, `price`, `production`) VALUES (9, 'bjsxt', 19, 'bj');

427 Mybatis 中框架优化

1.问题1 resultType的返回值类型太长了！

FlowerMapper.xml

<select id="selectAll" resultType="com.bjsxt.entity.Flower">
     SELECT * from flower
</select>

解决方案1:mybatis.xml 给实体类配置别名

<configuration>
    <!--给实体类配置别名-->
    <typeAliases>
        <typeAlias type="com.bjsxt.entity.Flower" alias="Flower"></typeAlias>
    </typeAliases>

如果，实体类过多，需要给每个类起别名，麻烦！

解决方案2：包下的所有类的别名都是当前类名不区分首字母大小写！

typeAliases>
        <!--包下的所有类的别名都是当前类名-->
        <package name="com.bjsxt.entity"></package>
    </typeAliases>

2.问题2 数据库的一些值，一般放入属性文件中！

<!--底层使用连接池连接数据库-->
<dataSource type="POOLED">
    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
    <property name="url"
              value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;serverTimezone=UTC"/>
    <property name="username" value="root"/>
    <property name="password" value="123456"/>
</dataSource>

解决在src下面写一个 jdbc.properties 注意amp;省略！
注意jdbc.properties在最前面不要加空格

m_driver = com.mysql.cj.jdbc.Driver
m_url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&serverTimezone=UTC
m_root=root
m_pwd=123456

x修改 mybatis.xml 注意属性文件在别名之上，所有configuration下的标签是有顺序的

<!--读取属性文件-->
<properties resource="jdbc.properties">


</properties>
<!--给实体类配置别名-->
<typeAliases>
    <!--        <typeAlias type="com.bjsxt.entity.Flower" alias="Flower"></typeAlias>-->
    <!--包下的所有类的别名都是当前类名-->
    <package name="com.bjsxt.entity"></package>
</typeAliases>



<environments default="mysql">
    <!--Mysql数据库连接-->
    <environment id="mysql">
        <!--Mybatis中的事务管理，目前和JDBC的事务保持一致-->
        <transactionManager type="JDBC"></transactionManager>
        <!--底层使用连接池连接数据库-->
        <dataSource type="POOLED">
            <property name="driver" value="${m_driver}"/>
            <property name="url"
                      value="${m_url}"/>
            <property name="username" value="${m_root}"/>
            <property name="password" value="${m_pwd}"/>
        </dataSource>
    </environment>

3.配置setting开启log4j支持

作用：

记录错误信息到文件中有日志级别方便查看！

log4j有5种级别：fatal error warn info debug

如果设置debug级别，5种错误都会输出！

1.指定mybatis中的日志类型

<!--指定mybatis中的日志类型-->
<settings>
    <setting name="logImpl" value="LOG4J"/>
</settings>

2.在src创建log4j.properties文件

log4j.rootCategory=error, CONSOLE,LOGFILE

#log4j.logger.com.bjsxt.mapper=debug
log4j.logger.com.bjsxt.mapper.FlowerMapper=debug

log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=- %m%n

# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=d:/axis.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n

注意：修改FlowerMapper.xml中的 namespace

<mapper namespace="com.bjsxt.mapper.FlowerMapper">

428 Mybatis 三种查询方式

三种查询方式：

//查询方式一：适合于结果集返回的是多行数据的时候！
List<Flower> list = sqlSession.selectList("com.bjsxt.mapper" +
        ".FlowerMapper1.selectAll");

//查询方式二：适合返回时一条结果集数据！
Flower flower = sqlSession.selectOne("com.bjsxt.mapper.FlowerMapper1" +
        ".selectOne");
//查询方式三
//作用：希望可以通过数据库中的某一列快速的找到这一列对应的结果集
Map<Object, Object> map = sqlSession.selectMap("com.bjsxt.mapper.FlowerMapper1.selectMap", "id");
System.out.println(map);
        
Object flower1 = map.get(3);//查询id=3,不是索引
System.out.println(flower1);

429 Mybatis参数传递操作

1.问题 id=1写死了，能不能动态的传递参数

<select id="selectOne" resultType="flower">
    select * from flower where id =1
</select>

传递一个参数写法：

<!--Flower selectOne(int id)-->
<select id="selectOne" resultType="flower" parameterType="int">
    select * from flower where id=#{param1}
</select>

Flower flower = sqlSession.selectOne("com.bjsxt.mapper.FlowerMapper2" +
                ".selectOne"
        , 1);
System.out.println(flower);

2.上面只能传递一个参数！如何解决？

<!--Flower selectOne(Flower flower)-->
<select id="selectOne2" resultType="flower" parameterType="flower">
    select * from flower where id = #{id} and name =#{name}
</select>

Flower f = new Flower();
f.setId(1);
f.setName("玫瑰花");
Flower flower2 = sqlSession.selectOne("com.bjsxt.mapper" +
                ".FlowerMapper2" +
                ".selectOne2"
        , f);
System.out.println(flower2);

3.上面方法数据库字段名正好对应了一个javabean,如果字段名对应两个javabean该如何处理？

<!--Flower selectOne(Map<> map)-->
<select id="selectOne3" resultType="flower" parameterType="map">
    select * from flower where id =#{a} and name =#{b}
</select>

//参数传递3 Map集合
Map<String,Object> map = new HashMap<>();
map.put("a",1);
map.put("b","玫瑰花");
Flower flower3 = sqlSession.selectOne("com.bjsxt.mapper.FlowerMapper2" +
        ".selectOne3",map);


System.out.println(flower3);

/*
#:底层是使用占位？
$:底层使用的是字符串拼接
*/

4.$ 和 # 区别？

<!--Flower selectOne(Map<> map)-->
<select id="selectOne3" resultType="flower" parameterType="map">
    select * from flower where id =${a} and name =${b}
</select>

Map<String,Object> map = new HashMap<>();
map.put("a",1);
map.put("b","'玫瑰花'");//需要加单引号
Flower flower3 = sqlSession.selectOne("com.bjsxt.mapper.FlowerMapper2" +
        ".selectOne3",map);


System.out.println(flower3);

430 Mybatis 增删改操作

添加操作

FlowerMapper3.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.bjsxt.mapper.FlowerMapper3">

    <!--
    int insert(Flower f);
    -->
<insert id="insert" parameterType="flower">
    insert into flower values(DEFAULT ,#{name},#{price},#{production})
</insert>


</mapper>

TestC

添加，删除，修改操作写好了必须提交
A sqlSession.commit(); 手动提交
B SqlSession sqlSession = factory.openSession(true); 自动提交

SqlSession sqlSession = factory.openSession(true);
//执行方法
Flower f = new Flower();
f.setName("韭菜花");
f.setPrice(5);
f.setProduction("中国");
int insert = sqlSession.insert("com.bjsxt.mapper.FlowerMapper3.insert"
        ,f);

mybatis.xml

<mappers>
    <mapper resource="com/bjsxt/mapper/FlowerMapper1.xml"></mapper>
    <mapper resource="com/bjsxt/mapper/FlowerMapper2.xml"></mapper>
    <mapper resource="com/bjsxt/mapper/FlowerMapper3.xml"></mapper>
</mappers>

修改操作

FlowerMapper3.xml

<update id="update">
    update flower set price =#{price},name=#{name} where id=#{id}
</update>

TestC

 SqlSession sqlSession = factory.openSession(true);
        //执行方法
        Flower f = new Flower();
        f.setId(8);
        f.setName("韭菜花1");
        f.setPrice(6);

//        int insert = sqlSession.insert("com.bjsxt.mapper.FlowerMapper3.insert"
//                ,f);

        int update = sqlSession.update("com.bjsxt.mapper.FlowerMapper3" +
                ".update",f);

删除操作

FlowerMapper3.xml

<delete id="delete">
    delete from flower where id=#{param1}
</delete>

TestC

int delete =  sqlSession.delete("com.bjsxt.mapper.FlowerMapper3.delete"
        ,8);

1.发现问题parameterType 可以省略？

是的，可以省略！

2.为什么delete中id=#{param1}，而update中where id=#{id}？

int delete(int a,int b,int c);

param1表示第一个参数！代表括号中的参数含义！

int update(Flower f)

id表示Flower对象中的id属性！

431 Mybatis_Mapper 代理方式实现crud

1.问题1sqlSession.delete(“com.bjsxt.mapper.FlowerMapper3.delete”,8);只能传递一个参数！

sqlSession.update(“com.bjsxt.mapper.FlowerMapper3” +
“.update”,f);

sqlSession.insert(“com.bjsxt.mapper.FlowerMapper3.insert”
,f);

2.书写的时候没有接口，后期的维护就比较差

3.解决：Mapper代理方式实现crud

FlowerMapper

package com.bjsxt.mapper;

import com.bjsxt.entity.Flower;

import java.util.List;

public interface FlowerMapper {

    //查询所有操作
    List<Flower> selectAll();

}

FlowerMapper.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 namespace="com.bjsxt.mapper.FlowerMapper">
    <!--id的名称和接口的名称必须保持一致-->
    <select id="selectAll" resultType="flower">
        select * from flower
    </select>
</mapper>

mybatis.xml

<mappers>
    <mapper resource="com/bjsxt/mapper/FlowerMapper1.xml"></mapper>
    <mapper resource="com/bjsxt/mapper/FlowerMapper2.xml"></mapper>
    <mapper resource="com/bjsxt/mapper/FlowerMapper3.xml"></mapper>
    <mapper resource="com/bjsxt/mapper/FlowerMapper.xml"></mapper>
</mappers>

TestD

//执行方法
FlowerMapper mapper = sqlSession.getMapper(FlowerMapper.class);
List<Flower> list = mapper.selectAll();
System.out.println(list);

在这里插入图片描述

插入

FlowerMapper

//修改操作
int insert(Flower flower);

FlowerMapper.xml

<insert id="insert">
    insert into flower values(default,#{name},#{price},#{production})
</insert

TestD

FlowerMapper mapper = sqlSession.getMapper(FlowerMapper.class);
Flower f = new Flower();
f.setName("sxt");
f.setPrice(19);
f.setProduction("bj");
mapper.insert(f);

432 Mybatis Mapper代理中多参数传递

多参数查询：

FlowerMapper

//查询操作

Flower selectOne(int id,String name);

FlowerMapper.xml

<select id="selectOne" resultType="flower">
    select * from flower where id=#{param1} and name=#{param2}
</select>

TestE

FlowerMapper mapper = sqlSession.getMapper(FlowerMapper.class);
Flower flower = mapper.selectOne(1, "玫瑰花");
System.out.println(flower);

问题1

<select id="selectOne" resultType="flower">
    select * from flower where id=#{param1} and name=#{param2}
</select>
能否写成
<select id="selectOne" resultType="flower">
    select * from flower where id=#{id} and name=#{name}
</select>
??????????????????

不能！

Flower selectOne(int id,String name);

底层相当于 map.put(“param1”,1) map.put(‘param2’,'玫瑰花)！！！得不到Flower的id，name属性！

如果就是想写：

select id=“selectOne” resultType=“flower”>
select * from flower where id=#{id} and name=#{name}
</select

可以把

Flower selectOne(int id,String name);

写成起别名

Flower selectOne(@Param(“uu”)int id,@Param(“yy”)String name);

select id=“selectOne” resultType=“flower”>
select * from flower where id=#{uu} and name=#{yy}
</select

FlowerMapper 传递多个对象

Flower selectOne4(Flower f1,Flower f2);

FlowerMapper.xml

<select id="selectOne4" resultType="flower">
    select * from flower where id=#{param1.id} and name=#{param2.name}
</select>

问题2 扫描文件太多了

<mappers>
    <mapper resource="com/bjsxt/mapper/FlowerMapper1.xml"></mapper>
    <mapper resource="com/bjsxt/mapper/FlowerMapper2.xml"></mapper>
    <mapper resource="com/bjsxt/mapper/FlowerMapper3.xml"></mapper>
    <mapper resource="com/bjsxt/mapper/FlowerMapper.xml"></mapper>
</mappers>

解决：有前提！不是通用！

<!--首先会找mapper包下的所有的接口，然后去再找接口的名字相同xml进行扫描-->
<mappers>
	<package name="com.bjsxt.mapper"></package>
</mappers>

433 Mybatis 动态SQLA

1.为什么使用mybatis动态sql?

因为下面的sql语句条件写死了！不灵活！

<select id="selectOne" resultType="flower">
    select * from flower where id=#{param1} and name=#{param2}
</select>

解决方案：9个标签！掌握5个！

if标签：

FlowerMapper.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.bjsxt.mapper.FlowerMapper">
    <select id="selectMore" resultType="flower">
        select * from flower where 1=1
        <!--OGNL表达式--> 
        <if test="param1!=null and param1!=''">
           and name = #{param1}
        </if>
        
        <if test="param2!=null and param2!=''">
           and production=#{param2}
        </if>
    </select>
</mapper>

TestA

//4.执行方法

FlowerMapper mapper = sqlSession.getMapper(FlowerMapper.class);

List<Flower> list = mapper.selectMore("玫瑰花","");

System.out.println(list);

where标签

FlowerMapper.xml

<!--where 标签的作用，会自动增加where关键字，并且会把多余的第一个and去掉-->
<select id="selectMore2" resultType="flower">
    select * from flower
    <where>
        <!--OGNL表达式-->
        <if test="param1!=null and param1!=''">
             name = #{param1}
        </if>

        <if test="param2!=null and param2!=''">
            and production=#{param2}
        </if>
    </where>
</select>

when标签相当于if else if else

<select id="selectMore4" resultType="flower">
    select * from flower
    <where>
        <choose>
            <when test="param1!=null and param1!=''">
                name = #{param1}
            </when>
            <when test="param2!=null and param2!=''">
                and production=#{param2}
            </when>
            <otherwise>1=1</otherwise>
        </choose>

    </where>
</select>

434 Myabtis 动态SQLB

set标签

FlowerMapper.xml

<!--set 会自动增加set关键字，并且去除最后一个逗号-->
<update id="update">
    update flower
    <set>

        <if test="name!=null and name!=''">
            name = #{name},
        </if>

        <if test="production!=null and production!=''">
            production=#{production}
        </if>

    </set>
    where id =
    #{id}

</update>

TestA

FlowerMapper mapper = sqlSession.getMapper(FlowerMapper.class);

//List<Flower> list = mapper.selectMore4("玫瑰花","中国");

Flower f = new Flower();
f.setId(9);
f.setName("bjsxt");

mapper.update(f);

trim标签

<!--
trim :
    prefix:添加前缀
    prefixOverrides:去除前缀
    suffix:添加后缀
    suffixOverrides:去除后缀
-->
<update id="update">
    update flower
    <trim prefix="set" suffixOverrides=",">

        <if test="name!=null and name!=''">
            name = #{name},
        </if>

        <if test="production!=null and production!=''">
            production=#{production},
        </if>
        id=#{id}
    </trim>
    where id =
    #{id}

</update>

436 动态SQL_ForEach

foreach

FlowerMapper2

public interface FlowerMapper2 {
    //map.put("list",li) map.put("array",arr)
    List<Flower> selectMore(List<Integer> li);
}

FlowerMapper2.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.bjsxt.mapper.FlowerMapper2">
    <select id="selectMore" resultType="flower">
        select * from flower where id in
        <foreach collection="list" open="(" separator="," close=")" item="it" >
            #{it}
        </foreach>
    </select>
</mapper>

TestA

public class TestA {
    public static void main(String[] args) throws IOException {
        //1.解析mybatis.xml
       InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        //2.获得sqlSession工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        //3.获得session对象
        SqlSession sqlSession = factory.openSession(true);
        //4.执行方法

        FlowerMapper2 mapper = sqlSession.getMapper(FlowerMapper2.class);


       List<Integer> list = new ArrayList<>();
       list.add(1);
       list.add(2);
       list.add(3);

       List<Flower> li = mapper.selectMore(list);
        System.out.println(li);
        //5关闭资源
        sqlSession.close();

    }
}

437 动态SQL_Bind

第一种方式：拼接不安全！

<select id="selectMore2" resultType="flower">
    select * from flower
    <where>
        <if test="param1!=null and param1!=''">
        name like '%${param1}%'
    </if>
        <if test="param2!=null and param2!=''">
            and production like '%${param2}%'
        </if>
    </where>
</select>

第二种方式安全:Bind标签

<select id="selectMore2" resultType="flower">
    select * from flower
    <where>
        <if test="param1!=null and param1!=''">
        <bind name="pa" value="'%'+param1+'%'"></bind>
        name like #{pa}
    </if>
        <if test="param2!=null and param2!=''">
        <bind name="pb" value="'%'+param2+'%'"/>
            and production like #{pb}
        </if>
    </where>
</select>

438 动态SQL_include和sql

sql标签

<!--定义sql代码片段-->
<sql id="sq1">
    id,name,price
</sql>

include标签

<select id="selectMore3" resultType="flower">
    select <include refid="sq1"></include> from flower
</select>

439 多表查询_数据库建立

CREATE TABLE `student` (
  `sid` int(5) NOT NULL AUTO_INCREMENT,
  `sname` varchar(55) DEFAULT NULL,
  `classno` int(5) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `clazz` (
  `clazzno` int(5) NOT NULL AUTO_INCREMENT,
  `cname` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`clazzno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

班级表和学生表是什么关系？

1对多！主外键体现！

多对多必须有第三张表！

440 多表查询业务代码查询A

一对一查询：

1.查询学生所在班级的信息，如何查？

先把所有学生查出来
拿着classno去clazz表查询班级信息

ClazzMapper.xml

<mapper namespace="com.bjsxt.mapper.ClazzMapper">
    <select id="selectOne" resultType="clazz">
        select * from clazz where clazzno=#{param1}
    </select>
</mapper>

StudentMapper.xml

<mapper namespace="com.bjsxt.mapper.StudentMapper">
    <select id="selectAll" resultType="student">
        select * from student
    </select>
</mapper>

业务代码查询：

//4.执行方法
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);

//查询所有学生所在班级的信息
List<Student> list = studentMapper.selectAll();

//查询所有学生--》clazzno
for (Student stu:list
     ) {
    Integer clazzno = stu.getClazzno();
    Clazz clazz = clazzMapper.selectOne(clazzno);
    System.out.println(stu+"-->"+clazz);
}

先定义两个实体类：

Clazz 和 Student

package com.bjsxt.entity;

import java.io.Serializable;

public class Student implements Serializable {
    private Integer sid;
    private String sname;
    private Integer clazzno;

    public Student() {
    }

    public Student(Integer sid, String sname, Integer clazzno) {
        this.sid = sid;
        this.sname = sname;
        this.clazzno = clazzno;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", clazzno=" + clazzno +
                '}';
    }

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public Integer getClazzno() {
        return clazzno;
    }

    public void setClazzno(Integer clazzno) {
        this.clazzno = clazzno;
    }
}

package com.bjsxt.entity;

import java.io.Serializable;

public class Clazz implements Serializable {
    private Integer clazzno;
    private String cname;

    @Override
    public String toString() {
        return "Clazz{" +
                "clazzno=" + clazzno +
                ", cname='" + cname + '\'' +
                '}';
    }

    public Clazz(Integer clazzno, String cname) {
        this.clazzno = clazzno;
        this.cname = cname;
    }

    public Clazz() {
    }

    public Integer getClazzno() {
        return clazzno;
    }

    public void setClazzno(Integer clazzno) {
        this.clazzno = clazzno;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }
}

441 多表查询业务代码查询B

1 想把学生对象和班级对象一起返回，怎么做？

Student

public class Student implements Serializable {
    private Integer sid;
    private String sname;
    private Integer clazzno;
    private Clazz clz;

TestA

for (Student stu:list
     ) {
    Integer clazzno = stu.getClazzno();
    Clazz clazz = clazzMapper.selectOne(clazzno);
    stu.setClz(clazz);
    System.out.println(stu);
}

一对多查询：

2.如何查询班级中所有学生的信息？

public class Clazz implements Serializable {
    private Integer clazzno;
    private String cname;

    private List<Student> li;

    public List<Student> getLi() {
        return li;
    }

StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
 ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);

//查询所有班级中学生的信息

 //查询所有班级的信息
 List<Clazz> clazzList = clazzMapper.selectAll();
 //查询班级中对应的学生信息
 for (Clazz cla:clazzList
      ) {

     Integer clazzno = cla.getClazzno();
     List<Student> list = studentMapper.selectMore(clazzno);
     cla.setLi(list);
     System.out.println(cla);

 }

ClazzMapper.xml

<select id="selectAll" resultType="clazz">
    select * from clazz
</select>

StudentMapper.xml

<select id="selectMore" resultType="student">
    select * from student where clazzno=#{param1}
</select>

442.多变查询 N+1方式查询

1.什么是N+1查询

mybatis想帮你做业务代码逻辑！

//查询所有班级的信息
    List<Clazz> clazzList = clazzMapper.selectAll();
    //查询班级中对应的学生信息
    for (Clazz cla:clazzList
    ) {

        Integer clazzno = cla.getClazzno();
        List<Student> list = studentMapper.selectMore(clazzno);
        cla.setLi(list);
        System.out.println(cla);

}

2.查询学生所在班级的信息

StudentMapper.xml

<select id="selectAll" resultMap="rm1">
    select * from student
</select>

<resultMap id="rm1" type="student">
    <!--column：数据库列名，property:实体的属性名-->
    <id column="sid" property="sid"></id>
    <result column="sname" property="sname"></result>
    <result column="clazzno" property="clazzno"></result>
    <!--
        select *from clazz where clazzno=?
        column：希望查询的那一列作为参数进行传递
        javaType:返回值类型
        property:把返回的结果赋值给对象中哪一个属性

    -->
    <association select="com.bjsxt.mapper.ClazzMapper.selectOne"
                 column="clazzno" javaType="clazz"
                 property="clz"></association>
</resultMap>

TestB

//查询所有学生所在的班级
  List<Student> list = studentMapper.selectAll();
  System.out.println(list);

3.查询班级所在所有学生的集合

ClazzMapper.xml

<resultMap id="rm2" type="clazz">
    <id column="clazzno" property="clazzno"></id>
    <result column="cname" property="cname"></result>

    <collection select="com.bjsxt.mapper.StudentMapper.selectMore"
                column="clazzno" ofType="student"
                property="li"></collection>
</resultMap>


<select id="selectAll" resultMap="rm2">
    select * from clazz
</select>

TestB

  //4.执行方法
  StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
  ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);

//查询所有学生所在的班级
  List<Clazz> list = clazzMapper.selectAll();
  System.out.println(list);

N+1条sql语句，查询效率不高！

443 多表查询多表查询sql语句

1.查询学生所在班级的信息一对一

StudentMapper.xml

<select id="selectAll2" resultMap="rm2">
    select * from student s join clazz c on s.clazzno =c.clazzno
</select>

<resultMap id="rm2" type="student">
    <!--书写的每一个值都是接收数据库查询的数据
		所以想要接受的数据的字段都不可以省略
-->
    <id column="sid" property="sid"></id>
    <result column="sname" property="sname"></result>
    <result column="clazzno" property="clazzno"></result>
    
    <association property="clz" javaType="clazz">
        <id column="clazzno" property="clazzno"></id>
        <result column="cname" property="cname"></result>
    </association>
</resultMap>

TestC

 StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
  ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);

//查询所有学生所在的班级
  List<Student> list = studentMapper.selectAll2();
  for(Student s : list){
      System.out.println(s);
  }

2.查询班级所在所有学生的集合多对一

ClazzMapper.xml

<select id="selectAll2" resultMap="rm2">
    select * from student s join clazz c on s.clazzno = c.clazzno
</select>

<resultMap id="rm2" type="clazz">
    <id column="clazzno" property="clazzno"></id>
    <result column="cname" property="cname"></result>

    <collection ofType="student" property="li">
        <id column="sid" property="sid"></id>
        <result column="sname" property="sname"></result>
        <result column="clazzno" property="clazzno"></result>
    </collection>
</resultMap>

TestC

StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);


List<Clazz> list = clazzMapper.selectAll2();
for(Clazz c : list){
    System.out.println(c);
}

444 Mybatis ResultMap和ResultType的应用场景

单表查询用ResultType

多表查询用 N+1 对标联合属性名和列名如果不一致时使用ResultMap

属性名和列名一致，mybatis可以自动映射！

1.属性名和列名如果不一致时，该如何处理？

1.写sql语句时，起别名！

修改xxxMapper.xml

<resultMap id="rm3" type="clazz">
    <id column="clazzno" property="clazzno1"></id>    
</resultMap>

445 Mybatis 缓存机制

二级缓存共用SqlSessionFactory

<!--启动mybatis中二级缓存-->
<cache readOnly="true"></cache>

在这里插入图片描述

446 ThreadLocal

1.发现问题，一条请求有多条sqlsession对象，如何解决？资源浪费，效率低

public class TestE {
    public static void main(String[] args) {

        new TestE().selectAll();
    }

    public void selectAll(){
        ClazzMapper mapper = DButil.getSqlSession().getMapper(ClazzMapper.class);
        Clazz clazz = mapper.selectOne(1);

        new TestF().findAll();
    }
}

解决：用户发出的一个请求中实现sqlsession的共享！

ThreadLocal作用：在同一个线程中实现数据sqlsession共享！底层封装的是Map集合！

在这里插入图片描述

原理：当前线程的id当作Map的key,sql语句作Map的value!

我怎么知道线程id,底层已经封装好了！

代码实现ThreadLocal

public class DButil {
    //2.获得sqlSession工厂
    private static SqlSessionFactory factory ;
    private  static ThreadLocal<SqlSession> tl =  new ThreadLocal<>();
    static {

        InputStream inputStream = null;
        try {
            //1.解析mybatis.xml
            inputStream = Resources.getResourceAsStream("mybatis.xml");
            //2.获得sqlSession工厂
             factory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //获得sqlsession对象

    public static SqlSession getSqlSession(){
        //获得ThreadLocal中的sqlsession对象
        SqlSession sqlSession = tl.get();
        
        if(sqlSession==null){
            sqlSession =  factory.openSession(true);
            //把创建好的对象放入ThreadLocal
            tl.set(sqlSession);
        }
        return tl.get();
    }
    
     //关闭SqlSession
    public static void closeAll(){
        SqlSession sqlSession = tl.get();
        if(sqlSession!=null){
            sqlSession.close();
        }
        
        tl.set(null);
    }
}

TestE

public class TestE {
    public static void main(String[] args) {

        new TestE().selectAll();
    }

    public void selectAll(){
        ClazzMapper mapper = DButil.getSqlSession().getMapper(ClazzMapper.class);
        Clazz clazz = mapper.selectOne(1);

        new TestF().findAll();

        DButil.closeAll();
    }
}

447 注解支持

StuMapper

public interface StuMapper {

    //查询学生信息
    @Select("select * from student")
    List<Student> selectAll();
    
    @Select("select * from student where sid=#{param1}")
    Student selectOne(int id);
    
    @Insert("insert into student values(default,#{sname},#{clazzno})")
    int insert(Student student);
    
     @Update("update student set sname=#{sname} where sid=#{sid}")
    int update(Student student);

    @Delete("delete from student where sid=#{param1}")
    int delete(int sid);
}

TestG

public class TestG {

    public static void main(String[] args) {
        StuMapper mapper = DButil.getSqlSession().getMapper(StuMapper.class);
        List<Student> list = mapper.selectAll();
        Student student = mapper.selectOne(1);
        System.out.println(list);

    }
}

注解缺点：

1.使用注解无法使java语句和sql解耦

2.无法使用多表查询

3.拼接不行！

448 Mybatis原理

在这里插入图片描述

在MyBatis运行开始时需要先通过Resources加载全局配置文件.下面需要实例化SqlSessionFactoryBuilder构建器.帮助SqlSessionFactory接口实现类DefaultSqlSessionFactory.

在实例化DefaultSqlSessionFactory之前需要先创建XmlConfigBuilder解析全局配置文件流,并把解析结果存放在Configuration中.之后把Configuratin传递给DefaultSqlSessionFactory.到此SqlSessionFactory工厂创建成功.

由SqlSessionFactory工厂创建SqlSession.

每次创建SqlSession时,都需要由TransactionFactory创建Transaction对象,同时还需要创建SqlSession的执行器Excutor,最后实例化DefaultSqlSession,传递给SqlSession接口.

根据项目需求使用SqlSession接口中的API完成具体的事务操作.

如果事务执行失败,需要进行rollback回滚事务.

如果事务执行成功提交给数据库.关闭SqlSession

浪去爪哇岛喝咖啡

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis学习笔记完整版

423 花卉管理系统查询信息1.创建项目结构和导包2.创建实体类flowerpackage com.bjsxt.entity;import java.io.Serializable;public class Flower implements Serializable { private Integer id; private String name; private Integer price; private String production;
复制链接

扫一扫