android SQLITE代码

文件：MAINACTIVITY.JAVA

-----------------------------------------------------------------------------------------------------------------------------------------

public class MainActivity extends Activity {

Button button1;
Button button2;
Button button3;
Button button4;
Button button5;


DatabaseHelper mOpenHelper;


private static final String DATABASE_NAME = "dbTest.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";//表里面的字段-->内容




@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
initLayout();
mOpenHelper = new DatabaseHelper(this);//获得数据库辅助类的


}


private void initLayout() {
button1 = (Button) findViewById(R.id.button1);
button1.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
CreateTable();
}
});


button2 = (Button) findViewById(R.id.button2);
button2.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
dropTable();
}
});


button3 = (Button) findViewById(R.id.button3);
button3.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
insertItem();
}
});
button4 = (Button) findViewById(R.id.button4);
button4.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
deleteItem();
}
});


button5 = (Button) findViewById(R.id.button5);
button5.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
showItems();
}
});


}




/*
* 重新建立数据表
*/
private 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("数据表重建错误");
}
}


/*
* 删除数据表
*/
private void dropTable() {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String sql = "drop table " + TABLE_NAME;
Log.i("dropTable:", sql);
try {
db.execSQL(sql);
setTitle("数据表成功删除：" + sql);
db.close();
} catch (SQLException e) {
setTitle("数据表删除错误");
}
}


/*
* 插入两条数据
*/
private void insertItem() {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();//得到一个可写数据库
String sql1 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
+ ") values('first', 'android的发展真是迅速啊');";
String sql2 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
+ ") values('secend', 'android的发展真快啊');";
try {
Log.i("first:sql1=", sql1);
Log.i("secend:sql2=", sql2);
db.execSQL(sql1);
db.execSQL(sql2);
setTitle("插入两条数据成功");
db.close();
} catch (SQLException e) {
setTitle("插入两条数据失败");
}
}


/*
* 删除其中的一条数据
*/
private void deleteItem() {
try {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
//db.delete(TABLE_NAME, " title = 'first'", null);
String sql = "delete from " + TABLE_NAME + " where title = 'first'";
db.execSQL(sql);
setTitle("成功删除title为first的一条记录");
db.close();
} catch (SQLException e) {

}


}


/*
* 在屏幕的title区域显示当前数据表当中的数据的条数。
*/
private void showItems() {


SQLiteDatabase db = mOpenHelper.getReadableDatabase();
String col[] = { TITLE, BODY };//查询字段
Cursor cur = db.query(TABLE_NAME, col, null, null, null, null, null);
Integer num = cur.getCount();
setTitle(Integer.toString(num) + " 条记录");
db.close();
}

}

-----------------------------------------------------------------------------------------------------

文件:DatabaseHelper.JAVA

//数据库操作必须创建类继承sqliteopenhelper数据库辅助类，然后覆写出2个抽象方法
public class DatabaseHelper extends SQLiteOpenHelper{
private static final String DATABASE_NAME = "dbtest.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";

//创建数据库的操作写在构造方法里面，构建方法带一个参数context

public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null,DATABASE_VERSION );
// TODO Auto-generated constructor stub
}


@Override
public void onCreate(SQLiteDatabase db) {//创建新的数据表
// TODO Auto-generated method stub
String sql = "CREATE TABLE " + TABLE_NAME + "("+TITLE+" text no null,"+BODY+" text no null"
+");";
Log.i("create db:", sql);
db.execSQL(sql);

}


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {//数据库升级调用的方法，一般不写
// TODO Auto-generated method stub

}


}