如何在 Android 上从 SQLite 数据库检索数据？

Question

我在“asset/database”文件夹中使用自己的数据库，我已经在 .java 数据库文件中给出了路径，

我想在开始按钮上执行活动，代码是：

GreTest.java

package andro.Alkonsys.example;



import android.app.Activity;
import android.database.Cursor;

import android.os.Bundle;

import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button; 
import android.widget.RadioButton;
import android.widget.TextView;
import andro.Alkonsys.example.DataBaseHelper; 

public class GreTest extends Activity {

    public Button start_test1;
    public Button next;
    public TextView Question;
    public RadioButton Option_first;
    public RadioButton Option_second;
    public RadioButton Option_third;
    public RadioButton Option_forth;
    private DataBaseHelper db;

    @Override
    public void onCreate(Bundle SaveInstanceState) 
    {
        super.onCreate(SaveInstanceState);
     setContentView(R.layout.gretest);

        start_test1=(Button)findViewById(R.id.btnstart_test);
        next=(Button)findViewById(R.id.btnnextquastion);
        Question=(TextView)findViewById(R.id.testquestion);
        Option_first=(RadioButton)findViewById(R.id.radio_first);
        Option_second=(RadioButton)findViewById(R.id.radio_second);
        Option_third=(RadioButton)findViewById(R.id.radio_third);
        Option_forth=(RadioButton)findViewById(R.id.radio_forth);   


    start_test1.setOnClickListener(new OnClickListener()
     {

          //int Qno; 
            //Cursor c;
            public void onClick(View v){
                // TODO Auto-generated method stub
                Cursor c = db.getData();            
                //if(c.moveToFirst())
                //{
                    Question.setText(c.getString(2));
                    //Option_first.setText(c.getString(3));
                //}
                //Qno += 1;
            }


   });  
}






    }
















/*  private OnClickListener radio_listener = new OnClickListener() {  
        public void onClick(View v) {      
            // Perform action on clicks      
            RadioButton rb = (RadioButton) v;      
        Toast.makeText(GreTest.this, rb.getText(), Toast.LENGTH_SHORT).show();    
        }}; */

并且我的数据库文件包含所有内容查询和声明：

DataBaseHelper.java

package andro.Alkonsys.example;


import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;


public class DataBaseHelper extends SQLiteOpenHelper
{

    //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/+andro.Alkonsys.example()+/Database/"; 
    private static final String DATABASE_NAME ="GreSatDbase"; 

    //The version of the database that this class understands
    private static final int DATABASE_VERSION = 1;

    //private SQLiteDatabase myDataBase;  

    private final Context myContext;
    private SQLiteDatabase db;

    /**
     * Constructor
     * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
     * @param context
     */
    public DataBaseHelper(Context context) 
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        myContext = context;
    }

    public void createDataBase() throws IOException{

        boolean dbExist = checkDataBase();

        if(dbExist){
            //do nothing - database already exist
        }else{

            //By calling this method and empty database will be created into the default system path
               //of your application so we are gonna be able to overwrite that database with our database.
            this.getReadableDatabase();

            try {

                copyDataBase();

            } catch (IOException e) {

                throw new Error("Error copying database");

            }
        }

    }

    private boolean checkDataBase(){

        SQLiteDatabase checkDB = null;

        try{
            String myPath = DB_PATH + DATABASE_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

        }catch(SQLiteException e){

            //database does't exist yet.

        }

        if(checkDB != null){

            checkDB.close();

        }

        return checkDB != null ? true : false;
    }

    private void copyDataBase() throws IOException{  
        //Open your local db as the input stream
        InputStream myInput = myContext.getAssets().open(DATABASE_NAME); 
        // Path to the just created empty db  
        String outFileName = DB_PATH + DATABASE_NAME;  
        //Open the empty db as the output stream 
        OutputStream myOutput = new FileOutputStream(outFileName);
        //transfer bytes from the inputfile to the outputfile 
        byte[] buffer = new byte[1024]; 
        int length;  
        while ((length = myInput.read(buffer))>0){ 
            myOutput.write(buffer, 0, length);     
            }    
        //Close the streams  
        myOutput.flush();    
        myOutput.close();   
        myInput.close();  
        } 

    public void openDataBase() throws SQLException{

        //Open the database
        String myPath = DB_PATH + DATABASE_NAME;
        db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }

    @Override
    public synchronized void close() {

            if(db != null)
                db.close();

            super.close();

    }



    @Override
    public void onCreate(SQLiteDatabase db) 
    {
        // TODO Auto-generated method stub

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
    {
        // TODO Auto-generated method stub
        String sql = myContext.getString(R.string.satgreDbase_onUpgrade);
        db.beginTransaction();
        try 
        {
            db.execSQL(sql);
            db.setTransactionSuccessful();
        }catch(SQLException e)
        {           
            System.out.println("Error Upgrading tables...");
        }
        finally 
        {
            db.endTransaction();
        }
        onCreate(db);
    }


    public Cursor getData() 
    {

        int Qno=1;
        // TODO Auto-generated method stub
        //Creates the query String. Query is static in DataCursor inner class
        String sql = "SELECT Question FROM PracTestAlgebra WHERE Qno = ?";

        //Create an array of String to specify values for ?'s in the sql Query
        String sqlArgs[] = new String[]{Integer.toString(Qno)};

        //Creates a cursor using the SQLiteDatabase object's rawQuery method
        Cursor c = db.rawQuery(sql,sqlArgs);

        //The cursor is returned
        return c;

    }



}  

，我想从我的数据库中检索数据，所以请帮助我。

谢谢

Answer 1

您可以在本文中找到帮助。

此外，我在应用此技术时遇到了一个错误：如果你的 sql 文件超过 1Mo，你的应用程序将无法读取它。这是因为android会自动压缩assets目录中的大文件，并且无法流式读取它们。

我发现克服这个错误的最简单方法是给你的sql文件加上压缩文件的扩展名：data.sql ->数据.sql.mp3。