mybatis

灬繁花落雨灬

已于 2022-07-18 15:51:46 修改

阅读量268

点赞数

分类专栏： javaweb 文章标签： java java-ee

于 2021-11-13 21:09:19 首次发布

本文链接：https://blog.csdn.net/qq_42426993/article/details/121305511

版权

javaweb 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

1.xml方式配置mybatis

目录结构：

1.1 porm.xml引入依赖

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.5</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.11</version>
    </dependency>
</dependencies>

1.2 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">
                <!--配置连接数据库的4个基本信息-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/ssm?serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <!--指定映射配置文件的位置，映射配置文件指的是每个dao独立的配置文件-->
    <mappers>
        <mapper resource="cn/zh/dao/IUserDao.xml"/>
    </mappers>
</configuration>

1.3 IUserDao.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">
 <!-- namespace:用来绑定dao接口的 -->
<mapper namespace="cn.zh.dao.IUSerDao">
    <!--配置查询所有-->
    <select id="findAll" resultType="cn.zh.domain.User">
        select * from user;
    </select>
</mapper>

1.4测试

public class MybatisTest {
    /**
     * 入门案例
     * @param args
     */
    public static void main(String[] args) throws Exception {
        //1.读取配置文件
        InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
        //2.创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3.使用工厂生产SqlSession对象
        SqlSession session = factory.openSession();
        //4.使用SqlSession创建Dao接口的代理对象
        IUSerDao userDao = session.getMapper(IUSerDao.class);
        //5.使用代理对象执行方法
        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println(user);
        }
        //6.释放资源
        session.close();
        in.close();
    }
}

2.annotation配置mybatis

只讨论与xml不同的地方

2.1 SqlMapConfig.xml

<!--指定映射配置文件的位置，映射配置文件指的是每个dao独立的配置文件
        如果是用注释来配置的话，此处应该使用class属性指定被注解的dao全限定类名
    -->
    <mappers>
        <mapper class="cn.zh.dao.IUSerDao"/>
    </mappers>

2.2 IUserDao.java

public interface IUSerDao {
    /**
     * 查询所有的操作
     * @return
     */
    @Select("select * from user")
    List<User> findAll();
}

3.xml方式进行CRUD操作

目录结构

3.1 jdbcConfig.properties

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

3.2 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>
    <!--引用外部配置文件-->
    <!--配置properties-->
    <properties resource="jdbcConfig.properties">
<!--        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ssm?serverTimezone=GMT%2B8"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>-->
    </properties>
    
    <!--使用typeAliases配置别名，它只能配置domain中类的别名-->
    <typeAliases>
        <!--typeAlias用于配置别名，type属性指定的是实体类全限定类名。alias属性指定别名，当指定了别名就不区分大小写了-->
        <!--<typeAlias type="cn.zh.domain.User" alias="user"></typeAlias>-->

        <!--用于指定要配置别名的包，当指定之后，该包下的实体类都会注册别名，并且类名就是别名，不再区分大小写-->
        <package name="cn.zh.domain"/>
    </typeAliases>
    
    <!--配置环境-->
    <environments default="mysql">
        <!--配置MySQL环境-->
        <environment id="mysql">
            <!--配置事务-->
            <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>

<!--    &lt;!&ndash;配置环境&ndash;&gt;
    <environments default="mysql">
        &lt;!&ndash;配置MySQL环境&ndash;&gt;
        <environment id="mysql">
            &lt;!&ndash;配置事务&ndash;&gt;
            <transactionManager type="JDBC"></transactionManager>
            &lt;!&ndash;配置连接池&ndash;&gt;
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/ssm?serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>-->

    <!--配置映射文件的位置-->
    <mappers>
        <!--<mapper resource="cn/zh/dao/IUserDao.xml"></mapper>-->

        <!--package标签用于指定dao接口所在的包，当制定了之后就不需要再写mapper以及resource或者class-->
        <package name="cn.zh.dao"/>
    </mappers>
</configuration>

3.3 User,QueryVo,DoubleUser

user

public class User implements Serializable {
    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;
}

DoubleUser

public class DoubleUser implements Serializable {
    private Integer userId;
    private String userName;
    private String userAddress;
    private String userSex;
    private Date userBirthday;
}

QueryVo

public class QueryVo {
    private User user;
    private List<Integer> ids;
}

3.4 IUserDao.java

public interface IUserDao {
    /**
     * 查询所有的用户
     * @return
     */
    List<User> findAll();

    /**
     * 保存用户
     * @param user
     */
    void saveUser(User user);

    /**
     * 更新用户
     * @param user
     */
    void updateUser(User user);

    /**
     * 根据用户的id删除用户
     * @param userId
     */
    void deleteUser(Integer userId);

    /**
     * 根据id查询用户信息
     * @param userId
     * @return
     */
    User findById(Integer userId);

    /**
     * 根据名称模糊查询
     * @param username
     * @return
     */
    List<User> findByName(String username);

    /**
     * 查询总用户的数量
     * @return
     */
    int findTotal();

    /**
     * 根据QueryVo查询
     * @param vo
     * @return
     */
    List<User> findUserByVo(QueryVo vo);

    /**
     * 数据库和Bean不匹配时查询所有
     */
    List<DoubleUser> findAllUser();
	
	/**
     * 根据传入参数条件查询
     * @param user  查询的条件
     * @return
     */
    List<User> findUserByCondition(User user);

    /**
     * 根据in查询
     * @param vo
     * @return
     */
    List<User> findUserByInIds(QueryVo vo);
}

3.5 IUserDao.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.zh.dao.IUserDao">
    <!--查询所有用户-->
    <select id="findAll" resultType="cn.zh.domain.User">
        select * from user;
    </select>

    <!--保存用户-->
    <insert id="saveUser" parameterType="cn.zh.domain.User">
        <!-- 获取插入操作后，获取插入数据的id
            keyProperty属性类中的名称
            keyColumn对应表中的属性
            order取值为AFTER代表插入后的行为
            resultType表示返回值的类型
         -->
        <selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
            select last_insert_id();
        </selectKey>
        insert into user(username,address,sex,birthday) values(#{username},#{address},#{sex},#{birthday});
    </insert>

    <!--更新用户-->
    <update id="updateUser" parameterType="cn.zh.domain.User">
        update user set username=#{username},address=#{address},sex=#{sex},birthday=#{birthday} where id=#{id};
    </update>

    <!--删除用户-->
    <delete id="deleteUser" parameterType="java.lang.Integer">
        delete from user where id = #{id};
    </delete>

    <!--根据id查询用户-->
    <select id="findById" parameterType="INT" resultType="cn.zh.domain.User">
        select * from user where id =#{id};
    </select>

    <!--根据名称模糊查询-->
    <select id="findByName" parameterType="String" resultType="cn.zh.domain.User">
        select * from user where username like #{username};
        <!-- select * from user where username like '%${value}%' （一般不使用这个）-->
    </select>
    
    <!--获取用户的总记录条数-->
    <select id="findTotal" resultType="java.lang.Integer">
        select count(id) from user;
    </select>

    <!--根据QueryVo的条件查询用户-->
    <select id="findUserByVo" parameterType="cn.zh.domain.QueryVo" resultType="cn.zh.domain.User">
        select * from user where username like #{user.username};
    </select>

    <!--数据库和Bean不匹配时查询所有-->
    <select id="findAllUser" resultType="cn.zh.domain.DoubleUser">
        select id as userId,username as userName,address as userAddress,sex as userSex,birthday as userBirthday from user;
    </select>

    <!--配置查询结果的列名和实体类的属性名的对应关系;
        id是唯一的一个标志，可以随意写;
        查询语句要使用resultMap
    -->
    <resultMap id="userMap" type="cn.zh.domain.DoubleUser">
        <!--主键字段的对应-->
        <id property="userId" column="id"></id>
        <!--非主键字段的对应-->
        <result property="userName" column="username"></result>
        <result property="userAddress" column="address"></result>
        <result property="userSex" column="sex"></result>
        <result property="userBirthday" column="birthday"></result>
    </resultMap>

	<!--根据条件查询-->
    <select id="findUserByCondition" parameterType="user" resultType="user">
        select * from user
        <where>
            <if test="username!=null">
                username=#{username};
            </if>
            <if test="sex!=null">
                sex=#{sex};
            </if>
        </where>
    </select>

    <!--根据queryVo中的id集合实现查询用户列表-->
    <select id="findUserByInIds" parameterType="QueryVo" resultType="user">
        select * from user
        <where>
            <if test="ids!=null and ids.size()>0">
                <foreach collection="ids" open="and id in(" close=")" item="uid" separator=",">
                    #{uid}
                </foreach>
            </if>
        </where>
    </select>
    
</mapper>

4.xml方式进行一对多查询

4.1 User,Account,AccountUser

User

public class User implements Serializable {
    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;
}

Account

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    private User user;
}

AccountUser

public class AccountUser extends Account {
    private String username;
    private String address;
}

4.2 IUserDao.java和IAccountDao.java

IUserDao.java

public interface IUserDao {
    /**
     * 查询所有的用户,同时获取用户的所有账户信息
     * @return
     */
    List<User> findAll();

    /**
     * 根据id查询用户信息
     * @param userId
     * @return
     */
    User findById(Integer userId);
}

IAccountDao.java

public interface IAccountDao {
    /**
     * 查询所有的账户
     * @return
     */
    List<Account> findAll();

    /**
     * 查找所有账户，并且带有用户名称和地址信息
     * @return
     */
    List<AccountUser> findAllAccount();
}

4.3 IUserDao.xml和IAccountDao.xml

IUserDao.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.zh.dao.IUserDao">
    <!--定义user的resultMap-->
    <resultMap id="userAccountMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <result property="birthday" column="birthday"></result>
        <!--配置user中的accounts集合的映射-->
        <collection property="accounts" ofType="account">
            <id property="id" column="aid"></id>
            <result column="uid" property="uid"></result>
            <result column="money" property="money"></result>
        </collection>
    </resultMap>
    <!--查询所有用户-->
    <select id="findAll" resultMap="userAccountMap">
        select u.*,a.id as aid,a.uid,a.money from user u left outer join account a on u.id = a.uid
    </select>

    <!--根据id查询用户-->
    <select id="findById" parameterType="INT" resultType="cn.zh.domain.User">
        select * from user where id =#{id};
    </select>

</mapper>

IAccountDao.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.zh.dao.IAccountDao">
    <!--定义封装account和user的resultMap-->
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="id"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!--一对一的关系映射：配置封装user的内容-->
        <association property="user" column="uid" javaType="cn.zh.domain.User">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="password" column="password"></result>
            <result property="sex" column="sex"></result>
            <result property="address" column="address"></result>
        </association>
    </resultMap>

    <!--查询所有用户-->
    <select id="findAll" resultMap="accountUserMap">
        select a.*,u.username,u.address from user u,account a where u.id=a.uid
    </select>

    <!--查询所有同时包含有用户名和地址信息-->
    <select id="findAllAccount" resultType="cn.zh.domain.AccountUser">
        select a.*,u.username,u.address from user u,account a where u.id=a.uid
    </select>
</mapper>

5.xml方式进行多对多查询

5.1 Role和User

Role

public class Role implements Serializable {
    private Integer roleId;
    private String roleName;
    private String roleDesc;
    private List<User> users;
}

User

public class User implements Serializable {
    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;
}

5.2 IRoleDao.java和IUserDao.java

IRoleDao.java

public interface IRoleDao {
    /**
     * 查询所有角色
     * @return
     */
    List<Role> findAll();
}

IUserDao.java

public interface IUserDao {
    /**
     * 查询所有的用户,同时获取用户的所有账户信息
     * @return
     */
    List<User> findAll();

    /**
     * 根据id查询用户信息
     * @param userId
     * @return
     */
    User findById(Integer userId);
}

5.3 IUserDao.xml和 IRoleDao.xml

IUserDao.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.zh.dao.IUserDao">
    <!--定义user的resultMap-->
    <resultMap id="userAccountMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <result property="birthday" column="birthday"></result>
    </resultMap>
    <!--查询所有用户-->
    <select id="findAll" resultMap="userAccountMap">
       select  * from user
    </select>

    <!--根据id查询用户-->
    <select id="findById" parameterType="INT" resultType="cn.zh.domain.User">
        select * from user where id =#{id};
    </select>

</mapper>

IRoleDao.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.zh.dao.IRoleDao">
    <!--定义role表的roleMap-->
    <resultMap id="roleMap" type="role">
        <id property="roleId" column="rid"></id>
        <result property="roleName" column="role_name"></result>
        <result property="roleDesc" column="role_desc"></result>
        <!--
			ofType: 一对多场景
			javaType: 一对一场景
		-->
        <collection property="users" ofType="user">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="sex" column="sex"></result>
            <result property="address" column="address"></result>
            <result property="birthday" column="birthday"></result>
        </collection>
    </resultMap>

    <!--查询所有-->
    <select id="findAll" resultMap="roleMap">
        select u.*,r.id as rid,r.role_name,r.role_desc from role r
        left outer join user_role ur on r.id = ur.rid
        left outer join user u on u.id = ur.uid
    </select>

</mapper>

6. xml方式进行懒加载

6.1 SqlMapConfig.xml中开启懒加载

<!--配置参数-->
    <settings>
        <!--开启支持延迟加载-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

6.2 IAccountDao.xml和IUserDao.xml

IAccountDao.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.zh.dao.IAccountDao">
    <!--定义封装account和user的resultMap-->
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="id"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!--一对一的关系映射：配置封装user的内容
            select属性指定的内容，查询用户的唯一标识
            column属性指定的内容，用户根据id查询时，所需要的参数的值
        -->
        <association property="user" column="uid" javaType="user" select="cn.zh.dao.IUserDao.findById"></association>
    </resultMap>

    <!--查询所有用户-->
    <select id="findAll" resultMap="accountUserMap">
        select * from account
    </select>

    <!--根据用户id查询账户信息-->
    <select id="findAccountByUid" resultType="account" parameterType="INT">
        select * from account where uid = #{uid}
    </select>

</mapper>

IUserDao.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.zh.dao.IUserDao">
    <!--定义user的resultMap-->
    <resultMap id="userAccountMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <result property="birthday" column="birthday"></result>
        <!--配置user中的accounts集合的映射-->
        <collection property="accounts" ofType="account" select="cn.zh.dao.IAccountDao.findAccountByUid" column="id"></collection>
    </resultMap>
    <!--查询所有用户-->
    <select id="findAll" resultMap="userAccountMap">
        select * from user
    </select>

    <!--根据id查询用户-->
    <select id="findById" parameterType="INT" resultType="cn.zh.domain.User">
        select * from user where id =#{id};
    </select>

</mapper>

7.annotation方式进行CRUD操作

7.1 SqlMapConfig.xml

    <!--指定带有注解的dao接口所在的位置-->
    <mappers>
        <package name="cn.zh.dao"/>
    </mappers>

7.2 IUserDao.xml

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

    /**
     * 保存用户
     * @param user
     */
    @Insert("insert into user(username,address,sex,birthday) values(#{username},#{address},#{sex},#{birthday})")
    void saveUser(User user);

    /**
     * 更新用户
     * @param user
     */
    @Update("update user set username=#{username},sex=#{sex},address=#{address},birthday=#{birthday} where id=#{id}")
    void updateUser(User user);

    /**
     * 删除用户
     * @param userId
     */
    @Delete("delete from user where id=#{id}")
    void deleteUser(Integer userId);

    /**
     * 查找一个用户
     * @param userId
     * @return
     */
    @Select("select * from user where id=#{id}")
    User findById(Integer userId);

    /**
     * 根据用户名模糊查询
     * @param username
     * @return
     */
    @Select("select * from user where username like #{username}")
    List<User> findUserByName(String username);

    /**
     * 查找总数
     * @return
     */
    @Select("select count(*) from user")
    int findTotalUser();
}

8. annotation方式进行一对多和多对多查询

8.1 IUserDao.java

public interface IUserDao {
    /**
     * 查询所有用户
     * @return
     */
    @Select("select * from user")
    @Results(id = "userMap",value = {
            @Result(id=true,column = "id",property = "userId"),
            @Result(column = "username",property = "userName"),
            @Result(column = "address",property = "userAddress"),
            @Result(column = "sex",property = "userSex"),
            @Result(column = "birthday",property = "userBirthday"),
            @Result(property = "accounts",column = "id",
                    many = @Many(select = "cn.zh.dao.IAccountDao.findAccountByUid",fetchType = FetchType.LAZY))
    })
    List<DoubleUser> findAll();


    /**
     * 查找一个用户
     * @param userId
     * @return
     */
    @Select("select * from user where id=#{id}")
    @ResultMap("userMap")
    DoubleUser findById(Integer userId);

    /**
     * 根据用户名模糊查询
     * @param username
     * @return
     */
    @Select("select * from user where username like #{username}")
    @ResultMap("userMap")
    List<DoubleUser> findUserByName(String username);

}

8.2 IAccountDao.java

public interface IAccountDao {
    /**
     * 查询所有账户并且获取每个账户所属的用户信息
     * @return
     */
    @Select("select * from account")
    @Results(id = "accountMap",value = {
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "uid",property = "uid"),
            @Result(column = "money",property = "money"),
            @Result(property = "doubleUser",column = "uid",
                    one = @One(select = "cn.zh.dao.IUserDao.findById",fetchType = FetchType.EAGER))
    })
    List<Account> findAll();

    /**
     * 根据用户id查询账户
     * @param userId
     * @return
     */
    @Select("select * from account where uid = #{userId}")
    List<Account> findAccountByUid(Integer userId);
}

灬繁花落雨灬

关注

0
点赞
踩
0

收藏

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

mybatis1.xml方式配置mybatis1.1 porm.xml引入依赖1.2 SqlMapConfig.xml1.3 IUserDao.xml1.4测试2.annotation配置mybatis2.1 SqlMapConfig.xml2.2 IUserDao.java3.xml方式进行CRUD操作3.1 jdbcConfig.properties3.2 sqlMapConfig.xml3.3 User,QueryVo,DoubleUser3.4 IUserDao.java3.5 IUserDao.x
复制链接

扫一扫

专栏目录