json - 如何在android的sqlite中快速批量插入json数据
问题描述
我已经编写了这段代码来将 json 数据插入到 android 中的 sqlite 中，它工作正常，但是我有 50000+ 行要插入，所以它需要很长时间才能插入到 sqlite 数据库中。那么如何以最快的方式插入这些数据，请给我我在 android 中非常新的代码。预先感谢。
下面我编写了插入数据的代码
private void insertItemDetails() {
final ProgressDialog loading = ProgressDialog .show(this,"Updating Data From Tally","Please wait");
StringRequest stringRequest=new StringRequest(Request.Method.GET, url,
new Response.Listener<String>() {
@Override
public void onResponse(String response) {
try {
loading.show();
itemDatabaseCon.open();
itemDatabaseCon.delete();
itemDatabaseCon.close();
itemDatabaseCon.open();
itemDatabaseCon.createTable();
int a=response.length();
// boolean b=a.equalsIgnoreCase("no");
Log.d("value", String.valueOf(a));
if (a==2) {
Log.d("inside item if loop ",response);
}
else {
JSONObject jsonObject = new JSONObject(response);
JSONArray array = jsonObject.getJSONArray("posts");
for (int i = 0; i < array.length(); i++) {
JSONObject ob = array.getJSONObject(i);
String stockid = ob.getString("stockid");
String itemname = ob.getString("itemname");
String group = ob.getString("group");
String baseunit = ob.getString("baseunit");
String alternateunit = ob.getString("alternateunit");
String gst = ob.getString("gst");
String hsn = ob.getString("hsn");
String mrp = ob.getString("mrp");
String sdtsellrate = ob.getString("sdtsellrate");
String closingstock = ob.getString("closingstock");
ContentValues contentValues = new ContentValues();
contentValues.put(Constant2.key_itemstockid, stockid);
contentValues.put(Constant2.key_itemname, itemname);
contentValues.put(Constant2.key_itemgroup, group);
contentValues.put(Constant2.key_itembaseunit, baseunit);
contentValues.put(Constant2.key_itemalternateunit, alternateunit);
contentValues.put(Constant2.key_itemgst, gst);
contentValues.put(Constant2.key_itemhsn, hsn);
contentValues.put(Constant2.key_itemmrp, mrp);
contentValues.put(Constant2.key_itemsdtsellrate, sdtsellrate);
contentValues.put(Constant2.key_itemclosingstock, closingstock);
itemDatabaseCon.insert(Constant2.Table_name, contentValues);
}
}
loading.dismiss();
} catch (JSONException e) {
e.printStackTrace();
}
}
}, new Response.ErrorListener() {
@Override
public void onErrorResponse(VolleyError error) {
Log.d("got api error ffff" , error.getMessage());
}
});
RequestQueue requestQueue= Volley.newRequestQueue(this);
requestQueue.add(stringRequest);
}
这是我的数据库控制器代码。
public class ItemDatabaseCon {
String TAG = "DBAdapter";
private SQLiteDatabase db;
private ItemDatabaseCon.DBHelper dbHelper;
public ItemDatabaseCon (Context context) {
dbHelper = new ItemDatabaseCon.DBHelper(context);
}
public void open() {
if (null == db || !db.isOpen()) {
try {
db = dbHelper.getWritableDatabase();
} catch (SQLiteException sqLiteException) {
}
}
}
public void close() {
if (db != null) {
db.close();
}
}
public int insert(String table, ContentValues values) {
try {
db = dbHelper.getWritableDatabase();
int y = (int) db.insert(table, null, values);
db.close();
Log.e("Data Inserted", "Item Data Inserted");
Log.e("number of row", y + "");
return y;
} catch (Exception ex) {
Log.e("Error Insert", ex.getMessage().toString());
return 0;
}
}
public void delete() {
db.execSQL("DROP TABLE IF EXISTS " + Constant2.Table_name);
}
public int getCount()
{
db = dbHelper.getWritableDatabase();
String qry="SELECT * FROM "+Constant2.Table_name;
Cursor cursor=db.rawQuery(qry,null);
return cursor.getCount();
}
public void createTable()
{
String create_sql = "CREATE TABLE IF NOT EXISTS " + Constant2.Table_name + "("
+ Constant2.key_id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ Constant2.key_itemstockid + " TEXT ," + Constant2.key_itemname + " TEXT ," + Constant2.key_itemgroup + " TEXT ,"
+ Constant2.key_itembaseunit + " TEXT ,"+ Constant2.key_itemalternateunit + " TEXT ,"+ Constant2.key_itemgst + " TEXT ,"
+ Constant2.key_itemhsn + " TEXT ,"+ Constant2.key_itemmrp + " TEXT ,"+ Constant2.key_itemsdtsellrate + " TEXT ,"
+ Constant2.key_itemclosingstock + " TEXT " + ")";
db.execSQL(create_sql);
}
public Cursor getAllRow(String table) {
return db.query(table, null, null, null, null, null, Constant2.key_id);
}
private class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context) {
super(context, Constant2.DB_Name, null, Constant2.Db_Version);
}
@Override
public void onCreate(SQLiteDatabase db) {
String create_sql = "CREATE TABLE IF NOT EXISTS " + Constant2.Table_name + "("
+ Constant2.key_id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ Constant2.key_itemstockid + " TEXT ," + Constant2.key_itemname + " TEXT ," + Constant2.key_itemgroup + " TEXT ,"
+ Constant2.key_itembaseunit + " TEXT ,"+ Constant2.key_itemalternateunit + " TEXT ,"+ Constant2.key_itemgst + " TEXT ,"
+ Constant2.key_itemhsn + " TEXT ,"+ Constant2.key_itemmrp + " TEXT ,"+ Constant2.key_itemsdtsellrate + " TEXT ,"
+ Constant2.key_itemclosingstock + " TEXT " + ")";
db.execSQL(create_sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + Constant2.Table_name);
}
}
}
解决方案
您可以在单个 SQLite 事务中进行插入。这将大大减少磁盘写入从 50000+ 到很少。
那是在循环开始使用 SQLiteDatabase 的beginTransaction()方法开始事务之前。循环完成后（所有行都已插入）成功使用setTransactionSuccessful()后面的方法endTransactionMethod()
- 请注意，如果您不这样做，
setTransactionSuccessful则更改将被回滚（因此，如果您遇到问题/错误并希望不应用更改（插入），请使用适当的逻辑，以便setTransactionSuccessful跳过但endTransaction运行）
例如以下可能是合适的：-
....
else {
itemDatabaseCon.beginTransaction(); //<<<<<<<<<< ADDDED start the transaction
JSONObject jsonObject = new JSONObject(response);
JSONArray array = jsonObject.getJSONArray("posts");
for (int i = 0; i < array.length(); i++) {
JSONObject ob = array.getJSONObject(i);
String stockid = ob.getString("stockid");
String itemname = ob.getString("itemname");
String group = ob.getString("group");
String baseunit = ob.getString("baseunit");
String alternateunit = ob.getString("alternateunit");
String gst = ob.getString("gst");
String hsn = ob.getString("hsn");
String mrp = ob.getString("mrp");
String sdtsellrate = ob.getString("sdtsellrate");
String closingstock = ob.getString("closingstock");
ContentValues contentValues = new ContentValues();
contentValues.put(Constant2.key_itemstockid, stockid);
contentValues.put(Constant2.key_itemname, itemname);
contentValues.put(Constant2.key_itemgroup, group);
contentValues.put(Constant2.key_itembaseunit, baseunit);
contentValues.put(Constant2.key_itemalternateunit, alternateunit);
contentValues.put(Constant2.key_itemgst, gst);
contentValues.put(Constant2.key_itemhsn, hsn);
contentValues.put(Constant2.key_itemmrp, mrp);
contentValues.put(Constant2.key_itemsdtsellrate, sdtsellrate);
contentValues.put(Constant2.key_itemclosingstock, closingstock);
itemDatabaseCon.insert(Constant2.Table_name, contentValues);
}
itemDatabaseCon.setTransactionSuccessful(); //<<<<<<<<<< ADDED indicate that changes (inserts) are all good
itemDatabaseCon.endTransaction(); //<<<<<<<<<< ADDED end the transaction
}
loading.dismiss();
....
- //<<<<<<<<<<表示更改/添加的代码
编辑
但是，考虑到插入方法，上述内容将没有影响，因为您在插入后关闭数据库。关闭数据库然后重新打开它是非常耗费资源的。
因此，为了受益于在单个事务中运行所有插入，您可以使用：-
public int insert(String table, ContentValues values) {
try {
db = dbHelper.getWritableDatabase();
int y = (int) db.insert(table, null, values);
//db.close(); //<<<<<<<<<< Commented out so as to not close the database
Log.e("Data Inserted", "Item Data Inserted");
Log.e("number of row", y + "");
return y;
} catch (Exception ex) {
Log.e("Error Insert", ex.getMessage().toString());
return 0;
}
}
推荐阅读
- mysql - 合并两个查询时有什么方法可以优化这个 MySQL 查询？
- xmlhttprequest - 页面关闭替代方案中的 SyncXHR
- oauth - Google OAuth 同意屏幕不显示新请求的敏感范围
- typo3 - Typo3 10 TCA "'type' => 'inline'," 创建新关系并上传按钮自动隐藏
- testing - 如何在同一个示例项目中使用 JUnit 和手动测试自定义 gradle 插件
- postgresql - 如何在 Hasura 中创建函数 postgresql 连接表
- c++ - 如何使用 libcurl 将大字符串发送到乌鸦服务器？
- python - Python if-condition根据不同的列值导入json文件
- firebase - 使用firebase云功能和firestore实现发送/接受好友请求的通知系统
- amazon-web-services - 如何在 AWS Managed Workflows for Apache Airflow 中启用 API？