通过使用SQLiteOpenHelper来操作android内置的一个很小的关系型数据库(SQLite)。
1)效果图
2)关键代码如下
1. 创建class MyDataBase.java代码如下
package com.example.androidsqlitedemo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class MyDataBase extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "demo_db";//数据库的名字
private static final int DATABASEVERSION = 1;//版本号
private static final String TABLE_NAME = "demo_table";//表名
public static final String FIELD_id = "_id";//必须为_id
public static final String FIELD_TEXT = "demo_text";
private SQLiteDatabase db;//数据库
private static final String TAG = "MyDataBase";
public MyDataBase(Context context) {
super(context, DATABASE_NAME, null, DATABASEVERSION);
// TODO Auto-generated constructor stub
db = this.getWritableDatabase();// 打开或新建数据库(第一次时创建)获得SQLiteDatabase对象,为了读取和写入数据
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
/* 建立表table */
Log.i(TAG, " onCreate() ");
String sql = "CREATE TABLE " + TABLE_NAME + " (" + FIELD_id
+ " INTEGER PRIMARY KEY AUTOINCREMENT," + " " + FIELD_TEXT
+ " TEXT)";
db.execSQL(sql);
}
//更新数据库
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
Log.i(TAG, " onUpgrade() ");
//删除表的SQL
String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
db.execSQL(sql);
onCreate(db);
}
//关闭数据库
@Override
public synchronized void close() {
// TODO Auto-generated method stub
Log.i(TAG, " close() ");
db.close();
super.close();
}
@Override
public void onOpen(SQLiteDatabase db1) {
// TODO Auto-generated method stub
Log.i(TAG, " onOpen() ");
super.onOpen(db1);
}
// 增加数据
public long addMethod(String str) {
/* 将新增的值放入ContentValues */
ContentValues cv = new ContentValues();
cv.put(FIELD_TEXT, str);//键值对
long row = db.insert(TABLE_NAME, null, cv);
Log.i(TAG, "addMethod row=" + row);
return row;
}
// 删除
public void deleteMethod(int id) {
String[] whereArgs = { Integer.toString(id) };
int rowsaffected = db.delete(TABLE_NAME, FIELD_id + "=?", whereArgs);
Log.i(TAG, "deleteMethod() rowsaffected=" + rowsaffected);
}
// 修改
public void modMethod(int id, String str) {
ContentValues values = new ContentValues();
values.put(FIELD_TEXT, str);
String[] whereArgs = { Integer.toString(id) };
int rowsaffected = db.update(TABLE_NAME, values, FIELD_id + " = ?",
whereArgs);
Log.i(TAG, "modMethod() rowsaffected=" + rowsaffected);
}
// 查询所有的数据 ,返回Cursor对象
public Cursor query() {
//asc是升序desc为降序(默认为asc)
return db.query(TABLE_NAME, null, null, null, null, null, FIELD_id + " ASC");
}
}
2.在res\layout下创建list.xml代码如下
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent" >
<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="TextView"
android:textSize="30sp"/>
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="TextView"
android:textSize="30sp"/>
</LinearLayout>
3.修改activity_main.xml代码如下
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<EditText
android:id="@+id/editText1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="text" >
<requestFocus />
</EditText>
<ListView
android:id="@+id/listView1"
android:layout_width="match_parent"
android:layout_height="wrap_content" >
</ListView>
</LinearLayout>
4.修改MainActivity.java代码如下
package com.example.androidsqlitedemo;
import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteCursor;
import android.support.v4.widget.SimpleCursorAdapter;
import android.view.KeyEvent;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.EditText;
import android.widget.ListView;
public class MainActivity extends Activity {
private final static int MENU_ADD=0;
private final static int MENU_EDIT=1;
private final static int MENU_DEL=2;
private EditText editText1=null;
private ListView listView1=null;
private MyDataBase mDataBase=null;
private SimpleCursorAdapter adapter=null;
private Cursor mCursor=null;
private int _id;
@SuppressWarnings("deprecation")
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
editText1=(EditText)findViewById(R.id.editText1);
listView1=(ListView)findViewById(R.id.listView1);
listView1.setOnItemClickListener(clickListener);
listView1.setOnItemSelectedListener(selectedListener);
/*创建SQLiteOpenHelper对象引用*/
mDataBase=new MyDataBase(this);
/*获得DataBase里的数据*/
mCursor=mDataBase.query();
/*new SimpleCursorAdapter并将mCursor传入,
显示数据字段为_id,todo_text*/
adapter=new SimpleCursorAdapter(
this,
R.layout.list,
mCursor,
new String[]{MyDataBase.FIELD_id,MyDataBase.FIELD_TEXT},
new int[]{R.id.textView1,R.id.textView2});
listView1.setAdapter(adapter);
}
@Override
/*添加Menu*/
public boolean onCreateOptionsMenu(Menu menu) {
menu.add(0, MENU_ADD, 0, "添加");
menu.add(0, MENU_EDIT, 1, "修改");
menu.add(0, MENU_DEL, 2, "删除");
return super.onCreateOptionsMenu(menu);
}
/*ListView的单击事件处理*/
OnItemClickListener clickListener=new OnItemClickListener() {
public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3){
mCursor.moveToPosition(arg2);
_id=mCursor.getInt(mCursor.getColumnIndex(MyDataBase.FIELD_id));
editText1.setText(mCursor.getString(mCursor.getColumnIndex(MyDataBase.FIELD_TEXT)));
}
};
/*监听处理鼠标滚轮的事件处理*/
OnItemSelectedListener selectedListener=new OnItemSelectedListener() {
public void onItemSelected(AdapterView<?> arg0, View arg1, int arg2, long arg3){
_id=mCursor.getInt(mCursor.getColumnIndex(MyDataBase.FIELD_id));
SQLiteCursor c=(SQLiteCursor)arg0.getSelectedItem();
editText1.setText(c.getString(c.getColumnIndex(MyDataBase.FIELD_TEXT)));
}
@Override
public void onNothingSelected(AdapterView<?> arg0) {
// TODO Auto-generated method stub
}
};
@Override
/*给Menu添加事件*/
public boolean onOptionsItemSelected(MenuItem item){
super.onOptionsItemSelected(item);
switch (item.getItemId()) {
case MENU_ADD://添加
addToDo();
break;
case MENU_DEL://删除
deleteToDo();
break;
case MENU_EDIT://修改
editToDo();
break;
default:
break;
}
return true;
}
/*添加*/
private void addToDo(){
String str=editText1.getText().toString();
if(str.equalsIgnoreCase("")){
return;
}
mDataBase.addMethod(str);
myUpDataShow();
}
/*删除*/
private void deleteToDo(){
if(_id==0){
return;
}
mDataBase.deleteMethod(_id);
myUpDataShow();
}
/*修改*/
private void editToDo(){
String str=editText1.getText().toString();
if(str.equalsIgnoreCase("")||_id==0){
return;
}
mDataBase.modMethod(_id, str);
myUpDataShow();
}
/*更新ListView*/
private void myUpDataShow(){
/*重新查询*/
mCursor.requery();
/*告知适配器已经变化*/
adapter.notifyDataSetChanged();
editText1.setText("");
_id=0;
}
@Override
/*退出程序*/
public boolean onKeyDown(int keyCode, KeyEvent event){
if(keyCode==KeyEvent.KEYCODE_BACK){
mDataBase.close();
this.finish();
}
return super.onKeyDown(keyCode, event);
}
}