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;}@Overridepublic void onCreate(SQLiteDatabase db) {DBG.log(TAG, DBG._FUNC_());db.execSQL("create table "+ TB_PERSON+ "(_id integer primary key autoincrement, name varchar, age varchar)");}@Overridepublic 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;@Overrideprotected 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);}@Overridepublic 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