Java - 数据库锁定

Question

我希望使用 csv 文件中的值填充我的数据库。但它返回 SQLITE_BUSY:数据库文件已锁定。

这是我的代码

try {
    BufferedReader br=new BufferedReader(new FileReader("v.csv"));
    String line;

    while((line=br.readLine())!=null) {
        System.out.println(line);
        String[]value = line.split(",");
        System.out.println(value.length);

        String sql = "INSERT into main ([Ticket #], Status, Priority, Department, [Account Name]) "
                + "values ('"+value[0]+"','"+value[1]+"','"+value[2]+"','"+value[3]+"','"+value[4]+"')";

        System.out.println("test");

        PreparedStatement pst = null;
        try {
            pst = DatabaseConnection.ConnectDB().prepareStatement(sql);
            pst.executeUpdate();
        } finally {
            if(pst != null) {
                pst.close();
            }
        }

    }
    br.close();

} catch(Exception e) {
    JOptionPane.showMessageDialog(null, e);
}

我使用不同的打印语句来检查错误所在，似乎无法执行Update();当循环第二次执行时。这是我在控制台中得到的内容

Ticket,Status,Priority,Department,Account Name
5
test

这是我的DatabaseConnection仅供您引用

public class DatabaseConnection {
    Connection conn = null;
    Statement stmt = null;

    public static Connection ConnectDB() {

        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:database.db");
            conn.setAutoCommit(true);
            return conn;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
            return null;
        }

    }
}

Answer 1

每次执行循环时，您都会连接到数据库。

在循环外部创建数据库连接并在循环中使用它，因此每次迭代时都不会getConnection。

try
{
    BufferedReader br=new BufferedReader(new FileReader("v.csv"));
    String line;

    db = DatabaseConnection.ConnectDB();
    while((line=br.readLine())!=null){
        System.out.println(line);
        String[]value = line.split(",");
        System.out.println(value.length);

        String sql = "INSERT into main ([Ticket #], Status, Priority, Department, [Account Name]) "
         + "values ('"+value[0]+"','"+value[1]+"','"+value[2]+"','"+value[3]+"','"+value[4]+"')";

        System.out.println("test");

        PreparedStatement pst = null;
        try{   
          pst = db.prepareStatement(sql);
          pst.executeUpdate();
        }finally{
          if(pst != null) {
            pst.close();
          }
        }
    }
    br.close();
}
catch(Exception e)
{
    JOptionPane.showMessageDialog(null, e);
}