Android：数据库SQLite基本操作（增删改查）

数据库SQLite

public class MySQLite extends SQLiteOpenHelper {
    private final String create_book = "create table book " + "(id integer primary key autoincrement, "
            + "price real, name text, " + "author text, pages integer)";
    private final String create_category = "create table category " + "(id integer primary key autoincrement, "
            + "category_name text, category_code integer)";

    Context ctx;

    public MySQLite(Context context, String name, CursorFactory factory, int version) {
        super(context, name, factory, version);
        ctx = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(create_book);
        db.execSQL(create_category);
        Toast.makeText(ctx, "Create DB", Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        switch (oldVersion) {
        case 1:
            db.execSQL(create_category);
        case 2:
            db.execSQL("alter table book add column category_code integer");
        default:
            break;
        }
    }
}

增删改查操作：

public class MainActivity extends Activity {
    SQLiteDatabase db;

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

        Button btn = (Button) findViewById(R.id.btn);
        db = new MySQLite(MainActivity.this, "test", null, 2).getWritableDatabase();

        btn.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                // db = new MySQLite(MainActivity.this, "test", null, 2).getWritableDatabase();
            }
        });

        // 增
        Button add_btn = (Button) findViewById(R.id.add_btn);
        add_btn.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                ContentValues values = new ContentValues();
                values.put("price", 10.00);
                values.put("name", "from A to Z");
                values.put("author", "lwt");
                values.put("pages", 300);
                db.insert("book", null, values);

                values.clear();
                values.put("price", 60.00);
                values.put("name", "麦琪的礼物");
                values.put("author", "欧亨利");
                values.put("pages", 255);
                db.insert("book", null, values);
            }
        });

        // 改
        Button edit_btn = (Button) findViewById(R.id.edit_btn);
        edit_btn.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                ContentValues values = new ContentValues();
                values.put("price", 23.00);
                db.update("book", values, "name=?", new String[] { "麦琪的礼物" });
            }
        });

        // 删
        Button del_btn = (Button) findViewById(R.id.del_btn);
        del_btn.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                db.delete("book", "id in (? , ?)", new String[] { "1", "2" });
            }
        });

        // 查
        Button sel_btn = (Button) findViewById(R.id.sel_btn);
        sel_btn.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                Cursor cursor = db.query("book", null, null, null, null, null, null);
                if (cursor.moveToFirst()) {
                    do {
                        String name = cursor.getString(cursor.getColumnIndex("name"));
                        String author = cursor.getString(cursor.getColumnIndex("author"));
                        Integer pages = cursor.getInt(cursor.getColumnIndex("pages"));
                        Double price = cursor.getDouble(cursor.getColumnIndex("price"));

                        Log.d("debug",
                                String.format("name: %s, author: %s, pages: %d, price:%f", name, author, pages, price));
                    } while (cursor.moveToNext());
                }
                cursor.close();
            }
        });
    }

}

或直接用语句：

db.execSQL("insert into book (name, author, price, pages) values (?, ?, ?, ?)", 
        new String[]{"name", "author", "10.11", "234"});
db.execSQL("delete from book where name = ?", new String[]{"lwt"});
db.execSQL("update book set price = ? where name = ?", new String[]{"99.99", "lwt"});
db.rawQuery("select * from book", null);

用adb命令行查看表：

adb shell #进入shell
cd data/data/com.example.testsqlite/databases #进入数据库所在目录
sqlite3 test #进入数据库
.table   #显示表
.schame  #显示建表语句
.exit    #退出