Android多传感器数据存入SQLite崩溃问题及高效存储方案咨询

Hey there! Let's tackle your sensor data storage crash and efficiency issues step by step. First, let's break down why your current code might be causing crashes, then walk through optimized solutions.

Why Your Current Code Might Be Crashing #

1. Frequent Database Open/Close Cycles: Each insert method calls getWritableDatabase() and immediately close() it. SQLite doesn't handle frequent connection churn well, especially across multiple threads—this can lead to connection conflicts and crashes.
2. Redundant Code & Human Error: Having 6 almost identical insert functions increases the chance of typos (like your SenosorsDBAdapter typo) and makes maintenance harder.
3. Unmanaged Thread Competition: Even with separate threads, concurrent access to the database without proper synchronization can trigger race conditions and SQLite exceptions.

Optimized Sensor Data Storage Solution #

Here's a refactored approach that fixes crashes and boosts performance:

Step 1: Refactor the Database Adapter

We'll reuse a single database connection, create a universal insert method, and add batch insertion for high-frequency sensor data:

public class SensorsDBAdapter {
    private SensorsDBHelper myHelper;
    private SQLiteDatabase writableDb;

    public SensorsDBAdapter(Context context) {
        myHelper = new SensorsDBHelper(context.getApplicationContext()); // Use app context to avoid leaks
        writableDb = myHelper.getWritableDatabase(); // Reuse this connection
    }

    // Universal insert method for all sensor types
    public long insertSensorData(int sensorType, String sensorName, String date, float x, float y, float z) {
        long id = -1;
        if (writableDb == null || !writableDb.isOpen()) {
            writableDb = myHelper.getWritableDatabase();
        }

        ContentValues contentValues = new ContentValues();
        contentValues.put(SensorsDBHelper.CREATION_DATE, date);
        contentValues.put(SensorsDBHelper.SENSOR_NAME, sensorName);
        contentValues.put(SensorsDBHelper.V_0, x);
        contentValues.put(SensorsDBHelper.V_1, y);
        contentValues.put(SensorsDBHelper.V_2, z);

        String tableName = getTableNameBySensorType(sensorType);
        if (tableName != null) {
            try {
                id = writableDb.insert(tableName, null, contentValues);
                Log.i("insertData", String.format("%s data inserted successfully", tableName));
            } catch (SQLiteException e) {
                Log.e("insertError", "Failed to insert data for sensor type: " + sensorType, e);
            }
        }
        return id;
    }

    // Batch insert for high-frequency data (way more efficient than single inserts)
    public void batchInsertSensorData(List<SensorData> sensorDataList) {
        if (writableDb == null || !writableDb.isOpen() || sensorDataList.isEmpty()) {
            return;
        }

        try {
            writableDb.beginTransaction(); // Minimize disk I/O with transactions
            for (SensorData data : sensorDataList) {
                ContentValues contentValues = new ContentValues();
                contentValues.put(SensorsDBHelper.CREATION_DATE, data.date);
                contentValues.put(SensorsDBHelper.SENSOR_NAME, data.sensorName);
                contentValues.put(SensorsDBHelper.V_0, data.x);
                contentValues.put(SensorsDBHelper.V_1, data.y);
                contentValues.put(SensorsDBHelper.V_2, data.z);

                String tableName = getTableNameBySensorType(data.sensorType);
                if (tableName != null) {
                    writableDb.insert(tableName, null, contentValues);
                }
            }
            writableDb.setTransactionSuccessful();
            Log.i("batchInsert", "Batch inserted " + sensorDataList.size() + " records");
        } catch (SQLiteException e) {
            Log.e("batchInsertError", "Batch insert failed", e);
        } finally {
            writableDb.endTransaction();
        }
    }

    // Helper to map sensor types to table names
    private String getTableNameBySensorType(int sensorType) {
        return switch (sensorType) {
            case Sensor.TYPE_ACCELEROMETER -> SensorsDBHelper.TABLE_ACCELEROMETER;
            case Sensor.TYPE_GYROSCOPE -> SensorsDBHelper.TABLE_GYROSCOPE;
            case Sensor.TYPE_LINEAR_ACCELERATION -> SensorsDBHelper.TABLE_LINEAR_ACCELERATION;
            case Sensor.TYPE_ROTATION_VECTOR -> SensorsDBHelper.TABLE_ROTATION_VECTOR;
            case Sensor.TYPE_MAGNETIC_FIELD -> SensorsDBHelper.TABLE_MAGNETOMETER;
            case Sensor.TYPE_ORIENTATION -> SensorsDBHelper.TABLE_ORIENTATION;
            default -> {
                Log.w("tableError", "Unknown sensor type: " + sensorType);
                yield null;
            }
        };
    }

    // Close the database when done (call this when stopping data collection)
    public void close() {
        if (writableDb != null && writableDb.isOpen()) {
            writableDb.close();
        }
        myHelper.close();
    }

    // Data class for batch insertion
    public static class SensorData {
        public int sensorType;
        public String sensorName;
        public String date;
        public float x;
        public float y;
        public float z;

        public SensorData(int sensorType, String sensorName, String date, float x, float y, float z) {
            this.sensorType = sensorType;
            this.sensorName = sensorName;
            this.date = date;
            this.x = x;
            this.y = y;
            this.z = z;
        }
    }

    static class SensorsDBHelper extends SQLiteOpenHelper {
        private static final String DATABASE_NAME = "sensorsDatabase.db";
        private static final int DATABASE_VERSION = 1;

        private static final String TABLE_ACCELEROMETER = "ACCELEROMETER_Table";
        private static final String TABLE_GYROSCOPE = "GYROSCOPE_sensorsTable";
        private static final String TABLE_LINEAR_ACCELERATION = "LinearAcceleration_sensorsTable";
        private static final String TABLE_ROTATION_VECTOR = "RotationVector_sensorsTable";
        private static final String TABLE_MAGNETOMETER = "Magnetometer_sensorsTable";
        private static final String TABLE_ORIENTATION = "Orientation_sensorsTable";

        public static final String CREATION_DATE = "creation_date";
        public static final String SENSOR_NAME = "sensor_name";
        public static final String V_0 = "value_x";
        public static final String V_1 = "value_y";
        public static final String V_2 = "value_z";

        // Reusable table creation template
        private static final String CREATE_TABLE_SQL = "CREATE TABLE %s (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "%s TEXT NOT NULL, " +
                "%s TEXT NOT NULL, " +
                "%s REAL NOT NULL, " +
                "%s REAL NOT NULL, " +
                "%s REAL NOT NULL)";

        public SensorsDBHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // Create all sensor tables with the template
            db.execSQL(String.format(CREATE_TABLE_SQL, TABLE_ACCELEROMETER,
                    CREATION_DATE, SENSOR_NAME, V_0, V_1, V_2));
            db.execSQL(String.format(CREATE_TABLE_SQL, TABLE_GYROSCOPE,
                    CREATION_DATE, SENSOR_NAME, V_0, V_1, V_2));
            db.execSQL(String.format(CREATE_TABLE_SQL, TABLE_LINEAR_ACCELERATION,
                    CREATION_DATE, SENSOR_NAME, V_0, V_1, V_2));
            db.execSQL(String.format(CREATE_TABLE_SQL, TABLE_ROTATION_VECTOR,
                    CREATION_DATE, SENSOR_NAME, V_0, V_1, V_2));
            db.execSQL(String.format(CREATE_TABLE_SQL, TABLE_MAGNETOMETER,
                    CREATION_DATE, SENSOR_NAME, V_0, V_1, V_2));
            db.execSQL(String.format(CREATE_TABLE_SQL, TABLE_ORIENTATION,
                    CREATION_DATE, SENSOR_NAME, V_0, V_1, V_2));
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // Add data migration logic here if needed (don't drop tables in production!)
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACCELEROMETER);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_GYROSCOPE);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_LINEAR_ACCELERATION);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_ROTATION_VECTOR);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_MAGNETOMETER);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORIENTATION);
            onCreate(db);
        }
    }
}

Step 2: Usage Examples

Single Insert (for low-frequency sensors)

// Reuse the same adapter instance throughout your session
SensorsDBAdapter dbAdapter = new SensorsDBAdapter(getApplicationContext());

// In your sensor callback:
long insertId = dbAdapter.insertSensorData(
        Sensor.TYPE_ACCELEROMETER,
        "Accelerometer",
        String.valueOf(System.currentTimeMillis()), // Use timestamp instead of string for better performance
        xValue, yValue, zValue
);

Batch Insert (for high-frequency sensors like accelerometers)

List<SensorsDBAdapter.SensorData> dataBuffer = new ArrayList<>();
final int BATCH_SIZE = 100; // Adjust based on your needs

// In your sensor callback:
dataBuffer.add(new SensorsDBAdapter.SensorData(
        Sensor.TYPE_ACCELEROMETER,
        "Accelerometer",
        String.valueOf(System.currentTimeMillis()),
        xValue, yValue, zValue
));

// Insert when buffer reaches batch size
if (dataBuffer.size() >= BATCH_SIZE) {
    dbAdapter.batchInsertSensorData(dataBuffer);
    dataBuffer.clear();
}

// Don't forget to insert remaining data when stopping collection:
if (!dataBuffer.isEmpty()) {
    dbAdapter.batchInsertSensorData(dataBuffer);
}

// Close the adapter when done:
dbAdapter.close();

Key Additional Tips #

• Thread Safety: Use a dedicated HandlerThread for all database operations to avoid concurrent access issues. Send sensor data to this thread for insertion instead of using multiple random threads.
• Date Storage: Store timestamps as long values (e.g., System.currentTimeMillis()) instead of strings—this saves space and makes queries faster.
• Memory Leaks: Always use the application context when initializing the adapter to prevent activity context leaks.

内容的提问来源于stack exchange，提问作者Mari