实验8 SQLite数据库操作

1.0" encoding="utf-8"?> <resources> <string name="hello">Hello World, MainActivity!</string> <string name="app_name">ContantsApp</string> <string name="please_input_password">请输入通讯录密码：</string> <string name="ok">确定</string> <string name="login_success">恭喜，登陆成功！</string> <string name="error_password">您输入的密码错误，请重新输入！</string> <string name="password_manage">通讯录密码管理：</string> <string name="new_password">新密码：</string> <string name="repeat_password">再一次：</string> <string name="password_is_null">输入密码为空，请输入密码！</string> <string name="password_set_success">密码设置成功！</string> <string name="password_change_success">密码修改成功！</string> <string name="password_isnot_equal">两次输入的密码不一致，请重新输入密码！</string> <string name="title">通讯录列表</string> <string name="not_dbcursor_values">数据库中没有记录，请点击菜单新建通讯录！</string> <string name="contantsName">姓名：</string> <string name="contantsPhone">固定电话：</string> <string name="contantsMobile">移动电话：</string> <string name="contantsEmail">电子邮件：</string> <string name="contantsPost">邮政编码：</string> <string name="contantsAddr">通讯地址：</string> <string name="contantsComp">公司地址：</string> <string name="menu_add">增加</string> <string name="menu_change">修改密码</string> </resources>

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

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:background="@drawable/background"
    >
    <LinearLayout
        android:orientation="horizontal"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal"
        >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_gravity="center_horizontal"
            android:text="@string/title"
            android:textSize="24px"
            android:textColor="@color/text"
            />
        <ImageView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:src="@drawable/title"
            />
        </LinearLayout>
        <ListView    
            android:id="@+id/db_listview"
            android:layout_width="fill_parent"
            android:layout_height="fill_parent"
            android:choiceMode="singleChoice"
            /> 
</LinearLayout>

（5）layout目录下新建一个contants_detail.xml,用于显示联系人详细信息，代码参考如下：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:background="@drawable/background"
    >
    <LinearLayout
        xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal"
        >                            <!-- 显示联系人姓名线性布局 -->
        <TextView
            android:layout_width="100px"
            android:layout_height="wrap_content"
            android:textSize="18px"
            android:textColor="@color/text"
            android:layout_gravity="left|center_vertical"
            android:text="@string/contantsName"
            />
        <EditText
            android:id="@+id/etName"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            />
         </LinearLayout>
    <LinearLayout
        xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        >                            <!-- 显示联系人固定电话的线性布局 -->
        <TextView
            android:layout_width="100px"
            android:layout_height="wrap_content"
            android:textSize="18px"
            android:textColor="@color/text"
            android:layout_gravity="center_vertical|left"
            android:text="@string/contantsPhone"
            />
        <EditText
            android:id="@+id/etPhone"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:phoneNumber="true"
            />
         </LinearLayout>
    <LinearLayout
        xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        >                                        <!-- 显示联系人手机号码的线性布局 -->
        <TextView
            android:layout_width="100px"
            android:layout_height="wrap_content"
            android:textSize="18px"
            android:textColor="@color/text"
            android:layout_gravity="left|center_vertical"
            android:text="@string/contantsMobile"
            />
        <EditText
            android:id="@+id/etMobile"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:phoneNumber="true"
            />
         </LinearLayout>
    <LinearLayout
        xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        >                                        <!-- 显示联系人电子邮件的线性布局 -->
        <TextView
            android:layout_width="100px"
            android:layout_height="wrap_content"
            android:textSize="18px"
            android:textColor="@color/text"
            android:layout_gravity="left|center_vertical"
            android:text="@string/contantsEmail"
            />
        <EditText
            android:id="@+id/etEmail"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            />
         </LinearLayout> 
    <LinearLayout
        xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        >                                        <!-- 显示联系人邮编的线性布局 -->
        <TextView
            android:layout_width="100px"
            android:layout_height="wrap_content"
            android:textSize="18px"
            android:textColor="@color/text"
            android:layout_gravity="left|center_vertical"
            android:text="@string/contantsPost"
            />
        <EditText
            android:id="@+id/etPost"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            />
         </LinearLayout>  
    <LinearLayout
        xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        >                                        <!-- 显示联系人通信地址的线性布局 -->
        <TextView
            android:layout_width="100px"
            android:layout_height="wrap_content"
            android:textSize="18px"
            android:textColor="@color/text"
            android:layout_gravity="left|center_vertical"
            android:text="@string/contantsAddr"
            />
        <EditText
            android:id="@+id/etAddr"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            />
         </LinearLayout>
    <LinearLayout
        xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        >                                        <!-- 显示联系人公司的线性布局 -->
        <TextView
            android:layout_width="100px"
            android:layout_height="wrap_content"
            android:textSize="18px"
            android:textColor="@color/text"
            android:layout_gravity="left|center_vertical"
            android:text="@string/contantsComp"
            />
        <EditText
            android:id="@+id/etComp"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            />
         </LinearLayout>
     <ImageButton
         android:id="@+id/detailSave"
         android:layout_width="fill_parent"
         android:layout_height="wrap_content"
         android:src="@drawable/save"
         />
</LinearLayout>

5)开发数据库辅助类MyOpenHelper类，新建一个MyOpenHelper.java。其中的框架代码如下

package com.cn.daming.databases;

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

public class DBOpenHelper extends SQLiteOpenHelper{

    private final static String DATABASE_NAME = "personal_contacts";
    private final static int DATABASE_VERSION = 1;
    private final static String TABLE_NAME = "contants";
    public static final String ID="_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 final static String LOGIN_TABLE_NAME = "contantslogin";
    public final static String LOGIN_USER = "admin";
    public final static String LOGIN_PWD = "password";
    
    public DBOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "create table "+TABLE_NAME+" ("
        + ID + " integer primary key autoincrement,"
        + NAME + " varchar,"
        + PHONE+" varchar,"
        + MOBILE + " varchar,"
        + EMAIL + " varchar,"
        + POST + " varchar,"
        + ADDR + " varchar,"
        + COMP + " varchar)";
        db.execSQL(sql);
        
        sql = "create table "+LOGIN_TABLE_NAME+" ("
        +LOGIN_USER+" text, "
        +LOGIN_PWD+" text )";
        db.execSQL(sql);        
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
        String sql = "drop table if exists "+TABLE_NAME;
        db.execSQL(sql);

        sql = "drop table if exists "+LOGIN_TABLE_NAME;
        db.execSQL(sql);
        onCreate(db);        
    }

    public Cursor selectContants(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
        return cursor;
    }
    
    public long insertContants(String[] strArray){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(NAME, strArray[0]);
        cv.put(PHONE, strArray[1]);
        cv.put(MOBILE, strArray[2]);
        cv.put(EMAIL, strArray[3]);    
        cv.put(POST, strArray[4]);
        cv.put(ADDR, strArray[5]);
        cv.put(COMP, strArray[6]);
        return db.insert(TABLE_NAME, null, cv);
    }
    
    public void deleteContants(String id){
        SQLiteDatabase db = this.getWritableDatabase();
        String where = ID+"=?";
        String[] whereValues = {id};
        db.delete(TABLE_NAME, where, whereValues);
    }
    
    public int updateContants(String id,String[] strArray){
        SQLiteDatabase db = this.getWritableDatabase();
        String where = ID+"=?";
        String[] whereValues = {id};
        ContentValues cv = new ContentValues();
        cv.put(NAME, strArray[0]);
        cv.put(PHONE, strArray[1]);
        cv.put(MOBILE, strArray[2]);
        cv.put(EMAIL, strArray[3]);    
        cv.put(POST, strArray[4]);
        cv.put(ADDR, strArray[5]);
        cv.put(COMP, strArray[6]);
        return db.update(TABLE_NAME, cv, where, whereValues);
    }
    
    public Cursor getContants(String id){
        SQLiteDatabase db = this.getReadableDatabase();
        String where = ID+"=?";
        String[] whereValues = {id};
        Cursor cursor = db.query(TABLE_NAME, null, where, whereValues, null, null, null);
        return cursor;
    }
    
    public long insertPwd(String password){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(LOGIN_USER, LOGIN_USER);
        cv.put(LOGIN_PWD, password);
        return db.insert(LOGIN_TABLE_NAME, null, cv);
    }
    
    public int updatePwd(String password){
        SQLiteDatabase db = this.getWritableDatabase();
        String where = LOGIN_USER+"=?";
        String[] whereValues = {LOGIN_USER};
        ContentValues cv = new ContentValues();
        cv.put(LOGIN_PWD, password);
        return db.update(LOGIN_TABLE_NAME, cv, where, whereValues);
    }

    public String getPwd(){
        SQLiteDatabase db = this.getReadableDatabase();
        String where = LOGIN_USER+"=?";
        String[] whereValues = {LOGIN_USER}; 
        Cursor cursor = db.query(LOGIN_TABLE_NAME, null, where, whereValues, null, null, null);
        if(cursor.moveToFirst()){
            return cursor.getString(cursor.getColumnIndex(LOGIN_PWD));
        }else{
            return "";
        }
    }
}

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

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

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

代码：

MainActivity.java

package com.cn.daming;

import java.util.ArrayList;
import java.util.List;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.AdapterView.OnItemLongClickListener;
import android.widget.ListView;
import android.widget.Toast;

import com.cn.daming.adapter.ContantsAdapter;
import com.cn.daming.databases.DBOpenHelper;

public class MainActivity extends Activity {

    private DBOpenHelper dbOpenHelper;
    private ContantsAdapter contantsAdapter;
    private ListView dbListView;
    private Cursor cursor;
    
    final int MENU_ADD = Menu.FIRST;
    final int MENU_CHANGE = Menu.FIRST+1;
    
    private List<String> ids;
    private List<String> names;
    private List<String> phones;
    AlertDialog dialog;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        dbOpenHelper = new DBOpenHelper(this);
        dbListView = (ListView)findViewById(R.id.db_listview);
        refreshDBOpenHelper();

        dbListView.setOnItemClickListener(new OnItemClickListener(){

            public void onItemClick(AdapterView<?> arg0, View arg1, int position,
                    long arg3) {
                Intent intent= new Intent();
                intent.putExtra("cmd", 0);    
                String contants_id = ids.get(position);//0代表查询联系人，1代表添加联系人
                intent.putExtra("id", contants_id);
                intent.setClass(MainActivity.this, DetailContantsActivity.class);
                startActivity(intent);
            }
        });
        
        dbListView.setOnItemLongClickListener(new OnItemLongClickListener(){

            public boolean onItemLongClick(AdapterView<?> arg0, View arg1,
                    final int position, long arg3) {
                dialog = new AlertDialog.Builder(MainActivity.this)
                .setTitle("提示!!")
                .setMessage("确定要删除这条记录？")
                .setPositiveButton("确定",
                 new DialogInterface.OnClickListener()
                 {
                     public void onClick(DialogInterface dialog, int whichButton)
                     {
                         String contants_id = ids.get(position);
                         dbOpenHelper.deleteContants(contants_id);
                         dbOpenHelper.close();
                         Toast.makeText(MainActivity.this, "正在删除数据库，请稍后。。。", Toast.LENGTH_LONG).show();
                         refreshDBOpenHelper();
                         contantsAdapter.notifyDataSetChanged();
                     }
                     })
                 .setNegativeButton("取消",new DialogInterface.OnClickListener()
                 {
                      public void onClick(DialogInterface dialog, int whichButton)
                      {
                         dialog.dismiss();
                      }
                  }).show();
                return false;
            }
        });
    }
    
    @Override
    protected void onResume() {
        refreshDBOpenHelper();    
        contantsAdapter.notifyDataSetChanged();
        super.onResume();
    }

    public void refreshDBOpenHelper(){
        cursor = dbOpenHelper.selectContants();
        ids = new ArrayList<String>();
        names = new ArrayList<String>();
        phones = new ArrayList<String>();
        
        int count = cursor.getCount();
        if(count>0){
            for(int i=0;i<count;i++){
                cursor.moveToPosition(i);
                ids.add(cursor.getString(0));
                names.add(cursor.getString(1));
                phones.add(cursor.getString(2));
            }
        }else{
            Toast.makeText(this, R.string.not_dbcursor_values, Toast.LENGTH_SHORT).show();
        }
        contantsAdapter = new ContantsAdapter(this,names,phones);
        dbListView.setAdapter(contantsAdapter);
        cursor.close();
        dbOpenHelper.close();
    }
    
    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        menu.add(0, MENU_ADD, 0, R.string.menu_add)
            .setIcon(R.drawable.add);    //add - add_button 
        menu.add(0, MENU_CHANGE, 0, R.string.menu_change)
            .setIcon(R.drawable.modify);        //add -add_change_password
        return super.onCreateOptionsMenu(menu);
    }
    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        switch(item.getItemId()){        //
        case MENU_ADD:            //press change add button
            Intent add_intent= new Intent(this,DetailContantsActivity.class);
            add_intent.putExtra("cmd", 1);
            startActivity(add_intent);
            break;
        case MENU_CHANGE:    //press change password button
            Intent change_password_intent = new Intent(MainActivity.this,PasswordManage.class);
            startActivityForResult(change_password_intent,0);
            break;
        }
        return super.onOptionsItemSelected(item);
    }
}

Login.java

package com.cn.daming;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import com.cn.daming.databases.DBOpenHelper;

public class Login extends Activity {

    private DBOpenHelper db;
    private EditText et;
    private Button login_button;
    private String password;
    
    @Override
    protected void onCreate(Bundle bundle) {
        super.onCreate(bundle);
        
        db = new DBOpenHelper(this);
        password = db.getPwd();
        db.close();
        if(password.equals("")){
            Intent intent = new Intent(Login.this,PasswordManage.class);
            startActivity(intent);
            finish();
            return;
        }
        setContentView(R.layout.login);
        
        et = (EditText)findViewById(R.id.login_password);
        login_button = (Button)findViewById(R.id.note_login);
        
        login_button.setOnClickListener(new Button.OnClickListener(){
    
            public void onClick(View v) {
                String input_pwd = et.getText().toString();
                if(password.equals(input_pwd)){
                    Toast.makeText(Login.this, R.string

                  

	 	
                    

                        

                            

                                
< 上一篇
golang github.com/go-sql-driver/mysql 遇到的数据库,设置库设计不合理的解决方法
                            
                            

                                 
下一篇 >
几款主流 NoSql 数据库的对比