android应用开发详解（二十一）-----------------------SQLite

package com.example.test_sqlite;
//这只是简单介绍SQLite的基本操作，不是运行的。。。
import android.os.Bundle;
import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.StaticLayout;
import android.view.Menu;


public class MainActivity extends Activity {


@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
// 实例化数据库帮助类
MyDBHelper helper = new MyDBHelper(this);
// 插入
helper.insert();
// 查询
helper.query();
}


// 数据库帮助类
class MyDBHelper extends SQLiteOpenHelper {
// 创建表SQL语句
private static final String CREATE_TABLE_SQL = "create table temp(_id integer,name text)";


// 构造方法
public MyDBHelper(Context context) {
super(context, "test.db", null, 0);
// TODO Auto-generated constructor stub
}


// SQLiteDatabase实例
private SQLiteDatabase db;


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


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


}


// 插入方法
private void insert() {
// 插入SQL语句
String sql = "insert into temp (_id,name) values(1,'jiangcuicui')";
// 执行插入
getWritableDatabase().execSQL(sql);
}


// 查询方法
private void query() {
// 获得游标
Cursor c = getWritableDatabase().query("temp", null, null, null,
null, null, null);
// 判断游标是否为空
if (c.moveToFirst()) {
// 遍历游标
for (int i = 0; i < c.getCount(); i++) {
c.move(i);
int id = c.getInt(0);
String name = c.getString(1);
System.out.println("id:" + id + "  name:" + name);
}
}
}


}


@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;
}


}

1、工程目录

2、DBHelper.java

package com.example.test_sqlite2;

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

//数据库帮助类

public class DBHelper extends SQLiteOpenHelper {
	// 数据库名称
	private static final String DB_NAME = "coll.db";
	// 数据表名称
	private static final String TABLE_NAME = "CollTable";
	private static final String CREATE_TABLE = "create table "
			+ "CollTable(_id integer primary key autoincrement,name text,url text,desc text)";
	// SQLiteDatabase实例
	private SQLiteDatabase db;

	// 构造方法
	public DBHelper(Context context) {
		super(context, DB_NAME, null, 2);
		// TODO Auto-generated constructor stub
	}

	// 创建表
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		this.db = db;
		db.execSQL(CREATE_TABLE);
	}

	// 插入方法
	public void insert(ContentValues values) {
		// 获得SQLiteDatabase实例
		SQLiteDatabase db = getWritableDatabase();
		// 插入
		db.insert(TABLE_NAME, null, values);
		db.close();
	}

	// 查询方法
	public Cursor query() {
		// 获得SQLiteDatabase实例
		SQLiteDatabase db = getWritableDatabase();
		// 获得查询游标
		Cursor c = db.query(TABLE_NAME, null, null, null, null, null, null);
		return c;

	}

	// 删除方法
	public void del(int id) {
		if (db == null)
			// 获得SQLiteDatabase实例
			db = getWritableDatabase();
		db.delete(TABLE_NAME, "_id=?", new String[] { String.valueOf(id) });

	}

	// 关闭数据库
	public void close() {
		if (db != null)
			db.close();
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}

}

AddActivity.java

package com.example.test_sqlite2;

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

public class AddActivity extends Activity {
	private Button addBtn;
	private EditText myEdit01, myEdit02, myEdit03;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.add);
		myEdit01 = (EditText) findViewById(R.id.edit01);
		myEdit02 = (EditText) findViewById(R.id.edit02);
		myEdit03 = (EditText) findViewById(R.id.edit03);
		addBtn = (Button) findViewById(R.id.button01);
		// 设置当前Activity标题
		this.setTitle("添加收藏信息");
		addBtn.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View arg0) {
				// TODO Auto-generated method stub
				String name = myEdit01.getText().toString();
				String url = myEdit02.getText().toString();
				String desc = myEdit03.getText().toString();
				ContentValues values = new ContentValues();
				values.put("name", name);
				values.put("url", url);
				values.put("desc", desc);
				// 实例化数据库帮助类
				DBHelper Helper = new DBHelper(getApplicationContext());
				// 插入数据
				Helper.insert(values);
				// 实例化intent
				Intent intent = new Intent(AddActivity.this,
						QueryActivity.class);
				// 启动activity
				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.add, menu);
		return true;
	}

}

QueryActivity.java

<pre name="code" class="html">package com.example.test_sqlite2;

import android.app.AlertDialog;
import android.app.AlertDialog.Builder;
import android.app.ListActivity;
import android.content.DialogInterface;
import android.content.DialogInterface.OnClickListener;
import android.database.Cursor;
import android.os.Bundle;
import android.support.v4.widget.SimpleCursorAdapter;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ListView;

public class QueryActivity extends ListActivity {

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		// 设置activity标题
		this.setTitle("浏览收藏信息");
		// 实例化数据库帮助类
		final DBHelper helper = new DBHelper(this);
		// 获得查询游标
		Cursor c = helper.query();
		// 列表项数组
		String[] from = { "_id", "name", "url", "desc" };
		// 列表项ID
		int[] to = { R.id.text01, R.id.text02, R.id.text03, R.id.text04 };
		// 适配器
		SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,
				R.layout.row, c, from, to);
		// 列表视图
		ListView listview = getListView();
		// 为列表添加适配器
		listview.setAdapter(adapter);
		// 提示对话框
		final AlertDialog.Builder builder = new AlertDialog.Builder(this);
		// 为listview设置单击监听器
		listview.setOnItemClickListener(new OnItemClickListener() {

			@Override
			public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
					long arg3) {
				// TODO Auto-generated method stub
				final long temp = arg3;
				builder.setMessage("真的要删除该记录吗？")
						.setPositiveButton("是",
								new DialogInterface.OnClickListener() {

									@Override
									public void onClick(DialogInterface arg0,
											int arg1) {
										// TODO Auto-generated method stub
										// 删除数据
										helper.del((int) temp);
										// 重新查询
										Cursor c = helper.query();
										String[] from = { "_id", "name", "url",
												"dec" };
										int[] to = { R.id.text01, R.id.text02,
												R.id.text03, R.id.text04 };
										SimpleCursorAdapter adapter = new SimpleCursorAdapter(
												getApplicationContext(),
												R.layout.row, c, from, to);
										ListView listview = getListView();
										listview.setAdapter(adapter);
									}
								})
						.setNegativeButton("否", new OnClickListener() {

							@Override
							public void onClick(DialogInterface arg0, int arg1) {
								// TODO Auto-generated method stub

							}
						}).create().show();
			}
		});
		helper.close();
	}

}

3、布局文件

add.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <TextView
        android:id="@+id/textview01"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="网站名称" />

    <EditText
        android:id="@+id/edit01"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="" />

    <TextView
        android:id="@+id/textview02"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="URL" />

    <EditText
        android:id="@+id/edit02"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="" />

    <TextView
        android:id="@+id/textview03"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="网站描述" />

    <EditText
        android:id="@+id/edit03"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="" />

    <Button
        android:id="@+id/button01"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="添加" />

</LinearLayout>

row.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"
    android:orientation="horizontal" >

    <TextView
        android:id="@+id/text01"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="1" />

    <TextView
        android:id="@+id/text02"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:paddingLeft="10px"
        android:text="2" />

    <TextView
        android:id="@+id/text03"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:paddingLeft="10px"
        android:text="3" />

    <TextView
        android:id="@+id/text04"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:paddingLeft="10px"
        android:text="4" />

</LinearLayout>

ContentValues 和HashTable类似都是一种存储的机制 但是两者最大的区别就在于，contenvalues只能存储基本类型的数据，像string，int之类的，不能存储对象这种东西，而HashTable却可以存储对象。