Mybatis学习笔记1

标题mybatis-config.xml文件

标题0.依赖

    <dependencies>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>

        <!-- mysql驱动包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!-- 单元测试 -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
    <!-- scope使用范围,test是指只能测试环境生效-->
    <scope>test</scope>
</dependency>
    </dependencies>

标题0.1 写mybatis核心配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://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.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/table02"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
         <!-- 这种方式会随着功能变多,加载文件变多,代码臃肿 -->
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

标题 namespace命名空间

select标签，用于执行查询语句，id：是接口中的方法，resultType:返回结果类型，一般可以写完整路径。#{id}就相当于之前的预处理的 ？会自动给此处复制。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace名称空间 -->
<!-- 用来关联,映射文件(XML)和接口文件,即接口的类路径 -->
<mapper namespace="com.qf.mapper.UserMapper">

    <select id="findUserById" resultType="com.qf.model.User">
        select * from tb_user where id = #{id}
    </select>
</mapper>

测试

public class TestMybatis {

    // TODO 优化
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        // 获得SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession( );

        // 利用的动态代理技术,获得UserMapper的代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        // 调用方法干活
        User user = userMapper.findUserById(1);
        System.out.println(user);
    }
}

5.crud

5.1根据id查询

   User findUserById(int id);

 <select id="findUserById" resultType="com.qf.model.User">
        <!-- #{id} 就相当于是之前的预处理的 ?,会自动给此处赋值  -->
        <!-- 其实就是接口方法的参数列表的值,会传给 #{id} -->
        <!-- 查询返回的结果集,会自动封装到resultType指定的对象!! -->
        <!--  但是ORM能自动封装有个前提: 查询返回的列名和实体类的属性名要完全一致-->
<!--
        select id uid,username uname,password pwd,phone tel,createTime,sex,money from tb_user where id = #{id}
-->
        select id,username,password,phone,createTime,sex,money from tb_user where id = #{id}
    </select>

5.2全部查询

public interface UserMapper {
    List<User> findAll();
}


<!-- 一个标签,就是一个SQL执行的语句 -->
    <!-- 【注意】虽然查询返回集合,但是返回类型此处还要写集合中存储的类型 -->
    <!-- 【或者这样理解】`在这里插入代码片`虽然返回集合,此处定义的是查询返回要封装的实体类类型 -->
    <select id="findAll" resultType="com.qf.model.User">
        select * from tb_user
    </select>

5.2多参数查询（1）

public interface UserMapper {
    User findUserByLogin(String username,String password);
}

`<select id="findUserByLogin" resultType="com.qf.model.User">
        <!-- 默认是不支持传多个参数,传入多个参数时,需要如下操作(2选1) -->
        <!--
            方案1: #{}内按顺序写param1,param2,....
        -->
        select * from tb_user where username = #{param1} and password = #{param2}
    </select>

5.2多参数查询（2）

public interface UserMapper {

    User findUserByLogin(@Param("username") String username, @Param("password") String password);
    <select id="findUserByLogin" resultType="com.qf.model.User">
      
        <!--
            方案2:在接口方法添加注解@Param 2)在#{}内写注解的值
        -->
        select * from tb_user where username = #{username} and password = #{password}
    </select>
}

5.3 Map参数查询

public interface UserMapper {
    User findUserByLoginMap(HashMap<String,Object> map);
}


 <select id="findUserByLoginMap" resultType="com.qf.model.User">
        <!-- 参数是Map,#{}内写的map的key -->
        select * from tb_user where username = #{usernameKey} and password = #{passwordKey}
    </select>
 @Test
    public void selectByLoginMap() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
       
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        // 获得SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession( );

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        HashMap<String, Object> map = new HashMap<>( );
        map.put("usernameKey","李子豪");
        map.put("passwordKey","123456");
        User user = mapper.findUserByLoginMap(map);
        System.out.println("user = " + user);
    }

5.4 增加

public interface UserMapper {
    int addUser(User user);
}
    <!--
        执行插入语句的标签是insert
        id 是方法名
        参数类型parameterType="" 可以省略
        没有指定返回类型,默认返回受影响行数
     -->
    <insert id="addUser">
        <!-- 单参数,#{}内随便写 -->
        <!-- 多参数,#{}内要么固定param1,parma2,要么固定是注解的值 -->
        <!-- map参数,#{}内写map的key -->
        <!-- 对象参数,#{}内属性名 -->
        insert into tb_user values (#{id},#{username},#{password},#{phone},#{createTime},#{money},#{sex});
    </insert>

    @Test
    public void addUser() throws IOException {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = new User( );
        user.setId(4);
        user.setUsername("谢谢");
        user.setPassword("123456");
        user.setPhone("1111");
        user.setCreateTime(new Date());
        user.setSex(1);
        user.setMoney(10000 );
        int i = mapper.addUser(user);

        System.out.println(i > 0 ? "插入成功":"失败");

        // mysql默认的事务是一句话就是一个独立的事务,自动提交
        // mybatis封装后,默认事务没有自动提交
        // 所以执行增删改需要手动提交事务
        sqlSession.commit();
    }

5.5修改

public interface UserMapper {
    int updateUser(User user);
}

<update id="updateUser">
        <!-- 对象参数,#{}内属性名 -->
        update tb_user set username=#{username},password=#{password},
        phone= #{phone},createTime=#{createTime},money=#{money},sex=#{sex}
        where id = #{id}
    </update>


   @Test
    public void updateUser() throws IOException {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = new User( );
        user.setId(4);
        user.setUsername("李四");
        user.setPassword("123456");
        user.setPhone("22222");
        user.setCreateTime(new Date());
        user.setSex(2);
        user.setMoney(20000 );
        int i = mapper.updateUser(user);

        System.out.println(i > 0 ? "更新成功":"失败");

        // mysql默认的事务是一句话就是一个独立的事务,自动提交
        // mybatis封装后,默认事务没有自动提交
        // 所以执行增删改需要手动提交事务
        sqlSession.commit();
    }

5.6删除

 int deleteUser(int id);

    <delete id="deleteUser">
        delete from tb_user where id=#{id}
    </delete>
  @Test
    public void deleteUser(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int i = userMapper.deleteUser(4);
        System.out.println(i);
        if (i>0){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
        // mysql默认的事务是一句话就是一个独立的事务,自动提交
        // mybatis封装后,默认事务没有自动提交
        // 所以执行增删改需要手动提交事务
        sqlSession.commit();