android.database
android.database.sqlite
Schema and contract
One of the main principles of SQL database is schema.
You may find it helpful to create a companion class, known as a contract class.
A contract class is a container for constants that defines names for URI, tables, and columns.
A good way to organize a contract class is to put definitions that are global to your whole database in the root level of the class;
then create an inner class for each table that enumerates its colums.
For example, this snippet defines the table name and column names for a single table;
By implementing the BaseColumns interface, your inner class can inherit a primary key field called _ID.
public final class FeedReaderContract {
// To prevent someone from accidentally instantiating the contract class,
// give it an empty constructor.
public FeedReaderContract() {}
/* Inner class that defines the table contents */
public static abstract class FeedEntry implements BaseColumns {
public static final String TABLE_NAME = "entry";
public static final String COLUMN_NAME_ENTRY_ID = "entryid";
public static final String COLUMN_NAME_TITLE = "title";
public static final String COLUMN_NAME_SUBTITLE = "subtitle";
...
}
}
private static final String TEXT_TYPE = " TEXT";
private static final String COMMA_SEP = ",";
private static final String SQL_CREATE_ENTRIES =
"CREATE TABLE " + FeedEntry.TABLE_NAME + " (" +
FeedEntry._ID + " INTEGER PRIMARY KEY," +
FeedEntry.COLUMN_NAME_ENTRY_ID + TEXT_TYPE + COMMA_SEP +
FeedEntry.COLUMN_NAME_TITLE + TEXT_TYPE + COMMA_SEP +
... // Any other options for the CREATE command
" )";
private static final String SQL_DELETE_ENTRIES =
"DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME;
Note: Because they can be long-running, be sure that you call getWritableDatabase() or getReadableDatabase() in a background thread,
such as with AsynTask or IntentService.
public class FeedReaderDbHelper extends SQLiteOpenHelper {
// If you change the database schema, you must increment the database version.
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "FeedReader.db";
public FeedReaderDbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public void onCreate(SQLiteDatabase db) {
db.execSQL(SQL_CREATE_ENTRIES);
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// This database is only a cache for online data, so its upgrade policy is
// to simply to discard the data and start over
db.execSQL(SQL_DELETE_ENTRIES);
onCreate(db);
}
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
onUpgrade(db, oldVersion, newVersion);
}
}
下面部分参考:http://blog.csdn.net/jason0539/article/details/10248457
定义为INTEGER PRIMARY KEY的字段只能存储64位整数
SQLiteOpenHelper抽象类:对数据库进行版本管理
SQLiteOpenHelper提供了两个重要的方法:
OnCreate(SQLiteDatabase db)用于初次使用时生成数据库表;
OnUpdate(SQLiteDatabase db, int oldVersion, int newVersion)用于升级软件时更新数据库表结构。
当使用getWriteableDabase() or getReadableDatabase()获取用于操作数据库的SQLiteDatabase实例时,
如果数据库不存在,Android系统会自动生成一个数据库,接着调用onCreate方法,生成数据库表结构和添加一些应用使用到的初始化数据。
OnUpdate 数据库的版本是有程序员控制的。比如现在的版本为1.0,由于业务需要,变更了数据库表结构,这是就需要升级软件。
这时可以把数据库版本设置为2. 然后就会在OnUpdate中实现表格的更新。
public class DatabaseHelper extends SQLiteOpenHelper {
//类没有实例化,是不能用作父类构造器的参数,必须声明为静态
private static final String name = "count"; //数据库名称
private static final int version = 1; //数据库版本
public DatabaseHelper(Context context) {
//第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,设置为null,代表使用系统默认的工厂类
super(context, name, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement, name varchar(20), age INTEGER)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE person ADD phone VARCHAR(12)"); //往表中增加一列
}
}
SQLiteDatabase:操作SQLite数据库
execSQL()可以执行insert, update, delete and create table之类的SQL语句
rawQuery()用于执行select语句
SQLiteDatabase db = ...;
db.execSQL("insert into person(name,age) values ("小红",4)");
db.close();
ps:实际中,values的值为用户输入,要注意特殊字符的处理,以保证组拼好的SQL语句语法正确。
可以使用SQLiteDatabase类重载后的方法execSQL(String sql, Object[] bingArgs),这个方法支持使用占位符参数(?)
db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"小红", 4});
rawQuery()
SQLiteDatabase db = ...;
Cursor cursor = db.rawQuery("select * from person",null);//第一个参数为SQL语句,第二个参数为第一个参数中占位符参数值。
while(cursor.moveToNext()){
int personid = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(2);
}
cursor.close();
db.close();
Cursor cursor = db.rawQuery("select * from person where name like ? and age=?", new String[]{"%小红%", "4"});
Create, Retrieve, Update, Delete
SQLiteDatabase还专门提供了
insert()
delete()
update()
query()
对于熟悉SQL语句的程序员而言,直接使用execSQL()和rawQuery()就能完成这些操作。
insert()
SQLiteDatabase db = databaseHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", "小红");
values.put("age", 4);
long rowid = db.insert(“person”, null, values);
第三个参数如果为null或空,也会插入一条除主键之外其他字段值为null的记录。
第二个参数如果第三个参数values 为Null或者元素个数为0, 由于Insert()方法要求必须添加一条除了主键之外其它字段为Null值的记录,为了满足SQL语法的需要, insert语句必须给定一个字段名,如:insert into person(name) values(NULL),倘若不给定字段名 , insert语句就成了这样: insert into person() values(),显然这不满足标准SQL的语法。对于字段名,建议使用主键之外的字段,如果使用了INTEGER类型的主键字段,执行类似insert into person(personid) values(NULL)的insert语句后,该主键字段值也不会为NULL。如果第三个参数values 不为Null并且元素的个数大于0 ,可以把第二个参数设置为null。的作用:
如果第三个参数values 为Null或者元素个数为0, 由于Insert()方法要求必须添加一条除了主键之外其它字段为Null值的记录,为了满足SQL语法的需要, insert语句必须给定一个字段名,如:insert into person(name) values(NULL),倘若不给定字段名 , insert语句就成了这样: insert into person() values(),显然这不满足标准SQL的语法。对于字段名,建议使用主键之外的字段,如果使用了INTEGER类型的主键字段,执行类似insert into person(personid) values(NULL)的insert语句后,该主键字段值也不会为NULL。如果第三个参数values 不为Null并且元素的个数大于0 ,可以把第二个参数设置为null。
delete()
SQLiteDatabase db = databaseHelper.getWritableDatabase();
db.delete("person", "personid<?", new String[]{"2"});
db.close();
update()
SQLiteDatabase db = databaseHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(“name”, “炸死特”);//key为字段名,value为值
db.update("person", values, "personid=?", new String[]{"1"});
db.close();
query()
SQLiteDatabase db = databaseHelper.getWritableDatabase();
Cursor cursor = db.query("person", new String[]{"personid,name,age"}, "name like ?", new String[]{"%炸死特%"}, null, null, "personid desc", "1,2");
while (cursor.moveToNext()) {
int personid = cursor.getInt(0); //获取第一列的值,第一列的索引从0开始
String name = cursor.getString(1);//获取第二列的值
int age = cursor.getInt(2);//获取第三列的值
}
cursor.close();
db.close();
query(table,colums,selection,selectionArgs, groupBy, having, orderBy, limit)
limit:指定偏移量和获取记录数。