android中sqlite数据库的使用

用一个Demo程序完成sqlite数据库的增删查改。

创建数据库和表

android中使用SQLiteDatabase需要先实现SQLiteOpenHelper类:

public class MyDatabaseHelper extends SQLiteOpenHelper {    public static final String CREATE_BOOK = "create table " + Constant.TABEL_NAME + " ("            + Constant.ID + " integer primary key autoincrement, "            + Constant.BOOK_NAME + " text, "            + Constant.AUTHOR + " text, "            + Constant.PAGES + " integer, "            + Constant.PRICE + " real)";    private Context mContext;    public MyDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {        super(context, name, factory, version);        mContext = context;    }    @Override    public void onCreate(SQLiteDatabase sqLiteDatabase) {        Log.i(getClass().getSimpleName(), "onCreate called...");        sqLiteDatabase.execSQL(CREATE_BOOK);    }    @Override    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {    }}

onCreate只在数据库被创建时调用，可以在里面创建我们想要的数据库；sqLiteDatabase.execSQL就是使用SQL语法执行数据库操作，这里是创建一个表，列名称为id,name,author,pages和price。每列都有自己的数据类型。
对于sqlite的操作，我们可以通过adb shell查看数据库的内容:
进入data/data/包名/databases目录，执行:

sqlite3 database

进入sqlite命令模式，便可以使用SQL语言对数据库进行操作：

select * from tablename;

就是查看数据库内容，因为还没有往表里添加内容，所以是空的。

新增数据

用SQL语句添加数据：

SQLiteDatabase sqLiteDatabase = myDatabaseHelper.getWritableDatabase();                sqLiteDatabase.execSQL("insert into book(name, author, pages, price)" +                        " values('xiyouji','wuchengen',400, 40.0)");                sqLiteDatabase.execSQL("insert into book(name, author, pages, price)" +                        " values('sanguoyanyi','luoguanzhong',500, 50.0)" );                sqLiteDatabase.execSQL("insert into book(name, author, pages, price)" +                        " values('hongloumeng','caoxueqin',600, 60.0)" );                sqLiteDatabase.execSQL("insert into book(name, author, pages, price)" +                        " values('shuihuzhuan','shinaian',700, 70.0)" );                sqLiteDatabase.close();

上面对book表添加了四组数据，再通过adb看看，为了方便查看，使用.mode column使table列对齐；.head on打开列名称显示，再使用select * from book查看内容：

数据查询

接下来再使用rawQuery对数据进行查询：

String[] args;Cursor cursor = null;args = new String[]{"50.0", "60.0"};                cursor = sqLiteDatabase.rawQuery("select * from book where price >= ? and price <= ?", args);                showDBInfo(cursor);                args = new String[]{"shinaian", "caoxueqin"};                cursor = sqLiteDatabase.rawQuery("select * from book where author = ? or author = ?", args);                showDBInfo(cursor);                cursor.close();

得到cursor，然后打印查到的数据：

    private void showDBInfo(Cursor cursor) {        if (cursor.moveToFirst()) {            do {                // 遍历Cursor对象，取出数据并打印                String name = cursor.getString(cursor.getColumnIndex(Constant.BOOK_NAME));                String author = cursor.getString(cursor.getColumnIndex(Constant.AUTHOR));                int pages = cursor.getInt(cursor.getColumnIndex(Constant.PAGES));                double price = cursor.getDouble(cursor.getColumnIndex(Constant.PRICE));                Log.i(getClass().getSimpleName(), "book name is " + name);                Log.i(getClass().getSimpleName(), "book author is " + author);                Log.i(getClass().getSimpleName(), "book pages is " + pages);                Log.i(getClass().getSimpleName(), "book price is " + price);            }            while (cursor.moveToNext());        }    }

相对于其它操作，数据库最复杂的就是查询功能，sqlitedatabase提供的接口：

public Cursor rawQuery(String sql, String[] selectionArgs) {    }

第一个参数就收sql语句，第二个是查询参数，我是看了这里的语法将查找条件定为价格在50.0到60.0之间的书。

第二次是查询作者为shinaian或caoxueqin的书，运行结果：

修改

将xiyouji价格改为1000，将作者为shinaian的书价格改为2000：

sqLiteDatabase.execSQL("update book set price = '1000' where name = 'xiyouji'");                sqLiteDatabase.execSQL("update book set price = '2000' where author = 'shinaian'");                sqLiteDatabase.close();

结果：

删除

删除caoxueqin的书：

sqLiteDatabase.execSQL("delete from book where author = 'caoxueqin'");                sqLiteDatabase.close();

sqlite不需要专门花时间学习，用的时候查查语法就差不多了。

直接用SQLite语句操作数据库

进入sqlite命令行模式后可以直接用SQLite语句操作数据库进行增删查改。增加、删除、修改命令就和代码里execSQL的参数一样；查询命令就把rawQuery第二个参数填入第一个中。：

主要代码

public class SQLiteActivity extends AppCompatActivity implements View.OnClickListener{    private Button creatBT;    private Button insertBT;    private Button queryBT;    private Button updateBT;    private Button deleteBT;    private MyDatabaseHelper myDatabaseHelper;    private SQLiteDatabase sqLiteDatabase;    private static final String DB_NAME = "MyDB.db";    private static final String TABEL_NAME = "book";    private List<Book> bookList;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_sqlite);        myDatabaseHelper = new MyDatabaseHelper(getApplicationContext(), DB_NAME, null, 1);        creatBT = (Button) findViewById(R.id.creatBT);        insertBT = (Button) findViewById(R.id.insertBT);        queryBT = (Button) findViewById(R.id.queryBT);        updateBT = (Button) findViewById(R.id.updateBT);        deleteBT = (Button) findViewById(R.id.deleteBT);        creatBT.setOnClickListener(this);        insertBT.setOnClickListener(this);        queryBT.setOnClickListener(this);        updateBT.setOnClickListener(this);        deleteBT.setOnClickListener(this);        bookList = new ArrayList<>();        Book book = new Book("xiyouji", "wuchengen", 500, 45.5);        bookList.add(book);        book = new Book("sanguoyanyi", "luoguanzhong", 400, 60.5);        bookList.add(book);        book = new Book("shuihuzhuan", "shinaian", 600, 66.3);        bookList.add(book);        book = new Book("hongloumeng", "caoxueqin", 700, 77.5);        bookList.add(book);    }    @Override    public void onClick(View view) {        String[] args;        Cursor cursor = null;        switch (view.getId()){            case R.id.creatBT:                Log.i(getClass().getSimpleName(), "creatBT clicked");                sqLiteDatabase = myDatabaseHelper.getWritableDatabase();                break;            case R.id.insertBT:                Log.i(getClass().getSimpleName(), "insertBT clicked");//                ContentValues cv = new ContentValues();//                cv.put(Constant.AUTHOR, "auther1");//                cv.put(Constant.PRICE, 100.5);//                cv.put(Constant.PAGES, 200);//                cv.put(Constant.BOOK_NAME, "name1");//                if (sqLiteDatabase == null){//                    sqLiteDatabase = myDatabaseHelper.getWritableDatabase();//                }//                long res = sqLiteDatabase.insert(TABEL_NAME, null, cv);//                Log.i(getClass().getSimpleName(), "sqLiteDatabase.insert res:" + res);                if (sqLiteDatabase == null){                    sqLiteDatabase = myDatabaseHelper.getWritableDatabase();                }                sqLiteDatabase.execSQL("insert into book(name, author, pages, price)" +                        " values('xiyouji','wuchengen',400, 40.0)");                sqLiteDatabase.execSQL("insert into book(name, author, pages, price)" +                        " values('sanguoyanyi','luoguanzhong',500, 50.0)" );                sqLiteDatabase.execSQL("insert into book(name, author, pages, price)" +                        " values('hongloumeng','caoxueqin',600, 60.0)" );                sqLiteDatabase.execSQL("insert into book(name, author, pages, price)" +                        " values('shuihuzhuan','shinaian',700, 70.0)" );//                sqLiteDatabase.close();                break;            case R.id.queryBT:                if (sqLiteDatabase == null){                    sqLiteDatabase = myDatabaseHelper.getWritableDatabase();                }//                String[] columns = new String[]{Constant.AUTHOR, Constant.BOOK_NAME};//                Cursor cursor = sqLiteDatabase.query(Constant.TABEL_NAME, columns, null, null, null, null, null);//                showDBInfo2(cursor);//                Cursor cursor = sqLiteDatabase.query(Constant.TABEL_NAME, null, null, null, null, null, null);//                showDBInfo(cursor);                args = new String[]{"50.0", "60.0"};                cursor = sqLiteDatabase.rawQuery("select * from book where price >= ? and price <= ?", args);                showDBInfo(cursor);                args = new String[]{"shinaian", "caoxueqin"};                cursor = sqLiteDatabase.rawQuery("select * from book where author = ? or author = ?", args);                showDBInfo(cursor);                cursor.close();                break;            case R.id.updateBT:                if (sqLiteDatabase == null){                    sqLiteDatabase = myDatabaseHelper.getWritableDatabase();                }                sqLiteDatabase.execSQL("update book set price = '1000' where name = 'xiyouji'");                sqLiteDatabase.execSQL("update book set price = '2000' where author = 'shinaian'");//                sqLiteDatabase.close();                break;            case R.id.deleteBT:                if (sqLiteDatabase == null){                    sqLiteDatabase = myDatabaseHelper.getWritableDatabase();                }                sqLiteDatabase.execSQL("delete from book where author = 'caoxueqin'");//                sqLiteDatabase.close();            default:                break;        }    }    private void showDBInfo(Cursor cursor) {        if (cursor.moveToFirst()) {            do {                // 遍历Cursor对象，取出数据并打印                String name = cursor.getString(cursor.getColumnIndex(Constant.BOOK_NAME));                String author = cursor.getString(cursor.getColumnIndex(Constant.AUTHOR));                int pages = cursor.getInt(cursor.getColumnIndex(Constant.PAGES));                double price = cursor.getDouble(cursor.getColumnIndex(Constant.PRICE));                Log.i(getClass().getSimpleName(), "book name is " + name);                Log.i(getClass().getSimpleName(), "book author is " + author);                Log.i(getClass().getSimpleName(), "book pages is " + pages);                Log.i(getClass().getSimpleName(), "book price is " + price);            }            while (cursor.moveToNext());        }    }    private void showDBInfo2(Cursor cursor){        if (cursor.moveToFirst()){            do {                // 遍历Cursor对象，取出数据并打印                String name = cursor.getString(cursor.getColumnIndex(Constant.BOOK_NAME));                String author = cursor.getString(cursor.getColumnIndex(Constant.AUTHOR));                Log.i(getClass().getSimpleName(), "book name is " + name);                Log.i(getClass().getSimpleName(), "book author is " + author);            }            while (cursor.moveToNext());        }    }}