在SQLiteOpenHelper类里getWritableDatabase()和getReadableDatabase()方法的内部实现都是调用了getDatabaseLocked()方法,该方法是处理数据库创建,表创建的核心方法。他定义了一系列逻辑骨架,但并不是具体的实现者。下面来看看该方法在创建完数据库后是怎样处理表的创建:
01.
private
SQLiteDatabase getDatabaseLocked(
boolean
writable) {
02.
03.
//......
04.
05.
db.beginTransaction();
06.
try
{
07.
if
(version ==
0
) {
08.
onCreate(db);
09.
}
else
{
10.
if
(version > mNewVersion) {
11.
onDowngrade(db, version, mNewVersion);
12.
}
else
{
13.
onUpgrade(db, version, mNewVersion);
14.
}
15.
}
16.
db.setVersion(mNewVersion);
17.
db.setTransactionSuccessful();
18.
}
finally
{
19.
db.endTransaction();
20.
}
21.
}
22.
23.
//......
24.
}
1.
在LitePal中,LitePalOpenHelper继承了SQLiteOpenHelper去实现onCreate()和onUpgrade():
01.
package
com.aliao.litepal.tablemanager;
02.
03.
import
android.database.sqlite.SQLiteDatabase;
04.
import
android.database.sqlite.SQLiteOpenHelper;
05.
06.
import
com.aliao.litepal.LitePalApplication;
07.
08.
/**
09.
* Created by 丽双 on 2015/6/9.
10.
*/
11.
public
class
LitePalOpenHelper
extends
SQLiteOpenHelper{
12.
13.
public
LitePalOpenHelper(String name,
int
version) {
14.
//将数据库名及版本号传给父类SQLiteOpenHelper
15.
super
(LitePalApplication.getContext(), name,
null
, version);
16.
}
17.
18.
@Override
19.
public
void
onCreate(SQLiteDatabase db) {
20.
Generator.create(db);
21.
}
22.
23.
@Override
24.
public
void
onUpgrade(SQLiteDatabase db,
int
oldVersion,
int
newVersion) {
25.
}
26.
}
01.
package
com.aliao.litepal.tablemanager;
02.
03.
import
android.database.sqlite.SQLiteDatabase;
04.
05.
/**
06.
* Created by 丽双 on 2015/6/10.
07.
*/
08.
public
class
Generator {
09.
10.
static
void
create(SQLiteDatabase db){
11.
create(db,
true
);
12.
}
13.
14.
private
static
void
create(SQLiteDatabase db,
boolean
force){
15.
Creator creator =
new
Creator();
16.
creator.createOrUpgradeTable(db, force);
17.
}
18.
19.
}
01.
package
com.aliao.litepal.tablemanager;
02.
03.
import
android.database.sqlite.SQLiteDatabase;
04.
import
com.aliao.litepal.tablemanager.model.TableModel;
05.
import
com.aliao.litepal.util.Const;
06.
import
java.util.Collection;
07.
08.
/**
09.
* Created by 丽双 on 2015/6/10.
10.
*/
11.
public
class
Creator {
12.
13.
private
Collection<TableModel> mTableModels;
14.
15.
/**
16.
* 根据表的映射对象来创建表-批量创建表
17.
* @param db
18.
* @param force
19.
*/
20.
protected
void
createOrUpgradeTable(SQLiteDatabase db,
boolean
force){
21.
for
(TableModel tableModel:getAllTableModels()){
22.
execute(getCreateTableSQLs(tableModel, db, force), db);
23.
giveTableSchemaACopy(tableModel.getTableName(), Const.TableSchema.NORMAL_TABLE, db);
24.
}
25.
}
26.
27.
/**
28.
* 批量执行sql语句来创建表
29.
* @param sqls
30.
* @param db
31.
*/
32.
protected
void
execute(String[] sqls, SQLiteDatabase db) {
33.
34.
}
35.
36.
/**
37.
* 获取创建表的sql语句
38.
* @param tableModel
39.
* @param db
40.
* @param force
41.
* @return
42.
*/
43.
protected
String[] getCreateTableSQLs(TableModel tableModel, SQLiteDatabase db,
boolean
force) {
44.
return
new
String[
0
];
45.
}
46.
47.
/**
48.
* 通过所有关系映射的对象类名集合来组装表模型集合
49.
* @return
50.
*/
51.
private
Collection<TableModel> getAllTableModels() {
52.
53.
return
mTableModels;
54.
}
55.
56.
/**
57.
* 将新创建的表的表名备份到table_schema中
58.
* @param tableName
59.
* @param tableType
60.
* @param db
61.
*/
62.
protected
void
giveTableSchemaACopy(String tableName,
int
tableType, SQLiteDatabase db) {
63.
64.
}
65.
}
先来看下execute()方法:
01.
/**
02.
* 批量执行sql语句来创建表
03.
* @param sqls
04.
* @param db
05.
*/
06.
protected
void
execute(String[] sqls, SQLiteDatabase db) {
07.
String throwSQL =
""
;
08.
try
{
09.
if
(sqls !=
null
){
10.
for
(String sql:sqls){
11.
db.execSQL(BaseUtility.changeCase(sql));
12.
}
13.
}
14.
}
catch
(SQLException e){
15.
throw
new
DatabaseGenerateException(DatabaseGenerateException.SQL_ERROR + throwSQL);
16.
}
17.
}
001.
package
com.aliao.litepal.tablemanager;
002.
003.
import
android.database.SQLException;
004.
import
android.database.sqlite.SQLiteDatabase;
005.
import
android.text.TextUtils;
006.
007.
import
com.aliao.litepal.exceptions.DatabaseGenerateException;
008.
import
com.aliao.litepal.parser.LitePalAttr;
009.
import
com.aliao.litepal.tablemanager.model.TableModel;
010.
import
com.aliao.litepal.util.BaseUtility;
011.
import
com.aliao.litepal.util.Const;
012.
import
com.aliao.litepal.util.DBUtility;
013.
014.
import
java.lang.reflect.Field;
015.
import
java.lang.reflect.Modifier;
016.
import
java.util.ArrayList;
017.
import
java.util.Collection;
018.
import
java.util.Iterator;
019.
import
java.util.List;
020.
import
java.util.Map;
021.
import
java.util.Set;
022.
023.
/**
024.
* Created by 丽双 on 2015/6/10.
025.
*/
026.
public
class
Creator {
027.
028.
private
Collection<TableModel> mTableModels;
029.
030.
/**
031.
* 根据表的映射对象来创建表-批量创建表
032.
* @param db
033.
* @param force
034.
*/
035.
protected
void
createOrUpgradeTable(SQLiteDatabase db,
boolean
force){
036.
for
(TableModel tableModel:getAllTableModels()){
037.
execute(getCreateTableSQLs(tableModel, db, force), db);
038.
giveTableSchemaACopy(tableModel.getTableName(), Const.TableSchema.NORMAL_TABLE, db);
039.
}
040.
}
041.
042.
/**
043.
* 批量执行sql语句来创建表
044.
* @param sqls
045.
* @param db
046.
*/
047.
protected
void
execute(String[] sqls, SQLiteDatabase db) {
048.
String throwSQL =
""
;
049.
try
{
050.
if
(sqls !=
null
){
051.
for
(String sql:sqls){
052.
db.execSQL(BaseUtility.changeCase(sql));
053.
}
054.
}
055.
}
catch
(SQLException e){
056.
throw
new
DatabaseGenerateException(DatabaseGenerateException.SQL_ERROR + throwSQL);
057.
}
058.
}
059.
060.
/**
061.
* 获取创建表的sql语句
062.
* @param tableModel
063.
* @param db
064.
* @param force
065.
* @return
066.
*/
067.
private
String[] getCreateTableSQLs(TableModel tableModel, SQLiteDatabase db,
boolean
force) {
068.
//force用来判断,是否要先删除已存在的表再新建
069.
if
(force){
070.
return
new
String[]{
071.
generateDropTableSQL(tableModel),
072.
generateCreateTableSQL(tableModel)
073.
};
074.
}
else
{
075.
if
(DBUtility.isTableExists(tableModel.getTableName(), db)) {
076.
return
null
;
077.
}
else
{
078.
return
new
String[] { generateCreateTableSQL(tableModel) };
079.
}
080.
}
081.
}
082.
083.
/**
084.
* 生成一个删除表的sql语句
085.
* @param tableModel
086.
* @return
087.
*/
088.
private
String generateDropTableSQL(TableModel tableModel) {
089.
return
generateDropTableSQL(tableModel.getTableName());
090.
}
091.
protected
String generateDropTableSQL(String tableName) {
092.
return
"drop table if exists "
+ tableName;
093.
}
094.
095.
/**
096.
* 生成一个创建表的sql语句
097.
* @param tableModel
098.
* @return
099.
*/
100.
private
String generateCreateTableSQL(TableModel tableModel) {
101.
return
generateCreateTableSQL(tableModel.getTableName(), tableModel.getColumns(),
true
);
102.
}
103.
protected
String generateCreateTableSQL(String tableName, Map<String, String> columnsMap,
boolean
autoIncrementId) {
104.
Set<String> columnNames = columnsMap.keySet();
105.
//判断集合里是有含有id或_id的列名,如果有则删除。防止生成两个id列,因为每个sql语句会自己创建id列作为主键
106.
removeId(columnNames);
107.
StringBuilder createTableSQL =
new
StringBuilder(
"create table "
);
108.
createTableSQL.append(tableName).append(
" ("
);
109.
//创建id作为主键,autoIncrementId总是true
110.
if
(autoIncrementId){
111.
createTableSQL.append(
"id integer primary key autoincrement,"
);
112.
}
113.
//判断有没有列名,没有列名就把刚拼接的sql语句最后那个逗号给去了
114.
Iterator<String> i = columnNames.iterator();
115.
if
(!i.hasNext()){
116.
createTableSQL.deleteCharAt(createTableSQL.length() -
1
);
117.
}
118.
boolean
needSeparator =
false
;
119.
while
(i.hasNext()){
120.
if
(needSeparator){
121.
createTableSQL.append(
", "
);
122.
}
123.
needSeparator =
true
;
124.
String columnName = i.next();
125.
createTableSQL.append(columnName).append(
" "
).append(columnsMap.get(columnName));
126.
}
127.
createTableSQL.append(
")"
);
128.
return
createTableSQL.toString();
129.
}
130.
131.
private
void
removeId(Set<String> columnNames) {
132.
String idName =
""
;
133.
for
(String columnName : columnNames) {
134.
if
(isIdColumn(columnName)) {
135.
idName = columnName;
136.
break
;
137.
}
138.
}
139.
if
(!TextUtils.isEmpty(idName)) {
140.
columnNames.remove(idName);
141.
}
142.
}
143.
protected
boolean
isIdColumn(String columnName) {
144.
return
"_id"
.equalsIgnoreCase(columnName) ||
"id"
.equalsIgnoreCase(columnName);
145.
}
146.
//........
147.
}
01.
package
com.aliao.litepal.tablemanager.model;
02.
03.
import
java.util.HashMap;
04.
import
java.util.Map;
05.
import
java.util.Set;
06.
07.
/**
08.
* Created by 丽双 on 2015/6/10.
09.
*/
10.
public
class
TableModel {
11.
12.
//表名
13.
private
String tableName;
14.
//列名是key,列的类型是value
15.
private
Map<String, String> columnsMap =
new
HashMap<>();
16.
//类名
17.
private
String className;
18.
19.
public
String getTableName() {
20.
return
tableName;
21.
}
22.
23.
public
void
setTableName(String tableName) {
24.
this
.tableName = tableName;
25.
}
26.
27.
public
Map<String, String> getColumns() {
28.
return
columnsMap;
29.
}
30.
31.
public
void
addColumns(String columnName, String columnType) {
32.
columnsMap.put(columnName, columnType);
33.
}
34.
35.
public
String getClassName() {
36.
return
className;
37.
}
38.
39.
public
void
setClassName(String className) {
40.
this
.className = className;
41.
}
42.
43.
public
Set<String> getColumnNames(){
44.
return
columnsMap.keySet();
45.
}
46.
}
01.
/**
02.
* 根据表的映射对象来创建表-批量创建表
03.
* @param db
04.
* @param force
05.
*/
06.
protected
void
createOrUpgradeTable(SQLiteDatabase db,
boolean
force){
07.
for
(TableModel tableModel:getAllTableModels()){
08.
execute(getCreateTableSQLs(tableModel, db, force), db);
09.
giveTableSchemaACopy(tableModel.getTableName(), Const.TableSchema.NORMAL_TABLE, db);
10.
}
11.
}
要构建tablemodel的信息,要拿到表名,也就是对应的映射关系的类名(没有包名前缀);类名即解析litepal.xml获取到的带有完整包名的类名;字段名即列名,字段的数据类型即列的类型。通过LitePalAttr类可以获取到在litepal.xml里配置的所有要映射到数据库的类名。基于这些类名再利用反射机制来获取该类的字段名及字段的数据类型。然而获取到的字段的数据类型不能直接用来构建sql语句,我们知道SQLite支持的数据类型有五种:NULL,INTEGER,REAL,TEXT, BLOB。所以要把字段的数据类型转换为这五种类型之一。
001.
package
com.aliao.litepal.tablemanager;
002.
003.
import
android.database.SQLException;
004.
import
android.database.sqlite.SQLiteDatabase;
005.
import
android.text.TextUtils;
006.
007.
import
com.aliao.litepal.exceptions.DatabaseGenerateException;
008.
import
com.aliao.litepal.parser.LitePalAttr;
009.
import
com.aliao.litepal.tablemanager.model.TableModel;
010.
import
com.aliao.litepal.tablemanager.typechange.BooleanOrm;
011.
import
com.aliao.litepal.tablemanager.typechange.DateOrm;
012.
import
com.aliao.litepal.tablemanager.typechange.DecimalOrm;
013.
import
com.aliao.litepal.tablemanager.typechange.NumericOrm;
014.
import
com.aliao.litepal.tablemanager.typechange.OrmChange;
015.
import
com.aliao.litepal.tablemanager.typechange.TextOrm;
016.
import
com.aliao.litepal.util.BaseUtility;
017.
import
com.aliao.litepal.util.Const;
018.
import
com.aliao.litepal.util.DBUtility;
019.
020.
import
java.lang.reflect.Field;
021.
import
java.lang.reflect.Modifier;
022.
import
java.util.ArrayList;
023.
import
java.util.Collection;
024.
import
java.util.Iterator;
025.
import
java.util.List;
026.
import
java.util.Map;
027.
import
java.util.Set;
028.
029.
/**
030.
* Created by 丽双 on 2015/6/10.
031.
*/
032.
public
class
Creator {
033.
034.
private
Collection<TableModel> mTableModels;
035.
036.
/**
037.
* 根据表的映射对象来创建表-批量创建表
038.
* @param db
039.
* @param force
040.
*/
041.
protected
void
createOrUpgradeTable(SQLiteDatabase db,
boolean
force){
042.
for
(TableModel tableModel:getAllTableModels()){
043.
execute(getCreateTableSQLs(tableModel, db, force), db);
044.
giveTableSchemaACopy(tableModel.getTableName(), Const.TableSchema.NORMAL_TABLE, db);
045.
}
046.
}
047.
048.
/**
049.
* 通过所有关系映射的对象类名集合来组装表模型集合
050.
* @return
051.
*/
052.
private
Collection<TableModel> getAllTableModels() {
053.
if
(mTableModels ==
null
){
054.
mTableModels =
new
ArrayList<>();
055.
}
056.
if
(!canUseCache()){
057.
mTableModels.clear();
058.
for
(String className : LitePalAttr.getIntance().getClassNames()){
059.
mTableModels.add(getTableModel(className));
060.
}
061.
}
062.
063.
return
mTableModels;
064.
}
065.
066.
private
OrmChange[] typeChangeRules = {
new
NumericOrm(),
new
TextOrm(),
new
BooleanOrm(),
067.
new
DecimalOrm(),
new
DateOrm() };
068.
069.
/**
070.
* 根据类名来创建表模型
071.
* @param className
072.
* @return
073.
*/
074.
private
TableModel getTableModel(String className) {
075.
String tableName = DBUtility.getTableNameByClassName(className);
076.
TableModel tableModel =
new
TableModel();
077.
tableModel.setTableName(tableName);
//设置表名
078.
tableModel.setClassName(className);
//设置类名
079.
//获取类的所有字段
080.
List<Field> supportedFields = getSupportedFields(className);
081.
//根据字段获取该字段的名字及数据类型,添加到‘字段名-数据类型’的哈希表columnMap里
082.
for
(Field field : supportedFields){
083.
String fieldName = field.getName();
084.
Class<?> fieldTypeClass = field.getType();
085.
String fieldType = fieldTypeClass.getName();
086.
String columnName =
null
;
087.
String columnType =
null
;
088.
//将字段类型转换为SQLite数据库支持的数据类型
089.
for
(OrmChange ormChange:typeChangeRules){
090.
String[] relations = ormChange.object2Relation(className, fieldName, fieldType);
091.
if
(relations !=
null
){
092.
columnName = relations[
0
];
093.
columnType = relations[
1
];
094.
tableModel.addColumns(columnName, columnType);
095.
break
;
096.
}
097.
}
098.
}
099.
100.
return
tableModel;
101.
}
102.
103.
/**
104.
* 利用反射机制获取该类所有的字段
105.
* 1.要求添加的字段的修饰符是私有的且被static的,
106.
* 2.字段的类型要符合基本数据类型
107.
* @param className
108.
* @return
109.
*/
110.
private
List<Field> getSupportedFields(String className) {
111.
112.
List<Field> supportedFields =
new
ArrayList<>();
113.
Class<?> dynamicClass =
null
;
114.
try
{
115.
dynamicClass = Class.forName(className);
116.
}
catch
(ClassNotFoundException e) {
117.
e.printStackTrace();
118.
}
119.
Field[] fields = dynamicClass.getDeclaredFields();
120.
for
(Field field:fields){
121.
int
modifiers = field.getModifiers();
122.
if
(Modifier.isPrivate(modifiers) && !Modifier.isStatic(modifiers)){
123.
Class<?> fieldTypeClass = field.getType();
124.
String fieldType = fieldTypeClass.getName();
125.
if
(BaseUtility.isFieldTypeSupported(fieldType)){
126.
supportedFields.add(field);
127.
}
128.
}
129.
}
130.
return
supportedFields;
131.
}
132.
133.
private
boolean
canUseCache() {
134.
if
(mTableModels ==
null
) {
135.
return
false
;
136.
}
137.
return
mTableModels.size() == LitePalAttr.getIntance().getClassNames().size();
138.
}
139.
140.
//...........
141.
142.
}
在getSupportedFields()里,只有private修饰符及非static修饰的字段才能保留下来,作为支持对象关系映射的字段。那么也就是说如果有的字段不想映射到数据库表中,可以使用public,protected或者default修饰符。
再看getTableModel()中java基本类型向SQLite支持的数据类型转换的实现:
01.
for
(OrmChange ormChange : typeChangeRules) {
02.
String[] relations = ormChange.object2Relation(className, fieldName, fieldType);
03.
if
(relations !=
null
) {
04.
columnName = relations[
0
];
05.
columnType = relations[
1
];
06.
tableModel.addColumn(columnName, columnType);
07.
break
;
08.
}
09.
}
NumericOrm类,将基本类型int,long,short或者其包装类转换为INTEGER类型
TextOrm类,将基本类型char或者其包装类Character、String转换为TEXT类型
BooleanOrm类,将boolean或者其包装类Boolean转换为INTEGER类型。Note:SQLite 并没有单独的布尔存储类型,而是将布尔值存储为整数 0 (false) 和 1 (true)。
DecimalOrm类,将float,double或者其包装类转换为REAL类型
DateOrm类,将Date类型转换为INTEGER类型
(正如郭神blog所说LitePal支持对象关系映射的数据类型共8种,int、short、long、float、double、boolean、String和Date)
看一个NumericOrm类的具体实现:
01.
package
com.aliao.litepal.tablemanager.typechange;
02.
03.
/**
04.
* Created by 丽双 on 2015/6/11.
05.
*/
06.
public
class
NumericOrm
extends
OrmChange {
07.
08.
/**
09.
* 如果传入的字段类型是int,long或者short,则把他转换为integer类型作为列的数据类型
10.
* @param className
11.
* @param fieldName
12.
* @param fieldType
13.
* @return
14.
*/
15.
@Override
16.
public
String[] object2Relation(String className, String fieldName, String fieldType) {
17.
if
(fieldName !=
null
&& fieldType !=
null
) {
18.
String[] relations =
new
String[
2
];
19.
relations[
0
] = fieldName;
20.
if
(fieldType.equals(
"int"
) || fieldType.equals(
"java.lang.Integer"
)) {
21.
relations[
1
] =
"INTEGER"
;
22.
return
relations;
23.
}
24.
if
(fieldType.equals(
"long"
) || fieldType.equals(
"java.lang.Long"
)) {
25.
relations[
1
] =
"INTEGER"
;
26.
return
relations;
27.
}
28.
if
(fieldType.equals(
"short"
) || fieldType.equals(
"java.lang.Short"
)) {
29.
relations[
1
] =
"INTEGER"
;
30.
return
relations;
31.
}
32.
}
33.
return
null
;
34.
}
35.
36.
}
创建表的涉及的几乎所有代码就这些,运行项目创建表后,用sqlite命令行查询UserInfo表的数据结构和建表语句:
select * from sqlite_master where name = 'UserInfo'
对应的UserInfo类:
01.
package
com.aliao.learninglitepal.entity;
02.
03.
import
java.io.Serializable;
04.
import
java.util.ArrayList;
05.
import
java.util.List;
06.
07.
/**
08.
* Created by 丽双 on 2015/6/5.
09.
* 用户表
10.
*/
11.
public
class
UserInfo
implements
Serializable {
12.
private
long
id;
13.
private
String userId;
14.
private
String userName;
15.
private
String permissionEndTime;
16.
private
String realName;
17.
private
String email;
18.
19.
private
List<SurveyInfo> surveyInfos =
new
ArrayList<>();
//与模板问卷一对多
20.
21.
public
long
getId() {
22.
return
id;
23.
}
24.
25.
public
void
setId(
long
id) {
26.
this
.id = id;
27.
}
28.
29.
public
String getEmail() {
30.
return
email;
31.
}
32.
33.
public
void
setEmail(String email) {
34.
this
.email = email;
35.
}
36.
37.
public
String getUserId() {
38.
return
userId;
39.
}
40.
41.
public
void
setUserId(String userId) {
42.
this
.userId = userId;
43.
}
44.
45.
public
String getUserName() {
46.
return
userName;
47.
}
48.
49.
public
void
setUserName(String userName) {
50.
this
.userName = userName;
51.
}
52.
53.
public
String getPermissionEndTime() {
54.
return
permissionEndTime;
55.
}
56.
57.
public
void
setPermissionEndTime(String permissionEndTime) {
58.
this
.permissionEndTime = permissionEndTime;
59.
}
60.
61.
public
String getRealName() {
62.
return
realName;
63.
}
64.
65.
public
void
setRealName(String realName) {
66.
this
.realName = realName;
67.
}
68.
69.
public
List<SurveyInfo> getSurveyInfos() {
70.
return
surveyInfos;
71.
}
72.
73.
public
void
setSurveyInfos(List<SurveyInfo> surveyInfos) {
74.
this
.surveyInfos = surveyInfos;
75.
}
76.
77.
}
到此为止,表的创建就结束了