#android training# android.database
来源:互联网 发布:办公自动化软件 编辑:程序博客网 时间:2024/04/28 22:33
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:指定偏移量和获取记录数。
- #android training# android.database
- Android Training
- Android training
- Database: android.database.CursorIndexOutOfBoundsException
- Android-database
- [Android Develop_003]-Android Database
- 【Android Training】置顶索引
- Google Android Training(转载)
- Android Training ---Saving data
- Android Training 学习随记
- Android Tag-start Training
- android training文档目录
- Android Training学习笔记
- Android Training--Fragment
- Android Training--Intent
- Android Training--SnackBar
- Android Training--Saving Files
- Android Training--Capture Photos
- 01序列2
- 汇编把AL最低位送到BL最低位,但保持AL不变
- iOS中property和synthesize的详细说明
- hibernate的sql查询出来封装实体的两种方式
- 代码设计探讨
- #android training# android.database
- c/c++函数 -strtol strtoll strtoul strtoull 使用注意项cc
- eclispe用正则全局搜索中文字符
- java基础面试题
- JVM调优
- 02-线性结构2 一元多项式的乘法与加法运算 (20分)
- mongodb ssh 登录
- Android6.0 SurfaceControl分析(一)SurfaceControl创建&使用 Surface创建&使用
- 5-3 寻找大富翁 (25分)