SQLite jdbc 插入慢

╰+哭是因爲堅強的太久メ 2023-10-07 20:58 25阅读 0赞

<dependency>
                <groupId>org.xerial</groupId>
                <artifactId>sqlite-jdbc</artifactId>
                <version>3.30.1</version>
            </dependency>

#### 加载外部db ####

因为，SQLite 文件格式稳定，跨平台且向后兼容，开发人员保证至少在2050年之前保持这种格式。SQLite数据库文件通常用作在系统之间传输丰富内容的容器并作为数据的长期存档格式 。我们可以加载其他平台的db直接使用

Connection connection = DriverManager.getConnection("jdbc:sqlite:C:/work/mydatabase.db");

#### Memory方式 ####

SQLite数据库通常存储在单个普通磁盘文件中。但是，在某些情况下，数据库可能存储在内存中。完成此操作后，不会打开任何磁盘文件。而是纯粹在内存中创建一个新数据库。一旦数据库连接关闭，该数据库将不复存在。

这是内存数据库的数据库连接 URL 的语法：

jdbc:sqlite::memory:

jdbc:sqlite:

Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");

#### 连接配置 ####

SQLite默认支持需要配置，我们可以通过配置参数来更好使用和优化。

SQLiteConfig config = new SQLiteConfig();

#### 插入数据缓慢 ####

所谓”事务“就是指一组SQL命令，这些命令要么一起执行，要么都不被执行。在SQLite中，每调用一次sqlite3\_exec()函数，就会隐式地开启了一个事务，如果插入一条数据，就调用该函数一次，事务就会被反复地开启、关闭，会增大IO量。如果在插入数据前显式开启事务，插入后再一起提交，则会大大提高IO效率，进而加数据快插入速度。

开启事务只需在业务代码的前后各加一句开启与提交事务的命令即可。

package com.study.myweb.test;
    
    import org.sqlite.SQLiteConfig;
    
    import java.sql.*;
    
    public class Test {
    
        public static void main(String[] args) {
            long startTime, endTime, exeTime;
            startTime = System.currentTimeMillis();// 开始时间
            
            test();
            
            endTime = System.currentTimeMillis();// 结束时间
            exeTime = endTime - startTime;// 毫秒
            System.out.println("用时：" + exeTime / 1000 / 60 / 60 + "时" + exeTime / 1000 / 60 % 60 + "分" + exeTime / 1000 % 60 + "秒");
    
        }
    
        public static void test() {
            Connection connection = null;
            PreparedStatement statement = null;
            try {
                SQLiteConfig config = new SQLiteConfig();
                // 设置共享缓存
                //这允许单独的数据库连接共享相同的内存数据库。当然，共享内存数据库的所有数据库连接都必
                //须处于同一进程中。当与数据库的最后一个连接关闭时，数据库将被自动删除并回收内存。
                // config.setSharedCache(true);
                config.setSynchronous(SQLiteConfig.SynchronousMode.OFF);//关闭写同步
                Class.forName("org.sqlite.JDBC");
                connection = DriverManager.getConnection("jdbc:sqlite::memory:", config.toProperties());
    
    
                // 1、建表 DDL
                String createUser = "create table user(" +
                        "id integer primary key autoincrement," +
                        "name varchar(20)," +
                        "age integer" +
                        ")";
                statement = connection.prepareStatement(createUser);
                statement.executeUpdate();
                statement.close();
    
    
                // 2、插入数据
                String insertUserData = "insert into user(name,age) values (?,?)";
                connection.setAutoCommit(false);//关闭自动提交，否则批量插入很慢
                statement = connection.prepareStatement(insertUserData);
                for (int i = 1; i <= 100000000; i++) {
                    statement.setString(1, "张三" + i);
                    statement.setInt(2, i);
                    statement.addBatch();//把这条执行语句加到PreparedStatement对象的批处理命令中
                    if (i % 500 == 0) {
                        statement.executeBatch();//把以上添加到批处理命令中的所有命令一次过提交给数据库来执行
                    }
                }
                statement.executeBatch();//把以上添加到批处理命令中的所有命令一次过提交给数据库来执行
                statement.close();
                connection.commit();//手动提交
                connection.setAutoCommit(true);//自动提交
    
    
                // 3、查询语句
                String selectUserData = "select count(*) from user";
                statement = connection.prepareStatement(selectUserData);
                ResultSet set = statement.executeQuery();
                if (set.next()) {
                    System.out.println("总条数：" + set.getInt(1));
                }
                set.close();
                statement.close();
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
            }
        }
    }

总条数：100000000  
用时：0时1分27秒

插入1亿条数据只需1分27秒 。