Android 中SQLite技术实例详解

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。

实例代码：

import java.util.ArrayList;import java.util.Iterator;import com.hucc.huccgps.R;import android.app.Activity;import android.app.Dialog;import android.content.Context;import android.content.Intent;import android.content.SharedPreferences;import android.net.Uri;import android.os.Bundle;import android.util.Log;import android.view.LayoutInflater;import android.view.View;import android.view.View.OnClickListener;import android.view.ViewGroup;import android.view.Window;import android.widget.AdapterView;import android.widget.AdapterView.OnItemClickListener;import android.widget.BaseAdapter;import android.widget.Button;import android.widget.EditText;import android.widget.ImageView;import android.widget.ListView;import android.widget.TextView;import android.widget.Toast;public class MainActivity extends Activity {private static final String TAG = "JGPS/MainActivity";private TextView mAdd_white = null;private ListView mWhiteListView = null;private Context mContext = null;private int mPosition;private DBHelper mDBHelper = null;private WhiteListAdapter mAdapter = null;private ArrayList<Item> mWhiteListItem = new ArrayList<Item>();@Overrideprotected void onCreate(Bundle savedInstanceState) {  Log.d("JGPS/Start", "onCreate");  super.onCreate(savedInstanceState);  setContentView(R.layout.relative_main);  this.mDBHelper = new DBHelper(this);  this.mContext = this;  this.mWhiteListItem = new ArrayList<Item>();  this.mWhiteListView = (ListView)findViewById(R.id.list_view);  initData();  this.mWhiteListView.setOnItemClickListener(new OnItemClickListener() {    @Override    public void onItemClick(AdapterView<?> paramAdapterView, View paramView,         int paramInt, long paramLong) {      Log.d("JGPS/MainActivity", ""+paramInt);      MainActivity.this.updateWhiteList((Item)MainActivity.this.mWhiteListItem.get(paramInt));    }  });  if (this.mAdd_white == null)     return;  mAdd_white.setOnClickListener(new OnClickListener() {    @Override    public void onClick(View arg0) {      MainActivity.this.addContactList();    }  });  Log.d("JGPS/end", "onCreate");}private void initData() {  this.mWhiteListItem = this.mDBHelper.getRelativesList();  if (this.mWhiteListItem.size() != 3) {    for (int i = 1; i < 4; i++) {      Log.d("JGPS/MainActivity", "initData,id=" + i);      Item localItem1 = new Item();      localItem1.setName("");      localItem1.setNumber("");      addItemToList(localItem1);      updateSetting(i,"");            }    this.mWhiteListItem = this.mDBHelper.getRelativesList();  }  Iterator localIterator = this.mWhiteListItem.iterator();  while (localIterator.hasNext()) {    Item localItem2 = (Item) localIterator.next();    Log.d("JGPS/MainActivity", "initData,id=" + localItem2.getId() + ",name = " + localItem2.getName() + ",number=" + localItem2.getNumber());  }  this.mAdapter = new WhiteListAdapter(this, this.mWhiteListItem);  this.mWhiteListView.setAdapter(mAdapter);}private void updateSetting(int id, String phoneNum) {  switch (id) {  case 1:    putSting("phone_num1",phoneNum);    break;  case 2:    putSting("phone_num2",phoneNum);    break;  case 3:    putSting("phone_num3",phoneNum);    break;  default:    Log.d("JGPS/MainActivity", "Wrong relative number id");  }    }private void putSting(String paramString1, String paramString2) {  SharedPreferences.Editor localEditor = getSharedPreferences("sos_config", 0).edit();  localEditor.putString(paramString1, paramString2);  localEditor.apply();}protected void updateWhiteList(Item paramItem) {  final Item item = paramItem;  Log.d("JGPS/MainActivity", "updateWhiteList------" + item.getId());  final Dialog localDialog = new Dialog(this, R.style.dialog);  localDialog.show();  Window localWindow = localDialog.getWindow();  localWindow.setContentView(R.layout.dialog_editor);  Button localButton1 = (Button) localWindow.findViewById(R.id.btn_ok);  Button localButton2 = (Button) localWindow.findViewById(R.id.btn_cancel);  final EditText phone = (EditText)localWindow.findViewById(R.id.phone);  final EditText name = (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(new OnClickListener() {    @Override    public void onClick(View paramView) {      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());      int id = Integer.parseInt(item.getId());      updateSetting(id, phone.getText().toString());      Toast.makeText(mContext, R.string.edit_success, 2000).show();      localDialog.cancel();    }  });  localButton2.setOnClickListener(new OnClickListener() {    @Override    public void onClick(View paramView) {      localDialog.cancel();    }  });}protected void updateContactInDB(String paramString1, String paramString2, String paramString3) {  Item localItem = new Item();  localItem.setId(paramString1);  localItem.setName(paramString2);  localItem.setNumber(paramString3);  updateItemList(localItem);}private void updateItemList(Item paramItem) {  Item localItem2;  Iterator localIterator1 = 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 = new WhiteListAdapter(this, this.mWhiteListItem);  this.mWhiteListView.setAdapter(mAdapter);}private void addContactList() {  final Dialog dialog = new Dialog(this, R.style.dialog);  dialog.show();  Window window = dialog.getWindow();  window.setContentView(R.layout.dialog_editor);  Button btn1 = (Button)window.findViewById(R.id.btn_ok);   Button btn2 = (Button)window.findViewById(R.id.btn_cancel);   final EditText phone = (EditText)window.findViewById(R.id.phone);  final EditText name = (EditText)window.findViewById(R.id.name);  btn1.setOnClickListener(new OnClickListener() {    @Override    public void onClick(View arg0) {      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) {        String str = 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(new OnClickListener() {    @Override    public void onClick(View arg0) {      dialog.cancel();    }  });}protected void deleteContactFromDB(String paramString) {  updateContactInDB(paramString, "", "");  Log.d("JGPS/MainActivity", "===addItemToList = "+paramString);  int id = Integer.parseInt(paramString);  updateSetting(id, paramString);  Toast.makeText(this.mContext, R.string.delete_success, 2000).show();}@Overrideprotected void onStart() {  Log.d("JGPS/MainActivity", "onStart()");  super.onStart();}protected void startContactIntent(int paramInt) {  Log.d("JGPS/MainActivity", "onClick " + paramInt);  this.mPosition = paramInt;  Intent localIntent = new Intent("android.intent.action.PICK");  localIntent.setType("vnd.android.cursor.dir/phone_v2");  startActivityForResult(localIntent, 100);}protected void addContactToDB(String paramString1, String paramString2) {  Iterator localIterator = this.mWhiteListItem.iterator();  while (localIterator.hasNext()) {    if(((Item)localIterator.next()).getNumber().equals(paramString2)){       Toast.makeText(mContext, R.string.replace, 2000).show();       return;     }   }  Item localItem = new Item();  localItem.setName(paramString1);  localItem.setNumber(paramString2);  addItemToList(localItem);  Toast.makeText(mContext, R.string.replace, 2000).show();}private void addItemToList(Item localItem) {  Log.d("JGPS/MainActivity", "addItemToList,name = " + localItem.getName() + ",number=" + localItem.getNumber());  this.mDBHelper.addRelativesItem(localItem);  this.mWhiteListItem = this.mDBHelper.getRelativesList();  this.mAdapter = new WhiteListAdapter(this, this.mWhiteListItem);  this.mWhiteListView.setAdapter(mAdapter);}public void startActivityForResult(Intent paramIntent, int paramInt){ super.startActivityForResult(paramIntent, paramInt);}private class WhiteListAdapter extends BaseAdapter{  private Context context;  private LayoutInflater mInflater;  private ArrayList<Item> listItem;  public WhiteListAdapter(Context context1,      ArrayList<Item> whiteListItem) {    this.context = context1;    this.listItem = whiteListItem;    this.mInflater = LayoutInflater.from(context);  }  public int getCount()  {    if ((this.listItem !=null) && (this.listItem.size() > 0)) {      return 3;    }   return 0;  }  public Object getItem(int paramInt)  {   if ((this.listItem != null) && (this.listItem.size() > 0))    return this.listItem.get(paramInt);   return null;  }  public long getItemId(int paramInt)  {   return 0L;  }  @Override  public View getView(int paramInt, View paramView, ViewGroup paramViewGroup) {    Log.d("JGPS/MainActivity", "getView,position=" + paramInt + ",name=" + ((Item)this.listItem.get(paramInt)).getName() + ",num=" + ((Item)this.listItem.get(paramInt)).getNumber());    if (paramView == null) {      final ViewHolder localViewHolder = new ViewHolder();      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);        String str1 = MainActivity.this.getString(R.string.family_string);        String str2 = 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(new OnClickListener() {        @Override        public void onClick(View paramView) {          MainActivity.WhiteListAdapter.ViewHolder localViewHolder = (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(new OnClickListener() {        @Override        public void onClick(View paramView) {          Intent localIntent = new Intent("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(new OnClickListener() {        @Override        public void onClick(View paramView) {         if(paramView.getId() != R.id.imageView_delete)            return;         final String str = ((Item)MainActivity.WhiteListAdapter.this.listItem.get(localViewHolder.position)).getId();              Log.d("JGPS/MainActivity", "delete item onClick,strId=" + str + ",position=" + localViewHolder.position);         final Dialog localDialog = new Dialog(MainActivity.WhiteListAdapter.this.context, R.style.dialog);         localDialog.show();         Window localWindow = 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));         Button localButton1 = (Button) localWindow.findViewById(R.id.btn_ok);         Button localButton2 = (Button) localWindow.findViewById(R.id.btn_cancel);         localButton1.setOnClickListener(new OnClickListener() {          @Override          public void onClick(View paramView) {            MainActivity.this.deleteContactFromDB(str);            localDialog.cancel();          }        });         localButton2.setOnClickListener(new OnClickListener() {          @Override          public void onClick(View paramView) {            localDialog.cancel();          }        });        }      });    }else {    }    return paramView;  }  public class ViewHolder{    public ImageView ivCall;     public ImageView ivDelete;     public ImageView ivSelect;     public int position;     public TextView txtView_name;     public TextView txtView_tel;  }}}import java.util.ArrayList;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;public class DBHelper extends SQLiteOpenHelper {public static final String TABLE_RELATIVES_CONTENT = "RelativesContent";public static final int VERSION = 1; //版本public static final String dbName = "SosDB"; //数据库的名字public static Context mContext = null;public SQLiteDatabase db = null;public DBHelper(Context paramContext) {  super(paramContext, "SosDB", null, 1);  mContext = paramContext;}// 创建数据库表@Overridepublic void onCreate(SQLiteDatabase paramSQLiteDatabase) {  paramSQLiteDatabase.execSQL("create table RelativesContent(id integer primary key autoincrement,name text , number text)");  closeDatabase();}private void closeDatabase() {  if (this.db == null)     return;  this.db.close();}@Overridepublic void onUpgrade(SQLiteDatabase paramSQLiteDatabase, int paramInt1, int paramInt2) {  onCreate(paramSQLiteDatabase);}//查询方法public ArrayList<Item> getRelativesList() {  String idf = null;  String namef = null;  String numf = null;  ArrayList localArrayList = new ArrayList();  openDatabase();  Cursor localCursor = this.db.query("RelativesContent", null, null, null, null, null, null);  if(localCursor == null)    return localArrayList;  while (localCursor.moveToNext()) {    Item localItem = new Item();    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);  }      return localArrayList;}//写入数据private void openDatabase() {  this.db = super.getWritableDatabase();}//添加数据public boolean addRelativesItem(Item localItem) {  openDatabase();  ContentValues localContentValues = new ContentValues();  localContentValues.put("name", localItem.getName());  localContentValues.put("number", localItem.getNumber());  long l = this.db.insert("RelativesContent", null, localContentValues);  closeDatabase();  return (1<=0L);}//更新数据public boolean updateRelativesItem(Item paramItem) {  openDatabase();  ContentValues localContentValues = new ContentValues();  localContentValues.put("name", paramItem.getName());  localContentValues.put("number", paramItem.getNumber());  SQLiteDatabase localSQLiteDatabase = this.db;  String[] arrayOfString = new String[1];  arrayOfString[0] = paramItem.getId();  long l = localSQLiteDatabase.update("RelativesContent", localContentValues, "id=?", arrayOfString);  closeDatabase();  return (1 <= 0L);}}import java.io.Serializable;public class Item implements Serializable{private String _id;private String name;private String number;public Item() {  // TODO Auto-generated constructor stub}public void Item(String paramString1, String paramString2, String paramString3) {  this._id = paramString1;  this.name = paramString2;  this.number = paramString3; }public String getId() {  return _id;}public void setId(String paramString) {  this._id = paramString; }public String getName() {  return name;}public void setName(String paramString) {  this.name = paramString; }public String getNumber() {  return number;}public void setNumber(String paramString) {  this.number = paramString; }   }