Android数据库操作的操作(SQLiteOpenHelper Dao)

以Person为例操作数据库。

Person类的定义如下：

package com.example.databasetest;

public class Person {
	private String name;
	private int age;
	private int id;
	public Person(){
		this("");
	}
	public Person(String name){
		this(name, 0);
	}
	public Person(String name,int age){
		this.name=name;
		this.age=age;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "Person:[name="+name+",age="+age+"]";
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
}

Person累中定义了name age id三个属性.....

2、创建数据库、用SQLiteOpenHelper工具来创建

SQLite中出了id 用primary key修饰后只能是int外，其实的字段是类型没什么关系，代码如下：

package com.example.databasetest;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;

public class MyDBHelper extends SQLiteOpenHelper {

	public static final String CREATE_TABLE_SQL="create table person"
				 +"("
				 +"_id integer primary key autoincrement,"
				 +"name,"
				 +"age"
				 +");";
	public static final String TABLE_NAME="person" ;
	public static final String TABLE_COLUMN_ID="_id" ;
	public static final String TABLE_COLUMN_AGE="age" ;
	public static final String TABLE_COLUMN_NAME="name" ;
	private String upgradeSql;
	
	public MyDBHelper(Context context, String name,int version) {
		super(context, name, null, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.execSQL(CREATE_TABLE_SQL);
			System.out.println("create table success !");
		} catch (Exception e) {
			System.out.println("create table failed !"+e.toString());
		}
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		System.out.println("onUpgrade-->oldVersion="+oldVersion+",newVersion="+newVersion);
	}

}

3、SqliteOpernHelper只是帮创建数据库和获取数据库、对数据的操作还需实现对应的方法（Dao）

package com.example.databasetest;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
/**
 * 操作数据库的类
 * @author csl
 *2015、07、04
 */
public class MyDAO {
	
	public static final String DEFAULT_TABLE="person";
	public static final String DEFAULT_SELECT_SQL="select * from "+DEFAULT_TABLE;
	public static final String DEFAULT_NULL_COLUMN="name";
	
	
	/**
	 * 查询
	 * @param db 数据库
	 * @param distinct 是否去除重复记录
	 * @param columns  要查询出来的列名
	 * @param selection	查询条件子句
	 * @param selectionArgs	为查询条件子句中的占位符传入参数
	 * @param groupBy	分组
	 * @param having	对分组进行过滤
	 * @param orderBy	排序
	 * @param limit  分页
	 * @return	
	 */
	public static Cursor query(SQLiteDatabase db, String table,String[] columns,
			String selection, String[] selectionArgs, String groupBy,
			String having, String orderBy) {
		/**
		 * select [distinct] columns/* from table_name
		 * [where (selection,selection)]
		 * [group by groupBy]
		 * [having having]
		 * [order by orderBy]
		 * [limit limit]
		 */
		//db.query(distinct, table, columns, selection, selectionArgs, groupBy, having, orderBy, limit, cancellationSignal)
		return db.query(table, columns, selection, selectionArgs, groupBy,
				having, orderBy);
	}
	public static Cursor query(SQLiteDatabase db, String table,
			String selection, String[] selectionArgs) {
		return query(db, table, null, selection, selectionArgs, null, null, null);
	}
	public static Cursor query(SQLiteDatabase db) {
		return query(db, DEFAULT_TABLE, null, null, null, null, null, null);
	}
	/**
	 * 插入一条数据
	 * @param db 数据库
	 * @param values	一条数据    
	 * ContentValues 类似于map   key对应于表中数据的列名 value对应相应的值
	 * @return	新添记录的行号  不成功时返回-1
	 */
	public static long insert(SQLiteDatabase db,String table,  String nullColumnHack, ContentValues values) {
		/**
		 * db.insert(table, nullColumnHack, values)
		 * long insert(String table,String nullColumnHack,ContentValues values)
		 * table :代表想插入数据的表名
		 * nullColumnHack：代表强行插入null值的数据列的列名，当value为null或不包含任何key-value对时该参数有效
		 * values：类似于map   key对应于表中数据的列名 value对应相应的值
		 */
		//insert into table_name[(column[,column...])]
		//values (value[,value...]);
		/**
		 *if values is not null
		 *insert into test values(null,'songlin',23);
		 *insert into person values(null,'values.name,values.age);
		 *if values is null
		 *insert into person(nullColumnHack) values(null); 
		 */
		
		//example is from mysql csl test truely
		
		/*mysql> insert into test values(null,'songlin',23);
		Query OK, 1 row affected (0.10 sec)

		mysql> select * from test;
		+-----+---------+------+
		| _id | name    | age  |
		+-----+---------+------+
		|   1 | songlin |   23 |
		+-----+---------+------+
		1 row in set (0.00 sec)

		mysql> insert into test(name) values('haha');
		Query OK, 1 row affected (0.07 sec)

		mysql> select * from test;
		+-----+---------+------+
		| _id | name    | age  |
		+-----+---------+------+
		|   1 | songlin |   23 |
		|   2 | haha    | NULL |
		+-----+---------+------+
		2 rows in set (0.00 sec)
		mysql> insert into test(name) values('haha'),('hehe'),('caca');
		Query OK, 3 rows affected (0.06 sec)
		Records: 3  Duplicates: 0  Warnings: 0
		
		mysql> select * from test;
		+-----+---------+------+
		| _id | name    | age  |
		+-----+---------+------+
		|   1 | songlin |   23 |
		|   2 | haha    | NULL |
		|   3 | haha    | NULL |
		|   4 | hehe    | NULL |
		|   5 | caca    | NULL |
		+-----+---------+------+
		5 rows in set (0.00 sec)
		*/
		return db.insert(table, nullColumnHack, values);
	}
	public static long insert(SQLiteDatabase db,ContentValues values) {
		return insert(db, DEFAULT_TABLE,DEFAULT_NULL_COLUMN, values);
	}
	/**
	 * 更新数据
	 * @param db 数据库
	 * @param values 代表想更新的数据
	 * @param whereClause	满足该whereClause条件的记录将会被更新
	 * @param whereArgs	用于为whereClause语句传入参数  （防止注入）
	 * @return 受此update影响的记录的条数
	 */
	public static int update(SQLiteDatabase db, String table,ContentValues values,
			String whereClause, String[] whereArgs) {
		/**
		 * db.update(table, values, whereClause, whereArgs)
		 * table:表名
		 */
		//upate table_name
		//set column1=value1[,column2=value2]...
		//[where comdition];
		/**
		 * update person 
		 * set name=values.name,age=values.age
		 * where (whereClause, whereArgs) 
		 * 
		 *whereClause=where age>?  
		 *whereArgs=new String[]{values.age+""}
		 */
		
		//exanple from mysql by csl 
		
		/*
		 mysql> select * from test;
		+-----+---------+------+
		| _id | name    | age  |
		+-----+---------+------+
		|   1 | songlin |   23 |
		|   2 | haha    | NULL |
		|   3 | haha    | NULL |
		|   4 | hehe    | NULL |
		|   5 | caca    | NULL |
		+-----+---------+------+
		5 rows in set (0.00 sec)
		
		mysql> update test
		    -> set age=22
		    -> where age is null;
		Query OK, 4 rows affected (0.17 sec)
		Rows matched: 4  Changed: 4  Warnings: 0
		
		mysql> select *from test;
		+-----+---------+------+
		| _id | name    | age  |
		+-----+---------+------+
		|   1 | songlin |   23 |
		|   2 | haha    |   22 |
		|   3 | haha    |   22 |
		|   4 | hehe    |   22 |
		|   5 | caca    |   22 |
		+-----+---------+------+
		5 rows in set (0.00 sec) 
		 */
		return db.update(table, values, whereClause, whereArgs);
	}
	public static int update(SQLiteDatabase db, ContentValues values,
			String whereClause, String[] whereArgs) {
		return update(db, DEFAULT_TABLE, values, whereClause, whereArgs);
	}
	/**
	 * 删除
	 * @param db 数据库
	 * @param whereClause 删除的条件
	 * @param whereArgs	  删除条件的一些参数	
	 * @return 被删除的行数
	 */
	public static int delete(SQLiteDatabase db, String table,String whereClause,
			String[] whereArgs) {
		/**
		 * db.delete(table, whereClause, whereArgs)
		 * table:表名
		 */
		
		//delet from table_name
		//[where condition];
		
		/**
		 * delete from person where (whereClause, whereArgs);
		 * whereClause:where name =?
		 * whereArgs:new String[]{"haha"}
 		 */
		return db.delete(table, whereClause, whereArgs);
	}
	public static int delete(SQLiteDatabase db,String whereClause,
			String[] whereArgs) {
		return delete(db, DEFAULT_TABLE, whereClause, whereArgs);
	}
	/**
	 * 查询
	 * @param db 数据库
	 * @return 查询返回的数据结果集
 	 */	
	public static Cursor rawQuery(SQLiteDatabase db,String sql,String[] selectionArgs){
		/**
		 * db.rawQuery(sql, selectionArgs)
		 * sql:查询测sql语句
		 * selectionArgs：sql语句的参数
		 */
		
		//select  column1,column2.../*  from table_name where condition;
		/**
		 * select * from person where age >20;
		 * sql:select * from person where age>?
		 * selectionArgs:new String[]{"20"}
		 */
//		return db.rawQuery("select * from person where age>?", new String[]{"20"});
		return db.rawQuery(sql,selectionArgs);
	}
	public static Cursor rawQuery(SQLiteDatabase db){
		return rawQuery(db, DEFAULT_SELECT_SQL, null);
	}
	public static void execSql(SQLiteDatabase db,String sql, Object[] bindArgs){
		db.execSQL(sql, bindArgs);
	}
	public static void execSql(SQLiteDatabase db,String sql){
		execSql(db,sql, null);
	}
}

写的粗俗...