保存值的 Android SQLite 问题

Question

我有一些安卓代码的问题。我在 SQLite 数据库中写入值，但在从数据库中读取选定的行后，我得到了另一个值。这是我的数据库类代码:

    package com.example.maszspotkanie;
    import android.content.ContentValues;
    import android.content.Context;
    import android.content.CursorLoader;
    import android.database.Cursor;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteDatabase.CursorFactory;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.util.Log;

    public class MSDatabase {
    private static final String DEBUG_TAG = "SqLiteTodoManager";
    private static final int DB_VERSION = 1;
    private static final String DB_NAME = "database.db";
    private static final String DB_MS_TABLE = "spotkania";
    public static final String KEY_ID = "_id";
    public static final String ID_OPTIONS = "INTEGER PRIMARY KEY AUTOINCREMENT";
    public static final int ID_COLUMN = 0;
    public static final String KEY_NAME = "name";
    public static final String NAME_OPTIONS = "TEXT NOT NULL";
    public static final int NAME_COLUMN = 1;
    public static final String KEY_COMPLETED = "completed";
    public static final String COMPLETED_OPTIONS = "INTEGER DEFAULT 0";
    public static final int COMPLETED_COLUMN = 2;
    public static final String KEY_DATE = "date";
    public static final String DATE_OPTIONS = "TEXT NOT NULL";
    public static final int DATE_COLUMN = 3;
    public static final String KEY_PICTURE = "picture";
    public static final String PICTURE_OPTIONS = "INTEGER";
    public static final int PICTURE_COLUMN = 4;
    public static final String KEY_HOUR = "hour";
    public static final String HOUR_OPTIONS = "TEXT NOT NULL";
    public static final int HOUR_COLUMN = 5;
    public static final String KEY_LAT = "lat";
    public static final String LAT_OPTIONS = "FLOAT NOT NULL";
    public static final int LAT_COLUMN = 6;
    public static final String KEY_LNG = "lng";
    public static final String LNG_OPTIONS = "FLOAT NOT NULL";
    public static final int LNG_COLUMN = 7;
    public static final String KEY_LOCALIZATION = "localization";
    public static final String LOCALIZATION_OPTIONS = "TEXT";
    public static final int LOCALIZATION_COLUMN = 8;
    public static final String KEY_DESCRIPTION = "description";
    public static final String DESCRIPTION_OPTIONS = "TEXT";
    public static final int DESCRIPTION_COLUMN = 9;
    private static final String DB_CREATE_MSDatabase_TABLE = "CREATE TABLE "
            + DB_MS_TABLE + "( " + KEY_ID + " " + ID_OPTIONS + ", " + KEY_NAME
            + " " + NAME_OPTIONS + ", " + KEY_DESCRIPTION + " "
            + DESCRIPTION_OPTIONS + ", " + KEY_COMPLETED + " "
            + COMPLETED_OPTIONS + ", " + KEY_DATE + " " + DATE_OPTIONS + ", "
            + KEY_PICTURE + " " + PICTURE_OPTIONS + ", " + KEY_HOUR + " "
            + HOUR_OPTIONS + ", " + KEY_LAT + " " + LAT_OPTIONS + ", "
            + KEY_LNG + " " + LNG_OPTIONS + ", " + KEY_LOCALIZATION + " "
            + LOCALIZATION_OPTIONS + ");";
    private static final String DROP_MSDatabase_TABLE = "DROP TABLE IF EXISTS "
            + DB_MS_TABLE;
    private SQLiteDatabase db;
    private Context context;
    private DatabaseHelper dbHelper;

    private static class DatabaseHelper extends SQLiteOpenHelper {
        public DatabaseHelper(Context context, String name,
                CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DB_CREATE_MSDatabase_TABLE);

            Log.d(DEBUG_TAG, "Database creating...");
            Log.d(DEBUG_TAG, "Table " + DB_MS_TABLE + " ver." + DB_VERSION
                    + " created");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL(DROP_MSDatabase_TABLE);

            Log.d(DEBUG_TAG, "Database updating...");
            Log.d(DEBUG_TAG, "Table " + DB_MS_TABLE + " updated from ver."
                    + oldVersion + " to ver." + newVersion);
            Log.d(DEBUG_TAG, "All data is lost.");

            onCreate(db);
        }
    }

    public MSDatabase(Context context) {
        this.context = context;
    }

    public MSDatabase open() {
        dbHelper = new DatabaseHelper(context, DB_NAME, null, DB_VERSION);
        try {
            db = dbHelper.getWritableDatabase();
        } catch (SQLException e) {
            db = dbHelper.getReadableDatabase();
        }
        return this;
    }

    public long insertMS(String description, String date, int picture,
            String hour, Float lat, Float lng, String localization, String name) {
        ContentValues newValues = new ContentValues();
        newValues.put(KEY_NAME, name);
        newValues.put(KEY_DESCRIPTION, description);
        newValues.put(KEY_DATE, date);
        newValues.put(KEY_PICTURE, picture);
        newValues.put(KEY_HOUR, hour);
        newValues.put(KEY_LAT, lat);
        newValues.put(KEY_LNG, lng);
        newValues.put(KEY_LOCALIZATION, localization);
        System.out.println("Database:  nazwa " + name + " termin " + date
                + " h " + hour + " opis " + description + " zdjecie " + picture
                + " lat " + lat + " lng " + lng + " lokalizacja "
                + localization);

        return db.insert(DB_MS_TABLE, null, newValues);
    }

    public boolean deleteMS(long id) {
        String where = KEY_ID + "=" + id;
        return db.delete(DB_MS_TABLE, where, null) > 0;
    }

    public Cursor getAllMS() {
        String[] columns = { KEY_ID, KEY_NAME, KEY_DESCRIPTION, KEY_COMPLETED,
                KEY_DATE, KEY_PICTURE, KEY_HOUR, KEY_LAT, KEY_LNG,
                KEY_LOCALIZATION };

        return db.query(DB_MS_TABLE, columns, null, null, null, null, null);
    }

    public MSDBTask getMS(int id) {
        String[] columns = { KEY_ID, KEY_NAME, KEY_DESCRIPTION, KEY_COMPLETED,
                KEY_DATE, KEY_PICTURE, KEY_HOUR, KEY_LAT, KEY_LNG,
                KEY_LOCALIZATION };
        String where = KEY_ID + "=" + id;
        Cursor cursor = db.query(DB_MS_TABLE, columns, where, null, null, null,
                null);
        MSDBTask task = null;
        if (cursor != null && cursor.moveToFirst()) {
            String name = cursor.getString(NAME_COLUMN);
            String description = cursor.getString(DESCRIPTION_COLUMN);
            String date = cursor.getString(DATE_COLUMN);
            int picture = cursor.getInt(PICTURE_COLUMN);
            String hour = cursor.getString(HOUR_COLUMN);
            float lat = cursor.getFloat(LAT_COLUMN);
            float lng = cursor.getFloat(LNG_COLUMN);
            String localization = cursor.getString(LOCALIZATION_COLUMN);
            boolean completed = cursor.getInt(COMPLETED_COLUMN) > 0 ? true
                    : false;
            task = new MSDBTask(id, name, description, completed, date,
                    picture, hour, lat, lng, localization);

        }
        return task;
    }

    public void close() {
        dbHelper.close();
    }

    public void dropDatabase() {
        db.execSQL(DROP_MSDatabase_TABLE);
        db.execSQL(DB_CREATE_MSDatabase_TABLE);
    }

    public boolean updateMS(MSDBTask task) {
        long id = task.getId();
        String description = task.getDescription();
        boolean completed = task.isCompleted();
        String date = task.getDate();
        int picture = task.getPicture();
        String hour = task.getHour();
        Float lat = task.getLat();
        Float lng = task.getLng();
        String localization = task.getLocalization();
        String name = task.getName();
        System.out.println("Database:  nazwa " + name + " termin " + date
                + " h " + hour + " opis " + description + " zdjecie " + picture
                + " lat " + lat + " lng " + lng + " lokalizacja "
                + localization);
        return updateMS(description, date, picture, hour, lat, lng,
                localization, name, completed, id);
    }

    public boolean updateMS(String description, String date, int picture,
            String hour, Float lat, Float lng, String localization,
            String name, boolean completed, long id) {

        String where = KEY_ID + "=" + id;
        int completedTask = completed ? 1 : 0;
        ContentValues updateMSValues = new ContentValues();
        updateMSValues.put(KEY_LOCALIZATION, localization);
        updateMSValues.put(KEY_LNG, lng);
        updateMSValues.put(KEY_LAT, lat);
        updateMSValues.put(KEY_HOUR, hour);
        updateMSValues.put(KEY_PICTURE, picture);
        updateMSValues.put(KEY_DATE, date);
        updateMSValues.put(KEY_COMPLETED, completedTask);
        updateMSValues.put(KEY_DESCRIPTION, description);
        updateMSValues.put(KEY_NAME, name);

        return db.update(DB_MS_TABLE, updateMSValues, where, null) > 0;
    }

}

还有我调用这个函数的类:

package com.example.maszspotkanie;

import java.util.ArrayList;
import java.util.List;

import android.view.View.OnClickListener;
import android.app.Activity;
import android.content.CursorLoader;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ListView;
import android.widget.Toast;

public class MainActivity extends Activity {
    Button noweSpotkanie;
    Button deleteAll;
     private MSDatabase MSDbAdapter;
        private Cursor MSCursor;
        private List<MSDBTask> tasks;
        private MSDBTaskAdapter listAdapter;
        private ListView lvSpotkania;
        private static final int NEWMS_ACTIVITY_REQUEST_CODE = 1;
        private int actualid;
        int idlast=0;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        noweSpotkanie=(Button) findViewById(R.id.button1);
        deleteAll=(Button) findViewById(R.id.drop);
        lvSpotkania=(ListView) findViewById(R.id.listView1);
        MSDbAdapter = new MSDatabase(getApplicationContext());
        MSDbAdapter.open();
        initListView();
        actualid=2147483647;
        noweSpotkanie.setOnClickListener(new OnClickListener(){

            @Override
            public void onClick(View v) {
                Intent intent = new Intent(getApplicationContext(),NewMSActivity.class);
                intent.putExtra("ID", actualid);
                startActivityForResult(intent, NEWMS_ACTIVITY_REQUEST_CODE);

            }


        });
        deleteAll.setOnClickListener(new OnClickListener(){

            @Override
            public void onClick(View v) {
                MSDbAdapter.dropDatabase();
                initListView();
            }


        });
    }
    @Override
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {


        if (NEWMS_ACTIVITY_REQUEST_CODE == resultCode) {
            String nazwa = data.getStringExtra("Nazwa");
            String termin = data.getStringExtra("Termin");
            String godzina = data.getStringExtra("Godzina");
            String opis = data.getStringExtra("Opis");
            String zdjecie = data.getStringExtra("Zdjecie");
            String lat = data.getStringExtra("Lat");
            String lng = data.getStringExtra("Lng");
            String lokalizacja = data.getStringExtra("Lokalizacja");
            String odpowiedz=data.getStringExtra("RESPONSE");

            System.out.println("Before save :  nazwa "+nazwa+ " termin " +termin+ " h "+ godzina 
                    + " opis " +opis + " zdjecie " + zdjecie+" lat " + lat
                    + " lng " +lng + " lokalizacja " +lokalizacja ); 
           saveNewTask(actualid,nazwa,termin,godzina,opis,zdjecie,lat,lng,lokalizacja,odpowiedz);
        }
    }
    private void saveNewTask(long id, String nazwa, String termin, String godzina, 
            String opis, String zdjecie, String lat, String lng, String lokalizacja,String odpowiedz){      

        int picture=Integer.parseInt(zdjecie);
        float LAT=Float.parseFloat(lat);
        float LNG=Float.parseFloat(lng);
        System.out.println("Save :   nazwa "+nazwa+ " termin " +termin+ " h "+ godzina 
                + " opis " +opis + " zdjecie " + picture+" lat " + LAT
                + " lng " +LNG + " lokalizacja " +lokalizacja+ "  odpowiedz "+odpowiedz );
        if(odpowiedz.equalsIgnoreCase("insertnew")){
            MSDbAdapter.insertMS(opis, termin,picture, godzina, LAT, LNG, lokalizacja, nazwa);

          idlast++;
          System.out.println("id="+idlast);
          initListView();
            }
        else{
            MSDBTask task=new MSDBTask(actualid,  nazwa,opis, false, termin,  picture, 
                     godzina,  LAT,  LNG, lokalizacja);
            MSDbAdapter.updateMS(task);
            }
        updateListViewData();
    }

    private void initListView() {

            fillListViewData();
            initListViewOnItemClick();


    }
     private void initListViewOnItemClick() {
            lvSpotkania.setOnItemClickListener(new OnItemClickListener() {
                @Override
                public void onItemClick(AdapterView<?> parent, View v, int position,
                        long id) {
                    MSDBTask task = tasks.get(position);
                    Intent intent = new Intent(getApplicationContext(),NewMSActivity.class);
                    actualid=task.getId();
                    intent.putExtra("ID", actualid);
                    int x=1;
                    startActivityForResult(intent, x);
                    updateListViewData();
                }
            });
        }
    private void fillListViewData() {
        getAllTasks();
        listAdapter = new MSDBTaskAdapter(this, tasks);     
        lvSpotkania.setAdapter(listAdapter);

    }
    private void getAllTasks() {
        tasks = new ArrayList<MSDBTask>();
        MSCursor = getAllEntriesFromDb();
        updateTaskList();

    }
    private void updateTaskList() {
        idlast=0;
        if(MSCursor != null && MSCursor.moveToFirst()) {
            do {
                int id = MSCursor.getInt(MSDbAdapter.ID_COLUMN);
                String description = MSCursor.getString(MSDbAdapter.DESCRIPTION_COLUMN);
                boolean completed = MSCursor.getInt(MSDbAdapter.COMPLETED_COLUMN) > 0 ? true : false;
                String name=MSCursor.getString(MSDbAdapter.NAME_COLUMN);
                String date=MSCursor.getString(MSDbAdapter.DATE_COLUMN);
                String hour=MSCursor.getString(MSDbAdapter.HOUR_COLUMN);
                int picture=MSCursor.getInt(MSDbAdapter.PICTURE_COLUMN);
                float lat=MSCursor.getFloat(MSDbAdapter.LAT_COLUMN);
                float lng=MSCursor.getFloat(MSDbAdapter.LNG_COLUMN);
                String localization=MSCursor.getString(MSDbAdapter.LOCALIZATION_COLUMN);
                tasks.add(new MSDBTask( id, name, description, completed, date, picture, 
                         hour,  lat, lng, localization));

                idlast++;

            } while(MSCursor.moveToNext());
        }

    }

    private Cursor getAllEntriesFromDb() {
        MSCursor = MSDbAdapter.getAllMS();

        if(MSCursor != null) {

            startManagingCursor(MSCursor);
            MSCursor.moveToFirst();
        }
        return MSCursor;
    }
    private void updateListViewData() {

        MSCursor.requery();
        tasks.clear();
        updateTaskList();
        listAdapter.notifyDataSetChanged();

    }
    @Override
    protected void onDestroy() {
        if(MSDbAdapter != null)
            MSDbAdapter.close();
        super.onDestroy();
    }
}

我从其他 Activity 中获取值，当然我检查了从下一个 Activity 转发的值是否正确。我正在使用 MDSBTask 类来获取和设置值:

package com.example.maszspotkanie;

public class MSDBTask {
    private int id;
    private String description;
    private boolean completed;
    private String date;
    private int picture;
    private String hour;
    private Float lat;
    private Float lng;
    private String localization;
    private String name;


    public MSDBTask(int id, String name,String description, boolean completed,String date, int picture, 
            String hour, Float lat, Float lng, String localization) {
        this.id = id;
        this.description = description;
        this.completed = completed;
        this.date=date;
        this.hour=hour;
        this.picture=picture;
        this.lat=lat;
        this.lng=lng;
        this.localization=localization;
        this.name=name;
        System.out.println("TASK: id "+id+" nazwa "+name+ " termin " +date+ " h "+ hour 
                + " opis " +description + " zdjecie " + picture+" lat " + lat
                + " lng " +lng + " lokalizacja " +localization );
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public boolean isCompleted() {
        return completed;
    }

    public void setCompleted(boolean completed) {
        this.completed = completed;
    }
    public int getPicture() {
        return picture;
    }

    public void setPicture(int picture) {
        this.picture = picture;
    }
    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }
    public String getHour() {
        return hour;
    }

    public void setHour(String hour) {
        this.hour = hour;
    }
    public Float getLat() {
        return lat;
    }

    public void setLat(Float lat) {
        this.lat = lat;
    }
    public Float getLng() {
        return lng;
    }

    public void setLng(Float lng) {
        this.lng = lng;
    }
    public String getLocalization() {
        return localization;
    }

    public void setLocalization(String localization) {
        this.localization = localization;
    }
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

当我打开已保存的行时，我在错误的列中得到了值。示例:

             Values to save            Saved values
name           name1                      name1
date           12/09/2015                 0
description    description1               Wrocław
picture        123                        13
lat            51.12708                   14.0
lng            16.991863                  51.12708
localization   Wrocław                    16.9919
hour           12:30                      123      

可能这只是一个我看不到的愚蠢错误。

Answer 1

您的 COLUMN INDEX 与实际的不匹配。

private static final String DB_CREATE_MSDatabase_TABLE = "CREATE TABLE "
            + DB_MS_TABLE + "( " + KEY_ID + " " + ID_OPTIONS + ", " + KEY_NAME
            + " " + NAME_OPTIONS + ", " + KEY_DESCRIPTION + " "
            + DESCRIPTION_OPTIONS + ", " + KEY_COMPLETED + " "
            + COMPLETED_OPTIONS + ", " + KEY_DATE + " " + DATE_OPTIONS + ", "
            + KEY_PICTURE + " " + PICTURE_OPTIONS + ", " + KEY_HOUR + " "
            + HOUR_OPTIONS + ", " + KEY_LAT + " " + LAT_OPTIONS + ", "
            + KEY_LNG + " " + LNG_OPTIONS + ", " + KEY_LOCALIZATION + " "
            + LOCALIZATION_OPTIONS + ");";

//Your COLUMN_INDEX
public static final int ID_COLUMN = 0;           //Ok
public static final int NAME_COLUMN = 1;         //Ok
public static final int DESCRIPTION_COLUMN = 9;  //It Should Be 2
public static final int COMPLETED_COLUMN = 2;    //It Should Be 3
public static final int DATE_COLUMN = 3;         //It Should Be 4
public static final int PICTURE_COLUMN = 4;      //It Should Be 5
public static final int HOUR_COLUMN = 5;         //It Should Be 6
public static final int LAT_COLUMN = 6;          //It Should Be 7
public static final int LNG_COLUMN = 7;          //It Should Be 8
public static final int LOCALIZATION_COLUMN = 8; //It Should Be 9

替代方法:您可以使用 getColumnIndex 获取 COLUMN INDEX

public int getColumnIndex (String columnName)

Returns the zero-based index for the given column name, or -1 if the column doesn't exist.
E.g.
cursor.getString(cursor.getColumnIndex(KEY_NAME)));