1、使用Sqlite的关键是创建一类，这个类继承SQLiteOpenHelper，继承的类必须重写三个方法：构造方法，onCreate方法，onUpgrate方法
2、SQLiteDatabase是直接操作数据库的对象，可以使用SQLiteOpenHelper的getReadableDatabase或getWritableDatabase方法得到SQLiteDatabase对象。具体使用这两个方法得到的SQLiteDatabase对象的不同点，根据方法名就可以看出。一个是得到可读的，一个是得到可写的。
实例代码：
1、布局文件
<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent"> <Button android:id="@+id/createDatabase" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="Create Database"></Button> <Button android:id="@+id/upgrateDatabase" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="Upgrate Database"></Button> <Button android:id="@+id/insert" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="Insert"></Button> <Button android:id="@+id/update" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="Update"></Button> <Button android:id="@+id/query" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="Query"></Button> <Button android:id="@+id/delete" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="Delete"></Button></LinearLayout>
2、自定义继承SQLiteOpenHelper的类，重写三个方法
package yyl.db;
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); }
//该函数在第一次创建数据库的时候执行 @Override public void onCreate(SQLiteDatabase db) { System.out.println("Creaet a database test_user and table user"); db.execSQL("create table user (id int,name varchar(20))");
}
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { System.out.println("upgrate a database");
}
}
3、数据库操作代码
package yyl.sqlite;
import yyl.db.DatabaseHelper;import android.app.Activity;import android.content.ContentValues;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.view.View;import android.widget.Button;
public class SqliteActivity extends Activity { // 定义变量 private Button createDatabase = null; private Button upgrateDatabase = null; private Button insert = null; private Button update = null; private Button query = null; private Button delete = null;
@Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main);
// 根据ID得到控件对象 createDatabase = (Button) findViewById(R.id.createDatabase); upgrateDatabase = (Button) findViewById(R.id.upgrateDatabase); insert = (Button) findViewById(R.id.insert); update = (Button) findViewById(R.id.update); query = (Button) findViewById(R.id.query); delete = (Button) findViewById(R.id.delete);
// 给按钮绑定单击事件监听器 createDatabase.setOnClickListener(new View.OnClickListener() {
@Override public void onClick(View v) { //生成DatabaseHelper对象 DatabaseHelper databaseHelper = new DatabaseHelper(SqliteActivity.this,"sqlite_user",null,1); //只有调用了getReadableDatabase方法，或者getWritableDatabase方法，才会创建数据库 databaseHelper.getReadableDatabase(); } }); upgrateDatabase.setOnClickListener(new View.OnClickListener() {
@Override public void onClick(View v) { //生成DatabaseHelper对象 DatabaseHelper databaseHelper = new DatabaseHelper(SqliteActivity.this,"sqlite_user",null,2); //只有调用了getReadableDatabase方法，或者getWritableDatabase方法，才会更新数据库 databaseHelper.getReadableDatabase();
} }); insert.setOnClickListener(new View.OnClickListener() {
@Override public void onClick(View v) { //创建ContentValue对象 ContentValues values = new ContentValues(); //插入键值对，注意值的类型必须和数据库表中字段的类型一致 values.put("id", 1); values.put("name", "yangyulin"); //生成SQLiteDatabase对象 DatabaseHelper dbHelper = new DatabaseHelper(SqliteActivity.this,"test_user",null,1); SQLiteDatabase db = dbHelper.getWritableDatabase(); //执行插入 db.insert("user", null, values); } }); update.setOnClickListener(new View.OnClickListener() {
@Override public void onClick(View v) { //生成DatabaseHelper对象 DatabaseHelper dbHelper = new DatabaseHelper(SqliteActivity.this,"test_user",null,1); //调用DatabaseHelper的getWritableDatabase方法，生成SQLiteDatabase对象 SQLiteDatabase db = dbHelper.getWritableDatabase(); //生成ContentValues对象，并设定键值对 ContentValues values = new ContentValues(); values.put("name", "newSky"); //更新表信息:update user set name = 'newSky' where id =1; db.update("user", values, " id=?", new String[]{"1"});
} }); query.setOnClickListener(new View.OnClickListener() {
@Override public void onClick(View v) { //生成DatabaseHelper对象 DatabaseHelper dbHelper = new DatabaseHelper(SqliteActivity.this,"test_user",null,1); //调用DatabaseHelper的getReadableDatabase方法，生成SQLiteDatabase对象 SQLiteDatabase db = dbHelper.getReadableDatabase(); //执行查询，将查询结果放入到Cursor中 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("name --> " + name); } } }); delete.setOnClickListener(new View.OnClickListener() {
@Override public void onClick(View v) { //生成DatabaseHelper对象 DatabaseHelper dbHelper = new DatabaseHelper(SqliteActivity.this,"test_user",null,1); //调用DatabaseHelper的getReadableDatabase方法，生成SQLiteDatabase对象 SQLiteDatabase db = dbHelper.getWritableDatabase(); db.delete("user", "id =?", new String[]{"1"}); } });
}}
查看数据库：
1、在CMD命令行下输入以下命令
>adb shelll
#>cd data/data/yyl.sqlite
#>cd databases
#>sqlite3 test_user
#>.schema
#>select * from user;
