Mybatis

最新推荐文章于 2023-08-14 23:29:59 发布

Lpepsi

最新推荐文章于 2023-08-14 23:29:59 发布

阅读量146

点赞数

本文链接：https://blog.csdn.net/qq_40866897/article/details/91792688

版权

之前就学了mybatis,但是没有做笔记所以有些东西过了一段时间就记不清了，所以重新过了一遍mybatis基础的知识并记录，该博客不适合初学mybatis的朋友

Mybatis

环境搭建

pom.xml

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.1</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.15</version>
    </dependency>
    <!--log4j-->
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
    <!--单元测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

Config.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">

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

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">
<!--mybatis主配置文件-->
<configuration>
    <!--配置环境-->
    <environments default="MySQL">
        <!--配置MySQL的环境-->
        <environment id="MySQL">
            <!--配置事务的类型-->
            <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?serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="1001101"/>
            </dataSource>
        </environment>
    </environments>
    <!--指定映射的位置-->
    <mappers>
        <mapper resource="mapper.UserMapper.xml"/>
    </mappers>
</configuration>

mybatis基本的使用步骤

//读取配置文件
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
//创建SqlSessionFactory工厂
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(inputStream);
//生产SqlSession对象
SqlSession sqlSession = factory.openSession();
//使用sqlsession创建Dao接口的代理对象
UserDao userDao =  sqlSession.getMapper(UserDao.class);
//使用代理对象执行方法
List<User> userList = userDao.findAll();
for (User user : userList) {
    System.out.println(user);
}
//释放资源
sqlSession.close();
inputStream.close();

读取文件的两种常用方法：

1.使用类加载器，只能读取类路径的配置文件

2.使用ServletContext对象的getRealPath()

CRUD操作

事先的准备

private InputStream inputStream;
private SqlSession sqlSession;
private UserDao userDao;

@Before//test执行前执行
public void init() throws Exception {
    //读取配置文件
    inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
    //创建SqlSessionFactory工厂
    SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
    SqlSessionFactory factory = builder.build(inputStream);
    //生产SqlSession对象
    sqlSession = factory.openSession();
    //使用sqlsession创建Dao接口的代理对象
    userDao =  sqlSession.getMapper(UserDao.class);
}

@After//test执行后执行
public void destory() throws IOException {
    //提交事务
    sqlSession.commit();
    //释放资源
    sqlSession.close();
    inputStream.close();
}

增加

<!--saveUser-->
<insert id="saveUser" parameterType="com.lb.pojo.User">
    insert into user (username,address,sex,birthday)values (#{username},#{address},#{sex},#{birthday})
</insert>

@Test
public void TestSave(){
    User user = new User();
    user.setUsername("mybatis");
    user.setAddress("天津");
    user.setSex("男");
    user.setBirthday(new Date());
    userDao.saveUser(user);
}

获取插入数据的id值

<!--saveUser-->
<insert id="saveUser" parameterType="com.lb.pojo.User">
    <selectKey keyProperty="id" keyColumn="id" order="AFTER" resultType="int">
        select last_insert_()
    </selectKey>
    insert into user (username,address,sex,birthday)values (#{username},#{address},#{sex},#{birthday})
</insert>

更新

<update id="updateUser" parameterType="com.lb.pojo.User">
    update user set username=#{username} where id = #{id}
</update>

/**
 * 测试更新
 */
@Test
public void TestUpdate() throws IOException {
    User user = new User();
    user.setId(46);
    user.setUsername("林北柠檬");
    userDao.updateUser(user);
}

删除

<!--delete-->
<delete id="deleteUser" parameterType="int">
    delete from user where id = #{id}
</delete>

/**
 * 测试删除
 */
@Test
public void TestDelete() throws IOException {
    userDao.deleteUser(49);
}

模糊查询

<select id="findByName" parameterType="string" resultType="com.lb.pojo.User">
    select *from user where username = '%${value}%'
</select>

/**
 * 测试模糊查找
 */
@Test
public void TestfindByName() throws IOException {
    List<User> userList = userDao.findByName("王");
    for (User user : userList) {
        System.out.println(user);
    }
}

开发规范

1.在mapper.xml中namespace等于Dao接口命名

2.Dao接口中的方法名和mapper.xml中的id一致

3.Dao接口中的方法参数类型和mapper.xml中的parameterType指定的类型一致

4.Dao接口中的方法返回值类型和mapper.xml中的resultType指定的类型一致

SqlMapConfig-properties

把数据库的配置提取出来

mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8
mysql.username=root
mysql.password=1001101

在SqlMapConfig.xml通过properties属性导入文件

<properties resource="mysql.properties"/>

<dataSource type="POOLED">
    <!--配置数据库的基本信息-->
    <property name="driver" value="${mysql.driver}"/>
    <property name="url" value="${mysql.url}"/>
    <property name="username" value="${mysql.username}"/>
    <property name="password" value="${mysql.password}"/>
</dataSource>

SqlMapConfig-typeAliases别名设置

之前在mapper.xml中parameterType，resultType中如果类型是实体类，需要写全限定类名，很繁琐，可以通过typeAliases来设置别名

单个别名定义

<!--定义别名-->
<typeAliases>
    <!--单个别名-->
    <typeAlias type="com.lb.pojo.User" alias="user"/>
</typeAliases>

<select id="findByid" parameterType="int" resultType="user">
    select *from user where id = #{id}
</select>

批量定义别名

mybatis会自动扫描包中的实体类，并自动创建别名，别名为实体类的类名

<typeAliases>
    <!--单个别名-->
    <!--<typeAlias type="com.lb.pojo.User" alias="user"/>-->
    <!--批量定义别名-->
    <package name="com.lb.pojo"/>
</typeAliases>

SqlMapConfig-mapper映射配置

单个映射文件的加载

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

使用实体类包装对象作为查询条件

当传入的查询条件复杂时，我们可以定义一个实体类将这些复杂的查询条件包装起来，然后将这个实体类作为parameterType

在Employee中定义了员工和工资

public class Employee implements Serializable {
    private double salary;
    private User user;

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

mapper接口

List<User> findByEmployee(Employee employee);

查询时这样定义，要使用包装类中对象的属性时，使用对象名.属性名即可

    <select id="findByEmployee" parameterType="employee" resultType="user">
        select *from user where username like #{user.username}
    </select>

实体类属性名和数据库字段名不匹配的解决方法

1.在sql语句中使用别名

2.使用resultmap映射属性名和字段名的关系

<!--resultMap-->
<resultMap id="UserMap" type="com.lb.pojo.User">
    <!--主键-->
    <id property="userId" column="user_id"/>
    <!--其他属性-->
    <result property="userName" column="user_name"/>
    <result property="userSex" column="user_sex"/>
    <result property="userAddress" column="user_address"/>
    <result property="userBirthday" column="user_birthday"/>
</resultMap>

将之前select用到resultType的地方都改为resultMap

<select id="findAll" resultMap="UserMap">
    select * from user
</select>

3.在通常情况下，实体类中的属性一般是采用驼峰命名命名的，而数据库中表的字段则用下划线区分字母。在这种情况下，Mybatis提供了一个全局属性mapUnderscoreToCamelCase来解决两者名字不一致的问题。在sqlMapConfig.xml中进行设置

<settings>
    <!--开启驼峰命名法-->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

select语句中正常使用resultType即可

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

动态sql

<select id="findByCondition" parameterType="user" resultMap="UserMap">
     select * from user where 1=1
     //test里填判断条件
     <if test="userName != null and userName != ''">
       and  user_name  like #{userName}
     </if>
</select>

无userName

public void TestfindByCondition() throws IOException {
    User user = new User();
    user.setUserName("");
    List<User> userList = userMapper.findByCondition(user);
    for (User user1 : userList) {
        System.out.println(user1);
    }
}
//查询语句为：select * from user where 1=1

有userName

public void TestfindByCondition() throws IOException {
    User user = new User();
    user.setUserName("%王%");
    List<User> userList = userMapper.findByCondition(user);
    for (User user1 : userList) {
        System.out.println(user1);
    }
}
//查询语句为：select * from user where 1=1 and user_name like '%王%'

where

简化sql语句中where条件判断的书写

<select id="findByCondition" parameterType="user" resultMap="UserMap">
     select * from user
     //与上面相比少了 where 1=1
     <where>
         <if test="userName != null and userName != ''">
             and  user_name  like #{userName}
         </if>
     </where>
</select>

foreach

用于遍历集合

collection：代表要遍历的集合元素
open：代表语句的开始部分
close：代表结束部分
item：代表遍历集合的每个元素，生成的变量名
sperator：分隔符

public class Employee implements Serializable {
    private double salary;
    private User user;
    private List<Integer> idList;

    public List<Integer> getIdList() {
        return idList;
    }

    public void setIdList(List<Integer> idList) {
        this.idList = idList;
    }

<select id="findByForEach" parameterType="employee" resultMap="UserMap">
    select * from user
    <where>
        <if test="idList != null and idList.size()>0">
            <foreach collection="idList" open=" and user_id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </if>
    </where>
</select>

一对一

<!--column表示数据库字段名,property表示映射到实体的属性-->
<resultMap id="accountUser" type="account">
    <id property="id" column="aid"/>
    <result property="uid" column="uid"/>
    <result property="money" column="money"/>
    <!--一对一的关系映射-->
    <association property="user" javaType="com.lb.pojo.User">
        <id property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="userSex" column="user_sex"/>
        <result property="userAddress" column="user_address"/>
        <result property="userBirthday" column="user_birthday"/>
    </association>
</resultMap>
<select id="findAll" resultMap="accountUser">
    select u.*,a.id as aid,a.uid,a.money from account a,user u where u.user_id = a.uid;
</select>

一对多

主表实体应该包含从表实体的集合引用

public class User implements Serializable {
    private int userId;
    private String userName;
    private Date userBirthday;
    private String userSex;
    private String userAddress;
    //账户的集合引用
    private List<Account> accounts;

    public List<Account> getAccounts() {
        return accounts;
    }

    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }

<resultMap id="userAccount" type="user">
    <!--主键-->
    <id property="userId" column="user_id"/>
    <!--其他属性-->
    <result property="userName" column="user_name"/>
    <result property="userSex" column="user_sex"/>
    <result property="userAddress" column="user_address"/>
    <result property="userBirthday" column="user_birthday"/>
    <collection property="accounts" ofType="account">
        <id property="id" column="id"/>
        <result property="uid" column="uid"/>
        <result property="money" column="money"/>
    </collection>
</resultMap>
<!--sql语句-->
<select id="findAccountUser" resultMap="userAccount">
    SELECT * FROM `user` u LEFT OUTER JOIN account a ON u.user_id = a.UID
</select>

多对多

<resultMap id="roleUserMap" type="role">
    <id property="roleId" column="rid"/>
    <result property="roleName" column="role_name"/>
    <result property="roleDesc" column="role_desc"/>
    <collection property="users" ofType="user">
        <id property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="userSex" column="user_sex"/>
        <result property="userAddress" column="user_address"/>
        <result property="userBirthday" column="user_birthday"/>
    </collection>
</resultMap>
<select id="findAll" resultMap="roleUserMap">
    select u.*,r.role_id as rid,r.role_name,r.role_desc from role r
    left OUTER JOIN user_role ur on r.role_id =ur.RID
    left OUTER JOIN `user` u ON u.user_id = ur.UID
</select>

注解版mybatis

注解版的mybatis不用写xxxmapper.xml，只需要写SqlMapConfig.xml，其他的都与xml版的使用相同

CRUD共有四个注解

@select
@update
@delete
@insert

public interface UserMapper {

    /**
     * 查询所有用户
     * @return
     */
    @Select("select *from user")
    List<User> findAll();

    /**
     * 保存用户
     * @param user
     */
    @Insert(" insert into user (user_name,user_address,user_sex,user_birthday)values (#{userName},#{userAddress},#{userSex},#{userBirthday})")
    void saveUser(User user);

    /**
     * 更新用户
     * @param user
     */
    @Update("update user set user_name=#{userName} where user_id = #{userId}")
    void updateUser(User user);
    
    /**
     * 删除用户
     * 
     */
    @Delete("delete from user where user_id = #{id}")
    void deleteUser(int user_id);
}

注解的一对一

@Select("select *from account")
@Results({
        @Result(id = true,column = "id",property = "id"),
        @Result(column = "uid",property = "uid"),
        @Result(column = "money",property = "money"),
        //通过account的uid查找user表的用户
        @Result(column = "uid",property = "user",
            one = @One(
                    select = "com.lb.dao.UserMapper.findById",
                    //一对一一般使用立即加载
                    fetchType = FetchType.EAGER
            )
        )
})
List<Account> findAll();
//-----------------
        @Select("select *from user where user_id = #{id}")
    User findById();

注解的一对多

@Select("select * from user")
@Results({
        @Result(id = true,column = "user_id",property = "userId"),
        @Result(column = "user_sex",property = "userSex"),
        @Result(column = "user_name",property = "userName"),
        @Result(column = "user_address",property = "userAddress"),
        @Result(column = "user_birthday",property = "userBirthday"),
        @Result(column = "user_id",property = "accounts",
            many = @Many(
                    select = "com.lb.dao.AccountMapper.findAccountByUid",
                    //一对多一般使用延迟加载
                    fetchType = FetchType.LAZY
            )

        )
})
List<User> findAllByAccount();
    //-------------
        @Select("select * from account where uid = #{userId}")
    List<Account> findAccountByUid(int userId);

测试代码在我的GitHub