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

Jonas Kongslund · Accepted Answer · 2011-04-17 17:14:51Z

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

edited Apr 17, 2011 at 17:14

answered Apr 15, 2011 at 20:03

Jonas Kongslund

5,2782 gold badges32 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Marco Over a year ago

Thank you for reply! I'm testing your solution ... give me a moment

Marco Over a year ago

Hello, I analyzed and tested your solution but still receive the same error. to follow the modified code:

Jonas Kongslund Over a year ago

Please show us all the code, including the part where you initialize conn.

Marco Over a year ago

Hi!, I have modify the code in the question box, now you can see the inizialization of conn.

Jonas Kongslund Over a year ago

I've added an example that deals with the resources in a proper way and does not execute the prepared statement more than once. It's been tested on about 200 files having a total of 887888 words. Does it solve your problem?

|

Collectives™ on Stack Overflow

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

Here the code:

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Here the code:

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related