Android 使用Sqlite数据库

一．把数据库文件如"citys.db"，放到assets目录下，再把这个文件拷贝到数据库对应的目录．代码如下：

   if(!getDatabasePath(fileName).exists())//先判断文件是否存在，filename即"citys.db"
  {
   try
   {
    InputStream inStream = this.getAssets().open(fileName);
    File file = new File(getDatabasePath(fileName).toString().substring(0, getDatabasePath(fileName).toString().indexOf(fileName)));
    if(!file.exists()) file.mkdir();//如果目录不存在,则先创建目录
    OutputStream outStream = new FileOutputStream(getDatabasePath(fileName).toString());
    byte[] buffer = new byte[1024];
    int length = 0;
    while((length = inStream.read(buffer)) > 0)
    {
     outStream.write(buffer, 0, length);
    }
    outStream.flush();
    outStream.close();
    inStream.close();
   }
   catch (Exception e)
   {
    e.printStackTrace();
   }
  }

二．打开数据库
方法一:

//getDatabasePath(fileName).toString()即/data/data/getPackageName()/databases/fileName
//此方法打开的数据库必须要先创建好表android_metadata，否则会报错：
sqlite returned: error code = 1, msg = no such table: android_metadata

SQLiteDatabase db = SQLiteDatabase.openDatabase(getDatabasePath(fileName).toString(), null, SQLiteDatabase.OPEN_READONLY);

方法二：

//此方法会在没有这个数据库时自动创建
SQLiteDatabase db = this.openOrCreateDatabase(fileName, MODE_PRIVATE, null); 

三．创建表

db.execSQL("create table if not exists cities(" + "id INTEGER PRIMARY KEY," + "city_name TEXT," + "longitude INTERGER," + "latitude INTERGER," + "province_name TEXT" + ");"); //如果没有citise这个表就创建这个表

四．查询表

 Cursor cursor = db.query("cities", new String[]{"city_name","longitude","latitude"}, "province_name==?", new String[]{"湖南"}, null, null, null);//"cities"为表名
  while(cursor.moveToNext())
  {
   String name =cursor.getString(cursor.getColumnIndex("city_name"));
   System.out.println(name);
  }

五．插入数据

  ContentValues contentValues = new ContentValues();
  contentValues.put("id", 1);
  contentValues.put("city_name", "常德");
  contentValues.put("longitude", 113650001);
  contentValues.put("latitude", 34720001);
  contentValues.put("province_name", "湖南");
  
  db.insert("cities", null, contentValues); 

六．清空数据

db.execSQL("drop table if exists cities;"); 

七．关闭数据库

cursor.close();
db.close();