如何在Android应用程序中使用现有的数据库

Question

我已创建了一个SQLite数据库。我想将这个数据库文件与我的Android项目一起使用，希望将此数据库捆绑到我的应用程序中。

不想创建一个新的数据库，应用程序如何访问此数据库并将其用作自己的数据库？

- Muhammad Umar

---

• 使用 Lita 工具打开数据库
• http://www.dehats.com/drupal/?q=node/58 - 你可以通过 Eclipse DDMS 视图 > 浏览器 > 在列表数据中获取已安装/已使用的应用程序 > 数据库 > yourdb_file.db，在浏览器窗口中有导入导出选项 - 在这里，您可以导入已经在移动设备上创建的数据库。

- Ankur Loriya

我强烈推荐使用SQLiteAssetHelper：https://github.com/jgilfelt/android-sqlite-asset-helper。 - CommonsWare

Answer 1

注意： 在尝试此代码之前，请在以下代码中找到此行：

private static String DB_NAME ="YourDbName"; // Database name

这里的DB_NAME是你数据库的名称。假设你在assets文件夹中有一个数据库副本，因此如果你的数据库名称是ordersDB，那么DB_NAME的值将为ordersDB。

private static String DB_NAME ="ordersDB";

---

将数据库保存在资产文件夹中，然后按照以下步骤操作：

DataHelper类：

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

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

public class DataBaseHelper extends SQLiteOpenHelper {

    private static String TAG = "DataBaseHelper"; // Tag just for the LogCat window
    private static String DB_NAME ="YourDbName"; // Database name
    private static int DB_VERSION = 1; // Database version
    private final File DB_FILE;
    private SQLiteDatabase mDataBase;
    private final Context mContext;

    public DataBaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        DB_FILE = context.getDatabasePath(DB_NAME);
        this.mContext = context;
    }

    public void createDataBase() throws IOException {
        // If the database does not exist, copy it from the assets.
        boolean mDataBaseExist = checkDataBase();
        if(!mDataBaseExist) {
            this.getReadableDatabase();
            this.close();
            try {
                // Copy the database from assests
                copyDataBase();
                Log.e(TAG, "createDatabase database created");
            } catch (IOException mIOException) {
                throw new Error("ErrorCopyingDataBase");
            }
        }
    }

    // Check that the database file exists in databases folder
    private boolean checkDataBase() {
        return DB_FILE.exists();
    }

    // Copy the database from assets
    private void copyDataBase() throws IOException {
        InputStream mInput = mContext.getAssets().open(DB_NAME);
        OutputStream mOutput = new FileOutputStream(DB_FILE);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInput.read(mBuffer)) > 0) {
            mOutput.write(mBuffer, 0, mLength);
        }
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }

    // Open the database, so we can query it
    public boolean openDataBase() throws SQLException {
        // Log.v("DB_PATH", DB_FILE.getAbsolutePath());
        mDataBase = SQLiteDatabase.openDatabase(DB_FILE, null, SQLiteDatabase.CREATE_IF_NECESSARY);
        // mDataBase = SQLiteDatabase.openDatabase(DB_FILE, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
        return mDataBase != null;
    }

    @Override
    public synchronized void close() {
        if(mDataBase != null) {
            mDataBase.close();
        }
        super.close();
    }

}

编写一个类似以下的DataAdapter类：

import java.io.IOException;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class TestAdapter {

    protected static final String TAG = "DataAdapter";

    private final Context mContext;
    private SQLiteDatabase mDb;
    private DataBaseHelper mDbHelper;

    public TestAdapter(Context context) {
        this.mContext = context;
        mDbHelper = new DataBaseHelper(mContext);
    }

    public TestAdapter createDatabase() throws SQLException {
        try {
            mDbHelper.createDataBase();
        } catch (IOException mIOException) {
            Log.e(TAG, mIOException.toString() + "  UnableToCreateDatabase");
            throw new Error("UnableToCreateDatabase");
        }
        return this;
    }

    public TestAdapter open() throws SQLException {
        try {
            mDbHelper.openDataBase();
            mDbHelper.close();
            mDb = mDbHelper.getReadableDatabase();
        } catch (SQLException mSQLException) {
            Log.e(TAG, "open >>"+ mSQLException.toString());
            throw mSQLException;
        }
        return this;
    }

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

     public Cursor getTestData() {
         try {
             String sql ="SELECT * FROM myTable";
             Cursor mCur = mDb.rawQuery(sql, null);
             if (mCur != null) {
                mCur.moveToNext();
             }
             return mCur;
         } catch (SQLException mSQLException) {
             Log.e(TAG, "getTestData >>"+ mSQLException.toString());
             throw mSQLException;
         }
     }
}

现在你可以像这样使用它：

TestAdapter mDbHelper = new TestAdapter(urContext);
mDbHelper.createDatabase();
mDbHelper.open();

Cursor testdata = mDbHelper.getTestData();

mDbHelper.close();

编辑：感谢JDx

对于Android 4.1（果冻豆），更改：

DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";

至：

DB_PATH = context.getApplicationInfo().dataDir + "/databases/";

在DataHelper类中，这段代码可以在Jelly Bean 4.2多用户环境下运行。

编辑：我们可以使用动态路径代替硬编码路径。

DB_PATH = context.getDatabasePath(DB_NAME).getAbsolutePath();

这将为我们提供数据库文件的完整路径，并适用于所有Android版本。

- Yaqub Ahmad

---

这在使用多个用户的Jellybean 4.2上无法工作。有没有解决方法？ - JDx

没问题，我已经弄清楚了。如果你将DataHelper类中的DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";改成DB_PATH = context.getApplicationInfo().dataDir + "/databases/";这段代码就可以在JB 4.2多用户上正常工作。 - JDx

我正在使用这个代码，但仍然无法解决应用程序的第一次运行错误，仍然提示“找不到表”。我使用了sqlite数据库。我在DB_NAME中定义了数据库扩展名，例如：Example.sqlite，并将文件名放在assests文件夹下的我的数据库文件夹中，名为Example.sqlite。希望您能帮助我，先生；D - Cjames

给JDx和其他人：有一个方法Context.getDatabasePath()，它可以显示特定数据库的确切位置（无需使用DB_PATH =“/ data / data /”+ context.getPackageName() +“/ databases /”;等垃圾代码）。http://developer.android.com/reference/android/content/Context.html#getDatabasePath(java.lang.String) - Alex Semeniuk

请将以下更改 private static String DB_NAME ="YourDbName"; 更改为 private static String DB_NAME ="YourDbName.db"; - Akash Dubey

Answer 2

如果您有预先构建好的数据库，则将其复制到资产文件夹中，并创建一个名为 DataBaseHelper 的新类，该类实现 SQLiteOpenHelper 接口。 然后使用以下代码：

import java.io.File;
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.SQLiteOpenHelper;

public class DataBaseHelperClass extends SQLiteOpenHelper{
 //The Android's default system path of your application database.
private static String DB_PATH = "/data/data/package_name/databases/";
// Data Base Name.
private static final String DATABASE_NAME = "DBName.sqlite";
// Data Base Version.
private static final int DATABASE_VERSION = 1;
// Table Names of Data Base.
static final String TABLE_Name = "tableName";

public Context context;
static SQLiteDatabase sqliteDataBase;

/**
 * Constructor
 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
 * @param context
 * Parameters of super() are    1. Context
 *                              2. Data Base Name.
 *                              3. Cursor Factory.
 *                              4. Data Base Version.
 */
public DataBaseHelperClass(Context context) {       
    super(context, DATABASE_NAME, null ,DATABASE_VERSION);
    this.context = context;
}

/**
 * Creates a empty database on the system and rewrites it with your own database.
 * 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.
 * */
public void createDataBase() throws IOException{
    //check if the database exists
    boolean databaseExist = checkDataBase();

    if(databaseExist){
        // Do Nothing.
    }else{
        this.getWritableDatabase();         
        copyDataBase(); 
    }// end if else dbExist
} // end createDataBase().

/**
 * Check if the database already exist to avoid re-copying the file each time you open the application.
 * @return true if it exists, false if it doesn't
 */
public boolean checkDataBase(){
    File databaseFile = new File(DB_PATH + DATABASE_NAME);
    return databaseFile.exists();        
}

/**
 * Copies your database from your local assets-folder to the just created empty database in the
 * system folder, from where it can be accessed and handled.
 * This is done by transferring byte stream.
 * */
private void copyDataBase() throws IOException{ 
    //Open your local db as the input stream
    InputStream myInput = context.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 input file to the output file
    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(); 
}

/**
 * This method opens the data base connection.
 * First it create the path up till data base of the device.
 * Then create connection with data base.
 */
public void openDataBase() throws SQLException{      
    //Open the database
    String myPath = DB_PATH + DATABASE_NAME;
    sqliteDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);  
}

/**
 * This Method is used to close the data base connection.
 */
@Override
public synchronized void close() { 
    if(sqliteDataBase != null)
        sqliteDataBase.close(); 
    super.close(); 
}

/**
* Apply your methods and class to fetch data using raw or queries on data base using 
* following demo example code as:
*/
public String getUserNameFromDB(){
    String query = "select User_First_Name From "+TABLE_USER_DETAILS;
    Cursor cursor = sqliteDataBase.rawQuery(query, null);
    String userName = null;
    if(cursor.getCount()>0){
        if(cursor.moveToFirst()){
    do{
                userName = cursor.getString(0);
            }while (cursor.moveToNext());
        }
    }
    return userName;
}


@Override
public void onCreate(SQLiteDatabase db) {
    // No need to write the create table query.
    // As we are using Pre built data base.
    // Which is ReadOnly.
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // No need to write the update table query.
    // As we are using Pre built data base.
    // Which is ReadOnly.
    // We should not update it as requirements of application.
}   
}

希望这可以帮助到您...

- Manoj Fegde

Answer 3

对于这个问题，我在使用其他 DatabaseHelpers 时遇到了困难，不确定原因。
以下是对我有效的解决方法：

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

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

public class DatabaseHelper extends SQLiteOpenHelper {

  private static final String TAG = DatabaseHelper.class.getSimpleName();

  private final Context context;
  private final String assetPath;
  private final String dbPath;

  public DatabaseHelper(Context context, String dbName, String assetPath)
      throws IOException {
    super(context, dbName, null, 1);
    this.context = context;
    this.assetPath = assetPath;
    this.dbPath = "/data/data/"
        + context.getApplicationContext().getPackageName() + "/databases/"
        + dbName;
    checkExists();
  }

  /**
   * Checks if the database asset needs to be copied and if so copies it to the
   * default location.
   * 
   * @throws IOException
   */
  private void checkExists() throws IOException {
    Log.i(TAG, "checkExists()");

    File dbFile = new File(dbPath);

    if (!dbFile.exists()) {

      Log.i(TAG, "creating database..");

      dbFile.getParentFile().mkdirs();
      copyStream(context.getAssets().open(assetPath), new FileOutputStream(
          dbFile));

      Log.i(TAG, assetPath + " has been copied to " + dbFile.getAbsolutePath());
    }

  }

  private void copyStream(InputStream is, OutputStream os) throws IOException {
    byte buf[] = new byte[1024];
    int c = 0;
    while (true) {
      c = is.read(buf);
      if (c == -1)
        break;
      os.write(buf, 0, c);
    }
    is.close();
    os.close();
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  }
}

- Dan Brough

Answer 4

如果您已经有一个数据库，将其保存在资产文件夹中并复制到应用程序中。更多细节请参见Android数据库基础知识。

- Andy

Answer 5

你可以通过使用内容提供程序来实现这一点。应用程序中使用的每个数据项都保持私有。如果一个应用程序想要在应用程序之间共享数据，那么只有一种技术可以实现这一点，那就是使用内容提供程序，它提供了访问该私有数据的接口。

- jeet