实验八：SQLite数据库操作

实验报告

课程名称	基于Android平台移动互联网开发	实验日期	2016年5月11日
实验项目名称	SQLite数据库操作	实验地点	S3010
实验类型	□验证型    √设计型    □综合型	学  时	2
一、实验目的及要求（本实验所涉及并要求掌握的知识点）
设计一个个人通讯录，掌握Android平台下的数据库开发，该个人通讯录主要包括联系人列表和联系人详细信息等界面。
二、实验环境（本实验所使用的硬件设备和相关软件）
(1)PC机 (2)操作系统：Windows XP (3)软件： Eclipse, JDK1.6，Android SDK，ADT
三、实验内容及步骤
新建工程； 修改布局文件activity_main.xml； 开发布局文件activity_main.xml用于显示联系人列表； layout目录下新建一个detail.xml,用于显示联系人详细信息； 接下来便进入MainActivity端的开发，实现数据库增加、删除、修改记录等操作； 新建一个Activity名字叫DetailActivity.java，实现联系人详细信息显示功能。

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

代码：

Mainactivity：

package com.example.txl;

import java.util.ArrayList;
import java.util.HashMap;

import android.os.Bundle;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.Menu;
import android.view.MenuItem;
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;
import android.widget.SimpleAdapter;

public class MainActivity extends Activity {
    Button btnadd, btndel;
    Intent it = new Intent();
    ListView listview;
    UserSQL usersql;
    SQLiteDatabase userdatabases;
    ArrayList<HashMap<String, Object>> userlist;
    String[] name=new String[100];

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        btnadd = (Button) findViewById(R.id.add);
        btndel = (Button) findViewById(R.id.delete);
        listview = (ListView) findViewById(R.id.listView1);
        usersql = new UserSQL(MainActivity.this, "user.db", null, 1);
        userdatabases = usersql.getReadableDatabase();
        userlist = new ArrayList<HashMap<String, Object>>();
        Cursor cursor = userdatabases.rawQuery("select * from userTable", null);
        cursor.moveToFirst();
        if (cursor.getCount() > 0) {
            for (int i = 0; i < cursor.getCount(); i++) {
                HashMap<String, Object> hashmap = new HashMap<String, Object>();
                hashmap.put("name",
                        cursor.getString(cursor.getColumnIndex("name")));
                name[i]=cursor.getString(cursor.getColumnIndex("name"));
                hashmap.put("mobile",
                        "["+cursor.getString(cursor.getColumnIndex("mobile"))+"]");
                userlist.add(hashmap);
                if (i < cursor.getCount()) {
                    cursor.moveToNext();
                }
            }
            SimpleAdapter sa = new SimpleAdapter(MainActivity.this, userlist,
                    R.layout.list, new String[] { "name", "mobile" }, new int[] {
                            R.id.namelist, R.id.phonelist });
            listview.setAdapter(sa);
        }
        listview.setOnItemClickListener(new OnItemClickListener() {

            @Override
            public void onItemClick(AdapterView<?> parent, View view,
                    int position, long id) {
                // TODO Auto-generated method stub
                Intent intent=new Intent(MainActivity.this,DetailActivity.class);
                Bundle bd=new Bundle();
                bd.putString("name", name[position]);
            
                intent.putExtras(bd);
                startActivity(intent);
            }
        });
    }

    @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
        switch (item.getItemId()) {
        case R.id.add:
            it.setClass(MainActivity.this, DetailActivity.class);
            Bundle bd=new Bundle();
            bd.putString("name", "");
            it.putExtras(bd);
            startActivity(it);
            break;
        case R.id.delete:
            it.setClass(MainActivity.this, DeleteActivity.class);
            startActivity(it);

        default:
            break;
        }
        return super.onOptionsItemSelected(item);
    }
    

}

Deleteactivity：

package com.example.txl;

import java.util.ArrayList;
import java.util.HashMap;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.AdapterView.OnItemClickListener;

public class DeleteActivity extends Activity {
    Button btnback;
    Intent it = new Intent();
    ListView listview;
    UserSQL usersql;
    SQLiteDatabase userdatabases;
    ArrayList<HashMap<String, Object>> userlist;
    String[] name = new String[100];
    Cursor cursor;
    SimpleAdapter sa;
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.delete);
        btnback = (Button) findViewById(R.id.back);
        listview = (ListView) findViewById(R.id.listView2);
        usersql = new UserSQL(DeleteActivity.this, "user.db", null, 1);
        userdatabases = usersql.getReadableDatabase();
        userlist = new ArrayList<HashMap<String, Object>>();
        cursor = userdatabases.rawQuery("select * from userTable", null);
        cursor.moveToFirst();
        
        if (cursor.getCount() > 0) {
            for (int i = 0; i < cursor.getCount(); i++) {
                HashMap<String, Object> hashmap = new HashMap<String, Object>();
                hashmap.put("name",
                        cursor.getString(cursor.getColumnIndex("name")));
                name[i] = cursor.getString(cursor.getColumnIndex("name"));
                hashmap.put("phone",
                        "[" + cursor.getString(cursor.getColumnIndex("phone"))
                                + "]");
                userlist.add(hashmap);
                if (i < cursor.getCount()) {
                    cursor.moveToNext();
                }
            }
            sa = new SimpleAdapter(DeleteActivity.this, userlist,
                    R.layout.list, new String[] { "name", "phone" }, new int[] {
                            R.id.namelist, R.id.phonelist });
            listview.setAdapter(sa);
        }
        listview.setOnItemClickListener(new OnItemClickListener() {

            @Override
            public void onItemClick(AdapterView<?> parent, View view,
                    int position, long id) {
                // TODO Auto-generated method stub
                
                userdatabases.delete("userTable", "name=?",new String[] { name[position] });
                list();
            }
        });
        btnback.setOnClickListener(new OnClickListener() {
            
            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                it.setClass(DeleteActivity.this, MainActivity.class);
                startActivity(it);
                finish();
            }
        });

    }
    public void list(){
        userlist = new ArrayList<HashMap<String, Object>>();
        cursor = userdatabases.rawQuery("select * from userTable", null);
        cursor.moveToFirst();
        
            for (int i = 0; i < cursor.getCount(); i++) {
                HashMap<String, Object> hashmap = new HashMap<String, Object>();
                hashmap.put("name",
                        cursor.getString(cursor.getColumnIndex("name")));
                name[i] = cursor.getString(cursor.getColumnIndex("name"));
                hashmap.put("phone",
                        "[" + cursor.getString(cursor.getColumnIndex("phone"))
                                + "]");
                userlist.add(hashmap);
                if (i < cursor.getCount()) {
                    cursor.moveToNext();
                }
            }
            sa = new SimpleAdapter(DeleteActivity.this, userlist,
                    R.layout.list, new String[] { "name", "phone" }, new int[] {
                            R.id.namelist, R.id.phonelist });
            listview.setAdapter(sa);
        
    }
}

Detailactivity：

package com.example.txl;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class DetailActivity extends Activity {
    EditText edname, edphone, edmobile, edemail, edpost, edaddr, edcomp;
    String name, phone, mobile, email, post, addr, comp;
    Button btnadd;
    UserSQL usersql;
    SQLiteDatabase userdatabase;
    Bundle bd;
    String selectname;

    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.detail);
        btnadd = (Button) findViewById(R.id.deadd);
        edname = (EditText) findViewById(R.id.edname);
        edphone = (EditText) findViewById(R.id.edphone);
        edmobile = (EditText) findViewById(R.id.edmoblie);
        edemail = (EditText) findViewById(R.id.edemail);
        edpost = (EditText) findViewById(R.id.edpost);
        edaddr = (EditText) findViewById(R.id.edaddr);
        edcomp = (EditText) findViewById(R.id.edcomp);
        usersql = new UserSQL(DetailActivity.this, "user.db", null, 1);
        userdatabase = usersql.getReadableDatabase();
        bd = getIntent().getExtras();
        if (!bd.getString("name").equals("")) {
            selectname = bd.getString("name");
            Cursor cursor = userdatabase.rawQuery(
                    "select * from userTable where name=?",
                    new String[] { selectname });
            cursor.moveToFirst();
            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")));
            btnadd.setText("更新");
        }
        btnadd.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                name = edname.getText().toString();
                phone = edphone.getText().toString();
                mobile = edmobile.getText().toString();
                email = edemail.getText().toString();
                post = edpost.getText().toString();
                addr = edaddr.getText().toString();
                comp = edcomp.getText().toString();

                if (!name.equals("") && !phone.equals("") && !mobile.equals("")
                        && !email.equals("") && !post.equals("")
                        && !addr.equals("") && !comp.equals("")) {

                    Cursor cursor = userdatabase.rawQuery(
                            "select * from userTable where name=?",
                            new String[] { name });
                    cursor.moveToFirst();
                    ContentValues cv = new ContentValues();
                    cv.put("name", name);
                    cv.put("phone", phone);
                    cv.put("mobile", mobile);
                    cv.put("email", email);
                    cv.put("post", post);
                    cv.put("addr", addr);
                    cv.put("comp", comp);
                    if (cursor.getCount() <= 0) {
                        userdatabase.insert("userTable", null, cv);
                        cv.clear();
                        Toast.makeText(DetailActivity.this, "保存" + name + "成功",
                                Toast.LENGTH_LONG).show();
                        userdatabase.delete("userTable", "name=?",
                                new String[] { selectname });

                        Intent it = new Intent();
                        it.setClass(DetailActivity.this, MainActivity.class);
                        startActivity(it);
                        finish();
                    } else if (cursor.getCount() == 1
                            && cursor.getString(cursor.getColumnIndex("name"))
                                    .equals(selectname)) {
                        userdatabase.update("userTable", cv, "name=?",
                                new String[] { selectname });
                        cv.clear();
                        Toast.makeText(DetailActivity.this, "更新" + name + "成功",
                                Toast.LENGTH_LONG).show();
                        Intent it = new Intent();
                        it.setClass(DetailActivity.this, MainActivity.class);
                        startActivity(it);
                        finish();
                    } else {

                        Toast.makeText(DetailActivity.this, name + "已注册",
                                Toast.LENGTH_LONG).show();
                    }
                } else {
                    Toast.makeText(DetailActivity.this, "信息不完整",
                            Toast.LENGTH_LONG).show();
                }
            }

        });
    }
}

UserSQL：

package com.example.txl;

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

public class UserSQL extends SQLiteOpenHelper{
    public static final String CREAT_USER="create table userTable(id integer primary key autoincrement,name,phone,mobile,email,post,addr,comp)";
    public UserSQL(Context context, String name, CursorFactory factory,
            int version) {
        super(context, name, factory, version);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(CREAT_USER);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        
    }

}

Layout：

Mainactivity：

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/LinearLayout1"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity" >

    <TextView
        android:id="@+id/title"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal"
        android:layout_marginTop="5dp"
        android:text="@string/title"
        android:textSize="28px" />

    <ListView
        android:id="@+id/listView1"
        android:layout_width="match_parent"
        android:layout_height="328dp"
        android:layout_weight="0.31" >
    </ListView>

    
</LinearLayout>

Delete：

<?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:padding="30px"
    android:orientation="vertical" >
      <TextView
        android:id="@+id/deltitle"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal"
        android:layout_marginTop="5dp"
        android:text="@string/title"
        android:textSize="32px" />

    <ListView
        android:id="@+id/listView2"
        android:layout_width="match_parent"
        android:layout_height="328dp"
        android:layout_weight="0.31" >
    </ListView>

   
        <Button
            android:id="@+id/back"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"

            android:text="@string/delback" />

  

</LinearLayout>

Detail：

<?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:padding="20px"
    android:orientation="vertical" >
<LinearLayout 
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >
    
    <TextView
        android:id="@+id/tvname"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/tvname" />

    <EditText
        android:id="@+id/edname"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="textPersonName" >

        <requestFocus />
    </EditText>
    </LinearLayout>
<LinearLayout 
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >
    <TextView
        android:id="@+id/tvphone"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/tvphone" />

    <EditText
        android:id="@+id/edphone"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="phone" />
    </LinearLayout>
<LinearLayout 
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >
    <TextView
        android:id="@+id/tvmoblie"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/tvmoblie" />

    <EditText
        android:id="@+id/edmoblie"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="phone" />
    </LinearLayout>
<LinearLayout 
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >
    <TextView
        android:id="@+id/tvemail"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/tvemail" />

    <EditText
        android:id="@+id/edemail"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="textEmailAddress" />
    </LinearLayout>
<LinearLayout 
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >
    <TextView
        android:id="@+id/tvpost"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/tvpost" />

    <EditText
        android:id="@+id/edpost"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="number" />
    </LinearLayout>
<LinearLayout 
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >
    <TextView
        android:id="@+id/tvaddr"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/tvaddr" />

    <EditText
        android:id="@+id/edaddr"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="textPostalAddress" />
    </LinearLayout>
<LinearLayout 
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >
    <TextView
        android:id="@+id/tvcomp"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/tvcomp" />

    <EditText
        android:id="@+id/edcomp"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10" />
    </LinearLayout>

<Button
    android:id="@+id/deadd"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_gravity="center_horizontal|bottom"
    android:text="@string/add" />

</LinearLayout>

List：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/LinearLayout2"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="horizontal" >



    <TextView
        android:id="@+id/namelist"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="25sp" />
    <TextView
        android:id="@+id/phonelist"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="25sp"/>
</LinearLayout>

运行结果：（截图）

 

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

　　本次实验涉及到了数据库，较为难，因此部分功能未能全部实现。