SQLite数据库操作详细示例
MainActivity如下:
package cc.testdb;import java.util.List;import cc.database.DBUtils;import cc.domain.Person;import android.os.Bundle;import android.view.View;import android.view.View.OnClickListener;import android.view.Window;import android.view.WindowManager;import android.widget.Button;import android.app.Activity;/** * Demo描述: * SQLite数据操作 * * 参考资料: * http://blog.csdn.net/lfdfhl/article/details/8195378 * */public class MainActivity extends Activity { private DBUtils mDBUtils; private Button mAddButton; private Button mQueryButton; private Button mUpdateButton; private Button mDeleteButton; private Button mCountButton; private Button mPageButton; private Button mTransactionButton;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);requestWindowFeature(Window.FEATURE_NO_TITLE);getWindow().setFlags(WindowManager.LayoutParams.FLAG_FULLSCREEN, WindowManager.LayoutParams.FLAG_FULLSCREEN);setContentView(R.layout.main);init();}private void init(){mAddButton=(Button) findViewById(R.id.addButton);mAddButton.setOnClickListener(new ClickListenerImpl());mQueryButton=(Button) findViewById(R.id.queryButton);mQueryButton.setOnClickListener(new ClickListenerImpl());mUpdateButton=(Button) findViewById(R.id.updateButton);mUpdateButton.setOnClickListener(new ClickListenerImpl());mDeleteButton=(Button) findViewById(R.id.deleteButton);mDeleteButton.setOnClickListener(new ClickListenerImpl());mCountButton=(Button) findViewById(R.id.countButton);mCountButton.setOnClickListener(new ClickListenerImpl());mPageButton=(Button) findViewById(R.id.pageButton);mPageButton.setOnClickListener(new ClickListenerImpl());mTransactionButton=(Button) findViewById(R.id.transactionButton);mTransactionButton.setOnClickListener(new ClickListenerImpl());}//注意://每次都生成新的mDBUtils,//因为每次数据库操作都需要一个新的openHelper//参见DBUtils类构造方法便知private class ClickListenerImpl implements OnClickListener {Person person=null;@Overridepublic void onClick(View v) {mDBUtils=new DBUtils(MainActivity.this);switch (v.getId()) {case R.id.addButton:for (int i = 0; i < 15; i++) {person=new Person("xiaoming"+i, "9527"+i);mDBUtils.add(person);}break;case R.id.queryButton:person=mDBUtils.query(5);System.out.println(person);break;case R.id.updateButton:person=mDBUtils.query(1);System.out.println("修改前:"+person);person=new Person(1, "xx", "1234");mDBUtils.update(person);person=mDBUtils.query(1);System.out.println("修改后:"+person);break;case R.id.deleteButton:mDBUtils.delete(2);break;case R.id.countButton:int count=mDBUtils.count();System.out.println("数据总量为:"+count);break;case R.id.pageButton:List<Person> list=mDBUtils.page(4, 9);for (int i = 0; i < list.size(); i++) { person=list.get(i); System.out.println("分页的数据:"+person);}break;case R.id.transactionButton:person=new Person(1, "ccc", "8888");mDBUtils.transaction(person);person=mDBUtils.query(1);System.out.println("事务操作后:"+person);break;default:break;}}}}
DBUtils如下:
package cc.database;import java.util.ArrayList;import java.util.List;import cc.domain.Person;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;public class DBUtils {private DataBaseOpenHelper openHelper;public DBUtils(Context context) {openHelper=new DataBaseOpenHelper(context);}public void initDataBase(Context context){openHelper=new DataBaseOpenHelper(context);openHelper.getWritableDatabase();}public void add(Person person){SQLiteDatabase db=openHelper.getWritableDatabase(); db.execSQL("insert into person (name,phone) values(?,?)", new Object[]{person.getName(),person.getPhone()});}//注意:// 1 rawQuery()方法查询后返回的结果是一个Cursor类的对象 // 2 最后要关闭cursor即cursor.close();public Person query(int id){SQLiteDatabase db=openHelper.getWritableDatabase();Cursor cursor=db.rawQuery("select * from person where personid=?", new String[]{String.valueOf(id)});while(cursor.moveToFirst()){int personid=cursor.getInt(cursor.getColumnIndex("personid"));String name=cursor.getString(cursor.getColumnIndex("name"));String phone=cursor.getString(cursor.getColumnIndex("phone"));return new Person(personid, name, phone);}cursor.close();return null;}//因为name和phone的类型都是String,但是id是整型的//所以这里的数组写成了Object类型的public void update(Person person){SQLiteDatabase db=openHelper.getWritableDatabase();db.execSQL("update person set name=?,phone=? where personid=?", new Object[]{person.getName(),person.getPhone(),person.getId()});}public void delete(int id){SQLiteDatabase db=openHelper.getWritableDatabase(); db.execSQL("delete from person where personid=?",new Object[]{String.valueOf(id)});}//在while循环里要注意终止循环,否则是个死循环//因为如果cursor不为空那么//cursor.moveToFirst()总是返回truepublic int count(){SQLiteDatabase db=openHelper.getReadableDatabase(); Cursor cursor=db.rawQuery("select count(*) from person", null);int i=0;while(cursor.moveToFirst()){ i=cursor.getInt(0); break;}return i;}public List<Person> page(int offset,int resuletNumber){SQLiteDatabase db=openHelper.getWritableDatabase();ArrayList<Person> persons=new ArrayList<Person>();Person person=null;Cursor cursor=db.rawQuery("select * from person limit ?,?", new String []{String.valueOf(offset),String.valueOf(resuletNumber)});while(cursor.moveToNext()){int personid=cursor.getInt(cursor.getColumnIndex("personid"));String name=cursor.getString(cursor.getColumnIndex("name"));String phone=cursor.getString(cursor.getColumnIndex("phone"));person=new Person(personid, name, phone);persons.add(person);}return persons; }/** * 结束事务有两种:提交事务和回滚事务. * 默认情况是回滚事务!!!! * 事务是否提交是由事务的标志来决定: * 如果事务的标志位失败(false),就回滚事务;否则(true)提交事务。 * 所以默认情况下事务的标志为失败(false)即回滚事务. */public void transaction(Person person){SQLiteDatabase db = openHelper.getWritableDatabase();//开启事务db.beginTransaction();try{db.execSQL("update person set name=? where personid=?", new Object[]{person.getName(),person.getId()});db.execSQL("update person set phone=? where personid=?", new Object[]{person.getPhone(),person.getId()});//设置事务的标志为成功db.setTransactionSuccessful();}finally{//结束事务,默认情况下是回滚事务db.endTransaction();}}}
DataBaseOpenHelper如下:
package cc.database;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DataBaseOpenHelper extends SQLiteOpenHelper { private final static String DATABASE_NAME="test.db"; //super(context, DATABASE_NAME, null, 1)方法: //若不存在DATABASE_NAME数据,则执行onCreate(SQLiteDatabase db)方法 //若已经存在则不再新建数据库 //方法中第三个参数为:version 版本号 //当version变大时会自动调用 //onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)方法public DataBaseOpenHelper(Context context) {super(context, DATABASE_NAME, null, 1);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("create table person(personid integer primary key autoincrement,name varchar(20),phone VARCHAR(12))");}//为person增加一个address字段,默认值为null@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {db.execSQL("ALTER TABLE person ADD address VARCHAR(12) NULL");}}
main.xml如下:
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingLeft="20dip" android:paddingRight="20dip" android:orientation="vertical" > <Button android:id="@+id/tipButton" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="SQLite数据库测试" android:layout_marginTop="15dip" android:clickable="false" /> <Button android:id="@+id/addButton" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="添加数据" android:layout_marginTop="15dip" /> <Button android:id="@+id/queryButton" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="查找数据" android:layout_marginTop="15dip" /> <Button android:id="@+id/updateButton" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="更新数据" android:layout_marginTop="15dip" /> <Button android:id="@+id/deleteButton" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="删除数据" android:layout_marginTop="15dip" /> <Button android:id="@+id/countButton" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="统计数据" android:layout_marginTop="15dip" /> <Button android:id="@+id/pageButton" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="数据分页" android:layout_marginTop="15dip" /> <Button android:id="@+id/transactionButton" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="事务操作" android:layout_marginTop="15dip" /></LinearLayout>