Mybatis快速使用
1、什么是 MyBatis？
MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。
2、快速使用
使用SpringBoot + Mybatis + MySQL搭建一个web程序
1、依赖的jar包
MySQL 驱动依赖和 Druid 连接池的依赖
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.49</version> <scope>runtime</scope> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.4</version> </dependency>
Mybatis 启动包依赖，此处导入的是 SpringBoot 和 Mybatis 整合启动器的依赖，可以看到，这个启动包依赖了mybatis和mybatis-spring（Mybatis 和 Spring 整合的 Jar 包），因此使用 SpringBoot 之后只需要导入这个启动器的依赖即可。
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency>
Web依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency>
2、配置文件：application.peoperties
#==================================================== server.port=80 server.servlet.context-path=/ #==================================================== #springboot + mybatis #mybatis.mapper-locations=classpath:mapper/**/*.xml mybatis.type-aliases-package=com.meihaocloud.**.domain #==================================================== #dataSource spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.driverClassName = com.mysql.jdbc.Driver spring.datasource.url = jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false spring.datasource.username = root # RSA加密密码 spring.datasource.password = TmByUyPPDR/GkA1dSKib4kQbQ6htynyXFsIzj2SXtQIrYeY1+nr1FcFqwGlZn2Z5oYewUD4p9m14tDyFiU1HQTF9yJ0GtAy3QelAs4QcYBuFjRxby4cUzV4ZQOAWfzIvaMI7NY7qYYXcsjo5KSsTNsVrGIBqd/6/W9A9h5U9Sgk= # 数据源其他配置 spring.datasource.initialSize=1 spring.datasource.minIdle=2 spring.datasource.maxActive=10 spring.datasource.maxWait=60000 spring.datasource.timeBetweenEvictionRunsMillis=60000 spring.datasource.minEvictableIdleTimeMillis= 300000 spring.datasource.validationQuery=SELECT 1 FROM DUAL spring.datasource.testWhileIdle=true spring.datasource.testOnBorrow=false spring.datasource.testOnReturn=false spring.datasource.poolPreparedStatements=true # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 spring.datasource.filters = stat,wall,log4j spring.datasource.maxPoolPreparedStatementPerConnectionSize = 10 spring.datasource.useGlobalDataSourceStat = true spring.datasource.connectionProperties = druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 spring.datasource.logSlowSql=true druid.login.name=admin123 druid.login.password=123admin!
import java.sql.SQLException;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.meihaocloud.newblog.common.utils.RSAUtils;
@Configuration
public class DruidConfig {
private Logger logger = LoggerFactory.getLogger(DruidConfig.class);
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.filters}")
private String filters;
@Value("${spring.datasource.logSlowSql}")
private String logSlowSql;
@Value("${druid.login.name}")
private String druidLoginName;
@Value("${druid.login.password}")
private String druidLoginPassword;
@Bean
public DataSource druidDataSource() {
DruidDataSource datasource = new DruidDataSource();
// 密码解密
String pwd = RSAUtils.decryptByPrivateKey(password);
datasource.setUrl(dbUrl);
datasource.setUsername(username);
datasource.setPassword(pwd);
datasource.setDriverClassName(driverClassName);
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
return datasource;
}
@Bean
public ServletRegistrationBean<StatViewServlet> statViewServle() {
ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<StatViewServlet>(
new StatViewServlet(), "/druid/*");
// IP白名单
// servletRegistrationBean.addInitParameter("allow","192.168.1.12,127.0.0.1");
// IP黑名单
// servletRegistrationBean.addInitParameter("deny","192.168.4.23");
// 控制台用户
servletRegistrationBean.addInitParameter("loginUsername", druidLoginName);
servletRegistrationBean.addInitParameter("loginPassword", druidLoginPassword);
// 是否能够重置数据
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean<WebStatFilter> statFilter() {
FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<WebStatFilter>(
new WebStatFilter());
// 添加过滤规则
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}3、配置 Mapper.xml
默认情况下xml和Mapper放在一起，就可以不用配置。但为了统一管理xml，就需要配置xml存放位置。
方法1： application.properties 中设置
既然是和 SpringBoot 整合，那么万变不离xxxAutoConfiguration这个配置类了，Mybatis 的配置类就是MybatisAutoConfiguration，如下：
@org.springframework.context.annotation.Configuration
@ConditionalOnClass({ SqlSessionFactory.class, SqlSessionFactoryBean.class })
@ConditionalOnBean(DataSource.class)
@EnableConfigurationProperties(MybatisProperties.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
public class MybatisAutoConfiguration {
private static final Logger logger = LoggerFactory.getLogger(MybatisAutoConfiguration.class);
private final MybatisProperties properties;
private final Interceptor[] interceptors;
private final ResourceLoader resourceLoader;
private final DatabaseIdProvider databaseIdProvider;
private final List<ConfigurationCustomizer> configurationCustomizers;
……@ConfigurationProperties(prefix = MybatisProperties.MYBATIS_PREFIX)
public class MybatisProperties {
public static final String MYBATIS_PREFIX = "mybatis";
/**Mybatis配置文件的位置
* Location of MyBatis xml config file.
*/
private String configLocation;
/** Mybatis的Mapper的xml文件的位置
* Locations of MyBatis mapper files.
*/
private String[] mapperLocations;
/**
* Packages to search type aliases. (Package delimiters are ",; \t\n")
*/
private String typeAliasesPackage;
……一般将mapper放在resources下
## xml文件放置在/src/main/resource/mapper/文件夹下 mybatis.mapper-locations=classpath*: mapper/**/*.xm |
方法2：在配置类中配置
4、配置扫描Mybatis的interface
方式1：@Mapper
该注解标注在 Mybatis 的interface类上，SpringBoot 启动之后会扫描后会自动生成代理对象。实例如下：
@Mapper
public interface SysUserMapper {
int deleteByPrimaryKey(String userId);
int insert(SysUser record);
int insertSelective(SysUser record);
SysUser selectByPrimaryKey(String userId);
int updateByPrimaryKeySelective(SysUser record);
int updateByPrimaryKey(SysUser record);
}缺点：每个interface上都要标注一个，很麻烦，一个项目中的 interface 可能会有上百个。
方式2：@MapperScan
@Mapper注解的升级版，标注在配置类上，用于一键扫描 Mybatis 的interface。
使用也是很简单的，直接指定接口所在的包即可，如下：
@SpringBootApplication
@ServletComponentScan
@MapperScan(value= {"com.meihaocloud.system.mapper","com.meihaocloud.order.mapper"})
public class NewBlogApplication extends SpringBootServletInitializer {
public static void main(String[] args) {
SpringApplication.run(NewBlogApplication.class, args);
}
@Override
protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
return application.sources(NewBlogApplication.class);
}
}@MapperScan和@Mapper这两个注解千万不要重复使用。
优点：一键扫描，不用每个 interface 配置。
5、基本操作
代码是mybatis-Generator生成的，格式都固定
SysUser selectByPrimaryKey(String userId);
<select id="selectByPrimaryKey" resultMap="BaseResultMap"
parameterType="java.lang.String">
select
<include refid="Base_Column_List" />
from t_sys_user
where user_id = #{userId,jdbcType=VARCHAR}
</select>int insert(SysUser record);
<insert id="insert"
parameterType="com.meihaocloud.newblog.system.domain.SysUser">
insert into t_sys_user (user_id, name,
password,
salt, status,
create_id, create_time, email,
mobile,mfa_key,mfa_bind_time,remark)
values
(#{userId,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR},
#{password,jdbcType=VARCHAR},
#{salt,jdbcType=VARCHAR}, #{status,jdbcType=CHAR},
#{createId,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP},
#{email,jdbcType=VARCHAR},
#{mobile,jdbcType=VARCHAR},
#{mfaKey,jdbcType=VARCHAR},
#{mfaBindtime,jdbcType=VARCHAR},
#{remark,jdbcType=VARCHAR})
</insert>int deleteByPrimaryKey(String userId);
<delete id="deleteByPrimaryKey" parameterType="java.lang.String">
delete from
t_sys_user
where user_id = #{userId,jdbcType=VARCHAR}
</delete>int updateByPrimaryKey(SysUser record);
<update id="updateByPrimaryKey"
parameterType="com.meihaocloud.newblog.system.domain.SysUser">
update t_sys_user
set name = #{name,jdbcType=VARCHAR},
password = #{password,jdbcType=VARCHAR},
salt = #{salt,jdbcType=VARCHAR},
status = #{status,jdbcType=CHAR},
create_id = #{createId,jdbcType=VARCHAR},
create_time =
#{createTime,jdbcType=TIMESTAMP},
email = #{email,jdbcType=VARCHAR},
mobile = #{mobile,jdbcType=VARCHAR},
mfa_key = #{mfaKey,jdbcType=VARCHAR},
mfa_bind_time = #{mfaBindTime,jdbcType=VARCHAR},
remark =#{remark,jdbcType=VARCHAR}
where user_id = #{userId,jdbcType=VARCHAR}
</update>
