如何为android创建一个SQL数据库？

Question

嘿，我想知道如何为android创建数据库，而不是由用户编辑的数据库，这是一个完整的数据库，并在应用程序apk中。 我是否使用excel或access，然后将数据库添加到应用程序文件夹，或者我应该怎么做？我只能在互联网上找到的是如何创建一个数据库，使用应用程序本身添加数据。 那么如何制作数据库？

Answer 1

如果我没有记错的话，你只需要将数据库的sqlite文件添加到应用程序资源中，然后将其与DBHelper类（如these）一起使用，但适用于您的应用程序需求。

我个人创建并编辑了带有this Firefox extension的sqlite文件。

Answer 2

您可以将现有的SQLite数据库放到应用程序的资产文件夹中。 如果您想在运行时在应用程序中访问此数据库，则可以使用此代码打开数据库;

 SQLiteDatabase db = SQLiteDatabase.openDatabase("path-to-database", null, SQLiteDatabase.OPEN_READONLY); 

目前我不知道你是否可以直接从资产打开数据库文件。否则，你也许可以把它复制到你的应用程序的内部数据库目录：

private void copyDatabase(final Context context) { 
    InputStream reader = null; 
    try { 
    reader = context.getAssets().open("asset-name"); 
    final File out = context.getDatabasePath("databasename"); 
    final OutputStream writer = new FileOutputStream(out); 
    final byte[] buffer = new byte[1024 * 100]; 
    int bytesRead = reader.read(buffer); 
    while (bytesRead > 0) { 
     writer.write(buffer, 0, bytesRead); 
     bytesRead = reader.read(buffer); 
    } 
    writer.close(); 
    } catch (IOException e) { 
    Log.e(TAG, e.getMessage(), e); 
    } finally { 
    if (reader != null) { 
     try { 
      reader.close(); 
     } catch (IOException e) { 
      Log.e(TAG, e.getMessage(), e); 
     } 
    } 
    } 
} 

Answer 3

@Danny回答：

有用于创建和更新数据库的两个选项。

一种是从外部创建数据库，然后将其放置在项目的assets文件夹中，然后从那里复制整个数据库。如果数据库有很多表和其他组件，这会更快。通过更改res/values/strings.xml文件中的数据库版本号来触发升级。升级将通过外部创建新数据库，用新数据库替换assets文件夹中的旧数据库，以其他名称将旧数据库保存在内部存储中，将新数据库从assets文件夹复制到内部存储，传输所有数据来自旧数据库（之前已重命名）的数据放入新数据库，最后删除旧数据库。您最初可以使用SQLite Manager FireFox插件来执行创建的sql语句来创建数据库。

另一种选择是从sql文件内部创建数据库。这并不是那么快，但如果数据库只有几张表，那么延迟对用户来说可能是不明显的。通过更改res/values/strings.xml文件中的数据库版本号来触发升级。升级将通过处理升级sql文件来完成。数据库中的数据将保持不变，除非其容器被删除，例如删除表格。

下面的示例演示如何使用任一方法。

这是一个示例create_database.sql文件。它将被放置在项目的assets文件夹中，或者被复制到SQLite Manager的“Execute SQL”中以创建外部方法的数据库（注意：注意Android所需的表格注释）。

--Android requires a table named 'android_metadata' with a 'locale' column 
CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US'); 
INSERT INTO "android_metadata" VALUES ('en_US'); 

CREATE TABLE "kitchen_table"; 
CREATE TABLE "coffee_table"; 
CREATE TABLE "pool_table"; 
CREATE TABLE "dining_room_table"; 
CREATE TABLE "card_table"; 

下面是一个示例update_database.sql文件，它是被放置在该项目的内部方法的资产文件夹或复制到的SQLite Manager的“执行SQL”创建数据库的外部方法。 （注意：请注意，本示例中包含的SQL解析器将忽略所有三种类型的SQL注释。）

--CREATE TABLE "kitchen_table"; This is one type of comment in sql. It is ignored by parseSql. 
/* 
* CREATE TABLE "coffee_table"; This is a second type of comment in sql. It is ignored by parseSql. 
*/ 
{ 
CREATE TABLE "pool_table"; This is a third type of comment in sql. It is ignored by parseSql. 
} 
/* CREATE TABLE "dining_room_table"; This is a second type of comment in sql. It is ignored by parseSql. */ 
{ CREATE TABLE "card_table"; This is a third type of comment in sql. It is ignored by parseSql. } 

--DROP TABLE "picnic_table"; Uncomment this if picnic table was previously created and now is being replaced. 
CREATE TABLE "picnic_table" ("plates" TEXT); 
INSERT INTO "picnic_table" VALUES ('paper'); 

以下是添加到数据库版本号的/res/values/strings.xml文件的条目。

<item type="string" name="databaseVersion" format="integer">1</item> 

这是一个访问数据库然后使用它的活动。 （注意：您可能需要运行在一个单独的线程数据库代码，如果它使用了大量的资源。）

package android.example; 

import android.app.Activity; 
import android.database.sqlite.SQLiteDatabase; 
import android.os.Bundle; 

/** 
* @author Danny Remington - MacroSolve 
* 
*   Activity for demonstrating how to use a sqlite database. 
*/ 
public class Database extends Activity { 
    /** Called when the activity is first created. */ 
    @Override 
    public void onCreate(Bundle savedInstanceState) { 
     super.onCreate(savedInstanceState); 
     setContentView(R.layout.main); 
     DatabaseHelper myDbHelper; 
     SQLiteDatabase myDb = null; 

     myDbHelper = new DatabaseHelper(this); 
     /* 
     * Database must be initialized before it can be used. This will ensure 
     * that the database exists and is the current version. 
     */ 
     myDbHelper.initializeDataBase(); 

     try { 
      // A reference to the database can be obtained after initialization. 
      myDb = myDbHelper.getWritableDatabase(); 
      /* 
      * Place code to use database here. 
      */ 
     } catch (Exception ex) { 
      ex.printStackTrace(); 
     } finally { 
      try { 
       myDbHelper.close(); 
      } catch (Exception ex) { 
       ex.printStackTrace(); 
      } finally { 
       myDb.close(); 
      } 
     } 

    } 
} 

这里是创建或在必要时更新数据库的数据库辅助类。 （注：Android的要求创建，为了与SQLite数据库工作扩展SQLiteOpenHelper类。）

package android.example; 

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; 

/** 
* @author Danny Remington - MacroSolve 
* 
*   Helper class for sqlite database. 
*/ 
public class DatabaseHelper extends SQLiteOpenHelper { 

    /* 
    * The Android's default system path of the application database in internal 
    * storage. The package of the application is part of the path of the 
    * directory. 
    */ 
    private static String DB_DIR = "/data/data/android.example/databases/"; 
    private static String DB_NAME = "database.sqlite"; 
    private static String DB_PATH = DB_DIR + DB_NAME; 
    private static String OLD_DB_PATH = DB_DIR + "old_" + DB_NAME; 

    private final Context myContext; 

    private boolean createDatabase = false; 
    private boolean upgradeDatabase = false; 

    /** 
    * 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, DB_NAME, null, context.getResources().getInteger(
       R.string.databaseVersion)); 
     myContext = context; 
     // Get the path of the database that is based on the context. 
     DB_PATH = myContext.getDatabasePath(DB_NAME).getAbsolutePath(); 
    } 

    /** 
    * Upgrade the database in internal storage if it exists but is not current. 
    * Create a new empty database in internal storage if it does not exist. 
    */ 
    public void initializeDataBase() { 
     /* 
     * Creates or updates the database in internal storage if it is needed 
     * before opening the database. In all cases opening the database copies 
     * the database in internal storage to the cache. 
     */ 
     getWritableDatabase(); 

     if (createDatabase) { 
      /* 
      * If the database is created by the copy method, then the creation 
      * code needs to go here. This method consists of copying the new 
      * database from assets into internal storage and then caching it. 
      */ 
      try { 
       /* 
       * Write over the empty data that was created in internal 
       * storage with the one in assets and then cache it. 
       */ 
       copyDataBase(); 
      } catch (IOException e) { 
       throw new Error("Error copying database"); 
      } 
     } else if (upgradeDatabase) { 
      /* 
      * If the database is upgraded by the copy and reload method, then 
      * the upgrade code needs to go here. This method consists of 
      * renaming the old database in internal storage, create an empty 
      * new database in internal storage, copying the database from 
      * assets to the new database in internal storage, caching the new 
      * database from internal storage, loading the data from the old 
      * database into the new database in the cache and then deleting the 
      * old database from internal storage. 
      */ 
      try { 
       FileHelper.copyFile(DB_PATH, OLD_DB_PATH); 
       copyDataBase(); 
       SQLiteDatabase old_db = SQLiteDatabase.openDatabase(OLD_DB_PATH, null, SQLiteDatabase.OPEN_READWRITE); 
       SQLiteDatabase new_db = SQLiteDatabase.openDatabase(DB_PATH,null, SQLiteDatabase.OPEN_READWRITE); 
       /* 
       * Add code to load data into the new database from the old 
       * database and then delete the old database from internal 
       * storage after all data has been transferred. 
       */ 
      } catch (IOException e) { 
       throw new Error("Error copying database"); 
      } 
     } 

    } 

    /** 
    * 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 transfering bytestream. 
    * */ 
    private void copyDataBase() throws IOException { 
     /* 
     * Close SQLiteOpenHelper so it will commit the created empty database 
     * to internal storage. 
     */ 
     close(); 

     /* 
     * Open the database in the assets folder as the input stream. 
     */ 
     InputStream 

    myInput = myContext.getAssets().open(DB_NAME); 

      /* 
      * Open the empty db in interal storage as the output stream. 
      */ 
      OutputStream myOutput = new FileOutputStream(DB_PATH); 

      /* 
      * Copy over the empty db in internal storage with the database in the 
      * assets folder. 
      */ 
      FileHelper.copyFile(myInput, myOutput); 

      /* 
      * Access the copied database so SQLiteHelper will cache it and mark it 
      * as created. 
      */ 
      getWritableDatabase().close(); 
     } 

     /* 
     * This is where the creation of tables and the initial population of the 
     * tables should happen, if a database is being created from scratch instead 
     * of being copied from the application package assets. Copying a database 
     * from the application package assets to internal storage inside this 
     * method will result in a corrupted database. 
     * <P> 
     * NOTE: This method is normally only called when a database has not already 
     * been created. When the database has been copied, then this method is 
     * called the first time a reference to the database is retrieved after the 
     * database is copied since the database last cached by SQLiteOpenHelper is 
     * different than the database in internal storage. 
     */ 
     @Override 
     public void onCreate(SQLiteDatabase db) { 
      /* 
      * Signal that a new database needs to be copied. The copy process must 
      * be performed after the database in the cache has been closed causing 
      * it to be committed to internal storage. Otherwise the database in 
      * internal storage will not have the same creation timestamp as the one 
      * in the cache causing the database in internal storage to be marked as 
      * corrupted. 
      */ 
      createDatabase = true; 

      /* 
      * This will create by reading a sql file and executing the commands in 
      * it. 
      */ 
       // try { 
       // InputStream is = myContext.getResources().getAssets().open(
       // "create_database.sql"); 
       // 
       // String[] statements = FileHelper.parseSqlFile(is); 
       // 
       // for (String statement : statements) { 
       // db.execSQL(statement); 
       // } 
       // } catch (Exception ex) { 
       // ex.printStackTrace(); 
       // } 
     } 

     /** 
     * Called only if version number was changed and the database has already 
     * been created. Copying a database from the application package assets to 
     * the internal data system inside this method will result in a corrupted 
     * database in the internal data system. 
     */ 
     @Override 
     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
      /* 
      * Signal that the database needs to be upgraded for the copy method of 
      * creation. The copy process must be performed after the database has 
      * been opened or the database will be corrupted. 
      */ 
      upgradeDatabase = true; 

      /* 
      * Code to update the database via execution of sql statements goes 
      * here. 
      */ 

      /* 
      * This will upgrade by reading a sql file and executing the commands in 
      * it. 
      */ 
      // try { 
      // InputStream is = myContext.getResources().getAssets().open(
      // "upgrade_database.sql"); 
      // 
      // String[] statements = FileHelper.parseSqlFile(is); 
      // 
      // for (String statement : statements) { 
      // db.execSQL(statement); 
      // } 
      // } catch (Exception ex) { 
      // ex.printStackTrace(); 
      // } 
     } 

     /** 
     * Called everytime the database is opened by getReadableDatabase or 
     * getWritableDatabase. This is called after onCreate or onUpgrade is 
     * called. 
     */ 
     @Override 
     public void onOpen(SQLiteDatabase db) { 
      super.onOpen(db); 
     } 

     /* 
     * Add your public helper methods to access and get content from the 
     * database. You could return cursors by doing 
     * "return myDataBase.query(....)" so it'd be easy to you to create adapters 
     * for your views. 
     */ 

    } 

的