Problems in Database Management - sqlite with Java (IDE: NetBeans)

Question

I have some problems in managing the storage of data via query (NetBeans-> Java-> Sqlite):

• 1) I have a folder with some txt file, containing several lines of text (the files do not exceed 2 Kb)
• 2) The program opens the files in sequence, and stores each word in a table
• 3)When the program comes to analyze too much data (some time more than 40 files ore more then 82) returns the following error

Exception in thread "main" java.sql.SQLException: unable to open database file at org.sqlite.DB.throwex (DB.java: 288) at org.sqlite.DB.executeBatch (DB.java: 236) at org.sqlite.PrepStmt.executeBatch (PrepStmt.java: 83)

• The error is in int [] upCountsb = prepb.executeBatch();

---

Here the code:

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

You need to release the resources you are using once you are done with them. E.g. prepb.close() after executing the statement.

The same thing goes for your file handles.

Also, the point of batching is lost if you execute the statement for every insert.

Since your files are very small you might as well prepare all the data in memory before you persist it to a database.

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;
    }
}