MyBatis:执行SQL语句

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

传统方法

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

Mapper代理方法

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
    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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
    <?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>

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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()