java - 数据库管理中的问题 - sqlite with Java (IDE : NetBeans)

Question

我在通过查询管理数据存储时遇到一些问题(NetBeans-> Java-> Sqlite):

• 1) 我有一个文件夹，里面有一些 txt 文件，其中包含几行文本(文件不超过 2 Kb)
• 2)程序按顺序打开文件，并将每个单词存储在表格中
• 3)当程序分析太多数据时(有时超过 40 个文件或超过 82 个)返回以下错误

线程“main”java.sql.SQLException中出现异常:无法打开数据库文件 在 org.sqlite.DB.throwex (DB.java: 288) 在 org.sqlite.DB.executeBatch (DB.java: 236) 在 org.sqlite.PrepStmt.executeBatch (PrepStmt.java: 83)

• 错误出现在 int [] upCountsb = prepb.executeBatch();

<小时/>

这里是代码:

import java.sql.Connection;
import java.sql.DriverManager;

import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;



public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException, InterruptedException {
Class.forName("org.sqlite.JDBC");

                Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/Users/.../test.db");
                Statement stmt;
                stmt = conn.createStatement();

                stmt.executeUpdate("DROP TABLE IF EXISTS words");
                stmt.executeUpdate("CREATE TABLE words (words)");


String path_dir ="C:/Users/.../good";
File currentDIR = new File("C:/Users/.../good");
File files[]=currentDIR.listFiles();
String tmp="";

ArrayList app = new ArrayList();

//Search in DIR for Files
for( File f1 : files ){
        String nameFile = f1.getName();

        FileReader f = null;
        BufferedReader fIN = null;
        String s;

       //Open the file xxx.txt
        try{

         f = new FileReader(path_dir+"/"+nameFile);
         fIN = new BufferedReader(f);
         s = fIN.readLine();

           while(s != null) {
            StringTokenizer st = new StringTokenizer(s);
                while(st.hasMoreTokens()) {


                    String str = st.nextToken().toString().toLowerCase();
                    Pattern pattern =Pattern.compile("\\W", Pattern.MULTILINE); 
                    String newAll = pattern.matcher(str).replaceAll("").trim();
                    tmp=newAll;


             app.add(tmp); //Add all data in the ArrayList app

                 } // Close  While 'hasMoreTokens'

                s = fIN.readLine();
           } //Close While on File


    } //Close TRAY
catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
}
 f.close(); //Close FileReader
 } //Close Scan DIR for FILE


//Add all data in the Tbl od Database
PreparedStatement prep = conn.prepareStatement("insert into words values (?);");
for (int z=0; z<app.size();z++){


                    prep.setString(1,app.get(z).toString().toLowerCase());
                    prep.addBatch();

                   conn.setAutoCommit(false);
  prep.executeBatch();   ***//Here I get the error although i use int [] Count =prep.executeBatch();***
                   conn.setAutoCommit(true);


                    }
            prep.close();

            } //Close MAIN

Answer 1

使用完正在使用的资源后，您需要释放它们。例如。执行该语句后prepb.close()。

文件句柄也是如此。

此外，如果对每个插入都执行该语句，则批处理的意义就会丢失。

由于您的文件非常小，因此您最好在将数据保存到数据库之前准备好内存中的所有数据。

package stackoverflow.wordanalysis;

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.regex.*;

public class WordFrequencyImporter {

    public static void main(String[] args) throws Exception {
        List<String> words = readWords("the-directory-from-which-to-read-the-files");
        Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
        try {
            createWordsTable(conn);
            persistWords(words, conn);
        } finally {
            conn.close();
        }
    }

    private static void persistWords(List<String> words, Connection conn)
            throws SQLException {
        System.out.println("Persisting " + words.size() + " words");
        PreparedStatement prep = conn
                .prepareStatement("insert into words values (?);");
        try {
            for (String word : words) {
                prep.setString(1, word);
                prep.addBatch();

            }
            conn.setAutoCommit(false);
            prep.executeBatch();
            conn.setAutoCommit(true);

        } finally {
            prep.close();

        }
    }

    private static void createWordsTable(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        try {
            stmt.executeUpdate("DROP TABLE IF EXISTS words");
            stmt.executeUpdate("CREATE TABLE words (words)");
        } finally {
            stmt.close();
        }
    }

    private static List<String> readWords(String path_dir) throws IOException {
        Pattern pattern = Pattern.compile("\\W", Pattern.MULTILINE);
        List<String> words = new ArrayList<String>();
        for (File file : new File(path_dir).listFiles()) {
            BufferedReader reader = new BufferedReader(new FileReader(file));
            System.out.println("Reading " + file);
            try {
                String s;
                while ((s = reader.readLine()) != null) {
                    StringTokenizer st = new StringTokenizer(s);
                    while (st.hasMoreTokens()) {
                        String token = st.nextToken().toString().toLowerCase();
                        String word = pattern.matcher(token).replaceAll("")
                                .trim();
                        words.add(word);
                    }
                }
            } finally {
                reader.close();
            }
        }
        return words;
    }
}