前面讲了Mysql数据库修改为Postgresql。
修改完成后,Thingsboard还不能跟JEECG共用数据库,存在字段类型不匹配的问题。
这里以device表为例操作。
在原有数据库表基础上添加以下字段,保存。
ALTER TABLE "public"."device" ADD COLUMN create_by varchar(50);
ALTER TABLE "public"."device" ADD COLUMN create_time timestamp(6);
ALTER TABLE "public"."device" ADD COLUMN update_by varchar(50);
ALTER TABLE "public"."device" ADD COLUMN update_time timestamp(6);
ALTER TABLE "public"."device" ADD COLUMN sys_org_code varchar(64);
COMMENT ON COLUMN "public"."device"."create_by" IS '创建人';
COMMENT ON COLUMN "public"."device"."create_time" IS '创建日期';
COMMENT ON COLUMN "public"."device"."update_by" IS '更新人';
COMMENT ON COLUMN "public"."device"."update_time" IS '更新日期';
COMMENT ON COLUMN "public"."device"."sys_org_code" IS '所属部门';
因为Thingsboard的id类型为uuid,在JEECG-Boot工程中ctrl+shift+R全局检索@TableId(type = IdType.ASSIGN_ID
将*.javai下的@TableId(type = IdType.ASSIGN_ID全部替换为@TableId(type = IdType.ASSIGN_UUID
打开JEECG,ONLINE表单开发,导入数据库表,选择device,代码生成。
url为jeecg_config.properties中的project_path,生成的代码会在project_path下的bussi_package包下。
将vue3下的所有文件复制到JEECG-Vue3工程下的src/views下新建的device包下
在JEECG-Boot工程中,修改生成的实体类,将主键id@TableId(type = IdType.UUID),改为@TableId(type = IdType.INPUT),属性类型设置为java.util.UUID,数据库中其他属于uuid类型的字段也分别设置为java.util.UUID,属于jsonb的数据设置为java.lang.Object类型,并写deviceData的setDeviceData方法,将传入的Object类型数据通过JSON.parse()方法转为json格式。
package org.jeecg.modules.device.entity;
import java.io.Serializable;
import java.io.UnsupportedEncodingException;
import java.math.BigInteger;
import java.util.Date;
import java.math.BigDecimal;
import java.util.UUID;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.TableLogic;
import lombok.Data;
import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecg.common.aspect.annotation.Dict;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
/**
* @Description: 设备
* @Author: jeecg-boot
* @Date: 2023-05-29
* @Version: V1.0
*/
@Data
@TableName("device")
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="device对象", description="设备")
public class Device implements Serializable {
private static final long serialVersionUID = 1L;
/**主键*/
@TableId(type = IdType.INPUT)
@ApiModelProperty(value = "主键")
private java.util.UUID id;
/**创建人*/
@ApiModelProperty(value = "创建人")
private java.lang.String createBy;
/**创建日期*/
@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
@ApiModelProperty(value = "创建日期")
private java.util.Date createTime;
/**更新人*/
@ApiModelProperty(value = "更新人")
private java.lang.String updateBy;
/**更新日期*/
@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
@ApiModelProperty(value = "更新日期")
private java.util.Date updateTime;
/**所属部门*/
@ApiModelProperty(value = "所属部门")
private java.lang.String sysOrgCode;
/**创建时间*/
@Excel(name = "创建时间", width = 15)
@ApiModelProperty(value = "创建时间")
private BigInteger createdTime;
/**附加信息*/
@Excel(name = "附加信息", width = 15)
@ApiModelProperty(value = "附加信息")
private java.lang.String additionalInfo;
/**客户Id*/
@Excel(name = "客户Id", width = 15)
@ApiModelProperty(value = "客户Id")
private java.util.UUID customerId;
/**设备配置信息*/
@Excel(name = "设备配置信息", width = 15)
@ApiModelProperty(value = "设备配置信息")
private java.util.UUID deviceProfileId;
/**设备数据*/
@Excel(name = "设备数据", width = 15)
@ApiModelProperty(value = "设备数据")
private java.lang.Object deviceData;
/**类型*/
@Excel(name = "类型", width = 15)
@ApiModelProperty(value = "类型")
private java.lang.String type;
/**设备名称*/
@Excel(name = "设备名称", width = 15)
@ApiModelProperty(value = "设备名称")
private java.lang.String name;
/**搜索文本*/
@Excel(name = "搜索文本", width = 15)
@ApiModelProperty(value = "搜索文本")
private java.lang.String searchText;
/**租户ID*/
@Excel(name = "租户ID", width = 15)
@ApiModelProperty(value = "租户ID")
private java.util.UUID tenantId;
/**固件ID*/
@Excel(name = "固件ID", width = 15)
@ApiModelProperty(value = "固件ID")
private java.util.UUID firmwareId;
/**软件ID*/
@Excel(name = "软件ID", width = 15)
@ApiModelProperty(value = "软件ID")
private java.util.UUID softwareId;
/**外部ID*/
@Excel(name = "外部ID", width = 15)
@ApiModelProperty(value = "外部ID")
private java.util.UUID externalId;
/**外部ID*/
@Excel(name = "标签", width = 15)
@ApiModelProperty(value = "标签")
private java.lang.String label;
public void setDeviceData(Object deviceData) {
// this.deviceData = deviceData;
try {
//查询转换
this.deviceData = JSON.parse(deviceData.toString());
} catch (Exception e) {
//新增转换
this.deviceData = JSONArray.toJSON(deviceData);
}
}
}
因为java中的JDBCType中UUID和JSON类型识别为OTHER类型,所有会存在数据转换异常,需要设置mybatisplus映射器,与postgresql数据库做映射。
这里我们分别创建UUIDTypeHandler和JsonbTypeHandler
package org.jeecg.modules.handler;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;
public class UUIDTypeHandler extends BaseTypeHandler<UUID> {
public UUIDTypeHandler() {
super();
}
@Override
public void setParameter(PreparedStatement ps, int i, UUID parameter, JdbcType jdbcType) throws SQLException {
super.setParameter(ps, i, parameter, jdbcType);
}
@Override
public UUID getResult(ResultSet rs, String columnName) throws SQLException {
return super.getResult(rs, columnName);
}
@Override
public UUID getResult(ResultSet rs, int columnIndex) throws SQLException {
return super.getResult(rs, columnIndex);
}
@Override
public UUID getResult(CallableStatement cs, int columnIndex) throws SQLException {
return super.getResult(cs, columnIndex);
}
@Override
public UUID getNullableResult(ResultSet arg0, String arg1)
throws SQLException {
String vStr = arg0.getString(arg1);
return getValue(vStr);
}
@Override
public UUID getNullableResult(ResultSet arg0, int arg1) throws SQLException {
String vStr = arg0.getString(arg1);
return getValue(vStr);
}
@Override
public UUID getNullableResult(CallableStatement arg0, int arg1)
throws SQLException {
String vStr = arg0.getString(arg1);
return getValue(vStr);
}
@Override
public void setNonNullParameter(PreparedStatement arg0, int arg1,
UUID arg2, JdbcType arg3) throws SQLException {
if(null != arg2){
arg0.setObject(arg1, arg2);
}
}
private UUID getValue(String vStr){
if(notNull(vStr)){
return UUID.fromString(vStr);
}
return null;
}
private boolean notNull(String arg1){
return (null != arg1 && !"".equals(arg1));
}
}
package org.jeecg.modules.handler;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@MappedTypes({ Object.class })
public class JsonbTypeHandler extends BaseTypeHandler<Object> {
private static final PGobject jsonObject = new PGobject();
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType)
throws SQLException {
if (ps != null) {
jsonObject.setType("jsonb");
jsonObject.setValue(parameter.toString());
ps.setObject(i, jsonObject);
}
}
@Override
public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
return rs.getObject(columnName);
}
@Override
public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return rs.getObject(columnIndex);
}
@Override
public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return cs.getObject(columnIndex);
}
}
分别处理UUID类型的属性和Jsonb类型的属性。
在MybatisInterceptor拦截器中加入判断
TableId annoTableField = field.getAnnotation(TableId.class);
if(null != annoTableField && annoTableField.type() == IdType.INPUT) {
field.setAccessible(true);
field.set(parameter, UUID.randomUUID());
}
随后编辑xml文件,在需要做类型映射的属性中加入如下内容typeHandler为刚自定义的处理器路径。
jdbcType=OTHER,typeHandler=org.jeecg.modules.handler.UUIDTypeHandler
jdbcType=OTHER,typeHandler=org.jeecg.modules.handler.JsonbTypeHandler
<?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="org.jeecg.modules.device.mapper.DeviceMapper">
<insert id="insert" parameterType="org.jeecg.modules.device.entity.Device">
INSERT INTO device
(id, create_by, update_by, update_time, sys_org_code, created_time, additional_info, customer_id, device_profile_id,
device_data, type, name, search_text, tenant_id, firmware_id, software_id, external_id, label)
VALUES (
#{id,jdbcType=OTHER,typeHandler=org.jeecg.modules.handler.UUIDTypeHandler},
#{createBy},
#{updateBy},
#{updateTime},
#{sysOrgCode},
#{createdTime},
#{additionalInfo},
#{customerId,jdbcType=OTHER,typeHandler=org.jeecg.modules.handler.UUIDTypeHandler},
#{deviceProfileId,jdbcType=OTHER,typeHandler=org.jeecg.modules.handler.UUIDTypeHandler},
#{deviceData,jdbcType=OTHER,typeHandler=org.jeecg.modules.handler.JsonbTypeHandler},
#{type},
#{name},
#{searchText},
#{tenantId,jdbcType=OTHER,typeHandler=org.jeecg.modules.handler.UUIDTypeHandler},
#{firmwareId,jdbcType=OTHER,typeHandler=org.jeecg.modules.handler.UUIDTypeHandler},
#{softwareId,jdbcType=OTHER,typeHandler=org.jeecg.modules.handler.UUIDTypeHandler},
#{externalId,jdbcType=OTHER,typeHandler=org.jeecg.modules.handler.UUIDTypeHandler},
#{label}
)
</insert>
</mapper>
然后重启项目,即可创建设备,Thingsboard和JEECG同步。
实现平台使用同一Postgresql数据库。