java - 如何从sqlite中的列中选择特定值

Question

我有一个 ListView，其中显示来自 SQLite 数据库的地点名称。该数据库有 5 列:_id , Map_no , Location , Date , Notes .

当我单击 ListView 中的某个项目时我希望能够获得Map_no , Location , Date和Notes从数据库中获取并将它们作为额外内容存储在 Intent 中，以便我能够将它们传递到下一个 Activity 。我尝试编写自己的查询，名为 getLocation返回位置并查找了有关该主题的不同教程并搜索SO答案，但无济于事。

MainActivity.java

package com.********************.******.***************;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.support.v4.widget.SimpleCursorAdapter;
import android.view.Window;
import android.widget.AdapterView;
import android.widget.Button;
import android.content.Intent;
import android.view.View;
import android.widget.ListView;



public class MainActivity extends Activity {
    private static final String TAG = "MainActivity";
    DBHelper dbHelper;
    SimpleCursorAdapter simpleCursorAdapter;
    private String map;
    private String location;
    private String date;
    private String note;

    public MainActivity() {
    }

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        requestWindowFeature(Window.FEATURE_NO_TITLE);
        requestWindowFeature(Window.FEATURE_ACTION_BAR);
        setContentView(R.layout.activity_main);
        dbHelper = new DBHelper(this);
        displayList();
    }

    public void displayList() {
        final Cursor cursor = dbHelper.getData();
        String from[] = new String[]{dbHelper.LOCATION};
        int to[] = new int[]{R.id.ListLayout1};
        simpleCursorAdapter = new SimpleCursorAdapter(this, R.layout.row_item, cursor, from, to, 0);


        final ListView listView = (ListView) findViewById(R.id.list);
        listView.setAdapter(simpleCursorAdapter);
        while (cursor.moveToNext());
        listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
                String location = dbHelper.getLocation(l).toString();
                Intent intent = new Intent(MainActivity.this, EditData.class);
                intent.putExtra("Location", location);
                intent.putExtra("Date", DBHelper.DATE);
                intent.putExtra("Not_at_homes", DBHelper.NOTATHOMES);
                startActivity(intent);
            }
        });
    }

    public void toInput(View view) {
        Button addbutton = (Button) findViewById(R.id.addButton);
        Intent intent = new Intent(MainActivity.this, InputPage.class);
        startActivity(intent);

    }
}

DBhelper.java

package com.******************.*****.****************;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;



public class DBHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "App_database.db";
    public static final String TABLE_NAME = "App_data_table";
    public static final String MAP_NO = "Map_no";
    public static final String LOCATION = "Location";
    public static final String DATE = "Date";
    public static final String NOTES = "Notes";
    public static final String _id = "_id";
    public static final String TAG = "DBHelper";
    private static DBHelper instance = null;


    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
       String createTable = "CREATE TABLE " + TABLE_NAME + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " + MAP_NO + " INTEGER, " + LOCATION + " TEXT, " + DATE + " INTEGER, " + Notes + " TEXT)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);

    }

    public boolean insertData(String Map_no, String Location, String Date, String Not_at_homes) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(MAP_NO, Map_no);
        contentValues.put(LOCATION, Location);
        contentValues.put(DATE, Date);
        contentValues.put(NOTES, Notes);

        Log.d(TAG, "addData: Adding " + Location + " to " + TABLE_NAME);

        long result = db.insert(TABLE_NAME, null, contentValues);
        if (result == -1) {
            return false;
        } else {
            return true;
        }


    }
//getting data
    public Cursor getData() {
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_NAME;
        Cursor data = db.rawQuery(query, null);
        return data;
    }

    public Cursor getItemID(String map, String location, String date, String notes) {
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT _id FROM " + TABLE_NAME +
                " WHERE " + MAP_NO + " = '" + map + "'" + " AND " + LOCATION + " = '" + location + "'" +
                " AND " + DATE + " = '" + date + "'" + " AND "
                + NOTES + " = '" + Notes+ "'";
        Cursor data = db.rawQuery(query, null);
        return data;
    }

    public void updateData(int id,String newMapNo, String oldMapNo, String newLocation, String oldLocation, String newDate, String oldDate, String newNotes, String oldNotes){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "UPDATE " + TABLE_NAME + " SET " + MAP_NO +
                " = '" + newMapNo + " WHERE " + _id + " = '" + id + "'" +
                " AND " + MAP_NO + " = '" + oldMapNo + "'"+ LOCATION +
                " = '" + newLocation + " WHERE " + _id + " = '" + id + "'" +
                " AND " + LOCATION + " = '" + oldLocation + "'"+ DATE +
                " = '" + newDate + " WHERE " + _id + " = '" + id + "'" +
                " AND " + DATE + " = '" + oldDate + "'"+ NOTES+
                " = '" + newNotes + " WHERE " + _id + " = '" + id + "'" +
                " AND " + NOTES + " = '" + oldNotes + "'";
        Log.d(TAG, "updating: query: " + query);
        Log.d(TAG, "updating: Setting map number, Location, Date and Not at homes to new values: Map number: " +
                newMapNo + " Location: " + newLocation + " Date: " +
                newDate + " Notes: " + newNotes);
        db.execSQL(query);
    }

    public void deleteData(int id, String mapNo, String location, String date, String Notes){
        SQLiteDatabase database = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_NAME + " WHERE "
                + _id + " = '" + id + "'" + " AND " + MAP_NO + " = '" + mapNo + "'" +
                LOCATION + " = '" + location + "'" + DATE + " = '" + date + "'" + NOTES +
                " = '" + Notes + "'";
        Log.d(TAG, "deleting: query: " + query);
        Log.d(TAG, "deleting: Deleting note with values: Map number: " + mapNo + " Location: " + location + " Created on: " + date + " With notes: " + Notes + ".");
        database.execSQL(query);
    }

    public static DBHelper getInstance(Context context){
        if (instance == null){
            instance = new DBHelper(context.getApplicationContext());
        }

        return instance;
    }

    public Cursor getLocation(long id){
        SQLiteDatabase db = this.getWritableDatabase();
            String query = "SELECT " + LOCATION + " FROM " + TABLE_NAME + " WHERE " + _id + " = '" + id + "'";
            Cursor rowDataLocation = db.rawQuery(query, null);
            return rowDataLocation;
    }

}

Answer 1

你可以尝试一下

public String getLocation(long id) 
 {

    SQLiteDatabase db = this.getWritableDatabase();
    String get_LOCATION = "";
    String last_query = "SELECT " + LOCATION + " FROM " + TABLE_NAME + " WHERE " + _id + " = '" + id + "'";
    Cursor c = db.rawQuery(last_query, null);
    if (c != null && c.moveToFirst()) 
    {
        get_LOCATION = c.getString(0); // Return Value 
    }
    db.close();
    return get_LOCATION;

}

仅供引用

String location = dbHelper.getLocation(l).toString();

您应该在 getLocation() 部分中传递正确的 PARAMETER 。确保 long l 包含正确的 ID 。