Android 平台下的 SQLite 使用

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/dreamList/archive/2010/11/05/5990757.aspx

 

SQLite 是一个非常流行嵌入式关系型数据库，之所以这么流行时因为相当小的内存占用和高速的响应，更重要的是他还是免费的。
Android 中， SQLite 是被集成于 Android runtime ，每个 Android 应用程序都可以方便的使用 SQLite 数据库。
如果你熟悉 JDBC ，那么这个掌握SQLite也就几个小时的事情。

Android为我们提供了 SQLiteOpenHelper 抽象类，只要继承它就我们就可以很方便的实现对数据库的创建及更新。SQLiteOpenHelper 抽象类两个很重要的方法onCreate和onUpdate，onCreate用于初次使用软件时创建数据库表，onUpdate用于更新修改数据库表结构。

当调用数据库SQLiteOpenHelper 实例的getWritableDatabase (或getReadableDatabase) 时，android会通过openOrCreateDatabase判断数据库是否存在，如果不存在的，会调用onCeate方法创建数据；如果数据库存在，则会通过判断数据库版本号是否执行onUpdate方法，下面是 抽象类 SQLiteOpenHelper 的部分源码，细看就知道其中的原理了

 ·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······15001. /** 02. * Create and/or open a database that will be used for reading and writing. 03. * Once opened successfully, the database is cached, so you can call this 04. * method every time you need to write to the database. Make sure to call 05. * {@link #close} when you no longer need it. 06. * 07. * <p>Errors such as bad permissions or a full disk may cause this operation 08. * to fail, but future attempts may succeed if the problem is fixed.</p> 09. * 10. * @throws SQLiteException if the database cannot be opened for writing 11. * @return a read/write database object valid until {@link #close} is called 12. */ 13.public synchronized SQLiteDatabase getWritableDatabase() { 14. if (mDatabase != null && mDatabase.isOpen() && !mDatabase.isReadOnly()) { 15. return mDatabase; // The database is already open for business 16. } 17. 18. if (mIsInitializing) { 19. throw new IllegalStateException("getWritableDatabase called recursively"); 20. } 21. 22. // If we have a read-only database open, someone could be using it 23. // (though they shouldn't), which would cause a lock to be held on 24. // the file, and our attempts to open the database read-write would 25. // fail waiting for the file lock. To prevent that, we acquire the 26. // lock on the read-only database, which shuts out other users. 27. 28. boolean success = false; 29. SQLiteDatabase db = null; 30. if (mDatabase != null) mDatabase.lock(); 31. try { 32. mIsInitializing = true; 33. if (mName == null) { 34. db = SQLiteDatabase.create(null); 35. } else { 36. db = mContext.openOrCreateDatabase(mName, 0, mFactory); 37. } 38. 39. int version = db.getVersion(); 40. if (version != mNewVersion) { 41. db.beginTransaction(); 42. try { 43. if (version == 0) { 44. onCreate(db); 45. } else { 46. onUpgrade(db, version, mNewVersion); 47. } 48. db.setVersion(mNewVersion); 49. db.setTransactionSuccessful(); 50. } finally { 51. db.endTransaction(); 52. } 53. } 54. 55. onOpen(db); 56. success = true; 57. return db; 58. } finally { 59. mIsInitializing = false; 60. if (success) { 61. if (mDatabase != null) { 62. try { mDatabase.close(); } catch (Exception e) { } 63. mDatabase.unlock(); 64. } 65. mDatabase = db; 66. } else { 67. if (mDatabase != null) mDatabase.unlock(); 68. if (db != null) db.close(); 69. } 70. } 71.} 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/dreamList/archive/2010/11/05/5990757.aspx

数据库操作工具类代码，继承SQLiteOpenHelper 抽象类，实现数据库的创建和更新。

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······15001.package cn.dreamlist.service; 02.import android.content.Context; 03.import android.database.sqlite.SQLiteDatabase; 04.import android.database.sqlite.SQLiteOpenHelper; 05. 06.public class DBOpenHelper extends SQLiteOpenHelper { 07. 08. public DBOpenHelper(Context context) { 09. /** 10. * 父类构造器中4个参数 11. * 12. * 1.上下文context 13. * context to use to open or create the database 14. * 15. * 2.数据库名,null 内存数据库 16. * name of the database file, or null for an in-memory database 17. * 18. * 3.游标工厂,默认为null 19. * factory to use for creating cursor objects, or null for the default 20. * 21. * 4.数据库版本号,如果版本号非0,且和之前的版本号不相同,则执行onUpgrade方法. 22. * version number of the database (starting at 1); if the database is older, onUpgrade will be used to upgrade the database 23. */ 24. super(context,"dreamlist.db", null, 1); 25. } 26. 27. /** 28. * 创建数据库表 29. */ 30. @Override 31. public void onCreate(SQLiteDatabase db) { 32. db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))"); 33. } 34. 35. /** 36. * 创建更新数据库表 37. */ 38. @Override 39. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 40. db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL"); 41. } 42. 43.} 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/dreamList/archive/2010/11/05/5990757.aspx

下面我们创建PersonService 类，实现对dreamlist.db 数据库的增删改查操作，当然SQLiteDatabase 类还给我们提供了写好的update,insert... 等方法，我们这里用rawQuery 和execSQL 方法加SQL语句实现了，代码看起来更加容易理解。

详细见下面代码

01.package cn.dreamlist.service; 02. 03.import java.util.ArrayList; 04.import java.util.List; 05.import android.content.Context; 06.import android.database.Cursor; 07.import android.database.sqlite.SQLiteDatabase; 08.import cn.dreamlist.domain.Person; 09. 10.public class PersonService { 11. private DBOpenHelper dbOpenHelper; 12. 13. public PersonService(Context context) { 14. dbOpenHelper = new DBOpenHelper(context); 15. } 16. 17. /** 18. * 保存用户 19. * @param person 20. */ 21. public void save(Person person) { 22. //获取SQLiteDatabase对象 23. SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 24. 25. db.execSQL("insert into person(name,phone) values(?,?)", new Object[] { 26. person.getName(), person.getPhone() }); 27. // db.close(); 考虑到性能，这里db不用关闭，因为只有本应用才使用数据库 28. } 29. /** 30. * 更新用户 31. * @param person 32. */ 33. public void update(Person person) { 34. SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 35. db.execSQL( 36. "update person set name=?, phone=? where personid=?", 37. new Object[] { person.getName(), person.getPhone(), 38. person.getId() } 39. ); 40. 41. } 42. 43. /** 44. * 通过ID, 查找用户 45. */ 46. public Person find(Integer id) { 47. SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); 48. Cursor cursor = db.rawQuery( 49. "select * from person where personid=?", 50. new String[] { id.toString() }); 51. if (cursor.moveToFirst()) { 52. int personId = cursor.getInt(cursor.getColumnIndex("personid")); 53. String name = cursor.getString(cursor.getColumnIndex("name")); 54. String phone = cursor.getString(cursor.getColumnIndex("phone")); 55. return new Person(personId,name, phone); 56. } 57. return null; 58. } 59. 60. /** 61. * 通过ID, 删除用户 62. * @param id 63. */ 64. public void delete(Integer id) { 65. SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 66. db.execSQL("delete from person where personid=?", new Object[] { id }); 67. } 68. /** 69. * 获取所有用户记录数 70. * @return 71. */ 72. public long getCount() { 73. SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 74. Cursor cursor = db.rawQuery("select count(*) from person", null); 75. cursor.moveToFirst(); 76. return cursor.getLong(0); 77. } 78. 79. /** 80. * 获取从offset开始的，maxResult条person记录 81. * @return 82. */ 83. public List<Person> getScrollData(int offset, int maxResult) { 84. List<Person> persons = new ArrayList<Person>(); 85. 86. SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 87. Cursor cursor = db.rawQuery( 88. "select * from person limit ?,?", 89. new String[] { String.valueOf(offset), 90. String.valueOf(maxResult) }); 91. while (cursor.moveToNext()) { 92. int personId = cursor.getInt(cursor.getColumnIndex("personid")); 93. String name = cursor.getString(cursor.getColumnIndex("name")); 94. String phone = cursor.getString(cursor.getColumnIndex("phone")); 95. Person person = new Person(personId,name, phone); 96. persons.add(person); 97. } 98. return persons; 99. } 100. 101. /** 102. * 获取所有的person记录 103. * @return 104. */ 105. public List<Person> getAllPerson() { 106. List<Person> persons = new ArrayList<Person>(); 107. 108. SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 109. Cursor cursor = db.rawQuery( 110. "select * from person ", null); 111. while (cursor.moveToNext()) { 112. int personId = cursor.getInt(cursor.getColumnIndex("personid")); 113. String name = cursor.getString(cursor.getColumnIndex("name")); 114. String phone = cursor.getString(cursor.getColumnIndex("phone")); 115. Person person = new Person(personId,name, phone); 116. persons.add(person); 117. } 118. return persons; 119. } 120.} 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/dreamList/archive/2010/11/05/5990757.aspx

JavaBean Person类代码如下：

01.package cn.dreamlist.domain; 02. 03.public class Person { 04. private Integer id; 05. private String name; 06. private String phone; 07. 08. public Person() { 09. } 10. 11. public Person(String name, String phone) { 12. this.name = name; 13. this.phone = phone; 14. } 15. 16. public Person(Integer id, String name, String phone) { 17. this.id = id; 18. this.name = name; 19. this.phone = phone; 20. } 21. 22. public Integer getId() { 23. return id; 24. } 25. 26. public void setId(Integer id) { 27. this.id = id; 28. } 29. 30. public String getName() { 31. return name; 32. } 33. 34. public void setName(String name) { 35. this.name = name; 36. } 37. 38. public String getPhone() { 39. return phone; 40. } 41. 42. public void setPhone(String phone) { 43. this.phone = phone; 44. } 45. 46. @Override 47. public String toString() { 48. return "Person [id=" + id + ", name=" + name + ", phone=" + phone + "]"; 49. } 50.} 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/dreamList/archive/2010/11/05/5990757.aspx