SQLite数据库操作详细示例
浏览数：37 /
时间：2015年06月12日
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;
import android.content.Context;
/**
* Demo描述:
* SQLite数据操作
*
* 参考资料:
* http://blog.csdn.net/lfdfhl/article/details/8195378
*
*/
public class MainActivity extends Activity {
private Button mAddButton;
private Button mQueryButton;
private Button mUpdateButton;
private Button mDeleteButton;
private Button mCountButton;
private Button mPageButton;
private Button mTransactionButton;
private Context mContext;
@Override
protected 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(){
mContext=this;
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());
}
private class ClickListenerImpl implements OnClickListener {
Person person=null;
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.addButton:
for (int i = 0; i < 15; i++) {
person=new Person("xiaoming"+i, "9527"+i);
DBUtils.add(mContext,person);
}
break;
case R.id.queryButton:
person=DBUtils.query(mContext,5);
System.out.println(person);
break;
case R.id.updateButton:
person=DBUtils.query(mContext,1);
System.out.println("修改前:"+person);
person=new Person(1, "xx", "1234");
DBUtils.update(mContext,person);
person=DBUtils.query(mContext,1);
System.out.println("修改后:"+person);
break;
case R.id.deleteButton:
DBUtils.delete(mContext,2);
break;
case R.id.countButton:
int count=DBUtils.count(mContext);
System.out.println("数据总量为:"+count);
break;
case R.id.pageButton:
List<Person> list=DBUtils.page(mContext,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");
DBUtils.transaction(mContext,person);
person=DBUtils.query(mContext,1);
System.out.println("事务操作后:"+person);
break;
default:
break;
}
}
}
}
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";
private static DataBaseOpenHelper mDataBaseOpenHelper;
//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);
}
//注意:
//将DataBaseOpenHelper写成单例的.
//否则当在一个for循环中频繁调用openHelper.getWritableDatabase()时
//会报错,提示数据库没有执行关闭操作
static synchronized DataBaseOpenHelper getDBInstance(Context context) {
if (mDataBaseOpenHelper == null) {
mDataBaseOpenHelper = new DataBaseOpenHelper(context);
}
return mDataBaseOpenHelper;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table person(personid integer primary key autoincrement,name varchar(20),phone VARCHAR(12))");
}
//为person增加一个address字段,默认值为null
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE person ADD address VARCHAR(12) NULL");
}
}
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 {
public static DataBaseOpenHelper openHelper;
public static SQLiteDatabase db;
public static void add(Context context,Person person){
openHelper=DataBaseOpenHelper.getDBInstance(context);
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 static Person query(Context context,int id){
openHelper=DataBaseOpenHelper.getDBInstance(context);
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 static void update(Context context,Person person){
openHelper=DataBaseOpenHelper.getDBInstance(context);
db=openHelper.getWritableDatabase();
db.execSQL("update person set name=?,phone=? where personid=?",
new Object[]{person.getName(),person.getPhone(),person.getId()});
}
public static void delete(Context context,int id){
openHelper=DataBaseOpenHelper.getDBInstance(context);
db=openHelper.getWritableDatabase();
db.execSQL("delete from person where personid=?",new Object[]{String.valueOf(id)});
}
//在while循环里要注意终止循环,否则是个死循环
//因为如果cursor不为空那么
//cursor.moveToFirst()总是返回true
public static int count(Context context){
openHelper=DataBaseOpenHelper.getDBInstance(context);
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 static List<Person> page(Context context,int offset,int resuletNumber){
openHelper=DataBaseOpenHelper.getDBInstance(context);
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 static void transaction(Context context,Person person){
openHelper=DataBaseOpenHelper.getDBInstance(context);
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();
}
}
}
Person如下:
package cc.domain;
public class Person {
private Integer id;
private String name;
private String phone;
public Person(String name, String phone) {
this.name = name;
this.phone = phone;
}
public Person(Integer id, String name, String phone) {
this.id = id;
this.name = name;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", phone=" + phone + "]";
}
}
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>郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
