android开发--sqlite的使用

android的文件存储上,sqlite也是重要的一环,它是本地数据库;在android开发环境eclipse中,当创建了sqlite数据库后可以在eclipse上用DDMS的file Explorer查看到,路径为data/data/项目包名/数据库名字.
接下来是做实现对sqlite的创建,对表的增删改查的操作实例:
大概android操作界面如下:

(1)实现界面布局:
3个EditText用来输入姓名,电话和地址,2个Button用来添加和修改联系人,最下面放置一个listview来显示数据库中存放的联系人信息
activity_main.xml文件配置如下:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.min.mysqlite.MainActivity" >

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="10dp"
        android:focusable="true"
        android:text="@string/contacts"
        android:textSize="20sp" >


    </TextView>

    <EditText
        android:id="@+id/et_name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="@string/name" >
    </EditText>

    <EditText
        android:id="@+id/et_phone"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="@string/phone"
        android:inputType="phone" />

    <EditText
        android:id="@+id/et_address"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="@string/address" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal" >

        <Button
            android:id="@+id/btn_add"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="@string/addper" />

        <Button
            android:id="@+id/btn_del"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="@string/update" />
    </LinearLayout>

    <ListView
        android:id="@+id/lv"
        android:focusable="true"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
    </ListView>
</LinearLayout>

(2)我们要使用sqlite,最好方法是继承SQLiteOpenHelper类,
DBHelper.java:

package com.min.mysqlite;

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

public class DBHelper extends SQLiteOpenHelper {
    private static final String DB_NAME_STRING = "mysqlite.db";
    private static final int VERSION_NUMBER = 1;
    //构造方法,确定数据库名字,版本
    public DBHelper(Context context) {
        super(context, DB_NAME_STRING, null, VERSION_NUMBER);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE  IF NOT EXISTS contacts"
                + "(_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,"
                + "phone TEXT,address TEXT)");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

}

当创建DBHelper对象时会调用oncreate方法,如果不存在contacts的表,则会新建名为contacts的表
(3)为了方便数据的管理,创建了一个类person用来封装_id,name,phone,address的数据
Person.java

package com.min.mysqlite;


public class Person {
    private int _id;
    private String name;
    private String phone;
    private String address;


    public int get_id() {
        return _id;
    }
    public void set_id(int _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;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public Person(String name, String phone, String address) {

        this.name = name;
        this.phone = phone;
        this.address = address;
    }

    public Person() {

    }



}

(4)创建DBManager.java来实现对表的增删改查

package com.min.mysqlite;

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 DBManager {
    private DBHelper helper;
    private SQLiteDatabase db;

    public DBManager(Context context) {
        helper = new DBHelper(context);
        db = helper.getWritableDatabase();
    }
    //添加联系人
    public void addPerson(Person person) {
        ContentValues cValues=new ContentValues();
        cValues.put("name", person.getName());
        cValues.put("phone", person.getPhone());
        cValues.put("address", person.getAddress());
        db.insert("contacts", null,cValues);
        //也可以用: db.execSQL("INSERT INTO contacts VALUES(null, ?,?,?)",new Object[]{person.getName(),person.getPhone(),person.getAddress()});
    }
    //通过名字删除
    public void delPerson(Person person){
        db.delete("contacts","name=?",new String[]{person.getName()});
    }
    //通过id删除
    public void delPerById(Person person){
        String s=Integer.toString(person.get_id());
        db.delete("contacts","_id=?",new String[]{s});
    }
    //更新联系人
    public void updatePerson(Person person){
        ContentValues cValues=new ContentValues();
        cValues.put("address",person.getAddress());
        cValues.put("phone",person.getPhone());
        db.update("contacts", cValues, "name=?", new String[]{person.getName()});

    }

    //查询操作,返回person集
    public List<Person> showDB(){
        List<Person> persons=new ArrayList<Person>();
        Cursor cursor=db.rawQuery("SELECT * FROM contacts", null);
        while (cursor.moveToNext()) {
            Person person=new Person();
            person.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
            person.setName(cursor.getString(cursor.getColumnIndex("name")));
            person.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
            person.setAddress(cursor.getString(cursor.getColumnIndex("address")));
            persons.add(person);

        }
        return persons;
    }

    //关闭数据库
    public void closeDB(){
        db.close();
    }


}

(5)MainActivity.java的代码:

package com.min.mysqlite;

import java.util.List;


import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;

public class MainActivity extends Activity {
    private EditText et_name;
    private EditText et_phone;
    private EditText et_address;
    private ListView lv;
    private List<Person> persons;
    private DBManager dbr;



    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        // init--------
        et_name = (EditText) findViewById(R.id.et_name);
        et_phone = (EditText) findViewById(R.id.et_phone);
        et_address = (EditText) findViewById(R.id.et_address);
        lv = (ListView) findViewById(R.id.lv);
        dbr=new DBManager(this);
        showList();
        // 添加按键的事件监听,调用addPerson()来将联系人信息插入到contacts表中
        findViewById(R.id.btn_add).setOnClickListener(
                new View.OnClickListener() {

                    @Override
                    public void onClick(View v) {
                        Person person=new Person();
                        person.setName(et_name.getText().toString());
                        person.setPhone(et_phone.getText().toString());
                        person.setAddress(et_address.getText().toString());
                        dbr.addPerson(person);
                        Toast.makeText(getApplicationContext(), "添加成功", Toast.LENGTH_SHORT).show();
                        showList();


                    }
                });
        //修改按键的事件监听,调用updatePerson()来将联系人信息更新到contacts表中
        findViewById(R.id.btn_update).setOnClickListener(
                new View.OnClickListener() {

                    @Override
                    public void onClick(View v) {
                        Person person=new Person();
                        person.setName(et_name.getText().toString());
                        person.setPhone(et_phone.getText().toString());
                        person.setAddress(et_address.getText().toString());
                        dbr.updatePerson(person);
                        Toast.makeText(getApplicationContext(), "修改成功", Toast.LENGTH_SHORT).show();
                        showList();

                    }
                });

        //点击listview对应的列表项来删除对应的数据
        lv.setOnItemClickListener(new OnItemClickListener() {

            @Override
            public void onItemClick(AdapterView<?> arg0, View arg1, int position,
                    long arg3) {

                Person person=persons.get(position);
                dbr.delPerById(person);
                Toast.makeText(getApplicationContext(), "已删除", Toast.LENGTH_SHORT).show();
                showList();

            }
        });


    }

    public  void  showList() {
        persons=dbr.showDB();
        MyAdapter mAdapter=new MyAdapter(this, persons);
        lv.setAdapter(mAdapter);
        mAdapter.notifyDataSetChanged();

    }
    @Override
    protected void onDestroy() {
    super.onDestroy();
        //当activity销毁时,关闭数据库操作
        dbr.closeDB();
    }
}

(6)最后是对listview设置adapter,继承自BaseAdapter:
MyAdapter.java:

package com.min.mysqlite;

import java.util.List;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;

public class MyAdapter extends BaseAdapter{
    private List<Person> persons; 
    private LayoutInflater layoutInflater;
    public MyAdapter(Context context,List<Person> persons) {
        this.persons=persons;
        layoutInflater=LayoutInflater.from(context);
    }
    @Override
    public int getCount() {
        // TODO Auto-generated method stub
        return persons.size();
    }

    @Override
    public Object getItem(int position) {

        return position;
    }

    @Override
    public long getItemId(int position) {

        return position;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        //定义ViewHolder类
        ViewHolder viewHolder=null;
        if (convertView==null) {
            //建立viewholder对象
            viewHolder=new ViewHolder();
            convertView=layoutInflater.inflate(R.layout.layout_base_adapter, null);
            viewHolder.tvname=(TextView) convertView.findViewById(R.id.tv_name);
            viewHolder.tvphone=(TextView) convertView.findViewById(R.id.tv_phone);
            viewHolder.tvaddress=(TextView) convertView.findViewById(R.id.tv_address);
            convertView.setTag(viewHolder);
        }else {
            viewHolder=(ViewHolder) convertView.getTag();
        }

        viewHolder.tvname.setText(persons.get(position).getName());
        viewHolder.tvphone.setText("电话: "+persons.get(position).getPhone());
        viewHolder.tvaddress.setText("地址: "+persons.get(position).getAddress());


        return convertView;
    }

    //定义ViewHolder静态类
    static class ViewHolder{
        public TextView tvname;
        public TextView tvphone;
        public TextView tvaddress;

    }

}

layout_base_adapter.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" 
    android:layout_marginTop="10dp">

    <TextView
        android:layout_gravity="center"
        android:id="@+id/tv_name"
        android:paddingLeft="10dp"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_weight="1"
        android:singleLine="true"
        android:ellipsize="end"
        android:textSize="16sp"
        android:text="@string/name" />

    <LinearLayout
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_weight="3"
        android:orientation="vertical" >

        <TextView
            android:id="@+id/tv_phone"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="@string/phone" />

        <TextView
            android:id="@+id/tv_address"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="@string/address" />
    </LinearLayout>

</LinearLayout>