mybatis笔记

奔跑的飞行兵

于 2023-10-24 21:04:41 发布

阅读量95

点赞数

分类专栏： Spring 文章标签： 1024程序员节

本文链接：https://blog.csdn.net/zc58778560/article/details/134021757

版权

Spring 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

快速入门

开发步骤：

（1）添加依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.13</version>
    <scope>compile</scope>
</dependency>

（2）创建User数据表

create table user
(
    id       int auto_increment,
    username varchar(20) not null,
    password varchar(20) not null,
    constraint user_id_uindex
        unique (id),
    constraint user_username_uindex
        unique (username)
);

（3）编写User实体类

（4）编写映射文件UserMapper.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="userMapper">
    <select id="findAll" resultType="com.example.domain.User">
        select * from user
    </select>
</mapper>

（5）编写核心文件SqlMapConfig.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="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/sql_test"/>
                <property name="username" value="root"/>
                <property name="password" value="toor"/>
            </dataSource>
        </environment>
    </environments>

    <!--加载映射文件-->
    <mappers>
        <mapper resource="com/example/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

（6）测试

//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session会话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
List<User> userList = sqlSession.selectList("userMapper.findAll");
//打印数据
System.out.println(userList);
//释放资源
sqlSession.close();

增加操作

映射文件：

<insert id="save" parameterType="com.example.domain.User">
    insert into user values(#{id}, #{username}, #{password})
</insert>

代码：

//执行sql语句
User user = new User();
user.setUsername("tom");
user.setPassword("123456");
int row = sqlSession.insert("userMapper.save", user);
//要提交事务，才会生效
sqlSession.commit();
//打印数据
System.out.println("影响行数：" + row);

修改操作

映射文件：

<update id="update" parameterType="com.example.domain.User">
    update user set username=#{username}, password=#{password} where id=#{id}
</update>

代码：

//执行sql语句
User user = new User();
user.setId(51);
user.setUsername("tom2");
user.setPassword("1234562");
int row = sqlSession.update("userMapper.update", user);
//要提交事务，才会生效
sqlSession.commit();
//打印数据
System.out.println("影响行数：" + row);

删除操作

映射文件：

<delete id="delete" parameterType="java.lang.Integer">
    delete from user where id=#{xxx}
</delete>

代码：

//执行sql语句
int row = sqlSession.delete("userMapper.delete", 5);
//要提交事务，才会生效
sqlSession.commit();
//打印数据
System.out.println("影响行数：" + row);

核心配置文件

1.environments标签

事务管理器类型有两种：

JDBC、MANAGED

数据源类型有三种：

UNPOOLED、POOLED、JNDI

2.mapper标签

加载方式有如下几种：

(1)使用相对于类路径的资源引用：

<mapper resource="com/example/mapper/UserMapper.xml"/>

(2)使用完全限定资源符：

<mapper url="file:///home/zc/mapper/UserMapper.xml"/>

(3)使用映射器接口实现类的完全限定类名：

<mapper class="org.mybatis.builder.AuthorMapper"/>

(4)将包内的映射器接口实现全部注册为映射器：

<package name="org.mybatis.builder"/>

3.properties标签

可以读取配置文件：

<properties resource="jdbc.properties"/>

<!--数据源环境-->
<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"></transactionManager>
        <dataSource type="POOLED">
            <property name="driver" value="${jdbc.driver}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="username" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
        </dataSource>
    </environment>
</environments>

4.typeAliases标签

在核心配置文件中定义别名：

<!--自定义别名-->
<typeAliases>
    <typeAlias type="com.example.domain.User" alias="user"/>
</typeAliases>

在映射文件中直接使用别名：

<select id="findAll" resultType="user">
    select * from user
</select>

mybatis内置的一些别名：

string -> String
long -> Long
int -> Integer
double -> Double
boolean -> Boolean

相应API

1.工厂构建器SqlSessionFactoryBuilder

//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

2.工厂对象SqlSessionFactory

//获得session会话对象
SqlSession sqlSession1 = sqlSessionFactory.openSession(); //不会自动提交事务
SqlSession sqlSession2 = sqlSessionFactory.openSession(true); //会自动提交事务

3.SqlSession对象

执行语句：

操作事务：

void commit();
void rollback();

MyBatis的Dao层实现

1.传统开发方式

编写Dao层接口：

public interface UserDao {
    List<User> findAll() throws IOException;
}

编写Dao层实现：

public class UserDaoImpl implements UserDao {
    @Override
    public List<User> findAll() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> userList = sqlSession.selectList("userMapper.findAll");
        return userList;
    }
}

2.代理开发方式

编写UserDao接口：

public interface UserDao2 {
    User findById(int id);
}

编写映射文件：

<mapper namespace="com.example.dao.UserDao2">
    <select id="findById" parameterType="int" resultType="com.example.domain.User">
        select * from user where id=#{xxx}
    </select>
</mapper>

使用：

InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//获得动态代理类
UserDao2 userDao2 = sqlSession.getMapper(UserDao2.class);
User user = userDao2.findById(1);
System.out.println(user);

Mapper接口开发需要遵循的规范：

1、Mapper.xml文件中的namespace与Dao接口的全限定名相同

2、Mapper.xml文件中的每个statement的id与Dao接口的方法名相同

3、Mapper.xml文件中的每个statement的parameterType与Dao接口方法的入参类型相同

4、Mapper.xml文件中的每个statement的resultType与Dao接口方法的返回类型相同

映射文件深入

1.动态sql

if

映射文件：

<select id="findByCondition" parameterType="user" resultType="user">
    select * from user
    <where>
        <if test="id!=0">
            and id=#{id}
        </if>
        <if test="username!=null">
            and username=#{username}
        </if>
        <if test="password!=null">
            and password=#{password}
        </if>
    </where>
</select>

代码：

UserDao2 userDao2 = sqlSession.getMapper(UserDao2.class);

User condition = new User();
condition.setUsername("u1");

List<User> userList = userDao2.findByCondition(condition);

foreach

我想实现查询语句：select * from user where id in(1,2,3);

映射文件：

<select id="findByIds" parameterType="list" resultType="user">
    select * from user
    <where>
        <foreach collection="list" open="id in(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </where>
</select>

代码：

UserDao2 userDao2 = sqlSession.getMapper(UserDao2.class);

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

List<User> userList = userDao2.findByIds(ids);

2.SQL片段抽取

<sql id="selectUser">select * from user</sql>

<select id="findById" parameterType="int" resultType="com.example.domain.User">
    <include refid="selectUser"/> where id=#{xxx}
</select>

核心配置文件深入

1.typeHandler标签

例如：一个Java中的Date类型，我想将它变成1970年至今的毫秒数存到数据库，读出来时再转成Date。

（1）定义转换类

public class DateTypeHandler extends BaseTypeHandler<Date> {
    //java类型 转 sql类型
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        long time = parameter.getTime();
        ps.setLong(i, time);
    }

    //sql类型 转 java类型
    @Override
    public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
        long time = rs.getLong(columnName);
        Date date = new Date(time);
        return date;
    }

    //sql类型 转 java类型
    @Override
    public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        long time = rs.getLong(columnIndex);
        Date date = new Date(time);
        return date;
    }

    //sql类型 转 java类型
    @Override
    public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        long time = cs.getLong(columnIndex);
        Date date = new Date(time);
        return date;
    }
}

（2）创建数据表

create table people
(
    id       int    not null
        primary key,
    birthday bigint not null
);

（3）在核心配置文件中进行注册

<typeHandlers>
    <typeHandler handler="com.example.handler.DateTypeHandler"/>
</typeHandlers>

（4）测试

写入：

PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);

People people = new People();
people.setId(1);
people.setBirthday(new Date(2023-1900, Calendar.JULY, 21));

peopleMapper.save(people);

读取：

PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
People people = peopleMapper.findById(1);
System.out.println(people);

2.plugins标签

可以使用第三方插件来对功能进行扩展，这里以分页助手PageHelper为例进行介绍。

（1）导入PageHelper依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.3.2</version>
    <scope>compile</scope>
</dependency>
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.5</version>
</dependency>

（2）修改核心配置文件

<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <property name="dialect" value="com.github.pagehelper.PageHelper"/>
    </plugin>
</plugins>

（3）测试

//设置分页相关参数(当前页，每页大小)
PageHelper.startPage(1, 2);

//本来是获取所有数据的，但是会自动帮我们加上分页
List<User> userList = userDao2.findAll();
System.out.println(userList);

//获得与分页相关参数
PageInfo<User> pageInfo = new PageInfo<>(userList);
System.out.println("当前页：" + pageInfo.getPageNum());
System.out.println("每页显示条数：" + pageInfo.getPageSize());
System.out.println("总条数：" + pageInfo.getTotal());
System.out.println("总页数：" + pageInfo.getPages());
System.out.println("上一页：" + pageInfo.getPrePage());
System.out.println("下一页：" + pageInfo.getNextPage());
System.out.println("是否是第一页：" + pageInfo.isIsFirstPage());
System.out.println("是否最后一页：" + pageInfo.isIsLastPage());

多表查询

一对一查询

假设我有2个实体类User和Order，Order中引用User。

Order类：

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    private User user;
}

order表：

create table `order`
(
    id        int auto_increment
        primary key,
    ordertime datetime not null,
    total     double   not null,
    uid       int      not null,
    constraint order_user_id_fk
        foreign key (uid) references user (id)
);

映射文件：

<mapper namespace="com.example.dao.OrderMapper">
    <!--说明怎么把sql结果转成java类型-->
    <resultMap id="map1" type="com.example.domain.Order">
        <id column="o.id" property="id"/>
        <result column="ordertime" property="ordertime"/>
        <result column="total" property="total"/>
        <association property="user" javaType="com.example.domain.User">
            <id column="u.id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
        </association>
    </resultMap>

    <!--查2张表-->
    <select id="findAll" resultMap="map1">
        select * from `order` o,user u where o.uid=u.id
    </select>
</mapper>

一对多查询

例如：查询当前用户的所有订单

映射文件：

<mapper namespace="com.example.dao.UserDao2">
    <resultMap id="map2" type="user">
        <id column="u.id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <collection property="orderList" ofType="com.example.domain.Order">
            <id column="o.id" property="id"/>
            <result column="ordertime" property="ordertime"/>
            <result column="total" property="total"/>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="map2">
        select * from user u,`order` o where u.id=o.uid
    </select>
</mapper>

多对多查询

准备3张表：zc_user, zc_role, zc_user_role

create table zc_user
(
    id int auto_increment primary key,
    username varchar(20) not null
);

create table zc_role
(
    id int auto_increment primary key,
    rolename varchar(20) not null
);

create table zc_user_role
(
    user_id int not null,
    role_id int not null,
    constraint zc_user_role_ibfk_1
        foreign key (user_id) references zc_user (id),
    constraint zc_user_role_ibfk_2
        foreign key (role_id) references zc_role (id)
);

ZcUser类：

public class ZcUser {
    private int id;
    private String username;
    private List<ZcRole> roleList;
    //省略...
}

ZcRole类：

public class ZcRole {
    private int id;
    private String rolename;
    //省略...
}

映射文件：

<?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.example.dao.ZcUserMapper">
    <resultMap id="map1" type="com.example.domain.ZcUser">
        <id column="u.id" property="id"/>
        <result column="username" property="username"/>
        <collection property="roleList" ofType="com.example.domain.ZcRole">
            <id column="r.id" property="id"/>
            <result column="rolename" property="rolename"/>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="map1">
        select * from zc_user u, zc_user_role ur, zc_role r where u.id=ur.user_id and ur.role_id=r.id
    </select>
</mapper>

注解开发

快速入门

准备实体对象Student：

public class Student {
    private int id;
    private String name;
    //省略...
}

创建对应数据表。

创建StudentMapper：

public interface StudentMapper {
    @Insert("insert into student values(#{id},#{name})")
    void save(Student student);

    @Select("select * from student")
    List<Student> findAll();

    @Select("select * from student where id=#{id}")
    Student findById(int id);

    @Update("update student set name=#{name} where id=#{id}")
    void update(Student student);

    @Delete("delete from student where id=#{id}")
    void delete(int id);
    
    //下面这个是新加的，与上面无关，只是记录一下
    //指定表的id列为自增主键并自动绑定到pojo
    @Options(useGeneratedKeys = true,keyColumn = "id")
    @Insert("insert into department(departmentName) values(#{departmentName})")
    int insertDept(Department department);
}

在核心配置文件中配置映射关系：

<!--加载映射关系-->
<mappers>
    <package name="com.example.dao"/>
</mappers>

使用：

public class AnnoMapperTest {
    private StudentMapper studentMapper;

    @Before
    public void setup() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        studentMapper = sqlSession.getMapper(StudentMapper.class);
    }

    @Test
    public void test_save() {
        Student student = new Student();
        student.setName("张灿");
        studentMapper.save(student);
    }

    @Test
    public void test_findAll() {
        List<Student> studentList = studentMapper.findAll();
        System.out.println(studentList);
    }

    @Test
    public void test_findById() {
        Student student = studentMapper.findById(1);
        System.out.println(student);
    }

    @Test
    public void test_update() {
        Student student = new Student();
        student.setId(1);
        student.setName("张灿2");
        studentMapper.update(student);
    }

    @Test
    public void test_delete() {
        studentMapper.delete(1);
    }
}

一对一查询

假设Order类有个字段是User

public interface OrderMapper {
    @Select("select * from `order` o, user u where o.uid=u.id")
    @Results({
            @Result(column = "o.id", property = "id"),
            @Result(column = "ordertime", property = "ordertime"),
            @Result(column = "total", property = "total"),
            @Result(column = "u.id", property = "user.id"),
            @Result(column = "username", property = "user.username"),
            @Result(column = "password", property = "user.password"),
    })
    List<Order> findAll();
}

另一种写法：

    @Select("select * from `order`")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "ordertime", property = "ordertime"),
            @Result(column = "total", property = "total"),
            @Result(
                    property = "user",
                    column = "uid", //使用返回的uid作为参数去查User
                    javaType = User.class,
                    one = @One(select = "com.example.dao.UserMapper.findById")
            )
    })
    List<Order> findAll2();

public interface UserMapper {
    @Select("select * from user where id=#{id}")
    User findById(int id);
}

一对多查询

假设一个User对应多个Order：

public interface UserMapper {
    @Select("select * from user")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "username", property = "username"),
            @Result(column = "password", property = "password"),
            @Result(
                    property = "orderList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select = "com.example.dao.OrderMapper.findByUid")
            )
    })
    List<User> findAllAndOrders();
}

public interface OrderMapper {
    @Select("select * from `order` where uid=#{uid}")
    List<Order> findByUid(int uid);
}

多对多查询

还是前面的3张表：zc_user, zc_role, zc_user_role

假设我们要查User，顺带上对应的Role

public interface ZcUserMapper {
    @Select("select * from zc_user")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "username", property = "username"),
            @Result(
                    property = "roleList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select = "com.example.dao.ZcRoleMapper.findByUserId")
            )
    })
    List<ZcUser> findUserAndRoleAll();
}

public interface ZcRoleMapper {
    @Select("select * from zc_user_role ur,zc_role r where ur.user_id=#{uid} and ur.role_id=r.id")
    List<ZcRole> findByUserId(int uid);
}

奔跑的飞行兵

关注

0
点赞
踩
0

收藏

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

开发步骤：（1）添加依赖</</</</</</</</</</（2）创建User数据表（3）编写User实体类（4）编写映射文件UserMapper.xml</</（5）编写核心文件SqlMapConfig.xml</</</</</</（6）测试。
复制链接

扫一扫