如何向 SQLite 表添加 1300 行？

有 3 种相对简单的方法可以实现这一点。

a) 创建一个代表您生成代码的公式，然后将生成的代码复制并粘贴到合适的位置。

b) 将数据另存为 csv 并将文件复制为资产，然后您可以打开并读取文件，提取数据并将其插入到数据库助手的 onCreate 方法中。

C) 将数据另存为 csv 并使用 sqlite 工具导入和加载数据，然后将该数据库复制为资产，然后使用该数据库。

选项 A 非常简单，假设您有以下内容：-

并且您的代码显示您有一个 insertCity 方法，该方法采用 City 对象。假设您使用 City("Tokyo",37339804) 构建一个城市，然后插入一个城市，您的代码可能类似于：-

`insert(new ObjectCity("Tokyo",37339804L));`

• 已假设人口为长期。

然后您可以在单元格 C1 中输入公式为 ="insertCity(new ObjectCity("&CHAR(34)&A1&CHAR(34)&","&B1&"L));"。然后，您可以将此单元格复制到 c2-c1300（使用上面的电子表格为 c2-c17）。生成的电子表格将类似于：-

然后，您只需将生成的代码（单元格 c1-c1300）复制并粘贴到合适的位置即可。

这是一个工作示例：-

ObjectCity 类：-

public class ObjectCity {

    private String cityName;
    private Long cityPopulation;

    public ObjectCity(String cityName,Long cityPopulation) {
        this.cityName = cityName;
        this.cityPopulation = cityPopulation;
    }

    public String getCityName() {
        return cityName;
    }

    public Long getCityPopulation() {
        return cityPopulation;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public void setCityPopulation(Long cityPopulation) {
        this.cityPopulation = cityPopulation;
    }
}

DatabaseHelper DBHelper 类（基于问题中的代码）：-

public class DBHelper extends sqliteOpenHelper {

    public static final String MI_TABLA_CITIES = "city";

    public DBHelper(Context context) {
        super(context,"theDatabase",null,1);
    }

    @Override
    public void onCreate(sqliteDatabase db) {
        String crearTablaCities = "create table "+MI_TABLA_CITIES+" (ID_CITY INTEGER PRIMARY KEY AUTOINCREMENT,NAME_CITY TEXT,POPULATION_CITY IntegeR)";
        db.execSQL(crearTablaCities);
    }

    @Override
    public void onUpgrade(sqliteDatabase db,int i,int i1) {

    }

    public void insertCity(ObjectCity newCity){
        sqliteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();

        //cv.put("ID_CITY",newCity.getCityID());
        cv.put("NAME_CITY",newCity.getCityName());
        cv.put("POPULATION_CITY",newCity.getCityPopulation());
        db.insert(MI_TABLA_CITIES,cv);
        //db.close();

    }

    /* function to load the country data */
    public void loadData() {
        if(DatabaseUtils.queryNumEntries(this.getWritableDatabase(),MI_TABLA_CITIES)> 0) return;

        insertCity(new ObjectCity("Tokyo",37339804L));
        insertCity(new ObjectCity("Delhi",31181376L));
        insertCity(new ObjectCity("Shanghai",27795702L));
        insertCity(new ObjectCity("Sao Paulo",22237472L));
        insertCity(new ObjectCity("Mexico City",21918936L));
        insertCity(new ObjectCity("Dhaka",21741090L));
        insertCity(new ObjectCity("Cairo",21322750L));
        insertCity(new ObjectCity("Beijing",20896820L));
        insertCity(new ObjectCity("Mumbai",20667656L));
        insertCity(new ObjectCity("Osaka",19110616L));
        insertCity(new ObjectCity("Karachi",16459472L));
        insertCity(new ObjectCity("Chongqing",16382376L));
        insertCity(new ObjectCity("Istanbul",15415197L));
        insertCity(new ObjectCity("Buenos Aires",15257673L));
        insertCity(new ObjectCity("Kolkata",14974073L));
        insertCity(new ObjectCity("Kinshasa",14970460L));
        insertCity(new ObjectCity("Lagos",14862111L));
    }
}

• 注意 loadData 方法。

  • 这使用 DatabaseUtils queryNumEntries 方法来检查是否存在任何数据，并且仅在表中没有行时才加载数据。
  • 其余代码是从电子表格复制粘贴的，如上图（第二张图）。

最后一个调用活动 @H_611_68@mainActivity :-

public class MainActivity extends AppCompatActivity {

    DBHelper db;

    @Override
    protected void onCreate(Bundle savedInstanceStatE) {
        super.onCreate(savedInstanceStatE);
        setContentView(R.layout.activity_main);
        db = new DBHelper(this);
        db.loadData(); //<<<<< LOADS THE CITY DATA (if not already loaded)
    }
}

结果

运行上述内容并使用 Android studio 的数据库检查器显示：-

从第一个答案开始：-

C) 将数据另存为 csv 并使用 sqlite 工具导入和加载数据，然后将该数据库复制为资产，然后使用该数据库。

然后使用 Excel 保存原始工作表的 csv。这已导入 sqlite 工具 Navicat for sqlite（还有其他此类工具，但我更喜欢 Navicat）：-

数据库和连接已关闭（保存数据库）。

在Android studio中，在Android View和新建中右键单击应用，然后选择目录（单击）并从对话框 src/Android/assets 中选择（也就是在 app/src/main 中创建资产文件夹（可以在 Android studio 之外完成））。

数据库文件（名为 thedatabase.db）已复制到新创建的资产文件夹中。

Database Helper 类 DBHelperB 是按照 :-

[RoutePrefix]

@H_611_68@mainActivity 然后修改为：-

public class DBHelperB extends sqliteOpenHelper {


    /*
        Note database created in sqlite tool and data imported from spreadsheet
        database file is the copied into the assets folder (after creaTing the folder)
     */
    public static final String dbname = "thedatabase.db"; //<<<< asset file name must match
    public static final String MI_TABLA_CITIES = "city";

    private static volatile DBHelperB instance = null;

    // Prevent construction outside
    private DBHelperB(Context context) {
        super(context,1);
    }

    // instead of using db = new DBHelper(this); 
    // this allows db = DBHelperB.geTinstance(); 
    // i.e. a single instance (aka a singleton) is retrieved
    // IMPORTANTLY this also copies the asset DB if not already loaded 
    public static DBHelperB getDBInstance(Context context) {
        if (instance == null) {
            getAssetDB(context); // <<<<< Copies ASSET DB
            instance = new DBHelperB(context);
        }
        return instance;
    }

    /* Won't be called as the DB will exists */
    @Override
    public void onCreate(sqliteDatabase db) {
        String crearTablaCities = "create table "+MI_TABLA_CITIES+" (ID_CITY INTEGER PRIMARY KEY AUTOINCREMENT,cv);
        //db.close();

    }

    /* 
        Copies the DB from the assets folder unless the db already exists
        Note if this fails a runtime exception will occur. it is then
        IMPORTANT to look at the log to determine the cause
        More often than not a failure is due to the asset not having been copied correctly
    
     */
    private static void getAssetDB(Context context) {
        File dbFile = context.getDatabasePath(DBName);
        InputStream asset;
        OutputStream db;
        byte[] buffer = new byte[4096];
        if (dbFile.exists()) return; // Database exists so no need to copy
        /* Ensure that the data/data/<package_name>/databases folder exists */
        if (!dbFile.getParentFile().exists()) {
            dbFile.getParentFile().mkdirs();
        }
        try {
            asset = context.getAssets().open(DBName);
            db = new FiLeoutputStream(dbFilE);
            int length;
            while ((length = asset.read(buffer)) > 0) {
                db.write(buffer,length);
            }
            db.flush();
            db.close();
            asset.close();

        } catch (Exception E) {
            e.printStackTrace();
            throw new RuntimeException("Failed to copy asset file");
        }
    }
}

• 查看评论

运行后的结果，根据日志包括：-

public class MainActivity extends AppCompatActivity {

    DBHelper db;
    DBHelperB assetdb; //ADDED

    @Override
    protected void onCreate(Bundle savedInstanceStatE) {
        super.onCreate(savedInstanceStatE);
        setContentView(R.layout.activity_main);
        db = new DBHelper(this);
        db.loadData();
        // ADDED >
        assetdb = DBHelperB.getDBInstance(this); /* will copy the asset db if need be */
        /* Database inspector doesn't like this data so show data another way */
        cursor csr = assetdb.getWritableDatabase().query("city",null);
        DatabaseUtils.dumpcursor(csr);
    }
}