Android 中SQLite技术实例详解

2024-02-18 00:42:03 36

Android和iOS的数据库都是用SQLite来实现.

一，SQLite数据库简介:

轻量级：SQLite数据库是一个轻量级的数据库，适用于少量数据的CURD;

文件本质:SQLite数据库支持大部分SQL语法，允许使用SQL语句操作数据库，其本质是一个文件，不需要安装启动。

数据读写：SQLite数据库打开只是一个文件的读写流。

二.简单的数据库语句知识

在android平台上，集成了一个嵌入式关系型数据库—SQLite,SQLite3支持NULL,INTEGER,REAL(浮点数字),TEXT(字符串文本)和BLOB(二进制对象)数据类型，实际上sqlite3也接受varchar(n),char(n),decimal(p,s)等数据类型，只不过在运算或保存时会转成对应的五种数据类型.

SQLite最大的特点是你可以把各种类型的数据保存到任何字段中，而不用关心字段声明的数据类型是什么。

今天我就用一个简单的列子来说明来实现SQLite。

实例代码：

importjava.util.ArrayList;
importjava.util.Iterator;

importcom.hucc.huccgps.R;

importandroid.app.Activity;
importandroid.app.Dialog;
importandroid.content.Context;
importandroid.content.Intent;
importandroid.content.SharedPreferences;
importandroid.net.Uri;
importandroid.os.Bundle;
importandroid.util.Log;
importandroid.view.LayoutInflater;
importandroid.view.View;
importandroid.view.View.OnClickListener;
importandroid.view.ViewGroup;
importandroid.view.Window;
importandroid.widget.AdapterView;
importandroid.widget.AdapterView.OnItemClickListener;
importandroid.widget.BaseAdapter;
importandroid.widget.Button;
importandroid.widget.EditText;
importandroid.widget.ImageView;
importandroid.widget.ListView;
importandroid.widget.TextView;
importandroid.widget.Toast;

publicclassMainActivityextendsActivity{

privatestaticfinalStringTAG="JGPS/MainActivity";
privateTextViewmAdd_white=null;
privateListViewmWhiteListView=null;
privateContextmContext=null;

privateintmPosition;
privateDBHelpermDBHelper=null;
privateWhiteListAdaptermAdapter=null;
privateArrayListmWhiteListItem=newArrayList();

@Override
protectedvoidonCreate(BundlesavedInstanceState){
Log.d("JGPS/Start","onCreate");
super.onCreate(savedInstanceState);
setContentView(R.layout.relative_main);
this.mDBHelper=newDBHelper(this);
this.mContext=this;
this.mWhiteListItem=newArrayList();
this.mWhiteListView=(ListView)findViewById(R.id.list_view);
initData();
this.mWhiteListView.setOnItemClickListener(newOnItemClickListener(){

@Override
publicvoidonItemClick(AdapterViewparamAdapterView,ViewparamView,
intparamInt,longparamLong){
Log.d("JGPS/MainActivity",""+paramInt);
MainActivity.this.updateWhiteList((Item)MainActivity.this.mWhiteListItem.get(paramInt));
}
});
if(this.mAdd_white==null)
return;

mAdd_white.setOnClickListener(newOnClickListener(){

@Override
publicvoidonClick(Viewarg0){
MainActivity.this.addContactList();
}
});

Log.d("JGPS/end","onCreate");
}

privatevoidinitData(){
this.mWhiteListItem=this.mDBHelper.getRelativesList();
if(this.mWhiteListItem.size()!=3){
for(inti=1;i<4;i++){
Log.d("JGPS/MainActivity","initData,id="+i);
ItemlocalItem1=newItem();
localItem1.setName("");
localItem1.setNumber("");
addItemToList(localItem1);
updateSetting(i,"");
}
this.mWhiteListItem=this.mDBHelper.getRelativesList();
}
IteratorlocalIterator=this.mWhiteListItem.iterator();
while(localIterator.hasNext()){
ItemlocalItem2=(Item)localIterator.next();
Log.d("JGPS/MainActivity","initData,id="+localItem2.getId()+",name="+localItem2.getName()+",number="+localItem2.getNumber());
}
this.mAdapter=newWhiteListAdapter(this,this.mWhiteListItem);
this.mWhiteListView.setAdapter(mAdapter);
}

privatevoidupdateSetting(intid,StringphoneNum){
switch(id){
case1:
putSting("phone_num1",phoneNum);
break;
case2:
putSting("phone_num2",phoneNum);
break;
case3:
putSting("phone_num3",phoneNum);
break;
default:
Log.d("JGPS/MainActivity","Wrongrelativenumberid");
}
}

privatevoidputSting(StringparamString1,StringparamString2){
SharedPreferences.EditorlocalEditor=getSharedPreferences("sos_config",0).edit();
localEditor.putString(paramString1,paramString2);
localEditor.apply();
}

protectedvoidupdateWhiteList(ItemparamItem){
finalItemitem=paramItem;
Log.d("JGPS/MainActivity","updateWhiteList------"+item.getId());
finalDialoglocalDialog=newDialog(this,R.style.dialog);
localDialog.show();
WindowlocalWindow=localDialog.getWindow();
localWindow.setContentView(R.layout.dialog_editor);
ButtonlocalButton1=(Button)localWindow.findViewById(R.id.btn_ok);
ButtonlocalButton2=(Button)localWindow.findViewById(R.id.btn_cancel);
finalEditTextphone=(EditText)localWindow.findViewById(R.id.phone);
finalEditTextname=(EditText)localWindow.findViewById(R.id.name);
name.setText(item.getName());
phone.setText(item.getNumber());
((TextView)localWindow.findViewById(R.id.title)).setText(R.string.edit_white);
localButton1.setOnClickListener(newOnClickListener(){

@Override
publicvoidonClick(ViewparamView){
if(phone.getText().toString().trim().length()==0){
Toast.makeText(mContext,R.string.toast_tel,2000).show();
return;
}
if(name.getText().toString().trim().length()==0){
Toast.makeText(mContext,R.string.name_null,2000).show();
return;
}
MainActivity.this.updateContactInDB(item.getId(),name.getText().toString(),phone.getText().toString());
intid=Integer.parseInt(item.getId());
updateSetting(id,phone.getText().toString());
Toast.makeText(mContext,R.string.edit_success,2000).show();
localDialog.cancel();
}
});
localButton2.setOnClickListener(newOnClickListener(){

@Override
publicvoidonClick(ViewparamView){
localDialog.cancel();
}
});
}

protectedvoidupdateContactInDB(StringparamString1,StringparamString2,StringparamString3){
ItemlocalItem=newItem();
localItem.setId(paramString1);
localItem.setName(paramString2);
localItem.setNumber(paramString3);
updateItemList(localItem);
}

privatevoidupdateItemList(ItemparamItem){
ItemlocalItem2;
IteratorlocalIterator1=this.mWhiteListItem.iterator();
while(localIterator1.hasNext()){
localItem2=(Item)localIterator1.next();
if(localItem2.getId().equals(paramItem.getId())){
localItem2.setName(paramItem.getName());
localItem2.setNumber(paramItem.getNumber());
this.mDBHelper.updateRelativesItem(paramItem);
}
}

Log.d("JGPS/MainActivity","updateItemInList---------");
this.mAdapter=newWhiteListAdapter(this,this.mWhiteListItem);
this.mWhiteListView.setAdapter(mAdapter);
}

privatevoidaddContactList(){
finalDialogdialog=newDialog(this,R.style.dialog);
dialog.show();
Windowwindow=dialog.getWindow();
window.setContentView(R.layout.dialog_editor);

Buttonbtn1=(Button)window.findViewById(R.id.btn_ok);
Buttonbtn2=(Button)window.findViewById(R.id.btn_cancel);
finalEditTextphone=(EditText)window.findViewById(R.id.phone);
finalEditTextname=(EditText)window.findViewById(R.id.name);
btn1.setOnClickListener(newOnClickListener(){

@Override
publicvoidonClick(Viewarg0){
if(phone.getText().toString().trim().length()==0)
{

Toast.makeText(mContext,R.string.toast_tel,2000).show();
return;
}
if(name.getText().toString().trim().length()==0)
{
Toast.makeText(mContext,R.string.name_null,2000).show();
return;
}
if(MainActivity.this.mWhiteListItem.size()>3){
Stringstr=MainActivity.this.getResources().getString(R.string.full1)+""+3+""+MainActivity.this.getResources().getString(R.string.full2);
Toast.makeText(mContext,str,2000).show();
dialog.cancel();
return;
}
MainActivity.this.addContactToDB(name.getText().toString(),phone.getText().toString());
dialog.cancel();
}
});
btn2.setOnClickListener(newOnClickListener(){

@Override
publicvoidonClick(Viewarg0){
dialog.cancel();
}
});
}

protectedvoiddeleteContactFromDB(StringparamString){
updateContactInDB(paramString,"","");
Log.d("JGPS/MainActivity","===addItemToList="+paramString);
intid=Integer.parseInt(paramString);
updateSetting(id,paramString);
Toast.makeText(this.mContext,R.string.delete_success,2000).show();
}

@Override
protectedvoidonStart(){
Log.d("JGPS/MainActivity","onStart()");
super.onStart();
}

protectedvoidstartContactIntent(intparamInt){
Log.d("JGPS/MainActivity","onClick"+paramInt);
this.mPosition=paramInt;
IntentlocalIntent=newIntent("android.intent.action.PICK");
localIntent.setType("vnd.android.cursor.dir/phone_v2");
startActivityForResult(localIntent,100);
}

protectedvoidaddContactToDB(StringparamString1,StringparamString2){
IteratorlocalIterator=this.mWhiteListItem.iterator();

while(localIterator.hasNext()){
if(((Item)localIterator.next()).getNumber().equals(paramString2)){
Toast.makeText(mContext,R.string.replace,2000).show();
return;
}
}
ItemlocalItem=newItem();
localItem.setName(paramString1);
localItem.setNumber(paramString2);
addItemToList(localItem);
Toast.makeText(mContext,R.string.replace,2000).show();
}

privatevoidaddItemToList(ItemlocalItem){
Log.d("JGPS/MainActivity","addItemToList,name="+localItem.getName()+",number="+localItem.getNumber());
this.mDBHelper.addRelativesItem(localItem);
this.mWhiteListItem=this.mDBHelper.getRelativesList();
this.mAdapter=newWhiteListAdapter(this,this.mWhiteListItem);
this.mWhiteListView.setAdapter(mAdapter);
}

publicvoidstartActivityForResult(IntentparamIntent,intparamInt)
{
super.startActivityForResult(paramIntent,paramInt);
}

privateclassWhiteListAdapterextendsBaseAdapter{

privateContextcontext;
privateLayoutInflatermInflater;
privateArrayListlistItem;

publicWhiteListAdapter(Contextcontext1,
ArrayListwhiteListItem){
this.context=context1;
this.listItem=whiteListItem;
this.mInflater=LayoutInflater.from(context);
}

publicintgetCount()
{
if((this.listItem!=null)&&(this.listItem.size()>0)){
return3;
}
return0;
}

publicObjectgetItem(intparamInt)
{
if((this.listItem!=null)&&(this.listItem.size()>0))
returnthis.listItem.get(paramInt);
returnnull;
}

publiclonggetItemId(intparamInt)
{
return0L;
}

@Override
publicViewgetView(intparamInt,ViewparamView,ViewGroupparamViewGroup){
Log.d("JGPS/MainActivity","getView,position="+paramInt+",name="+((Item)this.listItem.get(paramInt)).getName()+",num="+((Item)this.listItem.get(paramInt)).getNumber());
if(paramView==null){
finalViewHolderlocalViewHolder=newViewHolder();
paramView=this.mInflater.inflate(R.layout.relative_list_item,null);
localViewHolder.ivCall=(ImageView)paramView.findViewById(R.id.imageView_call);
localViewHolder.ivDelete=(ImageView)paramView.findViewById(R.id.imageView_delete);
localViewHolder.ivSelect=(ImageView)paramView.findViewById(R.id.recipients_picker);
localViewHolder.txtView_name=(TextView)paramView.findViewById(R.id.txtView_name);
localViewHolder.txtView_tel=(TextView)paramView.findViewById(R.id.txtView_number);

paramView.setTag(localViewHolder);
localViewHolder.position=paramInt;
localViewHolder.ivSelect.setTag(localViewHolder);

if((this.listItem.get(paramInt)==null)||
(((Item)this.listItem.get(paramInt)).getNumber()==null)
||(((Item)this.listItem.get(paramInt)).getNumber().equals(""))){
localViewHolder.ivCall.setVisibility(View.GONE);
localViewHolder.ivSelect.setVisibility(View.VISIBLE);
Stringstr1=MainActivity.this.getString(R.string.family_string);
Stringstr2=str1+(paramInt+1)+":";
localViewHolder.txtView_name.setText(str2);
localViewHolder.txtView_tel.setText(R.string.click_to_add);
}else{
localViewHolder.ivCall.setVisibility(View.VISIBLE);
localViewHolder.ivSelect.setVisibility(View.GONE);
localViewHolder.txtView_name.setText(((Item)this.listItem.get(paramInt)).getName());
localViewHolder.txtView_tel.setText(((Item)this.listItem.get(paramInt)).getNumber());
}

localViewHolder.ivSelect.setOnClickListener(newOnClickListener(){

@Override
publicvoidonClick(ViewparamView){
MainActivity.WhiteListAdapter.ViewHolderlocalViewHolder=(MainActivity.WhiteListAdapter.ViewHolder)paramView.getTag();
Log.d("JGPS/MainActivity","getView,mPosition="+MainActivity.this.mPosition);

MainActivity.this.startContactIntent(localViewHolder.position);
}
});

if((!(((Item)this.listItem.get(paramInt)).getName().equals("")))
||(!(((Item)this.listItem.get(paramInt)).getNumber().equals(""))))
{
localViewHolder.ivDelete.setVisibility(View.VISIBLE);
}else{
localViewHolder.ivDelete.setVisibility(View.GONE);
}

localViewHolder.ivCall.setOnClickListener(newOnClickListener(){

@Override
publicvoidonClick(ViewparamView){
IntentlocalIntent=newIntent("android.intent.action.CALL",
Uri.parse("tel:"+((Item)MainActivity.WhiteListAdapter.this.listItem.get(localViewHolder.position)).getNumber()));
MainActivity.WhiteListAdapter.this.context.startActivity(localIntent);
}
});

localViewHolder.ivDelete.setOnClickListener(newOnClickListener(){

@Override
publicvoidonClick(ViewparamView){
if(paramView.getId()!=R.id.imageView_delete)
return;
finalStringstr=((Item)MainActivity.WhiteListAdapter.this.listItem.get(localViewHolder.position)).getId();
Log.d("JGPS/MainActivity","deleteitemonClick,strId="+str+",position="+localViewHolder.position);

finalDialoglocalDialog=newDialog(MainActivity.WhiteListAdapter.this.context,R.style.dialog);
localDialog.show();
WindowlocalWindow=localDialog.getWindow();
localWindow.setContentView(R.layout.dialog_delete);
((TextView)localWindow.findViewById(R.id.txtView_delete)).setText(MainActivity.WhiteListAdapter.this.context.getResources().getString(R.string.delete_one)
+"'"+((Item)MainActivity.WhiteListAdapter.this.listItem.get(localViewHolder.position)).getName()
+"'"+MainActivity.WhiteListAdapter.this.context.getResources().getString(R.string.delete_two));
ButtonlocalButton1=(Button)localWindow.findViewById(R.id.btn_ok);
ButtonlocalButton2=(Button)localWindow.findViewById(R.id.btn_cancel);
localButton1.setOnClickListener(newOnClickListener(){

@Override
publicvoidonClick(ViewparamView){
MainActivity.this.deleteContactFromDB(str);
localDialog.cancel();
}
});
localButton2.setOnClickListener(newOnClickListener(){

@Override
publicvoidonClick(ViewparamView){
localDialog.cancel();
}
});
}
});

}else{

}

returnparamView;
}

publicclassViewHolder{
publicImageViewivCall;
publicImageViewivDelete;
publicImageViewivSelect;
publicintposition;
publicTextViewtxtView_name;
publicTextViewtxtView_tel;
}
}
}


importjava.util.ArrayList;

importandroid.content.ContentValues;
importandroid.content.Context;
importandroid.database.Cursor;
importandroid.database.sqlite.SQLiteDatabase;
importandroid.database.sqlite.SQLiteOpenHelper;
importandroid.util.Log;

publicclassDBHelperextendsSQLiteOpenHelper{

publicstaticfinalStringTABLE_RELATIVES_CONTENT="RelativesContent";
publicstaticfinalintVERSION=1;//版本
publicstaticfinalStringdbName="SosDB";//数据库的名字
publicstaticContextmContext=null;
publicSQLiteDatabasedb=null;

publicDBHelper(ContextparamContext){
super(paramContext,"SosDB",null,1);
mContext=paramContext;
}

//创建数据库表
@Override
publicvoidonCreate(SQLiteDatabaseparamSQLiteDatabase){
paramSQLiteDatabase.execSQL("createtableRelativesContent(idintegerprimarykeyautoincrement,nametext,numbertext)");
closeDatabase();
}

privatevoidcloseDatabase(){
if(this.db==null)
return;
this.db.close();
}

@Override
publicvoidonUpgrade(SQLiteDatabaseparamSQLiteDatabase,intparamInt1,intparamInt2){
onCreate(paramSQLiteDatabase);
}

//查询方法
publicArrayListgetRelativesList(){
Stringidf=null;
Stringnamef=null;
Stringnumf=null;
ArrayListlocalArrayList=newArrayList();
openDatabase();
CursorlocalCursor=this.db.query("RelativesContent",null,null,null,null,null,null);
if(localCursor==null)
returnlocalArrayList;
while(localCursor.moveToNext()){
ItemlocalItem=newItem();
idf=localCursor.getString(localCursor.getColumnIndex("id"));
namef=localCursor.getString(localCursor.getColumnIndex("name"));
numf=localCursor.getString(localCursor.getColumnIndex("number"));
localItem.setId(idf);
Log.d("JGPS/MainActivity","dbid="+idf);
if((namef==null)||("".equals(namef))){
localItem.setName("");
}else{
localItem.setName(namef);
}
if((numf==null)||("".equals(numf))){
localItem.setNumber("");
}else{
localItem.setNumber(numf);
}
localArrayList.add(localItem);
}
returnlocalArrayList;
}

//写入数据
privatevoidopenDatabase(){
this.db=super.getWritableDatabase();
}

//添加数据
publicbooleanaddRelativesItem(ItemlocalItem){
openDatabase();
ContentValueslocalContentValues=newContentValues();
localContentValues.put("name",localItem.getName());
localContentValues.put("number",localItem.getNumber());
longl=this.db.insert("RelativesContent",null,localContentValues);
closeDatabase();
return(1<=0L);
}

//更新数据
publicbooleanupdateRelativesItem(ItemparamItem){
openDatabase();
ContentValueslocalContentValues=newContentValues();
localContentValues.put("name",paramItem.getName());
localContentValues.put("number",paramItem.getNumber());
SQLiteDatabaselocalSQLiteDatabase=this.db;
String[]arrayOfString=newString[1];
arrayOfString[0]=paramItem.getId();
longl=localSQLiteDatabase.update("RelativesContent",localContentValues,"id=?",arrayOfString);
closeDatabase();
return(1<=0L);
}

}


importjava.io.Serializable;

publicclassItemimplementsSerializable{

privateString_id;
privateStringname;
privateStringnumber;

publicItem(){
//TODOAuto-generatedconstructorstub
}

publicvoidItem(StringparamString1,StringparamString2,StringparamString3)
{
this._id=paramString1;
this.name=paramString2;
this.number=paramString3;
}

publicStringgetId(){
return_id;
}

publicvoidsetId(StringparamString)
{
this._id=paramString;
}

publicStringgetName(){
returnname;
}

publicvoidsetName(StringparamString)
{
this.name=paramString;
}

publicStringgetNumber(){
returnnumber;
}

publicvoidsetNumber(StringparamString)
{
this.number=paramString;
}
}

这是基本的数据程序。

XML

relative_main









dialog_editor

relative_list_item

dialog_delete

这是一个简单的demo，我这里就不详细说明数据库的细节了，对于已经很熟悉数据库的，可以查看android官网有说明的，

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

Android 中SQLite技术实例详解

热门推荐

随机推荐