Android基础总结（5）——数据存储，持久化技术

 1 public class CoolWeatherOpenHelper extends SQLiteOpenHelper {
 2     
 3     /**
 4      *  Province表建表语句
 5      */
 6     public static final String CREATE_PROVINCE = "create table Province ("
 7                 + "id integer primary key autoincrement, " 
 8                 + "province_name text, "
 9                 + "province_code text)";
10     /**
11      *  City表建表语句
12      */
13     public static final String CREATE_CITY = "create table City ("
14                 + "id integer primary key autoincrement, " 
15                 + "city_name text, " 
16                 + "city_code text, " 
17                 + "province_id integer)";
18     /**
19      *  County表建表语句
20      */
21     public static final String CREATE_COUNTY = "create table County ("
22                 + "id integer primary key autoincrement, " 
23                 + "county_name text, " 
24                 + "county_code text, " 
25                 + "city_id integer)";
26 
27     public CoolWeatherOpenHelper(Context context, String name, CursorFactory factory,
28             int version) {
29         super(context, name, factory, version);
30     }
31 
32     @Override
33     public void onCreate(SQLiteDatabase db) {
34         db.execSQL(CREATE_PROVINCE);  // 创建Province表
35         db.execSQL(CREATE_CITY);  // 创建City表
36         db.execSQL(CREATE_COUNTY);  // 创建County表
37     }
38 
39     @Override
40     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
41         //调用db.execSQL()方法执行数据库的更新和升级，
42         
43         onCreate(db) ;  //重新创建数据库
44     }
45 }

1 public class CoolWeatherDB {
 2 
 3     // 数据库名
 4     public static final String DB_NAME = "cool_weather" ;
 6     // 数据库版本
 7     public static final int VERSION = 1;
 8 
 9     private static CoolWeatherDB coolWeatherDB;
10 
11     private SQLiteDatabase db;
12 
13     // 将构造方法私有化
14     private CoolWeatherDB(Context context) {
15         // 创建帮助类对象
16         CoolWeatherOpenHelper dbHelper = new CoolWeatherOpenHelper(context,DB_NAME, null, VERSION);
18          // 创建数据库
19         db = dbHelper.getWritableDatabase();
20     }
21 
22     // 获取CoolWeatherDB的实例。
23     public synchronized static CoolWeatherDB getInstance(Context context) {
24         if (coolWeatherDB == null) {
25             coolWeatherDB = new CoolWeatherDB(context);
26         }
27         return coolWeatherDB;
28     }    
29 } 

 1 /**
 2  * 将County实例存储到数据库。
 3  */
 4 public void saveCounty(County county) {
 5     if (county != null) {
 6         ContentValues values = new ContentValues();
 7         values.put("county_name", county.getCountyName());
 8         values.put("county_code", county.getCountyCode());
 9         values.put("city_id", county.getCityId());
10         db.insert("County", null, values);
11     }
12 }

 1 /**
 2  * 对存储到数据库的County实例进行更新。
 3  */
 4 public void updateCounty(County county) {
 5     if (county != null) {
 6         ContentValues values = new ContentValues();
 7         values.put("county_name", "beijing");
 8         values.put("county_code", "001");
 9         db.update("County", values, "city_id = ?", new String [] {"010201"}) ;
10     }
11 }

 1 /**
 2  * 删除存储到数据库中的某些County实例。
 3  */
 4 public void deleteCounty(County county) {
 5  6         db.delete("County", "county_name = ? ", new String [] {"beijing"}) ; 7 }

 1 /**
 2  * 从数据库读取某城市下所有的县信息。
 3  */
 4 public List<County> loadCounties(int cityId) {
 5     List<County> list = new ArrayList<County>();
 6     Cursor cursor = db.query("County", null, "city_id = ?",
 7             new String[] { String.valueOf(cityId) }, null, null, null);
 8     if (cursor.moveToFirst()) {
 9         do {
10             County county = new County();
11             county.setId(cursor.getInt(cursor.getColumnIndex("id")));
12             county.setCountyName(cursor.getString(cursor
13                     .getColumnIndex("county_name")));
14             county.setCountyCode(cursor.getString(cursor
15                     .getColumnIndex("county_code")));
16             county.setCityId(cityId);
17             list.add(county);
18         } while (cursor.moveToNext());
19     }
20     return list;
21 }

 1 public void operateSQL(){
 2     //插入
 3     db.execSQL("insert into Country (country_name, country_code, city_id) values (?,?,?)",new String [] {"beijing","001","111"});
 4     //更新
 5     db.execSQL("update Country set country_name = ? where country_code = ?", new String [] {"beijing","002"});
 6     //删除
 7     db.execSQL("delete from Country where contry_code = ?", new String [] {"002"});
 8     //查询，注意：查询是用的rawQuery()方法，不再是execSQL()方法
 9     db.rawQuery("select * from Country", null) ;    
10 }

 1 public void operateSQL() {
 2     db.beginTransaction() ;
 3     try {
 4         // 插入
 5         db.execSQL("insert into Country (country_name, country_code, city_id) values (?,?,?)",
 6                 new String[] { "beijing", "001", "111" });
 7         // 更新
 8         db.execSQL("update Country set country_name = ? where country_code = ?", new String[] { "beijing", "002" });
 9         // 删除
10         db.execSQL("delete from Country where contry_code = ?", new String[] { "002" });
11         // 查询，注意：查询是用的rawQuery()方法，不再是execSQL()方法
12         db.rawQuery("select * from Country", null) ;
13         //如果在endTransaction()执行之前没有执行setTransactionSuccessful()方法的话表明事务执行失败，
14         //所以所有操作一条都不完成
15         db.setTransactionSuccessful() ;
16     } catch (Exception e) {
17         // TODO: handle exception
18     }finally{
19         db.endTransaction() ;
20     }    
21 }