ch020 Android SQLite3(第二一部分)

--------------------------------------------AndroidManifest.xml----------------------------------

<manifest?xmlns:android="http://schemas.android.com/apk/res/android"

????package="com.ch20"

????android:versionCode="1"

????android:versionName="1.0"?>

????<uses-sdk

????????android:minSdkVersion="10"

????????android:targetSdkVersion="15"?/>

????<application

????????android:icon="@drawable/ic_launcher"

????????android:label="@string/app_name"

????????android:theme="@style/AppTheme"?>

????????<activity

????????????android:name=".ListViewActivity"

????????????android:label="@string/title_activity_main"?>

????????????<intent-filter>

????????????????<action?android:name="android.intent.action.MAIN"?/>

????????????????<category?android:name="android.intent.category.LAUNCHER"?/>

????????????</intent-filter>

????????</activity>

????</application>

</manifest>

--------------------------------------------Layout?activity_main.xml----------------------------

<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"?>

????<Button

????????android:id="@+id/add_id"

????????android:layout_width="fill_parent"

????????android:layout_height="wrap_content"

????????android:text="添加数据"/>

??????<Button

????????android:id="@+id/delete_id"

????????android:layout_width="fill_parent"

????????android:layout_height="wrap_content"

????????android:text="删除数据"/>

????????<Button

????????android:id="@+id/edit_id"

????????android:layout_width="fill_parent"

????????android:layout_height="wrap_content"

????????android:text="修改数据"/>

??????????<Button

????????android:id="@+id/qyery_id"

????????android:layout_width="fill_parent"

????????android:layout_height="wrap_content"

????????android:text="查询数据"/>

??????????

??????<ListView?

??????????android:id="@+id/show_result"

??????????android:layout_width="fill_parent"

??????????android:layout_height="wrap_content">

??????</ListView>

</LinearLayout>

--------------------------------------------Layout?list_item.xml---------------------------------

<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="horizontal"?>

????<TextView

????????android:id="@+id/view_id"

????????android:layout_width="wrap_content"

????????android:layout_height="wrap_content"

????????android:text="id....."?/>

????

????????<TextView

????????android:id="@+id/view_name"

????????android:layout_width="wrap_content"

????????android:layout_height="wrap_content"

????????android:text="name....."?/>

</LinearLayout>

--------------------------------------------Layout?list_input.xml--------------------------------

<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"?>

????<TextView

????????android:id="@+id/view_id"

????????android:layout_width="wrap_content"

????????android:layout_height="wrap_content"

????????android:text="学生编号"?/>

????<EditText

????????android:id="@+id/stu_id"

????????android:layout_width="fill_parent"

????????android:layout_height="wrap_content"?/>

????<TextView

????????android:id="@+id/view_name"

????????android:layout_width="wrap_content"

????????android:layout_height="wrap_content"

????????android:text="学生姓名"?/>

????????<EditText

????????android:id="@+id/stu_name"

????????android:layout_width="fill_parent"

????????android:layout_height="wrap_content"?/>

</LinearLayout>

--------------------------------------------ListViewActivity.java----------------------------------

package?com.ch20;

import?java.util.HashMap;

import?java.util.List;

import?android.app.Activity;

import?android.app.AlertDialog;

import?android.app.AlertDialog.Builder;

import?android.content.ContentValues;

import?android.content.DialogInterface;

import?android.os.Bundle;

import?android.view.LayoutInflater;

import?android.view.View;

import?android.view.View.OnClickListener;

import?android.widget.Button;

import?android.widget.EditText;

import?android.widget.ListView;

import?android.widget.SimpleAdapter;

/**

?*?

?*?项目名称：com.ch20????

?*?类名称：ListViewActivity????

?*?类描述：?CRUD

?*?创建人：方勇???

?*?创建时间：2012-12-4?上午9:37:39???

?*?Copyright?(c)?方勇-版权所有

?*/

public?class?ListViewActivity?extends?Activity?implements?OnClickListener?{

private?ListView?lisView;

private?List<HashMap<String,?Object>>?list;

private?Button?addBtn,?deleteBtn,?editBtn,?queryBtn;

private?SimpleAdapter?simpleAdapter;

private?DBHelper?dbHelper;

@Override

public?void?onCreate(Bundle?savedInstanceState)?{

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

findViews();

setListeners();

dbHelper?=?new?DBHelper(this);

init();

}

@Override

public?void?onClick(View?v)?{

switch?(v.getId())?{

case?R.id.add_id:

buildDialog_input().show();

break;

case?R.id.delete_id:

//?deleteDialog_input().show();

break;

case?R.id.edit_id:

buildDialog_update().show();

break;

case?R.id.qyery_id:

break;

}

}

/*?实例化UI?*/

private?void?findViews()?{

addBtn?=?(Button)?findViewById(R.id.add_id);

deleteBtn?=?(Button)?findViewById(R.id.delete_id);

editBtn?=?(Button)?findViewById(R.id.edit_id);

queryBtn?=?(Button)?findViewById(R.id.qyery_id);

lisView?=?(ListView)?findViewById(R.id.show_result);

}

/*?设置监听?*/

private?void?setListeners()?{

addBtn.setOnClickListener(this);

deleteBtn.setOnClickListener(this);

editBtn.setOnClickListener(this);

queryBtn.setOnClickListener(this);

}

/*?初始化数据?*/

private?void?init()?{

list?=?StudentBean.getInstance().findList(dbHelper);

simpleAdapter?=?new?SimpleAdapter(this,?list,?R.layout.list_item,?new?String[]?{?"id",?"name"?},?new?int[]?{

R.id.view_id,?R.id.view_name?});

lisView.setAdapter(simpleAdapter);

}

/*?添加数据对话框?*/

private?AlertDialog?buildDialog_input()?{

/*?获取布局文件?*/

LayoutInflater?layoutInflater?=?LayoutInflater.from(this);

final?View?view?=?layoutInflater.inflate(R.layout.list_input,?null);

/*?弹出对话框，输入数据?*/

Builder?build?=?new?AlertDialog.Builder(this);

build.setTitle("输入学生信息");//?标题

build.setView(view);//?内容

//?确定按钮

build.setPositiveButton("submit",?new?DialogInterface.OnClickListener()?{

@Override

public?void?onClick(DialogInterface?dialog,?int?which)?{

/*?获取数据?*/

EditText?filed_id?=?(EditText)?view.findViewById(R.id.stu_id);

EditText?filed_name?=?(EditText)?view.findViewById(R.id.stu_name);

/*?保存数据?*/

//?dbHelper.open();

//?ContentValues?values?=?new?ContentValues();

//?values.put("id",?Integer.parseInt(filed_id.getText().toString()));

//?values.put("name",?filed_name.getText().toString());

//?Log.i("a07",?"id="?+?filed_id.getText().toString());

//?Log.i("a07",?"name="?+?filed_name.getText().toString());

//?long?size?=?dbHelper.insert("stu",?values);

//?dbHelper.close();

ContentValues?values?=?new?ContentValues();

values.put(StudentBean.STU_ID,?Integer.parseInt(filed_id.getText().toString()));

values.put(StudentBean.STU_NAME,?filed_name.getText().toString());

long?size?=?StudentBean.getInstance().save(dbHelper,?values);

/*?追加数据?*/

if?(size?>?0)?{

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

map.put(StudentBean.STU_ID,?Integer.parseInt(filed_id.getText().toString()));

map.put(StudentBean.STU_NAME,?filed_name.getText().toString());

list.add(map);

/*?更新UI?*/

simpleAdapter.notifyDataSetChanged();

}

}

});

build.setNegativeButton("cancel",?null);

return?build.create();

}

/*?修改数据对话框?*/

private?AlertDialog?buildDialog_update()?{

/*?获取布局文件?*/

LayoutInflater?layoutInflater?=?LayoutInflater.from(this);

final?View?view?=?layoutInflater.inflate(R.layout.list_input,?null);

/*?弹出对话框，输入数据?*/

Builder?build?=?new?AlertDialog.Builder(this);

build.setTitle("输入学生信息");//?标题

build.setView(view);//?内容

//?确定按钮

build.setPositiveButton("submit",?new?DialogInterface.OnClickListener()?{

@Override

public?void?onClick(DialogInterface?dialog,?int?which)?{

/*?获取数据?*/

EditText?filed_id?=?(EditText)?view.findViewById(R.id.stu_id);

EditText?filed_name?=?(EditText)?view.findViewById(R.id.stu_name);

/*?保存数据?*/

// dbHelper.open();

// ContentValues?values?=?new?ContentValues();

// values.put("id",?Integer.parseInt(filed_id.getText().toString()));

// values.put("name",?filed_name.getText().toString());

// Log.i("a07",?"id="?+?filed_id.getText().toString());

// Log.i("a07",?"name="?+?filed_name.getText().toString());

// //?条件

// String?whereClause?=?"id=?";

// //?条件值

// String[]?whereArgs?=?new?String[]?{?filed_id.getText().toString()?};

// long?size?=?dbHelper.update("stu",?whereClause,?whereArgs,?values);

// dbHelper.close();

HashMap<String,Object>?oldValues?=?new?HashMap<String,Object>();

oldValues.put(StudentBean.STU_ID,?Integer.parseInt(filed_id.getText().toString()));

long?size?=?StudentBean.getInstance().update(dbHelper,?oldValues,?new?String[]{?filed_name.getText().toString()});

if?(size?>?0)?{

HashMap<String,?Object>?map?=?null;

String?id?=?filed_id.getText().toString();

for?(int?i?=?0;?i?<?list.size();?i++)?{

HashMap<String,?Object>?mmap?=?list.get(i);

if?(mmap.get("id").toString().equals(id))?{

map?=?list.get(i);

break;

}

}

map.put("id",?Integer.parseInt(filed_id.getText().toString()));

map.put("name",?filed_name.getText().toString());

//?list.add(map);

/*?更新UI?*/

simpleAdapter.notifyDataSetChanged();

}

}

});

build.setNegativeButton("cancel",?null);

return?build.create();

}

}

--------------------------------------------DBHelper.java-----------------------------------------

package?com.ch20;

import?android.content.ContentValues;

import?android.content.Context;

import?android.database.Cursor;

import?android.database.sqlite.SQLiteDatabase;

import?android.database.sqlite.SQLiteOpenHelper;

/**

?*?

?*?项目名称：com.ch20????

?*?类名称：DBHelper????

?*?类描述：数据库操作工具类??

?*?创建人：方勇???

?*?创建时间：2012-12-4?上午8:50:40???

?*?Copyright?(c)?方勇-版权所有

?*/

public?class?DBHelper?{

/*?DDL,定义数据结构?*/

private?DataBaseHelper?dbHelper;

/*?DML，数据库操作?*/

private?SQLiteDatabase?db;

/*?数据库名?*/

private?final?static?String?DATABASE_NAME?=?"a07.db3";

/*?版本号?*/

private?final?static?int?DATABASE_VERSION?=?1;

/*?上下文?*/

private?Context?mcontext;

public?DBHelper(Context?mcontext)?{

super();

this.mcontext?=?mcontext;

}

/*?静态内部类,针对DDL?*/

private?static?class?DataBaseHelper?extends?SQLiteOpenHelper?{

public?DataBaseHelper(Context?context)?{

super(context,?DATABASE_NAME,?null,?DATABASE_VERSION);

}

/*?创建表结构?*/

@Override

public?void?onCreate(SQLiteDatabase?db)?{

// db.execSQL("create?table?if?not?exists?stu(id?integer?primary?key,name?text)");

??? db.execSQL(StudentBean.getInstance().sql$createTable());

// db.execSQL("insert?into?stu?values(1,'a1')");

// db.execSQL("insert?into?stu?values(2,'a2')");

}

/*?针对数据库升级?*/

@Override

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

}

}

/*?打开数据库,如果已经打开就使用，否则创建?*/

public?DBHelper?open()?{

dbHelper?=?new?DataBaseHelper(mcontext);

db?=?dbHelper.getWritableDatabase();

return?this;

}

/*?关闭数据库?*/

public?void?close()?{

db.close();//?先关DML

dbHelper.close();//?DDL

}

/*?插入?*/

public?long?insert(String?tableName,?ContentValues?values)?{

return?db.insert(tableName,?null,?values);

}

/**

?*?

?*?更新

?*??

?*?@param?tableName???表名

?*?@param?whereClause?条件

?*?@param?whereArgs???条件值

?*?@param?values??????更新值

?*?@return?更新的条数

?*

?*/

public?long?update(String?tableName,?String?whereClause,?String[]?whereArgs,?ContentValues?values)?{

return?db.update(tableName,?values,?whereClause,?whereArgs);

}

/*?删除?*/

public?boolean?delete(String?tableName,?String?whereClause,?String[]?whereArgs)?{

return?db.delete(tableName,?whereClause,?whereArgs)?>?0;

}

/**

?*?

?*?查询，多条记录

?*??

?*?@param?tableName?????表名

?*?@param?columns???????列名

?*?@param?selection?????条件

?*?@param?selectionArgs?条件值

?*?@param?groupBy???????分组

?*?@param?having????????过滤

?*?@param?orderBy???????排序

?*?@param?limit?????????分页(2,3),从第二条记录开始，向下取三条记录

?*?@return?动态游标

?*

?*/

public?Cursor?findList(String?tableName,?String[]?columns,?String?selection,?String[]?selectionArgs,?String?groupBy,

String?having,?String?orderBy,?String?limit)?{

return?db.query(tableName,?columns,?selection,?selectionArgs,?groupBy,?having,?orderBy,?limit);

}

/**

?*?

?*?精确查询，返回一条数据

?*??

?*?@param?tableName?????表名

?*?@param?columns???????列名

?*?@param?selection?????条件

?*?@param?selectionArgs?条件值

?*?@param?groupBy???????分组

?*?@param?having????????过滤

?*?@param?orderBy???????排序

?*?@param?limit?????????分页(2,3),从第二条记录开始，向下取三条记录

?*?@return?动态游标

?*

?*/

public?Cursor?findInfo(String?tableName,?String[]?columns,?String?selection,?String[]?selectionArgs,?String?groupBy,

String?having,?String?orderBy,?String?limit)?{

Cursor?cursor?=?db.query(tableName,?columns,?selection,?selectionArgs,?groupBy,?having,?orderBy,?limit);

while?(cursor.moveToNext())?{

cursor.moveToFirst();

}

return?cursor;

}

/*?执行sql方法?*/

public?void?executeSql(String?sql)?{

db.execSQL(sql);

}

}

--------------------------------------------StudentBean.java-------------------------------------

package?com.ch20;

import?java.util.ArrayList;

import?java.util.HashMap;

import?java.util.List;

import?android.content.ContentValues;

import?android.database.Cursor;

/**

?*?

?*?项目名称：com.ch20????

?*?类名称：StudentBean????

?*?类描述：业务逻辑Bean??

?*?创建人：方勇???

?*?创建时间：2012-12-6?上午11:57:16???

?*?Copyright?(c)?方勇-版权所有

?*/

public?class?StudentBean?{

private?static?StudentBean?studentBean?=?null;

/*?表名?*/

public?final?static?String?TABLE_NAME?=?"stu";

/*?表对应的字段?*/

public?final?static?String?STU_ID?=?"id";

public?final?static?String?STU_NAME?=?"name";

/*?单例模式?*/

public?static?StudentBean?getInstance()?{

if?(null?==?studentBean)?{

studentBean?=?new?StudentBean();

}

return?studentBean;

}

/*?DDL操作?*/

//?创建表结构SQL

public?String?sql$createTable()?{

return?"create?table?if?not?exists?stu(id?integer?primary?key,name?text)";

}

//?删除表结构SQL

public?String?sql$dropTable()?{

return?"drop?table?if?exists?stu(id?integer?primary?key,name?text)";

}

/*?DML操作?*/

/*?插入?*/

public?long?save(DBHelper?dbHelper,?ContentValues?values)?{

dbHelper.open();

long?size?=?dbHelper.insert(TABLE_NAME,?values);

dbHelper.close();

return?size;

}

/**

?*?

?*?更新?

?*??

?*?@param?dbHelper?

?*?@param?oldValues?一般为Map,可根据自己的实际情况调整

?*?@param?newValues?一般为String[],可根据自己的实际情况调整

?*?@return?更新的记录

?*

?*/

public?long?update(DBHelper?dbHelper,?HashMap<String,?Object>?oldValues,?String[]?newValues)?{

dbHelper.open();

ContentValues?values?=?new?ContentValues();

values.put(STU_NAME,?newValues[0]);

long?size?=?dbHelper.update(TABLE_NAME,?"id=?",?new?String[]?{?oldValues.get(STU_ID).toString()?},?values);

dbHelper.close();

return?size;

}

/*?删除?*/

public?long?delete(DBHelper?dbHelper,?String?whereArs,?HashMap<String,?Object>?values)?{

return?0;

}

/*?获取数据库集合数据?*/

public?List<HashMap<String,?Object>>?findList(DBHelper?dbHelper)?{

dbHelper.open();

Cursor?cursor?=?dbHelper.findList(TABLE_NAME,?null,?null,?null,?null,?null,?null,?null);

List?list?=?cursor2List(cursor);

dbHelper.close();

return?list;

}

/*?游标转换为集合?*/

private?List<HashMap<String,?Object>>?cursor2List(Cursor?cursor)?{

List?list?=?new?ArrayList<HashMap<String,?Object>>();

/*?有记录?*/

while?(cursor.moveToNext())?{

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

int?id?=?cursor.getInt(cursor.getColumnIndex(STU_ID));

String?name?=?cursor.getString(cursor.getColumnIndex(STU_NAME));

map.put(STU_ID,?id);

map.put(STU_NAME,?name);

list.add(map);

}

return?list;

}

}

--------------------------------------------效果----------------------------------------------------

添加数据

修改数据