Android学习笔记（七）——SQLite数据库的使用

命令行操作SQLite：SQLite位于/data/data中

首先adb shell进行shell模式中；cd data/data/,cd gap.sqlite, cd databases; sqlite3 test_db进入test_db数据库操作模式；
.help
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
.load FILE ?ENTRY?     Load an extension library
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds
.timer ON|OFF          Turn the CPU timer measurement on or off
.width NUM NUM ...     Set column widths for "column" mode 

 对于使用SQLite数据库的说明，数据库非常的小，但是基本上保全了数据的基本功能， 但是SQLite不适合存储大量的数据，因为存储大量的数据会出现问题
 
 SQLiteOpenHelper是一个抽象类，用于管理数据的创建、升级操作，使用时要定义一个类继承于此类 ，并覆盖其中的OnCreate、OnUpdate这几个回调函数

 注意getReadableDatabase和getWritableDatabase这两方法的使用
 getReadableDatabase：如果数据库没有创建则创建数据库并调用回调函数Oncreate，如果已经创建，则调用回调函数Onupdate
 
 SQLiteDatabase类用于对表的数据进行查询、更新和删除操作，通过SQLiteOpenHelper类的子类的对象获得SQLiteDateBase类

Activity类：
public class SqliteActivity extends Activity {
	private Button createDb;
	private Button updateDb;
	private Button insert;
	private Button update;
	private Button query;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        //使用五个按钮分别去创建、更新数据库，插入数据、更新数据以及查询数据
        createDb = (Button)findViewById(R.id.create);
        updateDb = (Button)findViewById(R.id.updatedb);
        insert = (Button)findViewById(R.id.insert);
        update = (Button)findViewById(R.id.update);
        query = (Button)findViewById(R.id.query);
        
        //当点击创建按钮时候创建一个数据库
        createDb.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				DateBaseHelper dbHelper = new DateBaseHelper(SqliteActivity.this, "test_db");
				//创建或者打开一个数据库，在这里是创建一个数据库，并且执行回调函数onCreate
				dbHelper.getReadableDatabase();
			}
		});
        
        //更新数据库
        updateDb.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				DateBaseHelper dbHelper = new DateBaseHelper(SqliteActivity.this, "test_db", 2);
				//打开数据库
				dbHelper.getReadableDatabase();
			}
		});
        
        //插入操作
        insert.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				//ContentValues用于储存一些键值对
				ContentValues content = new ContentValues();
				content.put("id", 1);
				content.put("name", "zhangsan");
				DateBaseHelper dbHelper = new DateBaseHelper(SqliteActivity.this, "test_db");
				//以可写方式打开一个数据库
				SQLiteDatabase db = dbHelper.getWritableDatabase();
				//向数据库中插入一条数据
				db.insert("user", null, content);
			}
		});

        //更新操作
        update.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				ContentValues values = new ContentValues();
				values.put("name", "zhangsanfeng");
				DateBaseHelper dbHelper = new DateBaseHelper(SqliteActivity.this, "test_db");
				//SQLiteDatabase has methods to create, delete, execute SQL commands
				SQLiteDatabase db = dbHelper.getWritableDatabase();
				db.update("user", values, "id=?", new String[]{"1"});
			}
        });
        
        //查询操作
        query.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				DateBaseHelper dbHelper = new DateBaseHelper(SqliteActivity.this, "test_db");
				SQLiteDatabase db = dbHelper.getReadableDatabase();
				//定义一个游标来保存数据，游标的移动来取得数据
				Cursor cursor = db.query("user", new String[] {"id", "name"}, "id=?", new String[]{"1"}, null, null, null);
				while (cursor.moveToNext()) {
					System.out.println("name--->" + cursor.getString(cursor.getColumnIndex("name")));
				}
			}
		});
    }
}

 

DateBaseHelper类：
public class DateBaseHelper extends SQLiteOpenHelper {
	private static int VERSION = 1;

	public DateBaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}
	
	public DateBaseHelper(Context context, String name,
			int version) {
		this(context, name, null, version);
		// TODO Auto-generated constructor stub
	}
	
	public DateBaseHelper(Context context, String name) {
		this(context, name, VERSION);
		// TODO Auto-generated constructor stub
	}

	/**
	 * 当数据库被创建的时候，调用这个回调函数，可以在这里创建表
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		System.out.println("create a db");
		db.execSQL("create table user(id int,name varchar(20))");
	}

	/**
	 * 当数据库被更新的时候，调用这个回调函数，可以在这里添加表格，删除表格
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		// TODO Auto-generated method stub
		System.out.println("update a db");
	}

}

main.xml

<Button android:id="@+id/create"
			android:layout_width="fill_parent"
			android:layout_height="wrap_content"
			android:text="create db"/>
<Button android:id="@+id/updatedb"
			android:layout_width="fill_parent"
			android:layout_height="wrap_content"
			android:text="update db"/>
<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"/>