代码之家 › 专栏 › 技术社区 › jaghz123

从SQLite数据库检索特定列

android-sqlite android-studio sqlite android java

jaghz123 · 技术社区 · 6 年前

目前,我正在开发一款android应用程序,它基本上会为药物、何时/如何服用药物创建提醒,我想要的一个选项是检索所有药物名称的列表,以便用户能够将该列表发送到特定的电子邮件。目前我只有一封电子邮件,但我不知道如何检索药物名称。我确实在数据库处理程序类中创建了一个名为get all names的新方法,但我不太确定这是否正确。此外,一旦获得该列表,我如何将其包含在电子邮件意图中?

处方:

package com.example.finalapp;

import android.app.Activity;
import android.content.Intent;
import android.net.Uri;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;

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


public class Prescription extends Activity {

    private Button btn_order, btn_locate;
    private List list=new ArrayList();
    private ArrayAdapter<String> adapter;


    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.prescription);

        btn_order = (Button) findViewById(R.id.btn_order);
        btn_locate = (Button) findViewById(R.id.btn_locate);

        btn_locate.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                startActivity(new Intent(Prescription.this, PharmacyMapFragment.class));
            }
        });



//        DatabaseHandler db=new DatabaseHandler(this);
//        list = db.getAllNames();
//        //final Cursor accountCursor = db.getAccount(userPosition);
//        db.close();






        btn_order.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent intent = new Intent(Intent.ACTION_SENDTO);
                intent.setData(Uri.parse("mailto:")); // only email apps should handle this
                intent.putExtra(Intent.EXTRA_SUBJECT, "Prescription order for ");
                intent.putExtra(Intent.EXTRA_TEXT, "Medications");
                if (intent.resolveActivity(getPackageManager()) != null) {
                    startActivity(intent);
                }
            }
        });










    }

}

数据库处理程序:

package com.example.finalapp;

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;

import java.util.ArrayList;


public class DatabaseHandler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "ReminderManager";

    // table name
    private static final String TABLE_REMINDERS = "Reminders";
    private static final String TABLE_TIPS = "Tips";

    // Table Columns names

    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_REMINDERS_TABLE = "CREATE TABLE " + TABLE_REMINDERS + "( counter INTEGER PRIMARY KEY , medname TEXT , medinfo  TEXT,"+
                "interval  INTEGER ,TimeUnit TEXT,Freq INTEGER,weekdays TEXT,Times TEXT, StartTime TEXT,StartDate DATE,EndDate DATE)";
        db.execSQL(CREATE_REMINDERS_TABLE);
        String CREATE_TIPS_TABLE = "CREATE TABLE " + TABLE_TIPS + "( id INTEGER PRIMARY KEY, title  TEXT,"+
                "intro_text TEXT )";
        db.execSQL(CREATE_TIPS_TABLE);
        Log.d("DatabaseHandler","Database Created");
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_REMINDERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TIPS);
        // Create tables again
        onCreate(db);
    }

    int deleteReminder(String name)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_REMINDERS,"medname" + "='" + name + "'",null);
    }

    void addReminder(ArrayList al) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put("counter", (int) al.get(0));
        values.put("medname", ""+al.get(1));
        values.put("medinfo", ""+al.get(2));
        values.put("interval", (int) al.get(3));
        values.put("TimeUnit", ""+al.get(4));
        values.put("Freq", ""+al.get(5));
        values.put("weekdays", ""+al.get(6));
        values.put("Times",""+al.get(7));
        values.put("StartTime", ""+al.get(8));
        values.put("StartDate", ""+al.get(9));
        values.put("EndDate", ""+al.get(10));

        // Inserting Row
        db.insert(TABLE_REMINDERS, null, values);
        Log.d("Inserted:","Row Inserted");
        db.close(); // Closing database connection

        ArrayList all=new ArrayList();
        all.add(al.get(0));
        all.add(al.get(1));
        //final boolean hi = al.add("hi");
        FragmentReminder rm=new FragmentReminder();
        rm.receiveData(al);
    }

    void addTips(ArrayList al) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("id", (int)al.get(0));
        values.put("title", ""+al.get(1));
        values.put("intro_text", ""+al.get(2));

        // Inserting Row
        db.insert(TABLE_TIPS, null, values);
        Log.d("Inserted:",values+"");
        db.close(); // Closing database connection
    }



    // Getting single contact
    ArrayList getReminder(int count) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_REMINDERS, new String[] { "counter","medname",
                        "medinfo", "interval","TimeUnit","Freq","weekdays","Times","StartTime","StartDate","EndDate"}, "counter" + "=?",
                new String[] { ""+count },null,null,null,null);
        if (cursor != null)
            cursor.moveToFirst();

        ArrayList al=new ArrayList();

        al.add(cursor.getString(0));al.add(cursor.getString(1));al.add(cursor.getString(2));al.add(cursor.getString(3));
        al.add(cursor.getString(4));al.add(cursor.getString(5));al.add(cursor.getString(6));al.add(cursor.getString(7));
        al.add(cursor.getString(8));al.add(cursor.getString(9));al.add(cursor.getString(10));

        return al;

    }

    // Getting All Contacts
    public ArrayList getAllReminders() {
        ArrayList ReminderList = new ArrayList();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_REMINDERS;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()){
            do{
                ReminderList.add(cursor.getString(1));
            }while(cursor.moveToNext());
            db.close();
        }

        return ReminderList;
    }

    // Getting All Contacts
    public ArrayList getAllNames() {
        ArrayList NamesList = new ArrayList();
        // Select All Query
        String selectQuery = "SELECT" + "medname" +   "* FROM " + TABLE_REMINDERS;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()){
            do{
                NamesList.add(cursor.getString(1));
            }while(cursor.moveToNext());
            db.close();
        }

        return NamesList;
    }




    // Getting Reminder Count
    public int getRemindersCount() {
        String countQuery = "SELECT  * FROM " + TABLE_REMINDERS;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        if(cursor.getCount()==0)
            return 0;
        // looping through all rows and adding to list
        cursor.moveToLast();
        int count=Integer.parseInt(cursor.getString(0));

        db.close();
        return count;
    }

    public ArrayList getReminder(String s) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_REMINDERS, new String[] { "counter","medname",
                        "medinfo", "interval","TimeUnit","Freq","weekdays","Times","StartTime","StartDate","EndDate"}, "medname" + "=?",
                new String[] {s },null,null,null,null);
        if (cursor != null)
            cursor.moveToFirst();

        ArrayList al=new ArrayList();

        al.add(cursor.getString(0));al.add(cursor.getString(1));al.add(cursor.getString(2));al.add(cursor.getString(3));
        al.add(cursor.getString(4));al.add(cursor.getString(5));al.add(cursor.getString(6));al.add(cursor.getString(7));
        al.add(cursor.getString(8));al.add(cursor.getString(9));al.add(cursor.getString(10));

        return al;
    }
}

3 回复 | 直到 6 年前

Jyoti JK 6 年前

您可以尝试使用以下命令来获取特定列列表。

public String[] getit(String tablename)
{
    try {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor x = db.rawQuery("SELECT * FROM "+tablename, null);
    int n=x.getCount();
    x.moveToFirst();
    String[] a=new String[n];int i=0;
   do
    {
        a[i]=x.getString(x.getColumnIndex("medname"));
        i++;
    } while(x.moveToNext());

    x.close();
    return a;
    }
    catch (Exception e)
    {
        return null;
    }
}

或者将代码更改为,

  NamesList.add(cursor.getString(x.getColumnIndex("medname")));

在您的电子邮件意图中,使用 putExtra()

StringBuilder sb = new StringBuilder(); 
for (String s : db.getit("table")) {
    sb.append(s);
    sb.append("\n");
}
intent.putExtra(Intent.EXTRA_TEXT, sb.toString());

Parag Pawar 6 年前

首先,ArrayList应该有一个数据类型。在您的情况下,当您检索药物名称时,它应该是字符串。因此请使用ArrayList< String &燃气轮机;

用这个替换getAllNames方法,看看它是否有效。

public ArrayList<String> getAllNames() {
  ArrayList<String> mNamesList = new ArrayList<>();
  // Select All Query
  String selectQuery = "SELECT medname FROM " + TABLE_REMINDERS;

  SQLiteDatabase db = this.getReadableDatabase();
  Cursor cursor = db.rawQuery(selectQuery, null);
  cursor.moveToFirst()

  for(int i = 0; i < cursor.getCount; i++){
   mNamesList.add(cursor.getString(cursor.getColumnIndex("medname")));
  } 

  db.close();

  return mNamesList;
}

如果遇到任何问题,请返回!

Sachin Soma 6 年前

1、将此方法包含到DatabaseHandler类中。

public Cursor readData(SQLiteDatabase sqlite)
    {
        //Method for reading data from table

        return (sqlite.query(DbContract.TABLE_NAME,null,"medname",null,null,null,null));

    }

2、在需要数据的地方遵循此代码。

 DbHelper dbHelper= new DbHelper(this);
            SQLiteDatabase database= dbHelper.getReadableDatabase();
            Cursor cursor= dbHelper.readData(database);
            if(cursor.getCount()>0)
            {
                while (cursor.moveToNext())
                {
                    String medName;

                    medName=cursor.getString(cursor.getColumnIndex("medname"));
               }
            }