SQLite数据库操作详细示例

2021-09-23 04:25:07

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>

SQLite数据库操作详细示例,布布扣,bubuko.com

SQLite数据库操作详细示例

码农公寓

相关文章