第81天学习打卡（Mybatis CRUD Lombok 多对一处理一对多处理动态SQL）

最新推荐文章于 2024-09-12 01:15:37 发布

doudoutj

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量122

点赞数

文章标签： mysql java mybatis

本文链接：https://blog.csdn.net/doudoutj/article/details/115337054

版权

8.3 CRUD

我们可以在工具类创建的时候实现自动提交事务！

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

//工具类
//SqlSessionFactory---->sqlSession
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;
  static {

      try {
          //使用mybatis第一步 获取SqlSessionFactory对象
          String resource = "mybatis-config.xml";
          InputStream inputStream = Resources.getResourceAsStream(resource);
         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      } catch (IOException e) {
          e.printStackTrace();
      }
  }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);

    }


}

从上面代码提取的：这个是自动提交事务的设置：

 public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);

    }

编写接口，增加注解

package com.kuang.dao;

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

import java.util.List;
import java.util.Map;

public interface UserMapper {
    @Select("select * from user")
    List<User> getUsers();

    //方法存在多个参数，所有的参数前面必须加上@Param{"id"}注解
    //是根据注解的id进行查询的
    @Select("select * from user where id = #{id}")
    User getUserById(@Param("id") int id);

    @Insert("insert into user(id,name,pwd)values (#{id},#{name},#{password})")
    int addUser(User user);

    @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
    int updateUser(User user);

    @Delete("delete from user where id = #{uid}")
    int deleteUser(@Param("uid") int id);

}

测试类：

【注意：我们必须要讲接口注册绑定到我们的核心配置文件中！】

关于@Param()注解

基本类型的参数或者String类型，需要加上
引用类型不需要加
如果只有一个基本类型的话，可以忽略，但是建议大家都加上！
我们在SQL中引用的就是我们这里的@Param()中设定的属性名！

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

import java.util.List;

public class UserMapperTest {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

//        //底层主要应用反射
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      mapper.deleteUser(5);

      sqlSession.close();
      
  //    mapper.updateUser(new User(5,"to","231231"));//更新语句
        

     // mapper.addUser(new User(5,"hello","123123"));//插入语句


    /*    List<User> users = mapper.getUsers();
        for (User user : users) {
            System.out.println(user);
        }
        User userById = mapper.getUserById(1);
        System.out.println(userById);

*/

    }
}

#{} 与${}区别：

#{}能够很大程度上防止sql注入；

9.Lombok

使用步骤：

1.在IDEA中安装Lombok插件！
2.在项目中导入lombok的jar包

 <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>

    </dependency>

3在实体类上加注解即可

@Getter and @Setter
@FieldNameConstants
@ToString
@EqualsAndHashCode
@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor
@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog
@Data
@Builder
@SuperBuilder
@Singular
@Delegate
@Value
@Accessors
@Wither
@With
@SneakyThrows
@val
@var
experimental @var
@UtilityClass

@Data:无参构造。get set toString hashcode equals
@AllArgsConstructor:有参构造
@NoArgsConstructor:无参构造

Lombok的优缺点

优点：

1.能够通过注解的形式自动生成构造器、getter/setter，equals,hashcode,toString等方法，提高了一定的开发效率

2.让代码变得简洁，不用过多的去关注相应的方法

3.属性做修改时，也简化了维护这些属性所生成的getter/setter方法等

缺点：

1.不支持多种参数构造器的重载

2，虽然省去了手动创建getter/setter方法的麻烦，但大大降低了源代码的可读性和完整性，降低了阅读源代码的舒适度

10.多对一处理：

多个学生，对应一个老师
对于学生这边而言，关联…多个学生，关联一个老师【多对一】
对于老师而言，集合，一个老师有很多学生【一对多】

SQL:

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

遇到的问题：

在resource目录下建包不能用.隔开要用/隔开

测试环境搭建

1.导入lombok

2.新建实体类Teacher,Student

package com.kuang.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;

    //学生需要关联一个老师！
    private Teacher teacher;
}

package com.kuang.pojo;

import lombok.Data;

@Data
public class Teacher {
    private int id;
    private String name;

}

3.建立Mapper接口

package com.kuang.dao;

public interface StudentMapper {
}

package com.kuang.dao;

import com.kuang.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface TeacherMapper {
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

4.建立Mapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.StudentMapper">
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.TeacherMapper">
</mapper>

5.在核心配置文件中绑定注册我们的Mapper接口或者文件【方式很多】

<?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"/>

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

    <typeAliases>
        <package name="com.kuang.pojo"/>
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${pwd}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.kuang.dao.TeacherMapper"/>
        <mapper class="com.kuang.dao.StudentMapper"/>
    </mappers>


</configuration>

6.测试查询是否能够成功

import com.kuang.dao.TeacherMapper;
import com.kuang.pojo.Teacher;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;

public class MyTest {
    public static void main(String[] args) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}

xml中不要写中文注释：

按照查询嵌套处理：

StudentMapper.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.StudentMapper">
   <!--
思路：
1.查询所有学生的信息
2.根据查询出来的学生的tid，寻找对应的老师


--> 
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
<!--复杂的属性，我们需要单独处理  对象association  集合：collection
javaType="" 指定属性的类型
集合中的泛型信息，我们使用ofType获取
-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>

    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id}
    </select>
</mapper>

按照结果嵌套处理：

<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t
    where s.tid = t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

测试：

 @Test
    public void testStudent2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent2();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();

    }

回顾Mysql多对一查询方式：

子查询
联表查询

11.一对多处理

比如：一个老师拥有多个学生！

对于老师而言，就是一对多的关系！
1.环境搭建，和刚才一样

环境搭建

实体类

Teacher:

package com.kuang.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;

    //一个老师拥有多个学生
    private List<Student> students;

}

Student:

package com.kuang.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    private int tid;

}

按照结果嵌套处理

xml中不要有中文注释

  <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid ,s.name sname, t.name tname,t.id tid
        from student s,teacher t
        where s.tid = t.id and t.id = #{tid}
    </select>
    
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--复杂的属性。我们需要单独处理 对象：association 集合：collection  javaType="" 指定属性的类型
集合中的泛型信息，我们使用ofType
-->
        
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

按照查询嵌套处理

  <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from mybatis.teacher where id = #{tid}
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>
    
    <select id="getStudentByTeacherId" resultType="Student">
        select * from mybatis.student where tid = #{tid}
    </select>

小结

1.关联 association 【多对一】

2.集合 collection 【一对多】

3.JavaType & ofType

1.JavaType 用来指定实体类中属性的类型

2.ofType用来指定映射到List或者集合中的pojo类型，泛型中的约束类型！

注意点：

保证SQL的可读性，尽量保证通俗易懂
注意一对多和多对一中属性名和字段的问题
如果问题不好排查错误，可以使用日志，建议使用Log4j

面试高频

Mysql引擎
InnoDB底层原理
索引
索引优化

12.动态SQL

什么是动态SQL：动态SQL就是根据不同的条件生成不同的SQL语句

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

if
choose (when, otherwise)
trim (where, set)
foreach

搭建环境

CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT '博客id',
`title` VARCHAR(100) NOT NULL COMMENT '博客标题',
`author` VARCHAR(30) NOT NULL COMMENT '博客作者',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`views` INT(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8

创建一个基础工程

1.导包

2.编写配置文件

3.编写实体类

package com.kuang.pojo;

import lombok.Data;
import java.util.Date;
@Data
public class Blog {
    private int id;
    private String title;
    private String name;
    private Date createTime;
    private int views;
}

4.编写实体类对应的Mapper接口和Mapper.xml文件

package com.kuang.dao;

import com.kuang.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {
    //插入数据
    int addBlog(Blog blog);

    //查询博客
    List<Blog> queryBlogIF(Map map);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.BlogMapper">
    <insert id="addBlog" parameterType="blog">
 insert into mybatis.blog(id, title,author,create_time,views)
 values(#{id},#{title},#{author},#{createTime},#{views});

    </insert>
    
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog where 1=1
<if test="title != null">
    and title = #{title}
</if>
<if test="author != null">
    and author = #{author}
</if>

    </select>

</mapper>

IF

    
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog where 1=1
<if test="title != null">
    and title = #{title}
</if>
<if test="author != null">
    and author = #{author}
</if>

    </select>

MybatisUtils.java

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

//工具类
//SqlSessionFactory---->sqlSession
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;
  static {

      try {
          //使用mybatis第一步 获取SqlSessionFactory对象
          String resource = "mybatis-config.xml";
          InputStream inputStream = Resources.getResourceAsStream(resource);
         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      } catch (IOException e) {
          e.printStackTrace();
      }
  }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);

    }


}

测试：

import com.kuang.dao.BlogMapper;
import com.kuang.pojo.Blog;
import com.kuang.utils.IDutils;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class MyTest {
    @Test
    public void addInitBlog() {
        SqlSession session = MybatisUtils.getSqlSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IDutils.getId());
        blog.setTitle("Mybatis如此简单");
        blog.setAuthor("狂神说");
        blog.setCreateTime(new Date());
        blog.setViews(9999);


        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Java如此简单");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Spring如此简单");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("微服务如此简单");
        mapper.addBlog(blog);

        session.close();
    }

    @Test
    public void queryBlogIF(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
      //  map.put("title","Java如此简单");
        map.put("author","狂神说");
        List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();

    }

}