实验8 SQLite数据库操作

实验地点

S30010

实验类型

□验证型    √设计型    □综合型

学  时

2

一、实验目的及要求（本实验所涉及并要求掌握的知识点）

目的：设计一个个人通讯录，掌握Android平台下的数据库开发，该个人通讯录主要包括联系人列表和联系人详细信息等界面。

 

要求：程序主界面是通讯录的目录显示手机上联系人的名称。点击联系人的姓名可以显示联系人的详细信息。在按了MEMU键之后会弹出菜单栏。单击菜单栏上的按钮可以添加联系人和删除联系人。

二、实验环境（本实验所使用的硬件设备和相关软件）

(1)PC机

(2)操作系统：Windows XP

(3)软件： Eclipse, JDK1.6，Android SDK，ADT

三、实验内容及步骤

（1）确定数据库的数据结构。本程序只要一张表，该表的内容及说明如下表所示

 

 

字段名称	数据类型	说明	字段名称	数据类型	声明
_id	Integer	所插入记录的编号	name	varchar	联系人名称
phone	Varchar	联系人的固定电话	mobile	varchar	手机号码
Email	Varchar	联系人的邮箱的地址	post	varchar	联系人固话
addr	varchar	联系认的地址	comp	varchar	联系人所在地

 

（2） 在res/drawable-mdpi目录下拷入程序要用的图标

1）新建工程

 2）修改布局文件activity_main.xml

(3)定义字符串资源string.xml

(4)开发布局文件activity_main.xml用于显示联系人列表。

（5）layout目录下新建一个activity_person_message.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="match_parent"

    android:orientation="vertical" >

 

    <LinearLayout

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:orientation="horizontal" >

 

        <TextView

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="@string/tvName"

            android:textSize="20sp" />

 

        <EditText

            android:id="@+id/edName"

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:textSize="25sp" />

    </LinearLayout>

 

    <LinearLayout

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:orientation="horizontal" >

 

        <TextView

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="@string/tvPhone"

            android:textSize="20sp" />

 

        <EditText

            android:id="@+id/edPhone"

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:textSize="25sp" />

    </LinearLayout>

 

    <LinearLayout

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:orientation="horizontal" >

 

        <TextView

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="@string/tvMobile"

            android:textSize="20sp" />

 

        <EditText

             android:id="@+id/edMobile"

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:textSize="25sp" />

    </LinearLayout>

 

    <LinearLayout

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:orientation="horizontal" >

 

        <TextView

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="@string/tvEmail"

            android:textSize="20sp" />

 

        <EditText

            android:id="@+id/edEmail"

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:textSize="25sp" />

    </LinearLayout>

 

    <LinearLayout

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:orientation="horizontal" >

 

        <TextView

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="@string/tvPost"

            android:textSize="20sp" />

 

        <EditText

             android:id="@+id/edPost"

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:textSize="25sp" />

    </LinearLayout>

 

    <LinearLayout

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:orientation="horizontal" >

 

        <TextView

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="@string/tvAddr"

            android:textSize="20sp" />

 

        <EditText

             android:id="@+id/edAddr"

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:textSize="25sp" />

    </LinearLayout>

 

    <LinearLayout

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:orientation="horizontal" >

 

        <TextView

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="@string/tvComp"

            android:textSize="20sp" />

 

        <EditText

            android:id="@+id/edComp"

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:textSize="25sp" />

    </LinearLayout>

<ImageButton android:id="@+id/baocun"

    android:layout_width="fill_parent"

    android:layout_height="wrap_content"

    android:src="@drawable/baocun"

    />

</LinearLayout>

 (3)开发数据库辅助类ContactOpenHelper类，新建一个ContactOpenHelper.java。

（4）接下来便进入MainActivity端的开发，实现数据库增加、删除、修改记录等操作

（5）新建一个Activity名字叫PersonMessage.java，实现联系人详细信息显示功能。

 

 

四、实验结果（本实验源程序清单及运行结果或实验结论、实验设计图）

 

 

代码：

MainActivity.Java

public class MainActivity extends Activity {

   private ContactOpenHelper dbHelper;

   private ListView lv;

   private List<Integer> id = new ArrayList<Integer>();

 

   @Override

   protected void onCreate(Bundle savedInstanceState) {

       super.onCreate(savedInstanceState);

       setContentView(R.layout.activity_main);

       // 创建数据库

       dbHelper = new ContactOpenHelper(MainActivity.this, "contact.db", null,

              1);

       final SQLiteDatabase db = dbHelper.getReadableDatabase();

       // 查询数据

       Cursor cursor = db.rawQuery("select * from contactTable", null);

       List<String> strs = new ArrayList<String>();

 

       while (cursor.moveToNext()) {

          strs.add(cursor.getString(cursor.getColumnIndex("name")));

          id.add(cursor.getInt(cursor.getColumnIndex("_id")));

 

       }

       // 加载联系人列表

       lv = (ListView) findViewById(R.id.listView1);

       lv.setAdapter(new ArrayAdapter<String>(MainActivity.this,

              android.R.layout.simple_expandable_list_item_1, strs));

       lv.setChoiceMode(ListView.CHOICE_MODE_SINGLE);

       lv.setOnItemClickListener(new OnItemClickListener() {

 

          @Override

          public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,

                 long arg3) {

              // TODO Auto-generated method stub

//            点击了某个联系人后传递id给详细页面

              Intent intent = new Intent(MainActivity.this,

                     PersonMessage.class);

              Bundle bundle = new Bundle();

              bundle.putInt("id", id.get(arg2));

              intent.putExtras(bundle);

              startActivity(intent);

              finish();

          }

       });

       cursor.close();

       db.close();

   }

 

   @Override

   public boolean onCreateOptionsMenu(Menu menu) {

       // Inflate the menu; this adds items to the action bar if it is present.

       getMenuInflater().inflate(R.menu.main, menu);

       return true;

   }

 

   @Override

   public boolean onOptionsItemSelected(MenuItem item) {

       // TODO Auto-generated method stub

       Intent intent;

       switch (item.getItemId()) {

       case R.id.add:

 

          intent = new Intent(MainActivity.this, AddContactsActivity.class);

          startActivity(intent);

          Log.i("tianjia", "添加");

          finish();

          break;

       case R.id.delete:

          intent = new Intent(MainActivity.this, DeleteContactsActivity.class);

          startActivity(intent);

          Log.i("shanchu", "删除");

          finish();

          break;

       default:

          break;

       }

       return super.onOptionsItemSelected(item);

   }

}

DeleteContactsActivity.java

public class DeleteContactsActivity extends Activity {

   private ContactOpenHelper dbHelper;

   private ListView lv;

   private Button deletebt;

  

 

   @Override

   protected void onCreate(Bundle savedInstanceState) {

       super.onCreate(savedInstanceState);

       setContentView(R.layout.activity_delete_contacts);

       deletebt = (Button) findViewById(R.id.deleteBt);

       // 创建数据库

       dbHelper = new ContactOpenHelper(DeleteContactsActivity.this,

              "contact.db", null, 1);

       final SQLiteDatabase db = dbHelper.getReadableDatabase();

       // 查询数据

       Cursor cursor = db.rawQuery("select * from contactTable", null);

       Log.i("name", cursor.toString());

       /* 定义一个动态数组 */

 

       ArrayList<HashMap<String, Object>> listItem = new ArrayList<HashMap<String, Object>>();

       while (cursor.moveToNext()) {

          HashMap<String, Object> map = new HashMap<String, Object>();

          map.put("name", cursor.getString(cursor.getColumnIndex("name")));

          map.put("id", cursor.getInt(cursor.getColumnIndex("_id")));

          listItem.add(map);

 

       }

       // 加载联系人列表

       lv = (ListView) findViewById(R.id.listView2);

      

       SimpleAdapter mSimpleAdapter = new SimpleAdapter(this, listItem,

 

       R.layout.item, new String[] { "name"

 

       , "id"}, new int[] { R.id.tvItem, R.id.checkboxItem

               });

 

       lv.setAdapter(mSimpleAdapter);// 为ListView绑定适配器

      

       lv.setChoiceMode(ListView.CHOICE_MODE_MULTIPLE);

      

       cursor.close();

       deletebt.setOnClickListener(new OnClickListener() {

 

          @Override

          public void onClick(View arg0) {

              // TODO Auto-generated method stub

//            遍历所有控件得到被选中的checkbox，并删除选中的数据

              for (int i = 0, len = lv.getChildCount(); i < len; i++) {

                 View child = lv.getChildAt(i);

                 for (int j = 0, zilen = ((LinearLayout) child)

                        .getChildCount(); j < zilen; j++) {

                     View zichild = ((LinearLayout) child).getChildAt(j);

                     if (zichild instanceof CheckBox) {

                        if (((CheckBox) zichild).isChecked()) {

                            Log.i("check", ((CheckBox) zichild).getText()

                                   .toString());

                            db.execSQL("delete from contactTable where _id=‘"

                                   + Integer.parseInt(((CheckBox) zichild)

                                          .getText().toString()) + "‘");

 

                           

 

                            Intent intent = new Intent(

                                   DeleteContactsActivity.this,

                                   MainActivity.class);

                            startActivity(intent);

                            finish();

                        }

                     }

                 }

 

              }

              db.close();

          }

       });

 

   }

}

 

AddContactsActivity.java

public class AddContactsActivity extends Activity {

   private EditText edName;

   private EditText edPhone;

   private EditText edMobile;

   private EditText edEmail;

   private EditText edPost;

   private EditText edAddr;

   private EditText edComp;

   private ImageButton btbaocun;

   private ContactOpenHelper dbHelper;

 

   @Override

   protected void onCreate(Bundle savedInstanceState) {

       super.onCreate(savedInstanceState);

       setContentView(R.layout.activity_person_message);

       edName = (EditText) findViewById(R.id.edName);

       edPhone = (EditText) findViewById(R.id.edPhone);

       edMobile = (EditText) findViewById(R.id.edMobile);

       edEmail = (EditText) findViewById(R.id.edEmail);

       edPost = (EditText) findViewById(R.id.edPost);

       edAddr = (EditText) findViewById(R.id.edAddr);

       edComp = (EditText) findViewById(R.id.edComp);

       btbaocun = (ImageButton) findViewById(R.id.baocun);

       btbaocun.setOnClickListener(new OnClickListener() {

 

          @Override

          public void onClick(View arg0) {

              // TODO Auto-generated method stub

              // 创建数据库

              dbHelper = new ContactOpenHelper(AddContactsActivity.this,

                     "contact.db", null, 1);

              final SQLiteDatabase db = dbHelper.getReadableDatabase();

              ContentValues values = new ContentValues();

              // 开始填数据

              values.put("name", edName.getText().toString());

              values.put("phone", edPhone.getText().toString());

              values.put("mobile", edMobile.getText().toString());

              values.put("email", edEmail.getText().toString());

              values.put("post", edPost.getText().toString());

              values.put("addr", edAddr.getText().toString());

              values.put("comp", edComp.getText().toString());

              db.insert("contactTable", null, values);

              values.clear();

              Toast.makeText(AddContactsActivity.this, "添加成功",

                     Toast.LENGTH_LONG).show();

              Intent intent = new Intent(AddContactsActivity.this,

                     MainActivity.class);

              startActivity(intent);

              finish();

          }

       });

   }

 

}

PersonMessage.java

public class PersonMessage extends Activity {

   private ContactOpenHelper dbHelper;

   private EditText edName;

   private EditText edPhone;

   private EditText edMobile;

   private EditText edEmail;

   private EditText edPost;

   private EditText edAddr;

   private EditText edComp;

   private ImageButton btbaocun;

   @Override

   protected void onCreate(Bundle savedInstanceState) {

       super.onCreate(savedInstanceState);

       setContentView(R.layout.activity_person_message);

       // 创建数据库

              dbHelper = new ContactOpenHelper(PersonMessage.this, "contact.db", null,

                     1);

              final SQLiteDatabase db = dbHelper.getReadableDatabase();

              // 查询数据

              final Cursor cursor = db.rawQuery("select * from contactTable where _id="+getIntent().getExtras().getInt("id"), null);

             

              List<String> strs = new ArrayList<String>();

 

              cursor.moveToNext();

                

 

             

             

              edName=(EditText) findViewById(R.id.edName);

              edPhone=(EditText) findViewById(R.id.edPhone);

              edMobile=(EditText) findViewById(R.id.edMobile);

              edEmail=(EditText) findViewById(R.id.edEmail);

              edPost=(EditText) findViewById(R.id.edPost);

              edAddr=(EditText) findViewById(R.id.edAddr);

              edComp=(EditText) findViewById(R.id.edComp);

//            显示详细信息

              edName.setText(cursor.getString(cursor.getColumnIndex("name")));

              edPhone.setText(cursor.getString(cursor.getColumnIndex("phone")));

              edMobile.setText(cursor.getString(cursor.getColumnIndex("mobile")));

              edEmail.setText(cursor.getString(cursor.getColumnIndex("email")));

              edPost.setText(cursor.getString(cursor.getColumnIndex("post")));

              edAddr.setText(cursor.getString(cursor.getColumnIndex("addr")));

              edComp.setText(cursor.getString(cursor.getColumnIndex("comp")));

              btbaocun=(ImageButton) findViewById(R.id.baocun);

             

              btbaocun.setOnClickListener(new OnClickListener() {

 

                 @Override

                 public void onClick(View arg0) {

                     // TODO Auto-generated method stub

                    

              ContentValues cvContentValues= new ContentValues();

              cvContentValues.put("name",edName.getText().toString() );

              cvContentValues.put("phone", edPhone.getText().toString());

              cvContentValues.put("mobile", edMobile.getText().toString());

              cvContentValues.put("email", edEmail.getText().toString());

              cvContentValues.put("post", edPost.getText().toString());

              cvContentValues.put("addr", edAddr.getText().toString());

              cvContentValues.put("comp", edComp.getText().toString());

              String[] s={cursor.getString(cursor.getColumnIndex("_id"))};

              //db.execSQL("update contactTable set name=‘"+edName.getText().toString()+"‘" +" where _id=‘"+cursor.getString(cursor.getColumnIndex("_id"))+"‘");

                     db.update("contactTable", cvContentValues, "_id=?", s);

                    

                     Toast.makeText(PersonMessage.this,

                            "修改成功" ,

                            Toast.LENGTH_LONG).show();

                     cursor.close();

                     db.close();

                     Intent intent = new Intent(PersonMessage.this,

                            MainActivity.class);

                     startActivity(intent);

                     finish();

                 }

              });

   }

}

代码较多，就不粘贴全部了

运行结果：（截图）

 

 

 

五、实验总结（对本实验结果进行分析，实验心得体会及改进意见）

    此次实验，添加联系人和整个框架很快就做好了，一直有困难的地方是在删除联系人。刚开始一直想着监听listview，得到选中的checkbox，再得到数据，在网上找了很多方法，比较复杂。后来发现其实只要遍历listview的控件得到里面的checkbox的值就好了，当然checkbox里面是装有联系人的id的，只是我设置字体为白色，所以才看不见。在执行删除sql语句的时候因为太早关闭db，导致浪费了很多时间。因为前面的普定啊，修改很快就做好了。

实验评语

 

实验成绩

 

指导教师签名：              年   月   日

           

 

实验8 SQLite数据库操作

标签：