SQLite 数据库增删改查 完整例子

SQLite 数据库增删改查 完整例子

1）编写实体类：Employee,代码如下所示：

/*
* Copyright (C) Mesada Technologies Co., Ltd. 2005-2011.
* All rights reserved.
*
* This software is the confidential and proprietary information
* of Mesada Technologies Co., Ltd. ("Confidential Information").
* You shall not disclose such Confidential Information and shall
* use it only in accordance with the terms of the license agreement
* you entered into with Mesada.
*/
package com.mesada.database.demo;

import android.net.Uri;

/**
* 一个实体类
*
* @author Xiaolong Long
* @date 2011-3-10
* @version 1.0
*/
public class Employee {
private int id;
private String name;
private int age;

/**
* @return the id
*/
public int getId() {
return id;
}

/**
* @param id
* the id to set
*/
public void setId(int id) {
this.id = id;
}

/**
* @return the name
*/
public String getName() {
return name;
}

/**
* @param name
* the name to set
*/
public void setName(String name) {
this.name = name;
}

/**
* @return the age
*/
public int getAge() {
return age;
}

/**
* @param age
* the age to set
*/
public void setAge(int age) {
if (age >= 1 && age < 150) {
this.age = age;
} else {
this.age = -1;
}
}

/*
* (non-Javadoc)
*
* @see java.lang.Object#toString()
*/
@Override
public String toString() {
return "Employee [id=" + id + ", name=" + name + ", age=" + age + "]";
}

}

2）新建类DBAdapter类，封装了操作数据库的增删改查，打开，关闭数据库的功能，代码如下：
package com.mesada.database.demo;

/*
* Copyright (C) Mesada Technologies Co., Ltd. 2005-2011.
* All rights reserved.
*
* This software is the confidential and proprietary information
* of Mesada Technologies Co., Ltd. ("Confidential Information").
* You shall not disclose such Confidential Information and shall
* use it only in accordance with the terms of the license agreement
* you entered into with Mesada.
*/

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

/**
* Please specify the function of this class
*
* @author Xiaolong Long
* @date 2011-3-10
* @version 1.0
*/
public class DBAdapter {

private static final String DB_NAME = "mesada.db";
private static final String DB_TABLE = "employee";
private static final int DB_VERSION = 1;

// many columns
public static final String ID = "_id";
public static final String NAME = "name";
public static final String AGE = "age";

private SQLiteDatabase mDb;
private final Context mContext;
private DBOpenHelper mDBOpenHelper;

public DBAdapter(Context context) {
this.mContext = context;
}


/**
*
* Open the database.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
public void open() throws SQLiteException {
mDBOpenHelper = new DBOpenHelper(mContext, DB_NAME, null, DB_VERSION);
try {
mDb = mDBOpenHelper.getWritableDatabase();
} catch (Exception e) {
mDb = mDBOpenHelper.getReadableDatabase();
}
}

/**
*
* Close the database.
*
* @param
* @return
* @date 2011-3-10
* @author Xiaolong Long
*/
public void close() {
if (mDb != null) {
mDb.close();
}
}

/**
*
* Inserting a row into the table which called employee.
*
* @param
* @return
* @date 2011-3-10
* @author Xiaolong Long
*/
public long insert(Employee employee) {
System.err.println("insert----->");
ContentValues values = new ContentValues();
values.put(NAME, employee.getName());
values.put(AGE, employee.getAge());
long rowID = mDb.insert(DB_TABLE, null, values);
System.err.println("ROWID--->" + rowID);
return rowID;
}

/**
*
* Deleting special rows in the database.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
public long delteByID(long id) {
return mDb.delete(DB_TABLE, ID + "=" + id, null);
}

/**
*
* Deleting rows in the database.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
public long deleteAllRecords() {
return mDb.delete(DB_TABLE, null, null);
}

/**
*
* Convenience method for updating rows in the database.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
public long updateOneRecord(long id, Employee employee) {
ContentValues values = new ContentValues();
values.put(NAME, employee.getName());
values.put(AGE, employee.getAge());
return mDb.update(DB_TABLE, values, ID + "=" + id, null);
}

/**
*
* Query one record from the given table.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
public Employee[] queryOneRecord(long id) {
Cursor cursor = mDb.query(DB_TABLE, new String[] { ID, NAME, AGE }, ID
+ "=" + id, null, null, null, null);
return convertToEmployee(cursor);
}
/**
*
* Query all data from the given table.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
public Employee[] queryAllRecords() {
Cursor cursor = mDb.query(DB_TABLE, new String[] { ID, NAME, AGE },
null, null, null, null, null);
return convertToEmployee(cursor);
}

// 将数据写进实体类
public Employee[] convertToEmployee(Cursor cursor) {
int counts = cursor.getCount();

if (counts == 0 || !cursor.moveToFirst()) {
return null;
}
Employee[] employees = new Employee[counts];
for (int i = 0; i < counts; i++) {
employees[i] = new Employee();
employees[i].setId(cursor.getInt(0));
employees[i].setName(cursor.getString(1));
employees[i].setAge(cursor.getInt(2));
cursor.moveToNext();
}
return employees;
}

// 表结构是否存在记录
public boolean isExist() {
Cursor cursor = mDb.query(DB_TABLE, null, null, null, null, null, null);
int counts = cursor.getCount();
if (counts > 0) {
return true;
} else {
return false;
}
}

// 表结构是否存在某条记录
public boolean isExistRecord(long id) {

Cursor cursor = mDb.query(DB_TABLE, new String[] { ID, NAME, AGE }, ID
+ "=" + id, null, null, null, null);
int counts = cursor.getCount();
if (counts > 0) {
return true;
} else {
return false;
}
}

public static class DBOpenHelper extends SQLiteOpenHelper {

private static final String DB_CREATE = "create table " + DB_TABLE
+ " (" + ID + " integer primary key autoincrement, " + NAME
+ " text not null, " + AGE + " integer);";

public DBOpenHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(DB_CREATE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
/*
* 当你的应用要更新升级的时候，同时新版本中数据库表结构或内容有变化，这时upgrade方法会根据你的数据库版本号来判断数据库是否升级，
* 你可以在upgrade方法中执行数据库的变化。
*/
System.err.println("onUpgrade(,)...");
db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
onCreate(db);
}

}
}
3）在继承于Acitivity的类中编写代码，代码如下：
package com.mesada.database.demo;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends Activity implements OnClickListener {

private static final String TAG = "MainActivity";
private static final boolean mIsPrintInfo = true;

EditText mUserNameView;
EditText mAgeView;
EditText mIdView;

TextView mDataView;

Button mAddView;
Button mDisplayAllView;
Button mEmptyScreenView;
Button mDeleteAllView;
Button mDeleteByIdView;
Button mQueryByIdView;
Button mUpdateByIdView;

DBAdapter mDbAdapter;

/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
if (mIsPrintInfo)
Log.i(TAG, "onCreate(Bundle savedInstanceState)...");

super.onCreate(savedInstanceState);
setContentView(R.layout.main);
setupControls();

mDbAdapter = new DBAdapter(this);

mAddView.setOnClickListener(this);
mDeleteAllView.setOnClickListener(this);
mDisplayAllView.setOnClickListener(this);
mEmptyScreenView.setOnClickListener(this);
mDeleteByIdView.setOnClickListener(this);
mQueryByIdView.setOnClickListener(this);
mUpdateByIdView.setOnClickListener(this);

mDataView.setText(getString(R.string.no_data));

}

/**
*
* Finds the views that was identified by the id attribute from the XML.
*
* @param
* @return
* @date 2011-3-10
* @author Xiaolong Long
*/
private void setupControls() {

mUserNameView = (EditText) findViewById(R.id.userName);
mAgeView = (EditText) findViewById(R.id.age);
mIdView = (EditText) findViewById(R.id.id);

mDataView = (TextView) findViewById(R.id.data);

mAddView = (Button) findViewById(R.id.addOneRecord);
mDeleteAllView = (Button) findViewById(R.id.deleteAll);
mDisplayAllView = (Button) findViewById(R.id.displayAll);
mEmptyScreenView = (Button) findViewById(R.id.emptyScreen);
mDeleteByIdView = (Button) findViewById(R.id.deleteByID);
mQueryByIdView = (Button) findViewById(R.id.queryByID);
mUpdateByIdView = (Button) findViewById(R.id.updateByID);
}

public void onClick(View v) {
Log.i(TAG, "onClick(View v)...");

int id = v.getId();
switch (id) {
case R.id.addOneRecord:
addOneRecord();
break;
case R.id.deleteAll:
deleteAllRecords();
break;
case R.id.displayAll:
displayAll();
break;
case R.id.emptyScreen:
emptyScreen();
break;
case R.id.deleteByID:
deleteByID();
break;
case R.id.queryByID:
queryByID();
break;
case R.id.updateByID:
updateByID();
break;
default:
break;
}
}

/**
*
* Insert one record to the table which called employee.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
private void addOneRecord() {
if (mIsPrintInfo)
Log.i(TAG, "addOneRecord()...");

String userName = null;
String str = null;

userName = String.valueOf(mUserNameView.getText()).trim();
str = String.valueOf(mAgeView.getText()).trim();
if (!(userName.length() > 0) || !(str.length() > 0)) {
Toast.makeText(this, getString(R.string.cannot_be_null),
Toast.LENGTH_SHORT).show();
return;
} else {
try {
Employee employee = new Employee();
employee.setName(userName);
employee.setAge(Integer.parseInt(str));
System.err.println(employee);
mDbAdapter.open();
mDbAdapter.insert(employee);
Toast.makeText(this, getString(R.string.operation_successful),
Toast.LENGTH_SHORT).show();
emptyScreen();
displayAll();
} catch (Exception e) {
Toast.makeText(this, getString(R.string.operation_failed),
Toast.LENGTH_SHORT).show();
}

}
}

/**
*
* Delete special rows in the database.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
private void deleteByID() {
if (mIsPrintInfo)
Log.i(TAG, "deleteByID()...");

String str = null;
str = String.valueOf(mIdView.getText()).trim();
if (!(str.length() > 0)) {
Toast.makeText(this, getString(R.string.cannot_be_null),
Toast.LENGTH_SHORT).show();
return;
} else {
long id = Long.parseLong(str);
mDbAdapter.open();
if (!(mDbAdapter.isExistRecord(id))) {
mDataView.setText(getString(R.string.no_data));
mDbAdapter.close();
mDataView.setText("");
return;
} else {
try {
mDbAdapter.open();
mDbAdapter.delteByID(id);
Toast.makeText(this,
getString(R.string.operation_successful),
Toast.LENGTH_SHORT).show();
displayAll();
} catch (Exception e) {
Toast.makeText(this, getString(R.string.operation_failed),
Toast.LENGTH_SHORT).show();
}
}
}
}

/**
*
* Removes data from the table.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
private void deleteAllRecords() {
if (mIsPrintInfo)
Log.i(TAG, "deleteAll()...");

mDbAdapter.open();
if (!mDbAdapter.isExist()) {
Toast.makeText(this, getString(R.string.no_data),
Toast.LENGTH_SHORT).show();
return;
}
else {
try {
mDbAdapter.deleteAllRecords();
Toast.makeText(this, getString(R.string.operation_successful),
Toast.LENGTH_SHORT).show();
mDbAdapter.close();
emptyScreen();
} catch (Exception e) {
}
}

}

/**
*
* Update one record.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
private void updateByID() {
String userName = null;
String str1 = null;
String str2 = null;

userName = String.valueOf(mUserNameView.getText()).trim();
str1 = String.valueOf(mAgeView.getText()).trim();
str2 = String.valueOf(mIdView.getText()).trim();

if (!(userName.length() > 0) || !(str1.length() > 0)
|| !(str2.length() > 0)) {
Toast.makeText(this, getString(R.string.cannot_be_null),
Toast.LENGTH_SHORT).show();
return;
} else {
long id = Long.parseLong(str2);
mDbAdapter.open();
if (!(mDbAdapter.isExistRecord(id))) {
mDataView.setText(getString(R.string.no_data));
mDbAdapter.close();
return;
} else {
try {
Employee employee = new Employee();
employee.setName(userName);
employee.setAge(Integer.parseInt(str1));
System.err.println(employee);
mDbAdapter.open();
mDbAdapter
.updateOneRecord(Integer.parseInt(str2), employee);
Toast.makeText(this,
getString(R.string.operation_successful),
Toast.LENGTH_SHORT).show();
mDbAdapter.close();
mDataView.setText("");
displayAll();
} catch (Exception e) {
Toast.makeText(this, getString(R.string.operation_failed),
Toast.LENGTH_SHORT).show();
}
}
}
}

/**
*
* Please specify the function of this method
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
private void queryByID() {
String str = null;
str = String.valueOf(mIdView.getText()).trim();
if (!(str.length() > 0)) {
Toast.makeText(this, getString(R.string.cannot_be_null),
Toast.LENGTH_SHORT).show();
return;
} else {
long id = Long.parseLong(str);
mDbAdapter.open();
if (!(mDbAdapter.isExistRecord(id))) {
mDataView.setText(getString(R.string.no_data));
mDbAdapter.close();
return;
} else {
Employee[] employees = mDbAdapter.queryOneRecord(Integer
.parseInt(str));
for (Employee employee : employees) {
mDataView
.setText("ID: "
+ employee.getId()
+ ",\t\t\tUserName: "
+ employee.getName()
+ ",\t\t\tAge: "
+ (employee.getAge() == -1 ? getString(R.string.impossible)
: employee.getAge()) + "\n");
}
mDbAdapter.close();
}
}
}

/**
*
* Show all data from the table.
*
* @param
* @return
* @date 2011-3-11
* @author Xiaolong Long
*/
private void displayAll() {
mDbAdapter.open();
Employee[] employees = mDbAdapter.queryAllRecords();
if (employees != null) {
StringBuffer appStr = new StringBuffer();
for (Employee employee : employees) {
appStr.append("编号: "
+ employee.getId()
+ ",\t\t\t姓名: "
+ employee.getName()
+ ",\t\t\t年龄: "
+ (employee.getAge() == -1 ? getString(R.string.impossible)
: employee.getAge()) + "\n");
}
mDataView.setText(appStr);
mDbAdapter.close();
} else {
mDataView.setText(getString(R.string.no_data));
}

}


private void emptyScreen() {
mUserNameView.setText("");
mAgeView.setText("");
mIdView.setText("");
mDataView.setText(getString(R.string.no_data));
}
}
4）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">
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content">
<TextView
android:layout_width="100px"
android:layout_height="wrap_content"
android:text="@string/name" />
<EditText
android:id="@+id/userName"
android:layout_width="370px"
android:layout_height="wrap_content"
android:singleLine="true"></EditText>
</LinearLayout>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content">
<TextView
android:layout_width="100px"
android:layout_height="wrap_content"
android:text="@string/age" />
<EditText
android:id="@+id/age"
android:layout_width="370px"
android:layout_height="wrap_content"
android:singleLine="true"
android:numeric="integer"></EditText>
</LinearLayout>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content">
<Button
android:id="@+id/addOneRecord"
android:text="@string/add"
android:layout_width="120px"
android:layout_height="wrap_content"></Button>
<Button
android:id="@+id/displayAll"
android:text="@string/display_all"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></Button>
<Button
android:text="@string/empty_screen"
android:id="@+id/emptyScreen"
android:layout_width="120px"
android:layout_height="wrap_content"></Button>
<Button
android:text="@string/delete_all"
android:id="@+id/deleteAll"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></Button>
</LinearLayout>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content">
<TextView
android:text="@string/conditional_id"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></TextView>
<EditText
android:id="@+id/id"
android:layout_width="370px"
android:layout_height="wrap_content"
android:numeric="integer"></EditText>
</LinearLayout>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content">
<Button
android:text="@string/delete_id"
android:id="@+id/deleteByID"
android:layout_width="150px"
android:layout_height="wrap_content"></Button>
<Button
android:id="@+id/queryByID"
android:text="@string/query_id"
android:layout_width="150px"
android:layout_height="wrap_content"></Button>
<Button
android:id="@+id/updateByID"
android:text="@string/update_id"
android:layout_width="150px"
android:layout_height="wrap_content"></Button>
</LinearLayout>
<TextView
android:text="@string/txt_display_all"
android:layout_width="fill_parent"
android:layout_height="wrap_content"></TextView>
<TextView
android:id="@+id/data"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></TextView>
</LinearLayout>

5）AndroidMainfest.xml 文件代码如下所示：
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.mesada.database.demo"
android:versionCode="1"
android:versionName="1.0">
<application android:icon="@drawable/icon" android:label="@string/app_name">
<activity android:name=".MainActivity"
android:label="@string/app_name">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
<uses-sdk android:minSdkVersion="8" />
</manifest>