Mybatis使用简记

Mybatis是什么

MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的持久层框架。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以对配置和原生Map使用简单的 XML 或注解,将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。

官网:http://www.mybatis.org/

Hello Mybatis

首先通过一个项目实例熟悉Mybatis的基础使用吧。

项目结构

HelloMybatis项目结构

配置Maven依赖

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
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<!-- 导入Mybatis核心代码 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<!-- java连接数据库所需依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<!-- 打印操作日志(如执行的SQL)所需依赖 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>

配置mybatis-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
24
25
<?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.cj.jdbc.Driver"/>
<!-- 数据库地址 -->
<property name="url" value="jdbc:mysql://localhost:3307/work?characterEncoding=UTF-8"/>
<!-- 数据库用户名 -->
<property name="username" value="root"/>
<!-- 数据库密码 -->
<property name="password" value=""/>
</dataSource>
</environment>
</environments>
<!-- 导入Mybatis数据库查询映射文件 -->
<mappers>
<mapper resource="mapper/User.xml"/>
</mappers>
</configuration>

创建数据表

1
2
3
4
5
6
7
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

映射文件User.xml

1
2
3
4
5
6
7
8
9
10
<?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.zju.dao.UserDao">
<sql id="tableSection">user</sql>
<select id="getAllUser" resultType="com.zju.meta.User">
SELECT * FROM <include refid="tableSection"/>
</select>
</mapper>

实体对象User

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
package com.zju.meta;
/** 用户类 */
public class User {
private Integer id;
private String name;
private Integer age;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", password='" + password + '\'' +
'}';
}
}

Dao类

1
2
3
4
5
package com.zju.dao;
public interface UserDao {
List<User> getAllUser();
}

测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public class Main {
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 session = sqlSessionFactory.openSession();
try {
UserDao userDao = session.getMapper(UserDao.class);
List<User> users= userDao.getAllUser();
System.out.println(users);
} finally {
session.close();
}
}
}

日志配置

通过以下配置,可以将实际执行的SQL语句打印在日志中,方便排查问题。

1
2
3
4
5
6
7
8
9
# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
# 指定Mapper包范围的日志级别
log4j.logger.com.zju.dao=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m

测试

1
2
3
4
DEBUG [main] - ==> Preparing: SELECT * FROM user
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 3
[com.zju.meta.User@17211155, com.zju.meta.User@b3d7190, com.zju.meta.User@5fdba6f9]

Spring+Mybatis

项目结构

SpringMybatis项目结构

配置Maven依赖

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
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.3.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.1.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.18</version>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>

配置mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
<?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>
<settings>
<setting name="cacheEnabled" value="true"/>
<setting name="logImpl" value="LOG4J"/>
</settings>
<mappers>
<mapper resource="mapper/User.xml"/>
</mappers>
</configuration>

配置spring-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
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
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd"
default-autowire="byName">
<context:annotation-config/>
<context:component-scan base-package="com.zju"/>
<context:property-placeholder location="classpath:*.properties"
ignore-unresolvable="true"/>
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<!-- 数据库地址 -->
<property name="url" value="${db.url}"></property>
<!-- 用户名 -->
<property name="username" value="${db.user}"></property>
<!-- 密码 -->
<property name="password" value="${db.pass}"></property>
<!-- 最大连接池数量 -->
<property name="maxActive" value="${db.maxActive}"></property>
<!-- 初始化物理连接个数 -->
<property name="initialSize" value="${db.initialSize}"></property>
<!-- 最小连接池数量 -->
<property name="minIdle" value="${db.minIdle}"></property>
<!-- 最大等待时间 -->
<property name="maxWait" value="${db.maxWait}"></property>
<property name="timeBetweenEvictionRunsMillis" value="${db.timeBetweenEvictionRunsMillis}"></property>
<property name="minEvictableIdleTimeMillis" value="${db.minEvictableIdleTimeMillis}"></property>
<!-- 检测连接是否有效的SQL -->
<property name="validationQuery" value="${db.validationQuery}"></property>
<property name="testWhileIdle" value="${db.testWhileIdle}"></property>
<!-- 申请连接时是否执行validationQuery -->
<property name="testOnBorrow" value="${db.testOnBorrow}"></property>
<!-- 归还连接时是否执行validationQuery -->
<property name="testOnReturn" value="${db.testOnReturn}"></property>
<!-- 配置扩展插件 统计监控:stat 日志:log4j 防御SQL注入:wall-->
<property name="filters" value="${db.filters}"></property>
</bean>
<!-- 标志不能为sqlSessionFactory -->
<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
<bean id="sqlSessionTemplateReuse" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg index="0" ref="sqlSessionFactoryBean" />
<constructor-arg index="1" value="REUSE" />
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" >
<property name="basePackage" value="com.zju.dao"/>
<property name="sqlSessionTemplateBeanName" value="sqlSessionTemplateReuse"/>
</bean>
</beans>

配置privateSettings.properties

1
2
3
4
5
6
7
8
9
10
11
12
13
14
db.url=jdbc:mysql://localhost:3307/work?characterEncoding=UTF-8
db.user=root
db.pass=
db.maxActive=10
db.initialSize=2
db.minIdle=2
db.maxWait=60000
db.timeBetweenEvictionRunsMillis=3000
db.minEvictableIdleTimeMillis=300000
db.validationQuery=SELECT 'x' FROM DUAL
db.testWhileIdle=true
db.testOnBorrow=false
db.testOnReturn=false
db.filters=stat,wall,log4j

Service类

1
2
3
4
5
6
7
8
9
10
@Service
public class UserService {
@Resource
private UserDao userDao;
public List<User> getAllUser(){
return userDao.getAllUser();
}
}

测试类

1
2
3
4
5
6
7
public class Main {
public static void main(String[] args){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-config.xml");
UserService service = (UserService) context.getBean("userService");
System.out.println(service.getAllUser());
}
}

日志配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j='http://jakarta.apache.org/log4j/'>
<appender name="stdout" class="org.apache.log4j.ConsoleAppender">
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%5p [%t] - %m"/>
</layout>
</appender>
<logger name="com.zju.dao">
<level value="debug"/>
</logger>
<root>
<priority value="info"/>
<appender-ref ref="stdout"/>
</root>
</log4j:configuration>

测试

1
2
3
4
DEBUG [main] - ==> Preparing: SELECT * FROM user
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 3
[com.zju.meta.User@7b4c50bc, com.zju.meta.User@5884a914, com.zju.meta.User@50378a4]

相关问题

字符串对比

在Mybatis的if test语句中如果需要对比字符串,可以组合使用单引号和双引号,比如<if test='name=="Ted" '></if>

空指针报警

在运行Druid时可能出现Cannot resolve com.mysq.jdbc.Connection.ping method. Will use 'SELECT 1' instead.java.lang.NullPointerException报警

使用5.1.x版本的mysql-connector-java可以解决

Spring和Mybatis整合时无法读取properties

因为MapperScannerConigurer实际是在解析加载bean定义阶段的,这个时候要是设置sqlSessionFactory的话,会导致提前初始化一些类,这个时候,PropertyPlaceholderConfigurer还没来得及替换定义中的变量,所以导致把表达式当作字符串复制了。

把org.mybatis.spring.SqlSessionFactoryBean的id修改为’sqlSessionFactoryBean’,使得该值不是’sqlSessionFactory’,不然会造成提前初始化。

缺少依赖包错误

  • java.lang.NoClassDefFoundError: org/springframework/dao/support/DaoSupport 错误

    缺失spring-tx包

  • java.lang.NoClassDefFoundError: org/springframework/jdbc/datasource/TransactionAwareDataSourceProxy 错误

    缺失spring-jdbc包