Android Sqlite初体验

在android中封装好了，sqlite数据库。想要创建一个数据库很简单，只需要实现一个类继承SQLiteOpenHelper.

下面是一个小例子，代码里面有注释：

MyDBHelper.java

package com.example.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDBHelper extends SQLiteOpenHelper {

	public MyDBHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		System.out.println("--------数据库创建操作----");
		db.execSQL("create table user(id integer primary key autoincrement,name varchar(20),age integer)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		System.out.println("--------数据库更新升级操作-----");
		if(oldVersion < 2 && newVersion ==2){
			//版本升级，新增user表nickname字段，新增user1表
			db.execSQL("alter table user add nickname varchar(20)");
			db.execSQL("create table user1(id integer primary key autoincrement,name varchar(20),age integer)");
			System.out.println("--------数据库升级到2.0版本");
		}
		if(oldVersion < 3  && newVersion ==3){
			//版本升级，新增user1表nickname字段，新增user1表
			db.execSQL("alter table user1 add nickname varchar(20)");
			System.out.println("--------数据库升级到3.0版本");
		}
		
	}

}

MainActivty.java

package com.example.sqlite;

import android.app.Activity;
import android.app.ActionBar;
import android.app.Fragment;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.ViewGroup;
import android.widget.Toast;
import android.os.Build;

public class MainActivity extends Activity {
	MyDBHelper helper;
	int index;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
	}

	public void create(View view) {
		//得到helper对象，此操作不会创建数据库
		helper = new MyDBHelper(this, "test.db", null, 1);
		System.out.println("-------------得到helper对象，此操作不会创建数据库------");
		//获取可读或可写SQLiteDatabase对象，此操作才会创建数据库，第一次调用才会创建，以后调用不会创建
		 SQLiteDatabase data = helper.getReadableDatabase();
	}
	
	public void create2(View view) {
		helper = new MyDBHelper(this, "test.db", null, 2);
		helper.getReadableDatabase();
	}
	
	public void create3(View view) {
		helper = new MyDBHelper(this, "test.db", null, 3);
		helper.getReadableDatabase();
	}

	public void insert(View view) {
		//得到一个可写的database对象
		SQLiteDatabase database = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", "张三"+index);
		values.put("age", 24+index);
		database.insert("user", null, values);
		database.close();
		index++;
	}

	public void query(View view) {
		SQLiteDatabase database = helper.getReadableDatabase();
		Cursor cursor = database.query("user", null, null, null, null, null, null);
//		Cursor cursor  = database.rawQuery("select * from user", null); //等价于上面写法
		while (cursor.moveToNext()) {//遍历游标，调用一次moveToNext，游标向下滚动一次
			String name = cursor.getString(cursor.getColumnIndex("name"));//得到string类型的列
			Integer age = cursor.getInt(cursor.getColumnIndex("age"));//得到int类型的列
			System.out.println("name:"+name+"--age:"+age);
		}
		cursor.close();
		database.close();
	}
	
	public void update(View view) {
		SQLiteDatabase database = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", "李四");
		int i = database.update("user", values, "name = ?", new String[]{"张三0"});
		System.out.println("--------更新数据，更新了"+i);
		database.close();
	}
	public void delete(View view) {
		SQLiteDatabase database = helper.getWritableDatabase();
		int i = database.delete("user", "name = ?", new String[]{"张三0"});
		System.out.println("--------删除数据，删除了"+i);
		database.close();
	}
}

xml:

<FrameLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/container"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context="com.example.sqlite.MainActivity"
    tools:ignore="MergeRootFrame" >

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:orientation="vertical" >

        <Button
            android:id="@+id/button1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="create"
            android:text="创建数据库" />

        <Button
            android:id="@+id/button1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="create2"
            android:text="创建数据库版本2" />

        <Button
            android:id="@+id/button1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="create3"
            android:text="创建数据库版本3" />

        <Button
            android:id="@+id/button2"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="insert"
            android:text="新增数据" />

        <Button
            android:id="@+id/button3"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="query"
            android:text="查询数据" />
        <Button
            android:id="@+id/button3"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="update"
            android:text="更新数据" />
        <Button
            android:id="@+id/button3"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="delete"
            android:text="删除数据" />
    </LinearLayout>

</FrameLayout>