android中SQLite数据库操作例程
1、环境:
Windows7,eclipse4.41+adt
2、主要文件:
androidManifest.xml文件:工程说明文件
activity_main.xml文件:activity布局文件
listvie.xml文件:activity中ListView的布局文件
dbHelper.java文件:SQLiteOpenHelper的子类
MainActivity.java文件:Activity的子类
3、运行结果:
4、核心代码:
(1)androidManifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.mydbdemo"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="18" />
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<activity
android:name="com.example.mydbdemo.MainActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
(2)activity_main.xml
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
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" >
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_centerHorizontal="true"
android:orientation="vertical" >
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:addStatesFromChildren="true">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="name"
android:textColor="?android:attr/textColorSecondary"/>
<EditText
android:id="@+id/et_name"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true"/>
</LinearLayout>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:addStatesFromChildren="true">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="age "
android:textColor="?android:attr/textColorSecondary"/>
<EditText
android:id="@+id/et_age"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="0.5"
android:singleLine="true" />
</LinearLayout>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:addStatesFromChildren="true"
android:gravity="center">
<Button
android:id="@+id/bt_add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="add"/>
<Button
android:id="@+id/bt_modify"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="modify"/>
<Button
android:id="@+id/bt_delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="delete"/>
<Button
android:id="@+id/bt_select"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="select"/>
</LinearLayout>
<ListView
android:id="@+id/listView"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:padding="20dip">
</ListView>
</LinearLayout>
</RelativeLayout>
(3)listvie.xml
<?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">
<TextView
android:id="@+id/tvID"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="0.4"/>
<TextView
android:id="@+id/tvName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="0.3"/>
<TextView
android:id="@+id/tvAge"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="0.3"/>
</LinearLayout>
(4)dbHelper.java
package com.example.mydbdemo;
import android.content.Context;
import android.database.sqlite.*;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
/**
* 本类主要实现数据库和表的创建,以及数据更新。
* @author 秦子文
* 20140304
*/
public class dbHelper extends SQLiteOpenHelper{
public static final String TB_NAME="friends";
public dbHelper(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
//注意TB_NAME前后要加空格 sql语句大小写不敏感
db.execSQL("create table if not exists "+TB_NAME+" (_id integer primary key autoincrement,name varchar,age integer)");
}
//数据库更新时,系统主动调用这个方法
@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS"+TB_NAME);
onCreate(db);
}
}
(5)MainActivity.java
package com.example.mydbdemo;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import android.os.Bundle;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
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.EditText;
import android.widget.ListView;
import android.widget.SimpleAdapter;
/**
* 本类是Activity的子类主要用于与用户的交互
* add按钮可以将EditText框中的文字添加到数据库
* modify按钮可以将ListView中选中的条目修改
* delete按钮可以将ListView中选中的条目删除
* 默认选中select按钮
* @author 秦子文
* 20150304
*/
public class MainActivity extends Activity {
private static String DB_NAME="mydb";
private EditText et_name;
private EditText et_age;
private ArrayList<Map<String,Object>> data;
private dbHelper dbHelper;
private SQLiteDatabase db;
private Cursor cursor;
private SimpleAdapter listAdapter;
private View view;
private ListView listview;
private Button selBtn,addBtn,updBtn,delBtn;
private Map<String,Object> item;
private String selId;
private ContentValues selCV;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//获取各个控件的引用
et_name = (EditText)findViewById(R.id.et_name);
et_age = (EditText)findViewById(R.id.et_age);
listview = (ListView)findViewById(R.id.listView);
selBtn = (Button)findViewById(R.id.bt_select);
addBtn = (Button)findViewById(R.id.bt_add);
updBtn = (Button)findViewById(R.id.bt_modify);
delBtn = (Button)findViewById(R.id.bt_delete);
//为Button设置监听器,监听器接口均由对应的内部类实现
selBtn.setOnClickListener(new selbutton());
addBtn.setOnClickListener(new addbutton());
updBtn.setOnClickListener(new modifybutton());
delBtn.setOnClickListener(new delbutton());
//创建数据库
dbHelper = new dbHelper(this,DB_NAME,null,1);
//得到数据库引用。也可调用dbHelper.getReadableDatabase()方法,视情况而定
db = dbHelper.getWritableDatabase();
//创建ArrayList<Map<String,Object>>
data = new ArrayList<Map<String,Object>>();
dbFindAll(); //检索数据库中的所有数据并显示
//为ListView添加监听器
listview.setOnItemClickListener(new OnItemClickListener(){
@Override
public void onItemClick(AdapterView<?> parent, View v, int position,
long id) {
// TODO Auto-generated method stub
//将手指点击位置条目的所有数据保存到Map中
Map<String,Object> listItem = (Map<String,Object>)listview.getItemAtPosition(position);
//将对应数据显示到EditText控件中
et_name.setText((String)listItem.get("name"));
et_age.setText((String)listItem.get("age"));
//保存选中id
selId = (String)listItem.get("_id");
}
});
}
//Button监听接口实现类
public class addbutton implements OnClickListener{
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
dbAdd(); //添加数据
dbFindAll();
}
}
public class delbutton implements OnClickListener{
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
dbDel(); //删除数据
dbFindAll();
}
}
public class modifybutton implements OnClickListener{
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
dbUpdate(); //修改数据
dbFindAll();
}
}
public class selbutton implements OnClickListener{
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
dbFindAll();
}
}
protected void dbFindAll(){
data.clear();
cursor = db.query(dbHelper.TB_NAME, //表名
null, //columns
null, //selection
null, //selectionArgs
null, //groupBy
null, //having
"_id ASC"); //orderBy
cursor.moveToFirst();
while(!cursor.isAfterLast()){
String id = cursor.getString(0);
String name = cursor.getString(1);
String age = cursor.getString(2);
item = new HashMap<String,Object>();
item.put("_id", id);
item.put("name", name);
item.put("age", age);
data.add(item);
cursor.moveToNext();
}
showList();
}
protected void dbAdd(){
ContentValues values = new ContentValues(); //键值对类似于map
values.put("name", et_name.getText().toString().trim()); //得到EditText中的数据并整形,存入对应键下
values.put("age", et_age.getText().toString().trim());
long rowid = db.insert(dbHelper.TB_NAME, //表名
null, //空列的默认值
values); //列名和对应的列值
if(-1 == rowid){
Log.i("myDBDemo","数据插入失败!");
}else{
Log.i("myDBDemo","数据插入成功!");
}
}
protected void dbUpdate(){
ContentValues values = new ContentValues();
values.put("name", et_name.getText().toString().trim());
values.put("age", et_age.getText().toString().trim());
String where = "_id="+selId;
int i = db.update(dbHelper.TB_NAME, //表名
values, //更新的列和对应数据
where, //更新条件
null); //更新条件数据
if(i>0){
Log.i("mydbDemo","数据更新成功!");
}else{
Log.i("mydbDemo","数据更新失败!");
}
}
protected void dbDel(){
String where = "_id="+selId;
int i = db.delete(dbHelper.TB_NAME, //表名
where, //删除条件
null); //删除条件数组
if(i>0){
Log.i("myDbDemo","数据删除成功!");
}else{
Log.i("myDbDemo","数据删除失败!");
}
}
//ListView显示数据
private void showList(){
listAdapter = new SimpleAdapter(this, //山下文环境
data, //要显示的数据,类型为ArrayList<Map<String,Object>>
R.layout.listview, //listView引用的布局文件,即res/layout/listview.xml
new String[]{"_id","name","age"}, //data中的键名数组
new int[]{R.id.tvID,R.id.tvName,R.id.tvAge}); //要显示上面键名的控件数组,该id来自于res/layout/listview.xml
listview.setAdapter(listAdapter); //将此适配器设置为res/layout/activity_main.xml中ListView的适配器
}
@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;
}
}
5、工程完整代码:
http://download.csdn.net/detail/a_cainiao_a/8473383