mybatis.mapper
MyBatis 是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。
MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。
MyBatis 可以使用简单的 XML 或注解来配置和映射原生类型、接口和 Java 的 POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
如何使用:
全注解方式,使用@select
- poml文件:
添加依赖:mybatis依赖需要指定版本(springboot默认默认mybatis选项),加mysql,是因为连接的库是mysql
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
- 增加service、model、mapper文件
- service
//接口类
import java.util.Map;
public interface UserInfoService {
Map serachUserInfo();
}
//接口实现类
import cn.fenzeshidai.patrol.xunshi.mapper.UserInfoMapper;
import cn.fenzeshidai.patrol.xunshi.model.UserInfo;
import cn.fenzeshidai.patrol.xunshi.service.UserInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.HashMap;
import java.util.Map;
/**
* @ClassName UserInfoServiceImpl
* @Description: TODO
* @Author fz-zzg
* @Date 2019-08-21 15:59
* @Version V1.0
**/
@Service
public class UserInfoServiceImpl implements UserInfoService {
@Autowired
public UserInfoMapper userInfoMapper;
public Map serachUserInfo(){
Map map = new HashMap();
UserInfo userInfo =userInfoMapper.search();
System.out.println(userInfo.getHome());
return map;
}
}
- model
public class UserInfo {
public String name;
public String home;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getHome() {
return home;
}
public void setHome(String home) {
this.home = home;
}
}
- mapper:
import cn.fenzeshidai.patrol.xunshi.model.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
@Mapper
public interface UserInfoMapper{
@Select("SELECT name,home from user limit 1")
UserInfo search();
}
- properties加配置:
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://locahost:3306/fmc_patrol1
spring.datasource.username=1111
spring.datasource.password=1111
- test测试
@Autowired
UserInfoServiceImpl userInfoService;
@Test
public void search(){
userInfoService.serachUserInfo();
}
使用mapper.xml方式
在全注解方式基础上修改代码,
- 修改mapper
import cn.fenzeshidai.patrol.xunshi.model.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
@Mapper
public interface UserInfoMapper{
// @Select("SELECT name,home from user limit 1")
UserInfo search();
}
- 修改porperties配置:
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://locahost:3306/fmc_patrol1
spring.datasource.username=1111
spring.datasource.password=1111
mybatis.mapper-locations=classpath:/mybatis/mapper/*.xml
- 增加xml文件,文件位置(自己随便放):classpath:/mybatis/mapper/*.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" >
<!--namespace 是你的类全名-->
<mapper namespace="cn.fenzeshidai.patrol.xunshi.mapper.UserInfoMapper">
<!--id 调用的方法名,resultType是你的返回类型-->
<select id="search" resultType="cn.fenzeshidai.patrol.xunshi.model.UserInfo" >
SELECT name,home from user limit 1
</select>
</mapper>
使用druid:连接池
- 常用参数 (附录参数解释)
参数 | 说明 |
InitialSize | 初始化连接数 |
MinIdle | 最小的空闲连接数 |
MaxActive | 最大允许的连接数 |
MaxWait | 获取连接等待的超时时间 |
TimeBetweenEvictionRunsMillis | 检测需要关闭的空闲连接的间隔时间 |
KeepAlive | 设置是否保持连接活动 |
MinEvictableIdleTimeMillis | 连接在池中的最小生存时间 |
TestWhileIdle | 设置空闲时是否检测连接可用性 |
TestOnBorrow | 设置获取连接时是否检测连接可用性 |
ValidationQuery | 设置检测连接是否可用的sql语句 |
ValidationQueryTimeout | 校验连接是否可用的超时时间 |
ConnectionErrorRetryAttempts | 设置获取连接出错时的自动重连次数 |
FailFast | 设置获取连接出错时是否马上返回错误,true为马上返回 |
NotFullTimeoutRetryCount | 设置获取连接时的重试次数,-1为不重试 |
BreakAfterAcquireFailure | true表示向数据库请求连接失败后,就算后端数据库恢复正常也不进行重连,客户端对pool的请求都拒绝掉.false表示新的请求都会尝试去数据库请求connection.默认为false |
- pom引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
- yaml文件:
spring:
datasource:
druid:
#初始化连接数
initial-size: 5
# 最小的空闲连接数
min-idle: 5
#最大允许的连接数
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
#设置空闲时是否检测连接可用性
testWhileIdle: true
#设置获取连接时是否检测连接可用性
testOnBorrow: false
#归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
testOnReturn: false
#配置数据库web监控(地址:locahost:8080/druid)
stat-view-servlet:
enabled: true
url-pattern: /druid/*
login-username: admin
login-password: admin
多数据源
- 更改propertices文件
#dab1
spring.datasource.druid.db1.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.db1.url=jdbc:mysql://localhost:3306/fmc_patrol1
spring.datasource.druid.db1.username=admin
spring.datasource.druid.db1.password=fbJc9x6TBJEMvsbv
#db2
spring.datasource.druid.db2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.db2.url=mysql://localhost:3306/fmc_patrol
spring.datasource.druid.db2.username=admin
spring.datasource.druid.db2.password=B8D6963C-8BAD
- 增加配置文件
- 文件夹、src/main/java/com.xxxxx/config
//db1配置文件
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "cn.fenzeshidai.patrol.xunshi.mapper.db1", sqlSessionTemplateRef = "db1SqlSessionTemplate")
//basePackages=你的mapper路径,sqlSessionTemplateRef 这个不知道啥意思,貌似像命名
public class LogDbConfig {
@Bean(name = "db1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.db1")
//主配置(多源需要设定)
@Primary
//构建数据源
public DataSource db1DataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@Primary
//注册SqlSessionFactory
public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mybatis/mapper/db1/*.xml"));
return bean.getObject();
}
@Bean
@Primary
public DataSourceTransactionManager db1TransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean
@Primary
public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
db2:
package cn.fenzeshidai.patrol.xunshi.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "cn.fenzeshidai.patrol.xunshi.mapper.db2",sqlSessionTemplateRef ="db2SqlSessionTemplate")
public class MediaDataConfig {
@Bean(name = "db2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.db2")
public DataSource db2DataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mybatis/mapper/db2/*.xml"));
return bean.getObject();
}
@Bean
public DataSourceTransactionManager db2TransactionManager(@Qualifier("db2DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean
public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
- mapper文件夹在细分文件,db1,db2,与配置文件中的basePackage对应
- mapper xml文件也是这样的分类
文件目录