我在SQLite数据库上一直遇到问题已有一段时间了。经常会出问题，而我会收到此错误：
java.lang.RuntimeException: An error occured while executing doInBackground()
at android.os.AsyncTask$3.done(AsyncTask.java:299)
at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:352)
at java.util.concurrent.FutureTask.setException(FutureTask.java:219)
at java.util.concurrent.FutureTask.run(FutureTask.java:239)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:230)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
at java.lang.Thread.run(Thread.java:856)
Caused by: android.database.sqlite.SQLiteException: no such table: photo (code 1): , while compiling: SELECT * FROM photo
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1113)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:686)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1420)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1359)
at com.example.sition.diggintemp.db.DigginSQLiteHelper.getAllPhotos(DigginSQLiteHelper.java:1102)
at com.example.sition.diggintemp.LoadActivity$LoadAllTask.doInBackground(LoadActivity.java:249)
at com.example.sition.diggintemp.LoadActivity$LoadAllTask.doInBackground(LoadActivity.java:243)
at android.os.AsyncTask$2.call(AsyncTask.java:287)
at java.util.concurrent.FutureTask.run(FutureTask.java:234)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:230)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
at java.lang.Thread.run(Thread.java:856)
发生这种情况时，我需要清除整个数据库，将版本号设置为更高，并从服务器和设备中删除所有图片（因为它们与数据库中的信息有所关联）。
以下是我的LoadActivity中的AsyncTask（出现此错误），在此Activity中，我从服务器加载数据库并将SQLiteDB中的旧数据替换为在线DB中的数据。
private class LoadAllTask extends AsyncTask<JSONArray,Void,Void> {
@Override
protected Void doInBackground(JSONArray... params) {
JSONArray jsonProjects = params[0];
JSONArray jsonPhotos = params[1];
JSONArray jsonVideos = params[2];
ArrayList<Photo> oldPhotos = db.getAllPhotos();
ArrayList<Video> oldVideos = db.getAllVideos();
db.clearDB();
try {
for (int i = 0; i < jsonProjects.length(); i++) {
JSONObject jsonProject = (JSONObject) jsonProjects.get(i);
Project project = new Project(jsonProject.getInt("id"), jsonProject.getString("title"), jsonProject.getInt("user_id"), jsonProject.getString("description"));
db.addProject(project);
JSONArray jsonWells = jsonProject.getJSONArray("wells");
for (int i2 = 0; i2 < jsonWells.length(); i2++) {
JSONObject jsonWell = (JSONObject) jsonWells.get(i2);
Well well = new Well(jsonWell.getInt("id"), jsonWell.getString("number"), jsonWell.getInt("project_id"), jsonWell.getString("description"));
db.addWell(well);
}
JSONArray jsonTracks = jsonProject.getJSONArray("tracks");
for (int i2 = 0; i2 < jsonTracks.length(); i2++) {
JSONObject jsonTrack = (JSONObject) jsonTracks.get(i2);
Track track = new Track(jsonTrack.getInt("id"), jsonTrack.getString("number"), jsonTrack.getString("description"));
boolean inDb = false;
for (Track t : db.getAllTracks()) {
if (t.toString().equals(track.toString())) {
inDb = true;
}
}
if (!inDb) {
db.addTrack(track);
}
Track_Well track_well = new Track_Well(db.getAllTrackWells().size() + 1, jsonTrack.getInt("id"), jsonTrack.getInt("well_id"));
db.addTrackWell(track_well);
}
JSONArray jsonSurfaces = jsonProject.getJSONArray("surfaces");
for (int i2 = 0; i2 < jsonSurfaces.length(); i2++) {
JSONObject jsonSurface = (JSONObject) jsonSurfaces.get(i2);
Surface surface = new Surface(jsonSurface.getInt("id"), jsonSurface.getString("number"), jsonSurface.getInt("well_id"), jsonSurface.getString("description"));
boolean inDb = false;
for (Surface s : db.getAllSurfaces()) {
if (s.toString().equals(surface.toString())) {
inDb = true;
}
}
if (!inDb) {
db.addSurface(surface);
}
Track_Surface track_surface = new Track_Surface(db.getAllTrackSurfaces().size() + 1, (!jsonSurface.isNull("track_id") ? jsonSurface.getInt("track_id") : 0), jsonSurface.getInt("id"));
db.addTrackSurface(track_surface);
}
JSONArray jsonProfiles = jsonProject.getJSONArray("profiles");
for (int i2 = 0; i2 < jsonProfiles.length(); i2++) {
JSONObject jsonProfile = (JSONObject) jsonProfiles.get(i2);
Profile profile = new Profile(jsonProfile.getInt("id"), jsonProfile.getString("number"), jsonProfile.getInt("well_id"), jsonProfile.getString("description"));
boolean inDb = false;
for (Profile p : db.getAllProfiles()) {
if (p.toString().equals(profile.toString())) {
inDb = true;
}
}
if (!inDb) {
db.addProfile(profile);
}
Track_Profile track_profile = new Track_Profile(db.getAllTrackProfiles().size() + 1, (!jsonProfile.isNull("track_id") ? jsonProfile.getInt("track_id") : 0), jsonProfile.getInt("id"));
db.addTrackProfile(track_profile);
}
JSONArray jsonFieldfinds = jsonProject.getJSONArray("fieldfinds");
for (int i2 = 0; i2 < jsonFieldfinds.length(); i2++) {
JSONObject jsonFieldfind = (JSONObject) jsonFieldfinds.get(i2);
int id = jsonFieldfind.getInt("id");
String number = jsonFieldfind.getString("number");
int project_id = (!jsonFieldfind.isNull("project_id") ? jsonFieldfind.getInt("project_id") : 0);
int well_id = (!jsonFieldfind.isNull("well_id") ? jsonFieldfind.getInt("well_id") : 0);
int track_id = (!jsonFieldfind.isNull("track_id") ? jsonFieldfind.getInt("track_id") : 0);
int surface_id = (!jsonFieldfind.isNull("surface_id") ? jsonFieldfind.getInt("surface_id") : 0);
int profile_id = (!jsonFieldfind.isNull("profile_id") ? jsonFieldfind.getInt("profile_id") : 0);
int user_id = jsonFieldfind.getInt("user_id");
String description = (jsonFieldfind.getString("description"));
Fieldfind fieldfind = new Fieldfind(id,number,project_id,well_id,track_id,surface_id,profile_id,user_id,description);
db.addFieldfind(fieldfind);
}
}
for (int i3 = 0; i3 < jsonPhotos.length(); i3++) {
JSONObject jsonPhoto = (JSONObject) jsonPhotos.get(i3);
File mainDir = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_PICTURES) + File.separator + "Diggin");
boolean isPic = jsonPhoto.getString("image_path").contains("picture");
String pathStart = mainDir.getPath() + "/";
Photo photo = new Photo(jsonPhoto.getInt("id"), jsonPhoto.getInt("user_id"), jsonPhoto.getString("imageable_type"), jsonPhoto.getInt("imageable_id"), pathStart + jsonPhoto.getString("image_path"), jsonPhoto.getString("description"), jsonPhoto.getString("metadata"), jsonPhoto.getString("wind"));
photo.setImage_path(photo.getThumbnailFromImage_path());
String filename = photo.getDBImage_path();
File thumbDir = new File(mainDir, "thumbnails");
File thumbnailFile = new File(thumbDir, filename);
photo.setImage_path(thumbnailFile.toString());
if (!thumbnailFile.exists()) {
db.addPhoto(photo);
String image_str = jsonPhoto.getString("image");
byte[] byte_arr = Base64.decode(image_str, 0);
Bitmap bitmap = BitmapFactory.decodeByteArray(byte_arr, 0, byte_arr.length);
try {
createPicture(bitmap, photo);
} catch (IOException e) {
e.printStackTrace();
}
} else {
photo.setImage_path(photo.getImageFromThumbnail_path());
if(isPic) {
photo.setImage_path(thumbnailFile.toString());
}
db.addPhoto(photo);
}
}
for (int i4 = 0; i4 < jsonVideos.length(); i4++) {
JSONObject jsonVideo = (JSONObject) jsonVideos.get(i4);
File mainDir = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_PICTURES) + File.separator + "Diggin");
boolean isVid = jsonVideo.getString("image_path").contains("video");
String pathStart = mainDir.getPath() + "/";
Video video = new Video(jsonVideo.getInt("id"), jsonVideo.getInt("user_id"), jsonVideo.getString("imageable_type"), jsonVideo.getInt("imageable_id"), pathStart + jsonVideo.getString("image_path"), jsonVideo.getString("description"), jsonVideo.getString("metadata"));
video.setImage_path(video.getThumbnailFromImage_path());
String filename = video.getDBImage_path();
File thumbDir = new File(mainDir, "thumbnails");
File thumbnailFile = new File(thumbDir, filename);
video.setImage_path(thumbnailFile.toString());
if (!thumbnailFile.exists()) {
db.addVideo(video);
String image_str = jsonVideo.getString("image");
byte[] byte_arr = Base64.decode(image_str, 0);
Bitmap bitmap = BitmapFactory.decodeByteArray(byte_arr, 0, byte_arr.length);
try {
createPicture(bitmap, video);
} catch (IOException e) {
e.printStackTrace();
}
} else {
video.setImage_path(video.getImageFromThumbnail_path());
if(isVid) {
video.setImage_path(thumbnailFile.toString());
}
db.addVideo(video);
}
}
for (Photo photo : oldPhotos) {
boolean stillExists = false;
for (Photo p : db.getAllPhotos()) {
if (p.getThumbnailFromImage_path().equals(photo.getThumbnailFromImage_path())) {
stillExists = true;
}
}
if (!stillExists) {
//Delete thumbnail picture
String deleteCmd = "rm -r " + photo.getThumbnailFromImage_path();
Runtime runtime = Runtime.getRuntime();
try {
runtime.exec(deleteCmd);
} catch (IOException e) {
e.printStackTrace();
}
}
}
for (Video video : oldVideos) {
boolean stillExists = false;
for (Video v : db.getAllVideos()) {
if (v.getThumbnailFromImage_path().equals(video.getThumbnailFromImage_path())) {
stillExists = true;
}
}
if (!stillExists) {
//Delete thumbnail picture
String deleteCmd = "rm -r " + video.getThumbnailFromImage_path();
Runtime runtime = Runtime.getRuntime();
try {
runtime.exec(deleteCmd);
} catch (IOException e) {
e.printStackTrace();
}
}
}
} catch (JSONException e) {
e.printStackTrace();
}
return null;
}
@Override
protected void onPostExecute(Void aVoid) {
super.onPostExecute(aVoid);
Intent intent = new Intent(LoadActivity.this,MainActivity.class);
startActivity(intent);
}
}
有人知道是什么原因造成的，因为我不知道。
编辑：
以下是DigginSQLiteHelper的一些代码：
public class DigginSQLiteHelper extends SQLiteOpenHelper{
//region Database Version and Name
private static final int DATABASE_VERSION = 9;
private static final String DATABASE_NAME = "DigginTempDB";
//endregion Database Version and Name
//region Table names
private static final String TABLE_PROJECT = "project";
private static final String TABLE_WELL = "well";
private static final String TABLE_TRACK = "track";
private static final String TABLE_SURFACE = "surface";
private static final String TABLE_PROFILE = "profile";
private static final String TABLE_FIELDFIND = "fieldfind";
private static final String TABLE_TRACK_WELL = "track_well";
private static final String TABLE_TRACK_SURFACE = "track_surface";
private static final String TABLE_TRACK_PROFILE = "track_profile";
private static final String TABLE_PHOTO = "photo";
private static final String TABLE_VIDEO = "video";
//endregion Table names
//region Table Column names
private static final String KEY_ID = "id";
private static final String KEY_TITLE = "title";
private static final String KEY_USER_ID = "user_id";
private static final String KEY_NUMBER = "number";
private static final String KEY_PROJECT_ID = "project_id";
private static final String KEY_WELL_ID = "well_id";
private static final String KEY_TRACK_ID = "track_id";
private static final String KEY_SURFACE_ID = "surface_id";
private static final String KEY_PROFILE_ID = "profile_id";
private static final String KEY_IMAGEABLE_TYPE = "imageable_type";
private static final String KEY_IMAGEABLE_ID = "imageable_id";
private static final String KEY_IMAGE_PATH = "image_path";
private static final String KEY_DESCRIPTION = "description";
private static final String KEY_METADATA = "metadata";
private static final String KEY_WIND = "wind";
//endregion
//region Table Columns
private static final String[] COLUMNS_PROJECT = {KEY_ID,KEY_TITLE,KEY_USER_ID,KEY_DESCRIPTION};
private static final String[] COLUMNS_WELL = {KEY_ID, KEY_NUMBER, KEY_PROJECT_ID,KEY_DESCRIPTION};
private static final String[] COLUMNS_TRACK = {KEY_ID, KEY_NUMBER,KEY_DESCRIPTION};
private static final String[] COLUMNS_SURFACE = {KEY_ID, KEY_NUMBER, KEY_WELL_ID,KEY_DESCRIPTION};
private static final String[] COLUMNS_PROFILE = {KEY_ID, KEY_NUMBER, KEY_WELL_ID,KEY_DESCRIPTION};
private static final String[] COLUMNS_FIELDFIND = {KEY_ID, KEY_NUMBER, KEY_PROJECT_ID, KEY_WELL_ID, KEY_TRACK_ID, KEY_SURFACE_ID, KEY_PROFILE_ID, KEY_USER_ID,KEY_DESCRIPTION};
private static final String[] COLUMNS_TRACK_WELL = {KEY_ID, KEY_TRACK_ID, KEY_WELL_ID};
private static final String[] COLUMNS_TRACK_SURFACE = {KEY_ID, KEY_TRACK_ID, KEY_SURFACE_ID};
private static final String[] COLUMNS_TRACK_PROFILE = {KEY_ID, KEY_TRACK_ID, KEY_PROFILE_ID};
private static final String[] COLUMNS_PHOTO = {KEY_ID,KEY_USER_ID,KEY_IMAGEABLE_TYPE,KEY_IMAGEABLE_ID,KEY_IMAGE_PATH,KEY_DESCRIPTION,KEY_METADATA,KEY_WIND};
private static final String[] COLUMNS_VIDEO = {KEY_ID,KEY_USER_ID,KEY_IMAGEABLE_TYPE,KEY_IMAGEABLE_ID,KEY_IMAGE_PATH,KEY_DESCRIPTION,KEY_METADATA};
//endregion
public DigginSQLiteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// SQL statements to create tables
String CREATE_PROJECT_TABLE =
"CREATE TABLE project ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"title TEXT, "+
"user_id INTEGER, " +
"description TEXT);";
String CREATE_WELL_TABLE =
"CREATE TABLE well ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"number TEXT, " +
"project_id INTEGER, " +
"description TEXT, " +
" FOREIGN KEY ("+KEY_PROJECT_ID+") REFERENCES "+TABLE_PROJECT+"("+KEY_ID+"));";
String CREATE_TRACK_TABLE =
"CREATE TABLE track ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"number TEXT, " +
"description TEXT);";
String CREATE_SURFACE_TABLE =
"CREATE TABLE surface ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"number TEXT, " +
"well_id INTEGER, " +
"description TEXT, " +
" FOREIGN KEY ("+KEY_WELL_ID+") REFERENCES "+TABLE_WELL+"("+KEY_ID+"));";
String CREATE_PROFILE_TABLE =
"CREATE TABLE profile ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"number TEXT, " +
"well_id INTEGER, " +
"description TEXT, " +
" FOREIGN KEY ("+KEY_WELL_ID+") REFERENCES "+TABLE_WELL+"("+KEY_ID+"));";
String CREATE_FIELDFIND_TABLE =
"CREATE TABLE fieldfind ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"number TEXT, " +
"project_id INTEGER, " +
"well_id INTEGER, " +
"track_id INTEGER, " +
"surface_id INTEGER, " +
"profile_id INTEGER, " +
"user_id INTEGER, " +
"description TEXT, " +
" FOREIGN KEY ("+KEY_PROJECT_ID+") REFERENCES "+TABLE_PROJECT+"("+KEY_ID+")," +
" FOREIGN KEY ("+KEY_WELL_ID+") REFERENCES "+TABLE_WELL+"("+KEY_ID+")," +
" FOREIGN KEY ("+KEY_TRACK_ID+") REFERENCES "+TABLE_TRACK+"("+KEY_ID+")," +
" FOREIGN KEY ("+KEY_SURFACE_ID+") REFERENCES "+TABLE_SURFACE+"("+KEY_ID+")," +
" FOREIGN KEY ("+KEY_PROFILE_ID+") REFERENCES "+TABLE_PROFILE+"("+KEY_ID+"));";
String CREATE_TRACK_WELL_TABLE =
"CREATE TABLE track_well ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"track_id INTEGER, " +
"well_id INTEGER, " +
" FOREIGN KEY ("+KEY_TRACK_ID+") REFERENCES "+TABLE_TRACK+"("+KEY_ID+")" +
" FOREIGN KEY ("+KEY_WELL_ID+") REFERENCES "+TABLE_WELL+"("+KEY_ID+"));";
String CREATE_TRACK_SURFACE_TABLE =
"CREATE TABLE track_surface ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"track_id INTEGER, " +
"surface_id INTEGER, " +
" FOREIGN KEY ("+KEY_TRACK_ID+") REFERENCES "+TABLE_TRACK+"("+KEY_ID+")" +
" FOREIGN KEY ("+KEY_SURFACE_ID+") REFERENCES "+TABLE_SURFACE+"("+KEY_ID+"));";
String CREATE_TRACK_PROFILE_TABLE =
"CREATE TABLE track_profile ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"track_id INTEGER, " +
"profile_id INTEGER, " +
" FOREIGN KEY ("+KEY_TRACK_ID+") REFERENCES "+TABLE_TRACK+"("+KEY_ID+")" +
" FOREIGN KEY ("+KEY_PROFILE_ID+") REFERENCES "+TABLE_PROFILE+"("+KEY_ID+"));";
String CREATE_PHOTO_TABLE =
"CREATE TABLE photo( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"user_id INTEGER, "+
"imageable_type TEXT, "+
"imageable_id INTEGER, "+
"image_path TEXT, "+
"description TEXT, "+
"metadata TEXT, "+
"wind TEXT );";
String CREATE_VIDEO_TABLE =
"CREATE TABLE video( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"user_id INTEGER, "+
"imageable_type TEXT, "+
"imageable_id INTEGER, "+
"image_path TEXT, "+
"description TEXT, "+
"metadata TEXT );";
// Create tables
db.execSQL(CREATE_PROJECT_TABLE);
db.execSQL(CREATE_WELL_TABLE);
db.execSQL(CREATE_TRACK_TABLE);
db.execSQL(CREATE_SURFACE_TABLE);
db.execSQL(CREATE_PROFILE_TABLE);
db.execSQL(CREATE_FIELDFIND_TABLE);
db.execSQL(CREATE_TRACK_WELL_TABLE);
db.execSQL(CREATE_TRACK_SURFACE_TABLE);
db.execSQL(CREATE_TRACK_PROFILE_TABLE);
db.execSQL(CREATE_PHOTO_TABLE);
db.execSQL(CREATE_VIDEO_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (newVersion > oldVersion) {
// Drop older tables if existed
db.execSQL("DROP TABLE IF EXISTS video");
db.execSQL("DROP TABLE IF EXISTS photo");
db.execSQL("DROP TABLE IF EXISTS fieldfind");
db.execSQL("DROP TABLE IF EXISTS track_surface");
db.execSQL("DROP TABLE IF EXISTS track_profile");
db.execSQL("DROP TABLE IF EXISTS track_well");
db.execSQL("DROP TABLE IF EXISTS profile");
db.execSQL("DROP TABLE IF EXISTS surface");
db.execSQL("DROP TABLE IF EXISTS track");
db.execSQL("DROP TABLE IF EXISTS well");
db.execSQL("DROP TABLE IF EXISTS project");
// Create fresh tables
this.onCreate(db);
}
}
public static int getDatabaseVersion() {
return DATABASE_VERSION;
}
/**
* Method that clears the SQLite DB
*/
public void clearDB() {
SQLiteDatabase db = getWritableDatabase();
// Drop older tables if existed
db.execSQL("DROP TABLE IF EXISTS video");
db.execSQL("DROP TABLE IF EXISTS photo");
db.execSQL("DROP TABLE IF EXISTS fieldfind");
db.execSQL("DROP TABLE IF EXISTS track_surface");
db.execSQL("DROP TABLE IF EXISTS track_profile");
db.execSQL("DROP TABLE IF EXISTS track_well");
db.execSQL("DROP TABLE IF EXISTS profile");
db.execSQL("DROP TABLE IF EXISTS surface");
db.execSQL("DROP TABLE IF EXISTS track");
db.execSQL("DROP TABLE IF EXISTS well");
db.execSQL("DROP TABLE IF EXISTS project");
// Create fresh tables
this.onCreate(db);
}
public ArrayList<Photo> getAllPhotos() {
ArrayList<Photo> photos = new ArrayList<>();
// 1. build the query
String query = "SELECT * FROM " + TABLE_PHOTO;
// 2. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
// 3. go over each row, build project and add it to list
Photo photo;
if (cursor.moveToFirst()) {
do {
photo = new Photo(cursor.getInt(0),cursor.getInt(1),cursor.getString(2),cursor.getInt(3),cursor.getString(4),cursor.getString(5),cursor.getString(6),cursor.getString(7));
// Add photo to photos
photos.add(photo);
} while (cursor.moveToNext());
}
cursor.close();
// return photos
return photos;
}
}
我将我的评论移至此处，因为这是一个实际的解决方案
似乎您的clearDB方法删除了所有表格-为什么...？我不太确定数据库引擎在这种情况下的工作方式和行为。无论如何，我建议您尝试使用DELETE FROM表而不是删除并创建它们。拥有一个名为“ clear”的方法并执行类似操作是没有意义的。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句