Java中delete删除不了数据

[楚念悠]

初学者，大致描述一下功能。是做个缓存，代码家里木有...
数据库中有一个字段是CLOB类型，查询CLOB和插入CLOB都已经解决。但是最开始好用的删除竟然崩溃了。
（1）查询数据（启用缓存）→查询缓存表（缓存表有信息）→读取缓存表
（2）查询数据（启用缓存）→查询缓存表（缓存表无信息）→正常查询→查询结果插入缓存表
（3）查询数据（不启用缓存）→查询缓存表（缓存表有信息）→删除缓存表中信息→正常查询→查询结果插入缓存表
（4）查询数据（不启用缓存）→查询缓存表（缓存表无信息）→正常查询→查询结果插入缓存表


删除功能最初是好用的，因为插入CLOB字段数据量大于4000，通过这个方法解决了插入问题，但是删除功能崩溃，考虑了屏蔽了自动提交，添加提交也木有用。
将大字段存入Oracle数据库CLOB字段代码大致如下。其中有select for update锁表和自动提交改成false.
JAVA代码如下：


import java.sql.*;

import oracle.jdbc.OracleResultSet;
import oracle.sql.CLOB;

public class ClobTest {
public static void main(String[] args)throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
conn.setAutoCommit(false);

//下面往数据库里加入一行，其中value字段设置其值为empty_clob()，即设置为空值，以便接下来更改
PreparedStatement stmt = conn.prepareStatement("INSERT INTO clob_test VALUES (1,empty_clob())");
stmt.execute();

//下面执行SELECT查询，以取得一个oracle.sql.CLOB对象
//然后调用CLOB对象的putChar()方法写入数据。
stmt = conn.prepareStatement("SELECT value FROM clob_test FOR UPDATE");
ResultSet rs = stmt.executeQuery();
rs.next();
CLOB clob = ((OracleResultSet)rs).getCLOB(1);
clob.putChars(1,"this is a clob test!!!".toCharArray());

//这里把上面得到的已经写入数据的clob对象上传到数据库
stmt = conn.prepareStatement("UPDATE clob_test SET value=?");
stmt.setClob(1,clob);
stmt.execute();
conn.commit();

//检验结果。
stmt = conn.prepareStatement("SELECT * FROM clob_test");
rs = stmt.executeQuery();
rs.next();
Clob clob1 = rs.getClob("value");
String s = clob.getSubString((long)1,(int)clob.length()-1);
System.out.println(s);

//收尾工作
rs.close();
stmt.close();
conn.close();
}
}

[古布]

几个地方修改一下。
1.stmt.execute();
==>

stmt.executeUpdate();

2.stmt = conn.prepareStatement前加stmt.close();

[楚念悠]

您好！感谢您的回答。今天来看了下，昨天找的相似的向CLOB插入大字段的功能，但是细节还是我没记清楚，补充下。
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;

public class TestOracleClob implements Serializable{
public static void main(String[] args)
{
//create table test (id integer,content clob);
System.out.println("-------------------insert -----------------");
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@fangm:1521:LICSFC","SFC","SFC");
//con.setAutoCommit(false);
//Ok 1
String sql="insert into test values(1,empty_clob())";
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(sql);
String sqll="select content from test where id=1 for update";
ResultSet rss=stmt.executeQuery(sqll);
if(rss.next()){
CLOB clob = ((OracleResultSet)rss).getCLOB(1);
clob.putString(1,"ddddddddddddddddddddddddddddddddddd");
sql="update test set content=? where id=1";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setClob(1,clob);
pstmt.executeUpdate();
pstmt.close();
}
//Ok 2
//String sql1="insert into test values(2,empty_clob())";
//ResultSet rs3=stmt.executeQuery(sql1);
String sql12="insert into test values(?,?)";
PreparedStatement pstmt1=con.prepareStatement(sql12);
pstmt1.setInt(1,2);
pstmt1.setClob(2,oracle.sql.CLOB.empty_lob());
pstmt1.executeUpdate();
String sqll2="select content from test where id=2 for update";
ResultSet rss2=stmt.executeQuery(sqll2);
if(rss2.next()){
CLOB clob = ((OracleResultSet)rss2).getCLOB(1);
clob.putString(1,"affffffffffdfdfdfdddddddfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffdddfff");
String sql1="update test set content=? where id=2";
PreparedStatement pstmt=con.prepareStatement(sql1);
pstmt.setClob(1,clob);
pstmt.executeUpdate();
pstmt.close();
}
//con.commit();
rss.close();
rss2.close();
pstmt1.close();
rs.close();
stmt.close();
con.close();
System.out.println("-------------insert ok-------------");
}catch(Exception e){
System.out.println("insert:"+e);
}
System.out.println("-------------------query -----------------");
try{
String content="";
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@fangm:1521:LICSFC","SFC","SFC");
Statement stmt=con.createStatement();
String sql="select content from test where id=1";
ResultSet rs=stmt.executeQuery(sql);
if(rs.next()){
CLOB clob = ((OracleResultSet)rs).getCLOB(1);
if(clob!=null){
Reader is=clob.getCharacterStream();
BufferedReader br=new BufferedReader(is);
String s=br.readLine();
while(s!=null){
content+=s+",";
s=br.readLine();
}
}

}
rs.close();
stmt.close();
con.close();
System.out.println("clob:"+content);
System.out.println("-------------query ok-------------");
}catch(Exception ee){
System.out.println("query:"+ee);
}

}
}

昨天用的这个插入方法的OK2方法。

[楚念悠]

您好!感谢您的回答。
相似插入CLOB方法，今天食用您的方法发现细节不同。补充下具体插入方法。
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection
("jdbc:oracle:thin:@fangm:1521:LICSFC","SFC","SFC");
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@fangm:1521:LICSFC","SFC","SFC");
con.setAutoCommit(false);
String sql12="insert into test values(?,?)";
PreparedStatement pstmt1=con.prepareStatement(sql12);
pstmt1.setInt(1,2);
pstmt1.setClob(2,oracle.sql.CLOB.empty_lob());
pstmt1.executeUpdate();
String sqll2="select content from test where id=2 for update";
ResultSet rss2=stmt.executeQuery(sqll2);
if(rss2.next()){
CLOB clob = ((OracleResultSet)rss2).getCLOB(1);
clob.putString(1,"affffffffffdfdfdfdddddddfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffdddfff");
String sql1="update test set content=? where id=2";
PreparedStatement pstmt=con.prepareStatement(sql1);
pstmt.setClob(1,clob);
pstmt.executeUpdate();
pstmt.close();
}
//con.commit();
rss.close();
rss2.close();
pstmt1.close();
rs.close();
stmt.close();
con.close();
System.out.println("-------------insert ok-------------");
}catch(Exception e){
System.out.println("insert:"+e);
}

[楚念悠]

您好!感谢您的回答。
相似插入CLOB方法，今天食用您的方法发现细节不同。补充下具体插入方法。
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection
("jdbc:oracle:thin:@fangm:1521:LICSFC","SFC","SFC");
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@fangm:1521:LICSFC","SFC","SFC");
con.setAutoCommit(false);
String sql12="insert into test values(?,?)";
PreparedStatement pstmt1=con.prepareStatement(sql12);
pstmt1.setInt(1,2);
pstmt1.setClob(2,oracle.sql.CLOB.empty_lob());
pstmt1.executeUpdate();
String sqll2="select content from test where id=2 for update";
ResultSet rss2=stmt.executeQuery(sqll2);
if(rss2.next()){
CLOB clob = ((OracleResultSet)rss2).getCLOB(1);
clob.putString(1,"affffffffffdfdfdfdddddddfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffdddfff");
String sql1="update test set content=? where id=2";
PreparedStatement pstmt=con.prepareStatement(sql1);
pstmt.setClob(1,clob);
pstmt.executeUpdate();
pstmt.close();
}
//con.commit();
rss.close();
rss2.close();
pstmt1.close();
rs.close();
stmt.close();
con.close();
System.out.println("-------------insert ok-------------");
}catch(Exception e){
System.out.println("insert:"+e);
}

[古布]

ＬＺ那样写，可能有长度限制问题。一般都用流。参照
http://wenku.baidu.com/view/12f6513a376baf1ffc4fad38.html