当前位置: 开发笔记 > 编程语言 > 正文

Android中SQLite技术实例详解

作者：倚栏听风 | 来源：互联网 | 2023-10-13 12:59

Android和iOS的数据库都是用SQLite来实现.一，SQLite数据库简介:轻量级：SQLite数据库是一个轻量级的数据库，适用于少量数据的CURD;文件本质:SQL

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 mWhiteListItem = new ArrayList();

@Override
protected 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();
  this.mWhiteListView = (ListView)findViewById(R.id.list_view);
  initData();
  this.mWhiteListView.setOnItemClickListener(new OnItemClickListener() {

    @Override
    public void onItemClick(AdapterView<&＃63;> 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();
}

@Override
protected 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 listItem;

  public WhiteListAdapter(Context context1,
      ArrayList 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;
}

// 创建数据库表
@Override
public 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();
}

@Override
public void onUpgrade(SQLiteDatabase paramSQLiteDatabase, int paramInt1, int paramInt2) {
  onCreate(paramSQLiteDatabase);
}

//查询方法
public ArrayList 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=&＃63;", 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;
 }  
 }

这是基本的数据程序。

XML

relative_main



  


  


dialog_editor

<&＃63;xml version="1.0" encoding="utf-8"&＃63;>

relative_list_item

dialog_delete

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

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

Android中SQLite技术实例详解

二维码的实现与应用

JUnit下的测试和suite

使用Service Locator模式实现高效的服务命名访问

Oracle SQL 第二天

MySQL 5.7 学习指南：SQLyog 中的主键、列属性和数据类型

Easyui + asp.net mvc + sqlite 开发教程（录屏）适合入门

使用TabActivity实现Android顶部选项卡功能

解决JavaScript中法语字符排序问题

mysql数据库json类型数据,sql server json数据类型

使用 SQLite3 命令将 CSV 文件数据高效导入数据库的方法与技巧

解决SQLiteStudio在连接Android实机时遇到的ADB连接故障问题的方法

没有这样的表android_metadata，有什么问题？ - No such table android_metadata, what's the problem?

无法在SQLite中插入，错误代码:19 - Unable to INSERT in SQLite, Error code:19

在Android Studio中查看SQLite数据库

小巧、快速、稳定！我为什么选择使用Sqlite?