Android Sqlite - 在更多带有事务的表上插入 6000 条记录，正确的方法？

Question

我是安卓新手。我在包含 4 个表的 Android 上的 Sqlite 数据库中插入 6000 条记录时遇到问题。在单个表中插入记录，意味着在其他表中插入其他记录。鉴于记录的数量，我看到我需要使用事务，但使用数据库相关类所采用的结构是不可能的(因为锁定)。

为了简化，下面插入 Pois 和 Images(以及 Helper)的代码。什么是正确的方法？静态助手？静态 SQLite 数据库？多谢你们!

helper

    public class PoiDatabaseHelper extends SQLiteOpenHelper {

        private static final String DB_NAME = "poi_database"; 
        private static final int DB_VERSION = 1; 

        public PoiDatabaseHelper(Context context) { 
            super(context, DB_NAME, null, DB_VERSION); 
        } 

        @Override
        public void onCreate(SQLiteDatabase db) { 
            String poiTable = ""; 
            poiTable += "CREATE TABLE poi ("; 
            poiTable += " poi_id INTEGER,"; 
            poiTable += " name TEXT NOT NULL,"; 
            poiTable += " description_en TEXT,"; 
            poiTable += " description_it TEXT,"; 
            poiTable += " time TEXT,"; 
            poiTable += " address TEXT,";
            poiTable += " latitude TEXT,"; 
            poiTable += " logitude TEXT,"; 
            poiTable += " primary key  (poi_id)";
            poiTable += ")"; 

            String imgTable = "";
            imgTable += "CREATE TABLE image ("; 
            imgTable += " image_id INTEGER,"; 
            imgTable += " url TEXT,"; 
            imgTable += " thumb INTEGER,";
            imgTable += " poi_id INTEGER NOT NULL,";
            imgTable += " primary key  (image_id),";
            imgTable += " foreign key (poi_id) references poi(poi_id) on delete set NULL on update cascade";
            imgTable += ")";
} 

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
        //TODO
    }

}

兴趣点插入

public class PoiDaoImpl implements PoiDao {

    private Context context;
    private PoiDatabaseHelper poiDatabaseHelper;
    private SQLiteDatabase poiDatabase;

    public PoiDaoImpl(Context context) {
        this.context = context;
        poiDatabaseHelper = new PoiDatabaseHelper(context);
        poiDatabase = poiDatabaseHelper.getWritableDatabase();

    }

    @Override
    public void insert(Poi poi) {
        ContentValues values = new ContentValues(); 

        values.put("poi_id", poi.getId());
        values.put("name", poi.getName());
        values.put("description_en", poi.getDescriptionEn());
        values.put("description_it", poi.getDescriptionIt());
        values.put("time", poi.getTime());
        values.put("address", poi.getAddress());
        values.put("latitude", poi.getCoordinates().getLatitude());
        values.put("logitude", poi.getCoordinates().getLongitude());


        ImageDao imageDao = new ImageDaoImpl(context);
        for(Image image: poi.getImages()) {
            if(!image.getUrl().equals("")) {
                imageDao.insert(image, poi.getId());
            }
        }

        long id = poiDatabase.insert("poi", null, values); 
        //poiDatabase.close();

        //poiDatabaseHelper.close();

    }


    @Override
    public void insertAllPois(List<Poi> pois) {
        poiDatabase.beginTransaction();
        try {
            for(Poi poi: pois) {
                insert(poi);
            }
            poiDatabase.setTransactionSuccessful();
        } finally {
            poiDatabase.endTransaction();
        }


    }
}

图片插入

public class ImageDaoImpl implements ImageDao {

    private Context context;
    private PoiDatabaseHelper poiDatabaseHelper;
    private SQLiteDatabase poiDatabase;


    public ImageDaoImpl(Context context) {
        this.context = context;
        poiDatabaseHelper = new PoiDatabaseHelper(context);
        poiDatabase = poiDatabaseHelper.getWritableDatabase();

    }

    @Override
    public void insert(Image image, int idPoi) {
        ContentValues values = new ContentValues(); 
        values.put("url", image.getUrl());
        if(image.getThumb()) {
            values.put("thumb", 1);
        }
        else {
            values.put("thumb", 0);
        }

        values.put("poi_id", idPoi);
        //poiDatabase = poiDatabaseHelper.getWritableDatabase();
        long id = poiDatabase.insert("image", null, values);
        poiDatabase.close();
    }
}

Answer 1

使用该代码，我使用多线程没有问题。我看到您没有使用 helper 的唯一实例 ....

我以这种方式提供辅助实例 -> dblectura = ControladorBBDD.getBBDD(_ctx);在哪里 _ctx = activity_p.getApplicationContext();

public class ControladorBBDD extends SQLiteOpenHelper 
private static ControladorBBDD instancia;

private ControladorBBDD(Context ctx_p) throws Exception {

        super(ctx_p, DB_NAME, null, DATABASE_VERSION);
        try {
            ControladorBBDD.ctx = ctx_p;
            DB_PATH = ctx.getDatabasePath(DB_NAME).getAbsolutePath();
            String myPath = DB_PATH;// + DB_NAME;
            this.createDataBase();

            db = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READWRITE);

        } catch (SQLiteException ex) {
            Log.e(TAG+".ControladorBBDD", ex.getMessage());
            Conexiones.escribirLog("Error "+TAG+".ControladorBBDD: " + Log.getStackTraceString(ex));
            db.close();
        }
    }

public static synchronized ControladorBBDD getBBDD(Context ctx_p)
                throws Exception {
            if (instancia == null) {
                instancia = new ControladorBBDD(ctx_p);
            }
            return instancia;
        }