本文共 14215 字，大约阅读时间需要 47 分钟。

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
   
     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;			}		}	}}
   

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");	}}

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
   
     page(Context context,int offset,int resuletNumber){		openHelper=DataBaseOpenHelper.getDBInstance(context);		db=openHelper.getWritableDatabase();		ArrayList
    
      persons=new ArrayList
     
      ();			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();		}	}	}
     
    
   

修改后的DBUtils

package cc.database;import java.util.ArrayList;import java.util.List;import cc.domain.Person;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;/** * 在原来基础上修改后的DBUtils *   * 主要的修改: * 原来采用了原生的SQL语句进行增删改查 * 现在改为利用Android自带的方法: * insert(String table, String nullColumnHack, ContentValues values) * delete(String table, String whereClause, String[] whereArgs) * update(String table, ContentValues values, String whereClause, String[] whereArgs) * query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit) * 来实现 * 2014年12月14日16:53:08 */public class DBUtils {	public final static String TABLE_NAME="person";	public static DataBaseOpenHelper openHelper;	public static SQLiteDatabase db;			/**	 * 	 * 利用SQLiteDatabase.insert(String table, String nullColumnHack, ContentValues values)方法插入数据	 * 注意该方法的第二个参数:	 * 用于在未指定添加数据的情况下给某些可为空的列自动赋值为NULL.一般情况下不会这么做,所以传入NULL即可.	 * 	 * 该操作等同于:	 * db.execSQL("insert into person (name,phone) values(?,?)",new Object[]{person.getName(),person.getPhone()});	 */	public static void insertData(Context context,Person person){		openHelper=DataBaseOpenHelper.getDBInstance(context);		db=openHelper.getWritableDatabase();		ContentValues contentValues=new ContentValues();		contentValues.put("name", person.getName());		contentValues.put("phone", person.getPhone());        db.insert(TABLE_NAME, null, contentValues);	}			/**	 * 利用SQLiteDatabase.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit)	 * 方法查询数据	 * 	 * 该操作等同于:	 * Cursor cursor=db.rawQuery("select * from person where personid=?", new String[]{String.valueOf(id)});	 * 注意:	 * 1 rawQuery()方法查询后返回的结果是一个Cursor类的对象	 * 2 最后要关闭cursor即cursor.close();	 */	public  static Person queryData(Context context,int id){		openHelper=DataBaseOpenHelper.getDBInstance(context);		db=openHelper.getWritableDatabase();		Cursor cursor=db.query(TABLE_NAME, null, "personid=?",new String []{id+""}, null, null, null, null);		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;	}			/**	 * 利用SQLiteDatabase.update(String table, ContentValues values, String whereClause, String[] whereArgs)	 * 更新数据.	 * 第二个参数表示要更新的内容	 * 第三和第四个参数表示了要更新的列及其对应的值	 * 注意第三个参数中占位符的使用	 * 	 * 该操作等同于:	 * db.execSQL("update person set name=?,phone=? where personid=?",	 *    new Object[]{person.getName(),person.getPhone(),person.getId()});	 * 因为name和phone的类型都是String,但是id是整型的	 * 所以这里的数组写成了Object类型的	 */	public  static void updateData(Context context,Person person){		openHelper=DataBaseOpenHelper.getDBInstance(context);		db=openHelper.getWritableDatabase();		ContentValues contentValues=new ContentValues();		contentValues.put("name", person.getName());		contentValues.put("phone", person.getPhone());        db.update(TABLE_NAME, contentValues, "personid=?", new String []{person.getId()+""});	}			/**	 * 利用SQLiteDatabase.delete(String table, String whereClause, String[] whereArgs)	 * 删除数据	 * 	 * 该操作等同于:	 * db.execSQL("delete from person where personid=?",new Object[]{String.valueOf(id)});	 */	public  static void delete(Context context,int id){		openHelper=DataBaseOpenHelper.getDBInstance(context);		db=openHelper.getWritableDatabase();        db.delete(TABLE_NAME, "personid=?", new String []{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
   
     page(Context context,int offset,int resuletNumber){		openHelper=DataBaseOpenHelper.getDBInstance(context);		db=openHelper.getWritableDatabase();		ArrayList
    
      persons=new ArrayList
     
      ();			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 + "]";	}	}

转载地址：https://it9527.blog.csdn.net/article/details/20395739 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！