用数据库发送应用程序

如果您的应用程序需要一个数据库并且内置数据,那么运送该应用程序的最佳方式是什么? 我是不是该:

  • 预先创建SQLite数据库并将其包含在.apk

  • 在应用程序中包含SQL命令并让它创建数据库并在第一次使用时插入数据?

  • 我看到的缺点是:

  • 可能的SQLite版本不匹配可能会导致问题,我目前不知道数据库应该在哪里以及如何访问它。

  • 在设备上创建和填充数据库可能需要很长时间。

  • 有什么建议么? 指向任何问题的文档将不胜感激。


    我刚刚在ReignDesign博客的一篇名为“在Android应用程序中使用自己的SQLite数据库”的文章中找到了一种方法。 基本上你预先创建你的数据库,把它放到你的apk的资产目录中,然后首先使用copy到“/ data / data / YOUR_PACKAGE / databases /”目录。


    有两种创建和更新数据库的选项。

    一种是从外部创建数据库,然后将其放置在项目的资产文件夹中,然后从那里复制整个数据库。 如果数据库有很多表和其他组件,这会更快。 通过更改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文件。 它将被放置在内部方法的项目的assets文件夹中,或者被复制到SQLite Manager的“Execute 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需要创建一个扩展SQLiteOpenHelper的类才能使用Sqlite数据库。)

    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.
         */
    
    }
    

    这里是FileHelper类,它包含字节流复制文件和解析sql文件的方法。

    package android.example;
    
    import java.io.BufferedReader;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.FileReader;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.io.OutputStream;
    import java.io.Reader;
    import java.nio.channels.FileChannel;
    
    /**
     * @author Danny Remington - MacroSolve
     * 
     *         Helper class for common tasks using files.
     * 
     */
    public class FileHelper {
        /**
         * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
         * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
         * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
         * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
         * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
         * operation.
         * 
         * @param fromFile
         *            - InputStream for the file to copy from.
         * @param toFile
         *            - InputStream for the file to copy to.
         */
        public static void copyFile(InputStream fromFile, OutputStream toFile) throws IOException {
            // transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[1024];
            int length;
    
            try {
                while ((length = fromFile.read(buffer)) > 0) {
                    toFile.write(buffer, 0, length);
                }
            }
            // Close the streams
            finally {
                try {
                    if (toFile != null) {
                        try {
                            toFile.flush();
                        } finally {
                            toFile.close();
                        }
                }
                } finally {
                    if (fromFile != null) {
                        fromFile.close();
                    }
                }
            }
        }
    
        /**
         * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
         * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
         * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
         * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
         * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
         * operation.
         * 
         * @param fromFile
         *            - String specifying the path of the file to copy from.
         * @param toFile
         *            - String specifying the path of the file to copy to.
         */
        public static void copyFile(String fromFile, String toFile) throws IOException {
            copyFile(new FileInputStream(fromFile), new FileOutputStream(toFile));
        }
    
        /**
         * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
         * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
         * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
         * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
         * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
         * operation.
         * 
         * @param fromFile
         *            - File for the file to copy from.
         * @param toFile
         *            - File for the file to copy to.
         */
        public static void copyFile(File fromFile, File toFile) throws IOException {
            copyFile(new FileInputStream(fromFile), new FileOutputStream(toFile));
        }
    
        /**
         * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
         * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
         * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
         * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
         * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
         * operation.
         * 
         * @param fromFile
         *            - FileInputStream for the file to copy from.
         * @param toFile
         *            - FileInputStream for the file to copy to.
         */
        public static void copyFile(FileInputStream fromFile, FileOutputStream toFile) throws IOException {
            FileChannel fromChannel = fromFile.getChannel();
            FileChannel toChannel = toFile.getChannel();
    
            try {
                fromChannel.transferTo(0, fromChannel.size(), toChannel);
            } finally {
                try {
                    if (fromChannel != null) {
                        fromChannel.close();
                    }
                } finally {
                    if (toChannel != null) {
                        toChannel.close();
                    }
                }
            }
        }
    
        /**
         * Parses a file containing sql statements into a String array that contains
         * only the sql statements. Comments and white spaces in the file are not
         * parsed into the String array. Note the file must not contained malformed
         * comments and all sql statements must end with a semi-colon ";" in order
         * for the file to be parsed correctly. The sql statements in the String
         * array will not end with a semi-colon ";".
         * 
         * @param sqlFile
         *            - String containing the path for the file that contains sql
         *            statements.
         * 
         * @return String array containing the sql statements.
         */
        public static String[] parseSqlFile(String sqlFile) throws IOException {
            return parseSqlFile(new BufferedReader(new FileReader(sqlFile)));
        }
    
        /**
         * Parses a file containing sql statements into a String array that contains
         * only the sql statements. Comments and white spaces in the file are not
         * parsed into the String array. Note the file must not contained malformed
         * comments and all sql statements must end with a semi-colon ";" in order
         * for the file to be parsed correctly. The sql statements in the String
         * array will not end with a semi-colon ";".
         * 
         * @param sqlFile
         *            - InputStream for the file that contains sql statements.
         * 
         * @return String array containing the sql statements.
         */
        public static String[] parseSqlFile(InputStream sqlFile) throws IOException {
            return parseSqlFile(new BufferedReader(new InputStreamReader(sqlFile)));
        }
    
        /**
         * Parses a file containing sql statements into a String array that contains
         * only the sql statements. Comments and white spaces in the file are not
         * parsed into the String array. Note the file must not contained malformed
         * comments and all sql statements must end with a semi-colon ";" in order
         * for the file to be parsed correctly. The sql statements in the String
         * array will not end with a semi-colon ";".
         * 
         * @param sqlFile
         *            - Reader for the file that contains sql statements.
         * 
         * @return String array containing the sql statements.
         */
        public static String[] parseSqlFile(Reader sqlFile) throws IOException {
            return parseSqlFile(new BufferedReader(sqlFile));
        }
    
        /**
         * Parses a file containing sql statements into a String array that contains
         * only the sql statements. Comments and white spaces in the file are not
         * parsed into the String array. Note the file must not contained malformed
         * comments and all sql statements must end with a semi-colon ";" in order
         * for the file to be parsed correctly. The sql statements in the String
         * array will not end with a semi-colon ";".
         * 
         * @param sqlFile
         *            - BufferedReader for the file that contains sql statements.
         * 
         * @return String array containing the sql statements.
         */
        public static String[] parseSqlFile(BufferedReader sqlFile) throws IOException {
            String line;
            StringBuilder sql = new StringBuilder();
            String multiLineComment = null;
    
            while ((line = sqlFile.readLine()) != null) {
                line = line.trim();
    
                // Check for start of multi-line comment
                if (multiLineComment == null) {
                    // Check for first multi-line comment type
                    if (line.startsWith("/*")) {
                        if (!line.endsWith("}")) {
                            multiLineComment = "/*";
                        }
                    // Check for second multi-line comment type
                    } else if (line.startsWith("{")) {
                        if (!line.endsWith("}")) {
                            multiLineComment = "{";
                    }
                    // Append line if line is not empty or a single line comment
                    } else if (!line.startsWith("--") && !line.equals("")) {
                        sql.append(line);
                    } // Check for matching end comment
                } else if (multiLineComment.equals("/*")) {
                    if (line.endsWith("*/")) {
                        multiLineComment = null;
                    }
                // Check for matching end comment
                } else if (multiLineComment.equals("{")) {
                    if (line.endsWith("}")) {
                        multiLineComment = null;
                    }
                }
    
            }
    
            sqlFile.close();
    
            return sql.toString().split(";");
        }
    
    }
    

    SQLiteAssetHelper库使得这个任务非常简单。

    添加Gradle依赖很容易(但是Jar也可用于Ant / Eclipse),并且可以在以下位置找到文档:
    https://github.com/jgilfelt/android-sqlite-asset-helper

    正如文件中所解释的:

  • 将依赖关系添加到模块的gradle构建文件中:

    dependencies {
        compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'
    }
    
  • 将数据库复制到资产目录中的名为assets/databases的子目录中。 例如:
    assets/databases/my_database.db

    (或者,您可以将数据库压缩到zip文件中,例如assets/databases/my_database.zip 。这不是必需的,因为APK已经作为整体进行了压缩。)

  • 创建一个班级,例如:

    public class MyDatabase extends SQLiteAssetHelper {
    
        private static final String DATABASE_NAME = "my_database.db";
        private static final int DATABASE_VERSION = 1;
    
        public MyDatabase(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
    }
    
  • 链接地址: http://www.djcxy.com/p/12943.html

    上一篇: Ship an application with a database

    下一篇: Remove underline from links in TextView