实验8 SQLite数据库操作

平台移动互联网开发

实验日期

2016年5月6日

实验项目名称

SQLite数据库操作

实验地点

S30010

实验类型

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

学  时

 

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

1. 设计一个个人通讯录，掌握Android平台下的数据库开发，该个人通讯录主要包括联系人列表和联系人详细信息等界面。
2. 程序主界面是通讯录的目录显示手机上联系人的名称。点击联系人的姓名可以显示联系人的详细信息。单击图标按钮可以添加联系人和删除联系人。

 

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

(1)PC机

(2)操作系统：Windows XP

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

三、实验内容及步骤

（1）  确定数据库的数据结构。

（2）  新建工程，修改布局文件，定义字符串资源。

（3）  开发布局文件activity_main.xml用于显示联系人列表。

（4）  layout目录下新建一个detail.xml,用于显示联系人详细信息。

（5）  开发数据库辅助类MyOpenHelper类

（6）  DetailActivity端开发实现数据库增加、删除、修改记录等操作

（7）  新建Activity名为DetailActivity.java，实现联系人详细信息显示功能。

 

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

代码：

MainActivity类代码：

package com.example.tongxunlu;

 

import android.os.Bundle;

import android.app.Activity;

import android.content.Intent;

import android.database.Cursor;

import android.support.v4.widget.CursorAdapter;

import android.support.v4.widget.SimpleCursorAdapter;

import android.view.Menu;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.AdapterView;

import android.widget.AdapterView.OnItemClickListener;

import android.widget.Button;

import android.widget.ListView;

 

public class MainActivity extends Activity {

Button btn1,btn2;

ListView listview;

public static MyOpenHelper dbhelper;

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

btn1=(Button)findViewById(R.id.button1);

btn2=(Button)findViewById(R.id.button2);

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

dbhelper=new MyOpenHelper(MainActivity.this, "myDict.db10", 1);

Cursor cursor=dbhelper.getReadableDatabase().rawQuery("select * from contacts", null);

inflateList(cursor);

        btn1.setOnClickListener(new OnClickListener() {

 

@Override

public void onClick(View arg0) {

Intent intent=new Intent();

intent.setClass(MainActivity.this, BaocunActivity.class);

startActivity(intent);

MainActivity.this.finish();

}

});

        

       btn2.setOnClickListener(new OnClickListener() {

 

@Override

public void onClick(View arg0) {

Intent intent=new Intent();

intent.setClass(MainActivity.this, shanchuActivity.class);

startActivity(intent);

MainActivity.this.finish();

}

});

        

 

 

 

}

 private void inflateList(Cursor cursor) {

SimpleCursorAdapter simpleCursorAdapter=new SimpleCursorAdapter(MainActivity.this,

R.layout.lian, cursor, new String[]{"name","phone"},

new int[]{R.id.text1,R.id.text2}, CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);

listview.setAdapter(simpleCursorAdapter);

listview.setOnItemClickListener(new ItemClickListener());

}

 private final class ItemClickListener implements OnItemClickListener{  

  

        public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

         ListView listview = (ListView) parent;

         Cursor cursor1 = (Cursor) listview.getItemAtPosition(position);

        String personid = cursor1.getString(cursor1.getColumnIndex("name"));

        String personid1 = cursor1.getString(cursor1.getColumnIndex("phone"));

        String personid2 = cursor1.getString(cursor1.getColumnIndex("mobile"));

        String personid3 = cursor1.getString(cursor1.getColumnIndex("email"));

        String personid4 = cursor1.getString(cursor1.getColumnIndex("post"));

        String personid5 = cursor1.getString(cursor1.getColumnIndex("addr"));

        String personid6 = cursor1.getString(cursor1.getColumnIndex("comp"));

         Intent intent=new Intent();

intent.putExtra("username1",personid);

intent.putExtra("userphone1",personid1);

intent.putExtra("usermobile1",personid2);

intent.putExtra("useremail1",personid3);

intent.putExtra("userpost1",personid4);

intent.putExtra("useraddr1",personid5);

intent.putExtra("usercomp1",personid6);

intent.setClass(MainActivity.this, DetailActivity.class);

startActivity(intent);

MainActivity.this.finish();

              

        }  

    }   

@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;

}

}

DetailActivity类代码：

package com.example.tongxunlu;

 

 

import android.app.Activity;

import android.content.Intent;

import android.os.Bundle;

import android.view.Menu;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.EditText;

import android.widget.TextView;

 

public class DetailActivity extends Activity{

TextView textview1,textview2,textview3,textview4,textview5,textview6,textview7;

Button btn1,btn2;

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.detail);

btn1=(Button)findViewById(R.id.but1);

btn2=(Button)findViewById(R.id.but2);

Intent intent = getIntent();   

final String username = intent.getStringExtra("username1");

final String userphone = intent.getStringExtra("userphone1");

final String usermobile = intent.getStringExtra("usermobile1");

final String useremail = intent.getStringExtra("useremail1");

final String userpost = intent.getStringExtra("userpost1");

final String useraddr = intent.getStringExtra("useraddr1");

final String usercomp = intent.getStringExtra("usercomp1");

textview1=(TextView)findViewById(R.id.Text1);

textview2=(TextView)findViewById(R.id.Text2);

textview3=(TextView)findViewById(R.id.Text3);

textview4=(TextView)findViewById(R.id.Text4);

textview5=(TextView)findViewById(R.id.Text5);

textview6=(TextView)findViewById(R.id.Text6);

textview7=(TextView)findViewById(R.id.Text7);

textview1.setText(username);

textview2.setText(userphone);

textview3.setText(usermobile);

textview4.setText(useremail);

textview5.setText(userpost);

textview6.setText(useraddr);

textview7.setText(usercomp);

        btn1.setOnClickListener(new OnClickListener() {

 

@Override

public void onClick(View arg0) {

  Intent intent=new Intent();

  intent.putExtra("name1",username);

  intent.putExtra("phone1",userphone);

  intent.putExtra("mobile1",usermobile);

  intent.putExtra("email1",useremail);

  intent.putExtra("post1",userpost);

  intent.putExtra("addr1",useraddr);

  intent.putExtra("comp1",usercomp);

      intent.setClass(DetailActivity.this, xiugaiActivity.class);

      startActivity(intent);

      DetailActivity.this.finish();

}

});

 

        btn2.setOnClickListener(new OnClickListener() {

@Override

        public void onClick(View arg0) {

      Intent intent=new Intent();

      intent.setClass(DetailActivity.this, MainActivity.class);

      startActivity(intent);

      DetailActivity.this.finish();

    }

      });

}

@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;

}

}

MyOpenHelper类代码：

package com.example.tongxunlu;

 

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

 

public class MyOpenHelper extends SQLiteOpenHelper {

// final String

// CREATE_TABLE_SQL="create table myTable(_id integer primary key autoincrement,name,phone,mobile,email,post,addr,comp)";//创建数据表myTable

// public static final String DB_NAME="personal_contacts";

public static final String TABLE_NAME = "contacts";

public static final String ID = "_id";

public static final String NAME = "name";

public static final String PHONE = "phone";

public static final String MOBILE = "mobile";

public static final String EMAIL = "email";

public static final String POST = "post";

public static final String ADDR = "addr";

public static final String COMP = "comp";

public MyOpenHelper(Context context, String name, int version) {

super(context, name, null, version);

// TODO Auto-generated constructor stub

}

@Override

public void onCreate(SQLiteDatabase arg0) {

// TODO Auto-generated method stub

// arg0.execSQL(CREATE_TABLE_SQL);

arg0.execSQL("create table " + TABLE_NAME + " (" + ID

+ " integer primary key," + NAME + " varchar," + PHONE

+ " varchar," + MOBILE + " varchar," + EMAIL + " varchar,"

+ POST + " varchar," + ADDR + " varchar," + COMP + " varchar)");

}

 

@Override

public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {

// TODO Auto-generated method stub

}

}

BaocunActivity代码：

package com.example.tongxunlu;

 

 

import android.app.Activity;

import android.content.Intent;

import android.database.Cursor;

import android.os.Bundle;

import android.support.v4.widget.CursorAdapter;

import android.support.v4.widget.SimpleCursorAdapter;

import android.view.Menu;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.EditText;

import android.widget.ListView;

import android.widget.Toast;

 

public class BaocunActivity extends Activity{

EditText editTextNAME,editTextPHONE,editTextMOBILE,editTextEMAIL,editTextPOST,editTextADDR,editTextCOMP;

ListView listview;

Button btn,btn1;

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.baocun);

btn=(Button)findViewById(R.id.button1);

btn1=(Button)findViewById(R.id.button2);

editTextNAME=(EditText)findViewById(R.id.editText1);

editTextPHONE=(EditText)findViewById(R.id.editText2);

editTextMOBILE=(EditText)findViewById(R.id.editText3);

editTextEMAIL=(EditText)findViewById(R.id.editText4);

editTextPOST=(EditText)findViewById(R.id.editText5);

editTextADDR=(EditText)findViewById(R.id.editText6);

editTextCOMP=(EditText)findViewById(R.id.editText7);

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

btn.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View arg0) {

String name,phone,mobile,email,post,addr,comp;

name=editTextNAME.getText().toString();

phone=editTextPHONE.getText().toString();

mobile=editTextMOBILE.getText().toString();

email=editTextEMAIL.getText().toString();

post=editTextPOST.getText().toString();

addr=editTextADDR.getText().toString();

comp=editTextCOMP.getText().toString();

MainActivity.dbhelper.getReadableDatabase().execSQL("insert into contacts values(null,?,?,?,?,?,?,?)",new String[]{name,phone,mobile,email,post,addr,comp});

Toast.makeText(BaocunActivity.this, "保存成功！", Toast.LENGTH_LONG).show();

}

});

btn1.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View arg0) {

Intent intent=new Intent();

intent.setClass(BaocunActivity.this,MainActivity.class);

startActivity(intent);

BaocunActivity.this.finish();

}

});

}

@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;

}

}

shanchuActivity代码：

package com.example.tongxunlu;

 

import android.app.Activity;

import android.content.Intent;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.os.Bundle;

import android.support.v4.widget.CursorAdapter;

import android.support.v4.widget.SimpleCursorAdapter;

import android.view.Menu;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.AdapterView;

import android.widget.Button;

import android.widget.ListView;

import android.widget.Toast;

import android.widget.AdapterView.OnItemClickListener;

 

public class shanchuActivity extends Activity {

Button btn;

ListView listview1;

Cursor cursor;

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.shanchu);

listview1=(ListView)findViewById(R.id.lv1);

btn=(Button)findViewById(R.id.btn2);

    cursor=MainActivity.dbhelper.getReadableDatabase().rawQuery("select * from contacts", null);

inflateList(cursor);

 btn.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View arg0) {

Intent intent=new Intent();

intent.setClass(shanchuActivity.this,MainActivity.class);

startActivity(intent);

shanchuActivity.this.finish();

}

});

}

private void inflateList(Cursor cursor) {

SimpleCursorAdapter simpleCursorAdapter=new SimpleCursorAdapter(shanchuActivity.this,

R.layout.lian, cursor, new String[]{"name","phone"},

new int[]{R.id.text1,R.id.text2}, CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);

listview1.setAdapter(simpleCursorAdapter);

listview1.setOnItemClickListener(new ItemClickListener());

 

}

private final class ItemClickListener implements OnItemClickListener{  

  

        public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

         ListView listview = (ListView) parent;

         Cursor cursor1 = (Cursor) listview.getItemAtPosition(position);

        String personid = cursor1.getString(cursor1.getColumnIndex("name"));

        SQLiteDatabase db =  MainActivity.dbhelper.getWritableDatabase();

        db.delete("contacts", "name="+"‘"+personid+"‘", null);

        db.close();

       

        Toast.makeText(shanchuActivity.this, "删除成功！", Toast.LENGTH_LONG).show();

        inflateList(cursor);

        }  

}

 

 

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;

}

}

xiugaiActivity代码：

package com.example.tongxunlu;

 

import android.app.Activity;

import android.content.ContentValues;

import android.content.Intent;

import android.database.sqlite.SQLiteDatabase;

import android.os.Bundle;

import android.view.Menu;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.EditText;

import android.widget.Toast;

 

public class xiugaiActivity extends Activity{

Button btn1,btn2;

EditText editTextNAME,editTextPHONE,editTextMOBILE,editTextEMAIL,editTextPOST,editTextADDR,editTextCOMP;

public String name,phone,mobile,email,post,addr,comp;

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.xiugai);

btn1=(Button)findViewById(R.id.bon1);

btn2=(Button)findViewById(R.id.bon2);

editTextNAME=(EditText)findViewById(R.id.eT1);

editTextPHONE=(EditText)findViewById(R.id.eT2);

editTextMOBILE=(EditText)findViewById(R.id.eT3);

editTextEMAIL=(EditText)findViewById(R.id.eT4);

editTextPOST=(EditText)findViewById(R.id.eT5);

editTextADDR=(EditText)findViewById(R.id.eT6);

editTextCOMP=(EditText)findViewById(R.id.eT7);

Intent intent = getIntent();   

final String username1 = intent.getStringExtra("name1");

final String userphone1 = intent.getStringExtra("phone1");

final String usermobile1 = intent.getStringExtra("mobile1");

final String useremail1 = intent.getStringExtra("email1");

final String userpost1 = intent.getStringExtra("post1");

final String useraddr1 = intent.getStringExtra("addr1");

final String usercomp1 = intent.getStringExtra("comp1");

editTextNAME.setText(username1);

editTextPHONE.setText(userphone1);

editTextMOBILE.setText(usermobile1);

editTextEMAIL.setText(useremail1);

editTextPOST.setText(userpost1);

editTextADDR.setText(useraddr1);

editTextCOMP.setText(usercomp1);

name=editTextNAME.getText().toString();

phone=editTextPHONE.getText().toString();

mobile=editTextMOBILE.getText().toString();

email=editTextEMAIL.getText().toString();

post=editTextPOST.getText().toString();

addr=editTextADDR.getText().toString();

comp=editTextCOMP.getText().toString();

SQLiteDatabase db =  MainActivity.dbhelper.getWritableDatabase();

ContentValues values = new ContentValues();

ContentValues values1 = new ContentValues();

ContentValues values2 = new ContentValues();

ContentValues values3 = new ContentValues();

ContentValues values4 = new ContentValues();

ContentValues values5 = new ContentValues();

ContentValues values6 = new ContentValues();

values.put("name", name);

values1.put("phone", phone);

values2.put("mobile", mobile);

values3.put("email", email);

values4.put("post", post);

values5.put("addr", addr);

values6.put("comp", comp);

        db.update("contacts",values,"name="+"‘"+username1+"‘", null);

        db.update("contacts",values1,"phone="+"‘"+userphone1+"‘", null);

        db.update("contacts",values2,"mobile="+"‘"+usermobile1+"‘", null);

        db.update("contacts",values3,"email="+"‘"+useremail1+"‘", null);

        db.update("contacts",values4,"post="+"‘"+userpost1+"‘", null);

        db.update("contacts",values5,"addr="+"‘"+useraddr1+"‘", null);

        db.update("contacts",values6,"comp="+"‘"+usercomp1+"‘", null);

        db.close();

btn1.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View arg0) {

name=editTextNAME.getText().toString();

phone=editTextPHONE.getText().toString();

mobile=editTextMOBILE.getText().toString();

email=editTextEMAIL.getText().toString();

post=editTextPOST.getText().toString();

addr=editTextADDR.getText().toString();

comp=editTextCOMP.getText().toString();

SQLiteDatabase db =  MainActivity.dbhelper.getWritableDatabase();

ContentValues values = new ContentValues();

ContentValues values1 = new ContentValues();

ContentValues values2 = new ContentValues();

ContentValues values3 = new ContentValues();

ContentValues values4 = new ContentValues();

ContentValues values5 = new ContentValues();

ContentValues values6 = new ContentValues();

values.put("name", name);

values1.put("phone", phone);

values2.put("mobile", mobile);

values3.put("email", email);

values4.put("post", post);

values5.put("addr", addr);

values6.put("comp", comp);

        db.update("contacts",values,"name="+"‘"+username1+"‘", null);

        db.update("contacts",values1,"phone="+"‘"+userphone1+"‘", null);

        db.update("contacts",values2,"mobile="+"‘"+usermobile1+"‘", null);

        db.update("contacts",values3,"email="+"‘"+useremail1+"‘", null);

        db.update("contacts",values4,"post="+"‘"+userpost1+"‘", null);

        db.update("contacts",values5,"addr="+"‘"+useraddr1+"‘", null);

        db.update("contacts",values6,"comp="+"‘"+usercomp1+"‘", null);

        db.close();

        Toast.makeText(xiugaiActivity.this, "修改成功！", Toast.LENGTH_LONG).show();    

}

});

btn2.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View arg0) {

  Intent intent=new Intent();

  intent.putExtra("username1",name);

  intent.putExtra("userphone1",phone);

  intent.putExtra("usermobile1",mobile);

  intent.putExtra("useremail1",email);

  intent.putExtra("userpost1",post);

  intent.putExtra("useraddr1",addr);

  intent.putExtra("usercomp1",comp);

      intent.setClass(xiugaiActivity.this, DetailActivity.class);

      startActivity(intent);

      xiugaiActivity.this.finish();

}

});

}

 

@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;

}

}

 

运行结果：（截图）

 

 

 

 

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

实验中通过开发数据库辅助类MyOpenHelper类，将数据保存到数据库中，在MainActivity类中实现数据库增加、删除、修改记录等功能，在DetailActivity类中实现联系人详细信息显示功能。过程中遇到写不进数据库的问题，通过网上查阅、询问同学解决了

实验评语

 

实验成绩

 

指导教师签名：              年   月   日

实验8 SQLite数据库操作

标签：