因为在开发客户端的时候,服务器端的有写数据是重复的,不需要再去访问服务器的,然后服务器端提供的是一个sql文件,里面包含了数据库和数据,我们这些开发客户端的不可能一行一行的进行手动入库吧?所以我就想到了直接读取sql文件进行创建数据并插入数据好了。
创建DBHelp并继承SQLiteOpenHelper
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
public
class
DBHelper
extends
SQLiteOpenHelper {
private
Context mContext;
public
DBHelper(Context context, String databaseName,
CursorFactory factory,
int
version) {
super
(context, databaseName, factory, version);
mContext = context;
}
/**
* 数据库第一次创建时调用
* */
@Override
public
void
onCreate(SQLiteDatabase db) {
if
(!tabIsExist(
"test"
, db)) {
executeAssetsSQL(db,
"test.sql"
);
// db.execSQL(sql);
//System.out.println("创建表");
}
}
/**
* 数据库升级时调用
* */
@Override
public
void
onUpgrade(SQLiteDatabase db,
int
oldVersion,
int
newVersion) {
// 数据库不升级
if
(newVersion <= oldVersion) {
return
;
}
Configuration.oldVersion = oldVersion;
int
changeCnt = newVersion - oldVersion;
for
(
int
i =
0
; i < changeCnt; i++) {
// 依次执行updatei_i+1文件 由1更新到2 [1-2],2更新到3 [2-3]
String schemaName =
"update"
+ (oldVersion + i) +
"_"
+ (oldVersion + i +
1
) +
".sql"
;
executeAssetsSQL(db, schemaName);
}
}
/**
* 读取数据库文件(.sql),并执行sql语句
* */
private
void
executeAssetsSQL(SQLiteDatabase db, String schemaName) {
BufferedReader in =
null
;
try
{
in =
new
BufferedReader(
new
InputStreamReader(mContext.getAssets()
.open(Configuration.DB_PATH +
"/"
+ schemaName)));
//System.out.println("路径:" + Configuration.DB_PATH + "/" + schemaName);
String line;
String buffer =
""
;
while
((line = in.readLine()) !=
null
) {
buffer += line;
if
(line.trim().endsWith(
";"
)) {
db.execSQL(buffer.replace(
";"
,
""
));
buffer =
""
;
}
}
}
catch
(IOException e) {
Log.e(
"db-error"
, e.toString());
}
finally
{
try
{
if
(in !=
null
)
in.close();
}
catch
(IOException e) {
Log.e(
"db-error"
, e.toString());
}
}
}
public
List selectAllCities(SQLiteDatabase db) {
List areas =
new
ArrayList();
Area area;
String sql =
"select * from test where area_level=?"
;
Cursor cursor = db.rawQuery(sql,
new
String[] {
""
+
0
});
while
(cursor.moveToNext()){
area =
new
Area();
area.setId(cursor.getInt(
0
));
area.setArea_name(cursor.getString(
2
));
areas.add(area);
area =
null
;
}
cursor.close();
return
areas;
}
public
List selectAllAreas(SQLiteDatabase db,
int
parent_id) {
List areas =
new
ArrayList();
Area area;
String sql =
"select * from test where parent_id=?"
;
Cursor cursor = db.rawQuery(sql,
new
String[] {
""
+ parent_id });
while
(cursor.moveToNext()){
area =
new
Area();
area.setId(cursor.getInt(
0
));
area.setArea_name(cursor.getString(
2
));
areas.add(area);
area =
null
;
}
cursor.close();
return
areas;
}
/**
* 判断是否存在某一张表
* @param tabName
* @param db
* @return
*/
public
boolean
tabIsExist(String tabName, SQLiteDatabase db) {
boolean
result =
false
;
if
(tabName ==
null
) {
return
false
;
}
Cursor cursor =
null
;
try
{
String sql =
"select count(*) as c from sqlite_master where type ='table' and name ='"
+ tabName.trim() +
"' "
;
cursor = db.rawQuery(sql,
null
);
if
(cursor.moveToNext()) {
int
count = cursor.getInt(
0
);
if
(count >
0
) {
result =
true
;
}
}
}
catch
(Exception e) {
}
return
result;
}
}
|
Configuration.java是一些常量
1
2
3
4
5
6
7
|
public
class
Configuration {
public
static
final
String DB_PATH =
"schema"
;
public
static
final
String DB_NAME =
"test.db"
;
public
static
final
int
DB_VERSION =
1
;
public
static
int
oldVersion = -
1
;
}
|
其实这个过程非常的简单易懂,就是根据路径去读取文件,然后读取文件里面的内容,再根据关键字,sqllite会自动进行相应的操作,所以这个sql文件中的sql语句一定要规范,不然会写入不了的。
在activity中调用:
1
2
3
|
dbHelper =
new
DBHelper(
this
,
"test"
,
null
,
1
);
dbHelper.onCreate(dbHelper.getWritableDatabase());
|