代码之家  ›  专栏  ›  技术社区  ›  MikeT

有什么方法可以帮助解决常见的SQLite问题吗?

android-sqlite
  •  0
  • MikeT  · 技术社区  · 9 年前

    例如:-

    • 未找到表和列,因为通常假设 onCreate a的方法 DBHelper (子类 SQLiteOpenHelper 类)在每次创建DBHelper实例或每次运行应用程序时运行。 (注意! 仅当首次创建数据库时,然后仅当尝试使用 SQLiteDatabase getReadableDatabase getWriteableDatabse 方法,如果更改数据库结构/模式,则有3种简单的强制方法 创建时的回调函数 要运行,a)清除应用程序的数据,b)卸载应用程序或b)如果 onUpgrade 方法调用 方法(删除表后),然后增加数据库版本号)。
    • 插入/更新无效但未失败。

    那么,有什么公共设施可以提供帮助吗?

    笔记这是一个关于 sharing knowledge Q & A style .

    2 回复  |  直到 9 年前
        1
  •  2
  •   MikeT    8 年前

    以下是一些新手可能会觉得有用的常见实用程序,它们设计为非特定的,适用于任何数据库/表。

    目前有以下潜在有用的方法:-

    • getAllRowsFromTable 检索包含所有行的光标。
    • logCursorColumns
    • logCursorData 将光标数据和列写入日志。

    笔记我们一定会考虑添加/修改的请求/建议。

    此外,上述人员使用了一些方法或用于测试/创建上述内容:-

    • getEmptyColumnLessCursor ( :) ).
    • getBytedata 返回给定字节数组的十六进制字符串(用于BLOB处理)。
    • convertBytesToHex

    getAllRowsFromTable

    预期用途是创建一个光标,以便随后由 对数游标数据 方法。这不应该在生产应用程序中使用(并非不可能)。

    签名如下:- 

    public static Cursor getAllRowsFromTable(
                                  SQLiteDatabase db,
                                  String tablename,
                                  boolean use_error_checking,
                                  String forceRowidAs)

    其中:-

    • 第一个参数是SQLite数据库(由于泛型性质而需要)。
    • 第二个参数是从中获取数据的表名。
    • 第四个参数,如果不为null或如果字符串长度大于;0将创建一个附加列,该列根据参数命名,包含ROWID的内容(用于在未提供ROWID的别名,因此不包括ROWID时提供帮助)。 What's ROWID?? 

        mEventsDBHelper = new EventsDBHelper(this);


    // Get all rows from table (this exist or should do)
    Cursor events = CommonSQLiteUtilities.getAllRowsFromTable(
            mEventsDBHelper.getEventsDB(),
            EventsDBHelper.TBNAME,
            CommonSQLiteUtilities.ERROR_CHECKING_ON,
            null
    );

    // Deisgned to be problematic i.e. no such table        
    Cursor ooops = CommonSQLiteUtilities.getAllRowsFromTable(
            mEventsDBHelper.getEventsDB(),
            "NOTATABLE",
            CommonSQLiteUtilities.ERROR_CHECKING_ON,
            "rumplestiltskin"
    );


    • D/SQLITE_CSU: Table NOTATABLE was not located in the SQLite Database Master Table.

    • 尽管游标可能没有行也没有列(例如,在数据库中没有表的情况下),但始终会返回游标。

    • 当查询数据库表时(如果是),会捕获并报告异常。e、 g指定 是 作为第四个参数(有效地尝试使用 ROWID AS IS ,这是SQLIte不喜欢的,将导致类似的结果:- 

    10-09 18:57:52.591 3835-3835/? E/SQLiteLog: (1) near "IS": syntax error
10-09 18:57:52.592 3835-3835/? D/SQLITE_CSU: Exception encountered but trapped when querying table events Message was: 
                                             near "IS": syntax error (code 1): , while compiling: SELECT rowid AS  IS , * FROM events
10-09 18:57:52.592 3835-3835/? D/SQLITE_CSU: Stacktrace was:
10-09 18:57:52.592 3835-3835/? W/System.err: android.database.sqlite.SQLiteException: near "IS": syntax error (code 1): , while compiling: SELECT rowid AS  IS , * FROM events
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1163)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1034)
10-09 18:57:52.592 3835-3835/? W/System.err:     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1202)
10-09 18:57:52.592 3835-3835/? W/System.err:     at mjt.sqlitedbexamples.CommonSQLiteUtilities.getAllRowsFromTable(CommonSQLiteUtilities.java:97)
10-09 18:57:52.592 3835-3835/? W/System.err:     at mjt.sqlitedbexamples.MainActivity.onCreate(MainActivity.java:61)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.Activity.performCreate(Activity.java:5990)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2278)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2387)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.ActivityThread.access$800(ActivityThread.java:151)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.os.Handler.dispatchMessage(Handler.java:102)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.os.Looper.loop(Looper.java:135)
10-09 18:57:52.593 3835-3835/? W/System.err:     at android.app.ActivityThread.main(ActivityThread.java:5254)
10-09 18:57:52.593 3835-3835/? W/System.err:     at java.lang.reflect.Method.invoke(Native Method)
10-09 18:57:52.593 3835-3835/? W/System.err:     at java.lang.reflect.Method.invoke(Method.java:372)
10-09 18:57:52.593 3835-3835/? W/System.err:     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
10-09 18:57:52.593 3835-3835/? W/System.err:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
    • 返回的游标将为空,没有行或列。

    对数光标或列

    用于写入有关游标的列信息(通常反映数据库,尤其是在使用 getAllRowsFromTable ).

    签名如下:- 

        public static void logCursorColumns(Cursor csr)

    其中:-

    • 第一个参数是游标(任意游标)。

    示例用法:-

    笔记继续上一个示例。 

        CommonSQLiteUtilities.logCursorColumns(events);
    CommonSQLiteUtilities.logCursorColumns(ooops);


    10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: logCursorColumns invoked. Cursor has the following 8 columns.
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 1 is _id
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 2 is starts
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 3 is starts_timestamp
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 4 is ends
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 5 is ends_timestamp
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 6 is description
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 7 is counter
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 8 is bytedata
10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: logCursorColumns invoked. Cursor has the following 0 columns.

    对数游标数据

    这与 对数光标或列 除此之外,它还提供了更广泛的信息重新分类列,并提供了对存储在数据库中的实际数据进行重新分类的信息。

    签名如下:- 

        public static void logCursorData(Cursor csr)

    其中:-

    • 第一个参数是游标(任意游标)。

    示例用法:- 

        CommonSQLiteUtilities.logCursorData(events);
    CommonSQLiteUtilities.logCursorData(ooops);

    这将产生以下输出:- 

    10-09 19:30:31.801 1455-1455/? D/SQLITE_CSU: logCursorData Cursor has 6 rows with 8 columns.
10-09 19:30:31.801 1455-1455/? D/SQLITE_CSU: Information for row 1 offset=0
                                                For Column _id Type is INTEGER value as String is 1 value as long is 1 value as double is 1.0
                                                For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9
                                                For Column starts_timestamp Type is INTEGER value as String is 1507537831783 value as long is 1507537831783 value as double is 1.507537831783E12
                                                For Column ends Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column description Type is STRING value as String is TESTEVENT 001 just description value as long is 0 value as double is 0.0
                                                For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 00000000
10-09 19:30:31.802 1455-1455/? D/SQLITE_CSU: Information for row 2 offset=1
                                                For Column _id Type is INTEGER value as String is 2 value as long is 2 value as double is 2.0
                                                For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9
                                                For Column starts_timestamp Type is INTEGER value as String is 1507537831785 value as long is 1507537831785 value as double is 1.507537831785E12
                                                For Column ends Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column description Type is STRING value as String is TESTEVENT 002 description and bytearray value as long is 0 value as double is 0.0
                                                For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 0001020304050607080900
10-09 19:30:31.802 1455-1455/? D/SQLITE_CSU: Information for row 3 offset=2
                                                For Column _id Type is INTEGER value as String is 3 value as long is 3 value as double is 3.0
                                                For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9
                                                For Column starts_timestamp Type is INTEGER value as String is 1507537831789 value as long is 1507537831789 value as double is 1.507537831789E12
                                                For Column ends Type is INTEGER value as String is 15254678 value as long is 15254678 value as double is 1.5254678E7
                                                For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column description Type is STRING value as String is TESTEVENT 003 desc, bytes and endint value as long is 0 value as double is 0.0
                                                For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 7F7E7D7C
10-09 19:30:31.802 1455-1455/? D/SQLITE_CSU: Information for row 4 offset=3
                                                For Column _id Type is INTEGER value as String is 4 value as long is 4 value as double is 4.0
                                                For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9
                                                For Column starts_timestamp Type is INTEGER value as String is 1507537831792 value as long is 1507537831792 value as double is 1.507537831792E12
                                                For Column ends Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column description Type is STRING value as String is TESTEVENT 004 desc, bytes and endlong value as long is 0 value as double is 0.0
                                                For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 38424C56606A747E
10-09 19:30:31.803 1455-1455/? D/SQLITE_CSU: Information for row 5 offset=4
                                                For Column _id Type is INTEGER value as String is 5 value as long is 5 value as double is 5.0
                                                For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9
                                                For Column starts_timestamp Type is INTEGER value as String is 1507537831794 value as long is 1507537831794 value as double is 1.507537831794E12
                                                For Column ends Type is INTEGER value as String is 1699999999 value as long is 1699999999 value as double is 1.699999999E9
                                                For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column description Type is STRING value as String is TESTEVENT 005 desc, endint value as long is 0 value as double is 0.0
                                                For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 00000000
10-09 19:30:31.803 1455-1455/? D/SQLITE_CSU: Information for row 6 offset=5
                                                For Column _id Type is INTEGER value as String is 6 value as long is 6 value as double is 6.0
                                                For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9
                                                For Column starts_timestamp Type is INTEGER value as String is 1507537831796 value as long is 1507537831796 value as double is 1.507537831796E12
                                                For Column ends Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column description Type is STRING value as String is TESTEVENT 006 desc, endlong value as long is 0 value as double is 0.0
                                                For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0
                                                For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 00000000


    10-09 19:30:31.804 1455-1455/? D/SQLITE_CSU: logCursorData Cursor has 0 rows with 0 columns.

    该代码与QliteUtilities相同。java:- 

    public class CommonSQLiteUtilities {

    public static final boolean ERROR_CHECKING_ON = true;
    public static final boolean ERROR_CHECKING_OFF = false;

    // SQLite MASTER TABLE definitions
    static final String SQLITE_MASTER = "sqlite_master";
    static final String SM_TABLE_TYPE_COLUMN = "type";
    static final String SM_NAME_COLUMN = "name";
    static final String SM_TABLENAME_COLUMN = "tbl_name";
    static final String SM_ROOTPAGE_COLUMN = "rootpage";
    static final String SM_SQL_COLUMN = "sql";
    static final String SM_TYPE_TABLE = "table";
    static final String SM_TYPE_INDEX = "index";


    static final String CSU_TAG = "SQLITE_CSU";

    private CommonSQLiteUtilities() {}

    /**
     * Generic get all rows from an SQlite table,
     * allowing the existence of the table to be checked and also
     * allowing the ROWID to be added AS a supplied string
     *
     * @param db                    The SQLiteDatabase
     * @param tablename             The name of the table from which the
     *                              returned cursor will be created from;
     *                              Note!
     * @param use_error_checking    Whether ot not to try to detect errors
     *                              currently just table doesn't exist,
     *                              true to turn on, false to turn off
     *                              ERROR_CHECKING_ON = true
     *                              ERROR_CHECKING_OFF = false
     * @param forceRowidAs          If length of string passed is 1 or greater
     *                              then a column, as an alias of ROWID, will be
     *                              added to the cursor
     * @return                      the extracted cursor, or in the case of the
     *                              underlying table not existing an empty cursor
     *                              with no columns
     */
    public static Cursor getAllRowsFromTable(SQLiteDatabase db,
                                      String tablename,
                                      boolean use_error_checking,
                                      String forceRowidAs) {
        String[] columns = null;

        // Tablename must be at least 1 character in length
        if (tablename.length() < 1) {
            Log.d(CSU_TAG,new Object(){}.getClass().getEnclosingMethod().getName() +
                    " is finishing as the provided tablename is less than 1 character in length"
            );
            return new MatrixCursor(new String[]{});
        }

        // If use_error_checking is true then check that the table exists
        // in the sqlite_master table
        if (use_error_checking) {
            Cursor chkcsr = db.query(SQLITE_MASTER,null,
                    SM_TABLE_TYPE_COLUMN + "=? AND "
                            + SM_TABLENAME_COLUMN + "=?",
                    new String[]{SM_TYPE_TABLE,tablename},
                    null,null,null
            );

            // Ooops table is not in the Database so return an empty
            // column-less cursor
            if (chkcsr.getCount() < 1) {
                Log.d(CSU_TAG,"Table " + tablename +
                        " was not located in the SQLite Database Master Table."
                );
                // return empty cursor with no columns
                return new MatrixCursor(new String[]{});

            }
            chkcsr.close();
        }

        // If forcing an alias of ROWID then user ROWID AS ???, *
        if(forceRowidAs != null && forceRowidAs.length() > 0) {
            columns = new String[]{"rowid AS " +forceRowidAs,"*"};
        }

        // Finally return the Cursor but trap any exceptions
        try {
            return db.query(tablename, columns, null, null, null, null, null);
        } catch (Exception e) {
            Log.d(CSU_TAG,"Exception encountered but trapped when querying table " + tablename +
                    " Message was: \n" + e.getMessage());
            Log.d(CSU_TAG,"Stacktrace was:");
            e.printStackTrace();
            return new MatrixCursor(new String[]{});
        }
    }

    /**
     * Create and return a Cursor devoid of any rows and columns
     * Not used, prehaps of very little use.
     * @param db    The Sqlite database in which the cursor is to be created
     * @return      The empty Cursor
     */
    private static Cursor getEmptyColumnLessCursor(SQLiteDatabase db) {
        return new MatrixCursor(new String[]{});
    }

    /**
     * Write column names in the passed Cursor to the log
     * @param csr   The Cursor to be inspected.
     */
    public static void logCursorColumns(Cursor csr) {
        Log.d(CSU_TAG,
                new Object(){}.getClass().getEnclosingMethod().getName() +
                        " invoked. Cursor has the following " +
                        Integer.toString(csr.getColumnCount())+
                        " columns.");
        int position = 0;
        for (String column: csr.getColumnNames()) {
            position++;
            Log.d(CSU_TAG,"Column Name " +
                    Integer.toString(position) +
                    " is "
                    + column
            );
        }
    }

    /**
     * Write the contents of the Cursor to the log
     * @param csr   The Cursor that is to be displayed in the log
     */
    public static void logCursorData(Cursor csr) {
        int columncount = csr.getColumnCount();
        int rowcount = csr.getCount();
        int csrpos = csr.getPosition(); //<<< added 20171016 to
        Log.d(CSU_TAG,
                new Object(){}.getClass().getEnclosingMethod().getName() +
                        " Cursor has " +
                        Integer.toString(rowcount) +
                        " rows with " +
                        Integer.toString(columncount) + " columns."
        );
        csr.moveToPosition(-1);     //Ensure that all rows are retrieved <<< added 20171016
        while (csr.moveToNext()) {
            String unobtainable = "unobtainable!";
            String logstr = "Information for row " + Integer.toString(csr.getPosition() + 1) + " offset=" + Integer.toString(csr.getPosition());
            for (int i=0; i < columncount;i++) {
                logstr = logstr + "\n\tFor Column " + csr.getColumnName(i);
                switch (csr.getType(i)) {
                    case Cursor.FIELD_TYPE_NULL:
                        logstr = logstr + " Type is NULL";
                        break;
                    case Cursor.FIELD_TYPE_FLOAT:
                        logstr = logstr + "Type is FLOAT";
                        break;
                    case Cursor.FIELD_TYPE_INTEGER:
                        logstr = logstr + " Type is INTEGER";
                        break;
                    case Cursor.FIELD_TYPE_STRING:
                        logstr = logstr + " Type is STRING";
                        break;
                    case Cursor.FIELD_TYPE_BLOB:
                        logstr = logstr + " Type is BLOB";
                        break;
                }
                String strval_log = " value as String is ";
                String lngval_log = " value as long is ";
                String dblval_log = " value as double is ";
                String blbval_log = "";
                try {
                    strval_log = strval_log + csr.getString(i);
                    lngval_log = lngval_log + csr.getLong(i);
                    dblval_log = dblval_log +  csr.getDouble(i);
                } catch (Exception e) {
                    strval_log = strval_log + unobtainable;
                    lngval_log = lngval_log + unobtainable;
                    dblval_log = dblval_log + unobtainable;
                    try {
                        blbval_log = " value as blob is " +
                                getBytedata(csr.getBlob(i),24);
                    } catch (Exception e2) {
                        e2.printStackTrace();
                    }

                }
                logstr = logstr + strval_log + lngval_log + dblval_log + blbval_log;
            }
            Log.d(CSU_TAG,logstr);
        }
        csr.moveToPosition(csrpos); // restore cursor position <<< added 20171016
    }

    /**
     * Return a hex string of the given byte array
     * @param bytes     The byte array to be converted to a hexadecimal string
     * @param limit     the maximum number of bytes;
     *                  note returned string will be up to twice as long
     * @return          The byte array represented as a hexadecimal string
     */
    private static String getBytedata(byte[] bytes, int limit) {
        if (bytes.length < limit) {
            return convertBytesToHex(bytes);
        } else {
            byte[] subset = new byte[limit];
            System.arraycopy(bytes,0,subset,0,limit);
            return convertBytesToHex(subset);
        }
    }

    // HEX characters as a char array for use by convertBytesToHex
    private final static char[] hexarray = "0123456789ABCDEF".toCharArray();

    /**
     * Return a hexadecimal string representation of the passed byte array
     * @param bytes     The byte array to be represented.
     * @return          The string representing the byte array as hexadecimal
     */
    private static String convertBytesToHex(byte[] bytes) {
        char[] hexstr = new char[bytes.length * 2];
        for (int i=0; i < bytes.length; i++) {
            int h = bytes[i] & 0xFF;
            hexstr[i * 2] = hexarray[h >>> 4];
            hexstr[i * 2 + 1] = hexarray[h & 0xF];
        }
        return new String(hexstr);
    }
}

    要使用代码,只需创建 公共设施 类并复制上面的代码。

        2
  •  0
  •   MikeT    8 年前

    添加1-logDatabaseInfo

    这将把有关数据库的一些信息写入日志,例如:- 

    10-12 06:32:12.426 20575-20575/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/mjt.sqlitedbexamples/databases/eventsDB
10-12 06:32:12.426 20575-20575/? D/SQLITE_CSU: Database Version = 1
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table Name = events Created Using = CREATE TABLE events(_id INTEGER PRIMARY KEY, starts INTEGER DEFAULT (strftime('%s','now')),starts_timestamp INTEGER DEFAULT 0,ends INTEGER DEFAULT 0,ends_timestamp INTEGER DEFAULT 0,description TEXT, counter INTEGER DEFAULT 0,bytedata BLOB DEFAULT x'00000000')
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = _id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = starts ColumnType = INTEGER Default Value = strftime('%s','now') PRIMARY KEY SEQUENCE = 0
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = starts_timestamp ColumnType = INTEGER Default Value = 0 PRIMARY KEY SEQUENCE = 0
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = ends ColumnType = INTEGER Default Value = 0 PRIMARY KEY SEQUENCE = 0
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = ends_timestamp ColumnType = INTEGER Default Value = 0 PRIMARY KEY SEQUENCE = 0
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = description ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = counter ColumnType = INTEGER Default Value = 0 PRIMARY KEY SEQUENCE = 0
10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = bytedata ColumnType = BLOB Default Value = x'00000000' PRIMARY KEY SEQUENCE = 0

    以上是数据库名称 事件数据库 版本 是 ,有2个表 android\u元数据 (应用程序需要并自动生成)和 事件 (实际上还有3个表作为sqlite_master,是sqlite所必需的)。

    用于创建表的SQL也包含在每个列出的表中。

    对于每个表,列出了列,显示了列的 名称 ,它是 类型 ,它是 默认值 ,它是 序列 在 主要索引 (0=不在主索引中)。

    包括 用以下内容替换CommonSQLiteUtilities(或创建新)类(注意,根据原始答案,这包括所有其他实用程序):- 

    public class CommonSQLiteUtilities {

    public static final boolean ERROR_CHECKING_ON = true;
    public static final boolean ERROR_CHECKING_OFF = false;

    // SQLite MASTER TABLE definitions
    static final String SQLITE_MASTER = "sqlite_master";
    static final String SM_TABLE_TYPE_COLUMN = "type";
    static final String SM_NAME_COLUMN = "name";
    static final String SM_TABLENAME_COLUMN = "tbl_name";
    static final String SM_ROOTPAGE_COLUMN = "rootpage";
    static final String SM_SQL_COLUMN = "sql";
    static final String SM_TYPE_TABLE = "table";
    static final String SM_TYPE_INDEX = "index";

    static final String PRAGMA_STATEMENT = "PRAGMA ";
    static final String PRAGMA_DATABASELIST = "database_list";
    static final String PRAGMA_USERVERSION = "user_version";
    static final String PRAGMA_ENCODING = "encoding";
    static final String PRAGMA_FOREIGNKEYLIST = "foreign_key_list";
    static final String PRAGMA_INDEXINFO = "index_info";
    static final String PRAGMA_INDEXLIST = "index_list";
    static final String PRAGMA_TABLEINFO = "table_info";

    static final String PRAGMA_DBLIST_SEQ_COL = "seq";
    static final String PRAGMA_DBLIST_NAME_COL = "name";
    static final String PRAGMA_DBLIST_FILE_COL = "file";
    static final String PRAGMA_TABLEINFO_CID_COL = "cid";
    static final String PRAGMA_TABLEINFO_NAME_COl = "name";
    static final String PRAGMA_TABLEINFO_TYPE_COL = "type";
    static final String PRAGMA_TABLEINFO_NOTNULL_COL = "notnull";
    static final String PRAGMA_TABLEINFO_DEFAULTVALUE_COL = "dflt_value";
    static final String PRAGMA_TABLEINFO_PRIMARYKEY_COL = "pk";





    static final String CSU_TAG = "SQLITE_CSU";

    private CommonSQLiteUtilities() {}

    /**
     * Write Database information to the log;
     * Information wrttien is:
     * the database path, (will/should show connected databases)
     * the version number (note! user version i.e. version coded in DBHelper),
     * the tables in the database (includes android_metadata but not sqlite_master),
     * the columns of the tables
     * @param db    The SQLite database to be interrogated
     */
    public static void logDatabaseInfo(SQLiteDatabase db) {

        // Issue PRAGMA database_list commnand
        Cursor dblcsr = db.rawQuery(PRAGMA_STATEMENT + PRAGMA_DATABASELIST,null);
        // Write databases to the log
        while (dblcsr.moveToNext()) {
            Log.d(CSU_TAG,"DatabaseList Row " + Integer.toString(dblcsr.getPosition() + 1) +
                    " Name=" + dblcsr.getString(dblcsr.getColumnIndex(PRAGMA_DBLIST_NAME_COL)) +
                    " File=" + dblcsr.getString(dblcsr.getColumnIndex(PRAGMA_DBLIST_FILE_COL))
            );
        }
        dblcsr.close();
        // Issue PRAGMA user_version to get the version and write to the log
        //Note! to set user_version use execSQL not rawQuery
        Cursor uvcsr = db.rawQuery(PRAGMA_STATEMENT + PRAGMA_USERVERSION,null);
        while (uvcsr.moveToNext()) {
            Log.d(CSU_TAG,"Database Version = " + 
                    Integer.toString(uvcsr.getInt(uvcsr.getColumnIndex(PRAGMA_USERVERSION))));
        }
        uvcsr.close();
        // Select all table entry rows from sqlite_master
        Cursor tlcsr = db.rawQuery("SELECT * FROM " +
                SQLITE_MASTER + " WHERE " +
                SM_TABLE_TYPE_COLUMN + "='" + SM_TYPE_TABLE + "'"
                ,null);
        // For each table write table information to the log 
        // (inner loop gets column info per table)
        while (tlcsr.moveToNext()) {
            String current_table = tlcsr.getString(tlcsr.getColumnIndex(SM_TABLENAME_COLUMN));
            Log.d(CSU_TAG,
                    "Table Name = " + current_table +
                    " Created Using = " + tlcsr.getString(tlcsr.getColumnIndex(SM_SQL_COLUMN)),
                    null
            );
            // Issue PRAGMA tabel_info for the current table
            Cursor ticsr = db.rawQuery(PRAGMA_STATEMENT + PRAGMA_TABLEINFO +
                            "(" + current_table + ")",
                    null
            );
            // Write column info (see headings below) to the log
            while (ticsr.moveToNext()) {
                Log.d(CSU_TAG,"Table = " +
                        current_table +
                        " ColumnName = " +
                        ticsr.getString(ticsr.getColumnIndex(PRAGMA_TABLEINFO_NAME_COl)) +
                        " ColumnType = " +
                        ticsr.getString(ticsr.getColumnIndex(PRAGMA_TABLEINFO_TYPE_COL)) +
                        " Default Value = " +
                        ticsr.getString(ticsr.getColumnIndex(PRAGMA_TABLEINFO_DEFAULTVALUE_COL)) +
                        " PRIMARY KEY SEQUENCE = " + Integer.toString(
                                ticsr.getInt(ticsr.getColumnIndex(PRAGMA_TABLEINFO_PRIMARYKEY_COL))
                        )
                );
            }
            ticsr.close();
        }
        tlcsr.close();
    }

    /**
     * Generic get all rows from an SQlite table,
     * allowing the existence of the table to be checked and also
     * allowing the ROWID to be added AS a supplied string
     *
     * @param db                    The SQLiteDatabase
     * @param tablename             The name of the table from which the
     *                              returned cursor will be created from;
     *                              Note!
     * @param use_error_checking    Whether ot not to try to detect errors
     *                              currently just table doesn't exist,
     *                              true to turn on, false to turn off
     *                              ERROR_CHECKING_ON = true
     *                              ERROR_CHECKING_OFF = false
     * @param forceRowidAs          If length of string passed is 1 or greater
     *                              then a column, as an alias of ROWID, will be
     *                              added to the cursor
     * @return                      the extracted cursor, or in the case of the
     *                              underlying table not existing an empty cursor
     *                              with no columns
     */
    public static Cursor getAllRowsFromTable(SQLiteDatabase db,
                                      String tablename,
                                      boolean use_error_checking,
                                      String forceRowidAs) {
        String[] columns = null;

        // Tablename must be at least 1 character in length
        if (tablename.length() < 1) {
            Log.d(CSU_TAG,new Object(){}.getClass().getEnclosingMethod().getName() +
                    " is finishing as the provided tablename is less than 1 character in length"
            );
            return new MatrixCursor(new String[]{});
        }

        // If use_error_checking is true then check that the table exists
        // in the sqlite_master table
        if (use_error_checking) {
            Cursor chkcsr = db.query(SQLITE_MASTER,null,
                    SM_TABLE_TYPE_COLUMN + "=? AND "
                            + SM_TABLENAME_COLUMN + "=?",
                    new String[]{SM_TYPE_TABLE,tablename},
                    null,null,null
            );

            // Ooops table is not in the Database so return an empty
            // column-less cursor
            if (chkcsr.getCount() < 1) {
                Log.d(CSU_TAG,"Table " + tablename +
                        " was not located in the SQLite Database Master Table."
                );
                // return empty cursor with no columns
                return new MatrixCursor(new String[]{});

            }
            chkcsr.close();
        }

        // If forcing an alias of ROWID then user ROWID AS ???, *
        if(forceRowidAs != null && forceRowidAs.length() > 0) {
            columns = new String[]{"rowid AS " +forceRowidAs,"*"};
        }

        // Finally return the Cursor but trap any exceptions
        try {
            return db.query(tablename, columns, null, null, null, null, null);
        } catch (Exception e) {
            Log.d(CSU_TAG,"Exception encountered but trapped when querying table " + tablename +
                    " Message was: \n" + e.getMessage());
            Log.d(CSU_TAG,"Stacktrace was:");
            e.printStackTrace();
            return new MatrixCursor(new String[]{});
        }
    }

    /**
     * Create and return a Cursor devoid of any rows and columns
     * Not used, prehaps of very little use.
     * @param db    The Sqlite database in which the cursor is to be created
     * @return      The empty Cursor
     */
    private static Cursor getEmptyColumnLessCursor(SQLiteDatabase db) {
        return new MatrixCursor(new String[]{});
    }

    /**
     * Write column names in the passed Cursor to the log
     * @param csr   The Cursor to be inspected.
     */
    public static void logCursorColumns(Cursor csr) {
        Log.d(CSU_TAG,
                new Object(){}.getClass().getEnclosingMethod().getName() +
                        " invoked. Cursor has the following " +
                        Integer.toString(csr.getColumnCount())+
                        " columns.");
        int position = 0;
        for (String column: csr.getColumnNames()) {
            position++;
            Log.d(CSU_TAG,"Column Name " +
                    Integer.toString(position) +
                    " is "
                    + column
            );
        }
    }

    /**
     * Write the contents of the Cursor to the log
     * @param csr   The Cursor that is to be displayed in the log
     */
    public static void logCursorData(Cursor csr) {
        int columncount = csr.getColumnCount();
        int rowcount = csr.getCount();
        int csrpos = csr.getPosition(); //<<< added 20171016
        Log.d(CSU_TAG,
                new Object(){}.getClass().getEnclosingMethod().getName() +
                        " Cursor has " +
                        Integer.toString(rowcount) +
                        " rows with " +
                        Integer.toString(columncount) + " columns."
        );
        csr.moveToPosition(-1);     //Ensure that all rows are retrieved <<< added 20171016
        while (csr.moveToNext()) {
            String unobtainable = "unobtainable!";
            String logstr = "Information for row " + Integer.toString(csr.getPosition() + 1) + " offset=" + Integer.toString(csr.getPosition());
            for (int i=0; i < columncount;i++) {
                logstr = logstr + "\n\tFor Column " + csr.getColumnName(i);
                switch (csr.getType(i)) {
                    case Cursor.FIELD_TYPE_NULL:
                        logstr = logstr + " Type is NULL";
                        break;
                    case Cursor.FIELD_TYPE_FLOAT:
                        logstr = logstr + "Type is FLOAT";
                        break;
                    case Cursor.FIELD_TYPE_INTEGER:
                        logstr = logstr + " Type is INTEGER";
                        break;
                    case Cursor.FIELD_TYPE_STRING:
                        logstr = logstr + " Type is STRING";
                        break;
                    case Cursor.FIELD_TYPE_BLOB:
                        logstr = logstr + " Type is BLOB";
                        break;
                }
                String strval_log = " value as String is ";
                String lngval_log = " value as long is ";
                String dblval_log = " value as double is ";
                String blbval_log = "";
                try {
                    strval_log = strval_log + csr.getString(i);
                    lngval_log = lngval_log + csr.getLong(i);
                    dblval_log = dblval_log +  csr.getDouble(i);
                } catch (Exception e) {
                    strval_log = strval_log + unobtainable;
                    lngval_log = lngval_log + unobtainable;
                    dblval_log = dblval_log + unobtainable;
                    try {
                        blbval_log = " value as blob is " +
                                getBytedata(csr.getBlob(i),24);
                    } catch (Exception e2) {
                        e2.printStackTrace();
                    }

                }
                logstr = logstr + strval_log + lngval_log + dblval_log + blbval_log;
            }
            Log.d(CSU_TAG,logstr);
        }
        csr.moveToPosition(csrpos); // restore cursor position <<< added 20171016
    }

    /**
     * Return a hex string of the given byte array
     * @param bytes     The byte array to be converted to a hexadecimal string
     * @param limit     the maximum number of bytes;
     *                  note returned string will be up to twice as long
     * @return          The byte array represented as a hexadecimal string
     */
    private static String getBytedata(byte[] bytes, int limit) {
        if (bytes.length < limit) {
            return convertBytesToHex(bytes);
        } else {
            byte[] subset = new byte[limit];
            System.arraycopy(bytes,0,subset,0,limit);
            return convertBytesToHex(subset);
        }
    }

    // HEX characters as a char array for use by convertBytesToHex
    private final static char[] hexarray = "0123456789ABCDEF".toCharArray();

    /**
     * Return a hexadecimal string representation of the passed byte array
     * @param bytes     The byte array to be represented.
     * @return          The string representing the byte array as hexadecimal
     */
    private static String convertBytesToHex(byte[] bytes) {
        char[] hexstr = new char[bytes.length * 2];
        for (int i=0; i < bytes.length; i++) {
            int h = bytes[i] & 0xFF;
            hexstr[i * 2] = hexarray[h >>> 4];
            hexstr[i * 2 + 1] = hexarray[h & 0xF];
        }
        return new String(hexstr);
    }
}
    推荐文章
    oiyio  ·  SQLiteException无法将数据库从版本3降级为1虽然我删除了我的应用程序,但仍发生错误
    8 年前
    Hadis  ·  Android-JobScheduler的setPeriodic无法工作
    8 年前
    Aviumaa  ·  我的应用程序在创建Sqlite DB时崩溃[关闭]
    8 年前
    Bryan  ·  使用房间实现搜索
    8 年前
    jaghz123  ·  从SQLite数据库检索特定列
    8 年前
    user1846348  ·  Android SQLite:如何使用从其他表检索的id从表中检索数据
    8 年前
    user2872856  ·  rawQuery()精确匹配
    8 年前
    Tanmay Singal  ·  无法在Android中查询数据库
    8 年前
    DaveNOTDavid  ·  如何更新房间数据库中实体内的某些列?
    8 年前
    George Krastev  ·  SQLite查询未按特定顺序执行
    8 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号