1 建表sql
CREATE TABLE `dwd_t_dhzy` (
`zyid` varchar(108) NOT NULL COMMENT "作业ID（主键，36位UUID，用于增量同步和逻辑删除标记）",
`aqjdr` varchar(150) NULL COMMENT "安全交底人",
`sqdw` varchar(300) NULL COMMENT "作业申请单位",
`jsjdr` varchar(1500) NULL COMMENT "接受交底人（关联多个时英文逗号隔开）",
`changed_cause` varchar(1500) NULL COMMENT "作业变更原因（作业变更为是时，必填）",
`latitude` varchar(765) NULL COMMENT "作业地点纬度（2000国家大地坐标系）",
`zyryxx` varchar(1500) NULL COMMENT "作业人员信息（姓名、身份证号、特殊作业工种、特殊工作证号，关联多个时英文逗号隔开。例：张三:32013020083058,电焊工，T3201320083058）",
`sjqttszylx` varchar(150) NULL COMMENT "涉及其它特殊作业证类型（若涉及其它特殊作业时必填，见附录E特殊作业类型附录表细类编码）",
`zyfzr` varchar(150) NULL COMMENT "作业负责人",
`create_by` varchar(600) NULL COMMENT "创建人",
`zyzbh` varchar(150) NULL COMMENT "作业证编号",
`update_time` datetime NULL COMMENT "最后修改时间（新创建的数据和创建时间相同）",
`fxbs` varchar(600) NULL COMMENT "风险辨识（01：物体打击；02：机械伤害；03：车辆伤害；04：起重伤害；05：高处坠落；06：中毒和窒息；07：触电；08：淹溺；09：灼烫；10：火灾；11：坍塌；12：透水；13：放炮；14：冒顶片帮；15：火药爆炸；16：瓦斯爆炸；17：锅炉爆炸；18：容器爆炸；19：其他爆炸；20：其他；可多选，关联多个时英文逗号隔开）",
`sqr` varchar(60) NULL COMMENT "作业申请人",
`major_person_mobile` varchar(60) NULL COMMENT "负责人联系方式（负责人手机号码）",
`zydwlx` varchar(3) NULL COMMENT "作业单位类型（0：内部；1：第三方/承包商）",
`social_credit_code` varchar(1500) NULL COMMENT "施工单位统一社会信息代码（当是承包商作业时，需上传，多个通过英文逗号隔开）",
`ysr` varchar(150) NULL COMMENT "验收人",
`zyssjssj` datetime NULL COMMENT "作业结束时间（作业实际结束之前，填写作业计划结束时间，作业实际结束之后，更新为实结束时间）",
`company_code` varchar(27) NULL COMMENT "企业编码",
`zywz` varchar(300) NULL COMMENT "作业位置（动火地点及动火部位）",
`update_by` varchar(600) NULL COMMENT "最后修改人",
`longitude` decimal(9,6) NULL COMMENT "作业地点经度（2000国家大地坐标系）",
`sfsjqttszy` varchar(3) NULL COMMENT "是否涉及的其他特殊作业（0：否；1：是）",
`jhrgw` varchar(1500) NULL COMMENT "监护人岗位",
`create_time` datetime NULL COMMENT "创建时间",
`jhrzjbh` varchar(1500) NULL COMMENT "监护人证件编号",
`is_changed` varchar(3) NULL COMMENT "作业是否变更过（0否；1是）",
`dqzt` varchar(3) NULL COMMENT "当前状态（0未签发；1已签发；3已验收；4作废；5撤销）",
`zyzbfw` varchar(1500) NULL COMMENT "作业坐标范围（2000国家大地坐标系）",
`sjqttszyzbh` varchar(3000) NULL COMMENT "涉及的其他特殊作业安全作业证编号（涉及的其他特殊作业时，填写其他作业证编号。关联多个时英文逗号隔开）",
`dhzyjb` varchar(6) NULL COMMENT "动火作业级别（01：特级；02：一级；03：二级）",
`zyryid` varchar(1500) NULL COMMENT "作业人员编码（此处上传人员定位基础信息表中的人员ID，多个人员时ID用英文逗号隔开）",
`fxyfcs` varchar(6000) NULL COMMENT "风险预防措施",
`zydwmc` varchar(300) NULL COMMENT "作业单位名称（施工单位名称，通过英文逗号隔开）",
`jhr` varchar(1500) NULL COMMENT "监护人（监护人姓名，存在多人时，英文逗号隔开）",
`job_id` varchar(108) NULL COMMENT "作业活动ID（如计划作业，需关联作业活动ID）",
`fhyp` varchar(60) NULL COMMENT "防护用品（1：安全帽；2：防静电工作帽；3：空气呼吸器；4：防毒面具；5：过滤式呼吸器；6：眼面部防护具；7：安全鞋；8：防化学品鞋；9：防静电服；10：化学防护服；11：防电弧服；12：阻燃服；13：防化学品手套；14：防静电手套；15：绝缘手套；16：防护手套；17：安全带；18：安全绳；19：安全网；20：缓降装置；21：水平生命线装置；22：其他；可多选，关联多个时英文逗号隔开）",
`acceptanc_time` datetime NULL COMMENT "作业验收时间（当作业状态为已验收时，作业验收时间必填）",
`video_code` varchar(108) NULL COMMENT "视频摄像头编号（特殊作业关联的视频摄像头ID，需将企业固定摄像头、移动布控球接入省级视频平台，企业在企业端系统进行作业票报备时，在票证下选择对应的摄像头）",
`zysqsj` datetime NULL COMMENT "作业申请时间",
`zynr` varchar(600) NULL COMMENT "作业内容",
`zysskssj` datetime NULL COMMENT "作业开始时间（作业实际开始之前，填写作业计划开始时间，作业实际开始之后，更新为实际开始时间）",
`cancellen_cause` varchar(1500) NULL COMMENT "作业取消原因（作业取消是时，必填）",
`dhfs` varchar(150) NULL COMMENT "动火方式（1：电焊；2：气焊；3：气割；4：电钻；5：冲击钻；6：砂轮；7：喷砂机；8：氩弧焊；9：明火炙烤；10：等离子切割；11：磨光机；12：角磨机；13：其它；可多选，关联多个时英文逗号隔开）",
`is_cancelled` varchar(3) NULL COMMENT "作业是否取消（0否；1是）",
`delete_mark` varchar(3) NULL COMMENT "删除标识（正常：0；已删除：1）同步的数据不可物理删除，如需删除，标志记为1",
`version_num` decimal(10,1) NULL COMMENT "版本号",
`version` varchar(255) NULL COMMENT "版本",
`is_delete` varchar(255) NULL COMMENT "是否删除",
`is_dev` varchar(255) NULL COMMENT "是否开发数据",
`del_time` datetime NULL COMMENT "删除时间"
) ENGINE=OLAP
UNIQUE KEY(`zyid`)
DISTRIBUTED BY HASH(`zyid`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);
2、java代码
package com.xhb.data.center.dgp.web.utils;
import java.sql.*;
import java.util.*;
public class DorisTest {
public static void main(String[] args) {
// Doris数据库连接配置
String url = "jdbc:mysql://192.168.0.242:9030/test?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=group_commit=async_mode";
String username = "root";
String password = "123456";
String tableName = "dwd_t_dhzy";
// 要插入的数据
List<Map<String, Object>> data = prepareData();
// 插入数据到Doris
try {
insertBatch(url, username, password, tableName, data);
System.out.println("数据插入成功！");
} catch (Exception e) {
System.err.println("数据插入失败: " + e.getMessage());
e.printStackTrace();
}
}
/**
* 准备要插入的数据
*/
private static List<Map<String, Object>> prepareData() {
List<Map<String, Object>> data = new ArrayList<>();
Map<String, Object> record = new HashMap<>();
record.put("jsjdr", "施世X");
record.put("changed_cause", "test");
record.put("version_num", 1.0);
record.put("zyfzr", "刘燕X");
record.put("fxbs", "01,02");
record.put("major_person_mobile", "test");
record.put("ysr", "test");
record.put("social_credit_code", "91310113MAXXXNQ4X5A");
record.put("zydwlx", "0");
record.put("longitude", 0.0);
record.put("sfsjqttszy", "1");
record.put("zyid", "20250612113234385180224");
record.put("create_time", "2025-06-12 11:32:34");
record.put("jhrzjbh", "test");
record.put("dqzt", "1");
record.put("zyzbfw", "test");
record.put("dhzyjb", "02");
record.put("version", "V1.0");
record.put("zydwmc", "XX有限（总部）");
record.put("video_code", "test");
record.put("is_cancelled", "0");
record.put("aqjdr", "施世X");
record.put("sqdw", "协力单位/XX科技有限公司分公司");
record.put("latitude", 0.0);
record.put("zyryxx", "徐庆X:T422127197705180XXX");
record.put("sjqttszylx", "test");
record.put("create_by", "刘燕X");
record.put("zyzbh", "DH-2506007X");
record.put("update_time", "2025-06-12 16:03:54");
record.put("sqr", "施世X");
record.put("zyssjssj", "2025-06-12 16:03:53");
record.put("company_code", "test");
record.put("zywz", "暴怒专X");
record.put("update_by", "施世X");
record.put("jhr", "test");
record.put("is_changed", "0");
record.put("sjqttszyzbh", "DH-2506007X,SX-2503000X");
record.put("zyryid", "test");
record.put("fxyfcs", "test");
record.put("is_delete", "0");
record.put("job_id", "施世X");
record.put("fhyp", "test");
record.put("acceptanc_time", "2025-06-12 16:03:54");
record.put("zynr", "测试");
record.put("zysqsj", "2025-06-12 11:32:34");
record.put("zysskssj", "2025-06-12 15:52:10");
record.put("cancellen_cause", "test");
record.put("delete_mark", 0);
record.put("dhfs", "FIRE_DINAHAN,FIRE_QIGX");
data.add(record);
return data;
}
/**
* 批量插入数据到Doris
*/
private static void insertBatch(String url, String username, String password, String tableName, List<Map<String, Object>> data) throws Exception {
Connection connection = null;
PreparedStatement stmt = null;
try {
// 建立数据库连接
connection = DriverManager.getConnection(url, username, password);
// 设置会话变量
try (Statement statement = connection.createStatement()) {
statement.execute("SET group_commit = async_mode;set block_encryption_mode='';SET enable_insert_strict = true;SET insert_max_filter_ratio = 0;");
}
// 构造INSERT语句
if (data.isEmpty()) {
System.out.println("没有数据需要插入");
return;
}
Map<String, Object> firstRecord = data.get(0);
List<String> columnNames = new ArrayList<>(firstRecord.keySet());
String sql = buildInsertSQL(tableName, columnNames);
System.out.println("执行的SQL: " + sql);
// 准备PreparedStatement
stmt = connection.prepareStatement(sql);
// 批量设置参数并执行
for (Map<String, Object> record : data) {
for (int i = 0; i < columnNames.size(); i++) {
stmt.setObject(i + 1, record.get(columnNames.get(i)));
}
stmt.addBatch();
}
// 执行批量插入
int[] results = stmt.executeBatch();
System.out.println("成功插入 " + results.length + " 条记录");
} finally {
// 关闭资源
if (stmt != null) {
stmt.close();
}
if (connection != null) {
connection.close();
}
}
}
/**
* 构造INSERT SQL语句
*/
private static String buildInsertSQL(String tableName, List<String> columnNames) {
StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO ").append(tableName).append(" (");
sql.append(String.join(",", columnNames));
sql.append(") VALUES (");
sql.append(String.join(",", Collections.nCopies(columnNames.size(), "?")));
sql.append(")");
return sql.toString();
}
}
3、doris的异常
2025-11-07 17:55:08,948 WARN (mysql-nio-pool-15287|195528) [MysqlConnectProcessor.handleExecute():164] Process one query failed because unknown reason:
java.nio.BufferUnderflowException: null
at java.nio.Buffer.nextGetIndex(Buffer.java:532) ~[?:1.8.0_441]
at java.nio.HeapByteBuffer.getInt(HeapByteBuffer.java:366) ~[?:1.8.0_441]
at org.apache.doris.analysis.IntLiteral.setupParamFromBinary(IntLiteral.java:389) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.MysqlConnectProcessor.handleExecute(MysqlConnectProcessor.java:137) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.MysqlConnectProcessor.handleExecute(MysqlConnectProcessor.java:259) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.MysqlConnectProcessor.dispatch(MysqlConnectProcessor.java:314) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.MysqlConnectProcessor.processOnce(MysqlConnectProcessor.java:481) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.mysql.ReadListener.lambda$handleEvent$0(ReadListener.java:52) ~[doris-fe.jar:1.2-SNAPSHOT]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_441]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_441]
at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_441]
4、mysql驱动的异常
java.sql.BatchUpdateException: BufferUnderflowException, msg: null
at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:223)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchWithMultiValuesClause(ClientPreparedStatement.java:716)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:407)
at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:802)
at com.xhb.data.center.dgp.web.utils.DorisTest.insertBatch(DorisTest.java:129)
at com.xhb.data.center.dgp.web.utils.DorisTest.main(DorisTest.java:20)
Caused by: java.sql.SQLException: BufferUnderflowException, msg: null
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:555)
at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:339)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1054)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1003)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1312)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchWithMultiValuesClause(ClientPreparedStatement.java:677)
... 4 more
