Sqlite入门

在Android下，对Sqlite3进行了封装，使用相对更简单了。下面的代码主要实现了数据库的安装、升级、添加、查询等功能，至于修改、删除也非常容易实现。并实现了对长时间按键的处理。

MainActivity.java的代码：

 

package org.penguin.study.android.sqlite3;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;
import android.view.ContextMenu;
import android.view.View;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.View.OnCreateContextMenuListener;
import android.widget.TextView;

public class MainActivity extends Activity {
	private static final String DATABASE_NAME = "simple.db";
	private static final int DATABASE_VERSIOIN = 1;
	private static final String tag = "org.penguin.study.android.sqlite3.MainActivity";
	private static final String createTableOfPerson = "CREATE TABLE IF NOT EXISTS person  (personId INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , personName VARCHAR(20) UNIQUE , sex BOOL DEFAULT 1, birthday DATETIME, createdDate DATETIME DEFAULT CURRENT_TIMESTAMP, description TEXT)";
	private static final String dropTableOfPerson = "DROP TABLE IF EXISTS person";
	private static final String insertDataOfPerson = "INSERT INTO person (personName,sex,birthday,description) VALUES('赵静',0,'1978-11-22','一个惊艳的服装设计师')";
	private final DatabaseHelper dbHelper = new DatabaseHelper(this);
	private static final String tableNameOfPerson = "person";
	private static final String[] columnNamesOfPerson = { "personId", "personName", "sex", "birthday", "createdDate", "description" };

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);
		TextView messageTV = (TextView) findViewById(R.id.messageTV);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(tableNameOfPerson, columnNamesOfPerson, null, null, null, null, null); //较复杂的数据库查询方式
		cursor = db.rawQuery("SELECT * FROM person WHERE personId=? or personId=?", new String[] { "1","2" }); //个人推荐使用这种方式查询数据
		StringBuilder dbData = new StringBuilder();
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			dbData.append("编码:").append(cursor.getInt(0)).append("、姓名:").append(cursor.getString(1)).append("、性别:").append(cursor.getInt(2) == 1 ? "男" : "女")
					.append("、生日:").append(cursor.getString(3)).append("、简介:").append(cursor.getString(5)).append("\r\n");
			cursor.moveToNext();
		}
		messageTV.setText(dbData.toString());
		messageTV.setOnCreateContextMenuListener(new OnCreateContextMenuListener() {
			
			@Override
			public void onCreateContextMenu(ContextMenu menu, View v, ContextMenuInfo menuInfo) {
				new AlertDialog.Builder(MainActivity.this).setTitle("长按键事件处理").setMessage("确认清空数据吗？").show();
				
			}
		});
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSIOIN);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(createTableOfPerson);
			db.execSQL(insertDataOfPerson);
			ContentValues values=new ContentValues();//键为String，值为8种基本数据类+String类型
			values.put("personName", "程英华");
			values.put("sex", 1);
			values.put("birthday", "1978-02-04");
			values.put("description", "一个合格的程序员");
			db.insert("person", null, values);
			Log.i(tag, "成功创建数据库，并初始化数据！");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {			
			db.execSQL(dropTableOfPerson);
			db.execSQL(createTableOfPerson);
			db.execSQL(insertDataOfPerson);
			Log.i(tag, "成功升级数据库，并初始化数据！");
		}

	}
}

 

 布局文件：

 

<?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" android:id="@+id/messageTV" />
</LinearLayout>