Android中Sqlite数据库的应用

Android中Sqlite数据库的使用
依照习惯还是通过一个例子来熟悉,我只所以都把例子写在这里方便我以后查找使用,也可以共享给大家学习,如果大家有什么好的网址和Demo也可以告诉我,谢谢!共同进步!!!Android中Sqlite数据库的应用
DatabaseActivity.java启动类:
package com.example.Database;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

public class DatabaseActivity extends Activity implements
		Button.OnClickListener {

	DBAdapter db_ex;

	long id;

	/** Called when the activity is first created. */
	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);

		// 通过此类来进行数据库的相关操作
		db_ex = new DBAdapter(this, Constants.DB_NAME, Constants.DB_VERSION);

		Button add_btn = (Button) findViewById(R.id.Button01);
		Button cancel_btn = (Button) findViewById(R.id.Button02);
		Button update_btn = (Button) findViewById(R.id.Button03);
		Button check_btn = (Button) findViewById(R.id.Button04);

		add_btn.setOnClickListener(this);
		cancel_btn.setOnClickListener(this);
		update_btn.setOnClickListener(this);
		check_btn.setOnClickListener(this);

	}

	public void onClick(View v) {
		switch (v.getId()) {
		case R.id.Button01:
			// ---add 2 titles---
			// 每次操作数据库都要调用此函数open()
			db_ex.open();
			id = db_ex.insertTitle("1111111", "Android book", "Jim");
			id = db_ex.insertTitle("2222222", "Iphone book", "Edison");

			// ---获取所有标题---
			Cursor c = db_ex.getAllTitles();

			if (c.moveToFirst()) {
				do {
					DisplayTitle(c);
				} while (c.moveToNext());
			}
			db_ex.close();
			break;
		case R.id.Button02:
			// 删除一行数据
			db_ex.open();
			if (db_ex.deleteTitle(1)) {
				Toast.makeText(this, "Delete successful.", Toast.LENGTH_LONG)
						.show();
			} else {
				Toast.makeText(this, "Delete failed.", Toast.LENGTH_LONG)
						.show();
			}

			db_ex.close();
			break;
		case R.id.Button03:
			// 更新一条数据
			db_ex.open();
			if (db_ex.updateTitle(1, "1234567", "Programmer's Reference",
					"Wrox Press")) {
				Toast.makeText(this, "Update successful.", Toast.LENGTH_LONG)
						.show();
			}

			else {
				Toast.makeText(this, "Update failed.", Toast.LENGTH_LONG)
						.show();
			}

			// 将更新后的数据显示出来
			Cursor c1 = db_ex.getTitle(1);
			if (c1.moveToFirst()) {
				DisplayTitle(c1);
			} else {
				Toast.makeText(this, "No title found", Toast.LENGTH_LONG)
						.show();
			}

			db_ex.close();
			break;
		case R.id.Button04:

			// 检索一条特定的数据

			db_ex.open();
			Cursor c2 = db_ex.getTitle(2);
			if (c2.moveToFirst()) {
				DisplayTitle(c2);
			} else {
				Toast.makeText(this, "No title found", Toast.LENGTH_LONG)
						.show();
			}
			db_ex.close();

			break;
		default:
			break;

		}
	}

	// 显示数据库中的所有数据
	public void DisplayTitle(Cursor c) {
		Toast.makeText(
				this,
				"id: " + c.getString(0) + "\n" + "ISBN: " + c.getString(1)
						+ "\n" + "TITLE: " + c.getString(2) + "\n"
						+ "PUBLISHER: " + c.getString(3), Toast.LENGTH_LONG)
				.show();
	}

}

2. DatabaseHelper.java这个类管理数据库的创建和版本管理
package com.example.Database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

//A helper class to manage database creation and version management
public class DatabaseHelper extends SQLiteOpenHelper {
	private static final String TAG = "DBAdapter";

	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	// 只有在第一次创建数据库的时候调用
	// Called when the database is created for the first time
	public void onCreate(SQLiteDatabase db) {
		// Execute a single SQL statement that is not a query
		db.execSQL(Constants.DATABASE_CREATE);
		Log.i("", "execSQL");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
				+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS titles");
		onCreate(db);
	}
}


3. DBAdapter.java是对数据库的增,删,改,查的操作都在这个类里面
package com.example.Database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DBAdapter {
	DatabaseHelper DBHelper;
	SQLiteDatabase db;

	public DBAdapter(Context ctx, String base_name, int version) {
		DBHelper = new DatabaseHelper(ctx, base_name, null, version);
	}

	// 创建或打开一个读写数据库,每次写数据库都要调用此方法
	// 不在使用时,需调用close()
	public DBAdapter open() throws SQLException {
		// ---打开数据库---    
		db = DBHelper.getWritableDatabase();
		Log.i("", "getWritableDatabase");
		return this;
	}

	public void close() {
		// ---关闭数据库---
		DBHelper.close();
	}

	// ---向数据库中插入一个标题---   
	public long insertTitle(String isbn, String title, String publisher) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(Constants.KEY_ISBN, isbn);
		initialValues.put(Constants.KEY_TITLE, title);
		initialValues.put(Constants.KEY_PUBLISHER, publisher);
		return db.insert(Constants.TABLE_NAME, Constants.KEY_ROWID,
				initialValues);
	}

	// ---删除一个指定标题---   
	public boolean deleteTitle(long rowId) {
		return db.delete(Constants.TABLE_NAME, Constants.KEY_ROWID + "="
				+ rowId, null) > 0;
	}

	// ---检索所有标题---
	public Cursor getAllTitles() {
		return db.query(Constants.TABLE_NAME, new String[] {
				Constants.KEY_ROWID, Constants.KEY_ISBN, Constants.KEY_TITLE,
				Constants.KEY_PUBLISHER }, null, null, null, null, null);
	}

	// ---检索一个指定标题---  
	public Cursor getTitle(long rowId) throws SQLException {
		Cursor mCursor = db.query(true, Constants.TABLE_NAME, new String[] {
				Constants.KEY_ROWID, Constants.KEY_ISBN, Constants.KEY_TITLE,
				Constants.KEY_PUBLISHER }, Constants.KEY_ROWID + "=" + rowId,
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	// ---更新一个标题---   
	public boolean updateTitle(long rowId, String isbn, String title,
			String publisher) {
		ContentValues args = new ContentValues();
		args.put(Constants.KEY_ISBN, isbn);
		args.put(Constants.KEY_TITLE, title);

		args.put(Constants.KEY_PUBLISHER, publisher);
		return db.update(Constants.TABLE_NAME, args, Constants.KEY_ROWID + "="
				+ rowId, null) > 0;
	}

}



4.Constants.java文件主要是一些常量的保存:
package com.example.Database;

public class Constants {
	public final static String DB_NAME = "dne_db";
	public final static int DB_VERSION = 1;

	public static final String TABLE_NAME = "titles";
	public static final String KEY_ROWID = "_id";
	public static final String KEY_ISBN = "isbn";
	public static final String KEY_TITLE = "title";
	public static final String KEY_PUBLISHER = "publisher";

	public static final String DATABASE_CREATE = "create table IF NOT EXISTS "
			+ TABLE_NAME + " (_id integer primary key autoincrement, "
			+ "isbn text, title text not null, " + "publisher text not null);";
}


5.最后是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">
	<TextView android:layout_width="fill_parent"
		android:layout_height="wrap_content" android:text="@string/hello" />


	<Button android:text="add one row" android:id="@+id/Button01"
		android:layout_width="150dp" 
		android:layout_height="wrap_content"></Button>
		
	<Button android:text="delete one row" android:id="@+id/Button02"
		android:layout_width="150dp" 
		android:layout_height="wrap_content"></Button>
		
	<Button android:text="update one row" android:id="@+id/Button03"
		android:layout_width="150dp" 
		android:layout_height="wrap_content"></Button>
		
	<Button android:text="select one row" android:id="@+id/Button04"
		android:layout_width="150dp" 
		android:layout_height="wrap_content"></Button>
		
</LinearLayout>