数据推送项目小总结
1.多数据源配置
1.1核心配置
application.yml
gisq:
platform:
auth:
checkToken: false
web:
cross:
enabled: false #跨域开启
db:
config:
decrypt: false #密码是否加密
key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAJYQJiqpte76TBikKi/vxbaYSphFDs6kxRd0X/lJobWk2pg3uMJOlM2IrOI+Szy28dzeCDOyGCv/9/CD5yCZUdECAwEAAQ==
dynamic:
enabled: true #是否开启多数据源
enabledDefault: false #当数据源未查询到是否使用默认数据源,默认false;
dynamicDataSources:
slave: # 本地
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.168.11.154:3306/bdcdjjg?useUnicode=true&characterEncoding=utf8&serverTimzone=GMT%2b8&useSSL=false
username: root
password: 123456
cdsq: # MYSQL
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.168.11.154:3306/bdcdjjg?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2b8&useSSL=false
username: root
password: 123456
cert: # MYSQL
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.168.11.154:3306/bdcdjjg?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2b8&useSSL=false
username: root
password: 123456
qzk: # 前置库
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.168.11.154:3306/bdcdjjg?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2b8&useSSL=false
username: root
password: 123456
1.2注解使用
@DynamicSwitchDataSource(dataSourceId = "slave")
@Override
@DynamicSwitchDataSource(dataSourceId = "slave")
public int updateCdsqStats(Integer tszt, Integer tscs) {
return cdsqDAO.updateCdsqStats(tszt,tscs);
}
//dataSourceId = "slave" 对应yml配置文件中数据源的名称
2.两个List集合左连接
2.1案例
listLeft将listRight中xzqbm字段相等的进行左连接
左连接核心代码 利用java8 strem()处理
for (TSzshCodeDO t : listLeft) {
List<CdsqDO> cdsqDOList = listRight.stream()
.filter(right -> right.getXzqbm().equals(t.getXzqbm()))
.collect(Collectors.toList());
实体类处理 com.gisquest.realestate.data.supervise.digital.entity.TSzshCodeDO
//待会要赋值进去的对象
private List<TCertLogDO> tCertLogDOList;
//待会要赋值进去的对象
private List<CdsqDO> cdsqDOList;
处理核心代码
注意:List<TSzshCodeDO> res = new ArrayList<>();
遍历结果要用List集合接收
如果TSzshCodeDO res = new TSzshCodeDO();
前端数据处理的返回结果只会显示一条数据,但数据库实际上是插入的全部(多条)数据
/**
* 将需要归集的数据插入到本地库t_szsh_push
* @param baseQuartzProperties 调度的配置条件
* @param currentDate 指定某个日期,用于查询这个日期的数据统计
* @return
*/
@Override
public GenericResponse<Object> pushData(BaseQuartzProperties baseQuartzProperties, String currentDate) {
List<TSzshCodeDO> listLeft = tSzshCodeService.selectCodeDate(digitalProperties.getBaseQuartz().getCdsqSzshBsm(), digitalProperties.getBaseQuartz().getPushByQxdmXzqjb());
List<CdsqDO> listRight = cdsqDataService.selectCdsqCodeAndNum(digitalProperties.getBaseQuartz().getSqzt(), currentDate);
boolean flat = false;
// LogbackHelper.logDigitalSociety.info(listRight.toString());
/**
* left join
* 实现将xzqbm字段值相等的两个List结果进行左连接
* listLeft归集listRight中的字段
*/
List<TSzshCodeDO> res = new ArrayList<>();
for (TSzshCodeDO t : listLeft) {
List<CdsqDO> cdsqDOList = listRight.stream()
.filter(right -> right.getXzqbm().equals(t.getXzqbm()))
.collect(Collectors.toList());
// LogbackHelper.logDigitalSociety.info(cdsqDOList.toString());
t.setCdsqDOList(cdsqDOList);
LogbackHelper.logDigitalSociety.info(t.toString());
//判断list不为null且不为空
if (CollectionUtils.isNotEmpty(t.getCdsqDOList())) {
LogbackHelper.logDigitalSociety.info(cdsqDOList.toString());
LogbackHelper.logDigitalSociety.info(t.toString());
LogbackHelper.logDigitalSociety.info("cdsq表数据正在插入t_szsh_push......");
t.setSysSjzt("insert");
t.setServiceTime(currentDate);
tSzshCodeService.insertCdsqDataToPush(t);
LogbackHelper.logDigitalSociety.info("cdsq表数据插入t_szsh_push完成");
flat = true;
res.add(t);
}
}
if (flat == true) {
return GenericResponse.succeed(res, "查询成功,插入t_t_szsh_push成功");
} else {
return GenericResponse.succeed(null, "这天没有数据,插入t_t_szsh_push失败,等会再试试");
}
}
3.Date日期字段处理
3.1@DateTimeFormat@JsonFormat
参考https://www.cnblogs.com/mracale/p/9828346.html
https://blog.csdn.net/coder1998/article/details/106450905/
ERROR:
Failed to convert value of type java.lang.String to required type java.util.Date 报错
实体类
注解@JsonFormat主要是后台到前台的时间格式的转换
注解@DataFormAT主要是前后到后台的时间格式的转换
错误翻译:
首先上面的错误的意思是 前台传递 string类型的数据,后台使用Date类型去接收,但是报错了。
解决之道:
springboot要解决这个问题很简单,在接收的字段上面,添加下面的注解 就OK
注解说明:
@DateTimeFormat 该注解自动会解析处理,会把字符串类型 按照格式yyyy-MM-dd HH:mm:ss 转换成时间类型
@JsonFormat这个注解是从数据库查询出来,返回到页面的的转换. 把时间类型 转换成JSON格式类型,前提取出进行展示.
@DateTimeFormat(pattern = "yyyy-MM-dd")//接收时间类型
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8") //返回时间类型
private Date service_time;
Pom.xml依赖
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-annotations</artifactId>
<version>2.8.8</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.8.8</version>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.13</version>
</dependency>
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.3</version>
</dependency>
3.2util.Date转sql.date
参考 https://blog.csdn.net/qq_36648082/article/details/100012238
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); //格式化规则
Date date = user.getTime(); //获得你要处理的时间 Date型
String strDate= sdf.format(date ); //格式化成yyyy-MM-dd格式的时间字符串
Date newDate =sdf.parse(strDate);
//最后转换成 java.sql.Date类型数据就可以了 注意:最开始时间数据类型是 java.util.Date类型
java.sql.Date resultDate = new java.sql.Date(newDate.getTime());
3.3将字符串格式转换成日期格式
将字符串格式“2020-02-02” 转换成日期Date格式“2020-02-02”
/**
* 将字符串格式“2020-02-02” 转换成日期Date格式“2020-02-02”
*
* @param sDate
* @return
*/
public static Date StringToDateForMate(String sDate) {
Date res = new Date();
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
res = sdf.parse(sDate);
} catch (Exception e) {
e.printStackTrace();
}
return res;
}
3.4获取当前时间的前一天
参考https://blog.csdn.net/weixin_34192993/article/details/92188291
https://www.cnblogs.com/zhuyeshen/p/12776778.html
/**
* 获取当前时间的前一天
* @return
*/
public static String getYesterDay() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = new Date();
Calendar now = Calendar.getInstance();
now.setTime(date);
System.out.println(sdf.format(now.getTime()));
now.add(Calendar.DAY_OF_MONTH, -1);
System.out.println(now.getTime());
String yesDay = sdf.format(now.getTime());
return yesDay;
}
Date today = new Date();//获取今天的日期
Calendar c = Calendar.getInstance();
c.setTime(today);
c.add(Calendar.DAY_OF_MONTH, 1);
Date tomorrow = c.getTime();//这是明天
c.setTime(today);
c.add(Calendar.DAY_OF_MONTH, -1);
Date yesterday = c.getTime();//这是昨天
4.mybatis
4.1#取值
参考https://blog.csdn.net/u014520006/article/details/91042947
使用mybatis报错:
Error setting non null for parameter #1 with JdbcType null .
使用了#{}的不能用这个双引号
报错#{}导致的The error may involve defaultParameterMap的问题
参考http://www.manongjc.com/detail/13-jepoqljrhtngpqk.html
直接取值使用 ${} 接受表名,#{}接受其他参数
<select id="selectCdsqErrorDataReToSzsh" resultType="com.gisquest.realestate.data.supervise.digital.entity.TSzshPushDO">
SELECT
app_name,function_name_1,service_num, service_time,service_type,service_area_code,
service_access,source_unit,source_unit_code,NOW() as handle_time ,sys_sjzt,sys_record_guid
FROM t_szsh_push
WHERE szsh_code_bsm=2 and tszt=2 and tscs <= ${maxTscs} LIMIT 0,${maxRows};
</select>
${maxTscs}
直接传值 用${}
mybatis 为我们提供了两种支持动态 sql 的语法:#{} 以及 ${}
#{} 的参数替换是发生在 DBMS 中,而 ${} 则发生在动态解析过程中
${} 会导致 sql 注入的问题,优先使用 #{}
比较运算符
参考:https://blog.csdn.net/qq_35868412/article/details/80238754
参考:https://www.jianshu.com/p/c5095ea46471
4.2select
返回结果不止一行 用map接收
column=”service_area_code” 数据库表的字段
property=”xzqbm” 实体类的字段
<resultMap id="CodeAndNum" type="com.gisquest.realestate.data.supervise.digital.entity.CdsqDO">
<result column="service_area_code" property="xzqbm" jdbcType="VARCHAR"/>
<result column="service_num" property="service_num" jdbcType="VARCHAR"/>
</resultMap>
<select id="selectCdsqCodeAndNum" resultMap="CodeAndNum">
select substring(xzqbm,1,4) as service_area_code,count(*) as service_num
from cdsq
where sqzt=#{sqzt} and substring(cjsj,1,10)= #{cjsj}
group by substring(xzqbm,1,4) limit 0,100;
</select>
返回结果不止一行还可以用 resutType
<select id="selectCdsqDataToSzsh" resultType="com.gisquest.realestate.data.supervise.digital.entity.TSzshPushDO">
SELECT
app_name,function_name_1,service_num, service_time,service_type,service_area_code,
service_access,source_unit,source_unit_code,NOW() as handle_time ,sys_sjzt,sys_record_guid
FROM t_szsh_push
WHERE szsh_code_bsm=2 and tszt=0 and service_time=#{service_time} LIMIT 0,100;
</select>
4.3insert &foreach
4.3.1用foreach遍历List类型集合
⚠️ 在foreach标签里写了( )就不用 open=”(“ close=”)”
<insert id="insertCdsqDataToQzk" parameterType="com.gisquest.realestate.data.supervise.digital.entity.TSzshPushDO">
insert into szsh_app_service_info(
app_name,function_name_1,service_num,service_time,service_type,service_area_code,
service_access, source_unit,source_unit_code,handle_time,sys_sjzt,sys_record_guid
)
values
<foreach collection="list" item="item" separator="," index="index">
(#{item.app_name},#{item.function_name_1},#{item.service_num},#{item.service_time},#{item.service_type}, #{item.service_area_code},
#{item.service_access},#{item.source_unit},#{item.source_unit_code},now(),#{item.sys_sjzt},#{item.sys_record_guid})
</foreach>
</insert>
4.3.2用foreach遍历Object对象,Object对象里有List集合字段
TSzshPushDO实体类
@Data
public class TSzshCodeDO implements Serializable {
/**
* 标识码,唯一主键
*/
private Long bsm;
/**
* 应用名称,如“不动产浙里登”
*/
private String appName;
/**
* 一级功能名称,如“权属查询”,“电子权证”等
*/
private String functionName1;
/**
* 服务类别(查询类/办理类)
*/
private String serviceType;
/**
* 服务入口(浙里办,微信,支付宝等)
*/
private String serviceAccess;
/**
* 来源部门(省自然资源厅(省海洋局))
*/
private String sourceUnit;
/**
* 来源部门单位
*/
private String sourceUnitCode;
/**
* 服务编码,自定义的属性,如(监管 jg_01,一窗 yc_01)
*/
private String serviceCode;
/**
* 行政区编码
*/
private String xzqbm;
/**
* 行政区名称
*
*/
private String xzqmc;
/**
* 数字社会标识码 1:电子权证 2:权属查询(查家庭/查个人)
*/
private String szshbsm;
/**
* 行政区级别
*/
private String xzqjb;
//待会要赋值进去的对象
private List<TCertLogDO> tCertLogDOList;
//待会要赋值进去的对象
private List<CdsqDO> cdsqDOList;
private static final long serialVersionUID = 1L;
}
foreach 里的collection写tCertLogDOList 字段属性名
<!-- 将表t_cert_log数据插入到t_szsh_push -->
<insert id="insertDataToPush" parameterType="com.gisquest.realestate.data.supervise.digital.entity.TSzshCodeDO">
insert into t_szsh_push(
szsh_code_bsm,sys_sjzt,sys_record_guid,app_name,function_name_1,
service_type,service_access,source_unit,source_unit_code,service_code,
service_area_code,service_num,service_time,handle_time,gxsj,tszt,tscs,tssj
)
values(
#{bsm},#{sysSjzt},CONCAT(date_format(now(),'%Y%m%d'),'_',#{xzqbm},'01'),#{appName},#{functionName1},#{serviceType},#{serviceAccess},
#{sourceUnit},#{sourceUnitCode},#{serviceCode},concat(#{xzqbm},'01'),
<foreach collection="tCertLogDOList" item="item" index="">
#{item.service_num}
</foreach>,#{serviceTime},now(),now(),'0','0',now()
)
</insert>
4.4 resultType与parameterType属性的区别
4.5 selectkey标签的作用
<selectKey keyProperty="bsmMain" order="AFTER" resultType="java.lang.Long">
SELECT LAST_INSERT_ID() as bsmMain
</selectKey>
5.定时任务调度
5.1在定时调度的service层使用@Autowired对应的Impl取值为null
处理
使用private 申明 其他Service层的service,然后创建带参数的构造方法
用this.XXXService.XX来调用接口方法
⚠️因为平时在调用service层时都是在controller中,有配置扫描注入,spring会根据配置自动注入所依赖的服务层。
但因我们写的工具类不属于controller层,所以当所写接口需要调用服务层是,常常会为NULL。
private CdsqAppService cdsqAppService;
private DigitalProperties digitalProperties;
@Autowired
public CdsqQuartzServiceImpl(CdsqAppService cdsqAppService, DigitalProperties digitalProperties) {
this.cdsqAppService = cdsqAppService;
this.digitalProperties = digitalProperties;
}
this.cdsqAppService.pushDataXXX;
5.2static类型autowired 注入失败
参考:https://www.cnblogs.com/zhouyeqin/p/8150594.html
解决:
@Autowired
public CdsqAppService cdsqAppService;
private static CdsqAppService cdsqAppService1;
@PostConstruct
private void initStaticCdsqAppService(){
cdsqAppService1 = this.cdsqAppService;
}
GenericResponse<Object> res = this.cdsqAppService1.selectDataToPush(baseQuartzProperties, currentDate);
使用 this.cdsqAppService1
调用service方法
@Autowired
``@PostConstruct`
6.SpingBoot启动问题
6.1Controller层问题
报错
org.springframework.web.bind.MissingPathVariableException: Missing URI template variable ‘date‘ for
解决
@RequestMapping注解中 value的参数要用{ }
@ApiOperation(value = "cdsq数据推到push表")
@RequestMapping(value = "/cdsqpush/{date}", method = RequestMethod.GET)
6.2Controller层的路径开始不能一样
错误例子
@RequestMapping(value = "/cdsqpush/{date1}", method = RequestMethod.GET)
@RequestMapping(value = "/cdsqpush/{date1}", method = RequestMethod.GET)
/cdsqpush
相同会报错
正确例子
@RequestMapping(value = "/cdsqpush1/{date1}", method = RequestMethod.GET)
@RequestMapping(value = "/cdsqpush2/{date1}", method = RequestMethod.GET)
附录
1.Swagger接口地址
http://127.0.0.1:39129/swagger-ui/index.html
2.Java程序生成的Jar在Linux和Windows兼容问题
Error
Invalid or corrupt jarfile
参考https://blog.csdn.net/u012477338/article/details/79216607
分析
复制jar包的时候不能通过qq传输,也不知直接从windows直接拖到其他地方
解决
通过scp从windows传输到linux
Administrator@MTC-20210705EUZ MINGW64 ~/Desktop
$ scp ~/Desktop/test-jar.zip sunyun@192.168.50.122:~/Desktop
(sunyun@192.168.50.122) Password:
test-jar.zip 100% 68MB 4.0MB/s 00:17
然后在linux下解压启动就不会报错了