android数据存储<三>-----Sqlite操作

2022-06-14 08:26:43 浏览数 (1)

1.本文采用单元测试的方式来测试Sqlite数据的增删改查操作。因此在清单文件中需要注册单元测试环境

代码语言：javascript复制
       <uses-library android:name="android.test.runner" /> 
    
     
        <activity
            android:name="com.example.database.MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
               
                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
     </application>
<instrumentation android:name="android.test.InstrumentationTestRunner"
  android:targetPackage="com.example.database" android:label="Tests for My App" />
</manifest>

2.创建一个数据库文件dbservice.java 用来建立一个数据库和更新数据库表，并创建一个记类contact类  

代码语言：javascript复制
package com.example.databaseService;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class Dbservice extends SQLiteOpenHelper{

	public Dbservice(Context context) {
		super(context, "contacts.db", null,2);
		
	}
/*创建一个数据库表格,并定义字段*/
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE contacts (contactID integer primary key autoincrement,name varchar(20),phonenum vachar(12) NULL)");
		
	}
/*新增加一个字段，并更新数据库*/
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("ALTER TABLE contacts ADD amount vachar(12) NULL");
		
	}
	
 }
代码语言：javascript复制
package com.example.databaseService;




public class contacts {
  private Integer contactID;
  private String name;
  private String phonenum;
  private Integer amount;
  public contacts( String name, String phonenum, Integer amount) {
		
		
		this.name = name;
		this.phonenum = phonenum;
		this.amount = amount;
	}
public contacts(Integer contactId, String name, String phonenum, Integer amount) {
	
	this.contactID = contactId;
	this.name = name;
	this.phonenum = phonenum;
	this.amount = amount;
}
 public contacts() {
	
}
public Integer getContactId()
 {
	return contactID;
	 
 }
 public void SetContactId(Integer ID)
 {
	this.contactID=ID;
	 
 }
 public String  getName()
 {
	return name;
	 
 }
 public void SetName(String name)
 {
	this.name=name;
	 
 }
 public String  getphonenum()
 {
	return phonenum;
	 
 }
 public void Setphonename(String num)
 {
	this.phonenum=num;
	 
 }
 
 public Integer getAmount()
 {
	return amount;
	 
 }
 public void SetAmount(Integer amount)
 {
	this.amount=amount;
	 
 }
	@Override
	public String toString() 
	{   
		return "contact [contactID="   contactID   ", name="   name   ", phonenum="   phonenum
				  ", amount="  amount   "]";
	}
	
}

3.创建一个数据库操作类 dbOperation.Java用来完成数据库记录的增删改查

代码语言：javascript复制
/*以下是采用sql语句来完成操作，常规做法是这样的，但也可以采用googleAPI来完成*/
package com.example.databaseService;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class dataOperation {

Context context;
private Dbservice db;

final String mytag="mydb";

   public dataOperation(Context context) {
	
	   this.db = new Dbservice(context);
}

/*增加一个记录*/
public void adddata(contacts ct)
   {
	   SQLiteDatabase sqdb = db.getWritableDatabase();
		sqdb.execSQL("insert into contacts(name, phonenum, amount) values(?,?,?)",
				new Object[]{ct.getName(), ct.getphonenum(), ct.getAmount()});
	   
   }
/*删除一个记录*/
public void deletedata(Integer ID)
{     
	  
	   SQLiteDatabase sqdb = db.getWritableDatabase();
		sqdb.execSQL("delete from contacts where contactID=?", new Object[]{ID});
		Log.i(mytag,ID.toString());
		Log.i(mytag,"hello,hello");
	   
}
/*修改一个记录*/

public void changedata(contacts ct,Integer ID)
{
	 SQLiteDatabase sqdb = db.getWritableDatabase();
	 sqdb.execSQL("update contacts set name=?, phonenum=?, amount=? where contactID=?",new Object[]{ct.getName(), ct.getphonenum(), ct.getAmount(),ID});
}
/*查询一个记录*/
public contacts querydata(Integer ID)
{
	 SQLiteDatabase sqdb = db.getReadableDatabase();
	 Cursor cursor =sqdb.rawQuery("select * from contacts where contactID=?", new String[]{ID.toString()});
	 if(cursor.moveToFirst())
	 {
		 String name=cursor.getString(cursor.getColumnIndex("name"));

		String phonenum= cursor.getString(cursor.getColumnIndex("phonenum"));

		 Integer amount=cursor.getInt(cursor.getColumnIndex("amount"));
		
		 return new contacts(ID,name,phonenum,amount);
	 }
	 cursor.close();
	 return null;
	 
	 
}
/*统计记录的个数*/
public  long countdata()
{
	 SQLiteDatabase sqdb = db.getReadableDatabase();
	 Cursor cursor =sqdb.rawQuery("select count(*) from contacts", null);
	 cursor.moveToFirst();
	 return cursor.getLong(0);
}
/**
 * 分页获取记录
 * @param offset 跳过前面多少条记录
 * @param maxResult 每页获取多少条记录
 * @return
 */
public List<contacts> getScrollData(Integer offset, Integer maxResult){
List<contacts> cts = new ArrayList<contacts>();
SQLiteDatabase sqdb = db.getReadableDatabase();
Cursor cursor =sqdb.rawQuery("select * from contacts order by contactID asc limit ?,?", new String[]{offset.toString(),maxResult.toString()});
while(cursor.moveToNext()){
	int contactsID = cursor.getInt(cursor.getColumnIndex("contactID"));
	String name = cursor.getString(cursor.getColumnIndex("name"));
	String phonenum = cursor.getString(cursor.getColumnIndex("phonenum"));
	int amount = cursor.getInt(cursor.getColumnIndex("amount"));
	cts.add(new contacts(contactsID, name, phonenum, amount));
}
cursor.close();
return cts;
}
/**
 * 分页获取记录
 * @param offset 跳过前面多少条记录
 * @param maxResult 每页获取多少条记录
 * @return
 */
public Cursor getcursorScrollData(Integer offset, Integer maxResult){

SQLiteDatabase sqdb = db.getReadableDatabase();
Cursor cursor =sqdb.rawQuery("select * from contacts order by contactID asc limit ?,?", new String[]{offset.toString(),maxResult.toString()});
return cursor;
}
}

以上是采用Sql语句来操作，当然也可以不采用SQl语句，Android有相关接口直接操作记录。dbOperation2.java就是如下：

代码语言：javascript复制
/*采用sql语句来完成操作，常规做法是这样的，但也可以采用googleAPI来完成*/
package com.example.databaseService;

  import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;


	public class dataOperation2 {

	Context context;
	private Dbservice db;

	final String mytag="mydb";

	   public dataOperation2(Context context) {
		
		   this.db = new Dbservice(context);
	}

	/*增加一个记录*/
	public void adddata2(contacts ct)
	   {  
		  ContentValues values=new ContentValues();
		  values.put("name", ct.getName());
		  values.put("phonenum", ct.getphonenum());
		  values.put("amount", ct.getAmount());
		   SQLiteDatabase sqdb = db.getWritableDatabase();
			sqdb.insert("contacts", null, values);
		   
	   }
	/*删除一个记录*/
	public void deletedata2(Integer ID)
	{     
		  
		   SQLiteDatabase sqdb = db.getWritableDatabase();
		   
			sqdb.delete("contacts", "contactID=?", new String[]{ID.toString()} );
			
			
		   
	}
	/*修改一个记录*/

	public void changedata2(contacts ct,Integer ID)
	{
		 SQLiteDatabase sqdb = db.getWritableDatabase();
		 ContentValues values=new ContentValues();
		  values.put("name", ct.getName());
		  values.put("phonenum", ct.getphonenum());
		  values.put("amount", ct.getAmount());
		 sqdb.update("contacts", values, "contactID=?",new String[]{ID.toString()} );
	}
	/*查询一个记录*/
	public contacts querydata(Integer ID)
	{
		 SQLiteDatabase sqdb = db.getReadableDatabase();
		 Cursor cursor =sqdb.rawQuery("select * from contacts where contactID=?", new String[]{ID.toString()});
		 if(cursor.moveToFirst())
		 {
			 String name=cursor.getString(cursor.getColumnIndex("name"));

			String phonenum= cursor.getString(cursor.getColumnIndex("phonenum"));

			 Integer amount=cursor.getInt(cursor.getColumnIndex("amount"));
			
			 return new contacts(ID,name,phonenum,amount);
		 }
		 cursor.close();
		 return null;
		 
		 
	}
	/*统计记录的个数*/
	public  long countdata()
	{
		 SQLiteDatabase sqdb = db.getReadableDatabase();
		 Cursor cursor =sqdb.rawQuery("select count(*) from contacts", null);
		 cursor.moveToFirst();
		 return cursor.getLong(0);
	}
	/**
	 * 分页获取记录
	 * @param offset 跳过前面多少条记录
	 * @param maxResult 每页获取多少条记录
	 * @return
	 */
	public List<contacts> getScrollData(Integer offset, Integer maxResult){
	List<contacts> cts = new ArrayList<contacts>();
	SQLiteDatabase sqdb = db.getReadableDatabase();
	Cursor cursor =sqdb.rawQuery("select * from contacts order by contactID asc limit ?,?", new String[]{offset.toString(),maxResult.toString()});
	while(cursor.moveToNext()){
		int contactsID = cursor.getInt(cursor.getColumnIndex("contactID"));
		String name = cursor.getString(cursor.getColumnIndex("name"));
		String phonenum = cursor.getString(cursor.getColumnIndex("phonenum"));
		int amount = cursor.getInt(cursor.getColumnIndex("amount"));
		cts.add(new contacts(contactsID, name, phonenum, amount));
	}
	cursor.close();
	return cts;
	}
	/**
	 * 分页获取记录
	 * @param offset 跳过前面多少条记录
	 * @param maxResult 每页获取多少条记录
	 * @return
	 */
	public Cursor getcursorScrollData(Integer offset, Integer maxResult){

	SQLiteDatabase sqdb = db.getReadableDatabase();
	Cursor cursor =sqdb.rawQuery("select * from contacts order by contactID asc limit ?,?", new String[]{offset.toString(),maxResult.toString()});
	return cursor;
	}
	}

写一个测试用来进行测试：

代码语言：javascript复制
package com.example.test;

import java.util.List;

import com.example.databaseService.Dbservice;
import com.example.databaseService.contacts;
import com.example.databaseService.dataOperation;
import com.example.databaseService.dataOperation2;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;
import android.util.Log;


public class dbtest extends AndroidTestCase {
	 final String mytag="mydbtest";
	public void  creatdb()
	{
	 Dbservice db1= new Dbservice(this.getContext());
	 db1.getWritableDatabase();
	}
	public void adddbdata()
	{
		dataOperation Op=new dataOperation(this.getContext());
		for(int i = 0 ; i < 20 ; i  ){
			contacts cts = new contacts("zhangxx"  i, "136765765"  i, 100);
			Op.adddata(cts);
			
		}
	}
	public void adddbdata2()
	{
		dataOperation2 Op=new dataOperation2(this.getContext());
		
			contacts cts = new contacts("zhangxx5", "136765765", 200);
			Op.adddata2(cts);
			
		
	}
    
	public void deletadbdata2()
	{ 
		dataOperation2 Op=new dataOperation2(this.getContext());
		Op.deletedata2(6);
	
	
	}
	public void deletadbdata()
	{ 
		dataOperation Op=new dataOperation(this.getContext());
		Op.deletedata(20);
		Op.deletedata(10);
	
	}
	public void updatedata()
	{ 
		dataOperation Op=new dataOperation(this.getContext());
		contacts cts = new contacts("wangxiaoming", "13787701111", 150);
	    Op.changedata(cts, 3);
	}
	public void updatedata2()
	{ 
		dataOperation2 Op=new dataOperation2(this.getContext());
		contacts cts = new contacts("wxy", "13787701111", 250);
	    Op.changedata2(cts, 3);
	}
	public void querydata()
	{ 
		
		dataOperation Op=new dataOperation(this.getContext());
		contacts cts=Op.querydata(4);
		if (cts==null)
		{
			Log.e(mytag,"sorry,The recoder is not exist");
		}
		else
		{
		Log.e(mytag,cts.toString());
		}
	}
	public void getconut()
	{
		dataOperation Op=new dataOperation(this.getContext());
		Log.e(mytag,String.valueOf(Op.countdata()));
	}
	public void ScrollData() 
	{
		dataOperation Op=new dataOperation(this.getContext());
		List<contacts> cts=Op.getScrollData(0, 10);
		for(contacts ct:cts)
		{
			Log.e(mytag,ct.toString());
		}
	}
	public void ScrollData2() 
	{
		Cursor cursor;
		dataOperation Op=new dataOperation(this.getContext());
	    cursor=Op.getcursorScrollData(0, 5);
	    while(cursor.moveToNext())
	    {
	    	int contactsID = cursor.getInt(cursor.getColumnIndex("contactID"));
	    	String name = cursor.getString(cursor.getColumnIndex("name"));
	    	String phonenum = cursor.getString(cursor.getColumnIndex("phonenum"));
	    	int amount = cursor.getInt(cursor.getColumnIndex("amount"));
	       Log.e(mytag, new contacts(contactsID, name, phonenum, amount).toString());
	    }
	    
	}
	 /*事务交易，因为事物交易是原子性的，所以需要不可用简单的数据更新来完成*/
	public void Paytransaction()
	{
		Dbservice db =new Dbservice(this.getContext());
		SQLiteDatabase sqdb = db.getWritableDatabase();
		sqdb.beginTransaction();{
		try
		{
        sqdb.execSQL("update contacts set amount=amount-10 where contactID=3");
        sqdb.execSQL("update contacts set amount=amount 10 where contactID=2");
        sqdb.setTransactionSuccessful();
		}
		catch(Exception e)
		{
			Log.e(mytag, "Sorry,transaction is failedn");
		}
		finally
		{
        sqdb.endTransaction();
		}
	}
	}
	
	}

在这里增加了一个事务操作，事物操作必须是原子性操作，否则会造出交易失败。

实验验证方法：通过单元测试，操作数据库，将应用程序下的数据库导出到桌面。然后用sqlite explore查看即可。

如：

java 单元测试 腾讯云测试服务 数据库 sqlite

0 人点赞