SQLite数据库操作详细示范

SQLite数据库操作详细示例

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 &lt; 15; i&#43;&#43;) {person=new Person("xiaoming"&#43;i, "9527"&#43;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("修改前:"&#43;person);person=new Person(1, "xx", "1234");mDBUtils.update(person);person=mDBUtils.query(1);System.out.println("修改后:"&#43;person);break;case R.id.deleteButton:mDBUtils.delete(2);break;case R.id.countButton:int count=mDBUtils.count();System.out.println("数据总量为:"&#43;count);break;case R.id.pageButton:List&lt;Person&gt; list=mDBUtils.page(4, 9);for (int i = 0; i &lt; list.size(); i&#43;&#43;) { person=list.get(i); System.out.println("分页的数据:"&#43;person);}break;case R.id.transactionButton:person=new Person(1, "ccc", "8888");mDBUtils.transaction(person);person=mDBUtils.query(1);System.out.println("事务操作后:"&#43;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&lt;Person&gt; page(int offset,int resuletNumber){SQLiteDatabase db=openHelper.getWritableDatabase();ArrayList&lt;Person&gt; persons=new ArrayList&lt;Person&gt;();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字段,默认&#20540;为null@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {db.execSQL("ALTER TABLE person ADD address VARCHAR(12) NULL");}}

main.xml如下:

&lt;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"     &gt;    &lt;Button        android:id="@&#43;id/tipButton"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:text="SQLite数据库测试"        android:layout_marginTop="15dip"        android:clickable="false"        /&gt;         &lt;Button        android:id="@&#43;id/addButton"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:text="添加数据"        android:layout_marginTop="15dip"       /&gt;           &lt;Button        android:id="@&#43;id/queryButton"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:text="查找数据"        android:layout_marginTop="15dip"       /&gt;              &lt;Button        android:id="@&#43;id/updateButton"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:text="更新数据"        android:layout_marginTop="15dip"       /&gt;                &lt;Button        android:id="@&#43;id/deleteButton"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:text="删除数据"        android:layout_marginTop="15dip"       /&gt;                 &lt;Button        android:id="@&#43;id/countButton"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:text="统计数据"        android:layout_marginTop="15dip"       /&gt;                &lt;Button        android:id="@&#43;id/pageButton"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:text="数据分页"        android:layout_marginTop="15dip"       /&gt;                  &lt;Button        android:id="@&#43;id/transactionButton"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:text="事务操作"        android:layout_marginTop="15dip"       /&gt;&lt;/LinearLayout&gt;

SQLite数据库操作详细示范

热点推荐