Android ormlite升级数据库方案-KJFrameForAndroid升级Sqllite数据库

使用第三方数据库很好可是升级表结构很麻烦, KJLIbary如何升级数据库

现象:用KJDB来写数据库发现已有类添加新字段时会再调用save会报错

因为新添加字段在KJDB在已有的表中无法找到

如果需要升级数据 如给表添加字段就需要卸载重装，非常麻烦。

一种办法是。检测映射类与现有数据库表结构是否变化并修改

我们可以用基于数据库ALTER TABLE命令来升级表，命令详情参见http://www.cnblogs.com/lovko/archive/2009/03/17/1414222.html;

public class MainActivity extends Activity {

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		KJDB db = KJDB.create(this);
		/**检查表结构并升级*/
		db.checkDb();
		
		User user=new User();
		user.username=""+new Random().nextInt();
		user.passworld="123";
		/**追加的新字段*/
		user.newfield="a";
		db.save(user);

	}
}

org.kymjs.aframe.database.KJDB中插入一个新方法

 /**检查类变化时数据库表结构是否变化并修改*/
    public void checkDb() {
        /***/
        Cursor cursor = db.rawQuery(
                "SELECT name FROM sqlite_master WHERE type ='table'", null);
        if (cursor != null) {
            while (cursor.moveToNext()) {
                try {
                    String tablename = cursor.getString(0);
                    String classname = tablename.replaceAll("_", ".");
                    /**查询表所映射类的信息*/
                    TableInfo info = TableInfo.get(classname);
                    if (info != null) {

//						cudb.rawQuery("PRAGMA table_info(tbl_sfg_device)", null);
//						Cursor c=db.rawQuery("PRAGMA table_info("+tablename+")", null);


                        Iterator<Map.Entry<String, Property>> it = info.propertyMap
                                .entrySet().iterator();
                        // 检查该表是否有这个字段
                        HashMap<String, Boolean> map = new HashMap<String, Boolean>();


                        try {
                            Cursor columns = db.rawQuery("PRAGMA table_info("+tablename+")", null);
                            while(columns.moveToNext())
                            {
                                map.put(columns.getString(1), false);
                            }
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                            throw new RuntimeException(e);
                        }
                        /** 遍历类所有字段 */
                        while (it.hasNext()) {

                            Map.Entry<String, Property> item = it.next();
                            String key = item.getKey();
                            /**该字段不存在新建*/
                            if (map.get(key) == null) {
                                db.execSQL("ALTER TABLE " + tablename
                                        + " ADD COLUMN " + key + " " + "CHAR");
                                map.put(key, true);
                            }
                        }
                        it = info.propertyMap
                                .entrySet().iterator();
                        /**删除未映射字段*/
                        while(it.hasNext()){
                            Map.Entry<String, Property> item = it.next();
                            String key = item.getKey();
                            /**该字段未被遍历删除*/
                            if (map.get(key)==true) {
                                db.execSQL("ALTER TABLE "+tablename+" DROP COLUMN "+key);
                            }
                        }
                    }

                } catch (SQLException e) {
                    KJLoger.debug(getClass().getName() + e.getMessage());
                }
            }
        }
        if (cursor != null) {
            cursor.close();
            cursor = null;
        }

    }

这个方法还存在缺陷

就是你的包名和类名如果存在“_” 下划线会报错，不使用下滑线或者使用其他方法实现

如下，传入数据库的类名

 public void checkDb(List<Class> clazzs)
    {
        /***/
        Cursor cursor = db.rawQuery(
                "SELECT name FROM sqlite_master WHERE type ='table'", null);
        if (cursor != null) {
            while (cursor.moveToNext()) {
                try {
                    String tablename = cursor.getString(0);
                    boolean isEquals=false;
                    Class tagetCla=null;
                    Log.d("sqldb", tablename);
                    for(Class cla:clazzs)
                    {
                        isEquals=cla.getName().replaceAll("\\.", "_").equals(tablename);
//                        Log.d("sqldb",cla.getName().replaceAll("\\.", "_").equals(tablename)+" "+cla.getName().replaceAll("\\.", "_"));
                        if(isEquals) {
                            tagetCla=cla;
                            break;
                        }
                    }
                    if(isEquals==false)
                    {
                        continue;
                    }

                    Log.d("sqldb", "continue");

                    /**查询表所映射类的信息*/
                    TableInfo info = TableInfo.get(tagetCla.getName());
                    Log.d("sqldb", "info"+info);
                    if (info != null) {

//						cudb.rawQuery("PRAGMA table_info(tbl_sfg_device)", null);
//						Cursor c=db.rawQuery("PRAGMA table_info("+tablename+")", null);
                        Iterator<Map.Entry<String, Property>> it = info.propertyMap
                                .entrySet().iterator();
                        // 检查该表是否有这个字段
                        HashMap<String, Boolean> map = new HashMap<String, Boolean>();


                        try {
                            Log.d("sqldb", "PRAGMA table_info("+tablename+")");
                            Cursor columns = db.rawQuery("PRAGMA table_info("+tablename+")", null);
                            while(columns.moveToNext())
                            {
                                Log.d("sqldb", "--->"+columns.getString(1));
                                map.put(columns.getString(1), false);
                            }
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                            throw new RuntimeException(e);
                        }
                        /** 遍历类所有字段 */
                        while (it.hasNext()) {

                            Map.Entry<String, Property> item = it.next();
                            String key = item.getKey();
                            Log.d("sqldb", key+" "+map.get(key));
                            /**该字段不存在新建*/
                            if (map.get(key) == null) {

                                db.execSQL("ALTER TABLE " + tablename
                                        + " ADD COLUMN " + key + " " + "CHAR");
                                map.put(key, true);
                            }
                        }
                        it = info.propertyMap
                                .entrySet().iterator();
                        /**删除未映射字段*/
                        while(it.hasNext()){
                            Map.Entry<String, Property> item = it.next();
                            String key = item.getKey();
                            /**该字段未被遍历删除*/
                            if (map.get(key)==true) {
                                db.execSQL("ALTER TABLE "+tablename+" DROP COLUMN "+key);
                            }
                        }
                    }

                } catch (SQLException e) {
                    DebugLog.d("sqldb", "error:" + e.toString());
//                    KJLoger.debug(getClass().getName() + e.getMessage());
//                    throw new RuntimeException(e);
                }
            }
        }
        if (cursor != null) {
            cursor.close();
            cursor = null;
        }

    }