故障010：JAVA代码解决TEXT大字段列比较

帅ちいさい宝

已于 2022-10-23 18:32:20 修改

阅读量2.1k

点赞数 8

分类专栏：玩转达梦数据库--魔障篇文章标签： java 数据库 sql database

于 2022-10-22 17:12:55 首次发布

本文链接：https://blog.csdn.net/ai20110304/article/details/127464030

版权

玩转达梦数据库--魔障篇专栏收录该内容

12 篇文章 1 订阅

订阅专栏

故障010：JAVA代码解决TEXT大字段列比较

1. 问题描述
2. 预热知识
3. 案例再现
4. 解决方法
5.YY建议

1. 问题描述

昨日遇到某公司shen一样的开发人员，一直在微信群上吵吵，迁移到达梦数据库，啥也不想动（少动手、不动脑），结果程序跑不起来（查询语句中遇到TEXT大字段比较，报错：数据类型不匹配），需要我方技术协助。
既然我方给他提出几个方案，他却一点建议听不进去，咬牙不放，坚称MySQL上可行，令人感觉郁闷。
据说他们正在使用的MySQL有功能限制，不能最大限度地添加列，同时他们根本就没有前期的设计方案，拿不定有些字段的长度多少适当，全部整成TEXT类型，图方便省事，从不考虑数据库性能问题。更骚操作的事是，他们编写的代码要求兼容所有国产数据库（金仓、南大…），并且SQL是自动生成的，不想改动任何东西，对接到任何数据库就能直接用。听到此处，适配这玩意儿，根本不止在达梦数据库上，要求我方想方案，多么理直气壮的理由。

经典语录："我说了你又不听，听了你又不懂，懂了你又不做，做了你又做错，错了你又不认，认了你又不改，改了你又要忘，忘了我又要说"

先上他的测试示例截图，member字段就是text类型，执行报错。

在这里插入图片描述

2. 预热知识

必须熟悉的常用数据库的数据类型与JAVA数据类型的对应关系
在这里插入图片描述

3. 案例再现

3.1 代码与数据准备

-- 数据准备
drop table if exists test20221021;
create table test20221021 (c1 int, c2 text);
insert into test20221021 values(110, '帅小宝技术博客'), (120, 'chinese people'), (130, '达梦数据库');
commit;    -- 一定要记得提交哟，当时忘了手写commit，平时都是点击管理工具上“勾”表示提交事务。担心其他网友试验不出效果，怪我咯，在此提醒一下。
select * from test20221021;
select * from test20221021 where c2 = '达梦数据库'; -- c2 TEXT

// JAVA代码
/* 代码需求：通过c2字段等值比较后筛选数据行，注意c2属于TEXT大字段类型。*/
public class LongTextTest {
    private static final String jdbcName = "dm.jdbc.driver.DmDriver";
    private static final String jdbcUrl = "jdbc:dm://192.168.191.120:15236";
    private static final String userName = "SYSDBA";
    private static final String password = "SYSDBA***";

    public static void main(String[] args) throws IOException, SQLException {
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet result = null;

       try {
           Class.forName("dm.jdbc.driver.DmDriver");   //加载jdbc驱动
           conn =  DriverManager.getConnection(jdbcUrl, userName, password);  //发起连接数据库请求
           String sql = "select * from test20221021 where c2 = ?";
           prep = conn.prepareStatement(sql);
           prep.setString(1, "达梦数据库");
//           prep.setObject(1, "japanese", java.sql.Types.VARCHAR);
           result = prep.executeQuery();

           while(result.next()){
               System.out.println(result.getString("c2"));
           }

       } catch (Exception e) {
           e.printStackTrace();
       } finally {
//           不管成功与否，最后都要处理资源释放
           try{
               if (result != null)
                   result.close();
               if (prep != null)
                   prep.close();
               if (conn != null)
                   conn.close();
               System.out.println("Database connection already closed.");
           } catch (Exception e)
           {
               e.printStackTrace();
           }
       }

    }

}

在这里插入图片描述

3.2 原因探寻

☺️客户端管理工具佐证

在这里插入图片描述

其实java代码的原理基本就是如此，因绑定参数出错引起。

在这里插入图片描述

☺️sql日志佐证

在这里插入图片描述

☺️IDEA断点调试佐证

在这里插入图片描述

测试结论：

以上测试结果表明带绑定参数方式的大字段TEXT不能用普通的比较符运算（不分管理工具或者程序代码涉及绑定参数用法）。
个人认为达梦数据库为何不直接支持带绑定参数的text大字段作等于比较，可能会考虑到数据库性能和数据安全隐患的原因。

其一：一个text字段可以表示2G的数据内容，就算当它支持，遇到高并发业务系统，频繁调用执行此类查询语句，比较很大的文本内容，暂且别说应用发送这么多的内容到数据库的时候占用网络资源，而又因消耗内存资源，非常有可能导致数据库的内存不足，在极其低硬件配置的环境下。这也恰恰说明存在即合理的道理。

其二：绑定参数方式的语句，占位符？可以理解为未知，即未知数据内容长度，况且text支持2G数据内容，放开数据库的限制让它自动隐式转换成varchar类型，
但是varchar在数据库底层是有长度限制（即32767），遇到真实数据内容超过32767个字节，岂不是在数据类型转换时发生数据溢出或截断，这都是不合法的举动。MySQL能支持，是因为很多方面是放开的，比较宽松，但并不代表不存在隐患，而达梦就像python语言一样尽可能在生产运行前提前阻断，正所谓防患于未然。

4. 解决方法

4.1 方法一：设计合理的字段类型

根据该表实际用途，设计更为合理的字段类型和字符长度，且注意字符长度还受数据页大小影响，请参考我的另一篇文章《揭秘达梦数据库初始化参数》。
比如：询问那个开发人员说，他们顶多存2000字符，一般60个字符左右，正因字符长度不固定，可以定义c2 varchar(2000 char)。
如果考虑数据库性能，很少人用几百字符等于比较，即使建上索引，维护代价高，性能也不好。

4.2 方法二：like运算符全内容比较

注意事项：

达梦数据库对大字段文本的like比较是有限制的，受参数CLOB_LIKE_MAX_LEN的影响，默认值10240KB，最大上限是102400KB。
如果遇到超过10MB的文本内容，请及时调整参数值，并且它需要重启数据库生效。
正因为只支持100MB的文本内容，超过100MB，就应该换用其他方法解决，即不再适合。

在这里插入图片描述

/*
将java代码中sql文本调整一下，改为like运算表达式，其它保持不变。
*/
String sql = "select * from test20221021 where c2 like ?";

在这里插入图片描述

4.3 方法三：to_char函数转为普通字符类型

注意事项：
1.to_char/cast/convert/dbmsllob.substr等函数皆可以转换成普通字符类型，但切记它们都是长度限制，最多32767个字节，方法2比它更为靠谱一些。

/*
同样是改sql代码书写，java主体代码保持不变。
*/
String sql = "select * from test20221021 where to_char(c2) = ?";
String sql = "select * from test20221021 where cast(c2 as varchar) = ?";
String sql = "select * from test20221021 where convert(varchar, c2) = ?";
String sql = "select * from test20221021 where dbms_lob.substr(c2) = ?";

在这里插入图片描述

4.4 方法四：instr/postion函数变向比较法

TIPS：
1.instr/position函数返回大于0的值，则表示已找到，匹配成功。
2.这两个函数也是在内部进行字符类型转换，省去我们的手工显式工作。
3.也应该注意文本内容长度的限制，它只支持普通的字符类型，长度最大32767字节。

/*
同样是调整sql代码，java代码保持不变。
*/
String sql = "select * from test20221021 where instr(c2, ?) > 0";
String sql = "select * from test20221021 where position(? in c2) > 0";

在这里插入图片描述

4.5 方法五：自带终极大字段比较函数

TIPS：TEXT_EQUAL函数就是专门用来比较大字段类型的文本内容，该函数判断相同内容返回1（true），否则返回0(false)。

扣qun 940124259

/*
同样是调整sql代码，java代码保持不变。
*/
String sql = "select * from test20221021 where text_equal(c2, ?)";

在这里插入图片描述

4.6 方法六：字符串拼接大法

TIPS：
预先构造一个半成品的SQL语句，绕过绑定参数使用方式，通过字符串拼接方式构成最终目标SQL，也能完成目标查询功能，避开类型不匹配的错误（数据库遇到字面的文本内容，自动将大字段列转普通字符类型）。

注意事项：此类方法慎用，它也有限制和性能影响。
限制是大字段内容超长大多，数据库内部类型转换会报错，性能则是因SQL不能重用，放在高并发OLTP系统中很不适用，大量冲击SQL缓冲池。

/*
java核心代码保持不变，只改变sql书写方式，采用字符串拼接。
*/
String sql = "select * from test20221021 where c2 = "; //构造一个半成品sql语句
String var = "达梦数据库"; //假设var变量内容从外部传入
sql = sql + "'" + var + "'"; //通过字符串拼接形成目标查询语句

在这里插入图片描述

5.YY建议

初期设计合理，实事求是，有优化架构和通用架构的目标。很多软件公司都没有一份具体的设计方案和设计文档，直接搬老系统代码过来，根据现有客户的业务，边修边改（修修补补），总给人感觉很凌乱。
开发人员应当有开明的态度，学会灵便处理，不拘于固定套路。
犯这么多错误，要善于总结归纳，不能反复遇到同样的问题，反复询问。