SQLite使用方法

方法总结
[table]
|[color=olive][b]方法[/b][/color]|[color=olive][b]说明[/b][/color]
|getReadableDatabase()|SQLiteOpenHelper方法

[color=orange]Create and/or open a database.[/color]
|getWritableDatabase()|SQLiteOpenHelper方法

[color=orange]Create and/or open a database that will be used for reading and writing.[/color]
|onCreate(SQLiteDatabase db)|SQLiteOpenHelper方法

[color=orange]Called when the database is created for the first time.[/color]
|onOpen(SQLiteDatabase db)|SQLiteOpenHelper方法

[color=orange]Called when the database has been opened.[/color]
|onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)|SQLiteOpenHelper方法

[color=red]如果SQLiteOpenHelper创建时候输入的版本号和上一个不一致，将调用该方法
例如(类SQLiteActivity.java)[/color]

DatabaseHelper d = new DatabaseHelper(SQLiteActivity.this, "test_mars_db",2);
SQLiteDatabase s = d.getReadableDatabase();

[color=red]如果上个版本号是1，而这次是2，执行该语句的时候，将会触发onUpgrade方法。DatabaseHelper类是自建类，继承SQLiteOpenHelper类[/color]
[color=orange]Called when the database needs to be upgraded.[/color]
|close()|SQLiteOpenHelper方法
[color=orange]Close any open database object.[/color]
[/table]


例子：
Strings.xml(/res/values)

<?xml version="1.0" encoding="utf-8"?>
<resources>
     <string name="hello">Hello World, SQLiteActivity!</string>
     <string name="app_name">SQLiteActivity</string>
     <string name="createTable">create table</string>
     <string name="upgrade">upgrade table</string>
     <string name="insert">insert</string>
     <string name="update">update</string>
     <string name="query">query</string>
</resources>

Main.xml

<?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"
    >
<TextView  
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    android:text="@string/hello"
    />
<Button android:id="@+id/button1" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="@string/createTable"></Button>
<Button android:id="@+id/button2" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="@string/upgrade"></Button>
<Button android:id="@+id/button3" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="@string/insert"></Button>
<Button android:id="@+id/button4" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="@string/update"></Button>
<Button android:id="@+id/button5" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="@string/query"></Button>
</LinearLayout>

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {
	//DatabaseHelper作为一个助手类，提供两个功能
	//第一，getReadableDatabase()和getWritableDatabase()可以得到SQLiteDatabase
	//      如果第一次创建数据库，会调用SQLiteOpenHelper的onCreate方法
	//第二，提供onCreate()和onUpgrade()两个回调函数，允许创建和升级数据库
	private static final int VERSON = 1;
	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}
	public DatabaseHelper(Context context, String name) {
		this(context, name, VERSON);
	}
	public DatabaseHelper(Context context, String name, int version) {
		this(context, name, null, version);
	}
	//该函数在第一次创建数据库时执行，实际第一次得到SQLiteDatabase对象时执行
	@Override
	public void onCreate(SQLiteDatabase db) {
		System.out.println("create a Database");
		db.execSQL("create table user(id integer PRIMARY KEY autoincrement,name varchar(20))");
	}
	@Override
	 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		System.out.println("update a database");
	 }
}

SQLiteActivity.java

public class SQLiteActivity extends Activity {
    /** Called when the activity is first created. */
      @Override
      public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        //该将创建库和表
        Button b1 = (Button)findViewById(R.id.button1);
        b1.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				DatabaseHelper d = new DatabaseHelper(SQLiteActivity.this, "test_mars_db");
				SQLiteDatabase s = d.getReadableDatabase();
			}
		});
        //该按钮测试版本不同触发的方法upgrade
        Button b2 = (Button)findViewById(R.id.button2);
        b2.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				DatabaseHelper d = new DatabaseHelper(SQLiteActivity.this, "test_mars_db",2);
				SQLiteDatabase s = d.getReadableDatabase();
			}
        });
        //该按钮做表数据插入操作
        Button b3 = (Button)findViewById(R.id.button3);
        b3.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				ContentValues cv = new ContentValues();
				cv.put("id", 1);
				cv.put("name", "zhangsan");
				DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this, "test_mars_db");
				SQLiteDatabase s = dbHelper.getWritableDatabase();
				s.insert("user", null, cv);
			}
        });
        //该按钮做表数据更新操作
        Button b4 = (Button)findViewById(R.id.button4);
        b4.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				ContentValues cv = new ContentValues();
				cv.put("id", 1);
				cv.put("name", "zhangsanfeng");
				DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this, "test_mars_db");
				SQLiteDatabase s = dbHelper.getWritableDatabase();
				s.update("user", cv, "id=?", new String[]{"1"});
			}
        });
        //该按钮做表数据查询操作
        Button b5 = (Button)findViewById(R.id.button5);
        b5.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this, "test_mars_db");
				SQLiteDatabase s = dbHelper.getWritableDatabase();
				Cursor c = s.query("user", new String[]{"id","name"}, "id=?", new String[]{"1"}, null, null, null);
				while(c.moveToNext()){
					String name = c.getString(c.getColumnIndex("name"));
					System.out.println("query-->"+name);
				}
			}
        });
      }
}

[b]删除表语句!![/b]

db.execSQL("DROP TABLE IF EXISTS "+FOOD_TABLE_NAME);