MyBatis:执行SQL语句

我们已经完成工具类的设计，即从SqlSessionFactory中获取SqlSession，那么如何使用SqlSession来执行Sql语句呢？
假设我们现在已经有一张User表，并且里面存了一些信息，现在我想对这张表执行CRUD

传统方法

传统方法中我们需要建立一个Dao包，在Dao包里面有接口以及接口实现类，每增加一个新的接口方法就需要新增一个实现类，实现类类里的方法就是对数据库的操作，增删改查之类的。

Mapper代理方法

在这种方法中，接口类中每增加一个方法，只需要在xml中新增一个标签。
UserMapper.java:

    package com.xliu.dao;

<!-- User的实体类 -->
    import com.xliu.pojo.User;

    import java.util.List;

    public interface UserMapper {
        <!-- 定义一个抽象方法getUser()，返回类型是一个User类型的List -->
        List<User> getUser();
        //    根据Id查询用户
        User getUserById(int id);

        void addUser(User user);

        void updateUser(User user);

        void deleteUser(int id);
    }

UserMapper.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="com.xliu.dao.UserMapper">

    <select id="getUser" resultType="com.xliu.pojo.User">
        select * from mybatis.user
    </select>

    <select id="getUserById" resultType="com.xliu.pojo.User" parameterType="int">
        select * from mybatis.user where id = #{id}
    </select>

    <insert id="addUser" parameterType="com.xliu.pojo.User">
        insert into mybatis.user (id, name, pwd) values (#{id},#{name},#{pwd});
    </insert>

    <update id="updateUser" parameterType="com.xliu.pojo.User">
        update mybatis.user set name=#{name}, pwd=#{pwd} where id=#{id};
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id=#{id};
    </delete>
</mapper>

UserMapper.xml文件中，namespace对应接口，select id对应接口(namespace)中的方法名，resultType对应Sql语句执行的返回类型，如果接口方法有输入值的话，则添加属性parameterType。
另外需要注意的是增删改查的SQL语句对应的标签是不同的。

另外一定要记住，在/resources/config.xml中要添加Mapper.xml的映射：
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">
    <configuration>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                    <property name="username" value="root"/>
                    <property name="password" value="pwd"/>
                </dataSource>
            </environment>
        </environments>

<!-- 添加Mapper.xml的映射 -->
        <mappers>
            <mapper resource="com/xliu/dao/UserMapper.xml"/>
        </mappers>

    </configuration>

测试

package com.xliu.dao;

import com.xliu.pojo.User;
import com.xliu.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserMapperTest {
    @Test
    public void test() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try {
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<User> userList = mapper.getUser();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }


    }
    @Test
    public void testById() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User userById = mapper.getUserById(1);
        System.out.println(userById);
        sqlSession.close();
    }
    @Test
    public void addUser() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.addUser(new User(4,"hha", "pwd"));

//        增删改需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void updateUser() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(2,"Liu","password"));
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void deleteUser() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser(4);
        sqlSession.commit();
        sqlSession.close();
    }
}

需要记住：执行完一次之后需要sqlSession.close()，增删改的语句需要提交事务sqlSession.commit()