SSM框架12 分页功能、注解开发和CRUD、#和$区别、Lombok、association和collection、动态SQL语句、foreach动态sql、多数据库实现

Wantfly9951

已于 2023-01-26 17:12:13 修改

阅读量624

点赞数

分类专栏： SSM 文章标签： sql mybatis java

于 2023-01-25 02:16:59 首次发布

本文链接：https://blog.csdn.net/Wantfly9951/article/details/128739404

版权

SSM 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

文章目录

一、分页功能的实现

1、常规sql分页

语法：
select * from user limit startindex,pagesize;
select * from user limit pagesize;

mybatis实现分页，核心sql
1、接口
2、Mapper.xml文件
3、测试

    <select id="selectUserLimit" resultType="User" parameterType="map">
        select * from mybatis.user limit #{pageindex},#{pagesize};
    </select>

    @Test
    public void test7(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        Map<String,Integer> map=new HashMap<String, Integer>();
        map.put("pageindex",1);
        map.put("pagesize",3);
        List<User> list = mapper.selectUserLimit(map);
        System.out.println(list);
    }

2、RowBounds进行分页

    @Test
    public void test8(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
//      每页显示limit个 从offset开始查
        RowBounds rowBounds = new RowBounds(3, 3);

        List<User> list=sqlSession.selectList("dao.UserDao.selectUserLimitRowBounds",null,rowBounds);

        System.out.println(list.toString());
    }

List<User> selectUserLimitRowBounds(Map<String,Integer> map);

    <select id="selectUserLimitRowBounds" resultType="User">
        select * from mybatis.user;
    </select>

3、也可以使用mybatis插件进行分页（PageHelper）

二、使用注解开发

平时更喜欢用面向接口编程，解耦
注解对复杂的sql语句力不从心
注解进行查找

package dao;

import org.apache.ibatis.annotations.Select;
import pojo.User;

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

public interface UserDao {

    @Select("select * from mybatis.user;")
    List<User> getUser();
}

    <!--    配置映射 每一个mapper都需要再mybatis配置文件中注册-->
    <mappers>
        <mapper class="dao.UserDao"/>
    </mappers>

import dao.UserDao;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.User;
import until.MybatisUntil;

import java.util.List;

public class UserTest {
    @Test
    public void test1(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        List<User> user = mapper.getUser();
        System.out.println(user);
        sqlSession.close();
    }
}

三、注解实现CRUD

直接给openSession直接赋值true 不用再手动commit

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

package dao;

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

import java.util.List;

public interface UserDao {

    @Select("select * from mybatis.user;")
    List<User> getUser();
//  方法存在多个参数的时候，参数前面必须要加上@Param
    @Delete("delete from user where name=#{username};")
    int deleteuser(@Param("username") String username);

    @Insert("insert into user values (#{id},#{username},#{pwd});")
    int insertuser(@Param("id") int id,@Param("username") String username,@Param("pwd") String pwd);

    @Update("update user set pwd=#{pwd} where name=#{username}")
    int updateuser(@Param("username") String username,@Param("pwd") String pwd);
}

import dao.UserDao;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.User;
import until.MybatisUntil;

import java.util.List;

public class UserTest {
//    注释实现查询操作
    @Test
    public void test1(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        List<User> user = mapper.getUser();
        System.out.println(user);
        sqlSession.close();
    }
//  注释实现删除操作
    @Test
    public void test2(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        int mawu = mapper.deleteuser("mawu");
        if (mawu>0){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
        sqlSession.close();
    }
//  注释实现删除操作
    @Test
    public void test3(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        int a1 = mapper.insertuser(19,"a2", "123");
        if (a1>0){
            System.out.println("插入成功");
        }else {
            System.out.println("插入失败");
        }
        sqlSession.close();
    }
//  注释实现修改操作
    @Test
    public void test4(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        int lisi = mapper.updateuser("lisi", "999");
        if (lisi>0){
            System.out.println("修改成功");
        }else {
            System.out.println("删除失败");
        }
        sqlSession.close();
    }
}

四、#{}和${}的区别

1.#{}是预编译处理，是占位符，${}是字符串替换，是拼接符。也就是说参数是字符串类型的时候#{}不需要自己在sql语句写引号，而${}需要。
2.Mybatis在处理#{}的时候会将sql中的#{}替换成？号，调用PreparedStatement来赋值

#{}将传入的数据当作一个字符串，会对传入的数据加上一个双引号。

3.Mybatis在处理${}的时候就是把${}替换成变量的值，调用Statement来赋值

${}将传入的数据直接显示生成在sql中；

4. #{}方式能够很大程度上防止sql注入。

5.${}无法防止sql注入。

6.${}方式一般用于传入数据库对象，例如列表和表名。

7.由于#{}方式具有更高的安全行，所以能用#{}的地方尽量不要使用${}。

8.Mybatis排序时使用order by动态参数时需要注意，用${}而不是#{}。

五、Lombok

一个java库，一个插件，一个构建工具
省去自动生成get方法和set方法
自动化日志变量

步骤
1、IDEA安装Lombok插件
2、项目中导入Lombok的依赖
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
@StandardException
@val
@var
experimental @var
@UtilityClass
Lombok config system

package pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.type.Alias;
//@Alias设置别名
@Alias("user")
//@Data：无参构造、get、set、tostring、hashcode、equals
@Data
//@AllArgsConstructor：自动生成有参构造，加上之后无参构造就没了
@AllArgsConstructor
//@NoArgsConstructor：自动生成无参构造器
@NoArgsConstructor
public class User {
    private int id;
    private String name;
    private String pwd;
}

六、Mybatis多对一的处理

create table `student`(
    `id` INT(20) NOT NULL,
    `name` varchar(50) not null ,
    `tid` INT(20) not null,
    primary key (`id`),
    key `fktid`(`tid`),
    constraint `fktid` foreign key (`tid`) references `teacher` (`id`)
    )engine =innodb default charset =utf8;

create table `teacher`(
    `id` INT(20) NOT NULL,
    `name` varchar(50) not null ,
    primary key (`id`)
    )engine =innodb default charset =utf8;

关联关系的多对一查找

<?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="dao.StudentMapper">
<!--    第一种方法 用resultMap-->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student s,teacher t where s.tid=t.id;
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
<!--        复杂的属性单独处理
        对象就用association,集合用collection
        对象就用association column代表根据什么查的 javaType代表对应的类
-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="pojo.Teacher">
        select * from teacher where id=#{tid};
    </select>
<!--    第二种方法 嵌套处理-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id;
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"></result>
            <result property="name" column="tname"></result>
        </association>
    </resultMap>

</mapper>

import dao.StudentMapper;
import dao.TeacherMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Student;
import pojo.Teacher;
import until.MybatisUntil;

import java.util.List;

public class TestDemo {
    @Test
    public void test1(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(2);
        System.out.println(teacher.toString());
        sqlSession.close();
    }
    @Test
    public void test2(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> student = mapper.getStudent();
        for (Student student1 : student) {
            System.out.println(student1.toString());
        }
        sqlSession.close();
    }
    @Test
    public void test3(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> student = mapper.getStudent2();
        for (Student student1 : student) {
            System.out.println(student1.toString());
        }
        sqlSession.close();
    }

}

package dao;

import org.apache.ibatis.annotations.Select;
import pojo.Student;

import java.util.List;

public interface StudentMapper {
//    查询所有的学生以及对应的老师的声音
    List<Student> getStudent();

    List<Student> getStudent2();
}

七、Mybatis一对多的处理

package pojo;

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

import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
}

package dao;

import pojo.Teacher;

import java.util.List;

public interface TeacherMapper {
    List<Teacher> getTeacher();
    List<Teacher> getTeacher2();
}

<?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="dao.TeacherMapper">
<!--    结果嵌套查询-->
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid,t.id tid,t.name tname,s.name sname
        from student s,teacher t
        where s.tid=t.id
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"></result>
        <result property="name" column="tname"></result>
<!--        javaType指定属性类型 集合中的泛型类型用ofType指定-->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"></result>
            <result property="name" column="sname"></result>
            <result property="tid" column="tid"></result>
        </collection>
    </resultMap>
<!-- 第二种方法-->
    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from teacher
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <result column="id" property="id"></result>
        <result property="name" column="name"></result>
        <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId"></collection>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="Student">
        select * from student where tid=#{tid}
    </select>
</mapper>

import dao.TeacherMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Teacher;
import until.MybatisUntil;

public class TestDemo {
    @Test
    public void test1(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        for (Teacher teacher : mapper.getTeacher()) {
            System.out.println(teacher.toString());
        }
        sqlSession.close();

    }

    @Test
    public void test2(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        for (Teacher teacher : mapper.getTeacher2()) {
            System.out.println(teacher.toString());
        }
        sqlSession.close();

    }
}

八、动态SQL（if、where、when、choose、set、trim、otherwise）

动态SQL根据条件生成不同的SQL语句
在mybatis.xml settings标签设置驼峰命名

    <settings>
<!--        开启自动驼峰命名法，A_column和aColunm进行映射-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

create table `blog`(
    `id` int(20) 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;

import dao.BlogMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Blog;
import until.IDUntil;
import until.MybatisUntil;

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

public class TestDemo {
    @Test
    public void test1(){
        SqlSession sqlSession = MybatisUntil.getMybatis();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog1 = new Blog();
        blog1.setId(IDUntil.getId());
        blog1.setTitle("标题3");
        blog1.setAuthor("kerwin3");
        blog1.setCreateTime(new Date());
        blog1.setViews(9999);

        mapper.addBlog(blog1);

        Blog blog2 = new Blog();
        blog2.setId(IDUntil.getId());
        blog2.setTitle("标题4");
        blog2.setAuthor("kerwin4");
        blog2.setCreateTime(new Date());
        blog2.setViews(9998);

        mapper.addBlog(blog2);

        sqlSession.commit();

        sqlSession.close();
    }

    @Test
    public void test2(){
        SqlSession mybatis = MybatisUntil.getMybatis();
        BlogMapper mapper = mybatis.getMapper(BlogMapper.class);
        HashMap<String, String> map = new HashMap<>();
        map.put("title","标题1");
        map.put("author","kerwin1");
        for (Blog blog : mapper.selectBlog(map)) {
            System.out.println(blog.toString());
        }
        mybatis.close();

    }

    @Test
    public void test3(){
        SqlSession mybatis = MybatisUntil.getMybatis();
        BlogMapper mapper = mybatis.getMapper(BlogMapper.class);
        HashMap map = new HashMap<>();
        map.put("title","标题1");
        map.put("author","kerwin1");
        map.put("views",9999);
        for (Blog blog : mapper.selectBlogChoose(map)) {
            System.out.println(blog.toString());
        }
        mybatis.close();

    }

    @Test
    public void test4(){
        SqlSession mybatis = MybatisUntil.getMybatis();
        BlogMapper mapper = mybatis.getMapper(BlogMapper.class);
        HashMap map = new HashMap<>();
        map.put("views",8888);
        map.put("id","8fc19c9e3d72405ea1f335dc545179d9");
        int i = mapper.updateBlog(map);
        if (i>0){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }
        mybatis.commit();
        mybatis.close();

    }
}

<?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="dao.BlogMapper">
    <insert id="addBlog" parameterType="pojo.Blog">
        insert into blog values (#{id},#{title},#{author},#{createTime},#{views});
    </insert>
<!--    if 结合where进行判断-->
    <select id="selectBlog" parameterType="map" resultType="Blog">
        select * from blog where 1=1
        <if test="title!=null">
            and title=#{title}
        </if>
        <if test="author!=null">
            and author=#{author}
        </if>
    </select>
<!--    choose when otherwise类似java的switch语句
        where会自动去掉and
    -->
    <select id="selectBlogChoose" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <choose>
                <when test="title!=null">
                    title=#{title}
                </when>
                <when test="author!=null">
                    and author = #{author}
                </when>
                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>

    </select>
<!--    set标签则是在set语句中会删除无关的逗号-->
    <update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title!=null">
                title=#{title},
            </if>
            <if test="author!=null">
                author=#{author},
            </if>
            <if test="createTime!=null">
                create_time=#{createTime},
            </if>
            <if test="views!=null">
                views=#{views}
            </if>
        </set>
        where id=#{id}

        <trim prefix="WHERE" prefixOverrides="AND|OR" suffix="">

        </trim>
        <trim prefix="SET"  suffixOverrides=",">

        </trim>
    </update>
<!--    可以用trim定制where功能-->
</mapper>

package dao;

import pojo.Blog;

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

public interface BlogMapper {
    int addBlog(Blog blog);

    List<Blog> selectBlog(Map map);

    List<Blog> selectBlogChoose(Map map);

    int updateBlog(Map map);
}

package pojo;

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

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

九、动态SQL之Foreach

SQL片段：将一些功能的部分抽取出来重复使用
通过sql标签抽取公共部分
include标签引用
注意：
最好基于单表定义sql片段
不要存在where标签

    <sql id="if-title-author">
        <if test="title!=null">
            and title=#{title}
        </if>
        <if test="author!=null">
            and author=#{author}
        </if>
    </sql>
<!--    if 结合where进行判断-->
    <select id="selectBlog" parameterType="map" resultType="Blog">
        select * from blog where 1=1
        <include refid="if-title-author"></include>
    </select>

foreach将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

<!--    foreach实现集合的遍历，in实现判断-->
    <select id="selectBlogForeach" parameterType="map" resultType="Blog">
        select * from blog where author in
        <foreach collection="list" index="index" item="item" close=")" open="(" separator=",">
            #{item}
        </foreach>
    </select>

    @Test
    public void test5(){
        SqlSession mybatis = MybatisUntil.getMybatis();
        BlogMapper mapper = mybatis.getMapper(BlogMapper.class);
        HashMap map = new HashMap<>();
        ArrayList list = new ArrayList<>();
        list.add("kerwin1");
        list.add("kerwin2");
        map.put("list",list);
        List<Blog> blogs = mapper.selectBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog.toString());
        }
        mybatis.close();

    }

十、多数据库支持

如果配置了 databaseIdProvider，你就可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    </if>
  </selectKey>
  insert into users values (#{id}, #{name})
</insert>