用一个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());
}
}
}