Android SQL总结

作者: 站长 分类: 计算机吧 发布时间: 2014-10-10 21:54 ė1298 views 6没有评论

SQLite Database

SQLite is an embedded relational database engine. Its developers call it a self-contained, serverless, zero-configuration and transactional SQL database engine. 其实只需要记住SQLite不需要数据库服务器,不需要额外配置,其数据库本身就是一个文件的一个小型数据库。 通常情况下在android中创建数据库,系统会在”DATA/data/APP_NAME/databases”.目录为我们创建数据库文件。

常用Select语句

  1. desc <table> //查看表结构
  2. select * from <table> //查询所有更
  3. select , from table ;//查看指定列
  4. select distinct , from table ;//非重复查询
  5. insert into users(_id,username,password) select * from users;//复制
  6. select username from users where username like ‘S%’ ;//非重名字首字母为大写S的用户
  7. select username from users where username like ‘__S%’ ;//非重名字第三个字母为大写S的用户
  8. select * from users where _id in(001,220,230);
  9. select * from user order by _id;//以id的顺序排列
  10. select * from user order by _id desc;//以id反的顺序排

图形环境

很少有人直接在程序当中直接编写SQL语句,麻烦且容易出错。所以最好应该先在一个图形化的环境把SQL语句写好,测试好再加入到代码当中。 而且,SQLite数据库本身就是一个独立的文件,很容易从模拟器上抓取出来。可以使用图形化界面进行分析,和修改 etc.
SQLite expert 是个简单小巧的SQLite数据库管理软件,有免费版和,收费版两种,一般来讲免费版足以应付我们需求。
sqliteexpert官方网址
软件界面截图

使用SQLite expert 创建一张table,并让它为我们生成所需的sql语句

下面的例子,我们创建一张表用来存储用户的用户名和密码;数据库名”users”,表名”user_accounts”,有三个列:row_id INTEGER自增类型的主键,usernameTEXT类型 用来保存用户名,passwordTEXT类型,用来保存用户密码;

创建表格
为了创建一张table 首先要有一个数据库文件,点击”File”->”"New Database然后选择文件的路径,点击OK便创建了一个数据库文件 创建完数据库之后创建表格,右击数据库文件名选择”New Table”如下图所示:

这时我们便进入了”设计状态”,填写表名(user_accounts),点击”Add”,在Name中填写”row_id”,Type选择Integer,如下图所示:
因为row_id这个列比较特殊是我们的索引的主键,所以还要点击”Index”,之后会看到下图:
左边的Available Fields 中有我们刚刚创建的row_id,点击”Add”把row_id加入右边的Index Fields当中。这时上面的Primary和AutoIncrement会变成可先状态 ,勾选Primary和AutoIncrement点击OK;
再回到Field当中创建其它两列,分别为username类型为TEXT,password类型也为TEXT,但不需要创建Index; 完成之后点击Apply,这样我们便使用SQLite expert创建一张表格,点击DDL,我们可以看到SQLite expert已经为我们生成好了生成这张表格所需的SQL语句:
在android中需要代码生成表格时,只要把这段代码复制过去,就可以了。

增加用户
点击SQL,执行以下SQL语句,为表格增加一个用户:

  1. INSERT INTO user_accounts(row_id,username,password) VALUES(001,’John’,'abcdef’);

复制代码

点击Data会发现数据库里边增加了一个用户名为John的用户。为了练习,我们不妨再增加两个用户,David和Sarah
  1. INSERT INTO user_accounts(row_id,username,password) VALUES(002,’David’,’123456′);

     2.INSERT INTO user_accounts(row_id,username,password) VALUES(003,’Sarah’,’00000000′);

 

删除用户
执行下面的语句删除用户David:

DELETE FROM user_accounts WHERE username = ‘David’;

 

修改密码
执行以下语句修改Sarah的密码:

update user_accounts SET password=’666666′ WHERE username = ‘Sarah’;

 

查看所用户信息可以使用如下语句查看表内所有用户的信息:

SELECT * FROMM user_accounts;

 

一般来讲select * 的语句只在测试的时候时候,在正式代码中不推荐使用。

查看指定列的内容执行以下语句查看所有用户的用户名和密码:

SELECT username,password FROM user_accounts;

这时个就发现row_id列没有显示出来。
 
 

查询特定条件的信息
SQL可以通过给定查询条件进行精确查找,比如我们只需要John的密码。就可以使用这样的语句

SELECT password FROM user_accounts WHERE username = ‘John’;

 
 
以下流程图,来源于SQLite官方文档

Create Table Statement

column-def

type-name

column-constraint

table-constraint

foriegn-key-clause

Insert Statement

Delete Statement

qualified-table-name

Update Statement

qualified-table-name

Select Statement

select-core

result-column

join-source

single-source

join-op

join-constraint

ordering-term

compound-operator

Dealing with large amount of data分页当数据库数据量很大时,分页显示是个很简单且符合人们习惯的一种处理方法。
获取数据行总数:
最简单的方法是:SELECT所有的行,再调用curosr.getCount() ;取得行数,但这样效率会很低。 SQLite为我们提供了一个函数很容易查出总共有多少行。有个名为cet6_table的表格,我们想知道总共有多少行的word;可以使用如下 语句来完成:

  1. SELECT count(word) AS number FROM cet6_table;

复制代码

count()函数为我们返回一个Int整形,代表有多少行数据。返回的列的名字叫count(word),为了方便阅读和处理用as number给这个列取个 别名number;
  1. SELECT [word],[explanation] FROM cet6_table ORDER BY word LIMIT 100 OFFSET 200″

复制代码

上语句,返回自第200行的最多100条数据。分页时我们只要修改offset 后面的数据即可取得对应页面的数据。
详情见示例程序!

多线程自带数据库
有些程序比如,字典,输入法等程序会在apk中带有数据库。思路很简单,把预先准备好的数据库放入asset或者raw目录当中 使用时复制到对应的手机目录上;再打开。以下代码会把raw目录中的名为cet6的数据库文件写入sd卡根目录并打开。 对于一些常用或者比较重要的数据建议放在程序的私有目录当中。

  1. public void init(Context context) {
  2.     try {
  3.         String databasePath = Environment.getExternalStorageDirectory().getAbsolutePath();
  4.         String databaseFilename = databasePath + “/” + DATABASE_FILENAME;
  5.         File dir = new File(databasePath);
  6.         if (!dir.exists()){
  7.             dir.mkdir();
  8.         }
  9.         if (!(new File(databaseFilename)).exists()) {
  10.             InputStream is = context.getResources().openRawResource(
  11.                     R.raw.cet6);
  12.             FileOutputStream fos = new FileOutputStream(databaseFilename);
  13.             byte[] buffer = new byte[1024];
  14.             int count = 0;
  15.             while ((count = is.read(buffer)) > 0) {
  16.                 fos.write(buffer, 0, count);
  17.             }
  18.             fos.flush();
  19.             fos.close();
  20.             is.close();
  21.         }
  22.         mCet6Db = SQLiteDatabase.openOrCreateDatabase(databaseFilename,
  23.                 null);
  24.     } catch (Exception e) {
  25.         e.printStackTrace();
  26.     }
  27. }

复制代码

单词提示,IME使用AutoCompleteText + SQLite很容易实现类似于baidu ,Google等搜索时出现的提示文字。 输入法也是同样的原理,以拼音或五笔等作为索引,非常容易实现。可以加一个列标明一个字被选择的次数, 在select的时候可以按照这个列来排序,这样可以保存用户的习惯;作出来的输入法将更人性化。因为输入法的查询量比较大 可以考虑把数据库文件放入内存当中,SQLite本身是支持内存数据库的。但android中封装上没有提供这一功能,而且SQLiteDataBase类 很多核心功能都是通过JNI实现的,所以要把数据库搬入内存还需编写c/c++的代码。也可以考虑使用Lucene的全文搜索功能来实现。Date
SQLite有很多函数支持日期的方便处理。

  1. %d         day of month: 00
  2. %f         fractional seconds: SS.SSS
  3. %H         hour: 00-24
  4. %j         day of year: 001-366
  5. %J         Julian day number
  6. %m         month: 01-12
  7. %M         minute: 00-59
  8. %s         seconds since 1970-01-01
  9. %S         seconds: 00-59
  10. %w         day of week 0-6 with Sunday==0
  11. %W         week of year: 00-53
  12. %Y         year: 0000-9999
  13. %%         %

复制代码

SQLite支持以下格式的日期:
  1. YYYY-MM-DD
  2. YYYY-MM-DD HH:MM
  3. YYYY-MM-DD HH:MM:SS
  4. YYYY-MM-DD HH:MM:SS.SSS
  5. YYYY-MM-DDTHH:MM   //T 就是字母T,用于分开日期和时间
  6. YYYY-MM-DDTHH:MM:SS
  7. YYYY-MM-DDTHH:MM:SS.SSS
  8. HH:MM
  9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. now
  12. DDDDDDDDDD //is the Julian day number expressed as a floating point value.

复制代码

strftime(‘%Y-%m-%d’, column_name)方法可以很方便的对日期进行格式化,而不用Java对String进行再处理。
ContentValues 不支持直接把datetime(‘now’)之类函数作为值插入数据库。 比如想把当前系统时间插件数据库使用ContentValues:
  1. SimpleDateFormat dateFormat = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);
  2. Date date = new Date();
  3. ContentValues initialValues = new ContentValues();
  4. initialValues.put(“date_created”, dateFormat.format(date));
  5. long rowId = mDb.insert(DATABASE_TABLE, null, initialValues);

复制代码

或者如附带程序记事本程序一样使用exeSQL(sql);

SqliteOpenHelper

上流程图来自O’REILLY 视频公开课”Developing Android Applications with Java”中的视频截图
SqliteOpenHelper是android框架为我们提供的一个数据库助手类。对于数据库的操作一般都是从这个类继承。它主要负责数据库的创建,打开,更新 等一些操作。

Singleton DAO
很多有j2ee背景的开发者都喜欢建立一个单例模式的数据库操作类,专门处理数据库之间的交流。也有很多人把这个习惯带到的Android的开发当中。 而且我们也经常需要在几个Activity,Service同时访问一个数据库文件,所以经常能看到类似于这样的代码:

  1. public class DbAccess {
  2.     class DbHelper extends SQLiteOpenHelper {
  3.     //此处代码被省略
  4.     }
  5.     private static DB_NAME = “users”;
  6.     private mDbHelper = null;
  7.     private static mDbAccess;//to make sure there is only one instance exist;
  8.    
  9.     private DbAccess(Context context){
  10.         //private constructer;
  11.         if(mDbHelper == null){
  12.             mDbHelper = new DbHelper(context,DB_NAME);
  13.         }
  14.     }
  15.    
  16.     public DbAccess getInstance(Context context){
  17.         if(mDbAccess == null){
  18.             mDbAccess = new DbAccess(context);
  19.         }
  20.         return mDbAccess;
  21.     }
  22.    
  23.     //部分代码被省略
  24.     //insert
  25.     //delete
  26.     //update
  27.     //query
  28. }
  29. public class ActivityA extends Activity{
  30.     @Override
  31.     protected void onCreate(Bundle savedInstanceState) {
  32.         super.onCreate(savedInstanceState);
  33.         dao = DbAccess.getInstance(this);
  34.         ////部分代码被省略
  35.     }
  36.     ////部分代码被省略
  37. }

复制代码

程序的流程应该是这样的:
假设ActivityA创建了Dao的对象,又开启了ServiceA,A使用了一段时间再跳转到了ActivityB与B再接着使用同一个Dao对象。ActivityA创建Dao对象的时候 把自己的引用向上转型成一个Context对象,Dao又用这个context创建了SQLiteOpenHelper,所以SQLiteOpenHelper就持有了ActivityA的引用。当ActivityA被销毁 之后因为单例的Dao保留有ActivityA的引用,所以ActivityA不能被GC所回收,造成整个Acitivity内存的泄漏。

解决方案
1,最简单的,不使用单例模式,因为SQLiteOpenHelper保证了数据库的单例,所以实际上我们只是创建了多个SQLiteOpenHelper的对象,数据库 对象本身还是单例的。缺点:会有一些重复的代码,并生成多个SQLiteOpenHelper的子类对象。
2,把Dao对象放入Application当中,在Application的onCreate()中使用Application的Context进行初始化。优点,Application对象的创建在Activity和Service之前 所以保证了,Activity和Service等等使用时数据库已经准备好了。缺点:Application的生命周期是最长的,并且Android会尽量不让程序关闭,也就是尽量不 去销毁Application对象,所以数据库一直保持打开状态,依靠Application的onDestroy()关闭数据库,不确定的因素比较大,并且因为打开数据库的时候直接
影响程序启动时间。
3,先看一个问题,为什么SQLiteOpenHelper需要一个Context对象?查看SQLiteOpenHelper的源码发现如下代码:

  1. SQLiteDatabase db = null;
  2. try {
  3.     mIsInitializing = true;
  4.     String path = mContext.getDatabasePath(mName).getPath();
  5.     db = SQLiteDatabase.openDatabase(path, mFactory, SQLiteDatabase.OPEN_READONLY);
  6.     if (db.getVersion() != mNewVersion) {
  7.         throw new SQLiteException(“Can’t upgrade read-only database from version ” +
  8.         db.getVersion() + ” to ” + mNewVersion + “: ” + path);
  9.     }
  10.     onOpen(db);
  11.     Log.w(TAG, “Opened ” + mName + ” in read-only mode”);
  12.     mDatabase = db;

复制代码

所以我们可以自己仿照SQLiteOpenHelper写一个不需要Context的助手类,大constructer当中把路径传进行,而不是一个context对象。缺点:需要考虑多线程同步等 很多因素,有一定的复杂度。
4,在创建Dao对象时,不使用Activity的引用作为Context,而是把Application作为Context传入Dao的构造函数中进行创建。优点,不会影响程序的启动时间。 缺点:对于数据库的关闭还是不确定。
5,在第四种方案的基础上加入计数器。第调用getInstance计数器自增,再加入一个release()方法,计数器自减,当计数器为0时,关闭数据库(或者当计数器为零且 过一段时间没有连接时关闭)。

Snippets

1,记事本,简单CRUID练习,以及日期处理。
 
2,分页显示和简单六级词汇查询(数据库来自”百度文库” )

本文出自 烦死我吧,转载时请注明出处及相应链接。

本文永久链接: http://lishidi.com/pages/android-sql.html

0

发表评论


Ɣ回顶部