简单说说如何把json或者txt文件转换为db

前言

最近在Github上找到一个中国城市的json文件，虽然也有db文件，但是想通过这个json文件生成自己的数据库表。下面就简单来转换成自己的数据库

准备json

在Github找到一个下面json表

[
  {
    "areaId" : "4521984",
    "areaName" : "北京",
    "cities" : [
      {
        "areaId" : "4521985",
        "areaName" : "北京市",
        "counties" : [
          {
            "areaId" : "4521986",
            "areaName" : "东城区"
          },
          {
            "areaId" : "4521987",
            "areaName" : "西城区"
          },
          {
            "areaId" : "4521988",
            "areaName" : "朝阳区"
          },
          {
            "areaId" : "4521989",
            "areaName" : "丰台区"
          },
          {
            "areaId" : "4521990",
            "areaName" : "石景山区"
          },
          {
            "areaId" : "4521991",
            "areaName" : "海淀区"
          },
          {
            "areaId" : "4521992",
            "areaName" : "门头沟区"
          },
          {
            "areaId" : "4521993",
            "areaName" : "房山区"
          },
          {
            "areaId" : "4521994",
            "areaName" : "通州区"
          },
          {
            "areaId" : "4521995",
            "areaName" : "顺义区"
          },
          {
            "areaId" : "4521996",
            "areaName" : "昌平区"
          },
          {
            "areaId" : "4521997",
            "areaName" : "大兴区"
          },
          {
            "areaId" : "4521998",
            "areaName" : "怀柔区"
          },
          {
            "areaId" : "4521999",
            "areaName" : "平谷区"
          },
          {
            "areaId" : "4522001",
            "areaName" : "密云县"
          },
          {
            "areaId" : "4522002",
            "areaName" : "延庆县"
          }
        ]
      }
    ]
  }
....
]

完整的json文件可Goach_Frame的app 里面的assets里面查看

转换为相对格式的txt文件

把上文的json文件相对的City.txt文件

0,4521984,0,北京
1,4522003,0,天津
2,4522022,0,河北
3,4522217,0,山西
4,4522359,0,内蒙古
5,4522482,0,辽宁
6,4522611,0,吉林
7,4522689,0,黑龙江
8,4522847,0,上海
9,4522868,0,江苏
10,4523000,0,浙江
..........

代码如下:

rxPermissions.request(Manifest.permission.WRITE_EXTERNAL_STORAGE,
                    Manifest.permission.READ_EXTERNAL_STORAGE)
                    .subscribe({
                        t: Boolean ->
                        if(t){
                            val cityJson = AssetsUtils.getStringForAssets(context,"city.txt")
                            val mCitys = mGson.fromJson<List<CityResponse>>(cityJson,object: TypeToken<List<CityResponse>>(){}.type)
                            val stringBuilder = StringBuilder()
                            //省
                            mCitys.forEachIndexed { index, cityResponse ->
                                stringBuilder.append("$index,${cityResponse.areaId},0,${cityResponse.areaName}\n")
                            }
                            var cityCount = mCitys.size
                            //市
                            mCitys.forEach {
                                val parentId = it.areaId
                                it.cities.forEachIndexed { index, city ->
                                    stringBuilder.append("${cityCount+index},${city.areaId},$parentId,${city.areaName}\n")
                                }
                                cityCount += it.cities.size
                            }
                            //区,县
                            mCitys.forEach {
                                it.cities.forEach {
                                    val parentId = it.areaId
                                    it.counties.forEachIndexed { index, city ->
                                        stringBuilder.append("${cityCount+index},${city.areaId},$parentId,${city.areaName}\n")
                                    }
                                    cityCount += it.counties.size
                                }
                            }
                            AssetsUtils.stringToFile(context,"City.txt",stringBuilder.toString())
                        }
                    })

AssetsUtils.kt

object AssetsUtils {

    fun getStringForAssets(context: Context,name:String):String{
        try {
            val inputStream = context.assets.open(name)
            val br = BufferedReader(InputStreamReader(inputStream))
            val sb = StringBuffer()
            var len = br.readLine()
            while(len != null){
                sb.append(len)
                len = br.readLine()
            }
            inputStream.close()
            return sb.toString()
        }catch (e:FileNotFoundException){
            e.printStackTrace()
        }
        return ""
    }
    fun stringToFile(context: Context,fileName: String,saveStr:String){
        val file = File(getSaveDir(context,"City"),fileName)
        if(!file.exists()||file.length() == 0L){
            file.deleteOnExit()
            file.createNewFile()
            file.outputStream().use { it.write(saveStr.toByteArray(Charsets.UTF_8)) }
        }
    }
    private fun getSaveDir(context: Context,uniqueName:String):File{
        val cachePath: String
        val file = context.getExternalFilesDir(null)
        cachePath = if (file != null && (Environment.MEDIA_MOUNTED == Environment.getExternalStorageState() || !Environment.isExternalStorageRemovable())) {
            file.path
        } else {
            context.filesDir.path
        }
        val targetFile = File(cachePath + File.separator + uniqueName)
        if(!targetFile.exists()) targetFile.mkdirs()
        return targetFile
    }
}

CityResponse.kt

class CityResponse:Serializable{
    var areaId:Long = 0
    var areaName:String = ""
    var cities:List<City> = emptyList()
    class City(val areaId:Long, val areaName:String,val counties:List<Town>):Serializable
    class Town(val areaId:Long, val areaName:String):Serializable
}

生成city.db

这时候需要准备一台已经root的手机或者安装可以root的模拟器，这里使用夜神模拟器来做相应的操作
- 首先在Terminal里把city.txt文件push到相对应用的databases路径下，如这里我的路径是

adb push E:/City.txt data/data/com.goach.client/databases

第一个路径是txt放在电脑上的路径，第二个路径需要push的位置。
执行命令成功后

就可以在data/data/com.goach.client/databases看到City.txt文件

然后执行下面几个命令创建db

adb shell
cd data/data/com.goach.client/databases
sqlite3 City.db

执行成功如下

然后创建City表，执行命令和结果如下

.table是查看当前db里面存在的表

最后把City.txt导入到表里面

通过,分割，然后导入表里面就可以了。在databases里面可以查看City.db

然后把db导出到电脑，最后表如下