数据库数据插入

Question

public class DataBaseHandler extends SQLiteOpenHelper {

    private static String DATABASE_NAME = "UnitDatabase";
    private static String MEASUREMENT_TYPE_TABLE = "measurement_types";
    private static String idCol = "id";
    private static String typeCol = "type";
    private static int DATABASE_VERSION = 1;
    private static String[] measurementType = new String[] {"acceleration", "angles", "area", "astronomical",
            "density", "energy", "force", "frequency", "length/distance", "power", "pressure", "speed",
            "temperature", "torque", "volume", "weight"};

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String create_table = "CREATE TABLE " + MEASUREMENT_TYPE_TABLE +
                "(" + idCol + " integer primary key autoincrement " +
                typeCol + " varchar(255) not null ";

        db.execSQL(create_table);
    }

    /**
     * Populate the table containing measurement types
     * @param db
     */
    public void populateMeasurementTable(SQLiteDatabase db) {
        db = this.getWritableDatabase();
        for(int i = 0; i < measurementType.length; i++) {
            ContentValues values = new ContentValues();
            values.put(typeCol, measurementType[i]);
            db.insert(MEASUREMENT_TYPE_TABLE, null, values);
        }
    }
}

我想我应该先发布我的代码，然后再问问题。我的问题是最后一个方法(populateMeasurementTable())的实现。我希望能够在表中插入多个值，这就是我要这样做的方式，但是我认为这不是一种有效的方法，特别是如果我有更大的数组，比如：

private static String[] densityUnitTypes = new String[] { "grain/cubic foot", "grain/cubic inch",
            "grain/gallon [UK]", "grain/gallon [US]", "grain/ounce [UK]", "grain/ounce [US]", "grain/quart [UK]",
            "grain/quart [US]", "gram/cubic centimeter", "gram/cubic kilometer", "gram/cubic meter",
            "gram/cubic millimeter", "gram/kiloliter", "gram/liter", "gram/litre", "gram/microliter", "gram/milliliter",
            "hectogram/cubic centimeter", "hectogram/cubic kilometer", "hectogram/cubic meter",
            "hectogram/cubic micrometer", "hectogram/cubic millimeter", "hectogram/hectoliter", "hectogram/kiloliter",
            "hectogram/liter", "hectogram/litre", "hectogram/microliter", "hectogram/milliliter",
            "kilogram/cubic centimeter", "kilogram/cubic kilometer", "kilogram/cubic meter",
            "kilogram/cubic micrometer", "kilogram/cubic millimeter", "kilogram/kiloliter", "kilogram/liter",
            "kilogram/litre", "kilogram/microliter", "kilogram/milliliter", "kiloton/cubic mile [UK]",
            "kiloton/cubic mile [US]", "kiloton/cubic yard [UK]", "kiloton/cubic yard [US]", "kilotonne/cubic meter",
            "kilotonne/kiloliter", "kilotonne/liter", "kilotonne/litre", "microgram/cubic centimeter",
            "microgram/cubic kilometer", "microgram/cubic meter", "microgram/cubic micrometer",
            "microgram/cubic millimeter", "microgram/cubic nanometer", "microgram/kiloliter", "microgram/liter",
            "microgram/litre", "microgram/microliter", "microgram/milliliter", "milligram/cubic centimeter",
            "milligram/cubic kilometer", "milligram/cubic meter", "milligram/cubic millimeter",
            "milligram/kiloliter", "milligram/liter", "milligram/litre", "milligram/microliter", "milligram/milliliter",
            "nanogram/cubic centimeter", "nanogram/cubic kilometer", "nanogram/cubic meter",
            "nanogram/cubic millimeter", "nanogram/kiloliter", "nanogram/liter", "nanogram/litre",
            "nanogram/microliter", "nanogram/milliliter", "ounce/cubic foot", "ounce/cubic inch", "ounce/gallon [UK]",
            "ounce/gallon [US]", "pound/cubic foot", "pound/cubic inch", "pound/cubic mile", "pound/cubic yard",
            "pound/gallon [UK]", "pound/gallon [US]", "tonne/cubic kilometer", "tonne/cubic meter", "tonne/kiloliter",
            "tonne/liter", "tonne/litre", "water [0°C, solid]", "water [20°C]", "water [4°C]" };

我曾经考虑过使用API中的bulkInsert(Uri uri, ContentValues[] values)类中的ContentResolver方法，但是，我觉得编写自己的提供者类来扩展ContentProvider类，然后编写自己的bulkInsert()方法的实现是有点核心的--尽管它将允许我使用我已经读到的更有效的事务来执行任务。

所以我的问题是：

• 我选择这样做的效率有多高？
• 我会更好地编写我自己的bulkInsert()方法的实现吗？
• 有不同的更有效的方法吗？(例如，编写原始SQL输出以插入多个值)

Answer 1

要确切地了解哪些是最有效的，最好的方法是使用：

long start = System.nanoTime();
... perform operations ...
long stop = System.nanoTime();
double milliSecondsElapsed = (stop - start) / 1000000.0;

我认为你目前的做法很好。

我发现有另一种方法，但似乎需要编写一个看起来像这样的SQL查询：

INSERT INTO Contacts 
SELECT 'ae0caa6a-8ff6-d63f-0253-110b20ac2127' AS ID, 'xxx' AS FirstName, 'xxx' AS LastName, '9008987887' AS PhoneNumber, 'xxx@gmail.com' AS EmailId, 'Yes' AS Status 
UNION SELECT '9afab56e-a18a-47f2-fd62-35c78d8e0d94', 'yyy', 'yyy', '7890988909', 'yyy@gmail.com', 'Yes' 
UNION SELECT '378d757a-ee60-07a4-e8bc-396b402c3270', 'zzz', 'zzz', '9000898454', 'zzz@gmail.com', 'Yes'

除非向数据库插入行的替代方法显着地提高了性能，否则我将坚持您现在所做的方式。我认为编写这个SQL语句所需的代码会很难看(如果您想将当前的String[]/ContentValues方法转换为这个SQL语句)。尤其是考虑到我假设你只做了一次大规模插入。此外，转换为SQL所需的代码当然也需要时间来执行，这可能会忽略使用单个SQL语句执行大量插入所带来的性能提高。

此外，您当前的方法非常容易阅读和理解。

至于你是否应该写一个bulkInsert()方法，你可以仅仅为了它的挑战.如果您没有更好的事情可做:)

摘要

坚持你现在正在使用的东西。

Answer 2

代码中有一些常量。为什么它们不是最终的？另外，idCol和typeCol的命名也让我感到困惑。为什么不是ID_COLUMN和TYPE_COLUMN。

measurementTypes也是如此，这些都是常量。将它们命名并使用。