mybatis实现CRUD

mybatis实现CRUD

1.单个数据查询

    @Test
    public void testFindById(){
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

        SqlSessionFactory sqlSessionFactory = builder.build(AppTest.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml"));

        SqlSession sqlSession = sqlSessionFactory.openSession();

        User u = sqlSession.selectOne("user.findById", 2);

        System.out.println(u);


    }
}

    <select id="findById" parameterType="int" resultType="com.shenhao.domain.User">
        select * from user where id=#{id}
    </select>

2.简单的like查询

    @Test
    public void testFindByLike(){
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

        SqlSessionFactory sqlSessionFactory = builder.build(AppTest.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml"));

        SqlSession sqlSession = sqlSessionFactory.openSession();

        List<User> list = sqlSession.selectList("user.findByLike", "%王%");

        System.out.println(list);


    }

    <select id="findByLike" parameterType="String" resultType="com.shenhao.domain.User">
        select * from user where username like #{username}
    </select>

3.$和#的区别
关于#{}：

#{}等同于PreparedStatement中的占位符?，会自动对传入的字符串数据加一对单引号，可以避免Sql注入。

比如 select * from user where username = #{username} ,传入的username为小张，那么最后打印出来的就是
select * from user where username = ‘小张’

#{}可以接收简单类型值或Pojo属性值。 如果parameterType传输单个简单类型值，#{}括号中可以是任意名称。

关于${}:

${}将传入的数据直接显示生成在Sql中，只是简单的拼接。如：order by ${id}，如果传入的值是id，则解析成的Sql为order by id。

如果上面的例子使用${}，则成了select * from user where username = 小张

$
可以接收简单类型值或Pojo属性值，如果parameterType传输单个简单类型值，${}括号中只能是“value”这个字符串

总结：
$
方式一般用于传入数据库对象，例如传入表名、order by 的字段
一般能用#的就别用$.

4.保存操作

    @Test
    public void testSave(){
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

        SqlSessionFactory sqlSessionFactory = builder.build(AppTest.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml"));

        SqlSession sqlSession = sqlSessionFactory.openSession();

        User user = new User();
        user.setUsername("小明");
        user.setSex("1");
        user.setBirthday(new Date());
        user.setAddress("南京");

        sqlSession.insert("user.save",user);

        sqlSession.commit();

        sqlSession.close();


    }

    <insert id="save" parameterType="com.shenhao.domain.User">
        
        insert into user VALUES (null,#{username},#{sex},#{birthday},#{address})
    </insert>

5.保存获取自增长id的值

    <insert id="save" parameterType="com.shenhao.domain.User">
        <selectKey resultType="int" keyProperty="id" order="AFTER">
            select last_insert_id()
        </selectKey>
        insert into user VALUES (null,#{username},#{sex},#{birthday},#{address})
    </insert>

6.修改操作

 @Test
    public void testUpdate(){
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

        SqlSessionFactory sqlSessionFactory = builder.build(AppTest.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml"));

        SqlSession sqlSession = sqlSessionFactory.openSession();

        User user = new User();
        user.setUsername("小李");
        user.setId(28);

        sqlSession.update("user.updateUsernameById",user);

        sqlSession.commit();

        sqlSession.close();


    }

    <update id="updateUsernameById" parameterType="com.shenhao.domain.User">
        update user set username=#{username} where id=#{id}
    </update>

7.删除操作

    @Test
    public void testDelete(){
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

        SqlSessionFactory sqlSessionFactory = builder.build(AppTest.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml"));

        SqlSession sqlSession = sqlSessionFactory.openSession();


        sqlSession.delete("user.delById",27);

        sqlSession.commit();

        sqlSession.close();


    }

    <delete id="delById" parameterType="int">
        delete from user where id=#{id}
    </delete>