目录
对于具体的数据操作,Hibernate会自动生成SQL语句,而MyBatis则要求开发者编写具体的SQL语句。相对于Hibernate等“全自动”的ORM机制而言,MyBatis在SQL开发的工作量和数据库移植性上做出了让步,为数据持久化操作提供了更大的自由空间。
Spring整合MyBatis步骤:
前期准备工作
- 导入mybatis相关依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.1</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.3</version>
</dependency>
- 创建相关数据库表
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb DEFAULT CHARACTER SET utf8;
USE sampledb;
##创建用户表
CREATE TABLE t_user (
user_id INT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(30),
password VARCHAR(32),
credits INT,
last_visit datetime,
last_ip VARCHAR(23)
)ENGINE=InnoDB;
##创建用户登录日志表
CREATE TABLE t_login_log (
login_log_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
ip VARCHAR(23),
login_datetime datetime
)ENGINE=InnoDB;
##插入初始化数据
INSERT INTO t_user (user_name,password)
VALUES('admin','123456');
COMMIT;
1、写SQL映射文件 XXMapper.xml
- 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必须与对应的接口类一致-->
<mapper namespace="com.smarter.dao.mybatis.UserMybatisDao">
<select id="getMatchCount" resultType="java.lang.Integer">
<!--方法2:-->
SELECT count(*) FROM t_user WHERE user_name = #{username} and password = #{password}
</select>
<select id="findUserbyUsername" resultType="User" parameterType="java.lang.String">
SELECT
user_id userId,
user_name userName,
credits credits,
last_ip lastIp,
last_visit lastVisit
FROM t_user WHERE user_name = #{username}
</select>
<update id="updateLoginInfo">
UPDATE t_user
SET last_visit = #{lastVisit},
last_ip = #{lastIp},
credits = #{credits}
WHERE user_id = #{userId}
</update>
</mapper>
注意事项:
UserMapper.xml中getMatchCount的SQL语句含多个参数,此时不能使用parameterType属性指定(只适用于单个参数)
解决方法:
方法I :
UserMapper.xml:
SELECT count(*) FROM t_user WHERE user_name = #{0} and password = #{1}
DAO方法:
public int getMatchCount(String username, String password);
用#{index}是第几个就用第几个的索引,索引从0开始。DAO方法不变。
方法II:
基于注解
UserMapper.xml:
SELECT count(*) FROM t_user WHERE user_name = #{username} and password = #{password}
DAO方法:
public int getMatchCount(@Param("username") String username,
@Param("password") String password);
在select指定参数名,此参数名需要与在对应DAO接口类中的方法参数注解@Param(“xxx”)一致
- LoginLogMapper.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必须与对应的接口类一致-->
<mapper namespace="com.smarter.dao.mybatis.LoginLogMybatisDao">
<insert id="insertLoginLog" parameterType="LoginLog">
INSERT INTO t_login_log (user_id, ip, login_datetime)
VALUES(#{userId}, #{ip}, #{loginDate})
</insert>
</mapper>
DAO层:
public interface LoginLogMybatisDao {
//保存登陆日志SQL
public void insertLoginLog(LoginLog loginLog);
}
可以发现LoginLogMapper.xml中insertLoginLog的SQL语句含三个参数,但在其对应的LoginLogMybatisDao.insertLoginLog(LoginLog loginLog)方法参数却为LoginLog类。
此时,在LoginLogMapper.xml中可以通过#{属性名}取值(#{userId}, #{ip}, #{loginDate}),此属性名必须为LoginLog中的属性名,如下。
public class LoginLog {
private int loginLogId;
private int userId;
private String ip;
private Date loginDate;
...
}
2、写映射文件的对应接口类
UserMyBatisDao.java:
package com.smarter.dao.mybatis;
import com.smarter.domain.User;
import org.apache.ibatis.annotations.Param;
public interface UserMybatisDao {
// 方法2:基于注解,用@Param来指定哪一个
public int getMatchCount(@Param("username") String username,
@Param("password") String password);
// 函数作用:根据用户名获取User对象
public User findUserbyUsername(String username);
// 函数作用:更新用户积分、最后登录IP及最后登录时间
public void updateLoginInfo(User user);
}
LoginLogMybatisDao.java:
package com.smarter.dao.mybatis;
import com.smarter.domain.LoginLog;
public interface LoginLogMybatisDao {
//保存登陆日志SQL
public void insertLoginLog(LoginLog loginLog);
}
- 此接口所在包路径要与对应SQL映射文件的mapper的namespace命名空间一致;
- 此接口中的方法名要与映射文件中对应的SQL语句的id一致
3、写MyBatis配置文件
mybatis-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>
<typeAliases>
<!--不区分大小写-->
<typeAlias alias="User" type="com.smarter.domain.User"></typeAlias>
<typeAlias alias="LoginLog" type="com.smarter.domain.LoginLog"></typeAlias>
</typeAliases>
</configuration>
此文件中可使用typeAliases属性配置类或包的别名,用于简化SQL映射文件中的sql语句的返回值类型(resultType)或参数类型(parameterType)
4、Spring对应xml中写MyBatis相关配置
smart-context.xml:
<?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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.0.xsd">
<!-- 扫描类包,将标注Spring注解的类自动转化Bean,同时完成Bean的注入 -->
<context:component-scan base-package="com.smarter.dao"></context:component-scan>
<context:component-scan base-package="com.smarter.service"></context:component-scan>
<!-- 配置数据源 -->
<!--<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"-->
<!--destroy-method="close"-->
<!--p:driverClassName="com.mysql.jdbc.Driver"-->
<!--p:url="jdbc:mysql://localhost:3306/sampledb"-->
<!--p:username="root"-->
<!--p:password="123456" />-->
<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/sampledb?serverTimezone=GMT%2B8&useSSL=false"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<!--以下为Mybatis配置-->
<!--通过Spring风格创建MyBatis的SqlSessionFactory-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!--注入数据源-->
<property name="dataSource" ref="datasource"></property>
<!--指定Mybatis的总装配置文件-->
<property name="configLocation" value="classpath:mybatis-config.xml"></property>
<!--扫描式加载SQL映射文件,不需要在mybatis-config.xml中分别组装所有映射文件-->
<!--SqlSessionFactoryBean将扫描tk/mybatis/simple/mapper类路径并加载所有以.xml为后缀的映射文件-->
<property name="mapperLocations" value="classpath:com/smarter/mybatis/mapper/*.xml"></property>
</bean>
<!--mybatis-spring提供一个转换器MapperScannerConfigurer-->
<!--它可以将映射接口直接转换为Spring容器中的Bean,这样就可以在Service中注入映射接口的Bean了-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="sqlSessionFactory" ref="sqlSessionFactory"></property>
<!--MapperScannerConfigurer将扫描basePackage所指定的包下的所有接口类(包括子包)-->
<!--如果它们在SQL映射文件中定义过,则将为它们动态定义为一个Spring Bean-->
<!--这样就可以在Service中直接注入映射接口的Bean了-->
<property name="basePackage" value="com.smarter.dao.mybatis"></property>
</bean>
</beans>
5、Service层注入步骤2的接口实例,直接调用接口方法即可访问数据库
UserService.java
package com.smarter.service;
import com.smarter.dao.mybatis.LoginLogMybatisDao;
import com.smarter.dao.mybatis.UserMybatisDao;
import com.smarter.domain.LoginLog;
import com.smarter.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class UserService {
private UserMybatisDao userMybatisDao;
private LoginLogMybatisDao loginLogMybatisDao;
@Autowired
public void setUserMybatisDao(UserMybatisDao userMybatisDao) {
this.userMybatisDao = userMybatisDao;
}
@Autowired
public void setLoginLogMybatisDao(LoginLogMybatisDao loginLogMybatisDao) {
this.loginLogMybatisDao = loginLogMybatisDao;
}
public boolean hasMatchUser(String username, String password){
int matchCount = userMybatisDao.getMatchCount(username, password);
return matchCount > 0;
}
public User findUserByUserName(String username){
return userMybatisDao.findUserbyUsername(username);
}
@Transactional
public void loginSuccess(User user){
user.setCredits(5 + user.getCredits());
userMybatisDao.updateLoginInfo(user);
LoginLog loginLog = new LoginLog();
loginLog.setUserId(user.getUserId());
loginLog.setIp(user.getLastIp());
loginLog.setLoginDate(user.getLastvisit());
loginLogMybatisDao.insertLoginLog(loginLog);
}
}
6、测试MyBatis
package com.smarter.service;
import com.smarter.domain.User;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.Date;
public class Service {
@Test
public void test(){
ApplicationContext context =
new ClassPathXmlApplicationContext("smart-context.xml");
UserService userService =
context.getBean("userService", UserService.class);
boolean b1 = userService.hasMatchUser("admin", "123456");
boolean b2 = userService.hasMatchUser("admin", "1111");
System.out.println(b1);
System.out.println(b2);
User user = userService.findUserByUserName("admin");
System.out.println(user);
user.setUserId(1);
user.setUserName("admin");
user.setLastIp("192.168.12.7");
user.setLastvisit(new Date());
userService.loginSuccess(user);
}
}
测试前数据库表内容:
测试用户匹配及打印用户信息:
测试后数据库表内容:
参考:《精通Spring4.x——企业应用开发实战》
本项目github地址:https://github.com/bluJoker/forumSSM.git
版本:v3.0