All geek questions in one place

JAVA: sqlite throws an exception after writing multiple records?

I am trying to write several thousand records in sqlite DB. However, after recording a random number of records - 122/45/180, this raises the following exception:

java.sql.SQLException: unable to open database file

A random number of records are entered in the database, but this error stops further recording. What am I doing wrong?

EDIT:

Class loginModel:

private connection;

public loginModel(){
        connection = SqLiteConnection.connector();
        if(connection == null)
            System.exit(1);
    }

public boolean interactWithDB() throws SQLException{

    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    String query;

    try {
        query = "";
        DBPopulator dbPopulator = new DBPopulator();
        dbPopulator.populateData();
        List<dbData> dataList = dbPopulator.getdbData();
        Iterator<dbData> it = dataList.listIterator(); 
        while(it.hasNext()){
            dbData data = it.next();
            query = getNewInsertQuery(data); // builds query based on data input
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.executeUpdate();  
        }                   
        return true;
    } catch (Exception e) {
        // TODO: handle exception
        System.out.println(e);
        return false;
    }
    finally{
        if(preparedStatement!=null)
            preparedStatement.close();
        if(resultSet!=null)
            resultSet.close();
        connection.close();
    }
}

And I get a new connection through:

Class: SqLiteConnection

public static Connection connector(){
    try{
        Class.forName("org.sqlite.JDBC");           
        Connection conn = DriverManager.getConnection("jdbc:sqlite:C:\\Users\\akshaysu\\Documents\\r_sqlite.sqlite");
        conn.setAutoCommit(true);
        return conn;
    }       
    catch(Exception e){
        System.out.println("DB not found");
        System.out.println(e);
        return null;
    }
}

AN EXCEPTION:

java.sql.SQLException: unable to open database file
    at org.sqlite.DB.execute(DB.java:275)
    at org.sqlite.DB.executeUpdate(DB.java:281)
    at org.sqlite.PrepStmt.executeUpdate(PrepStmt.java:77)
    at application.loginModel.interactWithDB(loginModel.java:98)
    at application.loginController.buttonAction(loginController.java:30)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at sun.reflect.misc.Trampoline.invoke(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at sun.reflect.misc.MethodUtil.invoke(Unknown Source)
    at javafx.fxml.FXMLLoader$MethodHandler.invoke(Unknown Source)
    at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(Unknown Source)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEventImpl(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEvent(Unknown Source)
    at javafx.event.Event.fireEvent(Unknown Source)
    at javafx.scene.Node.fireEvent(Unknown Source)
    at javafx.scene.control.Button.fire(Unknown Source)
    at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(Unknown Source)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(Unknown Source)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(Unknown Source)
    at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(Unknown Source)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEventImpl(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEvent(Unknown Source)
    at javafx.event.Event.fireEvent(Unknown Source)
    at javafx.scene.Scene$MouseHandler.process(Unknown Source)
    at javafx.scene.Scene$MouseHandler.access$1500(Unknown Source)
    at javafx.scene.Scene.impl_processMouseEvent(Unknown Source)
    at javafx.scene.Scene$ScenePeerListener.mouseEvent(Unknown Source)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(Unknown Source)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$355(Unknown Source)
    at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(Unknown Source)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(Unknown Source)
    at com.sun.glass.ui.View.handleMouseEvent(Unknown Source)
    at com.sun.glass.ui.View.notifyMouse(Unknown Source)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$149(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

EDIT3:

private String getNewInsertQuery(dbData e){
        String query = "";
        query = "insert into r_sqlite (NAME, AGE, COMPANY-NAME) VALUES(";
        query += e.getName()+", ";
        query += e.getAge()+", ";
        query += e.getCompany();
        query += ");";      
        return query;
        }
+4
source share
2 answers

So, the following things fixed the problem. Thanks Jan for suggesting changes:

  • Packaging
  • Performing manual commit instead of auto commit.

Here's the code snippet:

connection.setAutocommit(false);
try(PreparedStatement ps = 
            connection.prepareStatement("insert into r_sqlite (NAME, AGE, COMPANY-NAME) VALUES(?,?,?)")) {
      Iterator<dbData> it = dataList.listIterator();
      int batchCount = 0;
      while(it.hasNext()) {
        dbData data = it.next();
        ps.setString(1,  data.getName());
        ps.setInt(2,  data.getAge());
        ps.setString(3,  data.getCompany());
        ps.addBatch();
        if(++batchCount % 1000 == 0) {
            ps.executeBatch();
            ps.clearBatch(); 
        }
      }
      ps.executeBatch();
      connection.commit();
    }
0
source

, , , :

  • :

    while(it.hasNext()){
    dbData data = it.next();
    query = getNewInsertQuery(data); 
    //This is never closed!
    preparedStatement = connection.prepareStatement(query);
    preparedStatement.executeUpdate();  
}

try-with-resource:

   while(it.hasNext()){
        dbData data = it.next();
        query = getNewInsertQuery(data); 
        //This whill always be closed!
        try(PreparedStatement preparedStatement = connection.prepareStatement(query)) {
           preparedStatement.executeUpdate();  
        }
    }

  • login(), :

    public boolean interactWithDB() throws SQLException{  
  try (Connection connection = SqLiteConnection.connector()) {
    String query = "";
    DBPopulator dbPopulator = new DBPopulator();
    dbPopulator.populateData();
    List<dbData> dataList = dbPopulator.getdbData();
    Iterator<dbData> it = dataList.listIterator(); 
    while(it.hasNext()){
        dbData data = it.next();
        query = getNewInsertQuery(data); // builds query based on data input
        try(PreparedStatement preparedStatement = connection.prepareStatement(query)) {
            preparedStatement.executeUpdate();  
        }
    }                   
    return true;
 } catch (Exception e) {
    e.printStackTrace();
    return false;
 }    
}

Exception Stacktrace, .

2

  • ? - ?
  • getNewInsertQuery () - ( ) ?

3 -

    try(PreparedStatement ps = 
            connection.prepareStatement("insert into r_sqlite (NAME, AGE, COMPANY-NAME) VALUES(?,?,?)")) {
      Iterator<dbData> it = dataList.listIterator();
      int batchCount = 0;
      while(it.hasNext()) {
        dbData data = it.next();
        ps.setString(1,  data.getName());
        ps.setInt(2,  data.getAge());
        ps.setString(3,  data.getCompany());
        ps.addBatch();
        if(++batchCount % 1000 == 0) {
            ps.executeBatch();  
        }
      }
      ps.executeBatch();
    }
+4

Source: https://habr.com/ru/post/1622723/

More articles:


All Articles