SQLite是一种轻量级的关系型数据库,它的运算速度非常的快,占用资源很少,特别适合在移动设备上使用
创建数据库
下面我们创建一个名为Book和Category的数据库
建立MydatabaseHelper类继承自SQLiteOpenHelper类代码如下:
public class MyDatabaseHelper extends SQLiteOpenHelper {
public static final String CREATE_BOOK="create table Book (" +
"id integer primary key autoincrement," +
"author text," +
"price real," +
"pages integer," +
"name text)";
public static final String CREATE_CATEGORY="create table Category(id integer primary key autoincrement," +
"category_name text," +
"category_code integer)";
private Context mContext;
public MyDatabaseHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
mContext=context;
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(CREATE_BOOK);
db.execSQL(CREATE_CATEGORY);
Toast.makeText(mContext, "Create succeeded", Toast.LENGTH_LONG).show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("drop table if exists Book");
db.execSQL("drop table if exists Category");
onCreate(db);
}
}
SQLiteOpenHelper中有二个非常重要的实例方法 ,getReadableDatabase()和getWritableDatabase()。这二个方法都可以创建或打开一个现有的数据库文件,并返回一个可对数据库进行读写操作的对象。不同的是当数据库不可写入的时候(如磁盘空间已满)getReadableDatabase()方法返回的对象将以只读的方式打开数据库,而getWritableDatabase()方法将出现异常。
SQLiteOpenHelper中二个抽象方法,分别是onCreate()和onUpgrade(),我们必须在自己的帮助类中重写这二个方法,然后分别在这二个方法中去实现创建,升级数据库的逻辑。
SQLiteOpenHelper中有二个构造方法可供重写,一般使用参数较少的那个构造方法,这个方法接收四个参数,第一个参数是Context,必须有它才可以对数据库进行操作。第二个参数是数据库名,创建数据库时使用的就是这里指定的名称。第三个参数允许我们在查询数据的时候返回一个自定义的Cursor,一般传入null。第四个参数表示当前的数据库版本号,用于数据库的升级升级操作。
调用getReadableDatabase()或getWritableDatabase()方法就能够创建数据库了。
数据库创建成功后数据库文件存放在/data/dat/<package name>/databases/目录下。
创建activity_main.xml布局
代码如下所示:
<Button
android:id="@+id/create_database"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="create database"
/>
<Button
android:id="@+id/addButton"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:text="Add data"
/>
<Button
android:id="@+id/UpdateButton"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:text="Update data"
/>
<Button
android:id="@+id/DeletButton"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:text="Delete data"
/>
<Button
android:id="@+id/QueryButton"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:text="Query data"
/>
在MainActivity中实现对数据库的增,删,改,查功能
代码如下:
public class MainActivity extends Activity {
private MyDatabaseHelper dbHelper;
private Button createDatabase;
private Button adddata;
private Button updatedata;
private Button deletedata;
private Button querydata;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper = new MyDatabaseHelper(this, "BookStore.db", null, 2);
createDatabase=(Button)findViewById(R.id.create_database);
adddata=(Button)findViewById(R.id.addButton);
updatedata=(Button)findViewById(R.id.UpdateButton);
deletedata=(Button)findViewById(R.id.DeletButton);
querydata=(Button)findViewById(R.id.QueryButton);
createDatabase.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
dbHelper.getWritableDatabase();
}
});
adddata.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("name", "The Da Vinci Code");
values.put("author", "Dan Brown");
values.put("pages", 454);
values.put("price", 16.96);
db.insert("Book", null, values);
values.put("name", "The Lost Symbol");
values.put("author", "Dan Brown");
values.put("pages", 510);
values.put("price", 19.95);
db.insert("Book", null, values);
}
});
updatedata.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("price", 10.99);
db.update("Book", values, "name = ?", new String[] {"The Da Vinci Code"});
}
});
deletedata.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
String whereClause="id = ?";
String[] whereArgs={"11"};
SQLiteDatabase db=dbHelper.getWritableDatabase();
//db.delete("Book", "id = ?", new String[] {"11"});
db.delete("Book", whereClause, whereArgs);
}
});
querydata.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
SQLiteDatabase db =dbHelper.getWritableDatabase();
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"));
int pages = cursor.getInt(cursor.getColumnIndex("pages"));
double price = cursor.getDouble(cursor.getColumnIndex("price"));
Log.d("MainActivity", "book name is "+name);
Log.d("MainActivity", "book author is "+author);
Log.d("MainActivity", "book pages is "+pages);
Log.d("MainActivity", "book price is "+price);
}while(cursor.moveToNext());
}
cursor.close();
}
});
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}
通过以上代码就可以对数据库有一定的了解了。