从SQLite数据库-ANDROID-SQLite数据库中选择特定行

Question

我正在为我的项目数据库工作，我有主键(rowid)，号码(联系号码)和姓名的列。我正在我的数据库中添加两个具有相同编号的不同条目，我需要从数据库中提取这两个条目。提取的代码是

public Cursor SelectList(String number) throws SQLException {
           String query = "SELECT FROM " + DATABASE_TABLE + " WHERE " + KEY_NUMBER + "='" + number.trim()+"'";

            Cursor mcursor = db.rawQuery(query, null);

            if(mcursor != null) {
                mcursor.moveToFirst();
            }

            return mcursor;
        }

但它在此行显示SQLite异常

Cursor mcursor = db.rawQuery(query, null);

DatabaseHandler的代码

package com.example.gul.databasealvie;

/**
 * Created by gul on 6/6/15.
 */

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DBAdapter {
    static final String KEY_ROWID = "_id";
    static final String KEY_NAME = "name";
    static final String KEY_NUMBER = "number";
    static final String KEY_ID="listid";

    static final String TAG = "DBAdapter";
    static final String DATABASE_NAME = "MyDB20";
    static final String DATABASE_TABLE = "contacts5";
    static final int DATABASE_VERSION = 1;

    static final String DATABASE_CREATE= "create table contacts5(_id integer primary key , "
            + "name text not null, number text not null, listid text not null);";
    final Context context;
    DatabaseHelper DBHelper;
    SQLiteDatabase db;
    public DBAdapter(Context ctx)
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }
    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context)
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db)
        {
            try {
                db.execSQL(DATABASE_CREATE);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS contacts");
            onCreate(db);
        }
    }
    //---opens the database---
    public DBAdapter open() throws SQLException
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }
    public boolean DeleteList(String number){
        db.execSQL("DELETE FROM "+DATABASE_TABLE+" WHERE "+KEY_NUMBER+"="+number);
        return true;
    }
    public void DropTable(){
        db.execSQL("Delete From " + DATABASE_TABLE);
    }
    public Cursor SelectList(String number) throws SQLException {
       String query = "SELECT FROM " + DATABASE_TABLE + " WHERE " + KEY_NUMBER + "='" + number.trim()+"'";
        Cursor mcursor = db.rawQuery(query,null);

        if (mcursor != null) {
            mcursor.moveToFirst();
        }
        return mcursor;
    }
    //---closes the database---
    public void close()
    {
        DBHelper.close();
    }
    //---insert a contact into the database---
    public long insertContact(TableData contact, String id )
    {
        long myid=Long.parseLong(id);
        ContentValues initialValues = new ContentValues();

        initialValues.put(KEY_NAME, contact.getName());
        initialValues.put(KEY_NUMBER,contact.getPhoneNumber());
        initialValues.put(KEY_ID, id);

        return db.insert(DATABASE_TABLE, null, initialValues);
    }
    //---deletes a particular contact---
    public boolean DeletContact(String number)throws SQLException{



            return db.delete(DATABASE_TABLE, KEY_NUMBER + "=" + number, null) > 0;
        }


    public long insertContact(Anonymous contact, String id )
    {

        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NAME, contact.getName());
        initialValues.put(KEY_NUMBER, contact.getPhoneNumber());
        initialValues.put(KEY_ID, id);
       // Log.d("Contact", contact.getName() + contact.getPhoneNumber());

        return db.insert(DATABASE_TABLE, null, initialValues);
    }
    public boolean deleteContact(String number)
    {

        return db.delete(DATABASE_TABLE, KEY_NUMBER + " = ?",
                new String[] { number }) > 0;
    }
    public Cursor getAllContacts()
    {
        return db.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
                KEY_NUMBER}, null, null, null, null, null);
    }
    //---retrieves a particular contact---
    public Cursor getContact(long rowId) throws SQLException
    {
        Cursor mCursor =
                db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                                KEY_NAME, KEY_NUMBER}, KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    public boolean iskey(long rowid)throws SQLException
    {
        Cursor mCursor=db.query(true,DATABASE_TABLE, new String[]{KEY_ROWID,KEY_NAME,KEY_NUMBER },KEY_ROWID+"="+rowid,null,null
        ,null,null,null);
        if(mCursor!=null && mCursor.moveToFirst()){
            return true;
        }

       else
            return false;
    }
    //---updates a contact---
    public boolean updateContact(long rowId, String name, String email)
    {
        ContentValues args = new ContentValues();
        args.put(KEY_NAME, name);
        args.put(KEY_NUMBER, email);
        return db.update(
                DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}

用于测试目的的代码

package com.example.gul.databasealvie;


import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.widget.Toast;
public class MainActivity extends Activity {
    DBAdapter db;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = new DBAdapter(this);
        AddContact();
        PrintingList();
        GetContacts();
        GetContact();
        //UpdateContact();
        DeleteContact();
    }
    public void AddContact() {
        Anonymous a= new Anonymous(34,"Wei-Meng Lee", "12345");
        Anonymous b= new Anonymous(2,"Wejhkjh Lee", "12234");
//---add a contact---
        db.open();
        if (db.insertContact(a,"22") >= 0){
            Toast.makeText(this, "Add successful.", Toast.LENGTH_LONG).show();
        }
    if (db.insertContact(b,"21") >= 0) {
            Toast.makeText(this, "Add successful.", Toast.LENGTH_LONG).show();
        }
        if (db.insertContact(b,"21") >= 0) {
            Toast.makeText(this, "Add successful.", Toast.LENGTH_LONG).show();
        }
        db.close();
    }
    public void PrintingList(){
        db.open();
        Cursor mCursor=db.SelectList("12234");
        if(mCursor.moveToFirst()){
            do {
                displaylist(mCursor);
            }while(mCursor.moveToNext());
        }
        db.close();

    }
    public void GetContacts() {
//--get all contacts---
        db.open();
        // db.DeleteList("12345");
       // if(db.DeletContact("12234")) {
         //   Log.i("Deleted contact", "");
        //}

        Cursor c = db.getAllContacts();
        if (c.moveToFirst())
        {
            do {
                DisplayContact(c);
            } while (c.moveToNext());
        }
        db.close();
    }
    public void GetContact() {
//---get a contact---
        db.open();
        Cursor c = db.getContact(2);
        if (c.moveToFirst())
            DisplayContact(c);
        else
            Toast.makeText(this, "No contact found", Toast.LENGTH_LONG).show();
        db.close();
    }
    public void UpdateContact() {
//---update a contact---
        db.open();
        if (db.updateContact(1, "Wei-Meng Lee", "weimenglee@gmail.com"))
            Toast.makeText(this, "Update successful.", Toast.LENGTH_LONG).show();
        else
            Toast.makeText(this, "Update failed.", Toast.LENGTH_LONG).show();
        db.close();
    }
    public void DeleteContact() {
        db.open();
        //if (db.deleteContact(1))
          //  Toast.makeText(this, "Delete successful.", Toast.LENGTH_LONG).show();
        //else
          //  Toast.makeText(this, "Delete failed.", Toast.LENGTH_LONG).show();
        db.close();
    }
    public void DisplayContact(Cursor c)
    {


        Log.i(
                "contacts", "id: " + c.getString(0) + "\n" +
                        "Name: " + c.getString(1) + "\n" +
                        "Number: " + c.getString(2)
        );
        db.open();
        if((db.iskey(2))){
            Log.i("Yay ", "it's working");
        }
        db.close();
    }
    public void displaylist(Cursor c){
        Log.i(
                "List","listid:"+c.getString(0)+ "\n"+
                "NAMElist: " + c.getString(1)+ "\n" +
                        "list: "+c.getString(2)
        );

        }
    }

Anonymous.java的代码

package com.example.gul.databasealvie;

/**
 * Created by gul on 6/6/15.
 */


/**
 * Created by Noor Zia on 5/26/2015.
 */
public class Anonymous {

    public long id;
    public String name;
    public String number;


    public Anonymous(){

        name="Unknown";
    }
    public Anonymous(String no){
        name="Unknown";
        number = no;

    }
    public Anonymous(long id, String name, String number){
        name=name;
        this.number=number;
        this.name=name;
        this.id=id;
    }



    public long getID(){
        return this.id;
    }

    // setting id


    // getting name
    public String getName(){

        return this.name;

    }

    // setting name


    // getting phone number
    public String getPhoneNumber(){
        return this.number;
    }

    // setting phone number

}

Answer 1

    dbHelper = new DBHelper(getApplicationContext());
    SQLiteDatabase db = dbHelper.getReadableDatabase();

    Cursor cursor = db.rawQuery("select * from centuaryTbl where email='"+email+"'",null);
    if (cursor.moveToFirst())
    {
        do
        {
            String s1 = cursor.getString(cursor.getColumnIndex("s1"));
            String s2 = cursor.getString(cursor.getColumnIndex("s2"));
            String s3 = cursor.getString(cursor.getColumnIndex("s3"));


        }while (cursor.moveToNext());
    }

Answer 2

您需要指定要检索的列。例如：

String query = "SELECT name, number FROM " + DATABASE_TABLE + " WHERE " + KEY_NUMBER + "='" + number.trim() + "'";

有关Select clause的更多信息

Answer 3

或者，您可以使用'*‘获取所有列

您的查询将是

String query = "SELECT * FROM " + DATABASE_TABLE + " WHERE " + KEY_NUMBER + "='" + number.trim()+"'";