我想实现通过键入一个关键词,将数据库中的该关键词删除,关键词字段的在数据库格式如“aaa;bbb;ccc;ddd”,思路是若删除aaa,现将含aaa的所有行取出,然后逐行update关键词这一项,更新为“bbb;ccc;ddd”(就是将新的字符串赋值过去)。
然而,代码一开始运行良好,rs1值为1(executeUpdate分返回值),但是更新了若干行后突然报错,下一次如果重新运行,则冲这个错误行开始,直接报错。我的分析是:这一行的数据有问题才导致的错误,但是看不出哪里有问题。
源代码如下(接口很简单,就省略了)
package test1_9;
import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
class inputKW implements inputable{
@Override
public String input() {
// TODO Auto-generated method stub
Scanner sc=new Scanner(System.in);
String str=null;
System.out.println("请输入要删除的关键词");
str=sc.nextLine();
sc.close();
return str;
}
}
public class Correct_KW {
public static void main(String[] args) throws SQLException {
try {
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
}catch(Exception e) {}
Connection conn = DriverManager.getConnection("jdbc:ucanaccess://wos_test.mdb");
Statement state = conn.createStatement();
System.out.println("----------------------数据酷连接成功!-----------------------");
inputKW str = new inputKW();
String keyWords= str.input();
keyWords = keyWords+";";
ResultSet rd = state.executeQuery("SELECT * FROM [data1]");
while (rd.next()) {
String val_wosId = rd.getString("wosId");
String val_author = rd.getString("author");
String val_title = rd.getString("title");
String val_keyword = rd.getString("keyword");
String val_institution = rd.getString("institution");
String val_type = rd.getString("type");
if (val_keyword == null) continue;
if(val_keyword.contains(keyWords)) { //包含某个关键词的条件下
System.out.println("wosId: "+val_wosId+" author: "+val_author+" title: "+val_title+" keyword: "+val_keyword+" institution: "+val_institution+" type: "+val_type);
val_keyword = val_keyword.replace(keyWords, "");
Statement state1 = conn.createStatement();
int rd1 = state1.executeUpdate("UPDATE [data1] SET [keyword] = '"+val_keyword+"' WHERE [wosId] = '"+val_wosId+"'");
System.out.println("Update result: "+rd1);
// PreparedStatement CKW = conn.prepareStatement("UPDATE [data1] SET [keyword] = ? WHERE [wosId] = ?");
// CKW.setString(1, val_keyword);
// CKW.setString(1, val_wosId);
// conn.commit();
}
}
}
}
运行结果如下(错误):