SQLite-Android平台对数据库的简单操作

package com.lovose.SQLite;

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.TextView;

public class SOLiteTest extends Activity {
OnClickListener listener1 = null;
OnClickListener listener2 = null;
OnClickListener listener3 = null;
OnClickListener listener4 = null;
OnClickListener listener5 = null;

Button button01;
Button button02;
Button button03;
Button button04;
Button button05;
TextView msg;

private static final String DATABASE_NAME = "DbForSQLiteTest.db";
private static final int DATABASE_VERSION = 1;
private static final String TABLE_NAME = "diary";
private static final String TITLE = "title";
private static final String BODY = "body";

DatebaseHelper mOpenHelper;

private static class DatebaseHelper extends SQLiteOpenHelper{
DatebaseHelper(Context context){
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
String sql = "CREATE TABLE " + TABLE_NAME + " (" + TITLE
+ " text not null, " + BODY + " text not null " + ");";
Log.i("haiyang:createDB=", sql);
db.execSQL(sql);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}

@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
prepareListener();
initLayout();
mOpenHelper = new DatebaseHelper(this);
}

private void initLayout() {
// TODO Auto-generated method stub
button01 = (Button)findViewById(R.id.Button01);
button01.setOnClickListener(listener1);

button02 = (Button)findViewById(R.id.Button02);
button02.setOnClickListener(listener2);

button03 = (Button)findViewById(R.id.Button03);
button03.setOnClickListener(listener3);

button04 = (Button)findViewById(R.id.Button04);
button04.setOnClickListener(listener4);

button05 = (Button)findViewById(R.id.Button05);
button05.setOnClickListener(listener5);

msg = (TextView)findViewById(R.id.TextView01);
}

private void prepareListener() {
// TODO Auto-generated method stub
listener1 = new OnClickListener(){
public void onClick(View v) {
// TODO Auto-generated method stub
createTable();
}
};
listener2 = new OnClickListener(){
public void onClick(View v) {
// TODO Auto-generated method stub
dropTable();
}
};
listener3 = new OnClickListener(){
public void onClick(View v){
insertItem();
}
};
listener4 = new OnClickListener(){
public void onClick(View v){
deleteItem();
}
};
listener5 = new OnClickListener(){
public void onClick(View v){
showItems();
}
};
}

//建立数据表
public void createTable() {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String SQL = "CREATE TABLE "+TABLE_NAME+ " ( " + TITLE
+ " text not null, " + BODY + " text not null " + ");";
Log.i("haiyang:createDB=", SQL);
try{
db.execSQL("DROP TABLE IF EXISTS diary");
db.execSQL(SQL);
setTitle("数据表成功重建");
} catch (SQLException e) {
setTitle("数据表重建错误");
}
}
//删除数据表
public void dropTable(){
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String SQL = "drop table "+TABLE_NAME;
try {
db.execSQL(SQL);
setTitle("数据表成功删除：" + SQL);
} catch (SQLException e) {
setTitle("数据表删除错误");
}
}
//插入记录
public void insertItem() {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String sql1 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
+ ") values('haiyang', 'I test android');";
String sql2 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
+ ") values('icesky', 'I test android too');";
String sql3 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
+ ") values('wongxming', 'I test android three times');";
try {
db.execSQL(sql1);
db.execSQL(sql2);
db.execSQL(sql3);
setTitle("插入三条数据成功");
} catch (SQLException e) {
setTitle("插入三条数据失败");
}
}
//删除一条记录
public void deleteItem(){
try {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
db.delete(TABLE_NAME, " title = 'haiyang'", null);
setTitle("删除title为haiyang的一条记录");
} catch (SQLException e) {

}
}
//在屏幕的textView区域显示当前数据表当中的数据的条数。
public void showItems(){
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String col[] = { TITLE, BODY };
Cursor cur = db.query(TABLE_NAME, col, null, null, null, null, null);

Integer num = cur.getCount();
setTitle(Integer.toString(num) + " 条记录");
StringBuilder sb = new StringBuilder();
sb.append("\tTitle : body\n");
cur.moveToFirst();
do{
sb.append(cur.getString(0)+" : "+cur.getString(1)+"\n");
}while(cur.moveToNext());

msg.setText(sb.toString());
}
}