MyBatis笔记(3)-Java API和SQL语句构建器

Java API

SqlSessionFactoryBuilder

SqlSessionFactoryBuilder 有五个 build() 方法,每一种都允许你从不同的资源中创建一个 SqlSession 实例。

1
2
3
4
5
SqlSessionFactory build(InputStream inputStream) // 最常用
SqlSessionFactory build(InputStream inputStream, String environment)
SqlSessionFactory build(InputStream inputStream, Properties properties)
SqlSessionFactory build(InputStream inputStream, String env, Properties props)
SqlSessionFactory build(Configuration config)

mybatis-config.xml创建SqlSessionFactory示例:

1
2
3
4
String resource = "org/mybatis/builder/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(inputStream);

SqlSessionFactory

创建SqlSession实例需要考虑:

  • 事务处理
  • 连接
  • 执行语句
1
2
3
4
5
6
7
8
SqlSession openSession()
SqlSession openSession(boolean autoCommit)
SqlSession openSession(Connection connection)
SqlSession openSession(TransactionIsolationLevel level)
SqlSession openSession(ExecutorType execType,TransactionIsolationLevel level)
SqlSession openSession(ExecutorType execType)
SqlSession openSession(ExecutorType execType, boolean autoCommit)
SqlSession openSession(ExecutorType execType, Connection connection)

默认的openSession()方法没有参数,它会创建有如下特性的SqlSession

  • 会开启一个事务(也就是不自动提交)
  • 将从由当前环境配置的 DataSource 实例中获取 Connection 对象
    事务隔离级别将会使用驱动或数据源的默认设置
  • 预处理语句不会被复用,也不会批量处理更新

SqlSession

事务控制方法

1
2
3
4
void commit()
void commit(boolean force)
void rollback()
void rollback(boolean force)

确保 SqlSession 被关闭

1
2
3
4
5
6
7
try (SqlSession session = sqlSessionFactory.openSession()) {
// following 3 lines pseudocode for "doing some work"
session.insert(...);
session.update(...);
session.delete(...);
session.commit();
}

使用映射器

1
<T> T getMapper(Class<T> type)

映射器注解示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Results(id = "userResult", value = {
@Result(property = "id", column = "uid", id = true),
@Result(property = "firstName", column = "first_name"),
@Result(property = "lastName", column = "last_name")
})
@Select("select * from users where id = #{id}")
User getUserById(Integer id);

@Results(id = "companyResults")
@ConstructorArgs({
@Arg(property = "id", column = "cid", id = true),
@Arg(property = "name", column = "name")
})
@Select("select * from company where id = #{id}")
Company getCompanyById(Integer id);

SqlProvider 注解示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName")
List<User> getUsersByName(String name);

class UserSqlBuilder {
public static String buildGetUsersByName(final String name) {
return new SQL(){{
SELECT("*");
FROM("users");
if (name != null) {
WHERE("name like #{value} || '%'");
}
ORDER_BY("id");
}}.toString();
}
}
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
@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName")
List<User> getUsersByName(
@Param("name") String name, @Param("orderByColumn") String orderByColumn);

class UserSqlBuilder {

// If not use @Param, you should be define same arguments with mapper method
public static String buildGetUsersByName(
final String name, final String orderByColumn) {
return new SQL(){{
SELECT("*");
FROM("users");
WHERE("name like #{name} || '%'");
ORDER_BY(orderByColumn);
}}.toString();
}

// If use @Param, you can define only arguments to be used
public static String buildGetUsersByName(@Param("orderByColumn") final String orderByColumn) {
return new SQL(){{
SELECT("*");
FROM("users");
WHERE("name like #{name} || '%'");
ORDER_BY(orderByColumn);
}}.toString();
}
}

SQL 语句构建器

MyBatis提供的SQL语句构建有两种写法

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
// Anonymous inner class
public String deletePersonSql() {
return new SQL() {{
DELETE_FROM("PERSON");
WHERE("ID = #{id}");
}}.toString();
}

// Builder / Fluent style
public String insertPersonSql() {
String sql = new SQL()
.INSERT_INTO("PERSON")
.VALUES("ID, FIRST_NAME", "#{id}, #{firstName}")
.VALUES("LAST_NAME", "#{lastName}")
.toString();
return sql;
}

// With conditionals (note the final parameters, required for the anonymous inner class to access them)
public String selectPersonLike(final String id, final String firstName, final String lastName) {
return new SQL() {{
SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
FROM("PERSON P");
if (id != null) {
WHERE("P.ID like #{id}");
}
if (firstName != null) {
WHERE("P.FIRST_NAME like #{firstName}");
}
if (lastName != null) {
WHERE("P.LAST_NAME like #{lastName}");
}
ORDER_BY("P.LAST_NAME");
}}.toString();
}

public String deletePersonSql() {
return new SQL() {{
DELETE_FROM("PERSON");
WHERE("ID = #{id}");
}}.toString();
}

public String insertPersonSql() {
return new SQL() {{
INSERT_INTO("PERSON");
VALUES("ID, FIRST_NAME", "#{id}, #{firstName}");
VALUES("LAST_NAME", "#{lastName}");
}}.toString();
}

public String updatePersonSql() {
return new SQL() {{
UPDATE("PERSON");
SET("FIRST_NAME = #{firstName}");
WHERE("ID = #{id}");
}}.toString();
}
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
public String selectPersonSql() {
return new SQL()
.SELECT("P.ID", "A.USERNAME", "A.PASSWORD", "P.FULL_NAME", "D.DEPARTMENT_NAME", "C.COMPANY_NAME")
.FROM("PERSON P", "ACCOUNT A")
.INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID", "COMPANY C on D.COMPANY_ID = C.ID")
.WHERE("P.ID = A.ID", "P.FULL_NAME like #{name}")
.ORDER_BY("P.ID", "P.FULL_NAME")
.toString();
}

public String insertPersonSql() {
return new SQL()
.INSERT_INTO("PERSON")
.INTO_COLUMNS("ID", "FULL_NAME")
.INTO_VALUES("#{id}", "#{fullName}")
.toString();
}

public String updatePersonSql() {
return new SQL()
.UPDATE("PERSON")
.SET("FULL_NAME = #{fullName}", "DATE_OF_BIRTH = #{dateOfBirth}")
.WHERE("ID = #{id}")
.toString();
}