pom文件引入依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
application.yaml
spring:
datasource:
remote-sqlserver1:
...
remote-xugu1:
...
remote-xugu2:
...
remote-xugu3:
username: SYSDBA
password: SYSDBA
driver-class-name: com.xugu.cloudjdbc.Driver
url: jdbc:xugu://ip:port/SYSTEM?time_zone=GMT-8:00
druid:
initial-size: 10 # 初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
min-idle: 10 # 最小连接池数量
maxActive: 200 # 最大连接池数量
maxWait: 60000 # 获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置
timeBetweenEvictionRunsMillis: 60000 # 关闭空闲连接的检测时间间隔.Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。
minEvictableIdleTimeMillis: 300000 # 连接的最小生存时间.连接保持空闲而不被驱逐的最小时间
validationQuery: SELECT 1 FROM DUAL # 验证数据库服务可用性的sql.用来检测连接是否有效的sql 因数据库方言而差, 例如 oracle 应该写成 SELECT 1 FROM DUAL
testWhileIdle: true # 申请连接时检测空闲时间，根据空闲时间再检测连接是否有效.建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRun
testOnBorrow: false # 申请连接时直接检测连接是否有效.申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn: false # 归还连接时检测连接是否有效.归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
poolPreparedStatements: true # 开启PSCache
maxPoolPreparedStatementPerConnectionSize: 20 #设置PSCache值
connectionErrorRetryAttempts: 3 # 连接出错后再尝试连接三次
breakAfterAcquireFailure: true # 数据库服务宕机自动重连机制
timeBetweenConnectErrorMillis: 300000 # 连接出错后重试时间间隔
asyncInit: true # 异步初始化策略
remove-abandoned: true # 是否自动回收超时连接
remove-abandoned-timeout: 1800 # 超时时间(以秒数为单位)
transaction-query-timeout: 6000 # 事务超时时间
filters: stat,wall,log4j2
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
web-stat-filter:
enabled: true
url-pattern: "/*"
exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
mybatisplus配置类
@Configuration
@MapperScan(basePackages = "cma.sxqxgxw.*.mapper", sqlSessionFactoryRef = "remoteXuGu1SqlSessionFactory")
public class RemoteXuGu1DatasourceConfig {
/**
* 新增分页拦截器，并设置数据库类型为mysql
* PaginationInnerInterceptor MybatiPlus的分页插件
*/
@Bean(name = "mybatisPlusInterceptor")
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
@Bean(name = "remoteXuGu1DatasourceProperties")
//application.yml配置的数据源前缀
@ConfigurationProperties(prefix = "spring.datasource.remote-xugu1")
public DataSourceProperties remoteXuGu1DatasourceProperties(){
return new DataSourceProperties();
}
@Bean(name = "remoteXuGu1Datasource")
//使用数据源属性类构建一个数据源
public DataSource remoteXuGu1Datasource(@Qualifier("remoteXuGu1DatasourceProperties") DataSourceProperties dataSourceProperties) {
return dataSourceProperties.initializeDataSourceBuilder().build();
}
@Bean("remoteXuGu1SqlSessionFactory")
public SqlSessionFactory remoteXuGu1SqlSessionFactory(@Qualifier("remoteXuGu1Datasource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
//设置字段值自动填充
bean.setGlobalConfig(new GlobalConfig().setBanner(false).setMetaObjectHandler(new MybatisPlusAutoFillConfig()));
//设置mybatis-plus分页插件
bean.setPlugins(mybatisPlusInterceptor());
// 设置标准控制台输出SQL（开发环境中使用）
{
MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
mybatisConfiguration.setLogImpl(StdOutImpl.class);
bean.setConfiguration(mybatisConfiguration);
}
// 配置Mapper.xml映射文件匹配路径
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
return bean.getObject();
}
@Bean("remoteXuGu1SqlSessionTemplate")
public SqlSessionTemplate remoteXuGu1SqlSessionTemplate(@Qualifier("remoteXuGu1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
字段值自动填充配置
/**
* 字段值自动填充配置
* 需要在多数据源配置里添加此配置方可生效;
* -------------------------------------
* IGNORED 忽略判断，所有字段都进行更新和插入
* NOT_NULL只更新和插入非NULL值
* NOT_EMPTY 只更新和插入非NULL值且非空字符串
* NEVER 永远不进行更新和插入
* DEFAULT 默认NOT_NULL
*/
@Component
public class MybatisPlusAutoFillConfig implements MetaObjectHandler {
// 新增时填充
@Override
public void insertFill(MetaObject metaObject) {
this.setFieldValByName("deleteFlag", 0, metaObject);
this.setFieldValByName("createTime", new Date(), metaObject);
}
// 修改时填充
@Override
public void updateFill(MetaObject metaObject) {
this.setFieldValByName("updateTime", new Date(), metaObject);
}
}
mapper文件
package com.meteorological.mapper.remote.xugu3;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.meteorological.entity.api.ApiElementsDO;
import org.springframework.stereotype.Repository;
@Repository
public interface IApiElementsMapper extends BaseMapper<ApiElementsDO> {
}
DO实体类
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.*;
import java.io.Serializable;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@TableName("API_ELEMENTS")
public class ApiElementsDO implements Serializable {
/**
* id
*/
@TableId(value = "ID", type = IdType.AUTO)
private Integer id;
/**
* 要素代码
*/
@TableField(value = "ELEMENT_CODE")
private String elementCode;
/**
* 要素名称
*/
@TableField(value = "ELEMENT_NAME")
private String elementName;
/**
* 单位
*/
@TableField(value = "UNIT")
private String unit;
@TableField(value = "DATA_CODE")
private String dataCode;
}
service中引入mapper
@Component
public class TRadar {
private final IApiElementsMapper apiElementsMapper;
Log log = LogFactory.getLog(com.meteorological.service.task.TRadar.class);
@Autowired
public TRadar(IApiElementsMapper apiElementsMapper) {
this.apiElementsMapper = apiElementsMapper;
}
@PostConstruct
@Scheduled(cron = "0 5/30 * * * *")
public void Task100() {
ApiElementsDO apiElementsDO = apiElementsMapper.selectById(1);
System.out.println(apiElementsDO);
}
}
自定义TypeHandler
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
@MappedTypes({Date.class})
@MappedJdbcTypes({JdbcType.DATE})
public class DateTimeWithTImeZoneTypeHandler extends BaseTypeHandler<Date> {
Log log = LogFactory.getLog(DateTimeWithTImeZoneTypeHandler.class);
/**
* 用于定义在Mybatis设置参数时该如何把Java类型的参数转换为对应的数据库类型
* @param ps 当前的PreparedStatement对象
* @param i 当前参数的位置
* @param parameter 当前参数的Java对象
* @param jdbcType 当前参数的数据库类型
*/
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
ps.setTimestamp(i, new Timestamp(parameter.getTime()));
}
/**
* 用于在Mybatis获取数据结果集时如何把数据库类型转换为对应的Java类型
*
* @param rs 当前的结果集
* @param columnName 当前的字段名称
* @return 转换后的Java对象
* @throws SQLException the SQL exception
*/
@Override
public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
String ret = rs.getString(columnName);
try {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S X");
return dateFormat.parse(ret);
} catch (ParseException e) {
log.fatal(e.getMessage());
return null;
}
}
/**
* 用于在Mybatis通过字段位置获取字段数据时把数据库类型转换为对应的Java类型
* @param rs 当前的结果集
* @param columnIndex 当前字段的位置
* @return 转换后的Java对象
*/
@Override
public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
System.out.println();
return null;
}
/**
* 用于Mybatis在调用存储过程后把数据库类型的数据转换为对应的Java类型
* @param cs 当前的CallableStatement执行后的CallableStatement
* @param columnIndex 当前输出参数的位置
* @return
* @throws SQLException
*/
@Override
public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
System.out.println();
return null;
}
}
DO类
autoResultMap = true
@TableField(value = “create_time”, typeHandler = DateTimeWithTImeZoneTypeHandler.class)
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@TableName(value = "CUSTOM_ZONE_GROUP", autoResultMap = true)
public class CustomZoneGroupDO implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 主键id
*/
@TableId(value = "id",type = IdType.AUTO)
private Integer id;
......
/**
* 创建时间
*/
@TableField(value = "create_time", typeHandler = DateTimeWithTImeZoneTypeHandler.class)
private Date createTime;
}
service实现类
@Override
public Result<Object> getSelfCustomZoneGroupByTimeRange(String timeRange, Integer page, Integer pageSize) {
//token获取用户信息
UsernamePasswordAuthenticationToken authentication = (UsernamePasswordAuthenticationToken) SecurityContextHolder.getContext().getAuthentication();
JWTUser jwtUser = (JWTUser) authentication.getPrincipal();
String dateString = timeRange.substring(1, timeRange.length() - 1); // 去掉中括号
String[] dateArray = dateString.split(",");
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
Date startDate;
Date endDate;
try {
startDate = format.parse(dateArray[0]);
endDate = format.parse(dateArray[1]);
} catch (ParseException e) {
return Result.error(CodeMsg.ORDER_TIME_FORMAT);
}
LambdaQueryWrapper<CustomZoneGroupDO> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(CustomZoneGroupDO::getUploadUserId, jwtUser.getUserDO().getUuid());
queryWrapper.lt(CustomZoneGroupDO::getCreateTime, endDate);
queryWrapper.gt(CustomZoneGroupDO::getCreateTime, startDate);
queryWrapper.orderByDesc(CustomZoneGroupDO::getCreateTime);
int pageInt = page != null && page > 0 ? page : 1;
int pageSizeInt = pageSize != null && pageSize > 0 ? pageSize : 10;
Page<CustomZoneGroupDO> pageDo = new Page<>(pageInt, pageSizeInt);
Page<CustomZoneGroupDO> customZoneGroupDOPage = customZoneGroupMapper.selectPage(pageDo, queryWrapper);
// DO 转 VO （Page对象）
Page<CustomZoneGroupVO> customZoneGroupVOPage = CustomZoneGroupDOMapper.INSTANCE.toVOPage(customZoneGroupDOPage);
return Result.success(customZoneGroupVOPage);
}
DO转VO
/*
* 本文件创建的位置要根据转换目标来定；比如是DO转VO，则写在DO的目录里，如果是DTO转DO，则写到DTO下
*/
import cma.sxqxgxw.productdata.vo.CustomZoneGroupVO;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.mapstruct.Mapper;
import org.mapstruct.Mapping;
import org.mapstruct.ReportingPolicy;
import org.mapstruct.factory.Mappers;
import java.util.List;
/**
* 注意：unmappedTargetPolicy
* ERROR：任何未映射的目标属性都将使构建失败–这可以避免意外的未映射字段
* WARN：（默认）构建期间的警告消息
* IGNORE：无输出或错误
*/
@Mapper(unmappedTargetPolicy = ReportingPolicy.ERROR)
public interface CustomZoneGroupDOMapper {
CustomZoneGroupDOMapper INSTANCE = Mappers.getMapper(CustomZoneGroupDOMapper.class);
List<CustomZoneGroupVO> toVO(List<CustomZoneGroupDO> metaDataDO);
@Mapping(target = "optimizeJoinOfCountSql", ignore = true)
Page<CustomZoneGroupVO> toVOPage(Page<CustomZoneGroupDO> page);
}