db2数据库中CLOB字段不能插入内容

最新推荐文章于 2020-11-28 09:51:17 发布

原创最新推荐文章于 2020-11-28 09:51:17 发布 · 1.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #测试

数据库专栏收录该内容

3 篇文章

订阅专栏

在使用JDBC进行DB2数据库操作时，遇到将大文本字段插入数据库的问题。通过分析数据库定义、操作类和运行后的控制台信息，发现将文件输入流直接设置为大文本字段值时可能存在问题。建议采用正确的方法处理大文本字段的输入，避免常见的错误代码。

今天写了一个JDBC操纵db2中大文本字段的demo，可是一直出错，希望大家帮帮忙看看哪里出错了！

数据库定义：

CREATE TABLE
    USERINFO
    (
        USERID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        USERNAME VARCHAR(20) NOT NULL,
        USERAGE INTEGER,
        USERDSP CLOB(200000),
        PRIMARY KEY (USERID)
    )

数据库操作类：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Clob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class PrepareStatementDemo {
          public void add() {
        String sqlString = "insert into USERINFO(USERNAME,USERAGE,USERDSP) values(?,?,?)";
        BasedJdbc basedJdbc = new BasedJdbc();
        PreparedStatement preparedStatement = basedJdbc.getPreparedStatement(sqlString);
        File file = new File("D:\\config\\a.txt");
        
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            preparedStatement.setString(1, "userClob");
            preparedStatement.setInt(2, 1);
            preparedStatement.setAsciiStream(3, inputStream, (int)file.length());
            preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        basedJdbc.close();
    }
}

单元测试：

import org.testng.annotations.Test;

public class PerparedStatementTest {
       
    @Test    
    public void addTest(){
        PrepareStatementDemo prepareStatementDemo = new PrepareStatementDemo();
        prepareStatementDemo.add();
    }
    
   
}

运行后控制台信息：

com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016, SQLERRMC=7;ROOT.USERINFO, DRIVER=4.12.55
	at com.ibm.db2.jcc.am.hd.a(hd.java:679)
	at com.ibm.db2.jcc.am.hd.a(hd.java:60)
	at com.ibm.db2.jcc.am.hd.a(hd.java:127)
	at com.ibm.db2.jcc.am.mn.b(mn.java:2290)
	at com.ibm.db2.jcc.am.mn.c(mn.java:2273)
	at com.ibm.db2.jcc.t4.cb.k(cb.java:370)
	at com.ibm.db2.jcc.t4.cb.a(cb.java:62)
	at com.ibm.db2.jcc.t4.q.a(q.java:50)
	at com.ibm.db2.jcc.t4.sb.b(sb.java:220)
	at com.ibm.db2.jcc.am.nn.nc(nn.java:3083)
	at com.ibm.db2.jcc.am.nn.b(nn.java:4037)
	at com.ibm.db2.jcc.am.nn.hc(nn.java:2422)
	at com.ibm.db2.jcc.am.nn.execute(nn.java:2402)
	at com.suning.study.jdbc.PrepareStatementDemo.add(PrepareStatementDemo.java:70)
	at com.suning.study.jdbc.PerparedStatementTest.addTest(PerparedStatementTest.java:31)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:80)
	at org.testng.internal.Invoker.invokeMethod(Invoker.java:702)
	at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:894)
	at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1219)
	at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127)
	at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111)
	at org.testng.TestRunner.privateRun(TestRunner.java:768)
	at org.testng.TestRunner.run(TestRunner.java:617)
	at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
	at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
	at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
	at org.testng.SuiteRunner.run(SuiteRunner.java:240)
	at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53)
	at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:87)
	at org.testng.TestNG.runSuitesSequentially(TestNG.java:1185)
	at org.testng.TestNG.runSuitesLocally(TestNG.java:1110)
	at org.testng.TestNG.run(TestNG.java:1022)
	at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:109)
	at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:202)
	at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:173)
PASSED: addTest

===============================================
    Default test
    Tests run: 1, Failures: 0, Skips: 0
===============================================