Mybatis_2_CRUD

1. CRUD

 

@Test
    public void queryTest() throws IOException {
        //读取配置文件
        InputStream resource = Resources.getResourceAsStream("mybatis-config.xml");
        //创建SqlSession工厂类对象
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resource);
        //创建SqlSession
        SqlSession sqlSession = sessionFactory.openSession();
        //获取映射器
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.getUserById(81);
        System.out.println(user);
    }

    @Test
    public void queryAllTest()throws IOException{
        //读取配置文件mybatis-config.xml
        InputStream resource = Resources.getResourceAsStream("mybatis-config.xml");
        //创建SqlSession工厂类对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource);
        //获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获取映射器
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> allUser = userMapper.getAllUser();
        allUser.forEach(System.out::println);
    }

    @Test
    public void addUserTest() throws IOException {
        //读取配置文件mybatis-config.xml
        InputStream resource = Resources.getResourceAsStream("mybatis-config.xml");
        //创建SqlSession工厂类对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource);
        //获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获取映射器对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("970285526");
        user.setUpassword("zhao1237");
        user.setEmail("zfx@pku.udu.cn");
        Boolean aBoolean = userMapper.addUser(user);
        sqlSession.commit(true);
        System.out.println(aBoolean);
    }

    @Test
    public void deleteUserTest() throws IOException {
        //读取配置文件mybatis-config.xml
        InputStream resource = Resources.getResourceAsStream("mybatis-config.xml");
        //创建SqlSession工厂类对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource);
        //获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获取映射器对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.deleteUserById(19);
        sqlSession.commit();
    }

    @Test
    public void updateUser() throws IOException {
        //读取配置文件mybatis-config.xml
        InputStream resource = Resources.getResourceAsStream("mybatis-config.xml");
        //创建SqlSession工厂类对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource);
        //获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获取映射器对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("970285525");
        user.setUpassword("zhao1237*****");
        user.setEmail("zfx@pku.udu.cn");
        userMapper.updateUser(user);
        sqlSession.commit();
    }

 

<?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.mapper.UserMapper"><!--通过namespace赋值为XxxxMapper接口实现绑定 Id为指定方法-->
    <select id="getUserById"  resultType="com.pojo.User">
        SELECT id,username,email FROM t_user WHERE id =#{id}
    </select>

    <select id="getAllUser" resultType="com.pojo.User">
        select id,username,email from t_user;
    </select>
    
    <insert id="addUser" >
        insert into t_user values(null,#{username},#{upassword},#{email})
    </insert>
    
    <delete id="deleteUserById" >
        delete from t_user where id=#{id}
    </delete>
    
    <update id="updateUser" >
        UPDATE t_user SET upassword=#{upassword} WHERE username=#{username}
    </update>
</mapper>

 

2.通过mppers的package属性引入放XxxxMapper.xml的包

2.1在resources目录下新建com.mapper包，包里面放所有的XxxMapper.xml文件

2.2在mybatis-config.xml中配置如下代码

    <!-- 映射器：指定映射文件或者映射类 -->
    <mappers>
        <package name="com.mapper"/>
    </mappers>

 3.mybatis中sql语句获取参数的两种方式：

#{}:preparedstatement
${}:satement,只用于模糊查询和批量删除

传给Mapper接口方法的参数的处理方式：
1.单个参数
1.1当传输的参数为单个String、基本数据类型及其包装类
#{}:#{任意值},会自动为string加上''
${}:必须使用${value}/${_param}，不会自动为string加上''
例如，当传入String ids = "1,2,3"; #{ids}会解析为'1,2,3'，而${value}会解析为1,2,3

1.2当传输的参数为单个JavaBean对象
#{}:#{bean的属性}
${}:${bean的属性}
1.3 当传输的参数为map对象
#{}:#{key名}
${}:${key名}
2.多个参数
当传输多个参数时，mybatis会将这些参数封装进map集合中，默认键名为0,1,...或param1,param2,...
#{}:#{0},#{1},.../#{pram1},#{param2},...
${}:${pram1},${param2},...
如果要自定义键名，可以在Mapper接口的方法中给每个参数加上注解@Param("自定义键名")

4.主键生成方式、获取主键值
4.1主键生成方式
1）支持主键自增：mysql sql service
2）不支持主键自增：oracle
4.2获取主键值
在添加后立刻获取主键值的方法:
在XxxMapper.xml配置文件中的insert语句配置如下：

 <!--useGeneratedKeys:指示要返回自增的主键;keyProperty:传给映射器的bean对象的该属性将用于接收返回的主键值-->
    <insert id="addUser" useGeneratedKeys="true" keyProperty="id">
        insert into t_user values(null,#{username},#{upassword},#{email})
    </insert>

 

5. 自定义映射关系

5.1 多对一查询---给bean中的bean类型属性赋值

UserBean：

private Integer id;
    private String username;
    private String upassword;
    private String email;
    private Department dept;

 

DepartmentBean：

 private Integer depId;
    private String depName;

 

UserMapper.xml:

<!--<resultMap type="" id="">:自定义映射，处理复杂的表关系,type:指定接收记录的bean，id:该自定义映射的标识
    <id column="" property="">:设置主键的映射关系,column:设置字段名 property:设置该字段映射给bean的哪个属性
    <result>:设置非主键的映射关系-->
    <resultMap type="com.pojo.User" id="userMap">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="email" property="email"/>
        <result column="did" property="dept.depId"/>
        <result column="dname" property="dept.depName"/>
    </resultMap>

</resultMap>
<select id="getUserWithSelectedDeptid" resultMap="userMap">
    select tu.id,tu.username,tu.email,td.id as did,td.depname as dname from t_user tu left join t_department td on tu.id=td.id and td.id=#{depId}
</select>

 

 5.2 多对一级联查询（先查A表，再根据A表的查询结果查B表）

UserMapper.xml:

<resultMap id="userStepMap" type="com.pojo.User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="email" property="email"/>
        <!--property:用于装下一步查询结果的bean的属性，select:选择指定查询方法，column:给指定方法输入的参数-->
        <association property="dept" select="com.mapper.DepartmentMapper.getDepartmentById" column="id"></association>
    </resultMap>
    <select id="getUserAndDeptidStepByStep" resultMap="userStepMap">
        select id,username,email from t_user where id=#{id}
    </select>

DepartmentMapper.xml:

<select id="getDepartmentById" resultType="com.pojo.Department">
        select id as depId,depname from t_department where id=#{id}
    </select>

Department接口:

public interface DepartmentMapper {
    Department getDepartmentById(Integer did);
}

 

5.3一对多查询
DepartmentMapper.xml:

<resultMap id="usersMap" type="com.pojo.Department">
        <id column="depId" property="depId"/>
        <id column="depName" property="depName"/>
        <!--<collection>标签专门用于处理一对多关系
            ofType:集合中的类型，不需要指定JAVAType-->
        <collection  property="users" ofType="com.pojo.User">
            <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="email" property="email"/>
        </collection>
    </resultMap>
    <select id="getUsersById" resultMap="usersMap">
        select td.id as depId,td.depname as depName,tu.id as id,tu.username as username,tu.email as email from t_department td, t_user tu where td.id=tu.id and td.id=#{value}
    </select>

 DepartmentBean:

private Integer depId;
    private String depName;
    private List<User> users;

 

5.4 多对一的级联查询
在<collection>的标签中设置 location属性