android - 数据库锁定错误sqlite android

Question

我在 sqllite android 中收到数据库被锁定错误。我的代码有什么问题吗？我尝试了从这里找到的各种东西，甚至试图在我调用查询后让线程休眠，但没有用。我仍然收到数据库锁定错误。谁能告诉我为什么会这样？我应该怎么做才能解决这个错误？

主 Activity 类:

import com.google.android.gms.common.ConnectionResult;
import com.google.android.gms.common.GooglePlayServicesUtil;
import com.google.android.gms.maps.CameraUpdateFactory;
import com.google.android.gms.maps.GoogleMap;

import com.google.android.gms.maps.model.BitmapDescriptorFactory;
import com.google.android.gms.maps.model.LatLng;
import com.google.android.gms.maps.model.MarkerOptions;
import com.google.gson.Gson;

public class MapViewActivity extends Activity implements LocationListener,
        SensorEventListener, OnClickListener {

    GoogleMap googleMap;

    private boolean started = false;
    private ArrayList<AccelLocData> sensorData;
    private SensorManager sensorManager;
    private Button btnStart, btnStop;
    private String provider;

    // File root, dir, sensorFile;
    FileOutputStream fOut;
    private Sensor mAccelerometer;
    private FileWriter writer;
    private DatabaseHelper databaseHelper;
    private BroadcastReceiver alarmReceiver;
    private PendingIntent pendingIntentSender, pendingIntentReceiver;

    private AlarmManager alarmManager;
    private Intent alarmIntent,alarmIntent2;

    // private Button btnUpload;

    @SuppressLint("NewApi")
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        try {

            databaseHelper = new DatabaseHelper(this);
            databaseHelper.removeAll();


            Log.v("datacount",
                    Integer.toString(databaseHelper.getLocDataCount()));

            sensorManager = (SensorManager) getSystemService(Context.SENSOR_SERVICE);
            mAccelerometer = sensorManager
                    .getDefaultSensor(Sensor.TYPE_ACCELEROMETER);

            btnStart = (Button) findViewById(R.id.btnStart);
            btnStop = (Button) findViewById(R.id.btnStop);
            btnStart.setOnClickListener(this);
            btnStop.setOnClickListener(this);
            btnStart.setEnabled(true);
            btnStop.setEnabled(false);

            alarmManager = (AlarmManager) getSystemService(ALARM_SERVICE);

            int status = GooglePlayServicesUtil
                    .isGooglePlayServicesAvailable(getBaseContext());
            if (status != ConnectionResult.SUCCESS) { // Google Play Services
                                                        // are
                                                        // not available

                int requestCode = 10;
                Dialog dialog = GooglePlayServicesUtil.getErrorDialog(status,
                        this, requestCode);
                dialog.show();

            } else { // Google Play Services are available

                // Getting reference to the SupportMapFragment of
                // activity_main.xml
                // SupportMapFragment supportMapFragment = (MapFragment)
                // getFragmentManager().findFragmentById(R.id.map);

                // Getting GoogleMap object from the fragment
                googleMap = ((MapFragment) getFragmentManager().findFragmentById(R.id.map)).getMap();


                // can use for overlay on the map
                List<Double> latList = new ArrayList<Double>();
                latList.add(145.7309593);
                latList.add(146.34);
                latList.add(147.34);

                List<Double> lonList = new ArrayList<Double>();
                lonList.add(-122.6365384);
                lonList.add(-123.6365384);
                lonList.add(-124.6365384);

                for (int i = 0; i < 3; i++) {
                    // LatLng latLng = new LatLng(45.7309593, -122.6365384);
                    LatLng latLng = new LatLng(latList.get(i).doubleValue(),
                            lonList.get(i).doubleValue());
                    googleMap.setMapType(GoogleMap.MAP_TYPE_NORMAL);
                    googleMap
                            .addMarker(new MarkerOptions()
                                    .position(latLng)
                                    .title("My Spot")
                                    .snippet("This is my spot!")
                                    .icon(BitmapDescriptorFactory
                                            .defaultMarker(BitmapDescriptorFactory.HUE_AZURE)));
                }

                // Enabling MyLocation Layer of Google Map
                googleMap.setMyLocationEnabled(true);

                LocationManager locationManager = (LocationManager) getSystemService(LOCATION_SERVICE);

                Criteria criteria = new Criteria();
                provider = locationManager.getBestProvider(criteria, true);
                Location location = locationManager
                        .getLastKnownLocation(provider);

                if (location != null) {
                    onLocationChanged(location);
                }

                locationManager
                        .requestLocationUpdates(provider, 20000, 0, this);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void onSensorChanged(SensorEvent event) {

        if (started) {

            double x = event.values[0];
            double y = event.values[1];
            double z = event.values[2];

            long timestamp = System.currentTimeMillis();

            LocationManager locManager = (LocationManager) getSystemService(Context.LOCATION_SERVICE);
            Criteria criteria = new Criteria();
            criteria.setPowerRequirement(Criteria.POWER_MEDIUM);
            criteria.setAccuracy(Criteria.ACCURACY_FINE);

            provider = locManager.getBestProvider(criteria, true);
            Location location = locManager.getLastKnownLocation(provider);

            double latitude = 0;
            double longitude = 0;
            if (location != null) {
                latitude = location.getLatitude();
                longitude = location.getLongitude();
            }
            AccelLocData accelLocData = new AccelLocData(timestamp, x, y, z,
                    latitude, longitude);

            // Log.d("X data","data x:" + data.getX());

            try {
                // writer.write(data.toString());
                 if (databaseHelper != null)
                 databaseHelper.insertLocData(accelLocData);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
    }

    @Override
    public void onLocationChanged(Location location) {

        TextView tvLocation = (TextView) findViewById(R.id.tv_location);

        // Getting latitude of the current location
        double latitude = location.getLatitude();

        // Getting longitude of the current location
        double longitude = location.getLongitude();

        // Creating a LatLng object for the current location
        LatLng latLng = new LatLng(latitude, longitude);

        // Showing the current location in Google Map
        googleMap.moveCamera(CameraUpdateFactory.newLatLng(latLng));

        // Zoom in the Google Map
        googleMap.animateCamera(CameraUpdateFactory.zoomTo(15));






    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
        case R.id.btnStart:

            Context context = getApplicationContext();
            alarmIntent = new Intent(context, AccelLocSender.class);

            AlarmManager alarmManager = (AlarmManager) context
                    .getSystemService(Context.ALARM_SERVICE);
            pendingIntentSender = PendingIntent.getBroadcast(context, 0,
                    alarmIntent, 0);

            alarmManager.setRepeating(AlarmManager.RTC_WAKEUP,
                    System.currentTimeMillis(), 60000, pendingIntentSender);

            alarmIntent2 = new Intent(context, AccelLocReceiver.class);
            pendingIntentReceiver = PendingIntent.getBroadcast(context, 0,
                    alarmIntent2, 0);
            alarmManager.setRepeating(AlarmManager.RTC_WAKEUP,
                    System.currentTimeMillis(), 30000, pendingIntentReceiver);

            btnStart.setEnabled(false);
            btnStop.setEnabled(true);
            Log.d("startbutton", "cam on click of start");
            started = true;

            // delete all files..
            // start thread to send data

            sensorManager.registerListener(this, mAccelerometer,
                    SensorManager.SENSOR_DELAY_UI);
            break;
        case R.id.btnStop:
            try {
                btnStart.setEnabled(true);
                btnStop.setEnabled(false);
                // btnUpload.setEnabled(true);
                started = false;

                sensorManager.unregisterListener(this);

                Context context1 = getApplicationContext();
                AlarmManager alarmManager1 = (AlarmManager) context1
                        .getSystemService(Context.ALARM_SERVICE);
                alarmManager1.cancel(pendingIntentSender);
                alarmManager1.cancel(pendingIntentReceiver);

            //  System.exit(0);

                } catch (Exception e) {
                e.printStackTrace();
            }
            break;
        default:
            break;
        }

    }

    protected void onPause() {
        super.onPause();

        /*
         * if (writer != null) { try { writer.close(); } catch (IOException e) {
         * // TODO Auto-generated catch block e.printStackTrace(); } }
         */
    }

    protected void onResume() {
        super.onResume();
        /*
         * try { Log.d("onresume","called onresume"); writer = new
         * FileWriter(sensorFile, true); } catch (IOException e) { // TODO
         * Auto-generated catch block e.printStackTrace(); }
         */
    }

    @Override
    public void onProviderDisabled(String arg0) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onProviderEnabled(String provider) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onStatusChanged(String provider, int status, Bundle extras) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onAccuracyChanged(Sensor arg0, int arg1) {
        // TODO Auto-generated method stub

    }

}

AccelLocSender.java

public class AccelLocSender extends BroadcastReceiver {

    private DatabaseHelper databaseHelper;
    private static int indexValue=1;

    @Override
    public void onReceive(Context context, Intent arg1) {
        // TODO Auto-generated method stub

        System.out.println("cmg in AccelLocsender");
        Log.i("Alarm sender", "Came in alarm sender");

        databaseHelper = new DatabaseHelper(context);

        sendDataToServer();

    }

    private void sendDataToServer() {

        try {


            int locDataCount = databaseHelper.getLocDataCount();

            List<AccelLocData> accelLocDataList = databaseHelper.getAllDataById(indexValue);
            Thread.sleep(3000);
            databaseHelper.deleteLocDataById(indexValue);
            Thread.sleep(3000);
            indexValue += 50;

            HttpClient httpClient = new DefaultHttpClient();
            HttpPost httpPost = new HttpPost("*****");

            List<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
            Log.d("datacount", Integer.toString(locDataCount));
            nameValuePairs.add(new BasicNameValuePair("datacount", Integer
                    .toString(locDataCount)));
            for (int i = 0; i < accelLocDataList.size(); i++) {

                nameValuePairs
                        .add(new BasicNameValuePair("latitude" + i,
                                Double.toString(accelLocDataList.get(i)
                                        .getLatitude())));
                nameValuePairs
                        .add(new BasicNameValuePair("longitude" + i, Double
                                .toString(accelLocDataList.get(i)
                                        .getLongitude())));
                nameValuePairs.add(new BasicNameValuePair("accelX" + i, Double
                        .toString(accelLocDataList.get(i).getX())));
                nameValuePairs.add(new BasicNameValuePair("accelY" + i, Double
                        .toString(accelLocDataList.get(i).getY())));
                nameValuePairs.add(new BasicNameValuePair("accelZ" + i, Double
                        .toString(accelLocDataList.get(i).getZ())));
                nameValuePairs
                        .add(new BasicNameValuePair("timeStamp" + i, Double
                                .toString(accelLocDataList.get(i)
                                        .getTimeStamp())));

            }

            httpPost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            httpClient.execute(httpPost);

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}

数据库助手.java

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "myapp.db";
    static String TABLE_NAME = "AccelLocation";

    private static final String KEY_ID = "id";
    private static final String LATITUDE_VAL = "latitude";
    private static final String LONGITUDE_VAL = "longitude";
    private static final String ACCEL_X = "accelX";
    private static final String ACCEL_Y = "accelY";
    private static final String ACCEL_Z = "accelZ";
    private static final String CUR_TIME = "dataTime";

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, 3);
    }

    SQLiteDatabase db;

    @Override
    public void onCreate(SQLiteDatabase db) {
        System.out.println("cmg in database helper");
        String CREATE_TABLE_SQL = "create table if not exists " + TABLE_NAME
                + " (" + KEY_ID + " integer primary key autoincrement,"
                + LATITUDE_VAL + " TEXT," + LONGITUDE_VAL + " TEXT," + ACCEL_X
                + " TEXT," + ACCEL_Y + " TEXT," + ACCEL_Z + " TEXT," + CUR_TIME
                + " TEXT" + ")";
        Log.d("createquery", CREATE_TABLE_SQL);
        System.out.println("Create query :" + CREATE_TABLE_SQL);
        // db.execSQL("DROP TABLE IF EXISTS"+TABLE_NAME);

        db.execSQL(CREATE_TABLE_SQL);
    //  db.close();
    }

    public void insertLocData(AccelLocData accelLocData) {

        if(db == null)
        {
            db = this.getWritableDatabase();
        }

        ContentValues values = new ContentValues();
        values.put(LATITUDE_VAL, accelLocData.getLatitude());
        values.put(LONGITUDE_VAL, accelLocData.getLongitude());
        values.put(ACCEL_X, accelLocData.getX());
        values.put(ACCEL_Y, accelLocData.getY());
        values.put(ACCEL_Z, accelLocData.getZ());
        values.put(CUR_TIME, accelLocData.getTimeStamp());

        // Inserting Row
        db.insert(TABLE_NAME, null, values);
    //  db.close(); // Closing database connection

    }

    public void dropTable(){
        if( db == null){
            db = this.getWritableDatabase();
        }

        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    }
    public List<AccelLocData> getAllData() {

        Cursor cursor = null;

        List<AccelLocData> accelLocDataList = new ArrayList<AccelLocData>();
        //SQLiteDatabase db = null;
        // Select All Query
        try {

            String selectQuery = "SELECT  * FROM " + TABLE_NAME;
            if(db == null){
                db = this.getWritableDatabase();
            }

            cursor = db.rawQuery(selectQuery, null);

            // looping through all rows and adding to list
            if (cursor.moveToFirst()) {
                do {
                    AccelLocData accelLocData = new AccelLocData(
                            Integer.parseInt(cursor.getString(0)),
                            Long.parseLong(cursor.getString(6)),
                            Double.parseDouble(cursor.getString(3)),
                            Double.parseDouble(cursor.getString(4)),
                            Double.parseDouble(cursor.getString(5)),
                            Double.parseDouble(cursor.getString(1)),
                            Double.parseDouble(cursor.getString(2)));
                    accelLocDataList.add(accelLocData);
                } while (cursor.moveToNext());
            }

        } catch (Exception e) {
            Log.e("DatabasegetallData", e.toString());
        } finally {
            if(cursor!= null)
            cursor.close();

    //      if(db!=null)
    //      db.close();


        }

        // return accelLocData list
        return accelLocDataList;

    }

    public List<AccelLocData> getAllDataById(int indexValue) {

        Cursor cursor = null;

        List<AccelLocData> accelLocDataList = new ArrayList<AccelLocData>();
    //  SQLiteDatabase db = null;
        // Select All Query
        try {
            String selectQuery = "SELECT  * FROM " + TABLE_NAME + " where "+ KEY_ID + " < "+ indexValue;

            if(db == null){
                db = this.getWritableDatabase();
            }
            cursor = db.rawQuery(selectQuery, null);

            // looping through all rows and adding to list
            if (cursor.moveToFirst()) {
                do {
                    AccelLocData accelLocData = new AccelLocData(
                            Integer.parseInt(cursor.getString(0)),
                            Long.parseLong(cursor.getString(6)),
                            Double.parseDouble(cursor.getString(3)),
                            Double.parseDouble(cursor.getString(4)),
                            Double.parseDouble(cursor.getString(5)),
                            Double.parseDouble(cursor.getString(1)),
                            Double.parseDouble(cursor.getString(2)));
                    accelLocDataList.add(accelLocData);
                } while (cursor.moveToNext());
            }

        } catch (Exception e) {
            Log.e("DatabasegetallData", e.toString());
        } finally {
            if(cursor!= null)
            cursor.close();

        //  if(db!=null)
        //  db.close();


        }

        // return accelLocData list
        return accelLocDataList;

    }

    // Deleting single accelLocData
    public void deleteLocData(AccelLocData accelLocData) {
        if( db == null){
            db = this.getWritableDatabase();
        }

        db.delete(TABLE_NAME, KEY_ID + " = ?",
                new String[] { String.valueOf(accelLocData.getId()) });
    //  db.close();
    }

    public void deleteLocDataById(int indexValue) {
        if(db == null){
            db = this.getWritableDatabase();
        }
        db.delete(TABLE_NAME, KEY_ID + " < ?",
                new String[] { String.valueOf(indexValue) });

    //  if(db != null)
    //  db.close();
    }

    // Getting contacts Count
    public int getLocDataCount() {
        String countQuery = "SELECT  * FROM " + TABLE_NAME;
        if (db == null){
            db = this.getReadableDatabase();
        }
        Cursor cursor = db.rawQuery(countQuery, null);
        int noOfRows = cursor.getCount();
        cursor.close();
    //  db.close();
        return noOfRows;
    }

    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
        // TODO Auto-generated method stub

    }

    public void removeAll() {

        if(db == null)
        db = this.getWritableDatabase(); // helper is object
                                                        // extends
                                                        // SQLiteOpenHelper
        db.delete(DatabaseHelper.TABLE_NAME, null, null);
    //  db.close();

    }

}

Answer 1

尝试在您的数据库助手中声明一个可重入锁，并在您访问数据库的任何地方锁定和解锁。这应该允许线程等待数据库可用性并避免崩溃/错误。例如在数据库助手中:

    public static Lock l = new ReentrantLock(false);

访问数据库的任何地方:

    l.lock();
    try
    {
           //do database editing
    }
    finally
    {
          l.unlock();
    }

但是，如前所述，如果您尝试每秒写入数据库的次数超过数据库编辑实际完成的次数，您最终将积压很长时间的数据库写入。我的建议是每次触发数据库写入时都使用时间戳比较，并跳过写入数据库，除非自上次将该信息写入数据库以来已经过了半秒。