java - Android中的SQLite比较表

Question

有什么方法可以比较表（当然具有相同的属性），如果它们相同则返回 true，否则返回 false？谢谢。

标签： javaandroidsqlite

Answer 1

不是直接的，但它真的很容易。

即 sqlite 存储用于定义表 sqlite_master（模式）中的表的 SQL。

对于 SQL 与 SQL 的最简单比较（对于列定义），您可以使用以下 SQL：-

WITH tablenames(table1,table2) AS (SELECT 'table1' /*<<<<< change accordingly */,'table2' /*<<<<< change accordingly*/)
SELECT 
    COALESCE(
        (SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table1 FROM tablenames)) =
        (SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table2 FROM tablenames))
    ,0)
;

在 Android 中，这可以使用（在数据库助手中）来完成：-

public boolean compareTable(String table1, String table2) {
    SQLiteDatabase db = this.getWritableDatabase();
    String result_column = "result";
    boolean rv = false;
    Cursor csr = db.rawQuery("WITH tablenames(table1,table2) AS (SELECT ? ,? )" +
            "SELECT " +
            "COALESCE(" +
            "(SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table1 FROM tablenames)) =" +
            "(SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table2 FROM tablenames))" +
            ",0) " +
            "AS " + result_column +
            ";",new String[]{table1,table2});
    if (csr.moveToFirst()) {
        rv = (csr.getInt(csr.getColumnIndex(result_column)) > 0);
    }
    csr.close();
    return rv;
}

• 请注意，这会将 SQL 中的最细微差别视为比较失败

例子

数据库助手DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;

    public static final String TABLE = "htmlstore";
    public static final String IDCOLUMN = BaseColumns._ID;
    public static final String HTMLCOLUMN = "html";

    public static final String TABLE2 = "otherstore";
    public static final String TABLE3 = "storeother";

    SQLiteDatabase db;

    public DatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        db = this.getWritableDatabase();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crt_htmlstore_table = "CREATE TABLE IF NOT EXISTS " + TABLE + "(" +
                IDCOLUMN + " INTEGER PRIMARY KEY," +
                HTMLCOLUMN + " TEXT" +
                ")";
        db.execSQL(crt_htmlstore_table);
        String crt_otherstore_table = "CREATE TABLE IF NOT EXISTS " + TABLE2 + "(" +
                IDCOLUMN + " INTEGER PRIMARY KEY," +
                HTMLCOLUMN + " TEXT" +
                ")";
        db.execSQL(crt_otherstore_table);
        String crt_storeother_table = "CREATE TABLE IF NOT EXISTS " + TABLE3 + "(" +
                HTMLCOLUMN + " TEXT, " +
                IDCOLUMN + " INTEGER, " +
                " PRIMARY KEY(" + IDCOLUMN + ")" +
                ")";
        db.execSQL(crt_storeother_table);
    }


    public long insert(String html) {
        ContentValues cv = new ContentValues();
        cv.put(HTMLCOLUMN,html);
        return db.insert(TABLE,null,cv);
    }

    public String getHTML(long id) {
        String rv = "";
        Cursor csr = db.query(TABLE,new String[]{HTMLCOLUMN},IDCOLUMN+"=?",new String[]{String.valueOf(id)},null,null,null);
        if (csr.moveToFirst()) {
            rv = csr.getString(csr.getColumnIndex(HTMLCOLUMN));
        }
        csr.close();
        return rv;
    }

    public boolean compareTable(String table1, String table2) {
        SQLiteDatabase db = this.getWritableDatabase();
        String result_column = "result";
        boolean rv = false;
        Cursor csr = db.rawQuery("WITH tablenames(table1,table2) AS (SELECT ? ,? )" +
                "SELECT " +
                "COALESCE(" +
                "(SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table1 FROM tablenames)) =" +
                "(SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table2 FROM tablenames))" +
                ",0) " +
                "AS " + result_column +
                ";",new String[]{table1,table2});
        if (csr.moveToFirst()) {
            rv = (csr.getInt(csr.getColumnIndex(result_column)) > 0);
        }
        csr.close();
        return rv;
    }

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

    }
}

• 请注意，实际上这 3 个表都是相同的（但第 3 个表在用于生成列定义的 SQL 方面不同，因此将被视为不同）

• COALESCE 用于将 null（sqlite_master 中没有此类表）转换为 0（假）

调用代码MainActivity.java

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        DatabaseHelper databaseHelper = new DatabaseHelper(this);

        Log.d("TABLECOMPARE"," Result = " + String.valueOf(databaseHelper.compareTable(DatabaseHelper.TABLE,DatabaseHelper.TABLE2)));
        Log.d("TABLECOMPARE"," Result = " + String.valueOf(databaseHelper.compareTable(DatabaseHelper.TABLE2,DatabaseHelper.TABLE3)));
        Log.d("TABLECOMPARE"," Result = " + String.valueOf(databaseHelper.compareTable(DatabaseHelper.TABLE,DatabaseHelper.TABLE3)));
        Log.d("TABLECOMPARE"," Result = " + String.valueOf(databaseHelper.compareTable(DatabaseHelper.TABLE,DatabaseHelper.TABLE)));
    }
}

结果 ：-

2020-01-14 07:23:29.624 D/TABLECOMPARE:  Result = true
2020-01-14 07:23:29.625 D/TABLECOMPARE:  Result = false
2020-01-14 07:23:29.625 D/TABLECOMPARE:  Result = false
2020-01-14 07:23:29.626 D/TABLECOMPARE:  Result = true

IE

• 表 1 和表 2 被认为是相同的
• 表2和表3被认为是不同的
• 表 1 和表 3 被认为是不同的
• 表 1 被认为与表 1 相同。