① 安卓系统完全支持SQLite数据库,创建的数据库在App内都是对于所有类都是可见的,App外则不可见。官方推荐的创建SQLite数据库的方式是通过继承SQLiteOpenHelper 类,重写onCreate()方法,然后就可以创建数据库的表了。
② SQLiteOpenHelper类:
SQLiteOpenHelper类是一个创建数据库和管理数据库版本的帮助类,使用时继承这个类并重写onCreat(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int) 两个方法还可选择性地重写onOpen(SQLiteDatabase)。如果数据库存在,则通过此类打开数据库,否则创建一个数据库,高阶一点可以进行事务管理。
构造函数:
SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)
第二个参数是数据库的名称,第四个参数是版本号。
仅返回一个帮助类的对象,而数据库只有在getWritableDatabase()或者getReadableDatabase()被调用时才会被创建或者打开。
方法:
void onCreate (SQLiteDatabase db)
当第一次创建数据库时被调用
void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)
当升级数据库时被调用
SQLiteDatabase getReadableDatabase ()
创建或者打开一个数据库,得到一个有效的数据库对象,直到getWritableDatabase()或者close()被调用,与构造函数相呼应。
SQLiteDatabase getWritableDatabase ()
创建或者打开一个数据库,得到一个有效的数据库对象,直到close()被调用,与构造函数相呼应。
注意一句话:Like getWritableDatabase(), this method may take a long time to return, so you should not call it from the application main thread, including from ContentProvider.onCreate().
③ SQLiteDatabase类是一个真正管理数据库的类,数据库的名称必须唯一。
常用的增删改查方法:
long insert (String table, String nullColumnHack, ContentValues values)
第一个参数是表名,第二个一般置为null就好了,第三个参数类似于map键值对,键是列名,值是列的值。
int delete (String table, String whereClause, String[] whereArgs)
第二个参数是一个占位符,第三个参数是第二个参数的实际值。
int update(String table, ContentValues values, String whereClause, String[] whereArgs)
类似上面
Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
返回指是一个游标对象
void execSQL (String sql)
Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE.
DatabaseHelper类:
package com.example.sqlitedemo;
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 {
private static final String DBNAME = "people.db"; //数据库的名,用于构造函数
private static final int VERSION = 1; //版本号
public DatabaseHelper(Context context) {
super(context, DBNAME, null, VERSION);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
// SQL语句
String sql = "create table people(id integer,name varchar(64))"; //SQL语句,创建数据库执行
db.execSQL(sql);
System.out.println("--> Database onCreate");
}
@Override //系统发现数据库版本升级时即执行此方法
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
// TODO Auto-generated method stub
}
}
主Activity:
package com.example.sqlitedemo;
import android.support.v4.widget.SimpleCursorAdapter.ViewBinder;
import android.support.v7.app.ActionBarActivity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
public class MainActivity extends ActionBarActivity {
private Button button1, button2, button3, button4, button5;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
button1 = (Button) findViewById(R.id.btn1); //create database
button2 = (Button) findViewById(R.id.btn2); //insert
button3 = (Button) findViewById(R.id.btn3); //delete
button4 = (Button) findViewById(R.id.btn4); //update
button5 = (Button) findViewById(R.id.btn5); //query
button1.setOnClickListener(new BtnListener1());
button2.setOnClickListener(new BtnListener2());
button3.setOnClickListener(new BtnListener3());
button4.setOnClickListener(new BtnListener4());
button5.setOnClickListener(new BtnListener5());
}
private class BtnListener1 implements View.OnClickListener {
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
DatabaseHelper helper = new DatabaseHelper(getApplicationContext());
helper.getWritableDatabase(); //必须使用getWrite和getRead方法才能获得有效的数据库对象
}
}
private class BtnListener2 implements View.OnClickListener { //为数据库增加两行
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
DatabaseHelper helper = new DatabaseHelper(getApplicationContext());
SQLiteDatabase database = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("id", 1);
values.put("name", "leelit1");
database.insert("people", null, values);
values.put("id", 2);
values.put("name", "leelit2");
database.insert("people", null, values);
System.out.println("--> insert two items");
}
}
private class BtnListener3 implements View.OnClickListener { //删除第2行
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
DatabaseHelper helper = new DatabaseHelper(getApplicationContext());
SQLiteDatabase database = helper.getWritableDatabase();
database.delete("people", "id=?", new String[] { "2" });
}
}
private class BtnListener4 implements View.OnClickListener { //将第2行的name改为lee2
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
DatabaseHelper helper = new DatabaseHelper(getApplicationContext());
SQLiteDatabase database = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", "lee2");
database.update("people", values, "id = ?", new String[] { "2" });
}
}
private class BtnListener5 implements View.OnClickListener { //查询并打印name字段的所有数据
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
DatabaseHelper helper = new DatabaseHelper(getApplicationContext());
SQLiteDatabase database = helper.getReadableDatabase();
Cursor cursor = database.query("people", null, null, null, null,
null, null);
while (cursor.moveToNext()) {
String name = cursor.getString(cursor.getColumnIndex("name"));
System.out.println("--> " + name);
}
}
}
}
结果:
小结:继承SQLiteOpenHelper帮助类,通过它获得SQLiteDababase有效对象然后进行管理。注意一句话:Like getWritableDatabase(), this method may take a long time to return, so you should not call it from the application main thread, including from ContentProvider.onCreate()。