Study-android sqlite

android的数据库操作sqlite3 主要是用到下面两个类

SQLiteOpenHelper只是创建一个数据库和版本控制的作用。
SQLiteDatabase  是数据库，带有增删改查等功能

一般做法是创建一个类DBHelper继承SQLiteOpenHelper，并且实现了构造函数、onCreate、onUpgrade函数

同时在DBHelper类实现了数据库的增删改查等功能。

DBHelper的构造函数如下 其实就是 super(context, DB_NAME, null, sVersion); sVersion需要1或1以上。context是依附的activity/service

如果这个函数的sVersion的值大于原来的版本时。就会调用onUpgrade函数。一般在这个函数里面改变表格的属性,比如增加一列等

调用这个构造函数后数据库并没有创建。还必须调用DBHelper的getWritableDatabase或者getReadableDatabase函数，如果原来有数据库则直接返回这个数据库。如果原来没有数据库则调用onCreate方法来创建一个数据库SQLiteDatabase,并返回SQLiteDatabase,同时也可以再onCreate里面通过SQLiteDatabase创建表。

<span style="white-space:pre">	</span>public DBHelper(Context context) {
		super(context, DB_NAME, null, sVersion);
	}

	/** 调用这个构造函数的时候,如果version升级了,则会调用onUpgrade */
	public DBHelper(Context context, int version) {
		super(context, DB_NAME, null, version);
		if (sVersion < version)
			sVersion = version;
	}

<pre name="code" class="java"><span style="white-space:pre">	</span>@Override //这个函数一般就是创建表
	public void onCreate(SQLiteDatabase db) {
		DBG.log(TAG, DBG._FUNC_());
		db.execSQL("create table "
				+ TB_PERSON
				+ "(_id integer primary key autoincrement, name varchar, age varchar)");
	}

数据库的增删改查 的操作有两种，一直是直接操作sql语句，一种是调用SQLiteDatabase的各种函数，目前都是直接使用sql语句。SQLiteDatabase的各种函数先不关心。

sql语句的操作有如下的例子，其他的可以参照

sqlite3 mysql.db.databasescreate table hello (id int,name varchar,age varchar);  //创建表select * from history //查询所有delete from history where name = 'name8';  //删除alter table history add timetime varchar; //增加列属性select * from history where time > '5';  //条件查询delete from history; //删除表

3、各种操作可以通过SQLiteDatabase的mSQLiteDB.execSQL函数，但是查询的只能通过rawQuery函数。sql语句可以使用占位符"？"。

例如：db.rawQuery("select * from " + TB_PERSON + " where name = ?", new String[] { name });

下面一个工程例子。创建一个Person的类，数据库里面创建一个person表格，进行增删改查。

Person类

public class Person {
	private int _id;
	private String name;
	private String age;

	public Person() {

	}

	public Person(int _id, String name, String age) {
		super();
		this._id = _id;
		this.name = name;
		this.age = age;
	}

	public int getId() {
		return _id;
	}

	public void setId(int _id) {
		this._id = _id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getAge() {
		return age;
	}

	public void setAge(String age) {
		this.age = age;
	}

}

DBHelper类

public class DBHelper extends SQLiteOpenHelper {
	private static final String TAG = "DBHelper";
	public static final String DB_NAME = "test.db";
	public static final String TB_PERSON = "person";

	public static int sVersion = 1;

	public DBHelper(Context context) {
		super(context, DB_NAME, null, sVersion);
	}

	/** 调用这个构造函数的时候,如果version升级了,则会调用onUpgrade */
	public DBHelper(Context context, int version) {
		super(context, DB_NAME, null, version);
		if (sVersion < version)
			sVersion = version;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		DBG.log(TAG, DBG._FUNC_());
		db.execSQL("create table "
				+ TB_PERSON
				+ "(_id integer primary key autoincrement, name varchar, age varchar)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		DBG.log(TAG, DBG._FUNC_());

	}

	/**
	 * 
	 * @param person
	 */
	public void add(Person person) {
		SQLiteDatabase db = getWritableDatabase();
		String sql = "insert into " + TB_PERSON + "(name,age)" + "values(?,?)";
		db.execSQL(sql, new Object[] { person.getName(), person.getAge() });
	}

	public void delete(int id) {
		SQLiteDatabase db = getWritableDatabase();
		String sql = "delete from " + TB_PERSON + " where _id = ?";
		db.execSQL(sql, new Object[] { "" + id });
	}

	public void delete() {
		SQLiteDatabase db = getWritableDatabase();
		String sql = "delete * from " + TB_PERSON;
		db.execSQL(sql);
	}

	public Cursor check() {
		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = null;
		try {
			cursor = db.rawQuery("select * from " + TB_PERSON, null);
		} catch (Exception e) {
		}
		return cursor;
	}

	public Cursor check(String name) {
		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = null;
		try {
			cursor = db.rawQuery("select * from " + TB_PERSON
					+ " where name = ?", new String[] { name });
		} catch (Exception e) {
		}
		return cursor;
	}

}

测试的activity

import com.wyd.study.DBG;
import com.wyd.study.R;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;

/**
 * 
 * @author WangYD
 * @time 2015年8月7日
 * 
 */
public class SqliteActivity extends Activity implements OnClickListener {

	private static final String TAG = "SqliteActivity";
	private static final int[] btns = new int[] { R.id.add, R.id.delete,
			R.id.show, };

	private int mIndex = 0;
	private String mName = "wangyd";
	private int mAge = 20;

	private DBHelper mDBHelper;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		DBG.log(TAG, DBG._FUNC_());
		setContentView(R.layout.activity_sqlite);
		for (int btn : btns) {
			findViewById(btn).setOnClickListener(this);
		}
		mDBHelper = new DBHelper(this);
	}

	@Override
	public void onClick(View v) {
		switch (v.getId()) {
		case R.id.add:
			Person person = new Person();
			person.setName(mName + mIndex);
			person.setAge(mAge + mIndex + "");
			mIndex++;
			mDBHelper.add(person);
			break;
		case R.id.delete:
			mDBHelper.delete(5);
			break;

		case R.id.show:
			Cursor cursor = mDBHelper.check();
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						/**获取列索引号*/
						int _name = cursor.getColumnIndex("name");
						int _id = cursor.getColumnIndex("_id");
						int _age = cursor.getColumnIndex("age");
						/** 获取行内容*/
						String name = cursor.getString(_name);
						String age = cursor.getString(_age);
						int id = cursor.getInt(_id);
						DBG.log(TAG, id + " " + name + " " + age);

					} while (cursor.moveToNext());
				}
			}
			break;
		}

	}

}

源码下载：http://download.csdn.net/detail/yidong_wang/8975697