学习mybatis 整理大全

最新推荐文章于 2022-04-25 17:13:31 发布

Corgy.

最新推荐文章于 2022-04-25 17:13:31 发布

阅读量199

点赞数

分类专栏： java 文章标签： mybatis java mysql 数据库

本文链接：https://blog.csdn.net/small_dog_/article/details/109822365

版权

java 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

Mybatis学习记录

基于maven的学习
无整合只是入门

开发步骤（以User代表）

添加mybatis的坐标
创建user数据表
编写User数据表类
编写核心映射文件UserMapper.xml
编写核心文件SqlMapConfig.xml
编写测试类

入门配置

位置导包

 <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.11</version>
    </dependency>
    <!--mybatis驱动包-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.3</version>
    </dependency>
    <!--日志文件驱动-->
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.12</version>
    </dependency>
</dependencies>

user数据表

在数据库创建

Create Table
CREATE TABLE `user` (
  `id` int(111) NOT NULL AUTO_INCREMENT,
  `username` varchar(111) NOT NULL,
  `password` varchar(111) NOT NULL,
  PRIMARY KEY (`id`)
)

User数据表类

package cn.corgy.domain1;

public class User {
    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

映射文件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="cn.corgy.domain1.User">
    select * from User
  </select>
</mapper>

核心文件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"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql:///test?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <!--加载映射文件-->
    <mappers>
        <mapper resource="cn/corgy/mapper/UserMapper"/>
    </mappers>

</configuration>

测试类

@Test
public void test1() throws IOException {
    //加载核心配置文件
    InputStream resourceAsFile = Resources.getResourceAsStream("sqlMapper.xml");
    //获得工厂对象
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsFile);
    //获取会话对象
    SqlSession sqlSession = build.openSession();
    //执行操作
    List<User> list = sqlSession.selectList("userMapper.FindAll");

    for (User it :
            list) {
        System.out.println(it);
    }
    sqlSession.close();

}

注意映射文件详解不全

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q6HN6wAW-1605784904228)(C:\Users\wang\AppData\Roaming\Typora\typora-user-images\image-20201113212835306.png)]

mybatisCRUD操作

增

添加映射

<!--    增加操作-->
<insert id="InsertOne" parameterType="cn.corgy.domain1.User">
    INSERT INTO user value (#{id},#{username},#{password});
</insert>

增加测试类

//增加操作
@Test
public void test2() throws IOException {
    User user = new User();
    user.setId(5);
    user.setUsername("小蛇");
    user.setPassword("1234");
    //加载核心配置文件
    InputStream resourceAsFile = Resources.getResourceAsStream("sqlMapper.xml");
    //获得工厂对象
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsFile);
    //获取会话对象
    SqlSession sqlSession = build.openSession();
    //添加操作
    int insert = sqlSession.insert("userMapper.InsertOne", user);
    sqlSession.close();
    System.out.println(insert);
    sqlSession.connit();
}

注意事务的提交

删

添加映射

 <!--删除操作-->
    <delete id="DeleteOne" parameterType="java.lang.Integer">
delete from user where id=#{id}
    </delete>

增加测试类

    //删除操作
    @Test
    public void test4() throws IOException {
        //加载核心配置文件
        InputStream resourceAsFile = Resources.getResourceAsStream("sqlMapper.xml");
        //获得工厂对象
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsFile);
        //获取会话对象
        SqlSession sqlSession = build.openSession();
        //执行操作
        int delete = sqlSession.delete("userMapper.DeleteOne", 1);
        System.out.println(delete);
        sqlSession.commit();
        sqlSession.close();
    }
}

修改注意事务的提交

改

添加映射

<!--修改操作-->
    <update id="UpdateOne" parameterType="cn.corgy.domain1.User">
update user set username=#{username},password=#{password} where id=#{id}
    </update>

添加测试类

//修改操作
@Test
public void test3() throws IOException {
    User user = new User();
    user.setId(5);
    user.setUsername("是啥");
    user.setPassword("111111");
    //加载核心配置文件
    InputStream resourceAsFile = Resources.getResourceAsStream("sqlMapper.xml");
    //获得工厂对象
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsFile);
    //获取会话对象
    SqlSession sqlSession = build.openSession();
    //添加操作
    int insert = sqlSession.update("userMapper.UpdateOne", user);
    //事务提交
    sqlSession.commit();
    sqlSession.close();
    System.out.println(insert);

}

注意事务的提交

查

添加映射

  <!-- 查询操作-->
<select id="FindAll" resultType="cn.corgy.domain1.User">
 	 select * from User;
</select>
 <!--根据id查询-->
<select id="FindOne" resultType="user" parameterType="int">
    select * from User where id=#{id};
</select>

添加测试类

找入门↑查询全部

//根据id查询
@Test
public void test5() throws IOException {
    //加载核心配置文件
    InputStream resourceAsFile = Resources.getResourceAsStream("sqlMapper.xml");
    //获得工厂对象
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsFile);
    //获取会话对象
    SqlSession sqlSession = build.openSession();
    //执行操作
    User user= sqlSession.selectOne("userMapper.FindOne",2);
    //关闭连接
    sqlSession.close();
    System.out.println(user);
}

Mybatis核心配置文件层次关系

数据关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dFTImU3U-1605784904232)(C:\Users\wang\AppData\Roaming\Typora\typora-user-images\image-20201113223444714.png)]

environments标签

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CNKxOYb2-1605784904235)(C:\Users\wang\AppData\Roaming\Typora\typora-user-images\image-20201113223634402.png)]

其中，事务管理器（transactionManager）类型有两种：

JDBC：这个配置就是直接使用了JDBC 的提交和回滚设置，它依赖于从数据源得到的连接来管理事务作用域。
MANAGED：这个配置几乎没做什么。它从来不提交或回滚一个连接，而是让容器来管理事务的整个生命周期（比如 JEE 应用服务器的上下文）。默认情况下它会关闭连接，然而一些容器并不希望这样，因此需要将 closeConnection 属性设置为 false 来阻止它默认的关闭行为。

其中，数据源（dataSource）类型有三种：

UNPOOLED：这个数据源的实现只是每次被请求时打开和关闭连接。沒有进行优化的JDBC。
POOLED：这种数据源的实现利用“池”的概念将 JDBC 连接对象组织起来。
JNDI：这个数据源的实现是为了能在如 EJB 或应用服务器这类容器中使用，容器可以集中或在外部配置数据源，然后放置一个 JNDI 上下文的引用。

映射文件标签

<!--加载映射文件-->
<mappers>
    <mapper resource="cn/corgy/mapper/UserMapper"/>
</mappers>

该标签的作用是加载映射的，加载方式有如下几种：

使用相对于类路径的资源引用，例如：
使用完全限定资源定位符（URL），例如：
使用映射器接口实现类的完全限定类名，例如：
将包内的映射器接口实现全部注册为映射器，例如：

资源文件标签

配置信息

jdbc.driver=com.mysql.cj.jdbc.Driver
#都可以成功com.mysql.jdbc.Driver
#数据库连接地址 后面的配置 不配置会导致乱码
jdbc.url=jdbc:mysql:///test?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
jdbc.username=root
jdbc.password=123456

配置文件

<!--加载资源文件-->
<properties resource="jdbc.properties"/>

配置类型别名

sqlMapperConfig.xml文件

<!--自定义别名-->
<typeAliases>
    <typeAlias type="cn.corgy.domain1.User" alias="user"/>
</typeAliases>

注意xml自定义的文件配置顺序

userMapper.xml

  <!-- 查询操作-->
  <select id="FindAll" resultType="user">
  select * from User;
</select>

Mybatis相应的API(学习了解)

SqlSessionFactoryBuilder

目标：用来创建sqlsession工厂对象
主要方法budler方法加载核心文件输入流
注意：注意事务的提交事务默认开启需要提交

Mybatis Dao层的实现

传统开发方式

编写UserDao接口

public interface UserDao {
    User findOne() throws IOException;
}

编写实现类

public class UserDaoImpl implements UserDao {
    @Override
    public User findOne() throws IOException {
        //读取文件
        InputStream stream = Resources.getResourceAsStream("sqlMapper.xml");
        //加载文件
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(stream);
        //创建会话对象
        SqlSession sqlSession = sessionFactory.openSession();
        //执行sql
        User user = sqlSession.selectOne("userMapper.FindOne", 2);
        return user;
    }
}

编写测试代码

@Test
public void findOneTest() throws IOException {
    UserDao userDaoIpml = new UserDaoImpl();
    User one = userDaoIpml.findOne();
    System.out.println(one);

}

代理方式开发

自己只写接口 mybatis来给你进行实现简称代理模式

采用 Mybatis 的代理开发方式实现 DAO 层的开发，这种方式是我们后面进入企业的主流。

Mapper 接口开发方法只需要程序员编写Mapper 接口（相当于Dao 接口），由Mybatis 框架根据接口定义创建接口的动态代理对象，代理对象的方法体同上边Dao接口实现类方法。

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

Mapper.xml文件中的namespace与mapper接口的全限定名相同
Mapper接口方法名和Mapper.xml中定义的每个statement的id相同
Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql的parameterType的类型相同
Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同

对应的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="cn.corgy.dao.UserMapper">
    <!-- 查询操作-->
  <select id="findAll" resultType="user">
    	select * from User;
  </select>
    <!--根据id查询-->
  <select id="findOne" resultType="user" parameterType="int">
    	select * from User where id=#{id};
  </select>
    <!--增加操作-->
   <insert id="insertOne" parameterType="user">
        INSERT INTO user value (#{id},#{username},#{password});
   </insert>
    <!--删除操作-->
    <delete id="deleteOne" parameterType="int">
		delete from user where id=#{id}
    </delete>
    <!--修改操作-->
    <update id="updateOne" parameterType="user">
		update user set username=#{username},password=#{password} where id=#{id}
    </update>
</mapper>

测试代码

//查询全部数据
@Test
public void findAll() throws IOException {
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList = mapper.findAll();
    System.out.println(userList);
}

//    根据id查询数据
@Test
public void findOne() throws IOException {
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User one = mapper.findOne(2);
    System.out.println(one);
}

//添加数据
@Test
public void updateOne() throws IOException {
    User user = new User();
    user.setId(1);
    user.setUsername("小程");
    user.setPassword("22222222222222");
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int i = mapper.insertOne(user);
    System.out.println(i);
    //注意事务的提交
    sqlSession.commit();
}

//添加数据
@Test
public void insertOne() throws IOException {
    User user = new User();
    user.setId(1);
    user.setUsername("小程");
    user.setPassword("1111111111112");
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int i = mapper.updateOne(user);
    System.out.println(i);
    //注意事务的提交
    sqlSession.commit();
}

//删除数据
@Test
public void deleteOne() throws IOException {
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int i = mapper.deleteOne(5);
    System.out.println(i);
    //注意事务的提交
    sqlSession.commit();
}

Mybatis映射文件的深入

动态sql

假如只通过部分信息来查询数据因为查询数据的固化可能写死现在通过动态sql语句来进行编写sql的语句可以解决这问题完善查询方式

详情下↓：

if

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="cn.corgy.mapper.UserMapper">
    <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>

</mapper>

测试类

  @Test
    public void findByConditionTest() throws IOException {
        User user = new User();
        user.setId(1);
//        user.setUsername("小程");
        user.setPassword("1111111111112");
        InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> byCondition = userMapper.findByCondition(user);
        System.out.println(byCondition);
        sqlSession.close();
    }

foreach

UserMapper.xml

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

测试类

@Test
public void findByIdTest1() throws IOException {
    List<Integer> ids = new ArrayList<Integer>();
    ids.add(1);
    ids.add(3);
    ids.add(5);
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<User> byId = userMapper.findById(ids);
    System.out.println(byId);
    sqlSession.close();
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CS6m23ir-1605784904238)(C:\Users\wang\AppData\Roaming\Typora\typora-user-images\image-20201114211628338.png)]

sql的抽取

<!--sql的抽取-->
<sql id="select">select * from user</sql>

<select id="findByCondition" parameterType="user" resultType="user">
    <include refid="select"></include><!--sql的引用-->
    <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>

mybatis核心配置文件深入

typeHandlers标签

你可以重写类型处理器或创建你自己的类型处理器来处理不支持的或非标准的类型。具体做法为：实现 org.apache.ibatis.type.TypeHandler 接口，或继承一个很便利的类 org.apache.ibatis.type.BaseTypeHandler，然后可以选择性地将它映射到一个JDBC类型。例如需求：一个Java中的Date数据类型，我想将之存到数据库的时候存成一个1970年至今的毫秒数，取出来时转换成java的Date，即java的Date与数据库的varchar毫秒值之间转换。

开发步骤：

① 定义转换类继承类BaseTypeHandler
② 覆盖4个未实现的方法，其中setNonNullParameter为java程序设置数据到数据库的回调方法，getNullableResult为查询时 mysql的字符串类型转换成 java的Type类型的方法
③ 在MyBatis核心配置文件中进行注册
④ 测试转换是否正确

创建新的表

Create Table

CREATE TABLE `users` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(128) NOT NULL,
  `password` VARCHAR(128) NOT NULL,
  `birthday` VARCHAR(128) 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="cn.corgy.dao.UserMapper">
    <sql id="select">select * from users</sql>
<insert id="save" parameterType="user">
    insert into user value(#{id},#{username},#{password},#{birthday})
</insert>
    
</mapper>

测试类

 @Test
    public void savetest() throws IOException {
        User user = new User();
        user.setUsername("小盆又");
        user.setId(1);
        user.setPassword("2222222");
        Date birthday = new Date();
        user.setBirthday(birthday);
        InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.save(user);
        sqlSession.commit();
    }
}

结果：报错因为date类型的数据没有上传到数据库保存出错需要自定义转化器

自定义转化处理器

package cn.corgy.handler;


import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;


public class DataHandler extends BaseTypeHandler<Date> {

    //将java类型转化成数据库需要的类型
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i,Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        System.out.println("111111111111111111111111111111111111111111"+time);
        preparedStatement.setLong(i,time);
    }

    //数据库类型转化为java类型
    // String s 是数据库字段的名称
    //ResultSet resultSet 查询的结果集
    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        long aLong = resultSet.getLong(s);
        return new Date(aLong);
    }

    //数据库类型转化为java类型
    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        long aLong = resultSet.getLong(i);
        return new Date(aLong);
    }

    //数据库类型转化为java类型
    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long aLong = callableStatement.getLong(i);
        return new Date(aLong);
    }
}

配置核心配置文件

<!--定义类型处理器-->
<typeHandlers>
    <typeHandler handler="cn.corgy.handler.DateTypeHandler"/>
</typeHandlers>

插入操作测试类

@Test
public void hello() throws IOException {
    User user = new User();
    user.setUsername("小盆");
    user.setId(1);
    user.setPassword("222111111122");
    user.setBirthday(new Date());
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    mapper.save(user);
    sqlSession.commit();
}

查询操作测试类

    @Test
    public void test2() throws IOException {
        User user = new User();
        user.setUsername("小盆");
        user.setId(1);
        user.setPassword("222111111122");
//        user.setBirthday(new Date());
        InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.find(user);
        System.out.println(users);
        sqlSession.commit();
    }

注意Date的包 sql有 util也有

plugin标签

注意导入标签

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>3.7.5</version>
</dependency>
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>0.9.1</version>
</dependency>

配置设置文件

<!--分页助手插件-->
<plugins>
    <plugin interceptor="com.github.pagehelper.PageHelper">
        <property name="dialect" value="mysql"/>
    </plugin>
</plugins>

测试类

@Test
public void test3() throws IOException {
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    //设置分页的相关参数
    PageHelper.startPage(2, 3);

    List<User> users = mapper.findAll();
    for (User it :
            users) {
        System.out.println(it);
    }
    sqlSession.commit();
}

获取分页的相关参数

@Test
public void test3() throws IOException {
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    //设置分页的相关参数
    PageHelper.startPage(2, 3);

    List<User> users = mapper.findAll();
    for (User it :
            users) {
        System.out.println(it);
    }

    //获取与分页相关的参数
    PageInfo<User> userPageInfo = new PageInfo<User>(users);
    //获取当前页
    int pageNum = userPageInfo.getPageNum();
    System.out.println("获取当前页" + pageNum);
    //每页显示条数
    int pageSize = userPageInfo.getPageSize();
    System.out.println("每页显示条数" + pageSize);
    //总条数
    long total = userPageInfo.getTotal();
    System.out.println("总条数" + total);
    //获取总页数
    int pages = userPageInfo.getPages();
    System.out.println("获取总页数" + pages);
    //上一页
    int prePage = userPageInfo.getPrePage();
    System.out.println("上一页" + prePage);
    //下一页
    int nextPage = userPageInfo.getNextPage();
    System.out.println("下一页" + nextPage);
    //是否是第一页
    boolean isFirstPage = userPageInfo.isIsFirstPage();
    System.out.println("是否是第一页"+isFirstPage);
    //是否是最后一页
    boolean isLastPage = userPageInfo.isIsLastPage();
    System.out.println("是否是最后一页"+isLastPage);
    //获取第一页
    int firstPage = userPageInfo.getFirstPage();
    System.out.println("获取第一页" + firstPage);
    //获取最后一页
    int lastPage = userPageInfo.getLastPage();
    System.out.println("获取最后一页" + lastPage);
    //获取全部信息
    List<User> list = userPageInfo.getList();
    System.out.println("获取全部信息" + Arrays.toString(list.toArray()));
}

多表操作

一对一

映射文件

<?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="cn.corgy.mapper.OrderMapper">
    <resultMap id="orderMap" type="order">
        <!-- 指定字段与实体属性的映射关系
        column 字段名称
        -->
        <id column="oid" property="id"/>
        <result column="order" property="order"/>
        <result column="total" property="total"/>
        <!-- 一种封装方式-->
        <!-- <result column="uid" property="user.id"/>-->
        <!-- <result column="username" property="user.username"/>-->
        <!-- <result column="password" property="user.password"/>-->
        <!--
        property: 当前实体的属性名称
        javaType: 当前实体<Order>中的属性<User>类型
        -->
        <association property="user" javaType="user">
            <id column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
        </association>
    </resultMap>

    <select id="findAll" resultMap="orderMap">
    SELECT * ,o.id oid FROM orders o,USER u WHERE o.uid=u.id
</select>
</mapper>

测试类

//一对一索引
@Test
public void findAll() throws IOException {
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = build.openSession(true);
    OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    List<User> all = mapper.findAll();
    System.out.println(all);
    sqlSession.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="cn.corgy.mapper.UserMapper">
    <resultMap id="UserMap" type="user">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <!--配置集合信息-->
        <collection property="orderList" ofType="Order">
            <id column="oid" property="id"/>
            <result column="orderTime" property="orderTime"/>
            <result column="total" property="total"/>
        </collection>
    </resultMap>
    <select id="findByUser" resultMap="UserMap">
select *,o.id oid from user u ,orders o where o.uid=u.id ;
</select>

</mapper>

测试类

//多对一查询
@Test
public void findByuser() throws IOException {
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = build.openSession(true);
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<Order> users = mapper.findByUser();
    System.out.println(users);
    sqlSession.close();
}

多对多

多对多sql模型

#用户表
CREATE TABLE `user` (
  `id` int(20) NOT NULL,
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`)
)
# 中间表
CREATE TABLE `user_relate_role` (
  `role_id` int(20) NOT NULL,
  `user_id` int(20) NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`)
)
#职位表
CREATE TABLE `roles_d` (
  `role_id` int(20) NOT NULL,
  `role_name` varchar(20) DEFAULT NULL,
  `role_note` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`role_id`)
)

映射文件

<?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="cn.corgy.mapper.UserMapper">
    <resultMap id="UserMap" type="user">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <!--配置集合信息-->
        <collection property="orderList" ofType="Order">
            <id column="oid" property="id"/>
            <result column="orderTime" property="orderTime"/>
            <result column="total" property="total"/>
        </collection>
    </resultMap>
    <select id="findByUser" resultMap="UserMap">
select *,o.id oid from user u ,orders o where o.uid=u.id ;
</select>

    <resultMap id="roleMap" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <collection property="roleList" ofType="cn.corgy.domain.Role">
            <id column="role_id" property="role_id"/>
            <result column="role_name" property="role_name"/>
            <result column="role_note" property="role_note"/>
        </collection>
    </resultMap>

    <select id="findByRole" resultMap="roleMap">
            SELECT * FROM user u,user_relate_role ur,roles_d r WHERE u.id=ur.user_id AND r.role_id=ur.role_id
    </select>

</mapper>

测试类

//多对多
@Test
public void findByRole() throws IOException{
    InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
    SqlSession sqlSession = build.openSession(true);
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<Role> byRole = mapper.findByRole();
    System.out.println(byRole);
}

以上对象类（toString get set省略）

private int id;
private String username;
private String password;
//用户的订单
private List<Order> orderList;

//用户的职位
private List<Role> roleList;

}

package cn.corgy.domain;

import java.util.Date;

public class Order {
private int id;
private Date orderTime;
private double total;

//订单属于哪个用户
private User user;

}

package cn.corgy.domain;

public class Role {
 private int role_id;
 private String role_name;
 private String role_note;
}

注解开发

@Insert：实现新增

@Update：实现更新

@Delete：实现删除

@Select：实现查询

@Result：实现结果集封装

@Results：可以与@Result 一起使用，封装多个结果集

@One：实现一对一结果集封装

@Many：实现一对多结果集封装

配置文件：

<?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="jdbc.properties"/>

<!--    &lt;!&ndash;定义别名&ndash;&gt;-->
<!--    <typeAliases>-->
<!--        <typeAlias type="cn.corgy.domain.User" alias="user"/>-->
<!--        <typeAlias type="cn.corgy.domain.Order" alias="order"/>-->
<!--        <typeAlias type="cn.corgy.domain.Role" alias="role"/>-->
<!--    </typeAliases>-->

    <!--配置环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <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>

    <!-- 加载注解映射关系-->
    <mappers>
        <package name="cn.corgy.mapper"/>    <!--相当于扫包-->
    </mappers>
</configuration>

Mapper接口文件：

package cn.corgy.mapper;

import cn.corgy.domain.Order;
import cn.corgy.domain.Role;
import cn.corgy.domain.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper {

    //基础查询
    @Insert("insert into user value(null,#{username},#{password})")
    void save(User user);

    @Update("update user set username=#{username}, password=#{password} where id=#{id} ")
    void update(User user);

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

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

    @Select("select * from orders where uid =#{id}")
    Order findByUid(int id);


    @Select("select * from user")
    List<User> findAll();

    //一对一查询
//    @Select("select * ,o.id oid from orders o,user u where o.uid=u.id ")
//    @Results({
//            @Result(column = "oid", property = "id"),
//            @Result(column = "orderTime", property = "orderTime"),
//            @Result(column = "total", property = "total"),
//             @Result(column = "uid",property = "user.id"),
//            @Result(column = "username",property = "user.username"),
//            @Result(column = "password",property = "user.password"),
//    })
//    List<Order> find11();

    @Select("select * from orders")
    @Results({
            @Result(column = "oid", property = "id"),
            @Result(column = "orderTime", property = "orderTime"),
            @Result(column = "total", property = "total"),
            @Result(
                    property = "user",//要封装的属性名称
                    column = "uid",
                    javaType = User.class,
                    one = @One(select = "cn.corgy.mapper.UserMapper.findById")
            )

    })
    List<Order> find11();

    //一对多查询
    @Select("select * from user")
    @Results({
            @Result(id = true, 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 = "cn.corgy.mapper.UserMapper.findByUid")
            )
    })
    List<User> find12();

    //多多表条件
    @Select("select * from roles_d r,user_relate_role ur where ur.role_id=r.role_id and ur.user_id=#{id}")
    List<Role> findByRoleid(int id);

    //多对多查询
    @Select("select * from user")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "username", property = "username"),
            @Result(column = "password", property = "password"),
            @Result(
                    column = "id",
                    property = "roleList",
                    javaType = List.class,
                    many = @Many(select = "cn.corgy.mapper.UserMapper.findByRoleid")
            )
    })
    List<User> find22();
}

测试文件：

package cn.corgy;

import cn.corgy.domain.Order;
import cn.corgy.domain.User;
import cn.corgy.mapper.UserMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestDemo {
    private UserMapper mapper = null;
    SqlSession sqlSession = null;

    @Before
    public void chi() throws IOException {
        InputStream stream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        sqlSession = sqlSessionFactory.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @After
    public void close() {
        sqlSession.close();
    }

    @Test
    public void Test1() {
        User user = new User();
        user.setUsername("小王");
        user.setPassword("12");
        mapper.save(user);
    }

    @Test
    public void Test2() {
        User user = new User();
        user.setId(6);
        user.setUsername("小神");
        user.setPassword("123");
        mapper.update(user);
    }

    @Test
    public void Test3() {
        mapper.delete(6);
    }

    @Test
    public void Test4() {
        User byId = mapper.findById(1);
        System.out.println(byId);
    }

    @Test
    public void Test5() {
        List<User> all = mapper.findAll();
        System.out.println(all);
    }

    //复杂查询
    @Test
    public void Test6() {
        List<Order> orderList = mapper.find11();
        System.out.println(orderList);
    }

    @Test
    public void Test7() {
        List<User> userList = mapper.find12();
        System.out.println(userList);
    }

    @Test
    public void Test8() {
        List<User> userList = mapper.find22();
        System.out.println(userList);
    }
}

Corgy.

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
学习mybatis 整理大全

Mybatis学习记录开发步骤（以User代表）添加mybatis的坐标创建user数据表编写User数据表类编写核心映射文件UserMapper.xml编写核心文件SqlMapConfig.xml编写测试类入门配置位置导包 <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId>
复制链接

扫一扫