JAVA:sqlite写几条记录后抛出异常？

Question

我正在尝试将几千条记录写入 sqlite 数据库。然而，在写入随机数量的记录后 - 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;
    }
}

异常(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)

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

Answer 1

关于您的代码的一些注释，您可能希望正确设置:

• 您不断地创建新语句:

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

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

编辑 2

• 与数据库的任何其他迭代？你在其他地方使用它吗？
• 您能否分享 getNewInsertQuery(data) - 是相同的查询(不同的值)还是不同的查询？

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