GBASE批量插入报错value is out of range for type integer
一、问题
最近公司在做GBASE数据库适配的时候，原来正常的功能报错：
ERROR: value "1554991280574955520" is out of range for type integer Call getNextException to see other errors in the batch.
二、分析
将报错的SQL在数据库工具中执行时可以成功执行，而且如果只插入一条数据时不报错，批量插入时报错。
经调试分析，怀疑是bigint类型的字段在批量插入时，值类型不一致导致的（由于业务上第一条数据某bigint类型字段的值为0，后面的数据该字段才有值），于是直接写代码连接GBASE库，创建表并使用模拟数据批量插入。
- 表结构
create table mybill (
id bigint default 0 not null,
name varchar(10) default ' ' not null,
billid bigint default 0 not null
);
- 模拟批量插入
String username = "user";
String password = "password";
String url = "jdbc:gbase://192.168.18.16:25432/testdb";
try(Connection con = DriverManager.getConnection(url, username, password)){
String sql = "insert into mybill(id, name, billid) values(?,?,?);";
try(PreparedStatement ps = con.prepareStatement(sql)){
//第一条数据
ps.setLong(1, 1235640848830431232L);
ps.setString(2, "Apple");
//bigint字段值用setInt方法设置为0
ps.setInt(3, 0);
ps.addBatch();
//第二条数据
ps.setLong(1, 1294869674105777152L);
ps.setString(2, "Banana");
//bigint字段值设置Long类型值
ps.setLong(3, 1295737670504558592L);
ps.addBatch();
//批量插入
ps.executeBatch();
}
}
- 运行
运行上面的代码，报错如下，和测试环境报错信息一样：
Exception in thread "main" java.sql.BatchUpdateException: Batch entry 0 insert into mybill(id, name, billid) values('1235640848830431232','Apple','0') was aborted: [172.27.160.134:56187/192.168.18.16:25432] ERROR: value "1295737670504558592" is out of range for type integer Call getNextException to see other errors in the batch.
at org.gbase.jdbc.BatchResultHandler.handleCompletion(BatchResultHandler.java:173)
at org.gbase.core.v3.QueryExecutorImpl.executeBatch(QueryExecutorImpl.java:611)
at org.gbase.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:1023)
at org.gbase.jdbc.PgStatement.executeBatch(PgStatement.java:1098)
at org.gbase.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1594)
at com.test.GBaseTest.testInsert(GBaseTest.java:33)
at com.test.GBaseTest.main(GBaseTest.java:18)
Caused by: org.gbase.util.PSQLException: [172.27.160.134:56187/192.168.18.16:25432] ERROR: value "1295737670504558592" is out of range for type integer
at org.gbase.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2901)
at org.gbase.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2630)
at org.gbase.core.v3.QueryExecutorImpl.executeBatch(QueryExecutorImpl.java:597)
... 5 more
三、解决办法
在设置bigint类型字段参数值时使用setLong方法：
ps.setLong(3, 0L);