JAVA:sqlite在写入一些记录后抛出异常

我正在尝试向sqlite DB写入几千条记录。然而，在写入随机数目的记录-122/45/180后，它抛出以下异常：

java.sql.SQLException: unable to open database file

数据库中会填充随机数目的记录，但此错误会停止进一步的记录写入。我做错了什么？

编辑：

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

我正在通过获得新的连接

类别：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;
    }
}

异常：

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)

第3版：

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

您可能希望对代码进行正确设置的一些注释：

你在你的时间里不断创建新的语句：

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

尝试使用资源样式：

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

现在再试一次，如果仍然不起作用，请共享完整的异常堆栈跟踪。

编辑2

数据库还有其他迭代吗？你在别的地方用它吗
您可以共享getNewInsertQuery（数据）吗？是SAME查询（不同的值）还是different查询

编辑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();
    }

因此，通过以下操作解决了问题。感谢Jan建议更改：

批处理
正在执行手动提交而不是自动提交

以下是代码片段：

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

相关内容

最新更新

热门标签：