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;
}
}
--------------------------------------------效果----------------------------------------------------
添加数据
修改数据
- com.ch20.rar (702.3 KB)
- 下载次数: 35