前端之家

  1. 首页
  2. 问答

在AsyncTask中将数据提取并插入到SQLite数据库中会冻结UI

• 问答

我正在尝试构建一个离线报价应用程序，我的服务器中有大约5000个报价以JSON格式显示，我想获取url并将数据插入到异步任务中的SQLite数据库中。如何在不冻结UI的情况下进行操作？这是我的AsyncTask代码

public class LongOperation extends AsyncTask<Void,Void,String> {
        MyDatabase quoteManager;
        public static final String MY_PREFS_NAME = "MyPrefsFile";


        @Override
        protected String doInBackground(Void... voids) {

            quoteManager = new MyDatabase(getapplicationContext());
            RequestQueue queue = Volley.newRequestQueue(getapplicationContext());
            String url = "http://10.0.2.2/quotelink/update.php?lastid=" + 100 + "&cat=" + MyDatabase.TABLE_NAME;

            // Request a string response from the provided URL.
            StringRequest stringRequest = new StringRequest(Request.Method.GET,url,new Response.Listener<String>() {
                        @Override
                        public void onResponse(String response) {
                            if (!response.equals("updated")) {
                                try {

                                    JSONObject jsonObj = new JSONObject(response);
                                    JSONArray contacts = jsonObj.getJSONArray("quotes");

                                    for (int i = 0; i < contacts.length(); i++) {
                                        JSONObject c = contacts.getJSONObject(i);
                                        String id = c.getString("id");
                                        String quote = c.getString("quote");
                                        String person = c.getString("person");
                                        String category = c.getString("category");
                                        quoteManager.insertQuotes(id,quote,person,category);

                                    }

                                } catch (JSONException e) {
//                                Log.e("ERROR",e.getMessage());
                                }
                            }
                        }
                    },new Response.ErrorListener() {
                @Override
                public void onErrorResponse(VolleyError error) {
                    showsnackBar(false);
                }
            });

            queue.add(stringRequest);

            RequestQueue queue2 = Volley.newRequestQueue(getapplicationContext());
            String url2 = "http://10.0.2.2/quotelink/update.php?lastid=" + 100 + "&cat=" + MyDatabase.TABLE_NAME2;

            StringRequest stringRequest2 = new StringRequest(Request.Method.GET,url2,new Response.Listener<String>() {
                        @Override
                        public void onResponse(String response) {
                            if (!response.equals("updated")) {
                                try {
                                    JSONObject jsonObj = new JSONObject(response);
                                    JSONArray contacts = jsonObj.getJSONArray("persons");

                                    // looping through All Contacts
                                    for (int i = 0; i < contacts.length(); i++) {
                                        JSONObject c = contacts.getJSONObject(i);
                                        String id = c.getString("id");
                                        String name = c.getString("name");
                                        String bio = c.getString("bio");
                                        quoteManager.insertPeople(id,name,bio);
                                    }

                                } catch (JSONException e) {
//                                Log.e("ERROR",new Response.ErrorListener() {
                @Override
                public void onErrorResponse(VolleyError error) {
                    showsnackBar(false);
                }
            });

            queue2.add(stringRequest2);


            //pass to the final one if the persons download is success
//            if(passnextDownload2){

            //category
            RequestQueue queue3 = Volley.newRequestQueue(getapplicationContext());
            String url3 = "http://10.0.2.2/quotelink/update.php?lastid=" + 100 + "&cat=" + MyDatabase.TABLE_NAME3;


            // Request a string response from the provided URL.
            StringRequest stringRequest3 = new StringRequest(Request.Method.GET,url3,new Response.Listener<String>() {
                        @Override
                        public void onResponse(String response) {
                           // Display the first 500 characters of the response string.
//                        textView.setText("Response is: "+ response.substring(0,500));
                            if (!response.equals("updated")) {
                                try {

                                    JSONObject jsonObj = new JSONObject(response);

                                    // Getting JSON Array node
                                    JSONArray contacts = jsonObj.getJSONArray("category");

                                    // looping through All Contacts
                                    for (int i = 0; i < contacts.length(); i++) {
                                        JSONObject c = contacts.getJSONObject(i);
                                        String id = c.getString("id");
                                        String name = c.getString("name");
                                        String bio = c.getString("bio");
                                        quoteManager.insertCategory(id,bio);
                                    }


                                } catch (JSONException e) {
//                                Log.e("ERROR",e.getMessage());

                                }
                            }
                        }
                    },new Response.ErrorListener() {
                @Override
                public void onErrorResponse(VolleyError error) {
                    showsnackBar(false);
                }
            });

            queue3.add(stringRequest3);

            return "retru";
        }

        @Override
        protected void onPostExecute(String s) {
            showsnackBar(true);
            super.onPostExecute(s);
        }
    }

这是我的数据库类代码


package com.example.database;

import android.content.Contentvalues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

import java.util.ArrayList;

public class MyDatabase extends SQLiteOpenHelper {

  private static final String DATABASE_NAME = "quotelink.db";
  private static final int DATABASE_VERSION = 1;

  public static final String TABLE_NAME  = "QUOTES";
  public static final String TABLE_NAME2  = "PERSONS";
  public static final String TABLE_NAME3  = "CATEGORY";
  public static final String TABLE_NAME4  = "SAVED";
  public static final String COL_1 = "ID";
  public static final String COL_2 = "QUOTE";
  public static final String COL_3 = "PERSON";
  public static final String COL_4 = "CATEGORY";

  //person section
  public static final String COL_21 = "ID";
  public static final String COL_22 = "NAME";
  public static final String COL_23 = "ABOUT";

  //category section
  public static final String COL_31 = "ID";
  public static final String COL_32 = "NAME";
  public static final String COL_33 = "ABOUT";
  public static final String COL_34 = "CREATIONDATE";

  //saved quotes
  public static final String COL_41 = "ID";
  public static final String COL_42 = "QUOTEID";
  public MyDatabase(Context context) {
    super(context,DATABASE_NAME,null,DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase sqLiteDatabase) {
    sqLiteDatabase.execSQL("create table " + TABLE_NAME + "("+COL_1+" INTEGER,"+COL_2+" TEXT,"+COL_3+" TEXT,"+COL_4+" INTEGER,"+COL_34+" datetime default current_timestamp)");
    sqLiteDatabase.execSQL("create table " + TABLE_NAME2 + "("+COL_21+" INTEGER,"+COL_22+" TEXT,"+COL_23+" TEXT,"+COL_34+" datetime default current_timestamp)");
    sqLiteDatabase.execSQL("create table " + TABLE_NAME3 + "("+COL_31+" INTEGER,"+COL_32+" TEXT,"+COL_33+" TEXT,"+COL_34+" datetime default current_timestamp)");
    sqLiteDatabase.execSQL("create table " + TABLE_NAME4 + "(ID integer primary key autoincrement,QUOTEID TEXT)");
  }




  @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase,int i,int i1) {
      sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
      sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME2);
      sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME3);
      sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME4);
      onCreate(sqLiteDatabase);
    }

  public boolean insertQuotes(String ID,String quote,String person,String category){
//        Log.e("RESADRESAD","INSERTING quotes");
    boolean returnBoolen = false;
    SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
    Contentvalues contentvalues = new Contentvalues();
    contentvalues.put(COL_1,ID);
    contentvalues.put(COL_2,quote);
    contentvalues.put(COL_3,person);
    contentvalues.put(COL_4,category);
//        Log.e("RESAD QUOITES",String.valueOf(checkForRecord(ID,TABLE_NAME)));
    //if there is no record,insert
    if(checkForRecord(ID,TABLE_NAME) == false){

      try{
        sqLiteDatabase.beginTransaction();
        long result = sqLiteDatabase.insert(TABLE_NAME,contentvalues);
        if(result == -1){
          returnBoolen = false;
        } else{
          returnBoolen = true;
        }
      } catch(Exception e) {
      } finally{
        sqLiteDatabase.setTransactionSuccessful();
        sqLiteDatabase.endTransaction();
      }

    } else{
      //upgrade the current record
      try{
        sqLiteDatabase.beginTransaction();
        sqLiteDatabase.update(TABLE_NAME,contentvalues,"ID=?",new String[]{ID});
        return true;
      } catch (Exception e){
        return false;
      } finally {
        sqLiteDatabase.setTransactionSuccessful();
        sqLiteDatabase.endTransaction();
      }
    }
    return returnBoolen;
  }

  public boolean insertPeople(String ID,String name,String bio){
    boolean returnBoolen = false;
    SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
    Contentvalues contentvalues = new Contentvalues();
    contentvalues.put(COL_21,ID);
    contentvalues.put(COL_22,name);
    contentvalues.put(COL_23,bio);
    //if there is no record,TABLE_NAME2) == false){

      try{
        sqLiteDatabase.beginTransaction();
        long result = sqLiteDatabase.insert(TABLE_NAME2,contentvalues);
        if(result == -1){
          returnBoolen = false;
        } else{
          returnBoolen = true;
        }
      } catch(Exception e) {

      } finally{
        sqLiteDatabase.setTransactionSuccessful();
        sqLiteDatabase.endTransaction();
      }
    } else{
      //upgrade the current record
      try{
        sqLiteDatabase.update(TABLE_NAME2,new String[]{ID});
        return true;
      } catch (Exception e){
        return false;
      }
    }
    return returnBoolen;
  }

  public boolean checkSaved(String ID){

      SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
      String sql = "SELECT EXISTS (SELECT * FROM "+TABLE_NAME4+" WHERE QUOTEID='"+ID+"' LIMIT 1)";
      Cursor cursor = sqLiteDatabase.rawQuery(sql,null);
      cursor.moveToFirst();

      // cursor.getInt(0) is 1 if column with value exists
      if (cursor.getInt(0) == 1) {
          cursor.close();
          return true;
      } else {
          cursor.close();
          return false;
      }
  }
    public boolean insertSaved(String ID){
        boolean returnBoolen = false;
        SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
        Contentvalues contentvalues = new Contentvalues();
        contentvalues.put(COL_42,ID);

            try{
                sqLiteDatabase.beginTransaction();
                long result = sqLiteDatabase.insert(TABLE_NAME4,contentvalues);
                if(result == -1){
                    returnBoolen = false;
                } else{
                    returnBoolen = true;
                }
            } catch(Exception e) {

            } finally{
                sqLiteDatabase.setTransactionSuccessful();
                sqLiteDatabase.endTransaction();
            }

        return returnBoolen;
    }


    public boolean unsaveQuote(String ID){
      SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
      int rowDeleted = sqLiteDatabase.delete(TABLE_NAME4,"QUOTEID" + " =?",new String[] {String.valueOf(ID)});
      sqLiteDatabase.close();//This is very important once database operation is done.
      if(rowDeleted != 0){
        //delete success.
        return true;
      } else {
        return false;
        //delete failed.
      }
    }


  public boolean insertCategory(String ID,String bio){
    boolean returnBoolen = false;

    SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
    Contentvalues contentvalues = new Contentvalues();
    contentvalues.put(COL_31,ID);
    contentvalues.put(COL_32,name);
    contentvalues.put(COL_33,TABLE_NAME3) == false){

      try{
        sqLiteDatabase.beginTransaction();
        long result = sqLiteDatabase.insert(TABLE_NAME3,contentvalues);
        if(result == -1){
          returnBoolen = false;
        } else{
          returnBoolen = true;
        }
      } catch(Exception e) {

      } finally{
        sqLiteDatabase.setTransactionSuccessful();
        sqLiteDatabase.endTransaction();
      }

    } else{
      //upgrade the current record
      try{
        sqLiteDatabase.update(TABLE_NAME3,new String[]{ID});
        return true;
      } catch (Exception e){
        return false;
      }
    }
    return returnBoolen;
  }

  public boolean checkForRecord(String ID,String TABLE_NAME_STRING) {
    SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
    String sql = "SELECT EXISTS (SELECT * FROM "+TABLE_NAME_STRING+" WHERE ID='"+ID+"' LIMIT 1)";
    Cursor cursor = sqLiteDatabase.rawQuery(sql,null);
    cursor.moveToFirst();

    // cursor.getInt(0) is 1 if column with value exists
    if (cursor.getInt(0) == 1) {
      cursor.close();
      return true;
    } else {
      cursor.close();
      return false;
    }
  }


  public Cursor getData(int ID,String TABLE_NAME_STRING){
    SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
    Cursor cursorc = sqLiteDatabase.rawQuery("SELECT * FROM " + TABLE_NAME_STRING+" WHERE ID='"+ID+"'",null);
    return cursorc;
  }

  public Cursor getallData(String TABLE_NAME_STRING){
    SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
    Cursor res = sqLiteDatabase.rawQuery("SELECT * from " + TABLE_NAME_STRING + " ",null);
    return res;
  }

  public Integer deleteData(String id,String TABLE_NAME_STRING){
    SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
    return sqLiteDatabase.delete(TABLE_NAME_STRING,"ID = ?",new String[]{id});
  }

  public String getLastID(MyDatabase quoteManager,String TABLE_NAME_STRING){
    String returnString = "0";
    SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
    Cursor cursorc = sqLiteDatabase.rawQuery("SELECT * FROM " + TABLE_NAME_STRING + " WHERE id=(SELECT max(id) FROM "+TABLE_NAME_STRING+")",null);
    if (cursorc != null) {
      while (cursorc.moveToNext()) {
        returnString = cursorc.getString(0);
      }
    } else{
      returnString = "0";
    }

    return returnString;

  }

  public ArrayList<String> getallIDs(String TABLE_NAME_STRING){
    String returnString = "0";
    ArrayList<String> idArray = new ArrayList<>();

    SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
    Cursor cursorc = sqLiteDatabase.rawQuery("SELECT id FROM " + TABLE_NAME_STRING +" WHERE 1",null);
    if (cursorc != null) {
      while (cursorc.moveToNext()) {
//                returnString = cursorc.getString(0);
        idArray.add(cursorc.getString(0));
      }
    } else{
      returnString = "0";
    }

    return idArray;
  }

  public long getRowNumber(String TABLE_NAME_STRING){
    SQLiteDatabase sqLiteDatabase = this.getwritabledatabase();
    long returnThis;
    if(DatabaseUtils.queryNumEntries(sqLiteDatabase,TABLE_NAME_STRING) == -1){
      returnThis = 0;
    } else{
      returnThis = DatabaseUtils.queryNumEntries(sqLiteDatabase,TABLE_NAME_STRING);
    }
    return returnThis;
  }

}

提前谢谢

vacant_7 回答：在AsyncTask中将数据提取并插入到SQLite数据库中会冻结UI
暂时没有好的解决方案，如果你有好的解决方案，请发邮件至：iooj@foxmail.com
android-databaseandroid-sqliteperformancesqlitesqliteopenhelper
本文链接：https://www.f2er.com/3080050.html

大家都在问