SQLite是android提供的一个关系型数据库。今天要总结的主要内容就是它。
在使用SQLite的时候用到的抽象类是
SQLiteOpenHelper
public SQLiteDatabase getReadableDatabase ()
Create and/or open a database. This will be the same object returned by getWritableDatabase()
unless some problem, such as a full disk, requires the database to be opened read-only. In that case, a read-only database object will be returned. If the problem is fixed, a future call togetWritableDatabase()
may succeed, in which case the read-only database object will be closed and the read/write object will be returned in the future.
Like getWritableDatabase()
, this method may take a long time to return, so you should not call it from the application main thread, including fromContentProvider.onCreate()
.
Returns
- a database object valid until
getWritableDatabase()
orclose()
is called.
Throws
SQLiteException | if the database cannot be opened |
---|
package sqlite;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper{
public DatabaseHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase arg0) {
// TODO Auto-generated method stub
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
注意在其中必须添加一个构造函数DatabaseHelper,其中有四个参数Context指的就是他的Activity参数,第二个参数指的是表的名字,第三个参数一般传的是空值,最后一个指的是当前的数据库的版本。数据库的版本是正数而且必须是递增的。
private static final int VERSION = 1;
public DatabaseHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
public DatabaseHelper(Context context,String name,int version){
this(context,name,null,version);
}
public DatabaseHelper(Context context,String name){
this(context,name,VERSION);
}
其中第二个构造函数是调用第一个构造函数,他自身含有三个参数
@Override
public void onCreate(SQLiteDatabase arg0) {
// TODO Auto-generated method stub
System.out.println("create a Database");
arg0.execSQL("create table user(id int , name varchar(20))");
}
我们在其中添加一句打印输出的语句和一个execSQL函数,它是用于执行SQL语句
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
>
<TextView
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/hello_world"
/>
<Button
android:id="@+id/createDatabase"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="createDatabase"
/>
<Button
android:id="@+id/updateDatabase"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="updateDatabase"
/>
<Button
android:id="@+id/insert"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="insert"/>
<Button
android:id="@+id/update"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="update"/>
<Button
android:id="@+id/query"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="query"/>
</LinearLayout>
下面我们来详细分析一下他们是如何实现的:
createButton = (Button)findViewById(R.id.createDatabase);
updateButton = (Button)findViewById(R.id.updateDatabase);
insertButton = (Button)findViewById(R.id.insert);
updateRecordButton = (Button)findViewById(R.id.update);
queryButton = (Button)findViewById(R.id.query);
createButton.setOnClickListener(new CreateListener());
updateButton.setOnClickListener(new UpdateListener());
insertButton.setOnClickListener(new InsertListener());
updateRecordButton.setOnClickListener(new UpdateRecordListener());
queryButton.setOnClickListener(new QueryListener());
为每一个按钮实现一个监听器:
class CreateListener implements OnClickListener{
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
DatabaseHelper dbHelper =new DatabaseHelper(MainActivity.this,"test_db");
SQLiteDatabase db =dbHelper.getReadableDatabase();
}
}
上面实现的是建立一个数据库的功能,这里我们引入了刚才建立的DatabaseHelper这一个类,其中MainActivity是该类的名字,数据库的名字我们叫做test_db;这里我们调用刚才DatabaseHelper两个参数的构造函数
之后使用SQLiteDatabase 读取数据库
class UpdateListener implements OnClickListener{
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
DatabaseHelper dbHelper =new DatabaseHelper(MainActivity.this,"test_db",2);
SQLiteDatabase db =dbHelper.getReadableDatabase();
}
}
和上面的那个操作差不多,唯一不同的是调用的构造函数不同
class InsertListener implements OnClickListener{
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
ContentValues values =new ContentValues();
values.put("id", 1);
values.put("name", "LiHua");
DatabaseHelper dbHelper= new DatabaseHelper(MainActivity.this,"test_db",2);
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.insert("user", null, values);
}
}
这里我们使用到了ContentValues函数,这个在数据库当中是很常用的函数他用来对数据库的成员进行赋值(或许说的不准确)是键值对,前面是关键字,后面对应的是输入的值
class UpdateRecordListener implements OnClickListener{
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
ContentValues values =new ContentValues();
values.put("name", "zhangsan");
DatabaseHelper dbHelper= new DatabaseHelper(MainActivity.this,"test_db",2);
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.update("user", values, "id=?", new String[]{"1"});
}
}
思路仍然是一样的:
class QueryListener implements OnClickListener{
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this,"test_db");
SQLiteDatabase db = dbHelper.getReadableDatabase();
//相当于查询语句 user是表格的名字
Cursor cursor = db.query("user", new String[]{"id","name"}, "id=?", new String[]{"1"}, null, null, null);
while(cursor.moveToNext()){
String name = cursor.getString(cursor.getColumnIndex("name"));
System.out.println("query--->" + name);
}
}
}